SQL Database Design and Implementation for Universal Sports Company

This project focuses on the design and implementation of a SQL database system for Universal Sports Company. The assignment begins with an introduction to databases, emphasizing their role in efficient data management. The main body of the project includes an Entity Relationship Diagram (ERD) illustrating the relationships between entities such as instructors, branches, members, membership types, sports, staff members, and trainers. The project then delves into the implementation phase, detailing the creation of database tables to store employee and participant information, along with forms, queries, and reports for data retrieval and management. The queries demonstrate how to extract specific data from the tables. Furthermore, the project explains the relationships between different tables, for instance, between branches and members and sports and trainers. The conclusion summarizes the development of the database system, highlighting its potential for improved information access and management within Universal Sports Company. References to relevant literature are provided at the end.

Information Engineering
(SQL)
1

Contents
INTRODUCTION...........................................................................................................................3
MAIN BODY..................................................................................................................................3
TASK 1............................................................................................................................................3
Entity Relationship Diagram.......................................................................................................3
TASK 2............................................................................................................................................7
Implement the database to meet the requirement of Universal Sport database system...............7
CONCLUSION..............................................................................................................................18
REFERENCES..............................................................................................................................19
2

INTRODUCTION
Database is based on the collection of inter-related data which helps in efficient retrieval
insertion, deletion of data from different database system. It can be organised the data into
different form of tables, schemas, reports and views. The report is mainly focused Universal
sports company in which developed the database system to gather or collect information about
staff members and other participants. It will be proposed the system to keep record the employee
information and accessible for any time. Furthermore, the documentation will describe about the
entity relationship diagram for identifying the entities, attributes. However, it will be produced
the database tables to store information of each employees.
MAIN BODY
TASK 1
Entity Relationship Diagram
Figure 1 Entity Relationship Diagram
Entity relationship diagram is also known as entity relationship model which mainly graphical
representation that depicts relationship among objects, concepts, people, places or event within
information technology (Latukolan, Arwan and Ananta, 2019). Basically, ERR uses for data
modelling technique that can help for defining business processes or serve as foundation for
relational databases. ERD can provide the brief idea about the entire database design that can
3

help for used to determine information system requirements. Entity relationship model is useful
for organising data that can be represented by relational structure. Sometimes, it is not
sufficiently represent both unstructured as well as semi-structure.
Data dictionary
Entities Attributes
Instructor  Instructor_id (PK)
 Instructor_name
 Instructor_email
 Instructor_PhoneNo
 Instructor_sex
 Instructor_type
 Sport_id (FK)
Branches  Branch_code (PK)
 Branch_phone
 Branch_Name
 Branch_Address
 Member_id (FK)
Member  Member_id (FK)
 Memebership_type
 Member_name
 Member_name
4

 Member_PhoneNo
 Member_address
 Member subscription
 Method of payment
 Start_date
Membership_type  Membership_type
 Subscription_amount
 Member_id (FK)
Sports  Sport_id (PK)
 Sport_type
 Sport_session
 Trainer_id (FK)
Staff_Member  Staff_code (PK)
 Staff_name
 Staff_address
 Staff_DOB
 Staff_PhoneNo
 Staff_marital_status
 Staff_insuranceNo
 Staff_title
 Staff_bankNo
5

Trainer  Trainer_id (PK)
 Trainer_name
 Trainer_PhoneNo
 Trainer_email
 Member_id (FK)
Table: 1
In above table, it consists of different entities and their appropriate attributes for establishing the
relationship with another one. Each and every entities are representing the suitable field, which
may contained specific value into column.
TASK 2
Implement the database to meet the requirement of Universal Sport database system.
Database tables
It can be defined the collection of related data or information held in table format within
database system (Ordonez and Bellatreche, 2019). In relational databases, it is set of data
elements using model of vertical columns and horizontal rows. Afterwards, cell being the unit
where each row and column intersect.
Fitness_Instructor
Branches
6

Member
Membership_type
Sport_table
Staff_table
7

Trainer_table
Forms
A form is based on the database object that can use to enter information and also edit, display
data from table or query (Sarino, Dinar and Juliana, 2019). Generally, it can be generated
different forms to control access the data such as field of data which may be displayed.
8

9

10

Queries
SELECT Branches.[branch_code], Branches.[branch_Phone], Branches.[Branch_name],
Branches.[branch_address]
FROM Branches;
SELECT Fitness_instructor.[instructor_id], Fitness_instructor.[instructor_name],
Fitness_instructor.[instructor_email], Fitness_instructor.[instructor_phoneNo],
Fitness_instructor.[instructor_sex], Fitness_instructor.[instructor_type]
FROM Fitness_instructor;
11

SELECT Member.[member_id], Member.[membership_type], Member.[member_name],
Member.[member_phoneNo], Member.[member_address], Member.[member_subscription],
Member.[method of payment], Member.[start date]
FROM Member;
SELECT MembershipType. [membership_type], MembershipType.[subscription_amount]
FROM MembershipType;
SELECT Sports. [sport_id], Sports. [sport_type], Sports. [sport_session]
FROM Sports;
SELECT Trainer.[trainer_id], Trainer.[Trainer_name], Trainer.[Trainer_phoneNo], Trainer.
[Trainer_email]
FROM Trainer;
12