Database Management System: Hotel Management System Case Study

Desklib provides past papers and solved assignments for students. This project details a hotel management database.

Database Management System

Table of Contents
Database Management System..................................................................................................1
Table of Figure...........................................................................................................................3
List of Tables.............................................................................................................................4
Introduction................................................................................................................................5
LO1 Analyze different types of database management systems................................................6
P1 Compare and contrast the different types of database models..........................................6
M1 Assess how relational database models and the process of normalisation can provide
reliable and efficient data structures......................................................................................9
LO2 Design a database management system using a relational model to meet client
requirements.............................................................................................................................14
P2 Produce a design for a relational database management system to meet client
requirements.........................................................................................................................14
M2 Analyse how the design will optimise system performance.........................................16
LO3 Develop a database management system using a suitable platform................................18
P3 Develop a fully functional system which meets client and system requirements, using
an open source language (with an application software e.g. MySQL with front end
Microsoft Access)................................................................................................................18
P4 Test the system for functionality and performance........................................................23
M3 Implement effective features in the solution to handle concurrency, security, user
authorisations and data recovery..........................................................................................24
LO4 Demonstrate the system administration and management tools available on the chosen
platform....................................................................................................................................26
P5 demonstrate the tools available in the system to monitor and optimise system
performance, and examine the audit logs.............................................................................26
P6 Demonstrate the tools available in the system to manage security and authorizations.. 28

M4 Assess the effectiveness of the system administration and management tools available
on the platform identifying any shortcomings of the tools..................................................29
Conclusion...............................................................................................................................31
Reference List..........................................................................................................................32
Table of Figure
Figure 1 – Network Model.........................................................................................................6
Figure 2 – Relational Model......................................................................................................7
Figure 3 – Object oriented database model................................................................................8
Figure 4 – Hierarchical Database Model...................................................................................8
Figure 5: Normalisation...........................................................................................................12
Figure 6: Normalisation Form..................................................................................................13
Figure 7: ERD Diagram...........................................................................................................15
Figure 8: ERD Diagram...........................................................................................................15
Figure 9: Hotel Form Data.......................................................................................................18
Figure 10: Hotel Form..............................................................................................................18
Figure 11: Employee Form......................................................................................................19
Figure 12: Employee Form Data..............................................................................................19
Figure 13: reservation form data..............................................................................................20
Figure 14: reservation form......................................................................................................20
Figure 15: Guest Form.............................................................................................................21
Figure 16: Guest Form Data.....................................................................................................21
Figure 17: Billing Form...........................................................................................................22
Figure 18: Billing Form Data...................................................................................................22
Figure 19: Room Data Form....................................................................................................23
Figure 20: Room Data Type.....................................................................................................23

Figure 21: Main Slide..............................................................................................................26
Figure 22: Database effectiveness............................................................................................27
Figure 23: Securely Inserting Data..........................................................................................28
Figure 24: Database Tracing....................................................................................................29
List of Tables
Table 1: Testing Table.............................................................................................................23
Table 2: Software for Back up Table.......................................................................................25

Introduction
Database management system is used for managing data or performing operation onto
relation and tuples. Here, in this assignment it is having various different database models
with their properties, specification & flow chart diagram also. Relational model is the most
common and famous database model, in relational model tables sort data which is called as
relations. Here relation means the combination of data with each another and consisting of
row and columns. After that we have choose one case study that is hotel management system.
In this, it is also having normalization rules up to third normal form. After that we have to
discuss about security aspects as well as tools which are used for optimization and analyzing
features. At the end we have to check and analyze effectiveness using power point
presentation with screenshot into report documentation file.

LO1 Analyze different types of database management systems
P1 Compare and contrast the different types of database models.
Individual database models are designed based on the rules and concepts of whichever
broader data model the designers adopt.
Database logical structure is being depicted by the choices of selection of different database
models. This also inculcates about the constraints and relationships that depicts the methods
of how a particular data could be accessed and stored. There are different types of database
present which could be used based upon the requirement of handling data. These database
models are –
Network Model
In network model a record could have more than one parent record implying towards that its
build upon hierarchical model thus further allowing for many to many relationship. Network
model is constructed and build with interrelated set of records which is according to the
mathematical theory of set.
Figure 1 – Network Model
Available at: https://d2slcw3kip6qmk.cloudfront.net/marketing/pages/chart/seo/database/discovery/network-model.svg
Relational Model

Relational model is the most common and famous database model, in relational model tables
sort data which is called as relations. Here relation means the combination of data with each
another and consisting of row and columns.
Tuples are basically the row of data and contains data interrelated with each other. For
example data related to each individual employee.
Relational database model also is responsible for the type of relation between table like one
to many relationship, one to one relationship and lastly many to many relationship.
Figure 2 – Relational Model
Available at: https://d2slcw3kip6qmk.cloudfront.net/marketing/pages/chart/seo/database/discovery/relational-model.svg
Object oriented database model
Database is defined based upon the objects as goes by the name where each record is an
object and database is a group of reusable elements of software, of objects collectively.
Object oriented database with methods and features also have support for different types of
databases like hypertext database and multimedia database.
Object oriented database or also hybrid database not only database table limited but also is
well famous for the post relation model of database.

Figure 3 – Object oriented database model
Available at: https://d2slcw3kip6qmk.cloudfront.net/marketing/pages/chart/seo/database/discovery/object-oriented-
model.svg
Hierarchical Model
This model is good for describing many real-world relationships.
In hierarchical model of database each and every record only has one parent or we can say
root element of it. As the data is present in this structure in a form like inverted tree. Each and
every sibling or child record is particularly sorted. That order for storing of data is defined by
the sorting order of each record. Real world data inter-reliability is well managed and stored
by this database model.
Figure 4 – Hierarchical Database Model
Available at: https://d2slcw3kip6qmk.cloudfront.net/marketing/pages/chart/seo/database/discovery/hierarchical-model.svg
(Lucid chart, 2018)

M1 Assess how relational database models and the process of normalisation can
provide reliable and efficient data structures.
Relational Database RD
Database is the collection of the relations where the relations is nothing, it just the tables, to
which they are interrelated by something call Primary key, composite key etc. There are
relationships present they are one to many, many to one etc. This model is the primary model
which is used to store the data and process the data according to it the model gives the
effective way to select the database and process it in effective manner.
The concepts of the Relational database are:
1) Relation keys: Every table will have the key which will uniquely identify the row which
will differ it from other rows.
2) Relation schema: It helps to show the table’s names, attributes and the name of the
attributes.
3)Table: This helps to store the entities or the data of the organisations .The table consist of
the rows and columns where the row indicated to the records and the column indicates to the
attributes of the tables.
4) Relation instance: This is the finite in number and also there should be no replicate of the
tuples.
5) Attribute domain: attributes have their scope which is predefined and known as the
attribute domain.
6) Tuples: there are the row in the table which have records for the relation to the other
tables.
Advantages of the database:
The data is divided into different categories which helps to understand easily and help to
update the information very easily and which helps to create the new tables if needed.
The data is accurate.
The data is flexible and trusted as well as secure...

Cod’s Rule for the RDBMS
Rule zero:
It qualify the system as RDBMS, with the help of relational capabilities manages the entirely
database (Studytonight.com, 2019).
Rule 1: Information Rule
Here the column and the rows should be unordered strictly .along with the information it
includes the metadata which is stored in the cell tables.
Rule 2: Guaranteed Access
The data should be accessible through the Table Name + primary Key which is in row
+Attribute which is in column.
Rule 3: Systematic treatment of the null
The use of null means that there is no data, no value. It should be consistently used. The
primary key should be not null always and the representation of null is “NULL”.
Rule 4: Active Online Catalo
The complete database is describe by the catalo which must be stored online only. The catalo
have the same rule as the rules of the database .The query should be also same as the
database.
Rule 5: Powerful and Well-structure language
There should be the specific one language to describe the data stored in the database .such as
SQL.
Rule 6: View updating Rule
All the update should be viewed by the syste4m in the well manner
Rule 7: Relational Level Operation
The relation must be Delete, update, insert at each level of relations. There are some
operation used such as Union, Intersection and the minus.
Rule 8: Physical Data Independence

The physical storage is not useful for the database, this means if file is transferred into other
disk or renamed there should be no change in the entire applications or the database.
Rule 9: Logical Data independence
There should be no change in the users view if there is internal change is done in the database
Example if there are two tables’ splits into the new tables then the new view is the join of the
other row tables
Rule 10: Integrity Independence
To make the RDBMS independent there should be the key store in the data dictionary.
Rule 11: Distribution Independence
A distribution of the network should be properly so that there should be impression on the
end users.
Rule 12: No subversion Rule
The low level should be subvert or bypass integrity rule to the data.
Normalisation:
There normalisation is the technique to remove the redundancy of the data.
There are commonly used normal forms used:
1) 1NF
2)2NF
3) NF
4) BCNF
1NF:
The rule of this normalisation is that the tables should not have the multiple value, the values
should be atomic that should follows the atomicity.
2NF:
In this first there should be 1NF removed, this says that no prime attributes is dependent on
the candidate key.

3NF:
The transitive Functional dependency of the non-prime should be removed
BCNF:
It stands for the Boyce Codd Normal Form, this the up gradation of the 3NF, BCNF says that
every functional dependency should consist of the super key.
Example of normalisation
Question:
Figure 5: Normalisation
Answer: