Consider the following attributes that someone wants to keep track of in a relational database.
stdnum //student number name //name of employee city //student address: city strnum //student address: street number street //student address: street name postcode //student address: postal code phone_number //student phone number course_num //course number e.g C3005W09 (note course number has "built in" section -winter 2009) course_name //course name room_num //location of a course or office e.g. TB204 building // building name e.g. "Tory" period // time period of course e.g. Tues and Thurs 10:00-11:30 grade //grade a student gets in a course temp //temporary attribute that can be removed when the design is finished
Dependencies stdnum -> name, city, street_num, street, postcode, phone_number; postcode -> city; phone_number -> city; course_num -> course_name, room_num, period; period,room_num -> course_num; room_num->building stdnum,course_num -> grade; stdnum,room_num-> temp;
Note the last dependency is an attempt to capture a many-to-many relationship that several students are assigned to an office. Imagine a room with many desks and that students are assigned to a specific desk to use as their office during the school year. The desks are however not identified in the database. The temp attribute is meant to make this a non trivial dependency, since the alternative would be to capture it as stdnum,room_num ->stdnum,room_num. This would be the correct capture of an N:N relationship but it would be trivial and removed by the normalization process in which case the data would not be represented. In the final design the temp attribute can be removed when the tables are complete
R1.1)Find a lossless-join, dependency preserving, 3rd normal form decomposition of the attributes that are to be stored. Show for each table in the decomposition, its key and the dependencies that apply to it (that is, the dependencies that project onto it).
[1 mark] Each table should have primary keys indicated [1 mark] The tables together should include all the necessary attributes [1 mark] Each table should show the dependencies that apply to it (this is the new set of dependencies) [1 mark] All The tables should be in 3nf [1 mark] It should be possible to join the necessary tables back together in a lossless way. Note need not be possible to join all the tables together into one giant table, only those tables that would need to be joined to support the intent of the application. (They don't need to show this but it should be true of their solution. If you don't think this is true, mark it wrong and have the students come an demonstrate this to you.)
R1.2)Show that your new set of functional dependencies is equivalent to the original set -i.e. show that the decomposition is dependency preserving. (To do this show that the closure of the original set of dependencies and the closure of those of the decomposition are equal, alternatively show that each set has the same minimal cover)
R1.3) Determine whether your design tables are all in BCNF as well and if not decompose them further until they are.

AccountingQueen

(3)

Status NEW Posted 06 Nov 2017 04:11 PM My Price 10.00

----------- He-----------llo----------- Si-----------r/M-----------ada-----------m -----------Tha-----------nk -----------you----------- fo-----------r u-----------sin-----------g o-----------ur -----------web-----------sit-----------e a-----------nd -----------acq-----------uis-----------iti-----------on -----------of -----------my -----------pos-----------ted----------- so-----------lut-----------ion-----------. P-----------lea-----------se -----------pin-----------g m-----------e o-----------n c-----------hat----------- I -----------am -----------onl-----------ine----------- or----------- in-----------box----------- me----------- a -----------mes-----------sag-----------e I----------- wi-----------ll

Not Rated(0)

• Buy Answer