SQL Management Studio Database Design for Online Fashion Outlet

This project report details the design and implementation of a relational database system for an online fashion outlet, focusing on the use of SQL Management Studio. The report begins with an introduction to database design, emphasizing the importance of entity-relationship diagrams (ERDs) in representing the relationships between different entities, such as customers, payments, and items. The main body of the report covers the design process, including the creation of ERDs, the definition of attributes, and the application of normalization principles (1NF, 2NF, and 3NF) to optimize data organization and minimize redundancy. The report then explores the development of the database system, including user interface design, output data validation, and the formulation of SQL queries to manage and retrieve data. The report also discusses system security and database maintenance strategies, and evaluates the effectiveness of the database solution in relation to user and system requirements. Finally, the report includes testing procedures, including GUI and database testing, to ensure the system meets the specified requirements. The report concludes with a discussion of future improvements and provides both technical and user documentation. This project showcases the student's understanding of database design principles and their practical application in a real-world scenario.

Database Design

Table of Contents
INTRODUCTION...........................................................................................................................3
MAIN BODY...................................................................................................................................3
P1 Design a relational database system of online fashion outlet shop by using the SQL
management studio techniques. ..................................................................................................3
D1 The effectiveness of the design in relation to user and system requirements. ......................8
P2 Develop the database system with the evidence of user interface, output data validation and
querying across the different tables. ...........................................................................................8
D2 Evaluate the effectiveness of database solution in the relation to system and user
requirement................................................................................................................................19
P3 Implement a query language into the relational database system. .......................................19
P4 Test the system against the user and system requirements. .................................................25
Test verification and validation process-...................................................................................28
P5 Produce the technical and user documentation.....................................................................28
D3 Future improvement that may require to ensure the continued effectiveness of database
system. ......................................................................................................................................30
CONCLUSION..............................................................................................................................31
REFERENCES..............................................................................................................................35

INTRODUCTION
Data base design can be termed as the organisation of the data according to the model
which helps in determining the type of data stored in it. Data base design involves classifying
data and identifying interrelationship. This study will help in developing a database design for
the fashion outlet in London in order to provide online facilities for the users. The database
designing of the fashion outlet is aimed at developing the relational database system which
allows the storage of customer details, order items, payment and employee details. This database
design will help the retailing company in improving the operational efficiency and also provide
easy access to the data and information regarding the day to day transactions.
Moreover, the report will discover the appropriate entity relationship diagram which
displays the relationship of different entities with each other along with the attributes and fields.
It will help in developing the database with the suitable tool known as SSMS Or structured query
Language. It will also enhance the understanding about development of the ERD to implement
the Querying statement to insert, update, delete and modify the data into the tables. Testing the
system is one of the important requirement so it will explore the testing phase of the database in
order to carry out the system as well as user requirements. It will also present the technicle and
user documentation.
MAIN BODY
P1 Design a relational database system of online fashion outlet shop by using the SQL
management studio techniques.
Entity relationship diagram for the entities along with the attributes.
Entity relationship model is basically describes the interrelated things of interest in the
specific domain of knowledge. ER is composed with the different entities types and specific
relationship that already exist between the entities (Bjeladinovic, 2018). It is commonly formed
to represent the things a enterprise requirements in order to perform business process,
consequently ER become an abstract of data model that define information or data structure
which can be implemented in relational database.

Entity-
An entity is a real world object that are represented in the relational database and it can
be any object, person and class. Data and informations are stored in the form of entities in the
DBMS. According to ERD, There are several type of entities used in the database system such as
customer, payment, categories, cart and item etc.
Attributes-
It refers to the database component in the form of table and describe the instances in the
row of DBMS (Hogan, 2018). Attributes are simply the one of non null cell in spreadsheet. It
stores only one pieces of data about the object that represent buy the table. As per ERD, it has
represented the attributes such as customer_id, name, phone no, payment_id, type etc. These are
the different type of attributes shows in the database tables.

Primary Key-
Primary key is a special type of entity and attributes that uniquely define the record in the
database table.
In customer table Primary key: customer_id
In online shopping site Primary key : reg_no
In payment table Primary Key : payment_id
In cart table Primary key : cart_id
In categories table Primary key : category_id
In item table
 Primary Key : item_id
In order table
 Primary Key : order_id
Foreign Key-
Foreign key is basically the field in one table that uniquely determine the row of another
and same table. It can use to create relationship between two tables in proper manner. It shows as
references primary key into another table.
Mapping into the relational Model
Entities :
Online Shopping site
Reg_no Name Web_address
Customer
Customer_id Customer_name Customer_address Customer_phone
Payment

Payment_id Payment_amount Payment_type
Cart
cart_id Cart_total cost
Categories
Categories_id Name Type
Item
item_id Name item_price
Employee
emp_id emp_first
name
emp_last
name
emp_pho
ne no
emp_gen
der
emp_sala
ry
emp_add
ress
emp_DO
B
emp_hire
date
Order
Order_id order_name order_type order_status
M1 Produce a design for a fully functional system which includes interfaced, ouput design s and
normalization.
Normalization Concept (1NF, 2NF and 3NF)
Normalization is a type of process that help to organize the data and information in the
database system. This process is used to minimize the redundancy from the relation and set of
relations. It also help to eliminate the undesirable characteristics such as Anomalies, update,
deletion and update etc. This process will help to divide a larger table into the smaller tables and
also link between them using relationship. It can be divided into three different forms such as
First Normal Form (1 NF)
If a relation may contain the composite and multi valued attributes, it also violates the
form normal form and relations is in the 1NF. A first normal form said to be 1 NF if each
attributes of relation is atomic.

 In this way, each row in the table should be determined the primary key
 There is no row of data repeating the column values and group.
Second Normal Form (2 NF)
To be in the 2NF (second normal form), a relation must be in the first normal form and it
may not contain any type of partial dependency. In 2NF, it has no partial dependency and no
prime attributes.
 Each and every key attributes are determined by using primary key.
 All type of sub type data which applicable to the multiple rows in the table and also
remove or place in another table. New generated table should be related the foreign key.
Illustration 1

Third Normal Form (3 NF)
In third norm form, it has no transitive dependency for the non prime attributes is in the
2NF (second normal form). This normal form should meet all the requirements of both 2NF and
1NF. In case, any column which are not matched to primary key so that it can remove them and
also put the another separate table.
D1 The effectiveness of the design in relation to user and system requirements.
It is important to establish and documents the users requirements and needs so that can
lead into the design process of system itself. User requirement must be included the overall
description of every task when the system will support the functions. Without having the
effective design, it is not possible to identify the actual processing of database system.
System design phase is initiated and also ensure that the system requirements or capture
in the repositories. It also mapping with the task and functions of system requirement.
P2 Develop the database system with the evidence of user interface, output data validation and
querying across the different tables.
Online Fashion outlet shop has developed the database that manage all type of operations
and their functionality. This is the graphical user interfaces when user can easily execute the
queries and tables. However, The GUI provided by each database server where the online shop
has own database sever to operates with the technical knowledge.
Illustration 2
Illustration 3

This figure is representing the process of user interface when they can follow the specific steps to
perform different operations and functions.
Output Validation:
Validation is basically process which allows the user to check the data which provided
and also check the information before enter data into the system. In SQL sever management
studio, It makes the assumption about the existing software development life cycle and adopt the
new concept that help to meet the goals. The database should have technological tool for
managing and retrieving the data in proper manner. It always maintain the user level security
system in the relational database system.
Illustration 4: User interface design

Database Tables generated by using SQL Queries:
Cart table:
Illustration 5

Customer table:
Illustration 6

Employee table:
Illustration 7