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]