CST 4301 Assignment 2
Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: daixieit
CST 4301
Assignment 2
Optima Furniture Company used to work with the above form for their sales. Hundreds of these forms are filled weekly to control the sales of this company. The company knew you have
finished the CST4301 Database (DB) course and hired you as Data Base designer to make their life easier; they asked you to create a DB system to control all sales by moving these forms into a relational DB system.
Customers can order many items as shown in the form. Normally the company sells 100’s of
items designated by their item No. The sale is assigned to one salesperson designated by his/her own code (salesperson can have many sales order). Each sale order is designated by the Sale
Order Number.
Your first steps to do this job will be:
Step1: Cluster the fields given in the form into tables
Step2: Create a Primary Key (PK) for each designed table
Step3: Connect these table into a relational DB
Step4: You might need extra table to join all designed tables
Step5: Summarize the above steps above (1,2,3,4) into ERD (Entity Relationship Diagram)
Q1: Create the tables and deploy the tables with the data derived from the forms. Underline the primary keys in the created tables:
Q2. Draw the Entity Relationship Diagram (ERD) showing the fields and the PKs. Underline the PKs in the tables. Connect the tables to show their relationship in the Database. The table in the ERD includes just the field names not the data.
To make sure the designed ERD is correct, write an SQL Statement for the following queries:
Q3: The Company wants to calculate the total sales for each salesperson. Write an SQL
statement to find the total sales by each salesperson. Display the Salesperson First and last names and the total sales. Sort the results by First name.
Sample of answer could like the following:
Q4. Find the information of the customers who dealt with sale person Adam Smith whose SP_Code is “A_ 12”:
Sample answer:
2023-11-20