Quick DB design question - exam revision.

techguy

Hi,

Studying for repeat in Relational Databases at the moment. I wonder would it be okay to post in this thread any small questions that I have answered and need confirmation?

Firstly:

Most RDBMSs allow you to set referential constraints to handle the conditions ON DELETE and ON UPDATE. Do these conditions apply to the deletion and update – of a foreign-key value, or a primary-key value?

I would say primary key because if there is a change to the foreign key value then it is no longer related to the primary key row that it was related to. So why change that value?

Thanks.

fasty

The record with the foreign key is subject to on delete or on update constraints in an FK -> PF relationship.

techguy

fasty wrote: »

The record with the foreign key is subject to on delete or on update constraints in an FK -> PF relationship.

So would the answer primary key, foreign key or both??

Webmonkey

I would say it's the foriegn key that is subject to these actions.

fasty

techguy wrote: »

So would the answer primary key, foreign key or both??

What Webmonkey said.

Are you using a particular RDBMS? I mean, SQL Server Express is free and you could actually set up two tables with said relationship and see what happens when you delete a table who's PK is an FK in another table! The best way to know this stuff, it to do it!

techguy

I stand corrected..

Thanks Guys..

Also, can I post my other questions here as opposed to starting loads of threads?

techguy

---

Another Question: - My answers in red..

---

Suppose that the following business rules exist:
• Rule1: A Customer may be deleted at any time.
• Rule 2: A Customer’s customerNum attribute may be updated.
• Rule 3: An Order may never be deleted from the system.
(i) Specify an ON UPDATE referential constraint for the attribute Orders.customerNum such that the above listed rules are upheld.
Cascade
(ii) Specify an ON DELETE referential constraint for the attribute Orders.customerNum such that the above listed rules are upheld.
Set Null
--

TBH, I've looked at tutorials and i'm still confused..

I can understand this UPDATE, DELETE stuff a small bit. If the question was on Customers.customerNum then the answers would be Cascade and Set Null but i'm a little confused on what happends when we are talking about a foreign key.. do the constraints still apply?

fasty

Your answer is right, even if it would be stupid in real life to delete a customer and have an order on the system that was made by no one.

Customers.customerNum is a primary key, Orders.customerNum is the foreign key. The contraint is on this relationship, not a primary or foreign key, but it STARTS in the table with the primary key.

You delete that record, the delete cascades and you've told it to set the FK to NULL.

The question just seems terribly worded to me.

techguy

fasty wrote: »

The question just seems terribly worded to me.

It's not just me so.. I thought a few of the questions weren't very clear about what they wanted but I figured that was just me wanting them to tell me exactly what they wanted..actually isn't that the way exam questions are supposed to be asked

---

Here's another one if you guys don't mind..:)

---

Question:

Using the following example business rules and the example table of Appendix A (at the back of this exam paper),

The columns {sNum, pNum} are being used as the primary-key.

List four functional dependencies using the standard notation you have studied. (You must refer to one of the above rules or state an assumption to qualify each functional dependency you list)

• Rule 1: A supplier number uniquely identifies a real-world supplier.
• Rule 2: A part number uniquely identifies a part.
• Rule 3: The (supplier number, part number) combination can be used to uniquely identify how a supplier supplies a given part (i.e. quantity supplied and any other details about a shipment)
• Rule 4: The city in which a supplier is located determines the status value for that supplier.

Answer:
sNum -> sName (Rule 1)
pNum-> pName (Rule 2)
sNum,pNum -> qtyDelivered (Rule 3)
sCity -> sStatus (Rule 4)

Fingers crossed..

Thanks Guys..this really means a lot. I've to repeat this exam early August and I have nobody else to correct my work..

Ronan_

I don't fully understand your question and correct me if im wrong but, if rule 4 states; "The city in which a supplier is located determines the status value for that supplier", then why is sName in your answer. The status is affected by sCity and not sName?

techguy

My bad, sorry..

I did a bad copy and past job but I have corrected the errors now and included the question..d'oh!!

Galtee

fasty wrote: »

Your answer is right, even if it would be stupid in real life to delete a customer and have an order on the system that was made by no one.

Customers.customerNum is a primary key, Orders.customerNum is the foreign key. The contraint is on this relationship, not a primary or foreign key, but it STARTS in the table with the primary key.

You delete that record, the delete cascades and you've told it to set the FK to NULL.

The question just seems terribly worded to me.

I may be late in but just to pickup on a few things, Deletes & updates only cascade if you implicitly specify the referential constraints. The answer to the chaps original question is Foreign Keys as they are by definition affected by referential constraints, given that most databases offer oninsert, onupdate, ondelete events that are fired on the actual table whose record(s) are being inserted, updated or deleted this would have been a badly worded question except for the mention of the term referential constraint.

fasty

Yeah, that sounds better than how I explained it!

techguy

Ok, well here's another question on Normalisation.

I'm happy with it but I'd just like to know for sure if i'm doing it right.. The amount of marks going or each question seems kind of odd.. 2 marks for part (i) even though I did a lot of work to get there.. Decompose table to 1NF and then 2NF. That brings me to my main query, am I doing too much here? Is it jst a one or two line answer that is required?
Part (ii) and part (iii) seem to be the same questions asked in slightly different ways but they have most amount of marks..

What am I missing here?

---

Here's my work: (PK is underlined)

---

Table:

Questions:
Using the single-table of Appendix A as a start point
(i) Decompose an example of a 2NF table.(2 marks)
(ii) State why the table you specified as your answer to part (i), is in 2NF and not in 3NF.(4 marks)
(iii) Decompose an example of a 3NF table.(2 marks)
(iv) State why the table you specified as your answer to part (iii), is in 3NF and not in 2NF.(4 marks)

Answers:
(i)
1NF:
SingleTableDB(sNum,sName,sStatus,sCity,pNum,pName,pColour,pWeight,pQtyDelivered)

Dependencies:

sNum->sName,sStatus,sCity (Partial Dep)
pNum->pName,pWeight,pColour (Partial Dep)
sNum,pNum->pQtyDelivered (Full Dep)
sCity->sStatus (Transitive Dep)

Dependency Diagram:


2NF:
Suppliers(sNum,sStatus,sCity)
Parts(pNum,pName,pWeight,pColour)
Delivieries(sNum,pNum,pQtyDelivered)

** Dependency Diagram for each new table here **
** Suppliers shows transitive dependency between sStatus and sCity **

(ii)
Because a transitive dependcy exists (???) Enough for 4 marks?

(iii)

sCity -> sStatus
Suppliers (sNum, sName, sCity)
Parts(pNum, pName, pColour, pWeight)
Deliveries(pNum,pName,pQtyDelivered)
Status(sCity,sStatus)

(iv)

Because the transitive dependency between sCity and sStatus no longer exists. I have made a new table Status that stores the relevant sStatus for each sCity. (???) Enough for 4 marks?

What is non-loss decomposition?
Non-Loss Decomposition happens during normalisation. It means that during normalisation (decomposing tables) no data is sacrificed due to the restructuring of tables.