Q.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)
Q2)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)
Q3)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)
Q.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.
Q.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)
Q.6)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 function which will display train details having maximum passenger for a given date.
2)Write a cursor which will display date wise train and their passenger details.
Q.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)
Q.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)
Q.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)
Q.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)
Q.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)
Q.12)Consider the following entities and their relationship.
Donor(donor_no, donor_name, city)
Blood_donation_detail (bd_no, blood_group, qty, date_of_collection)
Relationship between Donor and Blood_donation_detail is one-to-many.
Constraints:primary key, foreign key,donor_name should not be null,
blood_group can be A+, A-, B+, B-, AB+, AB-, O+, O-
Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following:
1)Write a function which will count total amount of blood collected for a given blood group on given date.
2)Write a cursor which will display donor wise blood donation details. (Use parameterized cursor)
Q.13)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 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)
Q.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)
Q.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)
Q.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)
Q.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)
Q.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)
Q.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).
Q.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.
Q.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)
Q.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)
Q.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)
Q.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. (Use parameterized cursor)
Q.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)
Q.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.
Q.27)Consider the following entities and their relationship.
Donor (donor_no, donor_name, city)
Blood_donation_detail (bd_no, blood_group, qty, date_of_collection)
Relationship between Donor and Blood_donation_detail is one-to-many.
Constraints:primary key, foreign key,donor_name should not be null,blood_group can be A+, A-, B+, B-, AB+, AB-, O+, O-
Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following:
1)Write a procedure which will display blood group wise total amount of quantity of blood available.
2)Write a trigger that restricts insertion of blood_donation_details having quantity greater than 300ml. (Raise user defined exception and give appropriate message)
Q.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)
Q.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)
Q.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)
