1) Consider the following entities and their relationship.

Customer (c_no, c_name, c_city, c_ph_no)

Ticket (t_no, booking_date, fare, traveling_date)
Relationship between Customer and Ticket is one-to-many.
Constraints: primary key, foreign key
c_name should not be null,
fare should be greater than zero.
Create a RDB in 3NF and write PL/SQL blocks in Oracle for the
following:
1) Write a procedure to display names of customer who have booked bus on
given date.
2) Write a trigger that restricts insertion of ticket having traveling date
smaller than booking date.(Raise user defined exception and give
appropriate message)

2) Consider the following entities and their relationship.

Student (s_reg_no, s_name, s_class)
Competition (comp_no, comp_name, comp_type)

Relationship between Student and Competition is many-to-many with

descriptive attribute rank and year.

Constraints: primary key, foreign key,

primary key for third table(s_reg_no, comp_no, year),
s_name and comp_name should not be null,
comp_type can be sports or academic.

Create a RDB in 3NF and write PL/SQL blocks in Oracle for the
following:

1) Write a function which will accept s_reg_no of student and returns total
number of competition in which student has participated in a given year.
2) Write a cursor which will display year wise details of competitions held.
(Use parameterized cursor)

3) Consider the following entities and their relationship.

Owner (o_no, o_name, o_city, o_ph_no)
Estate (e_no, e_type, e_city, e_price)

Relationship between Owner and Estate is one-to-many.

Constraints : primary key, foreign key,

o_name should not be null,
e_type can be flat, bungalow or land.

Create a RDB in 3NF and write PL/SQL blocks in Oracle for the
following:

1) Write a procedure which will accept owner number and display details of
all estates of given owner which belongs to pune city.
2) Write a cursor which will display type wise estate details. (Use
parameterized cursor)

4) Consider the following entities and their relationship.

Bus(bus_no, capacity, source, destination)
Driver(driver_no, driver_name, license_no, addr, age, salary)

Relationship between Bus and Driver is many-to-many with descriptive

attribute date_of_duty_allotted and shift.

Constraints: primary key, foreign key,

primary key for third table (bus_no,
driver_no,date_of_duty_allotted),
driver_name should not be null,
shift can be morning or evening.

Create a RDB in 3NF and write PL/SQL blocks in Oracle for the
following:

1) Write a function which will return name of driver having maximum

salary.
2) Write a cursor which will display date wise bus and their driver details.

5) Consider the following entities and their relationship.

Drug(d_no, d_name, company, price)
Medical _store(m_no, m_name, m_city, ph_no)
Relationship between Drug and Medical_Store is many-to-many with
descriptive attribute quantity.

Constraints: primary key, foreign key,

m_name and d_name should not be null,
m_city can be pune or pimpri.

Create a RDB in 3NF and write PL/SQL blocks in Oracle for the
following:

1) Write a package, which consists of one procedure and one function. Pass
drug number as a parameter to procedure and display details of that drug.
Pass city as a parameter to a function and return total number of
medical_store in given city.

2) Write a trigger that restricts insertion and updation of drug having price
less than zero. (Raise user defined exception and give appropriate
message

6) Consider the following entities and their relationship.

Movie (mvno,mvname,releaseyear)
Actor (actno,actname)

Relationship between Movie and Actor is many-to-many with

descriptive attribute rate of actor for movie.

Constraints : primary key, foreign key,

Actname and mvname should not be null.

Create a RDB in 3NF and write PL/SQL blocks in Oracle for the
following:

1) Create or replace a PL/SQL procedure to display details of all movies of

actor ‘Amitabh’.
2) Write a cursor to display names of all movies which are released in year
2002.

7) Consider the following entities and their relationship.

Route(route_no, source, destination, no_of_station)
Bus (bus_no, capacity, depot_name)

Relationship between Route and Bus is one-to-many

Constraints: primary key, foreign key,

depot_name should not be null,
bus capacity should be greater than 40.

Create a RDB in 3NF and write PL/SQL blocks in Oracle for the
following:

1) Write a procedure which will display all bus details for a given route.
2) Write a trigger that restricts insertion of route having number of station
less than zero. (Raise user defined exception and give appropriate
message)

8) Consider the following entities and their relationship.

University (u_no, u_name, u_city)
College (c_no, c_name, c_city, year_of_establishment )

Relationship between University and College is one-to-many

Constraints: primary key, foreign key,

u_name and c_name should not be null.

Create a RDB in 3NF and write PL/SQL blocks in Oracle for the
following:

1) Write a package, which consists of one procedure and one function. Pass
university number as a parameter to procedure and display details of that
university. Pass city as a parameter to a function and return total number
of colleges in given city.

2) Write a cursor which will display university wise their college details.
(Use parameterized cursor)

9) Consider the following entities and their relationship.

Patient (p_no, p_name, p_addr)
Doctor (d_no, d_name, d_addr, city)

Relationship between Patient and Doctor is many-to-many with

descriptive attribute disease and no_of_visits.

Constraints: primary key, foreign key,

primary key for third table(p_no, d_no, disease),
p_name and d_name should not be null.

Create a RDB in 3NF and write PL/SQL blocks in Oracle for the
following:

1) Write a procedure which will display patient detail who has visited more
than 3 times to the given doctor for ‘Diabetes’.
2) Write a trigger which will restrict insertion or updation of doctor_patient
details having no_of_visits less than zero. (Raise user defined exception
and give appropriate message)

10) Consider the following entities and their relationship.

Crop (c_no, c_name, c_season, pesticides)
Farmer (f_no, f_name, f_location)

Relationship between Crop and Farmer is many-to-many with

descriptive attribute year.

Constraints: primary key, foreign key,

primary key for third table(c_no, f_no, year),
c_name and f_name should not be null,
c_season can be rabi or kharif.

Create a RDB in 3NF and write PL/SQL blocks in Oracle for the
following:

1) Write a function which will return total number of farmers harvesting

given crop in a given year.
2) Write a cursor which will display season wise information of crops
harvested by the farmers. (Use parameterized cursor)
11) Consider the following entities and their relationship.
Researcher (r_no, r_name, r_city)
Research_Paper (rp_no, rp_title, rp_subject, rp_level)

Relationship between Researcher and Research_Paper is many-to-many

with descriptive attribute year.

Constraints: primary key, foreign key,

r_name and rp_title should not be null,
rp_subject can be computer, electronics or finance.
rp_level can be state, national or international.

Create a RDB in 3NF and write PL/SQL blocks in Oracle for the
following:

1) Write a procedure which will display details of research paper of a given

subject for a specified year.
2) Write a trigger before insert or update of each row of research_paper
published after 2010 be entered into table. (Raise user defined exception
and give appropriate message)

12) Consider the following entities and their relationship.

Employee (eno, ename, city, deptname)
Project (pno, pname, status)

Relationship between Employee and Project is many-to-many with

descriptive attribute number_of_days works on that project.

Constraints: primary key

Status has to be C-completed, P-progressive, I-
incomplete

Create a RDB in 3NF and write PL/SQL blocks in Oracle for the
following:

1) Write a function to return number of incomplete projects of a given

employee.
2) Create or replace a trigger before update on status of project such that
status of project once completed cannot be changed. Display appropriate
message.

13) Consider the following entities an d their relationship.

Client (c_no, c_name, c_addr, birth_date)
Policy_info (p_no, p_name, maturity_amt, prem_amt, policy_term)

Relationship between Client and Policy_info is many-to-many with

descriptive attribute date_of_purchase.

Constraints: primary key, foreign key,

c_name and p_name should not be null,
policy_term should be greater than zero.

Create a RDB in 3NF and write PL/SQL blocks in Oracle for the
following:

1) Write a procedure which will display all policy details of given client for
a given year.
2) Write a trigger which restricts insertion of policy_info having maturity
amount less than premium amount. (Raise user defined exception and
give appropriate message)

14) Consider the following entities and their relationship.

Company (c_no, c_name, c_city, c_share_value)
Person (p_no, p_name, p_city, p_ph_no)

Relationship between Company and Person is many-to-many with

descriptive attribute no_of_shares.

Constraints: primary key, foreign key,

c_name and p_name should not be null,
no_of_shares should be greater than zero.

Create a RDB in 3NF and write PL/SQL blocks in Oracle for the
following:
1) Write a function which will return name of person having maximum
number of shares of given company.
2) Write a cursor which will display person wise share details. (Use
parameterized cursor)

15) Consider the following entities and their relationship.

Person (p_no, p_name, p_addr)
Investment (inv_no, inv_name, inv_date, inv_amt)

Relationship between Person and Investment is one-to-many.

Constraints: primary key, foreign key,

p_name and inv_name should not be null,
inv_amt should be greater than 10000.

Create a RDB in 3NF and write PL/SQL blocks in Oracle for the
following:

1) Write a procedure which will display details of person, made investment

on given date.
2) Write a trigger that restricts insertion or updation of investment having
inv_date greater than current date. (Raise user defined exception and give
appropriate message)

16) Consider the following entities and their relationship.

Customer (c_no, c_name, c_city, c_ph_no )
Ticket (t_no, booking_date, fare, traveling_date)

Relationship between Customer and Ticket is one-to-many.

Constraints: primary key, foreign key,

c_name should not be null,
fare should be greater than zero.

Create a RDB in 3NF and write PL/SQL blocks in Oracle for the
following:
1) Write a function which will calculate and return total fare collected from
customers on given date.
2) Write a cursor which will display date wise ticket booked by customer.
(Use parameterized cursor)

17) Consider the following entities and their relationship.

Student (s_reg_no, s_name, s_class)
Competition (comp_no, comp_name, comp_type)

Relationship between Student and Competition is many-to-many with

descriptive attribute rank and year.

Constraints: primary key, foreign key,

primary key for third table(s_reg_no, comp_no, year)
s_name and comp_name should not be null,
comp_type can be sports or academic.

Create a RDB in 3NF and write PL/SQL blocks in Oracle for the
following:

1) Write a procedure which will accept year and type of competition as an

input and display details of competition accordingly.
2) Write a trigger that restricts insertion of rank value greater than 3. (Raise
user defined exception and give appropriate message)

18) Consider the following entities and their relationship.

Owner (o_no, o_name, o_city, o_ph_no)
Estate (e_no, e_type, e_city, e_price)

The Relationship between Owner and Estate is one-to-many.

Constraints: primary key, foreign key,

o_name should not be null,
e_type can be flat, bungalow or land.

Create a RDB in 3NF and write PL/SQL blocks in Oracle for the
following:
1) Write a function which will count and returns number of owners who
have purchase estate in the same city in which they live.
2) Write a trigger that restricts insertion or updation of estate having price
less than 1 lakh. (Raise user defined exception and give appropriate
message)

19) Consider the following entities and their relationship.

Bus(bus_no, capacity, source, destination)
Driver(driver_no, driver_name, license_no, addr, age, salary)

Relationship between Bus and Driver is many-to-many with descriptive

attribute date_of_duty_allotted and shift

Constraints: primary key, foreign key,

primary key for third table (bus_no, driver_no,
date_of_duty_allotted),
driver_name should not be null,
shift can be morning or evening.

Create a RDB in 3NF and write PL/SQL blocks in Oracle for the
following:

1) Write a procedure which will display bus_no and name of allotted driver
on given date and shift.
2) Write a trigger that restricts insertion or updation of driver table if driver
age is less than 18 or greater than 50. (Raise user defined exception and
give appropriate message).

20) Consider the following entities and their relationship.

Drug(d_no, d_name, company, price)
Medical _Store(m_no,m_name,m_city,ph_no)

Relationship between Drug and Medical_Store is many-to-many with

descriptive attribute quantity.

Constraints: primary key, foreign key,

m_name and d_name should not be null,
m_city can be pune or pimpri
Create a RDB in 3NF and write PL/SQL blocks in Oracle for the
following:

1) Write a function which will count total number of drugs available in

given medical store.
2) Write a cursor which will display medical store wise drug details with
available quantity.

21) Consider the following entities and their relationship.

Train(t_no, t_name)
Passenger (p_no, p_name, addr, age)

Relationship between Train and Passenger is many-to-many with

descriptive attribute date, seat_no and amt.

Constraints : primary key, foreign key,

primary key for third table (t_no, p_no, date),
t_name and p_name should not be null,
amt should be greater than zero.

Create a RDB in 3NF and write PL/SQL blocks in Oracle for the
following:

1) Write a procedure which will display passenger details of given train on

‘1 July 2014’.
2) Write a trigger that restricts insertion of train_passenger detail having
date less than current date. (Raise user defined exception and give
appropriate message)

22) Consider the following entities and their relationship.

Route(route_no, source, destination, no_of_station)
Bus (bus_no, capacity, depot_name)

Relationship between Route and Bus is one-to-many

Constraints: primary key, foreign key,

depot_name should not be null,
bus capacity should be greater than 40.
Create a RDB in 3NF and write PL/SQL blocks in Oracle for the
following:

1) Write a function which will return total number of routes having number
of stations greater than 10.
2) Write a cursor which will display route wise bus details. (Use
parameterized cursor)

23) Consider the following entities and their relationship.

University (u_no, u_name, u_city)
College (c_no, c_name, c_city, year_of_establishment )

Relationship between University and College is one-to-many

Constraints: primary key, foreign key,

u_name and c_name should not be null.

Create a RDB in 3NF and write PL/SQL blocks in Oracle for the
following:

1) Write a procedure which will display year wise details of colleges

belongs to given University.
2) Write a trigger that restricts insertion of college having year of
establishment greater than current year. (Raise user defined exception
and give appropriate message)

24) Consider the following entities and their relationship.

Patient (p_no, p_name, p_addr)
Doctor (d_no, d_name, d_addr, city)

Relationship between Patient and Doctor is many-to-many with

descriptive attribute disease and no_of_visits.

Constraints: primary key, foreign key,

primary key for third table(p_no, d_no, disease),
p_name and d_name should not be null.
Create a RDB in 3NF and write PL/SQL blocks in Oracle for the
following:

1) Write a function which will count total number of patients visiting to

given doctor for ‘Asthma’.
2) Write a cursor which will display doctor wise details of patients visited to
them.

25) Consider the following entities and their relationship.

Crop (c_no, c_name, c_season, pesticides)
Farmer (f_no, f_name, f_location)

Relationship between Crop and Farmer is many-to-many with

descriptive attribute year.

Constraints: primary key, foreign key,

primary key for third table(c_no, f_no, year),
c_name and f_name should not be null,
c_season can be rabi or kharif.

Create a RDB in 3NF and write PL/SQL blocks in Oracle for the
following:

1) Write a procedure which will display crop detail harvested by given

farmer in given year.
2) Write a trigger which will restricts insertion or updation of crop_farmer
table having year greater than current year. (Raise user defined exception
and give appropriate message)

26) Consider the following entities and their relationship.

Researcher (r_no, r_name, r_city)
Research_Paper (rp_no, rp_title, rp_subject, rp_level)

Relationship between Researcher and Research_Paper is many-to-many

with descriptive attribute year.

Constraints: primary key, foreign key,

r_name and rp_title should not be null,
rp_subject can be computer or electronics or finance.
rp_level can be state, national or international.

Create a RDB in 3NF and write PL/SQL blocks in Oracle for the
following:

1) Write a function which will return name of subject for which maximum
numbers of national level papers were presented by researcher in year
2013.
2) Write a cursor which will display rp_level wise and researcher wise
details of research paper presented by them.

27) Consider the following entities and their relationship.

Customer (cno, cname, city)
Loan (lno, lamt, noofyears)

Relationship between Customer and Loan is one-to-many.

Constraints: primary key, foreign key,

Lamt should be > 0
Create a RDB in 3NF and write PL/SQL blocks in Oracle for the
following:

1) Create or replace a function to find total loan amount from Mumbai city.
2) Write a cursor to display details of all customers who have taken loan for
more than 3 years.

28) Consider the following entities and their relationship.

Client (c_no, c_name, c_addr, birth_date)
Policy_info (p_no, p_name, maturity_amt, prem_amt, policy_term)

Relationship between Client and Policy_info is many-to-many with

descriptive attribute date_of_purchase.

Constraints: primary key, foreign key,

c_name and p_name should not be null,
policy_term should be greater than zero.
Create a RDB in 3NF and write PL/SQL blocks in Oracle for the
following:

1) Write a function which will return name of policy taken by minimum

number of clients.
2) Write a cursor which will display client wise policy details. (Use
parameterized cursor)

29) Consider the following entities and their relationship.

Company (c_no, c_name, c_city, c_share_value)
Person (p_no, p_name, p_city, p_ph_no)

Relationship between Company and Person is many-to-many with

descriptive attribute no_of_shares.

Constraints: primary key, foreign key,

c_name and p_name should not be null,
no_of_shares should be greater than zero.

Create a RDB in 3NF and write PL/SQL blocks in Oracle for the
following:

1) Write a procedure which will display names of person who are

shareholder of the given company.
2) Write a trigger which get activated when share value of company become
less than Rs. 10. (Raise user defined exception and give appropriate
message)

30) Consider the following entities and their relationship.

Person (p_no, p_name, p_addr)
Investment (inv_no, inv_name, inv_date, inv_amt)

Relationship between Person and Investment is one-to-many.

Constraints: primary key, foreign key,

p_name and inv_name should not be null,
inv_amt should be greater than 10000.
Create a RDB in 3NF and write PL/SQL blocks in Oracle for the
following:

1) Write a function which will return name of person having maximum total
amount of investment.
2) Write a cursor which will display person wise details of investment. (Use
parameterized cursor)