5915 Database Design Assessment Answer

Unit Name: DATABASE DESIGN

Marks for assessment: 

Instructions for Students

1. This take-home assessment is worth 100 Marks and of the total marks of this unit. 
2. Attempt to answer ALL THE QUESTIONS. 
3. Write your answers to this word document and provide an image for ERD and uploaded this to Canvas in MS Word or pdf format. Please ensure that you upload the correct file format to canvas and it’s readable to the assessors. 
4. This is an open book assessment which means you are permitted to use any materials handed out in class, your notes from the course, the textbook, and the internet. The materials from any other sources should be properly quoted and referenced.
5. Use in-text reference where appropriate and follow Harvard style. Include the bibliography at the end.
6. This is an individual assignment. The assessment must be taken completely alone. Showing it or discussing it with anyone is forbidden. 
7. You must achieve at least 50% of the available marks in the Take Home Assessment to pass this unit.

Extensions

Students can apply for an extension to the submission due date for an assessment item due to extenuating, evidenced circumstances (specific details are found in the Assessment Procedures). Please see the unit outlines for full details.

An Assignment Extension form is available from the Student Forms page.
Part 1. General questions. [20 marks]

1. Describe the 3-layer ANSI-SPARC architecture. Describe two advantages of this architecture. How does this architecture address the issue of data independence for a database system? (8 marks)
2. Why do we use normalization? How does normalization assist in reducing redundancies on a designed database? (6 marks)
3. How does a data dictionary can assist you in implementing your database? Describe two benefits of a data dictionary when implementing a database? Describe one problem that you may encounter if you do not have a data dictionary for a database when implementing that database? (6 marks)

Part 2. Normalization [20 marks total]

The following below displays the details of driving class, client, and vehicle.

Class (ClassID, DateAndTime, ClientFirstName, CientLastName, Sex, DOB, TelPhone, Email)

Vehicle (RegistrationNo, colour, model, make, DealerName, DealerTel, Dealerfax, DealerAddress, ServiceDate, ServiceDescription)

1. Provide two examples to show the insertion, deletion and update anomalies of the entities shown above. (5 marks)
2. Explain why the entities above are not in 3NF? (5 marks)
3. Convert the entities above into Third Normal Form (3NF). Create a data dictionary for your design. (10 marks)

Part 3. Database design. [60 marks]

Please read the case study and answer the questions below.  

The Abidas Sports Company (ASC) provides online shopping. ASC requires all clients to register at first at ASC. The details stored about clients are client number, first and last name, postal address, home address (street number, street name, suburb, post-code, city, state) and gender. ASC offers several types of products. These are namely Clothing, Shoes, Accessories. The clients need to make an order before they purchase any products on the ASC website. The details, of an order includes client number, product number, quality, order date and time. The client will pay for their orders. The payment details are: issue date, client name, amount paid, product number, product name, product quantity. The client receives an invoice after they make a payment for their orders. The details of the invoice include invoice number, date, time, amount paid, credit card number, expiry date, CCV number of the credit card, client number. Clients can purchase products that they require at the ASC store or they can purchase the product using ASC online ordering system which allows for the products ordered to be delivered to the client address. The details of items that are delivered to clients are stored. These details are included delivery number, delivery fee, client name, client address, the company name that delivery and products name. The delivery service company details are stored in the ASC database. These details include company name, company address, fax and phone number. If a client purchases products online then the clients also can collect the products they require at ASC store. The details of the online collection include customer number, order details, date, time, store name, the staff who serve this collection. The client also can make a payment for the products that the client wishes to purchase at the store. The details of all products purchased are stored. These details are client name, store name, store address (street number, street name, suburb, post-code, city, state), staff who processed the sale, date and time of sale, product name and product price.

ASC also has many stores that distribute in Australia. The stores' information is stored in the ASC database. The information about each store includes the store name, location, telephone number, fax number and store number. Each store has its inventory record. The inventory details are product name, size, gender, quality, storage location (eg Bay A, Bay B, Bay C in the store). Staff can order to the ASC main warehouse to send products to them if some products have low stock inventory. Once product ordered by ASC staff is received to an ASC store, the Staff at the ASC store save the details of products received from ASC main warehouse. The details of products received include product name, colour, size, quality, ASC warehouse address, fax and telephone number, the staff name who receive product, date and time, ASC store name and address that received the products from ASC main warehouse. Each store records its sales every day. The details of sales at each ASC store include store name, store number, address, total of sale, date, time. 

For each staff member the following data is stored in ASC database system: staff first and last name, staff number, position, salary, gender, date of birth, name of the stores he/she works in, internal telephone number and store number. The clients can browse the ASC website to find out the details of each store. If a customer finds some issues with products that they purchased, he/she then contact ASC at the following telephone number: 1800 800900. The staff at ASC once notified will recode the problem. The detail of the records includes the staff name, product name, colour, problem description, customer name, invoice number. 

Clients can register on ASC website, can join ASC VIP loyalty membership to earn point when they purchase products from ASC. A client who is a member of the ASC VIP loyalty membership program will receive a point for each product they purchase from ASC. VIP loyalty points for each client is stored. The details stored about VIP loyalty points for each client is client number, points earned, the amount paid, payment date, amount payment.  

Part 3.1 (42 marks):

1. Identify and list all entities, attributes, primary and foreign keys of the scenario given above. Make sure that your data model is normalized to 3NF. (25 marks)
2. Draw an E-R diagram (including entity name, multiplicity of each relationship) based on the entities you identified above. (12 marks)
3. Provide all assumptions that you made in your design. (5 marks)

Part 3.2 SQL [18 marks]

According to entity list above, write the following queries using SQL:

1. How many stores are in Canberra? (3 marks)

1. List the detail of clients who collected products from ASC on 2^nd of May 2020. (3 marks)
2. List details of all staff who work at Canberra ASC store. (3 marks)
3. Give all product a 30% discount. (3 marks)
4. Display the numbers of shoes in Canberra ASC store. (3 marks)

6. Display the first and last name of the customers who have put an order on 10 of May 2021 with an order price less than $50. (3 marks)