Introduction to Databases: Challenge UK ER Modeling and Normalization

This report provides a comprehensive analysis of the database design for Challenge UK, a small activity holiday specialist. The report begins with a top-down approach to create an Entity-Relationship (ER) diagram, identifying entities such as Course, Customer, Booking, Invoice, Guest, Employees, Hostel, Room, and Payment, along with their attributes and relationships. The relationships between entities are clearly defined, including one-to-one and one-to-many relationships. Following the ER modeling, the report normalizes the ERD using UNF, 1NF, 2NF, and 3NF, ensuring data integrity and efficiency. Finally, the report details the implementation phase, including table creation with SQL scripts for Customer, Employees, Guest, Bookings, Invoices, Course, Hostel, Room, and Payment tables. The report also includes a data dictionary, data population examples, and SQL queries to demonstrate the functionality of the database, addressing the information needs of Challenge UK for course booking and activity demand analysis. The report concludes with a bibliography of the resources used.

Running head: DATABASE CONCEPTS – CHALLENGE UK
DATABASE CONCEPTS – CHALLENGE UK
Name of the Student
Name of the University
Author Note

1DATABASE CONCEPTS – CHALLENGE UK
Table of Contents
Top Down approach to produce ER diagram..................................................................................2
Providing all entities in the “course booking system” of Challenge UK (Top down method).......4
Providing the Relationships between Entities:................................................................................5
Normalize ERD...............................................................................................................................7
Final Schema...................................................................................................................................9
Implementation................................................................................................................................9
Table creation..............................................................................................................................9
Data Dictionary..........................................................................................................................13
Data Population.........................................................................................................................20
Query.........................................................................................................................................22
Bibliography..................................................................................................................................28

2DATABASE CONCEPTS – CHALLENGE UK
Top Down approach to produce ER diagram
The ER model is a method of creating a graphical model or a template of a database. This
diagram is able to provide the real-world objects by using entity and relationship. An entity can
be anything that is related to the real world. An entity can have several properties and entity
properties contain values. Every database designer or developer creates the ER model before
implementing the database. ER model helps a developer to fulfill the user requirement. Every
user, businessman or stakeholder is able to understand the ER model. This is the one type of
communication tool that is understandable by all users. ER model can be transformed into a
relational table and it helps to increase the productivity of a user.
To develop a complete ER model for a course booking system database, a few steps are
needed to follow. The following steps are shared below: -
 Developer needs to identify all entities and the relationship between them.
 Every entity must have a name. every attribute should have a name and their
unique key that a normal user can understand.
 All relationships must connect with the entities, not with the attributes. If a
developer connects the relationship among attributes it is confusing for a normal
user.
 Providing attributes in the entity must have a unique name. This process will help
a user to understand the entities and their attributes.

3DATABASE CONCEPTS – CHALLENGE UK
Figure 1: Entity Relationship diagram
Source: created by author

4DATABASE CONCEPTS – CHALLENGE UK
Providing all entities in the “course booking system” of Challenge UK (Top
down method)
 Course: The course entity contains every information of a particular course. Every
course can be uniquely identified by their courseID and its data type is varchar. Datatype
of course name and location is varchar. Startdate holds date datatype and priceAdult,
PriceChild has decimal data type.
 Customer: The customer entity of this diagram is able to hold every information of a
customer. CustNo is the varchar datatype and it is the primary key in this table. Datatype
of Customer name and address is varchar and datatype of contact attribute is int.
 Booking: Boking entity is one of the most important entity in this database. Book id is a
foreign key in booking entity and its datatype is varchar. Status attribute is able to hold
the information of booking status and its data type is varchar. Custno, CourseID, ClerkID
is and its datatype is the date. TotalPlacesBooked is able to hold a total number of book
places and varchar and this is the foreign key in this table. BookingDate can store the
booking date data type of this attribute is int.
 Invoice: Every information on the invoice is stored in this entity. Every invoice has a
unique invoice number. It is the primary key in this entity and its datatype is varchar. The
data type of BookingID is varchar. BookingID is the foreign key in this entity and its data
type is varchar. Date attribute can hold the invoice date and its data type is date. Datatype
of the Total attribute is decimal.
 Guest: This entity can store every information of a guest. Every guest has their unique
guest id number and the data type of guest id is varchar. It is the primary key in this

5DATABASE CONCEPTS – CHALLENGE UK
entity. The datatype of Name, Gender, Address, DietPreferences, medical history,
RoomID, and bookingID is varchar. The foreign key in this entity is roomID and booking
ID.
 Employees: Employee entity holds every information of an employee. Every employee
has a unique employee id number. This the primary key in this entity. Data type of name,
email and role is varchar and contact number are int.
 Hostel: Every information about the hostel is stored in this entity. Hostel entity contains
three attributes, HostelID, Location, HostelManagerID and data types of these attributes
are varchar. The primary key in the hostel table is HostelID and Hostel manager id is a
foreign key in this entity.
 Room: Every room details of this system are stored in this entity. RoomID is the primary
key and its data type is varchar. noofBed contains bed number in every room and its data
type is int. HotelID is a varchar data type and it is the foreign key in Room entity.
 Payment: This entity holds every information of payment. Every payment is uniquely
identified by their payment id. The data type of payment id is varchar. In the payment
entity, AmountDue indicates the due money and data type of this attribute is decimal.
Paymentdate indicates the date of a payment. The data type of payment date is date.
PaymentMode is able to store the payment method and the data type of this attribute is
varchar. In this entity, the invoice number is the foreign key and the data type of
invoiceno is varchar.
Providing the Relationships between Entities:
Customer – Booking: Every customer is identified by their unique id number. One or
more customer can have only one booking.

6DATABASE CONCEPTS – CHALLENGE UK
Above scenario provides the one to one-and-only-one relationship. One or more customer can
have only one booking id.
Invoice – booking: The relationship between these entities is one to one. Every invoice
is identified by its unique invoice id number. In every invoice, one booking id is mentioned.
Invoice – payment: Every payment has only one invoice and every invoice number is
mentioned in the payment table. Every payment has a unique payment number.
The relationship between the above entities are one- to- one.
Guest – Booking: Every guest is identified by their unique guest ID. Only one guest is
able to book one room. The relationship between booking and guest is one to one-and-only-one
relationships.
Room – Guest: Every room has its unique room id number and it can hold one or many
guests. The relationship between room and guest is one to many.
Hostel – room: Every Hostel has its unique id number. One Hostel can have many
rooms. The relationship between the Hostel and the room is one-to-many.
Employee – Booking: Every employee has their unique id number. An employee of
Challenge UK can be a staff or clerk. Clerk has its id number.
Above scenario provides the one to one-and-only-one relationship

7DATABASE CONCEPTS – CHALLENGE UK
Normalize ERD
The ERD has been normalized with the help of the forms available for the course
booking system in Challenge UK.
Form Name UNF 1NF 2NF 3NF
Booking
Form
Booking clerk
Course id
Customernumb
er
Customer
name
Booking date
Location
CoursestartDat
e
Totalplacesboo
ked
Course name
Status
Booking
BookingId(PK)
clerkid
Custno
custname
Booking date
Totalplacesbooked
Status
CourseID
CourseName
Location
StartDate
Booking
BookingID(PK)
Booking clerk
Course id(FK)
Custno(FK)
Booking date
Totalplacebooked
Status
Course
CourseID(PK)
CourseName
Location
StartDate
Customer
CustomerID(PK)
Name
Address
Contact
Booking
BookingID(PK)
Booking clerk
Course id(FK)
Custno(FK)
Booking date
Totalplacesbooked
Status
Course
CourseID(PK)
CourseName
Location
StartDate
Customer
CustomerID(PK)
Name
Address
Contact

8DATABASE CONCEPTS – CHALLENGE UK
Payment
Details
Amount Due
Payment Mode
Invoice no
Payment date
Payment
PaymentID(PK)
Amount Due
Payment Mode
Payment date
InvoiceNo
Payment
PaymentID(PK)
Amount Due
Payment Mode
Payment Date
InvoiceNo(FK)
Payment
PaymentID(PK)
Amount Due
Payment Mode
Payment Date
InvoiceNo(FK)
Cancellation
Form
Cancellation
Number
Booking No
Date
Refund Due
Cancellation
Cancellation
number(PK)
Date
Refund Due
Booking no
Cancellation
Cancellation
number(PK)
Date
Refund Due
Booking no(FK)
Cancellation
Cancellation
number(PK)
Date
Refund Due
Booking no(FK)
Invoice form Booking No
Invoice no
Invoice date
Total Due
Invoice
Invoice No(PK)
Booking no
Invoice Date
Total Due
Invoice
Invoice No(PK)
Booking no(FK)
Invoice Date
Total Due
Invoice
Invoice No(PK)
Booking no(FK)
Invoice Date
Total Due
Course
record
spreadsheet
report
Start date
Course id
Price adult
Price child
Course
Course ID(PK)
Course Name
Start date
Location
Price Adult
Price Child
Course
Course ID(PK)
Course Name
Start date
Location
Price Adult
Price Child
Course
record
Spreadsheet
CourseID
Corse Name
Location
Course
CourseID(PK)
Course Name
Course
Course ID(PK)
Course Name
Course
Course ID(PK)
Course Name

9DATABASE CONCEPTS – CHALLENGE UK
Start Date
Guest Name
BookingNo
Gender
Special
Requirements
Location
Start Date
Price Adult
Price Child
Guest
GuestID(PK)
Guestname
Gender
Address
Diet preferences
Medical History
BookingID
Start date
Location
Price Adult
Price Child
Guest
GuestID(PK)
Guestname
Gender
Address
Diet preferences
Medical History
BookingID(FK)
Start date
Location
Price Adult
Price Child
Guest
GuestID(PK)
Guestname
Gender
Address
Diet preferences
Medical History
BookingID(FK)
Accommodat
ion Plan
Hostel
Room No
Bed
Location
Accomodation
ID(PK)
RoomID
Hostel
Hostelname(PK)
Location
Room
RoomID(PK)
No of rooms
HostelName
Accomodation
ID(PK)
RoomID(FK)
EmpID(FK)
GuestID(FK)
Hostel
Hostelname(PK)
Location
Room
RoomID(PK)
No of rooms
Hostel
Hostelname(PK)
Location
hostelmanagerId(FK)
Room
RoomID(PK)
No of rooms
HostelName(FK)

10DATABASE CONCEPTS – CHALLENGE UK
HostelName(FK)
Booking
clerk (Field
from
booking
form)
Booking clerk Employee
EmpID(PK)
Name
Contact
Email
Role
Employee
EmpID(PK)
Name
Contact
Email
Role
Employee
EmpID(PK)
Name
Contact
Email
Role
UNF to 1NF
The sample data and reports have been mapped to produce UNF structure to a database 1NF
(First Normal Form) structure. For example, the fields from the booking fields have been
mapped to the attributes in the booking entity. The fields selected from the booking forms are
Booking date, CourseID, total places booked for direct mapping. The customer related fields
have been mapped by using the customer entity. The First Normal form states that a table must
have all atomic values and the Relational model developed fulfills the criteria of 1NF. Hence it
can be said that first normal form is achieved.
1NF to 2NF
Second Normal form states that the attributes should have dependency on the key attribute of the
table where the concept of primary key comes into the scenario. For example, the functional
dependency between the customer attributes have been shown below:

11DATABASE CONCEPTS – CHALLENGE UK
2NF to 3NF
Lastly, the Third normal form explains that there should not be any transitive functional
dependencies between the attributes. The Accommodation table has been eliminated hence it
contains the transitive dependencies and the table room, guest and staffs of the company can be
associated. For example, in accommodation entity
[Meal token] does not -> [GuestID]
[Meal token] does not -> [EmpID]
[Meal token] does not -> [CustID]

12DATABASE CONCEPTS – CHALLENGE UK
Figure 2: Normalized Entity Relationship diagram
Source: created by author

13DATABASE CONCEPTS – CHALLENGE UK
Final Schema

14DATABASE CONCEPTS – CHALLENGE UK
Figure 3: Physical schema of implemented ERD
Source: created by author
Implementation
Table creation
Customer
CREATE TABLE `customers` (
`CustNo` varchar(11) NOT NULL,
`CustName` varchar(50) NOT NULL,
`CustAddress` varchar(50) NOT NULL,
`Contact` int(10) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
ALTER TABLE `customers`
ADD PRIMARY KEY (`CustNo`),
ADD UNIQUE KEY `Contact` (`Contact`);
Employees
CREATE TABLE `employees` (
`EmpID` varchar(20) NOT NULL,
`Name` varchar(50) NOT NULL,
`Contact` int(10) NOT NULL,
`Email` varchar(30) NOT NULL,
`Role` varchar(25) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
ALTER TABLE `employees`
ADD PRIMARY KEY (`EmpID`),
ADD UNIQUE KEY `phone` (`Contact`);
Guest
CREATE TABLE `guest` (

15DATABASE CONCEPTS – CHALLENGE UK
`GuestID` varchar(10) NOT NULL,
`Name` varchar(40) NOT NULL,
`Age` int(11) NOT NULL,
`Gender` varchar(10) NOT NULL,
`Address` varchar(50) NOT NULL,
`DietPreferences` varchar(50) NOT NULL,
`MedicalHistory` varchar(100) NOT NULL,
`RoomID` varchar(10) NOT NULL,
`BookingID` varchar(10) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
ALTER TABLE `guest`
ADD PRIMARY KEY (`GuestID`),
ADD KEY `fk018` (`RoomID`),
ADD KEY `fk0123` (`BookingID`);
ALTER TABLE `guest`
ADD CONSTRAINT `fk0123` FOREIGN KEY (`BookingID`) REFERENCES
`bookings` (`BookingID`),
ADD CONSTRAINT `fk018` FOREIGN KEY (`RoomID`) REFERENCES `room`
(`RoomID`);
Bookings
CREATE TABLE `bookings` (
`BookingID` varchar(10) NOT NULL,
`BookingDate` date NOT NULL,
`Status` varchar(50) NOT NULL,
`TotalPlacesBooked` int(10) NOT NULL,
`custno` varchar(10) NOT NULL,
`ClerkID` varchar(10) NOT NULL,
`CourseID` varchar(10) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
ALTER TABLE `bookings`
ADD PRIMARY KEY (`BookingID`),
ADD KEY `fk_1` (`ClerkID`),
ADD KEY `fk_2` (`custno`),
ADD KEY `fk_3` (`CourseID`);
ALTER TABLE `bookings`
ADD CONSTRAINT `fk_1` FOREIGN KEY (`ClerkID`) REFERENCES `employees`
(`EmpID`),
ADD CONSTRAINT `fk_2` FOREIGN KEY (`custno`) REFERENCES `customers`
(`CustNo`),

16DATABASE CONCEPTS – CHALLENGE UK
ADD CONSTRAINT `fk_3` FOREIGN KEY (`CourseID`) REFERENCES `course`
(`CourseID`);
Invoices
CREATE TABLE `invoice` (
`InvoiceNo` varchar(10) NOT NULL,
`BookingID` varchar(10) NOT NULL,
`Date` date NOT NULL,
`Total` decimal(8,2) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
ALTER TABLE `invoice`
ADD PRIMARY KEY (`InvoiceNo`),
ADD KEY `BookingID` (`BookingID`);
ALTER TABLE `invoice`
ADD CONSTRAINT `invoice_ibfk_1` FOREIGN KEY (`BookingID`) REFERENCES
`bookings` (`BookingID`);
Courses
CREATE TABLE `course` (
`CourseID` varchar(10) NOT NULL,
`CourseName` varchar(50) NOT NULL,
`Location` varchar(50) NOT NULL,
`StartDate` date NOT NULL,
`PriceAdult` decimal(8,2) NOT NULL,
`PriceChild` decimal(8,2) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
ALTER TABLE `course`
ADD PRIMARY KEY (`CourseID`);
Hostel
CREATE TABLE `hostel` (
`Hostelname` varchar(10) NOT NULL,
`Location` varchar(70) NOT NULL,
`HostelManagerID` varchar(10) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

17DATABASE CONCEPTS – CHALLENGE UK
ALTER TABLE `hostel`
ADD PRIMARY KEY (`Hostelname`),
ADD KEY `fk0` (`HostelManagerID`);
ALTER TABLE `hostel`
ADD CONSTRAINT `fk` FOREIGN KEY (`HostelManagerID`) REFERENCES
`employees` (`EmpID);
Room
CREATE TABLE `room` (
`RoomID` varchar(10) NOT NULL,
`NoofBeds` int(10) NOT NULL,
`HostelName` varchar(10) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
ALTER TABLE `room`
ADD PRIMARY KEY (`RoomID`),
ADD KEY `fk_6` (`HostelName`);
ALTER TABLE `room`
ADD CONSTRAINT `fk_6` FOREIGN KEY (`HostelName`) REFERENCES `hostel`
(`Hostelname`);
Data Dictionary
Bookings
Column Type Null Default Links to Comments MIME
BookingID (Primary
)
varchar(10) No
BookingDate date No

18DATABASE CONCEPTS – CHALLENGE UK
Status varchar(50) No
TotalPlacesBooked int(10) No
custno varchar(10) No customers -> CustNo
ClerkID varchar(10) No employees -> EmpID
CourseID varchar(10) No course -> CourseID
Indexes
Keyname Type Unique Packed Column Cardinality Collation Null Comment
PRIMARY BTREE Yes No BookingID 5 A No
fk_1 BTREE No No ClerkID 5 A No
fk_2 BTREE No No custno 5 A No
fk_3 BTREE No No CourseID 5 A No
course
Column Type Null Default Links to Comments MIME

19DATABASE CONCEPTS – CHALLENGE UK
CourseID (Primary
)
varchar(10) No
CourseName varchar(50) No
Location varchar(50) No
StartDate date No
PriceAdult decimal(8,2) No
PriceChild decimal(8,2) No
Indexes
Keyname Type Unique Packed Column
Cardinalit
y
Collation Null Comment
PRIMAR
Y
BTREE Yes No CourseID 9 A No
customers
Column Type Null Default Links to Comments MIME

20DATABASE CONCEPTS – CHALLENGE UK
CustNo (Primary
)
varchar(11) No
CustName varchar(50) No
CustAddress varchar(50) No
Contact int(10) No
Indexes
Keyname Type Unique Packed Column Cardinality Collation Null Comment
PRIMAR
Y
BTREE Yes No CustNo 5 A No
Contact BTREE Yes No Contact 5 A No
employees
Column Type Null Default Links to Comments MIME
EmpID (Primary
)
varchar(20) No

21DATABASE CONCEPTS – CHALLENGE UK
Name varchar(50) No
Contact int(10) No
Email varchar(30) No
Role varchar(25) No
Indexes
Keyname Type Unique Packed Column Cardinality Collation Null Comment
PRIMAR
Y
BTREE Yes No EmpID 7 A No
phone BTREE Yes No Contact 7 A No
guest
Column Type Null Default Links to Comments MIME
GuestID (Primary
)
varchar(10) No
Name varchar(40) No

22DATABASE CONCEPTS – CHALLENGE UK
Age int(11) No
Gender varchar(10) No
Address varchar(50) No
DietPreferences varchar(50) No
MedicalHistory varchar(100) No
RoomID varchar(10) No room -> RoomID
BookingID varchar(10) No bookings -> BookingID
Indexes
Keyname Type Unique Packed Column Cardinality Collation Null Comment
PRIMARY BTREE Yes No GuestID 5 A No
fk018 BTREE No No RoomID 5 A No
fk0123 BTREE No No BookingID 5 A No
hostel
Column Type Null Default Links to Comments MIME

23DATABASE CONCEPTS – CHALLENGE UK
Hostelname (Primary
)
varchar(10) No
Location varchar(70) No
HostelManagerID varchar(10) No employees -> EmpID
Indexes
Keyname Type
Uniqu
e
Packe
d
Column
Cardinalit
y
Collatio
n
Nul
l
Commen
t
PRIMAR
Y
BTRE
E
Yes No Hostelname 2 A No
fk0
BTRE
E
No No
HostelManagerI
D
2 A No
invoice
Column Type Null Default Links to Comments MIME

24DATABASE CONCEPTS – CHALLENGE UK
InvoiceNo (Primary
)
varchar(10) No
BookingID varchar(10) No
bookings ->
BookingID
Date date No
Total decimal(8,2) No
Indexes
Keyname Type Unique Packed Column Cardinality Collation Null Comment
PRIMARY BTREE Yes No InvoiceNo 5 A No
BookingID BTREE No No BookingID 5 A No
room
Column Type Null Default Links to Comments MIME
RoomID (Primary
)
varchar(10) No

25DATABASE CONCEPTS – CHALLENGE UK
NoofBeds int(10) No
HostelName varchar(10) No hostel -> Hostelname
Indexes
Keyname Type Unique
Packe
d
Column Cardinality Collation Null Comment
PRIMAR
Y
BTREE Yes No RoomID 5 A No
fk_6 BTREE No No HostelName 5 A No
Data Population
Customer
INSERT INTO `customers` (`CustNo`, `CustName`, `CustAddress`,
`Contact`) VALUES
('C101', 'Harry Patt', 'London', 34325435),
('C102', 'James Lannister', 'Bristol', 564646234),
('C103', 'Sara Linda', 'Edinburgh', 543536554),
('C104', 'Marry Fate', 'Frankfurt', 543564654),
('C105', 'Paul Sam', 'Cardiff', 64575676);
Employees

26DATABASE CONCEPTS – CHALLENGE UK
INSERT INTO `employees` (`EmpID`, `Name`, `Contact`, `Email`, `Role`)
VALUES
('E101', 'Mike Wilson', 324233254, '[e-mail]', 'Booking Manager'),
('E102', 'Joana Seth', 45435656, '[e-mail]', 'Booking Manager'),
('E103', 'Hank Pym', 453345, '[e-mail]', 'Activity Manager'),
('E104', 'Mark Rufflow', 5434545, '[e-mail]', 'Desk Staff'),
('E105', 'Paolo Mathew', 342534543, '[e-mail]', 'Hostel Manager'),
('E106', 'Linda hudson', 53465656, '[e-mail]', 'Hostel Manager'),
('E107', 'Jake lan', 545646456, '[e-mail]', 'Desk Staff');
Guests
INSERT INTO `guest` (`GuestID`, `Name`, `Age`, `Gender`, `Address`,
`DietPreferences`, `MedicalHistory`, `RoomID`, `BookingID`) VALUES
('G101', 'Sara Linda', 24, 'Female', 'Edinburgh', 'Healthy', '', '1',
'B103'),
('G102', 'Marry Fate', 19, 'Female', 'Frankfurt', '', '', '1',
'B104'),
('G103', 'Harrison Pete', 31, 'Male', 'Cardiff', '', '', '4', 'B101'),
('G104', 'John perry', 23, 'Male', 'New Queensland', '', '', '5',
'B102'),
('G105', 'Rose Matt', 43, 'Female', 'Greendale', 'Non Oily', 'Sugar',
'4', 'B105');
Bookings
INSERT INTO `bookings` (`BookingID`, `BookingDate`, `Status`,
`TotalPlacesBooked`, `custno`, `ClerkID`, `CourseID`) VALUES
('B101', '2019-07-10', 'Paid', 2, 'C101', 'E101', 'CAV01'),
('B102', '2019-05-08', 'Not Paid', 2, 'C102', 'E102', 'ABS01'),
('B103', '2019-08-08', 'Paid', 5, 'C103', 'E102', 'CAV01'),
('B104', '2019-02-27', 'Paid', 5, 'C104', 'E101', 'HNG01'),
('B105', '2018-10-10', 'Not Paid', 10, 'C105', 'E101', 'ORT01');
Invoices
INSERT INTO `invoice` (`InvoiceNo`, `BookingID`, `Date`, `Total`)
VALUES
('I101', 'B101', '2019-09-27', '45.54'),
('I102', 'B102', '2019-08-10', '23.86'),
('I103', 'B103', '2019-09-18', '45.54'),
('I104', 'B104', '2019-07-24', '56.56'),
('I105', 'B105', '2019-08-21', '687.67');

27DATABASE CONCEPTS – CHALLENGE UK
Courses
INSERT INTO `course` (`CourseID`, `CourseName`, `Location`,
`StartDate`, `PriceAdult`, `PriceChild`) VALUES
('ABS01', 'Abseiling', 'Building A1', '2019-09-18', '23.86', '56.98'),
('CAN01', 'Canoeing', 'Lake', '2019-08-05', '98.45', '23.23'),
('CAV01', 'Caving', 'Glow Worm Caves', '2019-09-24', '45.54',
'20.43'),
('CLM01', 'Climbing', 'Yosemite National Park', '2019-04-15', '34.80',
'32.23'),
('HNG01', 'Hang Gliding', 'Kitty Hawk', '2019-08-22', '56.56',
'29.32'),
('KAY01', 'Kayaking', 'Zanskar River', '2019-02-14', '59.76',
'34.56'),
('ORT01', 'Orienteering', 'Leicestershire', '2018-08-08', '687.67',
'456.56'),
('PON01', 'Pony Trekking', 'Mid Wales', '2019-08-24', '45.56',
'34.45'),
('VEN03', 'Adventure Course', 'Area 51', '2019-10-07', '456.65',
'345.45');
Hostel
INSERT INTO `hostel` (`Hostelname`, `Location`, `HostelManagerID`)
VALUES
('Loch Tay', 'London', 'E105'),
('Zake Inn', 'Bristol', 'E106');
Room
INSERT INTO `room` (`RoomID`, `NoofBeds`, `HostelName`) VALUES
('1', 5, 'Loch Tay'),
('2', 6, 'Zake Inn'),
('3', 4, 'Loch Tay'),
('4', 3, 'Loch Tay'),
('5', 8, 'Zake Inn');
Query
Q1 List of Hotels.

28DATABASE CONCEPTS – CHALLENGE UK
select hostelname from hostel;
Q2 List of hotels, their rooms and bed per room
select Hostelname, roomid , noofbeds from room;
Q3 List of courses
select Coursename from course;
Q4 List of scheduled courses
select Coursename, StartDate from course;

29DATABASE CONCEPTS – CHALLENGE UK
Q5 List of scheduled courses in a hostel
select r.hostelname , c.coursename, c.StartDate from room r inner JOIN
guest g on g.RoomID=r.RoomID inner JOIN bookings b ON
b.BookingID=g.BookingID inner join course c ON
c.CourseID=b.CourseID;
Q6 List of scheduled courses in between two given dates
select Coursename, StartDate from course where StartDate between '2018-09-18' and '2019-09-
19';

30DATABASE CONCEPTS – CHALLENGE UK
Q7 List of scheduled courses in a hostel between two given dates
select r.hostelname , c.coursename, c.StartDate from room r inner JOIN
guest g on g.RoomID=r.RoomID inner JOIN bookings b ON
b.BookingID=g.BookingID inner join course c ON
c.CourseID=b.CourseID where c.StartDate between '2018-09-18' and '2019-09-19';
Q8 List all account holders
select * from customers;
Q9 Add a new booking, including the details of the client and determine whether the client
is an account holder or not.
INSERT INTO `customers` VALUES
('C106', 'Kate Parker', 'London', 755734);
INSERT INTO `bookings` VALUES
('B106', '2019-07-15', 'Not Paid', 2, 'C106', 'E101', 'CAV01');
Q10Add three guests to the above booking -two adults and a child, at least one of them with
special requirements.

31DATABASE CONCEPTS – CHALLENGE UK
INSERT INTO `guest` (`GuestID`, `Name`, `Age`, `Gender`, `Address`, `DietPreferences`,
`MedicalHistory`, `RoomID`, `BookingID`) VALUES
('G106', 'Kate Parker', 24, 'Female', 'London', '', '', '2', 'B106'),
('G107', 'Lily Winston', 11, 'Female', 'London', '', '', '2', 'B106'),
('G108', 'Sam Wilson', 28, 'Male', 'London', '', '', '2', 'B106');
Q11 On the above booking, assign one adult guest and the child to available course(s).
update guest
set age= 10
where guestid='G108';
Q12 Calculate the cost of the booking above.
select b.bookingid, (c.PriceAdult+ (2*c.PriceChild)) as total from bookings b inner join course c
on c.CourseID=b.CourseID where b.BookingID='b106';
Q13 Update the number of places available on the course(s) booked by guests in the
booking above.
update bookings
set TotalPlacesBooked = 3
where BookingID='b016';
Q14 Create an invoice for the booking above.
INSERT INTO `invoice` VALUES
('I106', 'B106', '2019-09-27', '86.40');
Q15 Assume payment has been made in full and update the status of the booking.
update bookings
set Status='Paid'
where BookingID='b106';

32DATABASE CONCEPTS – CHALLENGE UK
Q16 Produce the course record spreadsheet for the course chosen by one the guest in the
booking above.
select g.name, c.* from guest g INNER join bookings b ON
b.BookingID=g.BookingID inner join course c on c.CourseID=b.CourseID where
b.BookingID='b106';
Q17 Produce the Scheduled Activity Report including the course chosen by one of the
guests in the booking above
select g.name, c.StartDate from guest g INNER join bookings b ON
b.BookingID=g.BookingID inner join course c on c.CourseID=b.CourseID;
Q18 Explain what tables would need to be updated as a result of a cancellation
As a result of cancellation, invocies, guests, course, bookings and rooms table would be needed
to be updated.

33DATABASE CONCEPTS – CHALLENGE UK
Bibliography
Chen, Y. (2016). Comparison of Graph Databases and Relational Databases When Handling
Large-Scale Social Data (Doctoral dissertation, University of Saskatchewan).
Connolly, T., & Begg, C. (2015). Database systems. Pearson Education UK.
Coronel, C., & Morris, S. (2016). Database systems: design, implementation, & management.
Cengage Learning.
Dutka, A. F., & Hanson, H. H. (1989). Fundamentals of data normalization (Vol. 196). New
York: Addison-Wesley.
Harrington, J. L. (2016). Relational database design and implementation. Morgan Kaufmann.
Hoffer, J., Venkataraman, R., & Topi, H. (2015). Modern database management. Prentice Hall
Press.
Hogan, R. (2018). A practical guide to database design. Chapman and Hall/CRC.
Huang, P. S., Wang, C., Singh, R., Yih, W. T., & He, X. (2018). Natural language to structured
query generation via meta-learning. arXiv preprint arXiv:1803.02400.
Jukic, N., Vrbsky, S., & Nestorov, S. (2016). Database systems: Introduction to databases and
data warehouses. Prospect Press.
Linkov, I., Anklam, E., Collier, Z. A., DiMase, D., & Renn, O. (2014). Risk-based standards:
integrating top–down and bottom–up approaches. Environment Systems and
Decisions, 34(1), 134-137.
Williams, R. (2019). Data management and data description. Routledge.