INFSCI 2710 – Database Systems – Spring 2023 Homework 1
Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: daixieit
INFSCI 2710 – Database Systems – Spring 2023
Homework 1 – Introduction to SQL
Homework Instructions
1. Connect to the database created for you by your instructor - you should have received your connection instructions and credentials last week.
2. Write SQL queries for each of the tasks using MySQL workbench.
3. Follow the underscore convention when naming your database schema, entities, and attributes.
4. Save your work as an SQL script – name your script yourPittID_assignment1.sql.
5. Submit your work via Canvas.
For this assignment, you will need to create tables to store information about a point-of-sale (POS) system.
The POS system must have the following entities (each entity’s logical structure is described below):
customers
|
Field |
Type |
Null |
Key |
|
customer_number |
int(11) |
NO |
PRIMARY |
|
customer_last_name |
varchar(50) |
NO |
|
|
customer_first_name |
varchar(50) |
NO |
|
|
phone |
varchar(50) |
NO |
|
|
address_line_1 |
varchar(50) |
NO |
|
|
address_line_2 |
varchar(50) |
YES |
|
|
city |
varchar(50) |
NO |
|
|
state |
varchar(50) |
YES |
|
|
zip |
varchar(15) |
YES |
|
employees
|
Field |
Type |
Null |
Key |
|
employee_number |
int(11) |
NO |
PRIMARY |
|
last_name |
varchar(50) |
NO |
|
|
first_name |
varchar(50) |
NO |
|
|
extension |
varchar(10) |
NO |
|
|
|
varchar(100) |
NO |
|
|
job_title |
varchar(50) |
NO |
|
products
|
Field |
Type |
Null |
Key |
|
product_code |
varchar(15) |
NO |
PRIMARY |
|
product_name |
varchar(70) |
NO |
|
|
product_vendor |
varchar(150) |
NO |
|
|
product_description |
text |
NO |
|
|
quantity_in_stock |
smallint(6) |
NO |
|
|
buy_price |
double |
NO |
|
|
msrp |
double |
NO |
|
orders
|
Field |
Type |
Null |
Key |
|
order_number |
int(11) |
NO |
PRIMARY |
|
order_date |
date |
NO |
|
|
required_date |
date |
NO |
|
|
shipped_date |
date |
YES |
|
|
status |
varchar(15) |
NO |
|
|
fk_customer_number |
int(11) |
NO |
Foreign key to customers |
|
fk_employee_number |
int(11) |
NO |
Foreign key to employees |
orderdetails
|
Field |
Type |
Null |
Key |
|
fk_order_number |
int(11) |
NO |
● Foreign key to orders ● Multiattribute PRIMARY key in this table in combination with fk_product_code |
|
fk_product_code |
varchar(15) |
NO |
● Foreign key to products ● Multiattribute PRIMARY key in this table in combination with fk_order_number |
|
quantity_ordered |
int(11) |
NO |
|
|
price_each |
double |
NO |
|
Task 1 (50 points): In database [your Pitt username], create the following entity tables. Each table’s logical structure should correspond to the descriptions provided in this assignment. Use the CREATE TABLE statement to create the tables, including referential integrity constraints (primary keys, foreign keys, etc.).
1. customers
2. employees
3. products
4. orders
5. orderdetails
Task 2 (50 points): For each table, insert at least 3 rows using the INSERT statement. You can make up your own data for the INSERT statements. Make sure to pay attention to the order in which you are inserting the data. For example, you must insert a record into customers, employees, and products tables before creating a corresponding record in orders. Similarly, you must have a record in orders before inserting corresponding records in orderdetails.
1. At least 3 customers in the customers table
2. At least 3 employees in the employees table
3. At least 3 products in the products table
4. At least 3 orders in the orders table
5. At least 2 order details for each order in the orderdetails table
2023-02-08
Introduction to SQL