Create a Program to Implement Database Schema in SQL Assignment Solution

Instructions

Objective

Write a SQL assignment program to implement a database schema in SQL language. This assignment would involve creating tables, defining relationships, and setting up the necessary constraints to ensure data integrity. The program should be well-structured, utilizing SQL commands effectively to define the various components of the database schema. Students would need to demonstrate their understanding of SQL concepts by correctly designing and implementing the schema. Through this assignment, students can gain hands-on experience in translating real-world scenarios into a structured database schema using the SQL language.

Requirements and Specifications

Source Code

Initializing the Dream Hotel Database Schema create database DreamHotel; use DreamHotel; Creating Hotel, Room, Booking, Billing, Guest, and Staff Tables create table Hotel ( hotelNo integer not null, hotelName varchar(50) not null, city varchar(50) not null, primary key(hotelNo) ); create table Room ( roomNo integer not null, hotelNo integer not null, type varchar(30) not null, price decimal(10, 2) not null, primary key(roomNo), foreign key(hotelNo) references Hotel(hotelNo) ); create table Guest ( guestNo integer not null, guestName varchar(50) not null, guestAddress varchar(50) not null, primary key(guestNo) ); create table Staff ( staffNo integer not null, fName varchar(50) not null, lName varchar(50) not null, dob date not null, gender char(1) not null, salary decimal(10, 2) not null, hotelNo integer not null, primary key(staffNo), foreign key(hotelNo) references Hotel(hotelNo) ); create table Booking ( bookingNo integer not null, guestNo integer not null, staffNo integer not null, dateFrom date not null, dateTo date not null, roomNo integer not null, hotelNo integer not null, primary key(bookingNo), foreign key(guestNo) references Guest(guestNo), foreign key(staffNo) references Staff(staffNo), foreign key(roomNo) references Room(roomNo), foreign key(hotelNo) references Hotel(hotelNo) ); create table Billing ( billNo integer not null, bookingNo integer not null, total decimal(10, 2) not null, primary key(billNo), foreign key(bookingNo) references Booking(bookingNo) ); General Constraints All fields are required for each table as they are all important for the business. A room is owned by only one hotel. A staff works only on one hotel. A booking is made by a guest for a particular room of a hotel handled by a staff working on that hotel. A billing is billed to a booking made by a guest. Populating the Hotel, Room, and Booking tables with 4 rows each Note: other tables have to be populated as well because of the general constraints. For instance, we cannot have a booking data if on the first place there are no guest who books for it and a staff to handle it. insert into Hotel values(1, 'Crowne Plaza', 'Louisville'); insert into Hotel values(2, 'Emerald Bay', 'Oakland'); insert into Hotel values(3, 'Hotel Bliss', 'Huntington'); insert into Hotel values(4, 'Sunset Lodge', 'Denver'); insert into Room values(1, 1, 'Single', 99.36); insert into Room values(2, 2, 'Double', 179.20); insert into Room values(3, 3, 'Queen', 150.00); insert into Room values(4, 4, 'King', 200.00); insert into Guest values(1, 'Robert Freeman', '3763 Sampson Street, Denver, CO'); insert into Guest values(2, 'Melissa Dean', '4485 Victoria Street, Greensburg, LA'); insert into Guest values(3, 'Rebecca Smith', '230 Spirit Drive, Jacksonville, FL'); insert into Guest values(4, 'Brandon Lang', '1468 Parkview Drive, Los Angeles, CA'); insert into Staff values(1, 'Lillian', 'Sorensen', '1989-10-24', 'F', 24510, 1); insert into Staff values(2, 'Samantha', 'Haywood', '1984-11-20', 'F', 32256, 2); insert into Staff values(3, 'Joann', 'Chapman', '1982-09-23', 'F', 40739, 3); insert into Staff values(4, 'Alfonzo', 'Bower', '1983-06-12', 'M', 65460, 4); insert into Booking values(1, 1, 1, '2021-07-01', '2021-07-02', 1, 1); insert into Booking values(2, 2, 2, '2021-07-02', '2021-07-03', 2, 2); insert into Booking values(3, 3, 3, '2021-07-03', '2021-07-04', 3, 3); insert into Booking values(4, 4, 4, '2021-07-04', '2021-07-05', 4, 4); Deleting Booking and Room tables Drop table Booking; Drop table Room; Observation 1: Dropping the Booking and Room table is successful if the engine used to create the tables are MyISAM (Indexed Sequential Access Method). Regardless whether the table is being referenced by another table or not, it will be deleted. Observation 2: Dropping the Booking and Room table is not successful if the engine used to create the tables are InnoDB. The reason why Booking cannot be deleted is because it is being referenced by the Billing table while the Room table is being referenced by the Booking table.