Once you find out what is wrong with it, redo the original design in correct los

Question

Once you find out what is wrong with it, redo the original design in correct lossless-join 4NF, doing only the minimum amount of decomposition required. (assume that the dependencies identified above are the only ones that hold.) For each table in your new scheme, underline the primary key.

As you the one your professor recommends for the Job recall, Mr. Claus runs a worldwide distribution business that requires him to maintain data on customer "orders and "credit". Order data is accumulated by regional representatives ating out of malls and department stores throughout the month of December. Credit data the the form of naughty" and "nice" reports filed by parents, teachers, and others throughout year. One of Mr. Claus's employees a Mr. R.T.R, N. Reindeer prepared the following design before learning about MVD's in CPS352: Toy order child ID, name, address age, gender, toyName) Naughty childID, time, offense) Niced child ID, time, goodDeed) Examples toy Name age gender address 12345 Football Lucy 20 Schultz Lane Blanket 56789 Linus 20 Schultz Lane 4 M child ID offense Yanked football from Charlie 12345 08:00 12-Sep-2016 ed ID time Read Christmas story on special 56789 19:00 15-Dec-2016 Upon taking CPS352, Mr. Reindeer identified the following FD's and MVD's for this data childID name, address, age, gender childID toyName childID time, offense child ID time, goodDeed 26. Rudolph realized that his original design is not 4NF. He proposed the following "lossless- join 4NF decomposition." What's wrong with it? Child Child ID, name, address age, gender) Toy Order CtoyName) Naughty childID, time offense) Same as before Nice child ID, time, goodDeed) Same as before

Explanation / Answer

Given decomposition is wrong. As there is no way to find about relation between child and toyname.

That is this relational schema would not be able to give details about which child bought which toy.

It can be decomposed in following way :

Assuming each parent will comment Naughty or nice only once
childID ->> toyname is the only non trivial mvd here.

so to remove this mvd, decompose the relation as given below:
Child(childID, name, address, age, gender) with p.k. = ChildId
Order(childID, toyname) with p.k = {childID, toyname}
Naughty(childID, time, offense) p.k = {childID}
Nice(childID, time, goodDeed) with p.k= {childID}

given decomposition is loss less join, as all FD, and MVDs hold.

Related Questions

Navigate

• Browse (All)
• Browse O
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.