Task$01$

You have been hired to design a database system for an online toy store. The database must keep track of

the customers, suppliers, products, product categories, and orders. You have the following information

about the way the store operates:

$$ Customer details must be recorded. This includes a customer number, first name, last name, email address

and password.

$$ The store wishes to implement a $$referral system$$ to reward customers who tell others about the

store. Therefore, customer details should also include a $$referrer$$ column, which will contain the

customer number of the customer who referred them, if applicable $($not all customers are referred by

someone$).$

$$ Details of item suppliers must be recorded $($i$.$e$.$ the sources of the items that they sell$).$ This includes a

supplier ID number, business name, phone number and website URL.

$$ Details of products must be recorded. This includes an item ID number, name, description, price $($how much

the store sells it for$),$ cost $($how much it costs the store to buy$)$ and stock $($how many of the item the store

currently has available$).$

$$ The store keeps track of the supplier of each product. Products only have one supplier.

$$ A list of item categories must be recorded, and the database must keep track of which items are in which

categories. All items are in at least one category, but can be in several of them.

$$ The only category details required are a category ID number and a category name.

$$ Details of orders made by customers must be recorded. This includes an invoice ID number, the date$/$time of

the order, a delivery address and a billing address, as well as the customer ID number of the customer who

made the order.

$$ For each order, the database must record details of ordered products $($i$.$e$.$ which products were included in

the order$)$ and the quantity ordered. Each order must contain at least one item.

Based on the details above, you are required to draw both a logical E$-$R diagram for this database and then a

corresponding physical E$-$R diagram. Clearly show all cardinality, primary and foreign keys, attributes and relationships

as appropriate.

Adhere to the distinctions between logical and physical E$-$R diagrams defined in Lecture $3.$

Use enhanced E$-$R model notation where$/$if appropriate, and remember to state your assumptions.

Task$02$

The toy store from Task $2$ now requires you to update and expand the previous database you designed for them, to

record some new and additional information. You must update and expand your E$-$R diagrams from Task $3$ to

incorporate these new requirements:

$$ Customers can now define addresses which are stored in the database. A customer can define multiple

addresses, and each address is associated with a single customer via their customer ID number. As well as

specifying the address itself, customers can specify a name for the address, e$.$g$.$Home$.$ An address ID

number is used to identify each address.

$$ Rather than storing addresses directly in the order details, the order details should now contain two

foreign keys referencing the address entity $$ one for the delivery address and one for the billing

address.

$$ Every order must reference an address for delivery and an address for billing, and the same address

can be used for delivery and billing.

$$ You do not need to worry about ensuring that the addresses associated with an order belong to the

customer placing the order $$ This is enforced outside of the database.

$$ The toy shop has decided to start doing limited time special events. Each event has a name, a start date$/$time

and an end date$/$time$.$ The store offers a percentage discount to all orders made during the event $($that is all

orders with an order date that occurs after the event$$s start date and before the event$$s end date$)$ which the

database will need to keep track of$.$

Create updated and expanded versions of your logical and physical E$-$R diagrams from Task $3$ that incorporate these

requirements. You should not need to modify much from your Task $3$ solution; you mainly need to expand the

diagrams by adding the new attributes, entities and relationships.

Remember to state any additional assumptions you make, use enhanced E$-$R model notation where$/$if appropriate, and

show cardinality on both diagrams. Ensure that you submit separate solutions to Tasks $3$ and $4$ a total of four E$-$R

diagrams.