Database Design

Q.1. In a police station many people lodge First Information Reports. These FIR’s are given to the investigation team headed by an officer. There are many junior officers under this officer. These officers in turn report to an area officer. Draw an ER-Diagram for the above scenario.

Q.2. Draw ER-Diagrams for the tables given below in Q.3.

Q.3. Create the tables as given below:

TABLE NAME - BRANCH

FIELD NAMENULL?DATA TYPE

BRANCH CODENOT NULLCHAR(4)

ADD1VARCHAR2(20)

ADD2VARCHAR2(20)

CITYVARCHAR2(25)

TELEPHONENUMBER(8)

TABLE NAME – AIRBUS

FIELD NAMENULL?DATA TYPE

AIRBUSNONOT NULLCHAR(5)

FIRST_CAPNUMBER(3)

BUS_CAPNUMBER(3)

ECO_CAPNUMBER(3)

FIRST_WL_CAPNUMBER(3)

BUS_WL_CAPNUMBER(3)

ECO_WL_CAPNUMBER(3)

TABLE NAME – SERVICE

FIELD NAMENULL?DATA TYPE

SS_CODENOT NULLCHAR(2)

SS_DESCNOT NULLVARCHAR2(25)

SS_FARENUMBER(5)

TABLE NAME – FARE

FIELD NAMENULL?DATA TYPE

ROUTE_CODENOT NULLCHAR(7)

ROUTE_DESCNOT NULLVARCHAR2(25)

ORIGINNOT NULLVARCHAR2(15)

DESTINATIONNOT NULLVARCHAR2(15)

FIRST_FARENUMBER(5)

BUS_FARENUMBER(5)

ECO_FARENUMBER(5)


TABLE NAME – FLIGHT_SCH

FIELD NAMENULL?DATA TYPE

FLIGHTNONOT NULLCHAR(4)

AIRBUSNOCHAR(5)

ROUTE_CODECHAR(7)

DEPRT_TIMECHAR(5)

JOURNEY_HRSCHAR(5)

FLIGHT_DAY1NUMBER(1)

FLIGHT_DAY2NUMBER(1)

TABLE NAME – FLIGHT

FIELD NAMENULL?DATA TYPE

FLIGHTNONOT NULLCHAR(4)

FLIGHT_DATENOT NULLDATE

FIRST_SEATS_BKNUMBER(3)

BUS_SEATS_BKNUMBER(3)

ECO_SEATS_BKNUMBER(3)

TABLE NAME – CONTROL

FIELD NAMENULL?DATA TYPE

AIR_TAXNUMBER(4)

EXCESS_BAG_CHGNUMBER(3)

FIRST_BG_LIMITNUMBER(3)

BUS_ BG_LIMITNUMBER(3)

ECO_ BG_LIMITNUMBER(3)

CANC_DEDUC_12NUMBER(2)

CANC_DEDUC_6NUMBER(2)

CANC_DEDUC_3NUMBER(2)

TABLE NAME – CANCELLATION

FIELD NAMENULL?DATA TYPE

PNRNOT NULLNUMBER(4)

FLIGHTNOCHAR(4)

FLIGHT_DATEDATE

CLASSCHAR(1)

RESERV_DATEDATE

PASS_NAMEVARCHAR2(20)

PASS_ADD1VARCHAR2(20)

PASS_ADD2VARCHAR2(20)

PASS_ADD3VARCHAR2(20)

PASSPORT_NONUMBER(8)

SS_CODECHAR(2)

CREDIT_CARD_NONUMBER(8)

CANCEL_DATEDATE

TOTAL_FARENUMBER(8,2)

BRANCH_CODECHAR(4)


TABLE NAME – RESERVATION

FIELD NAMENULL?DATA TYPE

PNRNOT NULLNUMBER(4)

FLIGHTNOCHAR(4)

FLIGHT_DATEDATE

CLASSCHAR(1)

RESERV_DATEDATE

PASS_NAMEVARCHAR2(20)

PASS_ADD1VARCHAR2(20)

PASS_ADD2VARCHAR2(20)

PASS_ADD3VARCHAR2(20)

PASSPORT_NONUMBER(8)

SS_CODECHAR(2)

CREDIT_CARD_NONUMBER(8)

PASS_STATUSCHAR(1)

TOTAL_FARENUMBER(8,2)

BRANCH_CODECHAR(4)

TABLE NAME – EMPLOYEE

FIELD NAMENULL?DATA TYPE

EMPNONOT NULLNUMBER(5)

EMP_NAMEVARCHAR2(30)

DESIGNATIONCHAR(10)

DATE_OF_JOINDATE

SALARYNUMBER(9,2)

DEPTNONUMBER(2)

TABLE NAME – DEPARTMENT

FIELD NAMENULL?DATA TYPE

DEPTNONOT NULLNUMBER(2)

DEPT_NAMEVARCHAR2(30)

DEPT_LOCVARCHAR2(20)

TABLE NAME – ALLOWANCE

FIELD NAMENULL?DATA TYPE

DESIGNATIONNOT NULLCHAR(10)

SP_ALLOWANCENUMBER(8,2)

CONVEYANCENUMBER(8,2)


Data Manipulation Language

Q.4. Insert the following data in their respective tables.

BRANCH

BRANCH_CODE

ADD1

ADD2

CITY

TELEPHONE

SAN

9, Avenue

EllisBridge

San Francisco

87897531

NYK

Bakery Lane

MountTower

New York

78546567

FLO

New Street

Dollis Lane

Florida

56578990

BOS

1st Floor

EstateTower

Boston

65897856

LAX

Heck Lane

North Apartment

Los Angeles

45764521

HOU

408, Ont Apt

Stevens Road

Houston

87677867

AMS

390, Howls Lane

North Avenue

Amsterdam

76765665

LOU

40, Saint Tower

Dell Road

Saint Louis

57897654

WAS

23, Hawk tower

Turkey Road

Washington

43678990

PHL

308, Hou Sau

Houston Road

Philadelphia

34562375

LON

34, Avenue Street

Rust Road

London

56783487

AIRBUS

AIRBUSNO

FIRST_CAP

BUS_CAP

ECO_CAP

FIRST_WL_CAP

BUS_WL_CAP

ECO_WL_CAP

AB01

100

120

130

10

15

20

AB02

80

100

120

8

10

12

AB03

100

120

130

10

15

20

AB04

80

100

120

8

10

12

AB05

80

100

120

8

10

12

AB06

100

120

130

10

15

20

AB07

80

100

120

8

10

12

SERVICE

SS_CODE

SS_DESC

SS_FARE

CC

Child Care

40

NU

Nurse

50

WC

Wheel Chair

30

NA

Not Availed

0

FARE

ROUTE_CODE

ROUTE_DESC

ORIGIN

DESTINATION

FIRST_FARE

BUS_FARE

ECO_FARE

SAN-LOU

San Francisco- St. Louis

San Francisco

St. Louis

400

350

300

FLO-WAS

Florida-Washington

Florida

Washington

300

250

200

BOS-PHL

Boston-Philadelphia

Boston

Philadelphia

250

200

150

NYK-LON

New York-London

New York

London

600

500

400

NYK-AMS

New York-Amsterdam

New York

Amsterdam

650

550

450

LAX-HOU

Los Angeles-Houston

Los Angeles

Houston

250

200

150

LOU-SAN

St. Louis-San Francisco

St. Louis

San Francisco

400

350

300

WAS-FLO

Washington-Florida

Washington

Florida

300

250

200

PHL-BOS

Philadelphia-Boston

Philadelphia

Boston

250

200

150

LON-NYK

London-New York

London

New York

600

500

400

AMS-NYK

Amsterdam-New York

Amsterdam

New York

650

550

450

HOU-LAX

Houston-Los Angeles

Houston

Los Angeles

250

200

150

FLIGHT_SCH

FLIGHTNO

AIRBUSNO

ROUTE_CODE

JOURNEY_HRS

DEPRT_TIME

FLIGHT_DAY1

FLIGHT_DAY1

WF13

AB01

WAS-FLO

3.30

9:00

1

3

FW24

AB01

FLO-WAS

3.30

5:00

2

4

SL36

AB02

SAN-LOU

5

13:30

3

6

LS47

AB02

LOU-SAN

5

7:30

4

7

NL35

AB03

NYK-LON

11

13:00

3

5

LN46

AB03

LON-NYK

11

13:00

4

6

BP14

AB04

BOS-PHL

2

08:15

1

4

PB25

AB04

PHL-BOS

2

11:15

2

5

NA36

AB05

NYK-AMS

12

18:00

3

6

AN47

AB05

AMS-NYK

12

08:00

4

7

LH13

AB06

LAX-HOU

2.30

14:00

1

3

HL24

AB06

HOU-LAX

2.30

14:00

2

4

FLIGHT

FLIGHTNO

FLIGHT_DATE

FIRST_SEATS_BK

BUS_SEATS_BK

ECO_SEATS_BK

WF13

01-Sep-98

1

1

0

NA36

01-Sep-98

0

0

2

AN47

02-Sep-98

1

1

0

PB25

03-Sep-98

3

0

0

NL35

08-Sep-98

1

0

2

NL35

10-Sep-98

0

0

0

CONTROL

AIR_
TAX

EXCESS_BAG_
CHG

FIRST_
BAG_
LIMIT

BUS_
BAG_
LIMIT

ECO_
BAG_
LIMIT

CANC_

DEDUC_

12

CANC_

DEDUC_

6

CANC_

DEDUC_

3

10

20

50

40

30

15

10

5

EMPLOYEE

EMPNO

EMP_NAME

DESIGNATION

DATE_OF_JOIN

SALARY

DEPTNO

10001

Robert

Officer

01-Dec-85

1000

10

10002

Allan

Clerk

14-May-82

500

10

10003

Martin

Manager

23-Dec-84

3500

20

10004

James

Analyst

22-Jul-90

5000

30

10005

John

Analyst

22-Jul-90

4900

30

10006

Jones

Clerk

16-Apr-86

950

30

DEPARTMENT

DEPTNO

DEPT_NAME

DEPT_LOC

10

Marketing

London

20

Accounts

America

30

Sales

New York

40

Software

Boston

50

Production

Boston

ALLOWANCE

DESIGNATION

SP_ALLOWANCE

CONVEYANCE

Manager

1000

500

Officer

800

400

Analyst

1200

500

Clerk

500

300


CANCELLATION

PNR

FLIGHTNO

FLIGHT
_DATE

CLASS

RESERV
_DATE

PASS_
NAME

PASS_
ADD1

PASS_
ADD2

PASS_
ADD3

PASS
PORT
_NO

SS_
CODE

CREDIT
_CARD
_NO

CANCEL
_DATE

TOTAL
_FARE

BRANCH
_CODE

1001

AN47

02-Sep-98

B

02-Aug-98

Nickel

67, Hill View

Rock Lane

Amste
rdam

23588

NA

14-Aug-98

550

AMS

1002

AN47

02-Sep-98

B

02-Aug-98

Jeffy

67, Hill View

Rock Lane

Amste
rdam

23668

NA

14-Aug-98

550

AMS

1003

AN47

02-Sep-98

B

02-Aug-98

D’mello

67, Hill View

Rock Lane

Amste
rdam

23634

NA

14-Aug-98

550

AMS

1006

NA36

01-Sep-98

F

08-Aug-98

Tim

56, Burli Game

Hamilton Lane

New York

463221

NA

11-Aug-98

650

NYK

1007

NA36

01-Sep-98

F

08-Aug-98

Jerry

56, Burli Game

Hamilton Lane

New York

463227

NA

11-Aug-98

650

NYK

RESERVATION

PNR

FLIGHTNO

FLIGHT
_DATE

CLASS

RESERV
_DATE

PASS_
NAME

PASS_
ADD1

PASS_
ADD2

PASS_
ADD3

PASS
PORT
_NO

SS_
CODE

CREDIT
_CARD
_NO

PASS_
STATUS

TOTAL
_FARE

BRANCH
_CODE

1017

NL35

08-Sep-98

F

15-Aug-98

Jerry

56, Burli Game

Hamilton Lane

New York

463227

NA

C

600

NYK

1005

AN47

02-Sep-98

F

03-Aug-98

Roune

45, Hill View

Rock Lane

Amste
rdam

23688

WC

C

680

AMS

1011

AN47

02-Sep-98

B

13-Aug-98

Mildred

Heck Street

Nr. New Bridge

Amste
rdam

789732

WC

56894

C

550

AMS

1012

NA36

01-Sep-98

E

15-Aug-98

Allen

420, Red Hunt

Nr. Ellis PO

New York

876543

NU

C

500

NYK

1013

NA36

01-Sep-98

E

15-Aug-98

Nick

420, Red Hunt

Nr. Ellis PO

New York

876542

NA

C

450

NYK

1015

NL35

08-Sep-98

E

15-Aug-98

Jane

47, Turry Apt.

Tarnes Road

New York

456788

NA

C

400

NYK

1016

NL35

08-Sep-98

E

15-Aug-98

Billi

47, Turry Apt.

Tarnes Road

New York

436768

NA

C

400

NYK

1008

PB25

03-Sep-98

F

10-Aug-98

Jenny

1,Borli

Red Wood Lane

Phila

Delphia

456789

NA

C

250

PHL

1009

PB25

03-Sep-98

F

10-Aug-98

James

1,Borli

Red Wood Lane

Phila

Delphia

456788

NA

C

250

PHL

1010

PB25

03-Sep-98

F

10-Aug-98

Harry

1,Borli

Red Wood Lane

Phila

Delphia

456787

NA

C

250

PHL

1004

WF13

01-Sep-98

F

02-Aug-98

John

480, Belmont

ViewBridge

Washin

gton

896754

NA

C

300

WAS

1014

WF13

01-Sep-98

B

15-Aug-98

Albert

Robert Lane

Cooks Street

Washin

Gton

678954

NA

C

250

WAS


Data Retrieval

Q.5. List the employees belonging to department 20.

Q.6. List the employees who are earning more than 1200 but less than 4000.

Q.7. List the employees who have joined after 1st Jan 84 in the order of the joining date.

Q.8. List the employees who are either in Officer or Manager position.

Q.9. List the employees who are located at New York.

Q.10. List the employees who are in the Sales department.

Q.11. List the departments that do not have any employees.

Q.12. List the employees who are earning more than Robert.

Q.13. Find out how many employees are there in the organization.

Q.14. Find out how many employees are working in Sales department.

Q.15. Find out the total salaries paid to the employees.

Q.16. What is the average salary paid to the employees?

Q.17. What is the minimum salary paid in department 30?

Q.18. Display names & grades of employees based on their designation.

DesignationGrade

ManagerA

OfficerB

AnalystC

ClerkD

Q.19. Display employee names & date of join, Joining date should be displayed in the following format:

26, January Nineteen Ninety Eight.

Q.20.Find out how long an employee has worked in the terms of number of:

- Days

- Months

- Years

Q.21. Display the total salaries department wise.

Q.22. Display the maximum salaries in each department along with the name of the department.

Q.23. Display the total salary (Salary+Sp_Allowance+Conveyance) of each employee in the order of total salary.

Q.24. List the number of employees along with their department numbers in each department.

Q.25. List the department wise total salary.

Q.26. List the number of employees in each designation in the descending order.

Q.27.List the total salary, maximum & minimum along with the average salary of each employee designation wise.

Q.28.List the total salary, maximum & minimum along with the average salary of each employee designation wise for department 30.

Q.29.List the total salary, maximum & minimum along with the average salary of each employee designation wise for department 30 & display only those rows that have their average salary greater than 1000.

Q.30. List the total salary of the employees for each designation department wise.

Q.31. List the employee details such as his employee number, name, date_of_join, basic salary & designation for department=’Marketing’.

Q.32. List the employee details such as his employee number, name, date_of_join, basic salary & designation for the employees working in the location ‘America’.

Q.33. List the departments where there are no employees functioning.

Q.34. Create a view called temp that consists of the employee number, name, date_of_joining from the employee table, department number, department name & location from the department table.

Q.35.Use predefined exceptions. Raise & handle storage_error exception when PL/SQL runs out of memory, raise & handle no_data_found exception when a select statement returns no rows, and raise & handle divide by zero exception if a no. is divided by zero.

Q.36.Use Cursor to retrieve reservation details based on the class that is passed as a parameter.

Q.37.Calculate the fare depending on the class and the special service availed using cursor.

Q.38.Create a procedure, which receives branch code as a parameter and calculates the total collection made due to reservation and lost due to cancellation. It also counts the total no. of reservation and cancellations.

Q.39. Create a function, which receives day no. of the week as a parameter and return the day in character format.

Q.40.Create a package named ‘airline’ which will pack the procedures and functions that we have created. Illustrates objects such as a record type and variable can be declared and used in the package.

Q.41.Create a trigger which will display total fare amount for the passenger, when a reservation is made.

Q.42.Accept the ‘AirbusNo’ and display its waiting capacity for the different classes.

Q.43.Accept the ‘AirbusNo’ and display the flights scheduled for that airbus.

Q.44.Accept flight day in characters and display the flights scheduled on the day. For example,if the user enters ‘TUE’ then display all the flights scheduled on Tuesday.

Q.45.Write a procedure that accepts ‘FlightNo’ and ‘Date’. Display the total seats booked for the different classes.

Q.46.Write a procedure that receives FlightNo and Flight_Date as a parameter. Display the passengers booked on that flight.