Download and install Oracle Express Edition 11g from the following link: http://
ID: 3734233 • Letter: D
Question
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 pathproject2_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:UserssarraDesktopProject2_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.
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
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 DependencieExplanation / Answer
1. CREATE TABLE STATEMENTS:-
2. INSERT STATEMENTS :-
insert into Customer values (85,'Wesley','Tanner',8175551193,'C',323);
insert into Customer values (100,'Breanna','Rhodes',2145559191,'I',129);
insert into Agent_Detail values ('Out','Outside');
insert into Agent_Detail values ('In','Inside');
insert into Agent values (20,'Megan','Aakash','Out');
insert into Agent values (5,'Janice','June','In');
insert into Reservation values (1001,'2-5-2018','2-7-2018',85,20);
insert into Reservation values (1002,'2-1-2018','2-3-2018'100,5);
insert into Cust_Detail values ('C','Corporate');
insert into Cust_Detail values ('I','Individual');
insert into Rate values ('ST','State Tax');
insert into Rate values ('GST','Global Tax');
insert into Room values(101,'SR');
insert into Room values(102,'DR');
insert into Res_Detail values (1001,101,'ST',1000);
insert into Res_Detail values (1002,102,'GST',2000);
insert into Room_Detail values ('SR','Single Room');
insert into Room_Detail values ('DR','Double Room');
3. MODIFICATION/ADD Data to the tables:-
-->update Customer set CustPhone=2145551234 where CustID=85;
-->insert into Customer values (120,'Amanda','Green',NULL,NULL,NULL);
-->update Reservation set CheckOut='2-8-2018' where ResID=1001;
-->insert into Reservation values (1011,'3-1-2018','3-4-2018',120,14);
-->update Res_Detail set ResType='C' where ResID=1003;
-->update Res_Detail set RateAmt=89 where ResID=1003;
-->update Res_Detail set RoomNum=224,RateType='W',Rate=119 where ResID=1011;
-->update Res_Detail set RoomNum=225,RateType='W',Rate=129 where RasID=1011;
After executing all these statements in the database execute the below statement to view the records of the tables inserted.
select * from <tablename>;
In the place of <tablename> place the original table name one by one and execute like
Ex:- select * from Customer;
Above are the create table statements and the sample insert statements which inserts 2 rows in each table. Also given modification statements also using update statement.
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.