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

Reports
A report is mainly consists of information that always pulled from tables or queries as
well as information (Xu and Hu, 2019). Generally, various reports may offer a way to view,
format and summarised information within Microsoft access database.
13

14

Relationship Diagram
15

Relationship between Branch and Member
In Member table, member-id is a primary key that consists of unique value into database
table. On the other hand, member_id is exits into branch table and it means that perform as
foreign key into branch. In order to establish the relationship between branch and member table.
It is essential for establishing relationship with the help of primary key (Member_id).
16

Relationship between Sports and Trainer
In relationship between Sports and trainer, Trainer_id is a primary key in which established the
relationship with another sport table. It can be represented as foreign key into sport table. This
will help for creating a relationship between them.
CONCLUSION
From above discussion, it has summarized about the concept of Universal Sports Company
which has mainly developed to new database system to store large amount of information related
the staff members and other participants. It has been proposed the new system to keep record the
employee information and also provided the facility of member to search or access information
in proper manner. In this report, it has been creating an entity relationship diagram for
identifying the entities, attributes. Afterwards, it also produced the database tables to store
information of each employees.
17

REFERENCES
Book and Journals
Latukolan, M.L.A., Arwan, A. and Ananta, M.T., 2019. Pengembangan Sistem Pemetaan
Otomatis Entity Relationship Diagram Ke Dalam Database. Jurnal Pengembangan
Teknologi Informasi dan Ilmu Komputer e-ISSN. 2548. p.964X.
Ordonez, C. and Bellatreche, L., 2019. Enhancing ER Diagrams to View Data Transformations
Computed with Queries. In DOLAP.
Sarino, S., Dinar, D.A.P. and Juliana, I.C., 2019. Database Structure of Land Allocation
Management Information System for Estimating Run-Off in Watersheds. International
Journal of GEOMATE. 17(59). pp.34-42.
Xu, S. and Hu, H., 2019. Development of a maritime safety management database using
relational database approach. International Journal of Shipping and Transport
Logistics. 11(4). pp.334-353.
18