DATABASE ASSIGNMENT QUESTION -
PURPOSE - This assignment requires student to draw a complete Entity Relationship Diagram (ERD) and create the relational database based on the given scenario with its business rules.
Consider the following scenario:
i. A customer can own many cars, but some customers do not own cars.
ii. A car is owned by one and only one customer.
iii. A car can get (generate) a maintenance record more than once.
iv. Each maintenance record is generated by one and only one car.
v. Some cars have not (yet) generated a maintenance procedure.
vi. Each maintenance procedure can use many parts.
(A maintenance procedure may include multiple maintenance actions, each one of which may or may not use parts. For example, 10,000 kilometers check may include the installation of a new oil filter and a new air filter. But tightening an alternator belt does not require a part.)
vii. A part may be used in many maintenance records.
(Each time an oil change is made, an oil filter is used. Therefore, many oil filters may be used during some period of time. Naturally, you are not using the same oil filter each time - but the part classified as "oil filter" shows up in many maintenance records as time passes.)
Based on the above scenario:
(a) Identify ALL entities (give suitable entity name); complete all the entities with necessary attributes together with assigned primary key; (Use your own assumption to determine the attributes)
(b) Identify ALL relationships, cardinality, and participation for each related entities.
(c) Draw an Entity Relationship Diagram (ERD) by clearly state the relationships between entities, cardinalities and attributes with correct notation.
(d) Based on ERD created in (c), convert it into relational schema. Indicate the primary key and foreign key for each relational schema (relations).
(e) Produce sample records for each relations that observe the relational integrity rules. FIVE records per relations are enough.
(f) Then, produce FIVE SQL queries that can be used to manipulate the data from each relations and show ALL the results from each of the queries. (The queries should include a simple queries, query for sorting results, aggregate function, subqueries and query to alter the existing tables)