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

This document discusses the database design process, including assumptions and constraints, logical design, physical design, and queries. It provides an entity relationship diagram and data dictionary for the case study. The document also covers the use of indexes, memory allocation, and transaction analysis in a relational database management system.

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

Question-5 Queries
Structured query language is based on the programming language to organise and retrieve
data in terms of relational database (Link and Prade, 2019). It is mainly used for identifying the
data elements, attributes which categorised into column that related to other rows. It is the most
suitable programming language for performing different actions such as create, update, modify,
insert and delete.
Account_table
13

Class_table
Course_table
14

Equipment_table
Instructor_table
15

Personal_trainer_table
Members_table
16

Schedule_table
Condition based queries
Greater than >
17

Sub Queries
18

Create Statement:
19

Complex Queries
5.1 SQL Queries
By using Inner join to find out each members account details.
20

By using Aggregate Min function to calculate the smallest course fee.
Order by and group by
21

Union
Like
These are different type of condistion applicable within database system. it is benefical for gym
online platform for accessing information by using query language. It applicable in database for
generating result or outcome.
22

Question-6 Database Security
Describe about the four-security threat in database and measure different criteria, how to protect
data and information.
Database security is mainly referred to the collective measurement that used to provide
security and its overall management software. It has needed to use illegitimate use for removing
attacks and threats within database system (Novalić, Kamberović and Saračević, 2019). it is a
broad term that must include the various processes, tools and methodologies that ensure security
of database environment. it can be divided the different database security threat within data
management.
 Excessive privileges: in health club, when members are granted default database
privilege that exceed the needs of job functions. in this way, it has chanced to abused
where one member easily changes the information of other members. It also takes
advantage of excessive database privileges and also access the personal information of
colleagues. Furthermore, companies also failed to update access of privileges for
members who change roles with an organization. As per scenario, it can be used the
excessive privilege by database administrator for maintained and controlled the account
by users. it should give privilege of user where they are setting password on account. At
any time to access details about gym. For Example-
 Database Injection attacks: database injection attacks are considered SQL injection that
are mainly targeting the traditional database system. NoSQL injection is targeting the
large collection of big data. SQL injective attacks are affecting on the overall process and
always try to target the overall class. In this way, it is successfully input injection attack,
give unrestricted access of hackers (Pohl and Melidis, 2019).
23

 Undamaged sensitive data: It is also another threat in the database where it faced
struggle to maintain the accuracy of inventory database which are critically contained the
data objects. The new database can easily emerge with the visibility to maintain the
security but sensitive data exposed threats which required for maintaining, controlling
and taken permission which are not implemented.
 Exploitation of vulnerable database: It is mainly taking the business to patch the
databases at the time of vulnerable. Many attackers are easier to exploit unpatched
database. In this way, database administrator often face struggle for maintaining overall
configuration, usually it has increased issues related the high workloads and mounting
backlogs for associated with time consuming.
These are different types of threat in the database which require for focused on resolving issues
and problem in proper manner. It helps for minimizing the issue in terms of database security to
give privilege of access only authorized members within organization.
Question-7 Optimization
Describe about the requirement of optimisation in database and evaluate its specific discussion
on how to monitor, optimise and back up database.
The database of Safe healthy club that have designed for requirement of optimised the
overall performance and efficiency. Optimization is based on the process for selecting an
efficient of executing the structure query statement which optimise the free to reorganise, process
and merge. In order to maintain overall process in proper manner. It can be optimised each SQL
statement on behalf of statistics which are collected accessible data or information (Puetz, 2018).
The optimization process is mainly involved in the database system which help for reducing the
response time. in this way, it is considered as an important concept in terms of database design to
easily optimise the level of performance and also acquired all type of functional needs.
During the building queries that yields faster results, where database designer analysis
overall services and methods to improve its efficiency greater than actual overall data
management processing. The optimisation process will help for performing different functions
which recovery, monitor and backup different DB task that simply look simple and usually set up
them. It is considered as a modern database which growing the business in global marketplace.
Optimise database is the faster back and recovery operation which easily exact the process
efficiently.
24

According to scenario, Optimise the database but it requires to optimization the size of data
which exists on the table for minimizing the space on disk. In this way, MYSQL supports to
increase size capabilities of overall database structure by using storage engine. For each table, it
has decided to store and uses index technique to manage optimization process.
For Example: -
practice rules, indexing, Relational Algebra are considered the better approach within the
database system. Index provide higher performance which gains to apply on column, when it
often to include “Where” clause. But having most of times, indexes will reduce the overall
performance of insert commands.
For Example: - Insert command statements
For Example- Algebra will increase performance which formed in the tree format to identify
shortcut path for execution.
∏(Column 1,Column 2….Column n)(Relation Name)
25

Question-8 Alternative database Architecture solutions
Describe about the Alternative database solution.
As per scenario, it can be determined that needs to consider the specific action performed
which accommodate to manage the entire unstructured and structured data in proper manner. In
this way, it should be used another alternative option for database designer to choose NoSQL
which providing the data schema in carefully maintain all details (Tomar and et.al., 2019). This
type of database is especially useful for working large set of distribution information or data.
By using NoSQL, developer can be created massive volume of new and rapidly changing
data types in different forms. In this way, it can be organised overall architecture in large scale in
which servers’ facilities of large storage within relational database system. Big Data: It is defined the large and diver set of information or data that growing and
increasing. It always encompasses large amount of volume where it will create and
collect the information wide variety.
Difference between non-relational and relational solution.
Relational Non-Relational
 SQL is based on the relational database.
 It is created data in the form of tables
so it become easier for finding suitable
data in large collection (Tomar and
et.al., 2019).
 It is the most suitable solution for
managing data or information in proper
manner.
 NoSQL is based on the non-relational
database that supports only traditional
method.
 NoSQL is considered as documents.
 It is another alternative solution for
managing the data in the form of
document.
Advantages and type of NoSQL database
 It is collecting the large amount of data in the form of unstructured, structured and semi-
structured.
 NoSQL is frequently flexible and easy to use in proper manner.
26

Question-9 Personal Reflection
Before I start for designing database, let me confirm with the safe health club and then
afterwards try to design an effective database system that consists of various information
regarding classes, training session, equipment’s and schedules. All type of details must be
required to store in proper manner. First of all, I could try to find the essential entities and its
significant attributes which help for me to understands overall process and how easily
maintained the information or data in database management system. I must follow the
normalization process that help for designing pattern and analyse overall design effectively. I
could analyse nature of application in both analytical and transactional forms (Vial, 2018). I have
broken the data or information into logical process which make simpler to resolve the problem
with the help of queries. I also used the functions and data type which supports for managing
overall processes. At beginning, I have faced the challenges and problem during database design
which must show error related the incorrect data values so I always chose an appropriate type
which are suitable for particular functions. In order to find the accurate solution that require for
storing data or information regarding health club. In this assessment, I have learnt many things
for generating a query and finding outcome. it is very useful for me to learn new features that
applicable for database development. I have learnt that how to organise data in the proper format
which always improve consistency, cost effective in terms of database management. I can
introduce the different applications, SQL features, programming concept that use in database
design for maintaining overall safe health club database.
As per my opinion, one of most common structure query language requires to understand in
properly because it helps for me to manipulate the language and perform different functions. At
the time of database development, I have learned to write the programs in the form of packages,
procedures, triggers and other debugging by using Structure query which supports for me to
handle overall process in proper manner. I have found that it may covers all type of
programming skill which help for increasing the efficiency of database system. by using database
project, I can improve own abilities which would better for enhancing knowledge in database
management of health club. It would better for me to implement new concept and idea, thought
for identifying the result or outcome. I have done better way to design overall database system so
as easier for members to access information of any classes within seconds. As per experience, it
27

becoming great opportunities for me to analyse overall situation and condition, putting extra
efforts to perform actions in proper manner.
CONCLUSION
From above study, it concludes that Database system is performing the different task that
easily manage and control data into organised ways. It has summarised about the Safe life and
health club that make plan for designing and developing database system. it can be determined
the different information and stored in DB such as classes, instructors, members, account,
equipment and so on. It also designed the logical and physical design of entity relationship
diagram that must be representing the overall relational between one or more tables. It also
assumed the important concept in database related security, which has important for maintaining
the security aspects in entire system. Furthermore, it has critically evaluated the security threat in
database and measures the different parameters to protect data and find out the optimisation
process that directly impact on the design and query level within safe or health club database.
28

REFERENCES
Book and Journals
Eshtay, M., Sleit, A. and Aldwairi, M., 2019. IMPLEMENTING BI-TEMPORAL
PROPERTIES INTO VARIOUS NOSQL DATABASE CATEGORIES. International
Journal of Computing. 18(1). pp.45-52.
Huang, X.P. and Leng, J., 2019. Design of Database Teaching Model Based on Computational
Thinking Training. International Journal of Emerging Technologies in Learning. 14(8).
Kraleva, R., Kralev, V. and Sinyagina, N., 2018. Design and Analysis of a Relational Database
for Behavioral Experiments Data Processing. International Journal of Online
Engineering. 14(2).
Kumar, K. and Azad, S.K., 2019. Determining All Possible Candidate Keys for Relational
Database Design. In Innovations in Soft Computing and Information Technology (pp.
241-248). Springer, Singapore.
Link, S. and Prade, H., 2019. Relational database schema design for uncertain data. Information
Systems. 84. pp.88-110.
Novalić, F., Kamberović, H. and Saračević, M., 2019. Application of ORACLE Database Design
in the Creation of Information Systems for Small and Medium Enterprises.
Pohl, S. and Melidis, D.P., 2019. Extending the database system of BACTOME.
Puetz, S.J., 2018. A relational database of global U–Pb ages. Geoscience Frontiers. 9(3). pp.877-
891
Tomar, D. and et.al., 2019. Migration of healthcare relational database to NoSQL cloud database
for healthcare analytics and management. In Healthcare Data Analytics and
Management (pp. 59-87). Academic Press.
Vial, G., 2018. Lessons in persisting object data using object-relational mapping. IEEE Software.
29