The Database Systems and Design

Running head: DATABASE SYSTEMS AND DESIGN
Database Systems and Design
Name of the Student
Name of the University
Author’s note:

DATABASE SYSTEMS AND DESIGN1
Table of Contents
Stage 1: Scenario and Conceptual Database Design.......................................................................2
Task 1.1: Scenario.......................................................................................................................2
Task 1.2: Conceptual Database Design.......................................................................................2
Stage 2: Logical Database Design and Oracle SQL Implementation/Querying..............................3
Task 2.1: Logical Database Design:............................................................................................3
Task 2.2: Tables in Oracle Database...........................................................................................3
Task 2.3: Indexes.........................................................................................................................7
Task 2.4: Data Population............................................................................................................7
Task 2.5: Queries.......................................................................................................................10
Bibliography:.................................................................................................................................12

DATABASE SYSTEMS AND DESIGN2
Stage 1: Scenario and Conceptual Database Design
Task 1.1: Scenario
The requirements from the hotel management system are as following. The database must
store the information about the guests as well as the rooms allotted to them. Customer must have
information about the room availability. Guests must book the available rooms online. The
record of services and food availed by the consumer should be stored. Bill must be generated for
the customer.
Task 1.2: Conceptual Database Design

DATABASE SYSTEMS AND DESIGN3
Figure 1: Conceptual Database Design of Hotel Management System
(Source: Created by Author)
Stage 2: Logical Database Design and Oracle SQL Implementation/Querying
Task 2.1: Logical Database Design:
Figure 2: Logical Database Design of Hotel Management System
(Source: Created by Author)
Task 2.2: Tables in Oracle Database
Create Table Family (
Family_Head_SSN varchar2(20) not null constraint family_pk primary key,
Family_Head_Name varchar2(100) not null,