Task 3 Consider the following business rules for an online store.

Customers register their name, address (consisting of street number, street name, suburb, state), credit card number and email account with the store, which uses their email account as a key. The store also records the customers class (one of Gold, Silver or Bronze, with the initial value of Bronze) and the customers discount rate (10% for Gold class, 5% for Silver class and 0% for Bronze class customers).

A customer can make online orders, each of which has an order id, an order date, a shipping date and the products and their quantities making up the order. Products have a product code, a product name, a description, one or more technical specifications and a unit price. A customer entity has already been modelled from the above rules, as follows:

Q3.1 Based on the above information, and utilising the above customer entity as is, complete the database requirements as an Entity-Relationship (ER) diagram. Carefully state any assumptions that you make. In your ER diagram, you must properly denote all applicable concepts, including weak or strong entities, keys, composite or multi-valued attributes; relationships and their participation and cardinality constraints. Page 4 of 6

Q3.2 Map the ER diagram from Q3.1 into a final relational database schema using the 4 step process described in week 4. Show every step of the mapping. No marks are awarded to the final schema if you do not show the partially built schema at the end of each step. Indicate the primary key (underlined) and any foreign key (with an asterisk) in each relation.

Q3.3 Validate each of the relations in the model using the normalisation process. For each relation: Give the (non-trivial) functional dependencies.

For each normal form (1NF, 2NF, 3NF) state if the relation is that form. Justify your answer.

If a relation is not in 3NF, perform a lossless decomposition of it to 3NF.