Learning Normalisation (1 Viewer)

[jmq]

im trying to understand "Normalisation" up to 4NF. i have attached here my test diagram of what i have understood. any comments or corrections in it?
thank you.

 

[NauticalGent]

Looks good for the most part. However you may want to double check your DeliveryID in your Delivaries and DelivaryDeatails tables. I suspect your one-to-many relationship is reversed. Also, your relationship in Customers and Orders is not quite right I don’t think...

 

[jmq]

thank you NauticalGent!
how about this?

 

[NauticalGent]

Half way there, check your Customers and Orders relationship...

 

[jmq]

oh yeah!

seen it. all good now?

 

[NauticalGent]

Much better!

 

[jmq]

i will now proceed in access. then i hope you can follow along. just to guide my confused mind.

i'll first create the table and it's relationships then i'll post it again.

thank you!

 

[NauticalGent]

Always happy to help. One question, is your project for your use or is this a course you are taking?

 

[jmq]

just for my learning (refreshing).. i've not coded for a very long time.. getting dull i think.:banghead:

 

[NauticalGent]

Not a problem, best of luck to you..

 

[jmq]

here is the tables and relationships in access

all good?

if yes, i'll populate it with data. then create a form..

then i'll post it again here.


the problem i see here is in linking the details. order and delivery to their appropriate tables.

 

[jmq]

how do handle this delivery table?

the customer should of course buy first a product before he can have a delivery. how do you do that in user form?

Thank you.

 

[jmq]

should i use insert or can we just properly create a reference/relationship between orderDetails and Deliveries?

 

[plog]

I'm looking at the last image file you posted and it's not correct. You've created a circular relationship among Customers, Deliveries, DeliveryDetails, Orders and OrderDetails. There should only be one way to trace a path between tables, but your circular path allows 2.

To fix this more questions have to be asked about your data.

1 why do orders and deliveries both need details?

2 can part of an order be delivered?

3 can a delivery contain more than 1 order?

4 isn't cost more associated with an order than a delivery?

 

[jmq]

i'll get back tommorow

 

[jdraw]

?? How do you change a Product's price?
Is there a requirement to report Price history?

You may find this info helpful.

Also, there are a number of youtube videos by Dr. Jennifer Widom on various aspects of Relational database. Here is one. She is very quick, but if you're a fast learner or want a quick reference....

 

[jmq]

@ plog

i know very little about this stuff. so please bear with me..

with question

1. i think i need the details in delivery because if i don't have a details then what will answer the question "what to deliver?"
2. yes. i may order 5 items, 2 of which i decide to be delivered and 3 i'll just carry up
3. yes. a delivery can have many orders and orders could have many items
4. cost association? order has a cost and also delivery. like delivery charge? freight charge?

end with

would that answers make it clearer now?

i have attached a new version of my diagram. is it ok now?

 

[jmq]

is it proper to put a cost in delivery details? why order details has no cost?

 

[jmq]

to clarify the [type] in Table Orders

 

[isladogs]

Hi jmq

I've been reading this from the sidelines whilst others are assisting you very well

One question - are you using OneNote to create your relationship diagrams?
Whatever it is, they are very clear and helpful.