KXO206-Asst3-Previous-Solution

This is a solution to a previous year’s assignment submitted by a former KXO206 Student
(used with permission)

KXO206
Database Management Systems

Assignment 3: Database Creation Report

Michael Cantwell

Skid Marx Motor Inn

Executive Summary
The Skid Marx Motor Inn has multiple requirements for its database management system (DBMS). These relate primarily to their user of the DBMS to support the operation of their Inn. As such this report provides scripts and example outputs of these scripts.

Ten requirements were specified by Skid Marx Motor Inn and each has been addressed in the following manner:


The SQL and PL/SQL script used to meet the specified requirement;



The resultant output from each specified requirement;



Relevant comments pertaining to each script;

All of the requirements as set out in the requirements report have been met.
Below is a summary of these:

Requirement

Completion Status

1 - Setup the Database

Complete

2 - Create a Structured Database Report

Complete

3 - Highlight Bookings

Complete

4 - Check Guests In and Out

Complete

5 - Summarise Room Income

Complete

6 - Find Available Rooms - PL/SQL

Complete

7 - Create a Cleaning Table

Complete

8 - Create a Cleaning Exception Report

Complete

9 - Create a Customer’s Bill

Complete

10 - Additional Scripts

Complete

Page 3 of 59

Contents
EXECUTIVE SUMMARY

3

CONTENTS

4

QUESTION 1

6

SQL SCRIPT

6

RESULTS

8

COMMENTS

9

10

QUESTION 2
SQLSCRIPT

10

RESULTS

11

COMMENTS

14

15

QUESTION 3
SQL SCRIPT

15

RESULTS

15

COMMENTS

16

17

QUESTION 4A
SQL SCRIPT

17

RESULTS

18

COMMENTS

18

19

QUESTION 4B
SQL SCRIPT

19

RESULTS

20

COMMENTS

20

21

QUESTION 5
SQL SCRIPT

21

RESULTS

22

COMMENTS

23

24

QUESTION 6
SQL QUERY

24

RESULTS

25

COMMENTS

25