CIS 3050 – Project 3 Version 21F – Rev. A
This project consists of three sections. Be sure to number the pages in your report.
Deliverable 1. Table Creation
Using ERwin and Microsoft SQL Server, create the tables and populate them with data. The source of
your data will be the Microsoft Excel spreadsheet provided to you on the Blackboard assignment. You
should realize that the spreadsheet is poorly designed, and you will need to normalize the data to
populate your tables.
A. Use ERwin to create the tables, and then forward engineer them to automatically generate the
schema in SQL Server. Include your .erwin file with your deliverables, as well as your SQL database.
NOTE: your database and ERwin files must be named using the syntax of LASTNAMEF-P3 (where F is
your first initial and P3 represents Project 3). Reminder: To obtain your SQL database file, first make sure
that there are no open connections to your SQL database (either your Query Session or ERwin) , then
right-click on your database and select the detach menu option. Detaching your database will produce
two output files, ( .mdf & .ldf ). Both are necessary to attach a database, so be sure to include both.
Here is list of the entities for this project:
SalesRepID (FK) of EMPLOYEE_T
CustomerID (FK) of CUSTOMER_T
CustomerPONumber (This means the Customer Purchase Order Number)
OrderID composite (PK), (FK) of ORDER_T
ProductID composite (PK), (FK) of PRODUCT_T
CIS 3050 – Project 3
StockQuantity this is the amount of the product we have in our warehouse. It is not
provided in the data, so make up an arbitrary amount.
ProductID composite (PK), (FK) of PRODUCT_T.ProductID
PartID composite(PK), (FK) of PRODUCT_T.ProductID
PartQuantity quantity of PartID that goes into ProductID (example, 2 tires
In one bicycle)
ProducID composite (PK), (FK) of PRODUCT_T
SupplierID composite (PK), (FK) of SUPPLIER_T
VendorPartID this is the ID the Vendor (i.e., Supplier) uses in their system), similar to
CustomerPONumber in the ORDER_t.
ProductCost this is the amount we paid the Vendor for the product
ManagerID (FK) unary
EmployeeID composite (PK), (FK) of EMPLOYEE_T
CourseID composite (PK), (FK) of COURSE_T
CIS 3050 – Project 3
EmployeeID (PK), (FK) of EMPLOYEE_T
CommissionRate (hint: only employees with commission rates should be added to this
B. Be sure to include the appropriate integrity constraints and the proper data types and field widths.
C. Populate the tables using SQL statements within SQL Server. Include a screenshot of successfully
populating at least one row in each table with your deliverables. Provide the results of the INSERT INTO
screenshots with one table per page.
I strongly suggest that you save all of your INSERT INTO statements in a text file. By doing this, if you
ever need to rebuild your database, it will not take long to add your data. I suspect that you will wish
that you have this text file when you work on Project 3 in the future.
Deliverable 2. SQL Statements
Each question within deliverable 2 must begin on a new page and be sure to document the question as
the title of each item at the top of each page. Also, using a 12-point font, include the SQL statement and
then provide a screen shot of each query. The screen shots must include both the SQL statement and
the results for each item below based on the data entered in task 1. The screen shots must be large
enough for the instructor to clearly read the results without a magnifying glass! Caution: Read the
instructions carefully! Each question is based on a single SQL statement, and the single SQL statement
might contain sub-queries (additional SELECT statements) within the statement.
A. Provide a list all of the Customer ID, Customer Names, and States and sort the list in alphabetical
order by Customer Name.
B. Provide a list of all of the Customer ID, Customer Names, and City, and sort the list by city with the
Customer Names in alphabetical order within each city.
C. List the customers showing the Customer ID, Customer Name, address, and sales rep name in
alphabetical order by customer name
D. Which employees have not completed course ID = 100? Hint: name of employee only, and the
best way to determine this is by having a subselect statement to determine the EmployeeIDs that
have completed CourseID 100, and then have a the select statement use the output of the subselect
to determine which of all of the employees are not in the list provided by the subselect.
E. How many sales reps does PSC have? Hint: I want to know how many, not who they are. Also, realize
that all sales reps are employees, but not all employees are sales reps. Also, keep in mind that being a
sales rep does not mean that they have actually sold anything.
F. List all of the sales reps sorted by largest commission rate first Hint: name and sales commission rate
G. Who are the manager(s) of the sales reps? Hint: name of the manager only.
H. List the employee’s names who report to a Sales Manager. Hint: Your SQL statement will need to
determine the manager first before it can determine the employees that report to him/her.
I. Who is the manager of the manager of the sales reps? Hint: Show the name of the sales rep’s
manager’s manager only, and your single SQL statement will need to determine the sales rep’s manager
before it can determine the manager of the sales rep’s manager.
J. List the employee names of those that report directly to the manager of the sales manager(s). Hint:
Your SQL statement must determine the sales manager before it can determine the manager of the sales
rep’s manager, and then it must determine the names of those that report to the manager of the sales
K. Provide an inventory report that lists the most costly items first. The inventory report should include
product identification numbers, product descriptions, unit prices, supplier names, cost, and quantity
supplied. Hint: the most costly item is the one in which the product of cost and quantity yields the
largest value. Be careful not to confuse cost with price. Price is the value that the products are sold to
the customers, and cost is the value that is paid to purchase the products from the suppliers. Also, be
aware that the word “product” above refers to the result of multiplication (i.e., the product of cost and
L. List all of the employees in alphabetical order and each course they have completed in order of date
completed. Hint: some employees might not have taken any courses.
Statement of academic integrity
Include the statement of academic integrity that was provided to you on Blackboard for.
Submission of project
The assignment is due before the end of the day on the due date, as documented within the syllabus.
You must submit your .docx, .erwin, .mdf, and .ldf files to the Blackboard assignment. Be sure to follow
all of the instructions included with the course syllabus regarding submitting projects. Late work will not