Creation of a variety of tables

69 views 8:28 am 0 Comments February 28, 2023

DTSC660: Data and Database Management with SQL
Module 1
Assignment 1
Purpose
For this assignment, we will simulate the creation of a variety of tables with various attributes.
The key to a successful grade on this assignment is the appropriate structure and data types for
each attribute. Make sure you pay attention to the information given and choose the data type
that BEST meets the client requirements and provides for data validation.
Submission
You will submit a total of 2 sql files. Each file must use the postgres standards taught in the
course. Use of other flavors of SQL such as T-SQL will result in an automatic 0 for the
assignment.
● File 1: You must submit a SQL document called <LastName>_Assignment1_APU. This
document must include ALL commands required to create the database and tables for
the client Auto Parts Unlimited.
● File 2: You must submit a SQL document called <LastName>_Assignment1_HG. This
document must include ALL commands required to create the database and tables for
the client Holy Grounds
● You will submit both files to the Assignment 1 folder. Failure to include both files will
result in loss of credit for any missing information as specified in the rubric.
Instructions
As in the practice assignment, you will be working for a company that creates and maintains
databases for small business customers. The company has taken on two new clients that you
will need to set up with their own databases. Note that since these are two separate databases,
you ARE allowed to repeat the names of tables and attributes (for example, both companies
have an inventory table. Both tables can be called inventory since they are in different
databases).
PART 1 (File 1)
The first client is a local auto parts company called “Auto Parts Unlimited”. Complete the
following steps for this client:
1. Create a database called “auto_parts_unlimited”
2. Connect to the database
3. Create the following 3 tables based on the client’s requirements:
a. Customers: Each customer has the following:
i. A unique numeric ID assigned automatically when their record is created
(for this assignment you do not need to add a constraint to enforce
uniqueness)

ii. First name
iii. Last name
iv. Phone number
v.
vi.
vii.
viii. 		Email
Street Address: Includes street number and name
Zip: 5 Digit zip code
State: Must be a two letter abbreviation, does NOT have to be from a
prepopulated list

b. Employees:

i. A unique numeric ID assigned when an employee is hired (for this
assignment you do not need to add a constraint to enforce uniqueness)
The same attributes as a customer plus:
ii.

1. A hire date
2. A salary amount
3. A probationary status: Can only be one of two values. Either an
employee is still probationary (less than 1 year working for the
company) or not.
c. Parts Inventory:
i. A unique alphanumeric part number (for this assignment you do not need
to add a constraint to enforce uniqueness)

ii. A manufacturer name
iii. A quantity in stock
iv.
v. 		A price
A backordered status: Only two values, either an item is backordered or
not.

***************************ASSIGNMENT CONTINUED ON NEXT PAGE****************************
Part 2 (File 2)
The second client is a local coffee shop / bakery called “Holy Grounds”. Complete the following
steps for this client:
1. Create a database called “holy_grounds”
2. Connect to the database
3. Create the following tables:
a. Coffee Inventory: represents all items in inventory.

i. Unique numeric SKU (for this assignment you do not need to add a
constraint to enforce uniqueness)
Name
Type of roast: Dark, Medium, Light ****(see note below)
Pounds on Hand: Total pounds of coffee on hand (can be fractional)
ii.
iii.
iv.

v. Organic: Only 2 values, either organic or not
vi. Low Acid: Only 2 values, either low acid or not
vii. Price per lb
b. Sales Transaction: represents either an online or in-store purchase
i. Unique receipt ID: While this is a smaller company, they do have multiple
locations and online sales. They complete a total of approximately 250
million transactions a year between these different revenue streams.
Make sure you choose a data type that represents whole values with
plenty of room to expand as this client likes to keep receipt records for
multiple years. This must be a positive number. (for this assignment you
do not need to add a constraint to enforce uniqueness)

ii.
iii.
iv. 		Sale amount
Sale type: Online or In-Store****(see note below)
Transaction Date

c. Store: represents a physical location where coffee is sold
i. Store ID: a unique numeric identifier assigned when each store is
launched (for this assignment you do not need to add a constraint to
enforce uniqueness)

ii. Store Manager: First and last name of store manager. Make sure this
value is long enough to include both first and last name with a space in
between.
Store phone number
Street address: Street number and name
City
Zip: 5 Digit zip code
State: Must be a two letter abbreviation, does NOT have to be from a
prepopulated list
iii.
iv.
v.
vi.
vii.

****For type of roast and sale type, if you are able to create these attributes such that data is
restricted to the specific values indicated in the description (for example Online or In-Store for
Sale Type), you will get a bonus 5 points added to your final grade (If you achieve a perfect
score, the bonus points will still be awarded. So the maximum score is actually a 105).
*******************************GRADING RUBRIC ON NEXT PAGE*********************************
This assignment will be graded on the following rubric:

Item Points
Database Creation Statements (2) 10
Table Creation Statements (6) 10
Below are the specific point values for attributes by tables. Note that credit is awarded for
appropriate definition and data type. Failure to use a data type that does not meet the client
requirements and meet data validation needs will result in loss of points for that attribute.
Some attributes are weighted more than others because of their difficulty.
Auto Parts Unlimited
Customers: Customer ID 5
Employees: Employee ID 5
Employees: Hire Date 5
Employees: Probationary Status 5
Parts Inventory: Price 5
Holy Grounds
Coffee Inventory: Type of Roast 5
Coffee Inventory: Pounds on Hand 5
Sales Transaction: Receipt ID 5
Store: Store Manager Name 5
ALL OTHER ATTRIBUTES (2 points each) 35
Total 100
Bonus 5
Max 105