CS425 Database Organization - Assignment 3: Modeling, ER and Normalization
Homework Assignment 3
CS425 - Database Organization Results
Instructions
• Try to answer all the questions using what you have learned in class
Part 3.1 Modeling (Total: 35 Points) Question 3.1.1 (35 Points)
Build a conceptional model for an Automobile company. The solution should be presented as an ER- diagram. Design your ER diagram with the following requirements.
- The database should record the information about Customers, Model, Brand, Vehicle, Dealer, Option, Inventory, and Company. Automobile company assists its Dealers in maintaining customer records and dealer inventory, as well as assist the sales staff in ordering cars.
- A Customer consists of Customer-ID, Name , Address and Phone. Customers are identified by a unique customer-ID. A Customer can have one or more cars, but each car is bought by only one customer.
- A Model consists of a unique model-ID, Model-Name, and Year.
- – Each model can be offered with a variety of options, but an individual car may have only some (or
none) of the available options.
- – Each brand offers several models.
- An Brand consists of unique Brand-ID and a Brand-Name and Producer-Company.
– Each company may have several brands (for example, GM has Chevrolet, Pontiac, Buick, Cadillac, GMC, Saturn, Hummer, Saab, Daewoo, Holden, Vauxhall, and Opel and Volkswagen has Volkswagen, Audi, Lamborghini, Bentley, Bugatti, Skoda, and SEAT).
- A Vehicle is identified using a Vehicle-ID. A Vehicle has a Price and Color.
- – Each individualvehicle is a particular model of a particular brand offered by the company (e.g., the
RAV4 is a model of the car brand TOYOTA of TOYOTA company).
- – Each individual vehicle has an identification number.
- A Dealer is uniquely identified by the Dealer-ID. A Dealer has a Name, Address and Phone.
- A Company includes a Company-ID, Company-Name,Address and Phone.
- An Option has its unique Option-Id. Option also has a Specifications of option and engine and trans- mission.
- An Inventory consists of the Inventory-ID, Name, Location and Phone. – Dealer keeps some cars in inventory
Part 3.2 Translation of ER into Relational Model (Total: 35 Points)
Question 3.2.1 (35 Points)
Take the following ER-model and translate it into a relational schema using the rules presented in class. Present the relational schema as an SQL script (assume that all attributes are of data type INT). Present the results of the following intermediate steps in this order:
1. Translate strong entities + unnest composite attributes 2. Translate weak entities
3. Translated multi-valued attributes
4. Translate relationships
Part 3.3 Normalization (Total: 30 Points)
Question 3.3.1 (30 Points)
Considering the following relations, determine the candidate key(s) and normal form for each relation (note that a relation can be in multiple normal forms). Please only consider the following normal forms: 1NF, 2NF, 3NF and BCNF.
1. R(S,T,U,W) and the Functional Dependencies are U→T,T→W,S→TW
2. R(A, B, C, D, E, F ) and the Functional Dependencies are AB→E, C→DE, E→A
3. R(A,B,C,D) and the Functional Dependencies are AB→CD,D→A
4. R(A, B, C, D, E, F ) and the Functional Dependencies are A→BCDEF, BC→ADEF, B→F, D→E
5. R(A,B,C,D,E,F,G,H)andtheFunctionalDependenciesareABC→DE,E→FG,H→G,G→H,ABC→EF 6. R(A,B,C,D) and the Functional Dependencies are C→B,B→AC,A→BD
