DATABASE CONCEPTS CHALLENGE UK

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

DATABASE CONCEPTS – CHALLENGE UK
1
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

DATABASE CONCEPTS – CHALLENGE UK
2
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.

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

DATABASE CONCEPTS – CHALLENGE UK
4
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

DATABASE CONCEPTS – CHALLENGE UK
5
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.

DATABASE CONCEPTS – CHALLENGE UK
6
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

DATABASE CONCEPTS – CHALLENGE UK
7
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