Tasks

a. Draw an ER diagram showing the manager's conceptual view of Happy Customer Garage.

b. Using Crow's Foot notation, transform the ERD into a normalised, logical, (relational) model, resolving any M:N relationships, and showing connectivity and cardinality. Add appropriate attributes to each relation and define primary and foreign keys.

c. Summarise your proposed implementation in a simple data dictionary in tabular form, showing all designer-defined objects in the database including indexes on the customer name in the CUSTOMER table and product name in the PRODUCT, a SEQUENCE to generate unique maintenance record numbers starting from 1000, and a TRIGGER that sets a flag when a part's quantity on hand falls below a designated minimum. The data dictionary should show the data types and any constraints on attributes.

d. Provide a SQL script file to define the database schema, HappyCustomerDB. Your script should be organised so that whenever the script is loaded all the database objects will be dropped and re-created.

e. Provide SQL script to insert at least several rows of data into each table.

You should zip your diagrams and scripts into a single file and submit via Stream. Your assignment will be marked by checking your diagrams against the business rules and running your scripts in Oracle 10g and evaluating the results.