Free up your time by getting your academic assignments done faster- without compromising on quality!

Custom Academic Papers

100% Original, Plagiarism Free, Customized to your instructions!

How It WorksOrder Now

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.

Hire your personal Essay Writer TODAY!

Get 20% off on your first two Orders this week.

Why Choose Us?

Frequently Asked Questions

9

Qualified Writers

Each paper writer passes a series of grammar and vocabulary tests before joining our team.
9

Anonymity

We care about the privacy of our clients and will never share your personal information with any third parties or persons.
9

Free Turnitin Report

A plagiarism report from Turnitin can be attached to your order to ensure your paper’s originality.
9

24/7/365 Support

You can contact us any time of day and night with any questions; we’ll always be happy to help you out.

Will your essay writers follow my paper instructions?

Yes, absolutely! When you make your first sign-up, you get a personal cabinet for your comprehensive experience.

You will be able to provide the instructions or attach files directly to the order.

Additionally, you will be able to chat with your essay writer and discuss any specific details or ask any questions.

How are you going to make sure that my essay is plagiarism-free?

We guarantee the originality of all our papers and have a zero-tolerance policy towards plagiarism.

We can provide you with a free plagiarism report from Turnitin (upon request).

How fast can you write my essay?

The minimum deadline is 3 hours, but if your assignment isn’t complicated, we may be able to adjust to your time-frame needs. Simply give us a deadline, and we’ll get it done.

We also offer discounts depending on the urgency of your assignment. The further your deadline, the lower the price per page will be.

If you write my paper and it has some issues, what should I do?

First of all, we will do our best to ensure that everything goes well.

If something needs to be changed/fixed/amended, we will revise your paper an unlimited number of times, free of charge, until you are satisfied with the results.

We also provide 24/7 support to assist you whenever you need it!

How does your essay writing service process payments?

We are certified by PayPal, Visa, Mastercard, American Express, and Discover. Furthermore, we process all payments a secure gateway, which ensures complete security and privacy of your data when you pay for an essay.

Does your paper writing service have any features?

Sure thing, we do!

When you order essays from our essay writing service, you get the following features for free:

• Plagiarism report
• Personal chat with paper writer
• Unlimited Revisions
• Title Page
• Formatting
• Best writer
• Outline