Relational Database Design and Development for Fashion Outlet System

This project report details the design and development of a relational database system for a fashion outlet, focusing on enhancing productivity and profitability through effective data management. The report begins with an introduction to database concepts, followed by the design of an Entity-Relationship Diagram (ERD) to model the entities (customer, order, product, payment, categories) and their attributes. The design incorporates normalization techniques (1NF, 2NF, 3NF) to reduce data redundancy and ensure data integrity. The project then progresses to the development phase, showcasing the user interface, data validation methods, and SQL queries for data retrieval, insertion, and manipulation. The implementation phase covers system security and database maintenance. The effectiveness of the database solution is evaluated in relation to user and system requirements. The project also implements SQL queries to retrieve data. The report concludes with a discussion of testing methodologies and user/technical documentation, highlighting the importance of testing for functionality, error detection, and system improvements, as well as recommendations for future enhancements.

Database Design & Development
1

Contents
INTRODUCTION...........................................................................................................................3
P1 Design a relational database system by using the appropriate design tool, technique and
containing the interrelated tables.................................................................................................3
Entity:..........................................................................................................................................4
D1 Effectiveness of design in the relation to system and user requirement................................8
P2 Develop the database system with the evidence of user interface, output and data
validations and querying across multiple tables..........................................................................9
M1 Implement the functional database system that involves system security and database
maintenance...............................................................................................................................15
D2 Evaluate the effectiveness of database solution in relation of user and system requirement.
...................................................................................................................................................15
P3 Implement a query language into the relational database system........................................16
P4/ M4 Discuss about the test system against user and system requirements...........................26
P5/ M5 Produce technical and user documentation..................................................................28
D3 Discuss the Improvement in future which require to ensure the continued effectiveness of
database system.........................................................................................................................29
CONCLUSION..............................................................................................................................30
REFERENCES..............................................................................................................................31
2

INTRODUCTION
Database is a collection of inter related data which help for inserting, deletion and
retrieval of data from the database system. It is also organised the data in the form of tables,
reports, schemas and views etc. It useful for managing the large amount of data and information
in the database system. This report will discuss about the Fashion outlet which is based on the
online shop to design the database for increasing the productivity and profitability. It can be
designed the database system that store the information regarding the consumer, orders, items
and payment, employee details etc. It allows to user access the information about the product that
provided by the online shop for Fashion outlet expansion.
Furthermore, this report will discuss about the Entity relationship model which required
for designing database because it is determined the important entities and create a relationship
between them. It will describe the ERD diagram for the entities along its attributes through
Normal forms. Moreover, it will produce the technical and user documentation that can use for
the designed database.
P1 Design a relational database system by using the appropriate design tool, technique and
containing the interrelated tables.
An Entity relationship diagram is basically set entity that stored in the database system. It
is a type of high level conceptual data model which based on the real entities to create
relationship with one or more. It helps to analyse the requirement of data in systematic manner to
produce the well-designed database system.
 Entity relationship Model allows to construct the database design and also considered the
requirement of system.
 It is very easy to use as the graphical tool for the purpose of data modelling.
 It is widely used in the database design and representing the logical structure of DBMS.
 It useful to determine the entities that exists in the database system and create relationship
between them.
3

Figure 1: Entity relationship Diagram
Entity:
An entity is a type of object in the system that create model and store information in the
database. It is generally applicable to recognise the concepts, abstract which are relevant to the
database. An entity is the analogues to the table in the relational model.
There are several type of entities
Attributes:
An attributes is an item of data which are stored in the form of entity. Each and every
entity are containing the specific attributes. It can be represented in the ER model to represent in
the appropriate manner.
4

Data Dictionary:
Entities Attributes Data type
Customer  Customer_id (PK)
 Customer_name
 Customer_email
 Customer_phone no
 Customer_address
 Order_id (FK)
Integer
Varchar
Varchar
Integer
Varchar
integer
Order  Order_id (PK)
 Order_type
 Order_status
 Order_name
Integer
Character
Varchar
varchar
Product  Product _id (PK)
 Product_name
 Product_price
 Product_type
 Product_weight
 Category_id (FK)
 Customer_id (FK)
Integer
Varchar
Number
Varchar
Real
Integer
integer
Payment  Payment_id (PK)
 Amount
 Payment_type
Integer
Number
varchar
Categories  Category_id (PK)
 Category_name
 Category_type
 Payment_id (FK)
Integer
Varchar
Text
integer
Table:1
5

Primary key:
A primary key is a type of special relational database column that can design to uniquely
determine the table records. It must contain unique values for each row or information and data.
It has a minimal attribute in table that uniquely identifies the tuples in the form of tables.
As per above table, it can be identified the primary key in each database tables.
In customer table:
 Primary key: Customer_id
In order table:
 Primary key: order_id
In product table
 Primary key: product_id
In Payment table:
 Primary key: payment_id
Foreign Key:
A Foreign key is a field in one table that uniquely determine the row of another or same
table. It can be defined in another table as a foreign key. It ensures the referential integrity of the
data and information.
For Example-s
In customer table:
Foreign key: order_id
It has created a relationship with order table because in order table, id is a primary key
that represented as foreign key into another table. It has created one to many relationships
between them.
In product table, customer_id is a foreign key in table so that it has created a relationship
between customer and product table.
6

Normalization:
Database normalisation is a type of process and method of structuring the relational
database system of Fashion outlet (Birch, Gwinnett and Walker, 2018). In order to reduce the
data redundancy and help to improve the data integrity.
1. First Normal Form
2. Second Normal Form3. Third Normal Form
First Normal Form
1NF is a property which create relation in the database system if the domain of every
attributes contain the atomic values and each attributes contain only single value in the system. It
is the simplest way to satisfy the requirement which allows the product price contain one or more
values in database table.
Second Normal Form
2NF is another normal form in database that can fulfil the requirement when it does not
have any prime attributes and its functionality dependents on the subset of candidate key. It must
satisfy the first Normal form.
7

Third Normal Form
3NF is basically used to normalise the database design to reduce the redundancy or
duplication of data. It is also ensured the referential integrity by ensuring second normal form.
D1 Effectiveness of design in the relation to system and user requirement.
The effectiveness of design in the relation which need to develop the documents that
consists the need and requirement of users. In this way, it should include the overall description
of every task in appropriate manner (Coury and Semmel, 2018). It has required for designing an
effective database system that can fulfill the need of users. User easily access the details related
the fashion cloths, item through the database system. Without having effective design, it is not
possible to maintain the processing of system. It ensures that capture the system requirement for
understanding the
8

P2 Develop the database system with the evidence of user interface, output and data validations
and querying across multiple tables.
It is developing the database of Fashion outlet online shop which provide the better
system to manage and control the overall functionality in proper manner (Goulet and et.al.,
2018). It is also stored and collect the information and data of fashion outlet. It provides the
graphical user interface that required for people to know about the technical knowledge which
help to access information in effective manner.
It shown the process of user interface which occurs when the user can access the
information through database system. In this way, database Engine has quickly processed the
functionality to perform the task in proper manner.
Data Validation:
It is an essential for Fashion company to validate the data that can use store in the
database system. It can be used the database engine to analyse and determine through SQL to
enter correct or wrong. It helps to manage and control the data that can applicable in the system.
It is developing the proper assumption related the software development life that adopt the
process to add more features in database. It should be used the SQL Server Management studio
that help to create and develop the database view and store the information in the form of tables
in it.
9

Output:
Database tables
Category
10

Customer table:
11

Order table:
12