Hw1

• Try to answer all the questions using what you have learned in class

Some questions are marked as bonus. You do not have to answer these questions to get full points for the assignment. However, you can get bonus points for these questions!

Question 3.1.1 (60 Points)

Build a conceptional model for a Material Resource Planning. The solution should be presented as an ER-diagram. Base your design on the following requirements.

The database should record information about Dealers, Location, Model, Series, Employee, Parts, Warehouse, supplier.

A Dealer identified by their account number, has also name, Address, phoneNo and email address on file. A location

that include locationNumber, and manager.

• Dealer may provide Feedback for each transaction they have placed (optional) for car model. For every transaction, a Dealer has to make a Payment. Each time a dealer buy car model (s) it forms a transaction

A Model is identified by the combination of name and year, manufacturer, type. A Serie is associated with a model

have a discriminator name, transmissionType, traction, seatMaterial

Each model is made by constitute of different quantities of Parts, that include partNumber, name, material and certification, leadTime

• Different parts are sold by different supplier at different prices, the supplier information are name, country, tier.

• The warehouse includes the list of parts inventory storage their

city, size, environment,

• An Employee is uniquely identified by the employeeId. For an Employee we record a transaction performed for dealer and salary

, bonus. Bonus each employee has to do referral and promote close deal to get their bonus.

Part 3.2 Translation of ER into Relational Model (Total: 40 point)

Question 3.2.1 (40 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