Database Design and Development

This document provides an overview of database design and development, including topics such as normalization, relational database development, system testing, and technical and user documentation.

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

1DATABASE DESIGN AND DEVELOPMENT
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

2DATABASE DESIGN AND DEVELOPMENT
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

3DATABASE DESIGN AND DEVELOPMENT
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

4DATABASE DESIGN AND DEVELOPMENT
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

5DATABASE DESIGN AND DEVELOPMENT
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

6DATABASE DESIGN AND DEVELOPMENT
The above table is completely in first normal form as there is no multivalued attribute
now. Each attribute in the table is atomic.
2ND Normal Form: The table will be in second normal form is the attributes are only
dependent on the primary key. The primary key are the values that distinguish the row in a
database table uniquely. The custName, custContact and custEmail are dependent on the custID
which is the primary key of the table. On the other hand, orderDate, orderTotal and orderStatus
are partially dependent on the orderID. The orderID is not the primary key of the table. This
partial dependency does not allow the table to be in second normal form. In order to make the
table in second normal form, the table is divided into two tables.
custID custName custContact custEmail
1 James 12345678 [e-mail]
2 Dino 987654321 [e-mail]
orderID orderDate orderTotal itemID itemName itemCost
1 2019-05-09 370 1 Suit 300
1 2019-05-09 370 2 T-Shirt 70
2 2019-06-09 480 1 Suit 300
2 2019-06-09 480 3 Pant 180
3 2019-05-09 370 1 Suit 300
3 2019-05-09 370 2 T-Shirt 70
4 2019-06-09 480 1 Suit 300
4 2019-06-09 480 3 Pant 180

7DATABASE DESIGN AND DEVELOPMENT
However, the anomalies still remains in the database tables as there another partial
dependency in the database table. The itemName, itemType, availableFor and itemCost are
dependent on the itemID. Now one more table is created from the above table.
orderID orderDate orderTotal itemID quantity
1 2019-05-09 370 1 1
1 2019-05-09 370 2 1
2 2019-06-09 480 1 1
2 2019-06-09 480 3 1
3 2019-05-09 370 1 1
3 2019-05-09 370 2 1
4 2019-06-09 480 1 1
4 2019-06-09 480 3 1
itemID itemName itemCost
1 Suit 300
2 T-Shirt 70
3 Pant 180
3RD Normal Form: In order to be in third normal form, the database table needs to be
free from any transitive dependency. The orderID, itemID and quantity is in transitive
dependency. That is why a new table is introduced, in the database to make the whole database
in third normal form.

8DATABASE DESIGN AND DEVELOPMENT
orderID itemID quantity
1 1 1
1 2 1
2 1 1
2 3 1
3 1 1
3 2 1
4 1 1
4 3 1
Relational Database Development:
Structured Query Language:
CREATE TABLE IF NOT EXISTS `mydb`.`Customer` (
`custID` INT NOT NULL,
`custName` VARCHAR(45) NULL,
`custContact` INT NULL,
`custEmail` VARCHAR(45) NULL,
PRIMARY KEY (`custID`));
-- -----------------------------------------------------
-- Table `mydb`.`PlacedOrder`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`PlacedOrder` (
`orderID` INT NOT NULL,

9DATABASE DESIGN AND DEVELOPMENT
`orderDate` DATE NULL,
`orderTotal` DECIMAL(12,2) NULL,
`orderStatus` VARCHAR(45) NULL,
`custID` INT NOT NULL,
PRIMARY KEY (`orderID`),
CONSTRAINT `fk_PlacedOrder_Customer` FOREIGN KEY (`custID`)
REFERENCES `mydb`.`Customer` (`custID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION);
-- -----------------------------------------------------
-- Table `mydb`.`Items`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`Items` (
`itemID` INT NOT NULL,
`itemName` VARCHAR(45) NULL,
`itemType` VARCHAR(45) NULL,
`availableFor` VARCHAR(45) NULL,
`itemCost` DECIMAL(12,2) NULL,
PRIMARY KEY (`itemID`));
-- -----------------------------------------------------
-- Table `mydb`.`OrderItem`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`OrderItem` (
`orderID` INT NOT NULL,
`itemID` INT NOT NULL,
`quantity` INT NULL,
PRIMARY KEY (`orderID`, `itemID`),
CONSTRAINT `fk_OrderItem_PlacedOrder1`
FOREIGN KEY (`orderID`)
REFERENCES `mydb`.`PlacedOrder` (`orderID`)

10DATABASE DESIGN AND DEVELOPMENT
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_OrderItem_Items1`
FOREIGN KEY (`itemID`)
REFERENCES `mydb`.`Items` (`itemID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION);
Database Query:
Query 1: Select concat(first_name, " ", last_name) AS FullName, gender, salary From
employees inner join salaries on employees.emp_no = salaries.emp_no;
Query 2: Select title, dept_name From employees inner join titles on employees.emp_no
= titles.emp_no inner join dept_emp on employees.emp_no = dept_emp.emp_no inner join
departments on departments.dept_no = dept_emp.dept_no;

11DATABASE DESIGN AND DEVELOPMENT
Query 3: Select concat(first_name, " ", last_name) AS FullName, gender From
employees inner join dept_emp on employees.emp_no = dept_emp.emp_no inner join
departments on departments.dept_no = dept_emp.dept_no WHERE departments.dept_no =
'd004';

12DATABASE DESIGN AND DEVELOPMENT
Query 4: Select departments.dept_no, departments.dept_name, concat(first_name, " ",
last_name) AS FullName From employees inner join dept_manager on employees.emp_no =
dept_manager.emp_no inner join departments on departments.dept_no = dept_manager.dept_no;
Query 5: Select departments.dept_no, departments.dept_name, concat(first_name, " ",
last_name) AS FullName From employees inner join dept_manager on employees.emp_no =
dept_manager.emp_no inner join departments on departments.dept_no =
dept_manager.dept_noWHERE hire_date>'1986-01-01';

13DATABASE DESIGN AND DEVELOPMENT
Query 6: Update employees set birth_date = '1956-04-24' where last_name = 'C';
Query 7: SET foreign_key_checks = 0;
Delete from employees Where emp_no = (Select emp_no From (Select E1.emp_no From
employees E1 inner join dept_emp on E1.emp_no = dept_emp.emp_no inner join departments on
departments.dept_no = dept_emp.dept_no WHERE departments.dept_no = 'd004' AND
E1.emp_no = 10003) as tmptable);
SET foreign_key_checks = 1;
Query 8: Create View empDetails As Select concat(first_name, " ", last_name) AS
FullName, salary, dept_name From employees inner join salaries on employees.emp_no =
salaries.emp_no inner join dept_emp on employees.emp_no = dept_emp.emp_no inner join
departments on departments.dept_no = dept_emp.dept_no;

14DATABASE DESIGN AND DEVELOPMENT
System Testing:
Test Description Expected Outcome Actual Outcome
Inserting data into the tables Insert data successfully
Joining tables Tables joined successfully
Deleting data from tables Deleted data successfully
Creating trigger Trigger working perfectly
Create functions Functions created
successfully
Create view View created successfully
The recommendations for improving the database development are as following.
i. Indexing entire amount of columns utilized in 'order by', 'where', and 'group by'
Clauses
ii. Optimizing like statements along with union clauses
Technical and User Documentation:
Technical Documentation:
Framework: The operating system used for installing the database management system is
Windows 10. The database management system is SQL Server 2017. The MS SQL server

15DATABASE DESIGN AND DEVELOPMENT
Management Studio 18 has been used for communicating with the database server. RAM of the
system is 8GB and CPU is an AMD based chipset. The space available in the system is 150GB.
Queries for Database Creation:
create database FashionOutlet;
use FashionOutlet;
create table Customer (
custID INT PRIMARY KEY,
custName VARCHAR(45),
custContact INT,
custEmail VARCHAR(45)
);
The customerID is the primary key that allows the database to be searched by rows
uniquely. This database does not have any foreign key. The only constraint is associated with the
primary key. The custID is a integer value that will store only numbers.

16DATABASE DESIGN AND DEVELOPMENT
create table PlacedOrder (
orderID INT PRIMARY KEY,
orderDate DATE,
orderTotal MONEY,
orderStatus VARCHAR(45),
custID INT,
CONSTRAINT fk_cust_order FOREIGN KEY (custID) REFERENCES [dbo].
[Customer] (custID)
);
The orderID is the primary key that allows the database to be searched by rows uniquely.
This database does has single foreign key that refers to the custID of Customer table. There are
two constraints in the tables which are associated with primary key orderID and foreign key
custID. The orderID is an integer value that will store only numbers.

17DATABASE DESIGN AND DEVELOPMENT
create table Items (
itemID INT PRIMARY KEY,
itemName VARCHAR(45),
itemType VARCHAR(45),
availableFor VARCHAR(45),
itemCost MONEY
);
The itemID is the primary key that allows the database to be searched by rows uniquely.
This database does not have any foreign key. The only constraint is associated with the primary
key. The itemID is a integer value that will store only numbers.
create table OrderItem (
orderID INT,
itemID INT,

18DATABASE DESIGN AND DEVELOPMENT
quantity INT,
PRIMARY KEY (orderID, itemID),
CONSTRAINT fk_itemorder_order FOREIGN KEY (orderID) REFERENCES [dbo].
[PlacedOrder] (orderID),
CONSTRAINT fk_itemorder_item FOREIGN KEY (itemID) REFERENCES [dbo].
[Items] (itemID)
);
The orderID and itemID are the primary keys that allows the database to be searched by
rows uniquely. These two attributes form a composite primary key. As both of these attributesare
from another table, the entity is a weak entity. This database does has two foreign key that refers
to the orderID of placedOrder table and itemID of items table. There are three constraints in the
tables which are associated with composite primary key orderID, itemID and foreign key
orderID and itemID. As the reference table entities are integer both the attributes in composite
primary are integer type data.

19DATABASE DESIGN AND DEVELOPMENT
Figure 1: Context Diagram
(Source: Created by Author)

20DATABASE DESIGN AND DEVELOPMENT
Figure 2: Data Flow Diagram
(Source: Created by Author)
User Manual:
The admin can record the customer details into the system. The database management
system provides an interface through which the admin can insert new rows, change data or
updated database. The admin can add orders and items into the database. The admin can also
place order for the customer. In order to add multiple items into same order, the admin will chose
the same order id every time new item is added. The admin can update the ordereditem table to
modify the order quantity.

21DATABASE DESIGN AND DEVELOPMENT
Bibliography:
Arasu, A., Babcock, B., Babu, S., Cieslewicz, J., Datar, M., Ito, K., Motwani, R., Srivastava, U.
and Widom, J., 2016. Stream: The stanford data stream management system. In Data Stream
Management (pp. 317-336). Springer, Berlin, Heidelberg.
Yu, X., Xia, Y., Pavlo, A., Sanchez, D., Rudolph, L. and Devadas, S., 2018. Sundial:
harmonizing concurrency control and caching in a distributed OLTP database management
system. Proceedings of the VLDB Endowment, 11(10), pp.1289-1302.
Zhang, B., Van Aken, D., Wang, J., Dai, T., Jiang, S., Lao, J., Sheng, S., Pavlo, A. and Gordon,
G.J., 2018. A demonstration of the ottertune automatic database management system tuning
service. Proceedings of the VLDB Endowment, 11(12), pp.1910-1913.
Nidzwetzki, J.K. and Güting, R.H., 2016. DISTRIBUTED SECONDO: An extensible highly
available and scalable database management system. FernUniversität, Fakultät für Mathematik
und Informatik.
Laudon, K.C. and Laudon, J.P., 2016. Management information system. Pearson Education
India.
Coronel, C. and Morris, S., 2016. Database systems: design, implementation, & management.
Cengage Learning.
Arulraj, J. and Pavlo, A., 2017, May. How to build a non-volatile memory database management
system. In Proceedings of the 2017 ACM International Conference on Management of Data (pp.
1753-1758). ACM.

22DATABASE DESIGN AND DEVELOPMENT