Hotel Schema Drop Table Hotel Cascade Constraints

Hotel Schema Drop Table Hotel Cascade Constraints Drop Table Ro

Hotel Schema drop table Hotel cascade constraints; drop table Room cascade constraints; drop table Guest cascade constraints; drop table Booking cascade constraints; Create Table Hotel( hotelNo VarChar(3), hotelName VarChar(20), city VarChar(20), Primary key (hotelNo)); Create table Room( roomNo Number(3), hotelNo VarChar(4), type Char(1), price Decimal(7), Primary key (roomNo, hotelNo), Foreign Key (hotelNo) References Hotel(hotelNo)); Create table Guest( guestNo VarChar(6), guestName Char(50), guestAddress Char(50), Primary key (guestNo)); Create table Booking( hotelNo VarChar(4), guestNo VarChar(6), dateFrom DATE, dateTo DATE, roomNo Number(3), Primary key (hotelNo, guestNo, dateFrom), Foreign Key (hotelNo) References Hotel(hotelNo), Foreign Key (guestNo) References Guest(guestNo)); Insert into Hotel values ('452', 'Residence Inn', 'Scranton'); Insert into Hotel values ('981', 'Marriott', 'Tallahassee'); Insert into Room values (104, '452', 'D', 79); Insert into Room values (509, '981', 'F', 100); Insert into Guest values ('600798', 'Thomas Hughes', '1 Autumn Way Jessup, MD'); Insert into Guest values ('123456', 'Sheeka Tull', '52 Gallaway St Baltimore, MD'); Insert into Booking values ('452', '600798', '11-Apr-2010', '21-Apr-2010', 104); Insert into Booking values ('981', '123456', '1-Feb-2010', '5-Feb-2010', 509); SQL1/ For SQL Assignment #1: Exercises 6.7, 6.9, 6.11, 6.13, 6.17, load and run the Hotel Schema file located in the Hotel Schema folder. Write SQL commands to answer the above questions, run each command on ORACLE DBMS and save them using SQL92 or later, etc. Submit your final list of SQL commands here as an attachment or paste in the text editor. SQL2/ For SQL Assignment #2: Do Exercises 6.8, 6.10, 6.12, 6.14, 6.16 Write SQL commands to answer the above questions, run each command on ORACLE DBMS and save them using SQL92 or later. Submit your final list of SQL commands here as an attachment or paste in the text editor. SQL3 / For SQL Assignment #3, Do Exercises 6.22 to 6.26. Write SQL commands to answer the above questions, run each command on ORACLE DBMS and save them using SQL-92 or later. Submit your final list of SQL commands here as an attachment or paste in the text editor.

Paper For Above instruction

The provided assignment involves creating and managing a hotel database schema, executing specific SQL commands, and applying these instructions in an Oracle DBMS environment using SQL92 or later standards. The primary goal is to properly set up the schema, insert sample data, and perform various SQL operations based on exercises from the textbook, ensuring accurate data retrieval and manipulation to support hotel management processes.

Initially, the hotel schema must be dropped if it exists, including all associated constraints, to avoid schema conflicts or duplication. This is achieved using the DROP TABLE ... CASCADE CONSTRAINTS command on Oracle, ensuring the removal of dependent objects such as foreign key constraints. Afterward, the schema is recreated with tables such as Hotel, Room, Guest, and Booking, each with appropriate primary keys and foreign key relationships to maintain referential integrity. These tables serve as foundational structures to store hotel information, room details, guest data, and reservations.

Inserting sample data into each table helps simulate real-world scenarios, such as two hotels ("Residence Inn" and "Marriott") located in different cities, associated rooms, and guest information. This facilitates executing meaningful SQL queries to retrieve, update, or delete information, as required by the exercises from the textbook (Exercises 6.7, 6.8, etc.).

The assignment specifies executing multiple exercises, which include SELECT, INSERT, UPDATE, DELETE, and possibly complex JOIN queries, to practice database management and querying skills. Each command must be run on Oracle DBMS, adhering to SQL-92 standards or later, ensuring compatibility and best practices. The resulting SQL commands should then be compiled into a final list and submitted for evaluation.

Overall, this task emphasizes understanding relational database design, constraints management, data insertion, and querying techniques, which are vital for developing efficient and reliable hotel management systems. Proper execution of these steps ensures the database supports essential hotel operations like room reservations and guest management reliably.

References

  • Elmasri, R., & Navathe, S. B. (2015). Fundamentals of Database Systems (7th ed.). Pearson.

Related Assignments