Introduction

PCworld is a retail startup that sell computers and accessories to customers. In this coursework, you are asked to create a fine, organized database for PCworld to help manage the information of their products and keep records of customer details as well as their orders. You will also be asked to write a few queries based on your database design.

Part 1: Database Design

The retail stores PCworld currently sell three kinds of product: PC, laptop and printer, each of which should has a unique model id. Each PC should have its attributes such as speed, RAM, HD, and price; Each laptop is described with speed, RAM, HD, screen size and price; Each printer should have its colour, type and price. All products should have a unique 4-character-long Id code and their makers.                                                                                                                                Before being able to make purchases, a customer needs to register an account with their first name, last name, city, address, and email. A customer can buy multiple products and use different payment methods, such as visa debit/credit, cash, etc. and will be assigned a 10-character-long ID code. The system should keep records of information of all orders, including customer id, product model, quantity, date of purchase, total price, and payment type.                                                      After purchasing products, the customer will receive an invoice with the total cost. The information on invoices should also be stored in the database. You need to decide what to include in your database.

Task 1: For the database design, make an ER diagram. The design must be in the third normal

form (3NF) and has no M:M relationships. You can make some assumptions about the data if they are not specified in the requirements. (50 marks)

Task 2: Write the SQL script containing all CREATE TABLE statements that create the tables. Your SQL script and your ER diagram must match (attributes = columns, entities = tables, 1:m relationships = foreign keys). (10 marks) 

Part 2: Query Task

Please write down queries for the following tasks and save them in a single SQL file. You should also indicate the question number correctly in the comments of your script. Failing to do so will lead to reduced or zero marks. (40 marks)

1.    List the makers that make at least two different models of PC.

2.    List the maker(s) of the laptop(s) with the highest available speed.

3.    List the cities with customers who bought a printer.

4.    List the makers of PCs that don't make any laptop or printer.

5.    List all laptop model numbers and only for those made by makers ‘A’ and ‘B’ list also their price. The prices of laptops not made by either A or B should be NULL.

6.    Find the makers who make exactly three different PC models. List all such makers, and for each of them, list also the average speed of the three PC models they make.

7.    List the makers who make at least two different computers (PCs or laptops) with the speed of at least 2.80.

8.    Find the dates on which the shop made total sales (money paid for all products sold on the date) of at least 1,000 pounds. List these dates and for each date list also the total quantity of products sold.