Unit Name: DATABASE DESIGN
Marks for assessment:
Instructions for Students
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]
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)
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):
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)
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)
For solution connect with our online professionals.