Answered You can hire a professional tutor to get the answer.
Designing with Functional Dependencies and Normal Forms This assignment is to gain practice designing relational tables using functional dependencies...
Designing with Functional Dependencies and Normal FormsThis assignment is to gain practice designing relational tables using functional dependencies and normal forms. The questions have similar but slightly different requirements.Problem 1)This problem is from an old final exam. Consider the following attributes that someone wants to keep track of in a relational database.//student number//name of employee//employee address: city//employee address: street number//employee address: street name//employee address: postal codephone_number //student phone number//course number e.g C3005W09 (note course number has "built in" section -winter 2009)//course name//location of a course or office e.g. TB204// building name e.g. "Tory"// time period of course e.g. Tues and Thurs 10:00-11:30//grade a student gets in a course//temporary attribute that can be removed when the design is finishedDependenciesstdnum -> 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->buildingstdnum,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 completeR1.1) [5 marks] 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).Marks assigned as follows[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) [5 marks] 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)Deduct marks from the max as follows. Deduct 1 mark for each dependency from the original set that is not shown to be a logical consequence of the new set. Similarly deduct 1 mark for each dependency from the new set not shown to be a logical consequence of the original dependency set. (Deduct only up to the max number of marks).R1.3) [1 mark] Determine whether your design tables are all in BCNF as well and if not decompose them further until they are.Assign 1 mark for a BCNF decomposition or an explanation of why it is not necessary.Problem 2)Riggs Auto is a small garage that specializes in Porsche repairs. Bill Riggs, the owner, wants to use a database to keep track of his customers and the cars he works on. He also wants to keep historical data on the cars so that customers who wish to buy an older car can access some of its history. Below is a list of data (attributes) that Bill wants to maintain in the database (You may add others if you think it is necessary). Also listed are some functional dependencies that apply to the data that Bill has come up with. Note there may be some functional dependencies that Bill has not thought of that you may have to address. Also it might be that some functional dependencies could be stated better in another way. It is part of this question to scrutinize them and if necessary modify them.The following requirements and assumptions apply. A person can own several cars, but a car is owned by a particular person at any given time. A person can have owned many cars, and a car can have had many owners. The database must keep information about the current and past owners of a car. A customer’s name and address combination is unique. A car has several license numbers during its life but only one VIN (Vehicle Identification Number). We will assume that while someone owns a car they use the same license plate number for the duration of the ownership. The database must keep track of current and past licence plate numbers. Finally the database must keep track of all parts installed in a car and the invoice on which the part was billed to the customer. A part is for a particular make, model, and year of car. An invoice lists the customer, car, and any parts installed and the hours of labour needed to install the part.Data Attributes:Make, Model, Year //of a carVIN //vehicle identification number of a car (unique)LIC //license plate number of a car (a car can have several license numbers in its life)Street, StreetNum, City, PostalCode //address of customer or car ownerName //name of owner or customerPartNum //part number of a car partPartQty //quantity of part in stockBuyDate //date on which a car was bought by a particular ownerSellDate //date on which a car was sold by a particular ownerInvDate //invoice dateInvNum //invoice number (unique)Hours //labour hours to install a part.Dependencies:Street, StreetNum, City -> PostalCodePostalCode -> CityVIN->make, model, yearName, Street, StreetNum, City , BuyDate, SellDate -> VIN, LICName, Street, StreetNum, City , VIN ->BuyDate, SellDate, LICPartNum -> PartQty, Make, Model, Year //part for a make, model, and year of carPartNum, InvNum -> InvDate, VIN, Hours //if part is installed in a carInvNum ->Name, Street, StreetNum, City, VINR2.1) [no marks] Examine the functional dependencies provided and decide if you want to make any changes. Show the changes and give the rational for why the changes are being madeNo marks for this part, it is optional how much they want to alter the dependencies and still represent the situation describedR2.2) [5 marks] Find a minimal cover of your set of dependenciesDeduct 1 mark for any dependency that does not need to be in the min cover (i.e. a redundant dependency)Deduct 1 mark for any dependency that has more than one time in its right hand side. e.g A,B->C //OK A,B ->C,D //not OKDeduct 1 mark for any dependency that has unnecessary attributes in its left hand side. e.g. A,B,C-> D is not OK if A,C->D give the same resultR2.3) [5 marks] Provide a set of tables for the database based on your minimal cover with all tables in 3rd normal form. Show the keys for each tables and the dependencies that map onto each table.Marks assigned as follows[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.)Problem 3)Consider a Database that keeps track of scenes filmed for different movies. A movie uses a screenplay (or story) which is broken down into scenes. The movie will also have the same scenes because it is a movie of that screenplay. Not all screenplays become movies, but every movie is of a particular screenplay. Scenes have a story-location where the story takes place and a filming location where the filming will actually take place. Each scene has some actors that appear in that scene. Actors have a name, phone number, address and agent that represents them. A scene can be filmed more than once (maybe the actor forgot their lines). Each filming of a scene is called a “Take”. The movie is usually created by using the best take of each scene and putting them together. Below is an E-R diagram that captures these requirements.Here is a proposed E-R diagram provided for the situation described above.R3.1) [5 marks] Provide a set of Functional dependencies that completely captures all the features in the situation and ER diagram.Deduct 1 mark, up to the maximum, for any ER-diagram feature that is not represented somehow with a functional dependency. (Note allow that N:N relationships with no attributes could have an extra "temp" attribute assigned just to make it non trivial)R3.2) [5 marks] Provide a minimum cover for the set of functional dependenciesDeduct 1 mark for any dependency that does not need to be in the min cover (i.e. a redundant dependency)Deduct 1 mark for any dependency that has more than one time in its right hand side. e.g A,B->C //OK A,B ->C,D //not OKDeduct 1 mark for any dependency that has unnecessary attributes in its left hand side. e.g. A,B,C-> D is not OK if A,C->D give the same resultR3.3) [5 marks] Based on your minimum cover find a lossless-join, dependency preserving, 3rd normal set of tables use for your databaseShow for each table in the decomposition, its key and the dependencies that apply to it. Marks assigned as follows[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.)R3.4) [2 marks] Determine if any of your 3NF tables are not in BCNF and if so decompose them further so they are in BCNF form. There may not be any depending on your designAssign [2marks] for a BCNF decomposition or an explanation of why it is not necessary.Problem 4)In assignment #1 and #2 your were asked to propose a database project that you will build. Note that depending on your design instincts you might not have any decomposition to do to reach the indented normal forms, but you should be able to recognize when this is not necessary.R4.1) [5 marks] Provide the E-R diagram which shows you initial understanding of the data and constraints (Just repeat the one from assignment #1 or #2. This is done so the TA can be reminded of what your database is about.) Assign 5 marks of providing this, you don't have to assess it further.R4.2) [5 marks] Provide a list of attributes which you need to store in your database (perhaps imagine that all attributes are initially in a single table -you don't need a key for this imaginary table). Deduct 1 marks for any attribute evident from the E-R diagram that is not accounted for in their list of attributesR4.3) [5 marks] List all the functional dependencies that apply to the attributes. Use the dependencies to capture 1:1, 1:N, and N:N relationships in the ER diagram as well.Deduct 1 mark, up to the maximum, for any ER-diagram feature that is not represented somehow with a functional dependency. (Note allow that N:N relationships with no attributes could have an extra "temp" attribute assigned just to make it non trivial)R4.4) [5 marks] Find a lossless-join, dependency preserving, 3rd normal form decomposition of this imaginary table.Show for each table in the decomposition, its key and the dependencies that apply to it. Marks assigned as follows[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.)R4.5) [2 marks] Determine if any of your 3NF tables are not in BCNF and if so decompose them further so they are in BCNF form. (There might be nothing to do here depending on your design).Assign [2 marks] for a BCNF decomposition or an explanation of why it is not necessary.
