CIS 305 – Project 3 Version 19S – Rev A This project consists of three deliverables. Be sure to number the pages in your report.
Deliverable 1. Table Creation
Using ERwin and Microsoft SQL Server, create the tables.
A. Use ERwin to create the tables, 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:
Entities: CUSTOMER_t
CustomerID CustomerName CustomerStreet CustomerCity CustomerState CustomerZip CreditLimit SalesRepID
ORDER_t OrderID
CustomerID
CustomerPONumber (This means the Customer Purchase Order Number) OrderDate
DueDate
ShipDate
ORDER_LINE_t OrderID
ProductID OrderQuantity
PRODUCT_t ProductID
composite (PK), (FK) of ORDER_t composite (PK), (FK) of PRODUCT_t
(PK) ProductDescription
(PK)
(FK) of SALES_REPRESENTATIVE _t
(PK)
(FK) of CUSTOMER_t
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.
Dr. Carlton
PRICE_HISTORY_t ProductID
StartDate EndDate UnitPrice
FABRICATED_t ProductID
PartID PartQuantity
PRODUCT_SUPPLIER_t ProducID
SupplierID VendorPartID
Composite Primary Key Composite Primary Key
composite (PK), (FK) of PRODUCT_t.ProductID
composite (PK), (FK) of PRODUCT_t.ProductID
quantity of PartID that goes into ProductID (example, 2 tires In one bicycle)
composite (PK), (FK) of PRODUCT_t
composite (PK), (FK) of SUPPLIER_t
this is the ID the Vendor (i.e., Supplier) uses in their system), similar to CustomerPONumber in the ORDER_t.
this is the amount we paid the Vendor for the product
ProductCost PurchasedQuantity
SUPPLIER_t
SupplierID (PK)
SupplierName SupplierStreet SupplierCity SupplierState SupplierZip
EMPLOYEE_t
EmployeeID (PK)
EmployeeFirstName EmployeeLastName EmployeeJobTitle EmployeeStreet EmployeeCity EmployeeState EmployeeZip EmployeeHireDate
ManagerID
EMPLOYEE_COURSE_t EmployeeID
(FK)
unary to EmployeeID
CourseID CompletionDate
CIS 305 – Project 3
Version 19S – Rev A
composite (PK), (FK) of EMPLOYEE_t
composite (PK), (FK) of COURSE_t
Dr. Carlton
CIS 305 – Project 3
Version 19S – Rev A
COURSE_T
CourseID (PK)
CourseDescription
SALES_REPRESENTATIVE_T
EmployeeID (PK), (FK) of EMPLOYEE_t
CommissionRate (hint: only employees with commission rates should be added to this table).
B. Be sure to include the appropriate integrity constraints and the proper data types and field widths.
Deliverable 2. Populating Your Database With Data
After you have created your database in deliverable 1, you must now populate it with data using your INSERT INTO statements. I strongly suggest that you save 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. You will create your INSERT INTO statements manually using the data from the Excel spreadsheet that is included with this Blackboard assignment. This is tricky and a bit time- consuming.
The last entity to populate is PRICE_HISTORY_t, and you need to think about this carefully as you create your INSERT INTO statements for this table. Use the following rules:
Use the unit price value from the Excel spreadsheet for prices valid from 1/1/2019 until 3/31/2019. Then from 4/1/2019 until 5/20/2019, increase each unit price by 4% from the previous price.
Then from 5/21/2019 until 5/29/2019, increase each unit price by 5% from the previous price. Then from 5/30/2019 until 7/07/2019, decrease each unit price by 10% from the previous price. Then from 7/08/2019 until 10/22/2019, increase each unit price by 10% from the previous price. Then from 10/23/2019 until 12/31/2019, increase each unit price by 4% from the previous price. Populate your tables with the correct data.
Dr. Carlton
CIS 305 – Project 3 Version 19S – Rev A
Deliverable 3. SQL Statements
Each question within deliverable 3 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.
1. Provide a list all of the Customer ID, Customer Names, and States and sort the list in alphabetical order by Customer Name.
2. Provide a list of all of the Customer ID, Customer Names, and States, and sort the list by state with the Customer Names in alphabetical order within each state.
3. List the customers showing the Customer ID, Customer Name, address, and sales rep name in alphabetical order by customer name
4. Which employees have not completed course ID = 90? 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 90, 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.
5. 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.
6. List all of the sales reps sorted by largest commission rate first Hint: name and sales commission rate 7. Who is the manager of the sales reps? Hint: name of the manger only.
8. List the employee names of those that report directly to the manager of the sales reps. Hint: Your SQL statement will need to determine the manager first before it can determine the employees that report to him/her.
9. 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.
10. List the employee names of those that report directly to the manager of the sales manager. Hint: Your SQL statement must determine the sales manger before it can determine manager of the sales rep’s manager, and then it must determine the names of those that report to the manager of the sales rep’s manager.
Dr. Carlton
CIS 305 – Project 3 Version 19S – Rev A
11. 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 quantity).
12. 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.
13. List all of the products provided by each supplier in alphabetical order with the least expensive products shown first for each supplier. Hint: Be sure to include the Product ID, Product Description, Supplier ID, Supplier Name, Vendor Part #, and Cost.
14. Which sales representative has the highest dollar amount in total sales? Hint: Provide the name and the total sales amount for that rep only. Do not include the information for other people, and do not list details about orders.
15. List all of the sales reps and their total commission amounts in order of highest sales commission dollar amount first. Hint: Provide all of sales reps’ names and their total commission dollar amounts – imagine that you were going to write one check to each sales rep to pay their total commission amount, so be sure to produce that dollar amount. Extra Hint: Not all of the sales reps have sold any orders.
16. Which sales representative has sold the most products? Hint: Name of the sales rep and a number representing the total number of distinct products sold by that rep. This is the number of different products they have sold, not the quantity of any products.
17. Which sales representative has generated the most profit for the company? Hint: Provide the name of the sales rep and the total profit amount only. This one SQL statement will have subqueries. You must determine the profitability of a product by subtracting its cost from its selling price and multiplying the difference by the quantity sold. Regarding the cost, many products are supplied by more than one suppler, and each supplier may provide a different cost, so when this occurs, use the least expensive cost in measuring your profitability.
18. Provide a listing of all of the details for each order. The report must be organized by sales rep, then alphabetically by each customer for that sales rep, and then by each Order ID for that customer. The listing must include the Sales Rep’s name, Customer Name, Order ID, Order Date, Due Date, Product ID, Product Description, Quantity Purchased, and Unit Price.
19. List the suppliers that provide hack saws, and sort the list with the lowest prices first. Hint: Your SQL statement must look for a “Hack Saw”, and not some ProductID or PartID. Include the Supplier IDs, Supplier Names, Vendor Part #s, and cost.
20. Show the components and cost of materials for fabricating a hack saw. Hint: Your SQL statement must look for a “Hack Saw”, and not some ProductID or PartID. Include the ProductID (really PartID), Product Description, and Quantity Used for each component of a hack saw.
Dr. Carlton
CIS 305 – Project 3 Version 19S – Rev A 21. Which customer purchased the overall largest dollar amount? Hint: One name and the total dollar
amount only – the result should be one record only
22. List the ProductIDs, product descriptions, and unit prices that are valid on July 14, 2019 for all products sorted by ProductID. Hint: Search for prices valid on ’19-JUL-2019’
Statement of academic integrity
Include the statement of academic integrity that was provided to you on Blackboard for.
Submission of project
Deliver the hard copy project report to the instructor’s workstation at the beginning of the class session on the date indicated on the class schedule found on the last page of the course syllabus. Deliver the electronic data portions of the project by e-mail to the instructor before the end of the day on the due date, as documented within the syllabus. You must submit your .erwin, .mdf, and .ldf files within a single e-mail message. Be sure to follow all of the instructions included with the course syllabus regarding submitting projects. Late work will not be accepted.
Dr. Carlton