SQL Customer and Employees

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 (where F is your first initial). Reminder: To obtain your SQL database file, right-click on your database and select the detach menu option. Detaching your database will produce two output files, a .mdf and a .ldf file. 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 (PK)
CustomerName
CustomerStreet
CustomerCity
CustomerState
CustomerZip
CreditLimit
SalesRepID (FK) of EMPLOYEE_T

ORDER_T
OrderID (PK)
CustomerID (FK) of CUSTOMER_T
CustomerPONumber
OrderDate
DueDate
ShipDate

ORDER_LINE_T
OrderID composite (PK), (FK) of ORDER_T
ProductID composite (PK), (FK) of PRODUCT_T
OrderQuantity

PRODUCT_T
ProductID (PK)
ProductDescription
UnitPrice
StockQuantity

FABRICATED_T
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)

PRODUCT_SUPPLIER_T
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
PurchasedQuantity

SUPPLIER_T
SupplierID (PK)
SupplierName
SupplierStreet
SupplierCity
SupplierState
SupplierZip

EMPLOYEE_T
EmployeeID (PK)
EmployeeName
EmployeeStreet
EmployeeCity
EmployeeState
EmployeeZip
ManagerID (FK) unary

EMPLOYEE_COURSE_T
EmployeeID combination (PK), (FK) of EMPLOYEE_T
CourseID combination (PK), (FK) of COURSE_T
CompletionDate

COURSE_T
CourseID (PK)
CourseDescription

SALES_COMMISSION_RATE_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. C. Populate the tables using SQL statements within SQL Server.

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. Provide screen shots of each query, both the SQL statement(s) 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.

• Provide a list all of the Customer ID, Customer Names, and States and sort the list in alphabetical order by Customer Name.
• 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.
• List the customers showing the Customer ID, Customer Name, address, and sales rep name in alphabetical order by customer name
• 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.
• 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.
• List all of the sales reps sorted by largest commission rate first Hint: name and sales commission rate
• Who is the manager of the sales reps? Hint: name of the sales manger only.
• List the employee names of those that report directly to the sales manager. Hint: Your SQL statement will need to determine the sales manager before it can determine the employees that report to him/her.
• Who is the sales manager’s manager? Hint: name of the sales manager’s manager only, and your single SQL statement will need to determine the sales manager before it can determine the manager of the sales manager.
• 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 manager, and then it must determine the names of those that report to the manager of the sales manager.
• 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.
• List the employees in alphabetical order and each course they have completed in order of date completed