Design a database by analysing the requirements for the scenario detailed on the following pages. State any assumptions/notes you have made regarding your database design at the beginning of the database design document. Do not make any assumptions/notes that change the structure of the scenario, as this may make Task 2 of the assignment difficult. Only make assumptions/notes that influence your database design. If you are unsure about an assumption/note you wish to make, ask your lecturer/tutor. Once you have identified the entities, attributes and relationships of the scenario in sufficient depth, you are required to create a logical ER diagram and a physical ER diagram to depict your database. Adhere to the distinctions between logical and physical ER diagrams covered in lectures. Show the Table Creation Order in your physical ERD. Lastly, create a Table Instance Chart (TIC) Data Dictionary for each entity in your data model. List your TICs in the appropriate table creation order that will need to be used to create the database. Include any additional information, if any, that may be needed to implement the database. Remember, the TICs should contain all the information needed to implement your database. For an example, download the 'Sample Table Instance Charts for Assignment Task 1' from Moodle. Your complete database design should consist of a list of assumptions/notes, logical and physical ER diagrams and TICs. This should be in the form of a single word-processed document. Be sure to include details of both team members if appropriate. You can use www.draw.io or www.gliffy.com (or any other modelling tool you are familiar with) to draw your ERDs. Show Primary and Foreign Keys as such: CustomerID (PK) and CustID (FK). Page 2 of 4 Scenario Details You have been hired to design a database for a toy shop business. The following are details about how this toy shop 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). • Customers can 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. • 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. • 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. Page 3 of 4 Further Details Some further details and restrictions regarding the scenario are relevant: • Customers cannot be referred by themselves (same customer ID and referrer). • The email address of a customer must be unique and must contain a "@" • Prices and Costs of products must be greater than or equal to $0.00 • The name of each category must be unique • The date/time of an order should default to the current date/time. • The quantity of an ordered item should default to 1, and the database should not allow the same item to appear multiple times in an order (same item and order ID on multiple rows). General Information and Guidelines The information above describes the entities, attributes and relationships required in the database. Some minor details, such as the cardinality of some relationships, have been omitted. It is up to you to make (and state) any assumptions you need in order to complete the database design. If you are uncertain about any part of the scenario described above, seek clarification from your lecturer. Be sure to specify the most appropriate data type (and length, where applicable) for each attribute in your data dictionary. Note that when you are storing a date/time, it should be stored as a single column - do not split the date and time into two columns unless there is a very good and necessary reason to do so. Some tables will benefit from having a compound primary key