QuestionQuestion

Transcribed TextTranscribed Text

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. Reservation Conomer CustType ? - a Apom Y Cuchn , Scontan - - 5 CustTyoeDest CustPhone Agail 3 ? 2 / 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 tab 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 IV. 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.

Solution PreviewSolution Preview

These solutions may offer step-by-step problem-solving explanations or good writing examples that include modern styles of formatting and construction of bibliographies out of text citations and references. Students may use these solutions for personal skill-building and practice. Unethical use is strictly forbidden.

-- Change the phone number of Customer 85 to ‘2145551234’
UPDATE CUSTOMER_BXS SET CUSTPHONE = '2145551234'
WHERE CUSTID = 85;

-- Add Customer 120 (Amanda Green, no phone number)

INSERT INTO "CUSTOMER_BXS" (CUSTID, CUSTFNAME, CUSTLNAME) VALUES ('120', 'Amanda', 'Green');


-- Change the checkout date for reservation 1001 to 2/8/2018

UPDATE "RESERVATION_BXS"
SET CHECKOUTDATE = TO_DATE('2018-02-08 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
WHERE RESID = 1001;

-- Add a reservation:

INSERT INTO "RESERVATION_BXS" (RESID, CHECKINDATE, CHECKOUTDATE, CUSTID, AGENTID)
VALUES ('1011', TO_DATE('2018-03-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'),
TO_DATE('2018-03-04 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '120', '14');

-- Change the RateCode of ResID 1003 to C

UPDATE "RESDETAIL_BXS"
SET RATETYPE = 'C' WHERE RESID = 1003;

-- Change the RateAmt of ResID 1003 to $89

UPDATE "RESDETAIL_BXS"
SET RATEAMT = '89' WHERE RESID = 1003;


-- Add the following details for reservation 1011:
-- RoomNum    RateType Rate   
-- 224 W $119
-- 225 W $129


INSERT INTO "RESDETAIL_BXS" (RESID, ROOMNUM, RATETYPE, RATEAMT) VALUES ('1011', '224', 'W', '119');
INSERT INTO "RESDETAIL_BXS" (RESID, ROOMNUM, RATETYPE, RATEAMT) VALUES ('1011', '225', 'W', '129');...

By purchasing this solution you'll be able to access the following files:
Solution.zip.

$55.00
for this solution

or FREE if you
register a new account!

PayPal, G Pay, ApplePay, Amazon Pay, and all major credit cards accepted.

Find A Tutor

View available Database Development Tutors

Get College Homework Help.

Are you sure you don't want to upload any files?

Fast tutor response requires as much info as possible.

Decision:
Upload a file
Continue without uploading

SUBMIT YOUR HOMEWORK
We couldn't find that subject.
Please select the best match from the list below.

We'll send you an email right away. If it's not in your inbox, check your spam folder.

  • 1
  • 2
  • 3
Live Chats