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

Online Shopping site tables
Illustration 8

Order table:
Illustration 9

Product table:
Illustration 10

Payment table:
Illustration 11

Create database table:

M2 Implement a functional database system which includes the system security and database
maintenance.
It is needed to maintain the system security of database system that help to store the information
about the product, customer and categories etc. The designer has used an effective tool and
technique to maintain the security aspects in the business process. There are some important
security concerns such as
 backup and recovery
 maintenance strategy
 data integrity
 confidentiality
D2 Evaluate the effectiveness of database solution in the relation to system and user requirement.
The effectiveness of database solution is directly linked with the user and system
requirement. It helps to the design approach to determine the logical conceptual ways to
understand the need or requirement of users.
P3 Implement a query language into the relational database system.
SQL stands for structure query language which can use to design for managing the data
in the relational database system (Link and Prade, 2019). This is the standard language for the
relational database of fashion industry that help to perform different task in the tables such as
update, delete, modify and retrieve data form the database. It is possible because of the SQL
statements that contain various type of syntax to manage the data in the form of tables.

Query Solving:
1. Create a SQL statement to list all the employee and their full names, salary and gender.
Illustration 12

2. Create a SQL statement to show the title of employee and their department name.
3. Create a SQL statement to show the full names, gender who belong to the department number
'd004'.
Illustration 13
Illustration 14

4. Create a SQL statement to show all the departments and their managers of department's.
Illustration 15

5. Create SQL Statement to show the list of department manager who were hired after 1986.
Illustration 16

6. Create a SQL statement to change any employee's date of birth. Assume the employee has just
phoned in his last name.
7. Create a SQL statement to delete employee's record who belongs to department 'd004' and ID
is 10003.
Illustration 17
Illustration 18

8. Create a database view to show the list of employee first name, last name and salaries,
departments.
P4 / M4 Test the system against the user and system requirements.
Software testing can be defined as investigation according to the expected model of the
database with its actual functioning. It is mainly conducted to provide the stakeholders with
information about the quality of the software product or service under the test. Database testing
is defined as an activity to check weather the actual results match the expected result in order to
ensure the defect free system (Matsuda and et.al., 2018). It helps in identifying the errors, gaps
and missing requirements of the database system. There are commonly two types of testing in the
software design that is User interface testing and Database Testing.
 GUI testing: It Deals with all the testable items which are mainly open to the users. This
testing is done on the view user interface by which the users interact with the software.
Such UI includes Forms, Presentation, Graphs, Menu and reports etc. all these interface
provides the easy accessibility to the user which allow the user to directly insert delete
and update the data into the Back-end database. It can be usually presented via front -end
tools such as VB, VB.net, VC++, Delphi-Front end etc. This testing includes the
validation of the text boxes, selection of the drop-down list, calenders, buttons,
navigation from one page to another etc.
Illustration 19

 Back-end or database Testing: It deals with the testable items that are generally hidden
from the user. It commonly consists of the internal process and storage space such as
Assembly, DBMS, SQL server, MYSQL, etc. It includes the database table testing,
columns, server validation, Data duplication etc.
There are various different types of testing are there such as:
 Database schema testing: This type of testing mainly involves Validation process,
verification of the table, views, columns etc. this type of testing is verifying the name of
database, device services and allocated the enough space for each database in proper
manner. It also check that the values entered in the table which are accurate (McDermott,
Stephan and Gibson, 2018).
 Procedural testing: This testing is basically follows the specific processes, procedures
and practices that can use to ensure that the system and application is tested or validate
before deployment. It must targeted the specific framework to maintain the security in
overall system in effective manner.
 Functional testing : Functional testing is a type of software that can test against the
functional requirements and specifications. It help to verify that each functions in the
application operates in the proper manner. This testing is generally form of automated
testing that deals with the functionality of application.
This Software is mainly designed for the Fashion Outlet retailing in order to establish the online
services of the company through the development of the online website (Singh, Bhadauria and
Gurung, 20189). This system is aimed at checking the schema, tables, triggers, etc. of the
database under the testing process. It may involve the creation of the complex queries in order to
check load and stress the database responsiveness. Mostly it checks the consistency and integrity
of the of the data.

Test Description Expected outcome Actual Outcome
Schema Testing The expected outcome of
schema testing to verify the
entire schema which enter the
correct value in it.
Database Table and Column
Testing
It display the entire values that
should be generated through
the SQL statements.
Stored procedure testing It will create the database table
to store all the data and
information in the form of
database tables in proper
manner.

Login and User security
Testing
Test verification and validation process-
Verification is a process which includes the design, coding, documents and scripts of the
system. This process is evaluating the work products of the development phase of database to
determine whether they meet the requirements or needs. It also ensures that design pattern full
fill the business needs in proper manner. It easily analyse the data or information that can enter in
the Database system (Son and et.al., 2018). Verification process makes a test cases which may
prepare for the verification of their data and information.
Validation is a dynamic mechanism of database system that validates the actual outcome
or results. It uses the different method such as white box testing, black box testing and non-
functional testing. Validation process can easily find the bugs in the overall system.
P5 / M5 Produce the technical and user documentation
Technical documentation refers to the type of documents that describe the functionality,
architecture of technical procedure of database development. It is the core aspects of database
design when divided into the sub division of the development. Users are generally focus on the
development aspects to collect the necessary resources which requires for meeting the specific
goals and objectives.
User use the database development life cycle on the basis of model:
 It has separated the development of database that is creations and specifications of the
schema to define data in the DBMS.
 It also use the three schema architecture as on the basis for distinguish the activities with
the data schema.

Requirement gathering- It is the first step for gathering the requirements when the user
have to the determine the customer (database user) to understand their proposed system. It also
obtain the document the data and functional requirements. It is also establishing the requirement
which involves the consultation with the agreements among all the users as persistent data. Data
administrator plays important role in maintain the information in proper manner.
Data requirements documents is basically used to confirm and understanding the need
with users.
Analysis- It should be began with the statements of data requirement and then produce
the conceptual data model. In this way, it easily obtain the details description of entities and
attributes that describe the database design (Thai and et.al., 2018). This phase is analysing the
user requirement in both high level and low level properties. In database documentation, the
conceptual model is based on the formal representation of what type of data should contain in
database so that constraints must be satisfied.
Logical design- the database development start with the logical model that help to
produce the specification of logical schema. It will be determining the data type of DBMS
system and representation the independent of any system. Relational data system is describe the
detailed specification, tables, columns, constraints that need to satisfy the description of
Illustration 20: DBMS Life cycle

information in proper manner. A good conceptual data model is necessary to explain the
database properties, but doesn't direct transform to the SQL tables.
SQL (DDL) statement can be needed to use the database which implement the scripts to
meet the user requirements.
D3 Future improvement that may require to ensure the continued effectiveness of database
system.
For making the system adaptive to change the environment, it will be necessary for the
online shopping system or database system that can help to create the tables. It is performing
different functions in the future to satisfy the requirements, it can create separate SQL queries,
statements for the purpose of database creations. and columns which combine with other
relational. For the further enhancement to handle the nested queries, sub queries such as select,
create, insert etc. These are the different statements use in the Structural query language.

CONCLUSION
Database system is a process and method to organize, retrieve, update and delete the
information in the database system. It is also determined the organisation of the data according to
the model which helps for managing the data and information in proper manner. Database design
involves classifying data and identifying interrelationship.
Moreover, it is discussed about the entity relationship diagram that shows the specific
relationship between the different with each other along with the attributes and fields. It is used
the SSMS platform for developing the database system in proper ways. It summarised that
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 explore the testing phase of the database in order to carry out the
system as well as user requirements. It also present the technical and user documentation.

Screenshots

REFERENCES
Book and Journals
Bjeladinovic, S., 2018. A fresh approach for hybrid SQL/NoSQL database design based on data
structuredness. Enterprise Information Systems. 12(8-9). pp.1202-1220.
Hogan, R., 2018. A practical guide to database design. Chapman and Hall/CRC.
Link, S. and Prade, H., 2019. Relational database schema design for uncertain data. Information
Systems.
Matsuda, K. and et.al., 2018. Design paper: Japan Endoscopy Database (JED): A prospective,
large database project related to gastroenterological endoscopy in Japan. Digestive
Endoscopy. 30(1). pp.5-19.
McDermott, T.E., Stephan, E.G. and Gibson, T.D., 2018, April. Alternative database designs for
the distribution common information model. In 2018 IEEE/PES Transmission and
Distribution Conference and Exposition (T&D) (pp. 1-9). IEEE.
Singh, A., Bhadauria, V. and Gurung, A., 2018. A Problem-Solving Based Approach to
Teaching Database Design.
Son, Y. and et.al., 2018. Design and Implementation of SSD-assisted Backup and Recovery for
Database Systems. IEEE Transactions on Knowledge and Data Engineering.
Thai, S. and et.al., 2019. Concrete-filled steel tubular columns: Test database, design and
calibration. Journal of Constructional Steel Research. 157. pp.161-181.

1

2

3

4

5