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
...
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]),
[Show More]
