Task 1: Update Your Logical Database Design from Project 1

First, make sure your ERwin data model from Project 1 is a logical/physical data model that uses MS SQL Server as the database. If you have created a logical only data model or pointed to another type of database in Project 1, you will need to create a new logical/physical model that uses SQL Server as the database, copy and paste everything from your previous model.

Next, update your ERwin data model based on instructor’s review and feedback on Project 1. Make sure you have the correct primary keys, foreign keys, data types, relationships, and cardinalities for these tables.

If you don’t specify the data type for each attribute appropriately, you may encounter errors during the forward engineering process because SQL Server will not accept inappropriate data types, such as precision and scales for decimal type of data.  For example, use Decimal (10,2) for prices instead of Decimal (). Similarly, if you don’t specify the field width for each attribute/column as described in the table, data may be truncated during the data populating process.

Task 2. Create Database in SQL Server Using ERwin Forward Engineering

Use ERwin forward engineering function to automatically create the database schema in Microsoft SQL Server. Save the .ere file generated during the process.  Watch the YouTube video (link posted on Blackboard -> Course Documents -> YouTube videos) to learn how to forward engineer ERwin model to SQL Server.

Task 3. Normalize and populate the data

3.1 Normalize the data

Next you need to normalize the data provided in “Project 2 Starting Data Summer 2017” to the 3rd level, and populate them to your MS SQL database tables created from above.

In addition to that the business rules in Project #1, more hints are listed in the following:

Products with the exact same name are considered as the same product. In your normalized data, you will need to create a product ID starting from 1, incremental by 1, for each product that has a unique name. Sort the products by name alphabetically and then assign each a product ID, for example, “Bandages (Box of 1000)” will have product ID 1.

All products that PSC sells are purchased from suppliers. A product can be purchased from multiple suppliers at various costs.

Some products may not have been ordered yet, such as Silicon Spatula.

The Quantity in the “Customer Orders Detail” is the Order Quantity associated with each item ordered by each customer, and should go into ORDER_LINE_T.

The Quantity in the “Product Supply Detail ” is the Supply Quantity that should go into PRODUCT_SUPPLIER_T. Because the same item/product can be purchased from multiple suppliers, so the sum of the quantity you can find for this item/product is the Stock Quantity that should go into PRODUCT_T.

The Item Price in the “Customer Orders Detail” is the Unit Price the product is sold for, and should go into PRODUCT_T.  

The Cost in the “Supplier Purchasing Details” is the Supply Unit Cost and should go into PRODUCT_SUPPLIER_T.

PSC decided that one product can be in one and only one category, while one category contains one or more products.

Lastly, do not forget that in “Customer Orders Detail”, “Customer 3876 wants to add 1 snow mobile for $5,400 to their order 1530. Please populate the database with this new information as well.”

3.2 Populate the data in MS SQL Server using SQL

When you populate the data, be aware that existing integrity constraints will force you to enter data in certain orders. For example, customer ID is required in Order_T so you cannot populate the orders before you populate the customers. So a good approach is to write down the order you populate the tables with data following integrity constraints. For example,

Get a Unique Answer HERE or Order a Similar Paper

Get a Unique Answer HERE or Order a Similar Paper

Our website has a team of professional writers who can help you write any of your homework. They will write your papers from scratch. We also have a team of editors just to make sure all papers are of HIGH QUALITY & PLAGIARISM FREE. To make an Order you only need to click Ask A Question and we will direct you to our Order Page at WriteDemy. Then fill Our Order Form with all your assignment instructions. Select your deadline and pay for your paper. You will get it few hours before your set deadline.