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),

12DATABASE SYSTEM AND DESIGN
Amount number(20 ),
Client_Id varchar2(6),
Event_Id varchar2(6),
Room_No number (8),
Arrival_Date date,
Depurture_Date date,
Staff_Id varchar2(6),
primary key (Booking_Id),
FOREIGN KEY (Client_Id) REFERENCES Client(Client_Id),
FOREIGN KEY (Event_Id) REFERENCES Event(Event_Id),
FOREIGN KEY (Staff_Id) REFERENCES Administration(Staff_Id)
);
Payment Table:
CREATE TABLE Payment(
Payment_Id varchar2(6),
Payment_date date,
Amount number(20),
PaymentType_Id varchar2(6),

13DATABASE SYSTEM AND DESIGN
Client_Id varchar2(6),
Booking_Id varchar2(6),
Staff_Id varchar2(6),
primary key (Payment_Id),
FOREIGN KEY (PaymentType_Id) REFERENCES PaymentType(PaymentType_Id),
FOREIGN KEY (Client_Id) REFERENCES Client(Client_Id),
FOREIGN KEY (Booking_Id) REFERENCES Booking(Booking_Id),
FOREIGN KEY (Staff_Id) REFERENCES Administration(Staff_Id)
);
Report Table:
CREATE TABLE Report(
Client_Id varchar2(6),
Booking_Id varchar2(6),
Payment_Id varchar2(6),
FOREIGN KEY (Client_Id) REFERENCES Client(Client_Id),
FOREIGN KEY (Booking_Id) REFERENCES Booking(Booking_Id),
FOREIGN KEY (Payment_Id) REFERENCES Payment(Payment_Id)
);
Index Creation

14DATABASE SYSTEM AND DESIGN
1. CREATE INDEX clientname ON client (fullname);
2. CREATE INDEX eventname ON Event (Event_Name);
3. CREATE INDEX type ON Room (ROOM_TYPE);
4. CREATE INDEX arrival_date ON Booking (ARRIVAL_DATE);
Data Population
Inserting data into Client table:
insert into client values('C001','Steve Jobs','New
York',898876678,'[e-mail]','steve0999','0999jobs')
insert into client values('C002','Alan Walker','New
York',899086678,'[e-mail]','alan0899','0899walker')
insert into client values('C003','Robin
Smith','Australia',709876678,'[e-mail]','robin0979','0979smith')
insert into client values('C004','Ivan
Bayross','Chicago',698876078,'[e-mail]','ivan0999','099bayross')
Inserting data into Room table:
insert into room values('R001','Single',20000)

15DATABASE SYSTEM AND DESIGN
insert into room values('R002','Double',35000)
insert into room values('R003','Triple',45000)
insert into room values('R004','Quad',50000)
insert into room values('R005','Twin',450000)
Inserting data into Administration table:
insert into administration values('STF001','Preksa Jain')
insert into administration values('STF002','John Poul')
insert into administration values('STF003','Zyam Preet')
insert into administration values('STF004','Diksa Kaur')
Inserting data into Event table:
insert into event values('E001','wedding events')

16DATABASE SYSTEM AND DESIGN
insert into event values('E002','business events')
insert into event values('E003','themed parties')
insert into event values('E004','individual stays')
Inserting data into Guest table:
insert into guest values('GUE001','John',40)
insert into guest values('GUE002','Ivan',5)
insert into guest values('GUE003','Katy',2)
insert into guest values('GUE004','Chris',1)
Inserting data into Booking table:
insert into booking values('B001','C002','E004','27-Aug-19','29-Aug-19','STF001','40000')

17DATABASE SYSTEM AND DESIGN
insert into booking values('B002','C001','E001','01-Oct-19','02-Oct-19','STF002','100000')
insert into booking values('B003','C001','E004','28-Aug-19','01-Sep-19','STF001','80000')
insert into booking values('B004','C004','E002','06-Sep-19','08-Sep-19','STF003','150000')
insert into booking values('B005','C003','E003','12-Dec-19','15-Dec-19','STF004','200000')
Inserting data into PaymentType table:
insert into paymenttype values('PT001','Cash Payement')
insert into paymenttype values('PT002','Credit Card Payment')
insert into paymenttype values('PT003','Debit Card Payment')
insert into paymenttype values('PT004','Voucher Payment')
Inserting data into Payment table:
insert into payment values('P001','26-Feb-2019','PT001','C001','B003','STF001',80000)

18DATABASE SYSTEM AND DESIGN
insert into payment values('P002','20-Jan-2019','PT002','C002','B001','STF001',40000)
insert into payment values('P003','10-Mar-2019','PT003','C003','B005','STF004',200000)
insert into payment values('P004','26-Feb-2019','PT002','C004','B004','STF003',150000)
insert into payment values('P005','20-Jan-2019','PT001','C001','B001','STF002',100000)
SQL Query
Query 1.: select fullname from client where Address='New York' order by fullname;

19DATABASE SYSTEM AND DESIGN
Query 2.: select client.fullname, client.address, client.mobileno, booking.Event_ID from
client inner join booking on client.CLIENT_ID=booking.CLIENT_ID;
Query 3.:select * from booking where ARRIVAL_DATE between '01-Aug-19' AND '31-
Aug-19';
Query 4.: select * from Guest where GUEST_NAME like 'K%' or GUEST_NAME like '%n' ;
Query 5.: select payment.STAFF_ID , payment.BOOKING_ID, client.client_id,
client.fullname, client.address, client.mobileno from payment full outer join client on
payment.client_Id=client.CLIENT_ID where amount >= 100000;

20DATABASE SYSTEM AND DESIGN
Query 6.: Select * from payment where amount in (select max(amount) from payment);