Database Design Report: Logical and Physical Design for Health Club

This report details the database design for a Safe Life and Health Club, addressing the creation of a database system to manage member information, accounts, classes, schedules, courses, equipment, and trainer details. The report begins by outlining assumptions and constraints considered during the design process. It then progresses to the logical design, including an entity-relationship diagram (ERD) illustrating the relationships between various entities like members, equipment, courses, and trainers. The physical design is subsequently developed, encompassing table schemas and a data dictionary. The report delves into physical design features, design decisions for successful implementation, and the use of a relational database management system (RDBMS) like MySQL. It covers file systems, indexes, memory allocation, and transaction analysis. The report also discusses database security, including potential threats and mitigation strategies, along with optimization techniques to enhance performance. Finally, it considers alternative database architecture solutions and concludes with a personal reflection on the design process.

Database design
1

Contents
INTRODUCTION...........................................................................................................................3
MAIN BODY..................................................................................................................................3
Question-1 Assumptions and Constraints........................................................................................3
Question-2 Logical Design..............................................................................................................5
Question-3 Physical Design.............................................................................................................7
Question-4 Physical design Features.............................................................................................10
Question-5 Queries........................................................................................................................11
Question-6 Database Security........................................................................................................22
Question-7 Optimization...............................................................................................................23
Question-8 Alternative database Architecture solutions...............................................................24
Question-9 Personal Reflection.....................................................................................................25
CONCLUSION..............................................................................................................................26
REFERENCES..............................................................................................................................27
2

INTRODUCTION
A Database is a collection of information or data that are stored in organised manner which
consists of various type of tables which may each include different fields in proper manner. It is
the most efficient concept in terms of large data management which require for identifying the
particular data within few minutes.
This report will discuss about the Safe life and health club that make plan for designing and
developing database system. the database will help for managing overall information about the
members, accounts, classes, schedule data, course, equipment and trainer details in it. It will use
for each member to access information about the particular course where trainer provide the
effective training sessions. This documentation will determine about the logical and physical
design of entity relationship diagram that must be representing the overall relational between one
or more tables. It will describe about the database security concept which provide a feature for
securing data in proper manner. The report will critically evaluate the security threat in database
and measures the different parameters to protect data. It will describe about the optimisation that
directly impact on the design and query level within safe or health club database.
MAIN BODY
Question-1 Assumptions and Constraints
Describe the assumptions that make due to information which are not being clear and detailed in
properly.
In this relational database system, I have considered the different assumption in the database
which are desirability and reasonableness of finding the proper result or outcome. Since safe
health club database system is based on the large data collection which are not feasible to
develop case study. In this way, it will require to demonstrate the working process of database
and also implement some assumptions, which as follows:
 The number of trainers has been restricted for teaching on classes at one time.
 It is only open classes for seven days and many people can be booked through online
platform.
 The total number of equipment that can be used in each class as per requirement.
 In stoppage booking of classes, some times not considered in properly.
 Each member contains information about the client and its fitness details by updating on
account.
3

 Staff members are responsible for maintain equipment and coordinating with client.
 Instructor must be scheduled the classes of fitness and providing training by personal
trainer that assigned by staff members.
Understand about the business constraints that has chosen business which may clear from case
study and perhaps common knowledge.
Business constraints are based on the rules that enforces on the data column of different
tables. It is mainly used to limit the data type that can exist into DB table. It must ensure the
reliability and accuracy of data or information in the database system.
 Member Table: this table is mainly consisting of details about the member where
information can be stored in the table. It including member_id, member_lastname,
member_firstname, phoneno, address etc. these are different information that can be
exists in the database table.
Constraint: Member_id is a primary key and unique.
 Equipment Table: the table consists of different attributes such as equipment_id,
equipment type, equipment date, price and status. It helps for creating a relationship with
another table.
Constraints: Equipment_id is a unique that has established the relation with classes table.
In this way, it can easily identify constraints between two tables.
 Course Table: this table contains information about course id, name, duration, start date,
fees etc. these are different attributes exits on the table which requires for identifying
constraints in it.
Constraints: Course_id is a unique
 Trainer Table: the table mainly involves the multiple attributes such as trainer id, name,
contact no and so on.
Constraints: Trainer id is performing as a foreign key which establish the relationship
with course table.
4

Question-2 Logical Design
Design an entity relationship diagram for above case study.
Figure 1 Entity relationship Diagram
An entity relationship diagram is based on the data modeling technique or method that can be
represented the graphically an information system entity and its relationships (Eshtay, Sleit and
Aldwairi, 2019). It is considered as a conceptual model which acquired all type of entity
framework in the form of infrastructure. It useful for database design because of easily analyses
the problem and resolve in logical manner according to the design use of database for business
purpose.
 Primary key: it is set of minimal attributes that exists in table which uniquely identifies
tuple of tables. Each attribute has one or more attributes in database tables. For Example-
in member_table, member id is a primary key and contain unique values.
 Foreign Key: it is mainly defined the attributes set that subjected to contain specific
values which represented as primary key into another table as foreign key. In this way, it
has developed the relationship between one or tables in proper manner. For Example- In
personal trainer table, trainer_id is a primary key that establish relationship with the class
table in which representing as a foreign key.
5

Relationships:
Relationship between Equipment and classes:
These are two different tables that must be established the one to many relationships where
one classes contain various type of equipment for purpose of yoga, Aerobic and many more. In
Equipment table, equipment id is a primary key that mainly create a relationship with other class
table. It is representing as a foreign key.
 Primary Key: In equipment table, equipment id is a primary key.
 Foreign Key: in class table, Equipment id is a foreign key.
Relationship between members and Equipment:
Members and equipment tables are consisting of multiple attributes which established the
relationship with one or more tables. In equipment table, equipment_id is a primary key that
contains of multiple attributes where equipment id shows as a foreign key into members table. In
this way, it has developed the one to one relationship between both tables.
 Primary Key: In equipment table, equipment id is a primary key.
 Foreign Key: in members table, Equipment id is a foreign key.
Relationship between instructor and schedule:
These are two different tables that must create relationship between them by using
foreign key. In instructor table, instructor_id is a primary key that represents as a foreign key into
schedule table so as generating one to one relation in proper manner. In these tables, there are
different types of attributes used for showing their properties in database tables. It useful for
finding overall information of particular schedule date, time.
 Primary key: In instructor table, instructor_id is a primary key.
 Foreign key: In schedule table, instructor_id is representing as a foreign key.
6

Question-3 Physical Design
3.1 Develop physical diagram with relation schemas and find out the data dictionary
Figure 2 Physical design
The physical data model is the most common approach that represent the process of information
to database system (Huang and Leng, 2019). it is mainly shown entire table structure which may
including primary, foreign and their relationship between tables. This type of relationship is
providing physical structure which mainly focused on primary key, foreign key and
relationships.
3.2 Design the table schema and identify the data dictionary for each table as per case study
Data schema
A database schema is based on the skeleton structure that mainly representing the logical
view of entire health club database. It is defined that how data organised in proper ways and how
multiple relationship associated with them. This type of database is formulating the constraints
that applicable on data or information.
7

Account
Account_Number Account_date Account_type Account_fees MembershipAccount_type
Class
Class_id Class_name Class_price Class_description Equipment_id
Course
Course_id Course_name Course_duration Course_start_date Trainer_id Course_fee
Equipment
Equipment_id Equipment_type Equipment_date Equipment_price Equipment_status
Instructor
Instructor_id Instructor_Name Room_phoneNo Salary
Personal Trainer
Trainer_id Trainer_name Trainer_contactNo Member_id
Schedule
Schedule_date Schedule_time Class_id Instructor_id
8

Data dictionary
It is a type of file that contains database metadata’s which mainly contains records about
the project. This type of data dictionary is crucial part of relational database which is invisible
for users and only database developed interact with the overall dictionary (Kraleva, Kralev and
Sinyagina, 2018). In MS SQL server, it is executing the SQL statement which determine the
tables and fields that are referencing properly in valid manner.
Member Table
Field Name Data Type Field Length Constraint Description
Member_id Int 10 Primary key Member_id,
Auto generated
Member_lastName Char 10 Not null Last name of
member
Member_FirstName Char 10 Not null First name of
member
Member_PhoneNo Int 10 Not null Mobile or
landline number
Member_address Varchar 10 Not null Correct address
of member
Member_monthlyFees Int 10 Not null Fees on monthly
basis
Account_number Int 10 Not null Account number
of members
Equipment_id Bigint 10 Not null Equipment id
Table:1
9

Account
Field Name Data Type Field Length Constraint Description
Account_number Int 10 Primary key Account_number,
Auto generated
Account_date Date 10 Not null Date of account
Account_type Char 10 Not null Categories
account type
Account_fees Bigint 10 Not null Monthly account
fees
MembershipAccount_type Char 10 Not null Divided each
membership
account type
Table: 2
Question-4 Physical design Features
Describe about the design decision that have to make ensure the successful implementation of
logical design to physical database.
As per scenario, it can be determined about essential requirement of database designing. It
makes decision on behalf of logical design which always useful for identifying different entities
and attributes that easily meet the business scenario. Logical data model that should be used as
blueprint that help for creating overall process and structure of safe health club database. It can
be mapped into the logical manner in which useful for making important decisions. Before
starting the design process, it is mainly used the initialise the data and understood overall
process. It is very important for database designer that implementing an appropriate logical to
physical design.
10

Explain about the Relational database management system and applicable file systems, use of
indexes, memory allocation and transaction analysis.
The relational database management system is used for storing and collecting large number
of data or information in the system. it consists of file system uses of indexes, transaction
analysis and memory allocation (Kumar and Azad, 2019).
File management system is a type of database that allows to access single files, tables at
same time. it directly stores set of files within flat file in which contains no relation to another
files.
In terms of Relational database management system, it will use MYSQL which proposed
to handle all type of indexes and identify the fields in particular table. As per scenario, Indexes
are basically used to quickly locate data or information without having to search particular row
in database table. This will create by using one or more column in database table for providing
the random lookups and efficient to access in ordered records. As per scenario, database index is
formed in the data structure that improve the speed of data retrieval operation at cost of
additional writes and space of storage.
For Example- it will create index for account database table at the time of creation. It
should follow the statement of new table create.
In this way, it has to be created index for column and list of columns, which specify name of
index.
The database is stored the information in memory where it much easier for easily faster
memory access than disk access. It takes lot of time and compared to memory access in order to
increase need of resources because of device drivers. It can be operating the system through
event schedulers in task management.
SQL consists of four important statement which mainly referred as CRUD Matrix.
 Create: insert to store data into column.
 Read: Select the retrieve data into table
11

 Update: update to change the data and replace from another one.
 Delete: delete the data from list of columns.
CRUD Matrix shown in the form of table that containing SQL statement which affecting the part
of database.
For Example: -
Nr. USER ACTION Create Read Update Delete
1 Login account on Web Site --- --- --- ---
2 Update the view User Survey SurveyMaster --- ---
3 Find data into column --- Site Directory --- ---
4 Search detail on gym site Class_id, trainer_id, name, timing equipment, name, quantity ID Table ---
5 Post to the Message Board Confirm schedule Confirm class timing ID Table ---
6 Check the News equipment --- News --- ---
7 Logout --- --- --- ---
Table:1
12