database

this is my friend code and this how i want it following all the questions as he did it. But mine is for a autosale company that want to keep track of their client information. I use the Oracle SQL developper.

Section 4: IMPLEMENTATION

·

SQL Commands and their output:

·

Used to create tables

> /* Step-0 used to create and load Assign03_3_5 tables */

/* CREATE TABLE TRAVEL */

> CREATE TABLE TBL_PRJ_TRAVEL (

TRV_CODE NUMBER(5) PRIMARY KEY,

TRV_DESTINATION VARCHAR(20) NOT NULL,

TRV_ORIGIN VARCHAR(20) NOT NULL,

TRV_DATE_START DATE NOT NULL)

CREATE TABLE succeeded.

/* CREATE TABLE PASSENGER */

> CREATE TABLE TBL_PRJ_PASSENGER (

PASSENGER_ID VARCHAR(20) PRIMARY KEY,

PASSENGER_FN VARCHAR(25) NOT NULL,

PASSENGER_LN VARCHAR(20) NOT NULL,

ENFANT_or_ADULT VARCHAR(6) NOT NULL,

Handicapped VARCHAR(3) NOT NULL)

CREATE TABLE succeeded.

/* CREATE TABLE BAGGAGE */

> CREATE TABLE TBL_PRJ_BAGGAGE (

BAG_NUM NUMBER(8) PRIMARY KEY,

PASSENGER_ID VARCHAR(20) NOT NULL,

BAG_WEIGHT NUMBER(2) NOT NULL,

BAG_OVERWEIGHT NUMBER(3) NOT NULL,

PAYMENT_METHOD VARCHAR(10) NOT NULL,

FOREIGN KEY (PASSENGER_ID) REFERENCES TBL_PRJ_PASSENGER(PASSENGER_ID))

CREATE TABLE succeeded.

/* CREATE TABLE SEAT */

> CREATE TABLE TBL_PRJ_SEAT (

SEAT_NUM VARCHAR(5) PRIMARY KEY,

AISLE VARCHAR(1) NOT NULL)

CREATE TABLE succeeded.

/* CREATE TABLE TICKET */

> CREATE TABLE TBL_PRJ_TICKET (

TICKET_NUM NUMBER(6) PRIMARY KEY,

TICKET_PRICE NUMBER(9,2) DEFAULT 0.00 NOT NULL,

TRV_CODE NUMBER(5) NOT NULL,

SEAT_NUM VARCHAR(5) NOT NULL,

TRV_DATE_ARRIVAL DATE NOT NULL,

FOREIGN KEY (TRV_CODE) REFERENCES TBL_PRJ_TRAVEL(TRV_CODE),

FOREIGN KEY (SEAT_NUM) REFERENCES TBL_PRJ_SEAT(SEAT_NUM))

CREATE TABLE succeeded.

·

Used to create indexes and / or sequences, if appropriate

No index and / or sequences

·

Used to insert initial sample data into all tables

/* INSERT VALUES INTO TABLE TRAVEL */

> /* TRAVEL rows */

> INSERT INTO TBL_PRJ_TRAVEL VALUES (00001,’DELWARE’,’AKRON’,’04-NOV-11′)

1 rows inserted

> INSERT INTO TBL_PRJ_TRAVEL VALUES (00002,’COLUMBUS’,’CLEVELAND’,’06-JUN-11′)

1 rows inserted

> INSERT INTO TBL_PRJ_TRAVEL VALUES (00003,’CLEVELAND’,’CINCINATTI’,’07-JUL-11′)

1 rows inserted

> INSERT INTO TBL_PRJ_TRAVEL VALUES (00004,’AKRON’,’CIRCLEVILLE’,’12-DEC-10′)

1 rows inserted

> INSERT INTO TBL_PRJ_TRAVEL VALUES (00005,’DAYTON’,’COLUMBUS’,’01-JAN-11′)

1 rows inserted

> INSERT INTO TBL_PRJ_TRAVEL VALUES (00006,’PITTSBURG’,’COLUMBUS’,’03-MAR-10′)

1 rows inserted

> INSERT INTO TBL_PRJ_TRAVEL VALUES (00007,’CINCINATTI’,’DELWARE’,’07-APR-11′)

1 rows inserted

> INSERT INTO TBL_PRJ_TRAVEL VALUES (00008,’CIRCLEVILLE’,’PITTSBURG’,’06-MAY-12′)

1 rows inserted

/* INSERT VALUES INTO TABLE PASSENGER */

> /* PASSENGER rows */

> INSERT INTO TBL_PRJ_PASSENGER VALUES (‘PASSGR-#1321′,’FRANCK’,’YEBOUE’,’ADULT’,’NO’)

1 rows inserted

> INSERT INTO TBL_PRJ_PASSENGER VALUES (‘PASSGR-#1331′,’YANNICK’,’HARRIS’,’ADULT’,’NO’)

1 rows inserted

> INSERT INTO TBL_PRJ_PASSENGER VALUES (‘PASSGR-#1341′,’ROGER’,’BRIAN’,’ADULT’,’NO’)

1 rows inserted

> INSERT INTO TBL_PRJ_PASSENGER VALUES (‘PASSGR-#1351′,’DOUGLAS’,’ROGER’,’ADULT’,’NO’)

1 rows inserted

> INSERT INTO TBL_PRJ_PASSENGER VALUES (‘PASSGR-#1361′,’HENRY’,’MOORE’,’ENFANT’,’NO’)

1 rows inserted

> INSERT INTO TBL_PRJ_PASSENGER VALUES (‘PASSGR-#1371′,’LIZ’,’ZEN’,’ADULT’,’YES’)

1 rows inserted

> INSERT INTO TBL_PRJ_PASSENGER VALUES (‘PASSGR-#1381′,’NENE’,’DORA’,’ADULT’,’NO’)

1 rows inserted

> INSERT INTO TBL_PRJ_PASSENGER VALUES (‘PASSGR-#1301′,’HELEN’,’LEWIS’,’ENFANT’,’NO’)

1 rows inserted

/* INSERT VALUES INTO TABLE BAGGAGE */

> /* BAGGAGE rows */

> INSERT INTO TBL_PRJ_BAGGAGE VALUES (00001123,’PASSGR-#1361′,14,000,’CASH’)

1 rows inserted

> INSERT INTO TBL_PRJ_BAGGAGE VALUES (00001124,’PASSGR-#1321′,00,000,’CASH’)

1 rows inserted

> INSERT INTO TBL_PRJ_BAGGAGE VALUES (00001125,’PASSGR-#1371′,23,013,’CREDIT’)

1 rows inserted

> INSERT INTO TBL_PRJ_BAGGAGE VALUES (00001126,’PASSGR-#1331′,17,000,’DEBIT’)

1 rows inserted

> INSERT INTO TBL_PRJ_BAGGAGE VALUES (00001127,’PASSGR-#1301′,03,000,’CASH’)

1 rows inserted

> INSERT INTO TBL_PRJ_BAGGAGE VALUES (00007728,’PASSGR-#1341′,12,000,’CREDIT’)

1 rows inserted

> INSERT INTO TBL_PRJ_BAGGAGE VALUES (00009929,’PASSGR-#1381′,01,000,’CASH’)

1 rows inserted

> INSERT INTO TBL_PRJ_BAGGAGE VALUES (00003630,’PASSGR-#1351′,10,650,’CREDIT’)

1 rows inserted

/* INSERT VALUES INTO TABLE SEAT */

> /* SEAT rows */

> INSERT INTO TBL_PRJ_SEAT VALUES (‘A-001′,’A’)

1 rows inserted

> INSERT INTO TBL_PRJ_SEAT VALUES (‘A-003′,’A’)

1 rows inserted

> INSERT INTO TBL_PRJ_SEAT VALUES (‘B-001′,’A’)

1 rows inserted

> INSERT INTO TBL_PRJ_SEAT VALUES (‘B-002′,’B’)

1 rows inserted

> INSERT INTO TBL_PRJ_SEAT VALUES (‘B-003′,’B’)

1 rows inserted

> INSERT INTO TBL_PRJ_SEAT VALUES (‘A-017′,’A’)

1 rows inserted

> INSERT INTO TBL_PRJ_SEAT VALUES (‘B-030′,’B’)

1 rows inserted

> INSERT INTO TBL_PRJ_SEAT VALUES (‘C-008′,’C’)

1 rows inserted

/* INSERT VALUES INTO TABLE TICKET */

> /* TICKET rows */

> INSERT INTO TBL_PRJ_TICKET VALUES (‘002021′,45.23,00001,’A-001′,’04-NOV-11’)

1 rows inserted

> INSERT INTO TBL_PRJ_TICKET VALUES (‘002022′,42.32,00002,’A-003′,’06-JUN-11’)

1 rows inserted

> INSERT INTO TBL_PRJ_TICKET VALUES (‘002023′,14.93,00003,’B-001′,’07-JUL-11’)

1 rows inserted

> INSERT INTO TBL_PRJ_TICKET VALUES (‘002024′,63.24,00004,’B-002′,’12-DEC-10’)

1 rows inserted

> INSERT INTO TBL_PRJ_TICKET VALUES (‘002025′,58.36,00005,’C-008′,’01-JAN-11’)

1 rows inserted

> INSERT INTO TBL_PRJ_TICKET VALUES (‘002026′,43.25,00006,’B-030′,’03-MAR-10’)

1 rows inserted

> INSERT INTO TBL_PRJ_TICKET VALUES (‘002027′,78.56,00007,’A-017′,’07-APR-11’)

1 rows inserted

> INSERT INTO TBL_PRJ_TICKET VALUES (‘002028′,35.45,00008,’B-003′,’06-MAY-12’)

1 rows inserted

·

Used to list the contents of all tables after inserting the initial sample data

> /* CONTENT OF TABLE TRAVEL*/

> SELECT * FROM TBL_PRJ_TRAVEL

TRV_CODE TRV_DESTINATION TRV_ORIGIN TRV_DATE_START

———————- ——————– ——————– ————————-

1 DELWARE AKRON 04-NOV-11

2 COLUMBUS CLEVELAND 06-JUN-11

3 CLEVELAND CINCINATTI 07-JUL-11

4 AKRON CIRCLEVILLE 12-DEC-10

5 DAYTON COLUMBUS 01-JAN-11

6 PITTSBURG COLUMBUS 03-MAR-10

7 CINCINATTI DELWARE 07-APR-11

8 CIRCLEVILLE PITTSBURG 06-MAY-12

8 rows selected

> /* CONTENT OF TABLE TPASSENGER*/

> SELECT * FROM TBL_PRJ_PASSENGER

PASSENGER_ID PASSENGER_FN PASSENGER_LN ENFANT_OR_ADULT HANDICAPPED

——————– ————————- ——————– ————— ———–

PASSGR-#1321 FRANCK YEBOUE ADULT NO

PASSGR-#1331 YANNICK HARRIS ADULT NO

PASSGR-#1341 ROGER BRIAN ADULT NO

PASSGR-#1351 DOUGLAS ROGER ADULT NO

PASSGR-#1361 HENRY MOORE ENFANT NO

PASSGR-#1371 LIZ ZEN ADULT YES

PASSGR-#1381 NENE DORA ADULT NO

PASSGR-#1301 HELEN LEWIS ENFANT NO

8 rows selected

> /* CONTENT OF TABLE BAGGAGE*/

> SELECT *

FROM TBL_PRJ_BAGGAGE

BAG_NUM PASSENGER_ID BAG_WEIGHT BAG_OVERWEIGHT PAYMENT_METHOD

———————- ——————– ———————- ———————- ————–

1123 PASSGR-#1361 14 0 CASH

1124 PASSGR-#1321 0 0 CASH

1125 PASSGR-#1371 23 13 CREDIT

1126 PASSGR-#1331 17 0 DEBIT

1127 PASSGR-#1301 3 0 CASH

7728 PASSGR-#1341 12 0 CREDIT

9929 PASSGR-#1381 1 0 CASH

3630 PASSGR-#1351 10 650 CREDIT

8 rows selected

> /* CONTENT OF TABLE SEAT */

> SELECT * FROM TBL_PRJ_SEAT

SEAT_NUM AISLE

——– —–

A-001 A

A-003 A

B-001 A

B-002 B

B-003 B

A-017 A

B-030 B

C-008 C

8 rows selected

> /* CONTENT OF TABLE TICKET */

> SELECT * FROM TBL_PRJ_TICKET

TICKET_NUM TICKET_PRICE TRV_CODE SEAT_NUM TRV_DATE_ARRIVAL

———————- ———————- ———————- ——– ————————-

2021 45.23 1 A-001 04-NOV-11

2022 42.32 2 A-003 06-JUN-11

2023 14.93 3 B-001 07-JUL-11

2024 63.24 4 B-002 12-DEC-10

2025 58.36 5 C-008 01-JAN-11

2026 43.25 6 B-030 03-MAR-10

2027 78.56 7 A-017 07-APR-11

2028 35.45 8 B-003 06-MAY-12

8 rows selected

·

Used to produce basic system queries including table joins

> /*WANT TO KNOW EACH PASSENGER AND THE BAGGAGE NUMBER THAT HE/SHE POSSESSES */

> SELECT PASSENGER_FN, PASSENGER_LN, P.PASSENGER_ID, BAG_NUM

FROM TBL_PRJ_PASSENGER P, TBL_PRJ_BAGGAGE D

WHERE P.PASSENGER_ID = D.PASSENGER_ID

ORDER BY P.PASSENGER_LN

PASSENGER_FN PASSENGER_LN PASSENGER_ID BAG_NUM

————————- ——————– ——————– ———————-

ROGER BRIAN PASSGR-#1341 7728

NENE DORA PASSGR-#1381 9929

YANNICK HARRIS PASSGR-#1331 1126

HELEN LEWIS PASSGR-#1301 1127

HENRY MOORE PASSGR-#1361 1123

DOUGLAS ROGER PASSGR-#1351 3630

FRANCK YEBOUE PASSGR-#1321 1124

LIZ ZEN PASSGR-#1371 1125

8 rows selected

> /*WANT TO KNOW THE DESTINATIONS AND THE PRICE FOR THE TRIP FOR TICKETS THAT ARE MORE THAN $25*/

> SELECT TICKET_PRICE, TRV_DATE_START, TRV_DATE_ARRIVAL, T.TRV_CODE

FROM TBL_PRJ_TICKET T , TBL_PRJ_TRAVEL V

WHERE T.TRV_CODE = V.TRV_CODE

AND T.TICKET_PRICE > 50

TICKET_PRICE TRV_DATE_START TRV_DATE_ARRIVAL TRV_CODE

———————- ————————- ————————- ———————-

63.24 12-DEC-10 12-DEC-10 4

58.36 01-JAN-11 01-JAN-11 5

78.56 07-APR-11 07-APR-11 7

·

Used to create manager view

> /* CREATE A MANAGER VIEW THAT SELECT COMPLETE INFORMATION FROM TABLES */

> CREATE VIEW MANAGER AS (

SELECT TICKET_NUM, V.TRV_CODE, S.SEAT_NUM, AISLE, TRV_DESTINATION, TRV_ORIGIN, TICKET_PRICE

FROM TBL_PRJ_TICKET T, TBL_PRJ_SEAT S, TBL_PRJ_TRAVEL V

WHERE S.SEAT_NUM = T.SEAT_NUM

AND V.TRV_CODE = T.TRV_CODE)

CREATE VIEW succeeded.

> /* DISPLAY THE MANAGER’S VIEW CREATED */

> SELECT * FROM MANAGER

TICKET_NUM TRV_CODE SEAT_NUM AISLE TRV_DESTINATION TRV_ORIGIN TICKET_PRICE

———————- ———————- ——– —– ——————– ——————– ———————-

2021 1 A-001 A DELWARE AKRON 45.23

2022 2 A-003 A COLUMBUS CLEVELAND 42.32

2023 3 B-001 A CLEVELAND CINCINATTI 14.93

2024 4 B-002 B AKRON CIRCLEVILLE 63.24

2025 5 C-008 C DAYTON COLUMBUS 58.36

2026 6 B-030 B PITTSBURG COLUMBUS 43.25

2027 7 A-017 A CINCINATTI DELWARE 78.56

2028 8 B-003 B CIRCLEVILLE PITTSBURG 35.45

8 rows selected

·

Used to create an updateable view for data entry purposes

> /* CREATE AN UPDATEABLE VIEW */

> CREATE VIEW UPDATEABLE_V AS (

SELECT BAG_NUM, P.PASSENGER_ID , PASSENGER_FN, PASSENGER_LN, BAG_WEIGHT ,BAG_OVERWEIGHT , PAYMENT_METHOD, Handicapped

FROM TBL_PRJ_PASSENGER P, TBL_PRJ_BAGGAGE B

WHERE P.PASSENGER_ID = B.PASSENGER_ID)

CREATE VIEW succeeded.

> /* DISPLAY DATA IN THE UPDATEABLE VIEW */

> SELECT * FROM UPDATEABLE_V

BAG_NUM PASSENGER_ID PASSENGER_FN PASSENGER_LN BAG_WEIGHT BAG_OVERWEIGHT PAYMENT_METHOD HANDICAPPED

———————- ——————– ————————- ——————– ———————- ———————- ————– ———–

1123 PASSGR-#1361 HENRY MOORE 14 0 CASH NO

1124 PASSGR-#1321 FRANCK YEBOUE 0 0 CASH NO

1125 PASSGR-#1371 LIZ ZEN 23 13 CREDIT YES

1126 PASSGR-#1331 YANNICK HARRIS 17 0 DEBIT NO

1127 PASSGR-#1301 HELEN LEWIS 3 0 CASH NO

7728 PASSGR-#1341 ROGER BRIAN 12 0 CREDIT NO

9929 PASSGR-#1381 NENE DORA 1 0 CASH NO

3630 PASSGR-#1351 DOUGLAS ROGER 10 650 CREDIT NO

8 rows selected

> /* UPDATE CUSTOMERS USING THE UPDATEABLE VIEW CREATED */

> /* SHOW THE DATA WE WANT TO UPDATE */

> SELECT BAG_NUM, PASSENGER_ID, BAG_WEIGHT, BAG_OVERWEIGHT, PAYMENT_METHOD

FROM TBL_PRJ_BAGGAGE

WHERE PASSENGER_ID = ‘PASSGR-#1371’

OR PAYMENT_METHOD = ‘CREDIT’

BAG_NUM PASSENGER_ID BAG_WEIGHT BAG_OVERWEIGHT PAYMENT_METHOD
———————- ——————– ———————- ———————- ————–
1125 PASSGR-#1371 23 13 CREDIT
7728 PASSGR-#1341 12 0 CREDIT
3630 PASSGR-#1351 10 650 CREDIT

> /* UPDATE DATA USING THE UPDATEABLE VIEW */

> UPDATE UPDATEABLE_V

SET PAYMENT_METHOD = ‘DEBIT’

WHERE PASSENGER_ID = ‘PASSGR-#1371’

1 rows updated

> /*CHECK IF THE DATA WAS UPDATED */

> SELECT BAG_NUM, PASSENGER_ID, BAG_WEIGHT, BAG_OVERWEIGHT, PAYMENT_METHOD
FROM TBL_PRJ_BAGGAGE
WHERE PASSENGER_ID = ‘PASSGR-#1371’

OR PAYMENT_METHOD = ‘DEBIT’

BAG_NUM PASSENGER_ID BAG_WEIGHT BAG_OVERWEIGHT PAYMENT_METHOD
———————- ——————– ———————- ———————- ————–

1125 PASSGR-#1371 23 13 DEBIT

1126 PASSGR-#1331 17 0 DEBIT

·

Used to create roles that define access privileges

Access privileges and limits for the tables in this database will be set-up by the DBA after the database is implemented and tested.

·

Used to create reports with headers / footers / column headings / page numbers

> /* CREATE reports with headers / footers / column headings / page numbers */

> CREATE VIEW MANAGER AS (
SELECT TICKET_NUM, V.TRV_CODE, S.SEAT_NUM, AISLE, TRV_DESTINATION, TRV_ORIGIN, TICKET_PRICE
FROM TBL_PRJ_TICKET T, TBL_PRJ_SEAT S, TBL_PRJ_TRAVEL V
WHERE S.SEAT_NUM = T.SEAT_NUM
AND V.TRV_CODE = T.TRV_CODE)
CREATE VIEW succeeded.
> /* DISPLAY THE MANAGER’S VIEW CREATED */
> SELECT * FROM MANAGER
TICKET_NUM TRV_CODE SEAT_NUM AISLE TRV_DESTINATION TRV_ORIGIN TICKET_PRICE
———————- ———————- ——– —– ——————– ——————– ———————-
2021 1 A-001 A DELWARE AKRON 45.23
2022 2 A-003 A COLUMBUS CLEVELAND 42.32
2023 3 B-001 A CLEVELAND CINCINATTI 14.93
2024 4 B-002 B AKRON CIRCLEVILLE 63.24
2025 5 C-008 C DAYTON COLUMBUS 58.36
2026 6 B-030 B PITTSBURG COLUMBUS 43.25
2027 7 A-017 A CINCINATTI DELWARE 78.56
2028 8 B-003 B CIRCLEVILLE PITTSBURG 35.45
8 rows selected