Database Managemanet

Instruction: This assignment is designed to let you have a hands-on experience writing SQL statements. It includes defining the schema of relations and populating data into each relation. You have to use MySQL to complete this assignment.

To create a script file for defining relations and populating data, you can use any text editor (notepad/notepad++) to write SQL statements and save it with the file extension .sql (i.e., HW2Team1.sql). Name the file as HW2TeamX.sql (where X is your team number). Write every members full name and participation on the first page of this document as follows.

Make sure your SQL script file can be run successfully without any syntax errors in MySQL before submitting it. If it contains any syntax errors, your grade of this assignment will be a ZERO. If your file can be run without error messages, the maximum possible points for each portion are listed below:

- Define Relation 45 points

- Load Data 45 points

- Display Data 10 points

TASKS:

Consider the schemas and instances of following three relations for a Sales Report database:

- Customers (cID: String, cName: String, bDate: Date, phone: String, address: String)

- Products (pNo: String, pName: String, listedPrice: Real, qtyInStock: Integer, deptName: String)

- Sales (cID: String, pNo: String, sDate: Date, salePrice: Real, qty: Integer)

Assume that a customer may have several purchases for the same product at different tunes. There are two foreign keys for this schema:

1. the attribute cID of relation Sales that references relation Customers, and

2. the attribute pNo of relation Sales that references relation Products..

Instances of the relations:

Customers

cID	cName	bDate	phone	address
L003001	Angelina Jolie	1988-11-20	336-266-7777	123 IT St. Greensboro NC
L004002	Harold N. Bornstein	1979-05-02	919-200-9999	456 ET St. Greensboro NC
L004010	Robert Schlossberg	1991-01-01	704-111-0000	789 CT Rd. Charlotte NC
L005001	Katharine Q. Seelye	1992-08-10	980-222-5566	323 BE Rd. Charlotte NC

Products

pNo	pName	listedPrice	qtyInStock	deptName
C0011	Wireless Mouse	9.99	100	Computers
C1002	24" touch screen	400.00	20	Computers
E2005	70" smart TV	1,000.00	5	Entertainment

Transcripts

cID	pNo	sDate	salPrice	qty
L003001	C0011	2018-11-20	8.99	2
L004010	E2005	2018-11-25	600.00	1
L004010	C0011	2018-11-25	8.99	1

Task 1: write SQL statements to define the above three relations. You should choose a suitable data type and/or data size of each column. The chosen domains should be able to hold data in the above instances.

Task 2: write SQL statements to load data into each relation based on given instances. In addition, you are required to come up with at least 2 more records for each table.

Task 3: write a SELECT statement to display the data of all tables.