Duncan Nisbett Student ID: #000856539 C170: Data Management - Applications 1 Data Management Project. 100% pass rate

Duncan Nisbett Student ID: #000856539 C170: Data Management - Applications 1 Data Management Project Part A Normalized Model 1a First Normal Form Table Design CREATE TABLE [dbo].[Sales_1NF] ( [SaleId] INT NOT N ...

Duncan Nisbett Student ID: #000856539 C170: Data Management - Applications 1 Data Management Project Part A Normalized Model 1a First Normal Form Table Design CREATE TABLE [dbo].[Sales_1NF] ( [SaleId] INT NOT NULL IDENTITY(1,1), [DonutId] INT NOT NULL, [Name] NVARCHAR(50) NOT NULL, [Description] NVARCHAR(250) NULL, [UnitPrice] MONEY NULL, [Quantity] INT NOT NULL, [SaleDate] DATE NOT NULL, [SpecialHandlingNotes] NVARCHAR(500) NULL, [CustomerId] INT NULL,Duncan Nisbett Student ID: #000856539 C170: Data Management - Applications 2 [CustomerFirstName] NVARCHAR(50) NULL, [CustomerLastName] NVARCHAR(50) NULL, [CustomerStreetAddress1] NVARCHAR(50) NULL, [CustomerStreetAddress2] NVARCHAR(50) NULL, [CustomerCity] NVARCHAR(50) NULL, [CustomerState] NCHAR(2) NULL, [CustomerZip] NCHAR(6) NULL, [CustomerHomePhone] NCHAR(10) NULL, [CustomerMobilePhone] NCHAR(10) NULL, [CustomerOtherPhone] NCHAR(10) NULL, CONSTRAINT [PK_Sales_1NF] PRIMARY KEY ([SaleId],[DonutId]) ) Reasoning I took the Sales form sheet and reviewed the data to break out each individual artifact. The table has been broken up based on the requirements and the unique data points found within the form. From there I used a standard naming convention to give each data point a self describing name like, CustomerFirstName, to make a clear designation on the type of value one could find in the column. Each data point was also examined to determine what type of data it best represented. A whole number such as id or count column was assigned as an integer, any short text string stored as nchar, longer text strings stored as nvarchar, and then money for the unit price. The Primary Key was derived as being the SaleId and DonutId. A composite key with those 2 data point enforces uniqueness for each record. 1b Second Normal Form Table Design CREATE TABLE [dbo].[Product_2NF] ( [ProductId] INT NOT NULL IDENTITY(1,1), [Name] NVARCHAR(50) NOT NULL, [Description] NVARCHAR(250) NOT NULL, [UnitPrice] MONEY NOT NULL, CONSTRAINT [PK_Product_2NF] PRIMARY KEY (ProductId) )Duncan Nisbett Student ID: #000856539 C170: Data Management - Applications 3 CREATE TABLE [dbo].[Sales_2NF] ( [SaleId] INT NOT NULL IDENTITY(1,1), [SaleDate] DATE NOT NULL, [CustomerId] INT NOT NULL, [ProductId] INT NOT NULL, [Quantity] INT NOT NULL, [SpecialHandlingNotes] NVARCHAR(500) NULL, [LastName] NVARCHAR(50) NOT NULL, [FirstName] NVARCHAR(50) NOT NULL, [Address1] NVARCHAR(250) NOT NULL, [Address2] NVARCHAR(250) NULL, [City] NVARCHAR(50) NOT NULL, [State] NCHAR(2) NOT NULL, [Zip] NCHAR(6) NOT NULL, [CustomerHomePhone] NCHAR(10) NULL, [CustomerMobilePhone] NCHAR(10) NULL, [CustomerOtherPhone] NCHAR(10) NULL, CONSTRAINT [PK_Sales_2NF] PRIMARY KEY ([SaleId]), )Duncan Nisbett Student ID: #000856539 C170: Data Management - Applications 4 CREATE TABLE [dbo].[Line_Item_2NF] ( [ProductId] INT NOT NULL, [SaleId] INT NOT NULL, [Quantity] NCHAR(10) NOT NULL, CONSTRAINT [FK_Line_Item_2NF_Sale] FOREIGN KEY ([SaleId]) REFERENCES [Sales_2NF]([SaleId]), CONSTRAINT [FK_Line_Item_2NF_Product] FOREIGN KEY ([ProductId]) REFERENCES [Product_2NF]([ProductId]), CONSTRAINT [PK_Line_Item_2NF] PRIMARY KEY ([SaleId], [ProductId]) ) Reasoning The sales data was broken out to 3 sections. Product data to store the information into each individual item that can be sold. The Sales table tracks information specific to each transaction along with the individual components of the sales data including the customer data. Finally a Line Item table was created to store the information for each item linked to a sale. 2 foreign keys are configured to enforce that a line item must be linked to a valid sale and to a valid product. As the 2 foreign keys also end up creating a unique record the same keys were configured as a composite primary key.Duncan Nisbett Student ID: #000856539 C170: Data Management - Applications 5 1c Third Normal Form Table DesignDuncan Nisbett Student ID: #000856539 C170: Data Management - Applications 6 Reasoning Most of the tables in this form are nearly the same as the second normal form. However, in the third normal form we have added an additional table called Customer. This table breaks out the customer data that pertains to a sale to its own table. Adding this table allows a customer to exist on many orders on the sales table and be linked to a master customer record through the CustomerId column. CustomerId in the Sales table was altered to be a foreign key to enforce valid data. The Line Item table contains a foreign key constraint against the order table to only allow actual order records to be linked to a line item. It also contains a foreign key constraint against the product table to ensure we have only valid products linked to an order. Line Item table also uses a composite primary key utilizing the SaleId and ProductId to enforce unique records. All tables use named primary keys for easier identification than the auto generated key names that SQL uses.Duncan Nisbett Student ID: #000856539 C170: Data Management - Applications 7 Part B Entity Relationship Diagram