Download and install Oracle Express Edition 11g from the following link: http://www.oracle.com/technetwork/database/database-technologies/express-edition/downloads/index.html Note: You will be required to create an account.

Open Notepad and type the following at the top of the file:

spool path\project2_abc.txt'

-- Include the full path. This will start logging to the specified file.

set echo on

-- This will ensure that all input and output is logged to the file.

Type all statements into a Notepad file (it is recommended that you do this a few statements at a time, testing as you go along). Please indicate the different sections of the project using comment lines, which can be created using two hyphens at the beginning of the comment text; e.g., --Part I or --Part II (without the quotations). Name the file using your initials as a suffix with an sql extension (e.g., project2_abc.sql).

Type the following at the very end of the file:

set echo off

-- This will turn off logging.

spool off

-- This will close the file.

Start your database using the "Start Database" option in the Oracle Database program group (from the Windows "Start button").

Open the SQL command line from the "Run SQL Command Line" option in the Oracle Database program group (from the Windows "Start button"). When the window opens, you will see an SQL prompt. Type the following command to connect to your database:

connect system/password

where password is the password you entered during the installation process.

Once connected, you may run your SQL file by typing the start command at the SQL prompt (include the full path). For example:

start C:\Users\sarra\Desktop\Project2_kls.sql

You will see all of the statements and output scroll by. When it is finished, the SQL prompt will appear again.

To disconnect from the database and close your session, type the following:

exit

Ensure that all your statements and related output appear correctly in the spooled text file.

If you have mistakes, you can make the necessary corrections to the SQL statements in your notepad file. Note: If your SQL file contains Create Table statements, you will need to drop the previously-created tables before running the SQL file again. You can do this at the SQL prompt, or you can add the Drop Table statements to the beginning of the SQL file before you run it (the latter is the better option).

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 creating and running the sql file are in posted in Blackboard. If you need additional help, a lab assistant is available per the schedule in Blackboard.

ResNum

CheckIn

CheckOut

CustID

CustFName

CustLName

CustPhone

CustType

CustDesc

LoyaltyID

AgentID

AgentFName

AgentLName

Agent Type

Agent Desc

RoomNum

RoomType

RoomDesc

RateType

RateDesc

RateAmt

1001

2/5/2018

2/7/2018

85

Wesley

Tanner

8175551193

C

Corporate

323

20

Megan

Smith

FD

Front Desk

224

K

King Bed

C

Corporate

$120

1001

2/5/2018

2/7/2018

85

Wesley

Tanner

8175551193

C

Corporate

323

20

Megan

Smith

FD

Front Desk

225

D

2 Double Beds

C

Corporate

$125

1002

2/1/2018

2/3/2018

100

Breanna

Rhodes

2145559191

I

Individual

129

5

Janice

May

T

Telephone

305

D

2 Double Beds

S

Standard

$149

1003

2/9/2018

2/11/2018

15

Jeff

Miner

I

Individual

14

John

King

FD

Front Desk

409

D

2 Double Beds

W

Weekend

$99

1004

2/22/2018

2/23/2018

77

Kim

Jackson

8175554911

C

Corporate

210

28

Ray

Schultz

T

Telephone

320

D

2 Double Beds

C

Corporate

$110

1005

2/15/2018

2/18/2018

119

Mary

Vaughn

8175552334

I

Individual

118

20

Megan

Smith

FD

Front Desk

302

K

King Bed

S

Standard

$139

1006

2/24/2018

2/26/2018

97

Chris

Mancha

4695553440

I

Individual

153

14

John

King

FD

Front Desk

501

KS

King Suite

W

Weekend

$119

1006

2/24/2018

2/26/2018

97

Chris

Mancha

4695553440

I

Individual

153

14

John

King

FD

Front Desk

502

KS

King Suite

W

Weekend

$119

1007

2/20/2018

2/25/2018

100

Breanna

Rhodes

2145559191

I

Individual

129

20

Megan

Smith

FD

Front Desk

302

K

King Bed

S

Standard

$139

1008

3/23/2018

3/25/2018

85

Wesley

Tanner

8175551193

C

Corporate

323

5

Janice

May

T

Telephone

320

D

2 Double Beds

W

Weekend

$89

1008

3/23/2018

3/25/2018

85

Wesley

Tanner

8175551193

C

Corporate

323

5

Janice

May

T

Telephone

321

K

King Bed

W

Weekend

$99

1009

3/1/2018

3/4/2018

28

Renee

Walker

2145559285

I

Individual

135

14

John

King

RC

Res Center

502

KS

King Suite

W

Weekend

$129

1010

3/1/2018

3/3/2018

23

Shelby

Day

I

Individual

28

Ray

Schultz

T

Telephone

225

D

2 Double Beds

W

Weekend

$129

I. Based on the relational schema (3NF) from Project 1, 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 INTEGER in the Customer table, it must be declared as INTEGER in the Rental table, too). You must print a copy of each CREATE TABLE statement. In addition, execute a DESCRIBE statement for each table after it is created and print the output before proceeding to Step II.

II. After creating the tables, insert the sample data from Project 1 into each table, and print the statements used for each insert transaction. After all rows have been inserted, run a SELECT statement on each table to list all contents (all columns and all rows). Print the SELECT statement and output for each table before proceeding to Step III.

III. Execute the transactions below to modify/add to the data entered in the previous step (COMMIT the transactions if not automatically committed). Print the statements and output for each transaction before proceeding to Step IV:

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/2018

Add a reservation:

ResID CheckIn CheckOut CustID AgentID

1011 3/1/2018 3/4/2018 120 14

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 RateType Rate

224 W $119

225 W $129

Execute a SELECT statement on each of the tables to list its final contents. Print the statement and output for each SELECT statement.

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

Roo ResN mNu Chec Chec |Custl kIn kOut D CustF Custl Nam |Nam Agen Agen CustP CustT Cust Loyal Agenta tLNa e Rate ype Desc Amt Rate MT um ype Desc me me -Partial Dependencies Transitive Dependencies Nested Transitive Dependencie