Database Design and Development

Running head: DATABASE DESIGN AND DEVELOPMENT
Database Design and Development
Name of the Student
Name of the University
Author’s note:

DATABASE DESIGN AND DEVELOPMENT1
Table of Contents
Relational Database Design:............................................................................................................2
Data Dictionary:...........................................................................................................................2
Normalization:.............................................................................................................................4
Relational Database Development:..................................................................................................8
System Testing:.............................................................................................................................13
Technical and User Documentation:.............................................................................................14
Technical Documentation:.........................................................................................................14
User Manual:.............................................................................................................................20
Bibliography:.................................................................................................................................21

DATABASE DESIGN AND DEVELOPMENT2
Relational Database Design:
Figure 1: ERD of Fashion Outlet Expansion System
(Source: Created by Author)
Data Dictionary:
Customer
Attribute Data Type Primary Key Foreign Key
custID INT Yes
custName VARCHAR(45)
custContact INT
custEmail VARCHAR(45)
PlacedOrder
Attribute Data Type Primary Key Foreign Key
orderID INT Yes
orderDate DATE

DATABASE DESIGN AND DEVELOPMENT3
orderTotal DECIMAL(12,2)
orderStatus VARCHAR(45)
custID INT EFERENCES
Customer (`custID`)
Items
Attribute Data Type Primary Key Foreign Key
itemID INT Yes
itemName VARCHAR(45)
itemType VARCHAR(45)
availableFor VARCHAR(45)
itemCost DECIMAL(12,2)
OrderItem
Attribute Data Type Primary Key Foreign Key
orderID INT Yes REFERENCES
PlacedOrder
(`orderID`)
itemID INT Yes REFERENCES Items
(`itemID`)
quantity INT

DATABASE DESIGN AND DEVELOPMENT4
Normalization:
The normalization is the process of eliminating data redundancy and anomalies from the
database. As the first normal form, the multivalued attributes has been eliminated from the
database. The second normal was achieved by eliminating the partial dependency. The details of
the order were dependent on the order id. Therefore a new table order was created which had
order as the primary key and reference key of customer id to denote which customer placed the
order. The third normal form was achieved by removing transitive dependency. The purchased
item quantity has transitive dependency on the order id. Therefore a new table was added which
had item and order table reference key and ordered quantity.
1st Normal Form: The first normal form states that the attributes must be atomic. If any
attribute is multivalued attribute then the database table will not be in first normal form. Now the
following table is not in first normal form.
custID custName orderID orderDate orderTotal itemID itemName itemCost
1 James 1 2019-05-
09
370 1 Suit 300
2 T-Shirt 70
2 2019-06-
09
480 1 Suit 300
3 Pant 180
2 Dino 3 2019-05-
09
370 1 Suit 300
2 T-Shirt 70
4 2019-06-
09
480 1 Suit 300
3 Pant 180

DATABASE DESIGN AND DEVELOPMENT5
As seen in the above table, the attributes other than client attributes are multivalued
attributes. The order attributes have two values for each customer and 4 item attributes are for
each customer. If the above table is to be converted into the first normal form database then
following table is created.
custID custName orderID orderDate orderTotal itemID itemName itemCost
1 James 1 2019-05-
09
370 1 Suit 300
1 James 1 2019-05-
09
370 2 T-Shirt 70
1 James 2 2019-06-
09
480 1 Suit 300
1 James 2 2019-06-
09
480 3 Pant 180
2 Dino 3 2019-05-
09
370 1 Suit 300
2 Dino 3 2019-05-
09
370 2 T-Shirt 70
2 Dino 4 2019-06-
09
480 1 Suit 300
2 Dino 4 2019-06-
09
480 3 Pant 180