IMAT5103 Database Systems and Design: EER Modelling and SQL Solution

This document presents a comprehensive solution to a database systems and design assignment, focusing on EER modeling and SQL. The solution begins with an introduction to database concepts, including redundancy, data inconsistency, and the advantages of a database approach. It includes an EER diagram for ABC's booking database system, detailing entities, attributes, and relationships. The solution then provides reduced relational schemas, logical database design, and steps for database design, including physical, logical, and conceptual design. Detailed entity descriptions with data types and attributes are provided for Client, Room, Administration, Guest, Event, PaymentType, Booking, Payment, and Report tables. SQL commands for table creation, including primary and foreign keys, are presented, along with index creation. Data population using INSERT statements for each table is shown. Finally, the solution includes SQL queries to retrieve and manipulate data, demonstrating filtering, joining, and aggregation techniques. This assignment covers the core concepts of database design and implementation.

Running head: DATABASE SYSTEM AND DESIGN
Database System and Design
Name of the Student:
Name of the University:
Author note:

1DATABASE SYSTEM AND DESIGN
Introduction
A database is a system that helps to manage a set of related information. It can be defined as a
collection of coherent and meaningful data. In an organization multiple users are included
and they share the same recourses. So most of the data is stored repeatedly. Storing same data
multiple times is termed as redundancy which gives several problem such as problame in
updating records, wastage of memory that causes data inconsistency. To maintain large
amount of data in a well-organized manner by using the database approach.
In early days, the applications of the database systems were directly built based on the
traditional file systems. All the system records are stored into files. The traditional operating
system supports the file-processing systems. But the traditional file processing system has
certain disadvantages such as, data inconstancy, data redundancy, accessing data are difficult,
data isolation (different files, formats), integrity problems like insert, modify or delete
constraints, atomicity problem, simultaneous accessing of database by several users, security
problems. The database system provide solutions to overcome all those problems.

2DATABASE SYSTEM AND DESIGN
A conceptual database design for ABC’s bookings database system
EER Diagram
 An entity set or an attribute is used to represents an object.
 Strong and weak entity set are used to represent strong entities and weak entities.
 Generalization and specialization is used for modularity in the database designing.
 Use of binary relations and ternary relations.
 Use of aggregation-aggregate each entity set into a single unit, without having the
details of the internal storage-structure of the database.
 This provides the way to express a real world concept by a relationship set or by a set
of entities.
Reduced Relational Schemas
 Each schema consists of multiple columns generally associated with attributes with
unique names.
 Primary key can be used to define a set of entity and their relationship to express the
content of the database.
 The set of database schemas are used to represent an ER diagram.
 Each entity set has their unique schemas that is associated with corresponding
relationship set or entity set.

3DATABASE SYSTEM AND DESIGN
Logical Database Design
Database Design
These are the following steps when designing a database:
 Physical Design - the physical design describes the physical storage structure for the
database system.
 Logical designing - the logical designing describes the database schemas.
 Conceptual designing - the conceptual designing converts the user requirements to the
conceptual schema.
 User requirements - interactive database can be associated with domains from the
requirement specification of the user.
Entity: Client

4DATABASE SYSTEM AND DESIGN
Column Name Data Type Size Attributes
Client_Id varchar2 6 Primary key ‘first
letter must be C’
FullName varchar2 20 Not Null
Address varchar2 20 Not Null
MobileNo number 20 Not Null
Email varchar2 30 Not Null
Username varchar2 20 Not Null
Password varchar2 10 Not Null
Entity: Room
Column Name Data Type Size Attributes
Room_No varchar2 6 Primary key ‘first
letter must be R’
Room_Type varchar2 20 Not Null
Room_Price varchar2 20 Not Null
Description number 30 Not Null
Entity: Administration
Column Name Data Type Size Attributes
Staff_Id varchar2 6 Primary key ‘first
letter must be S’

5DATABASE SYSTEM AND DESIGN
StaffName varchar2 20 Not Null
Username varchar2 20 Not Null
Password varchar2 30 Not Null
Entity: Guest
Column Name Data Type Size Attributes
Guest _Id varchar2 6 Primary key ‘first
letter must be S’
GuestName varchar2 20 Not Null
No_ Guest number 10 Not Null
Entity: Event
Column Name Data Type Size Attributes
Event _Id varchar2 6 Primary key ‘first
letter must be E’
Event_Name varchar2 20 Not Null

6DATABASE SYSTEM AND DESIGN
Entity: PaymentType
Column Name Data Type Size Attributes
PaymentType _Id varchar2 6 Primary key ‘first
letter must be T’
PaymentType Name varchar2 20 Not Null
Entity: Booking
Column Name Data Type Size Attributes
Booking _Id varchar2 6 Primary key ‘first
letter must be B’
Client_Id varchar2 6 Primary key/Foreign
key references
Client_Id of the
Client table
Event_Id varchar2 6 Primary key/Foreign
key references
Event_Id of the
Event table

7DATABASE SYSTEM AND DESIGN
Room_No number 20 Not Null
Arrival_Date Date 30 Not Null
Depurture_Date Date 20 Not Null
Breakfast varchar2 10 Not Null
Payment_Id varchar2 6 Primary key/Foreign
key references
Payment_Id of the
Even Payment t
table
Staff_Id varchar2 6 Primary key/Foreign
key references
Staff_Id of the
Administartion table
Entity: Payment
Column Name Data Type Size Attributes
Payment_Id varchar2 6 Primary key ‘first
letter must be P’
Date Date
Amount number 10 Primary key/Foreign
key references
Event_Id of the
Event table
PaymentType_Id number 20 Primary key/Foreign

8DATABASE SYSTEM AND DESIGN
key references
PaymentType_Id of
the PaymentType
table
Client_Id varchar2 6 Primary key/Foreign
key references
Client_Id of the
Client table
Booking_Id varchar2 6 Primary key/Foreign
key references
Booking_Id of the
Bookingtable
Staff_Id varchar2 6 Primary key/Foreign
key references
Staff_Id of the
Administartion table
Entity: Report
Column Name Data Type Size Attributes
Client_Id varchar2 6 Primary key/Foreign
key references
Client_Id of the
Client table
Booking_Id varchar2 6 Primary key/Foreign

9DATABASE SYSTEM AND DESIGN
key references
Booking_Id of the
Booking table
Payment_Id varchar2 6 Primary key/Foreign
key references
Payment_Id
of the Payment table
Tables Creation
SQL create table command is used create a table.
Client Table:
CREATE TABLE Client(
Client_Id varchar2(6),
FullName varchar2(6) NOT NULL,
Address varchar2(20),
MobileNo number(20),
Email varchar2(30),
Username varchar2(20),
Password varchar2(10),
primary key (Client_Id)

10DATABASE SYSTEM AND DESIGN
);
CREATE INDEX ClientName ON Clients(FullName);
Room Table:
CREATE TABLE Room(
Room_No varchar2(6),
Room_Type varchar2(6) NOT NULL,
Room_Price number(20),
Description varchar2(20),
primary key ( Room_No)
);
Administration Table:
CREATE TABLE Administration(
Staff_Id varchar2(6),
Staff_Name varchar2(6),
primary key ( Staff_Id)
);
Guest Table:
CREATE TABLE Guest(
Guest_Id varchar2(6),

11DATABASE SYSTEM AND DESIGN
Guest_Name varchar2(6),
Guest_No number(10),
primary key ( Guest_Id)
);
Event Table:
CREATE TABLE Event(
Event_Id varchar2(6),
Event_Name varchar2(20),
primary key (Event_Id)
);
PaymentType Table:
CREATE TABLE PaymentType(
PaymentType_Id varchar2(6),
PaymentTypeName varchar2(20),
primary key (PaymentType_Id)
);
Booking Table:
CREATE TABLE Booking(
Booking_Id varchar2(6),