Database Design, Implementation, and Query Language Report

This report provides a comprehensive overview of relational database design and implementation, focusing on the application of these concepts within an e-commerce context, specifically referencing Marks and Spencer. The report details the process of designing a relational database system using various design tools and techniques, including the identification of key tables, fields, and relationships. It explores the use of SQL for database management and query implementation, and also covers the development of a user interface, data validation, and the use of XAMPP, PHP, and MySQL. The report also addresses the testing of the system against user and system requirements, and the production of both technical and user documentation, offering insights into the practical aspects of database design and management.

Database Design

TABLE OF CONTENTS
INTRODUCTION...........................................................................................................................3
Part 1................................................................................................................................................3
P1 Design a relational database system with the help of design tools and techniques..........3
Relational database of Marks and Spencer (E-commerce): ............................................................5
Part 2..............................................................................................................................................12
P2 Develop the database system with evidence of user interface, output and data validations12
P3 Implement a query language into the relational database system...................................26
Part 3..............................................................................................................................................30
P4 Test the system against user and system requirements...................................................30
Part 4..............................................................................................................................................34
P5 Produce technical and user documentation.....................................................................34
CONCLUSION..............................................................................................................................38
REFERENCES..............................................................................................................................39

INTRODUCTION
Database design is a group of process that is helpful for designing, implementing and
maintenance of organization in database management system. Database design is helpful for
producing different database to meet the requirement according to users. It is crucial part of
enterprises to improve the performance of database.
Marks and Spencer is a leading organization that is uses different database tool to manage
the entire business process effectively and efficiently. This report will discuss about the
relational database system to design using different techniques and tools. The assignment also
discusses about the query language which implements in relational to database system. Further,
this report will describe the user and technical documentation and test the system to identify the
need and requirement of database design (Al-Barak and Bahsoon, 2016). This report will discuss
about the different software requirement specification which is required for creating database
management system.
PART 1
P1 Design a relational database system with the help of design tools and techniques
A database management system is software that allow hardware to perform different task
and functions such as storing, updating, deleting and modifying the data. Marks and Spencer is
the fastest growing organization and they use various tools and techniques to design an effective
database for e-commerce business (Battaglia and Smith, 2017). In this way, they are providing
different product information and stored in the form of database system. There are some
important steps helps for designing database system:
1. Identifies the main purpose of system.
2. Determine that what tables are needed for database.
3. Identify unique values and fields.
4. Determine the relationship between various tables.
5. Refine the design
6. Add some data to create the system object.
Marks and Spencer is using SQL technique for maintaining their database system. This
organization is creating their own e-commerce application to create database (Kaneko, 2016). It
also builds the application code and logics in the database. E-commerce application helpful of

organization in context of managing the entire database system by using MS Access, SQL
Server 7, Oracle 8 etc.
Design of database-
Customer table: Customer_id, customer_name, Customer_class
Product table: Product_id, product_name, Product_purchased date,
Order table: Order_id, Payment_id, order_date,
Order Details: Order detail_id, Product_id, Order_number, product_quality
Delivery: Delivery_id, delivery_date, supplier_id
Order detail delivery: delivery_id, order_id, order detail_id
Headquarters: Headquarters_id, branch_id
Branch : Branch_id

Relational database of Marks and Spencer (E-commerce):

Database table-

Marks and Spencer is using e-commerce application to design relational database system
in proper table. In SQL server, they use high level relational database system to create table in
proper ways. Relational database management system provides the service to user interact with
the organization. All the table shows information and details of particular product and their
delivery (Kaneko, 2016). Marks and Spencer is using database system and requirement of
database that is needed to store the information of company and customer in the form of database
management system. In relational database, primary key is playing important role to create table
and each table has unique rows and column is known as primary key. For Example, the unique
number is customer_id which is used as primary key in customer table and product_id is unique
number in product table. Primary key is used as a reference key of another table. They will
generate unique value in database table (Currim and et.al., 2015). According to database table, it
shows that the primary key has unique identity. A database create relationship between the
tables in which database is design in different ways:
 One to many
 Many to many
 one to one

One to many- One to many relationships is that, when the table of rows have many
relationships with another data table. According to this table, it can be concluded that category
have many relations with the products. This relationship will show one to many relationships in
both tables.
Many to many- many to many relationships have two tables so that one table rows have
many relationships with another database table while another table have multiple relationship.

One to one- One to one relationship is defined as the relation between two tables
therefore, both table are interacting with each other because one row contains the same values in
both tables. This relationship is established with the help of primary key and unique foreign key.
Normalisation-
normalisation is data base theory set some specific rule and technique such as
 It is helpful for identifying the relationship between attributes.
 It can be combined with the attribute in the form of relations.
 The combination of relations in the form of database.
1st Normal Form-
In this table, it is in first form and all the key attributes define and does not have any repeating
groups.
For Example-
ORDER
Order_id customer_id product 1 product 2 product 3
508 456 chair bed pen
409 789 laptop
According to 1NF, it can be extract the repeating group in table place new table without
repetition.
ORDER
Order_id customer_id
508 456
409 789
2nd Normal Form-
2 NF table is generated if only 1NF and non-key attributes is dependent on the primary key.
There attributes are dependent on multi attributes key.

For Example- Order (order_id, Customer, customer-id, customer_address, customer_contact,
order_date, order_total)
We know that customer_address and customer_contact are fully dependent on customer but not
order_id. This is not dependent on entire key.
3rd Normal Form-
In table, every non key attributes are transitively dependent on the primary key.
For example- Order (order_id, Customer, customer-id, customer_address, customer_contact,
order_date, order_total)
Here, we know that customer_address and customer_contact is fully dependent on customer and
not key.
ER Diagram:

PART 2
P2 Develop the database system with evidence of user interface, output and data validations
XAMPP software is local web-server for running the scripting language and it is open
source to run the program. XAMPP stand for X- cross platform, Apache, Maria DB, php and
perl. XAMPP server developed by Apache. This server is helpful for creating Login form for
validations using PHP and XAMPP.
Requirement-
 XAMPP Server
 HTML, PHP and CSS
 IDE
Creating MYSQL database
There are some important steps to create validation form with the help of PHP, MYSQL and
Apache. I also include some source code describe in given below: