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
(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