Draw ER Diagram for a following case study?

Requirements:

1. Confirm to 3rd Normal Form
2. Should be aware there are a number of disjoint subtype entities
3. Valid supertype and subtype entities and hierarchy included
4. Should use Crow's Foot notation and confirm to the standards
5. All relationships should be labelled and identified as Mandatory or Optional
6. All connectivity, participation and cardinalities (if there are specific limits) should be shown
7. All appropriate entities, attributes and relationships identified
8. Assumption to be included at the beginning.
9. Explain the relation among Entities
10. All cardinalities and participation between Entities should be explained.
11. All the relation of each entity should be identified.
12. Tables with Data for Each Entity

Case Study:

A Toys for You to home delivery, sells Toy's for example: Doll, ball, Teddy bear, toy car and toy train and other toys over the website and phone. They need a system that handles the delivery of purchased goods to customers. They need to understand the data storage requirements for this system. The Toy's for You to home delivery have distribution warehouses in most of the Australian states (the New South Wales Australia warehouse looks after Sydney warehouses and Canberra ). The warehouse is usually located in an industrial Area. For example, the warehouse for Victoria is located at address in Moorabbin, and the NSW warehouse located in Alexandria. Each warehouse has a unique id, one or more managers, address (one address line, suburb, and state (postcode is obtained from these), email address, phone number and other information is also kept about area of the warehouse (in square meters), number of loading bays, number of access points/doors and a general description for any other interesting information. Information is additionally kept about the manager/s e.g., title, highest qualification obtained and date of that qualification. As well as managers, the warehouses also have other employees - workers, drivers and Jockey's. The workers pack vans for delivery of the goods and unload and store warehouse deliveries. All workers must have a vehicle driving licence (the licence number and expiry date are kept on file) and also have a licence to drive forklifts and the forklift licence number and expiry date are also kept on file. All drivers have a driver's licence and a record of any endorsement to drive certain vehicles. As well as their driver's licence number and expiry date, the highest endorsement level and the endorsement expiry date are kept on file. A driver is able to drive a delivery vehicle with a Gross Vehicle Mass (GVM) that is equal to or less than his endorsement level. All assistants have certificate qualifications that allow them to correctly install appliances. The certificate title and year awarded is kept on file for assistants. Assistants assist the driver in delivering and installing the delivered appliance. Sometimes assistants also drive, but only in emergencies. Their driver's licence number and expiry date must also be kept on file. For all employees, a record is kept of their employee id, first name, last name, contact phone, contact email, start date, termination date and as noted above, driver's licence number and expiry date. Each warehouse has a fleet of transport vehicles. These can range from 5 tonne trucks to small 1 tonne vans. All vehicles are identified by their registration number and information is also kept about their type, seating capacity, carrying capacity (the tonnage GVM already mentioned e.g. 5 tonnes), kerb mass/weight (the tare mass with a full tank of petrol i.e. the weight of an empty vehicle ready to be loaded), load space in cubic metres, maximum load area height, maximum load area width, maximum load area depth and status (e.g. "Being Loaded", "Ready for Delivery", "On Delivery", "In Service").  

To make a purchase through Toy's for You to home delivery, it is necessary to sign up and create a customer account. Information kept about a customer includes customer id, first name, last name, phone, email and address (1 line of address, suburb and state (postcode is obtained from these). For a delivery to be made a customer must have a paid invoice for the goods in question. An invoice is made up of a header record containing invoice id, date, customer id and payment status (D/F). Each line item of the invoice contains a product id, product description and sold price. Other information kept on the product includes product type code (e.g. "Doll", "Bicycle", "Toy Train"). After a customer has ordered and successfully paid for their item/s, their invoice is complete, and one or more delivery requests are created. The delivery request is made up of one or more of the items on the invoice. For example, Mr. Adam may have paid for 2 Trains and Two Bicycle. These would be recorded as three separate line items on the invoice. He may want one Train to be delivered to his nephew who lives at an address in Sydney, and the other three items, Bicycle and two trains delivered to his home address in Queensland. The Train delivery to his nephew would be allocated to and handled by the Sydney warehouse and the Victoria warehouse would be allocated and handle the delivery to his address. The delivery request has a unique id, request date, requested delivery date, actual delivery date, delivery address (1 lines of address, suburb and state (postcode is obtained from these)), contact name, contact phone number, delivery warehouse id and delivery instructions. For the purposes of obtaining postcodes, a record is kept of the postcode attached to the suburb and state. 

Answer rating: 100% (QA)

To create an EntityRelationship ER diagram for the given case study and ensure it conforms to 3rd Normal Form well break down the requirements step by step and design the ER diagram accordingly Assump... View the full answer