Database Design: Assumptions, Logical Design, Physical Design, Queries

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