After modifying your erwin erd, modify your sql server database (erwin forward engineer) to include
the functionality described in deliverable 1. be sure to name your improved database using this syntax:
lastname-f-p4. be sure to include both the data file (mdf) and the lock file (ldf).
once you have modified your database, you must then populate it with data using your insert into
statements; however, you will need to modify your insert into statements for your product entity
since it no longer has the unitprice attribute.
after populating all of the other tables in your improved database, you will then need to populate the
price_history entity by using insert into statements.
use the previous unit price value with price valid from 1/1/2017 until 3/31/2017.
then from 4/1/2017 until 5/20/2017, increase each unit price by 4% from the previous price.
then from 5/21/2017 until 5/29/2017, increase each unit price by 5% from the previous price.
then from 5/30/2017 until 7/07/2017 decrease each unit price by 10% from the previous price.
then from 7/08/2017 until 11/25/2017, increase each unit price by 10% from the previous price.
then from 11/26/2017 until 12/31/2018, increase each unit price by 4% from the previous price.
populate your tables with the correct data.
here are a list of the entities and attributes for the statements
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
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
price_history_t
productid(pk), (fk) of product_t
startdate (pk)
enddate
unitprice