information system

INSY 3304

Project 2

This project must be completed using an Oracle database with all statements manually typed into an SQL file and run as a batch in Oracle, and the output must be written to a text (.txt) file. Statements and results (feedback from Oracle) must be included in the output file and printed for submission. Directions for downloading the software and for creating and running the SQL file will be posted in Canvas.

Project 2 Relational Schema

I. A) Based on the 3NF relational schema from Project 1 (shown above), analyze the tables, their relationships, and the sample data you were given in Project 1. Create the tables shown, using the best data types based on your analysis. Follow each table name with an underscore and your first, middle, and last initials (if you do not have a middle initial, use X). For example, if your name is Alice B Chandler, you would name the Customer table Customer_abc. Also, keep in mind that foreign key relationships require the same data types on both sides (e.g., if you declare CustID as NUMBER in the Customer table, it must be declared as NUMBER in the Rental table, too). All CREATE table statements and their resulting output (e.g., “Table created”) must be included in your printed output.

B) Execute a DESCRIBE statement for each of the tables. All DESCRIBE statements and their resulting output (i.e., the table structure) must be included in your printed output).

II. A) Insert the sample data from Project 1 into each table. Execute a COMMIT statement to permanently save your changes. All INSERT statements and COMMIT statements, along with their resulting output (e.g., “Row inserted”) must be included in your printed output.

B) Execute a SELECT statement on each table to list all contents (all columns and all rows). All SELECT statements and their resulting output (i.e., the table and its data) must be included in your printed output.

III. Execute the transactions below to modify/add to the data entered in the previous step. Execute a COMMIT statement to permanently save your changes. All INSERT, UPDATE, and COMMIT statements, along with their resulting output (e.g., “Row inserted”) must be included in your printed output.

Customer table

Change the phone number of Customer 85 to ‘2145551234’

Add Customer 120 (Amanda Green, no phone number)

Reservation table

Change the checkout date for reservation 1001 to 2/8/2019

Add a reservation:

ResID CheckIn CheckOut CustID AgentID Rate Type

1011 10/1/2019 10/4/2019 120 14 W

Add a room:

RoomNum RoomType

226 D

ResDetail table

Change the RateCode of ResID 1003 to C

Change the RateAmt of ResID 1003 to $89

Add the following details for reservation 1011:

RoomNum Rate

224 $139

225 $129

226 $129

IV. Execute a SELECT statement on each table to list all contents (all columns and all rows). All SELECT statements and their resulting output (i.e., the table and its data) must be included in your printed output.

Add a cover sheet with your name, section number, and project number. Staple all printouts together with the cover sheet on top.