Case Study - Furniture Deliveries System

A wholesaler to home delivery, sells furniture's for example: bedrooms, mattresses and dinning tables and other home appliances 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 wholesaler to home delivery have distribution warehouses in most of the Australian states (the New South Wales Australia warehouse looks after Sydney warehouses and Canberra, and the warehouse in Victoria looks after Melbourne, Queensland and Adelaide). 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, state, and postcode), email address, phone number and other information is also kept about area of the warehouse (in square meters), number of loading bays. 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 Assistants. 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 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. 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 e.g. 5 tonnes), 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 wholesaler 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, state, and postcode). 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., "Bed", "Mattress", "Dinning"). 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. Alan may have paid for 2 Matrasses and one dining table. These would be recorded as three separate line items on the invoice. He may want one Mattress to be delivered to his Mother who lives at an address in Sydney, and the other two items, Mattress and dinning table delivered to his home address in Queensland. The Mattress delivery to his Mother would be allocated to and handled by the Sydney warehouse and the Vitoria 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 ,state, and postcode), 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. Detailed Requirements This assignment is an individual assignment. It is a requirement of this assignment that students use XAMPP or Microsoft SQL for the database components. Students should submit a report that follows the format of a business/professional report and contain, at a minimum, a Title Page, Table of Contents, Executive Summary and References (if cited) and the following content:

1. An ER model of the case study system. This should conform to third normal form. Students should be aware there are a number of disjoint subtype entities. Students are able to use any drawing package to present the ER diagram, but the diagram should use the Crows foot notation and conform to the standards identified in (Michael L. Hernandez, 4th edition). These include that entities are shown in a rectangle with name of entity in grey at top separated from two columns below with PK, FK identifiers, where appropriate in the first column and attributes in second column. Primary key attributes to be separated from other attributes by a line across the rectangle. All entity to be in upper case and attribute names to be in capitalise. All relationships should be labelled and identified as mandatory or optional. All connectivity, participation and cardinalities (if there are specific limits) should be shown.

2. A screen shot of the XAMPP showing the creation of a database with the name

3. The DDL statements required to create an implementation of the conceptual data model above. Appropriate constraints must be created. Students must follow the same naming conventions i.e. lower for keywords, uppercase names for tables and attributes with an underscore between words and new line for each clause. Students should use the default schema i.e. there is no need to create one. .

4. 4. DML statements to insert sufficient (5 rows ) data into the database to correctly to display the inserted data in tables.