University Database Design and Development Report - Semester 1

This report details the design and development of a relational database system for a Fashion Outlet, covering various crucial aspects. It begins with an Entity-Relationship Diagram (ERD) illustrating the database structure, followed by a data dictionary defining attributes, data types, and primary/foreign keys for tables such as Customer, PlacedOrder, Items, and OrderItem. Normalization is explained through the stages of 1st, 2nd, and 3rd normal forms, detailing how data redundancy and anomalies are addressed. The report then presents the relational database development, including SQL queries for database creation, and various queries for data retrieval, updates, and deletions. System testing procedures are described, along with expected and actual outcomes. Finally, the report concludes with technical documentation, including framework details, database creation queries, and diagrams, alongside a user manual providing administrative instructions and a comprehensive bibliography.

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';