Task 2:
Scenario:
Oman Tourist Center (OTC) rents cars to its customers on rent. A car is rented to one or many
customers. A customer is characterized by a unique customer number, other attributes of the customer entity have to assumed by you (minimum four attributes are expected). A car is identified by a unique serial number. The other attributes are model number, manufacture year and class. A customer should have rented at least one car or many cars from OTC. Whenever a customer rents a car, OTC wishes to record the various rental details (you need to assume and add at least 4 attributes in addition to referential keys). A car is manufactured by exactly one manufacturer. A manufacturer manufactures at least one or more cars. A manufacturer is defined using unique number, name, country, sales representative name and telephone number. A car has had many maintenance events. A maintenance event corresponds to exactly one car. Maintenance event is characterized by unique repair number, date of maintenance, mileage, repair time.
a) Construct the Entity Relationship Diagram (ERD) for the above given scenario. Identify all the entities, associative entities, attributes of each entity including primary key, relationship between the entities and cardinality constraints. State the necessary assumptions for your recommended database design.
b) Analyze the above given scenario and discuss about the possible structure of associative relation by giving the details on the datatype and the size of each attribute. State any assumptions necessary to support your design.
c) Discuss referential integrity constraints in the design arrived at in task 2(a).
Task 3:
a) Normalize the below given Form to First Normal Form, Second Normal Form and Third Normal Form. Make assumptions for the identification of the primary key wherever necessary.
Here is a table of “Voyage Attendant Statement” I have upload it in a separate word file named “Module Attendance Sheet “.
The attribute names chosen should be meaningful and should be different for each student to avoid similarity in the submitted work. The students are advised to add the last 4 digits of their student id to the names of the entities to avoid similarity with other students’ work.
b) Discuss the storage requirements for complete database (obtained in 3NF). Support your answer with reference to the above Question. (At least 150 words)
c) Calculate storage requirements of complete database (obtained in 3NF) for 5 records in each table. Assume the following: 2 bytes for int/integer, 4 bytes for numeric/date, 1 byte for 1 character. (Maximum 400 words).