RDBMS-SEM2



Slip 1
 Client (client_no, client_name, address, birthdate)
Policy_info (policy_no, desc, maturity_amt, prem_amt, date)
Relation between Client and Policy_info is Many to Many
Constraint: Primary key, prem_amt and maturity_amt should be > 0.
Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following:
1)Write a function which will return total maturity amount of policies of a particular client.
2)Write a cursor which will display policy date wiseclient details.

Slip 2
Consider the following Item_Supplier database       
Item (itemno, itemname )
Supplier (supplier_No , supplier_name, address, city )
Relationship between Item and Supplier is many-to-many with descriptive attribute rate and quantity
Constraints: itemno ,supplier_No primary key
Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following:
1)Write function to print the total number of suppliers of a particular item
2)Write a trigger which will fire before insert orupdate on rate and quantity less than or equal to zero. (Raise user definedexception and give appropriate message)

Slip 3
Consider the following entities and their relationship.          
Newspaper (name,language , publisher , cost )
Cities (pincode , city, state)
Relationship between  Newspaper and Cities is many-to-many with descriptive attribute daily required
Constraints: name and pincode primary key
Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following:
1)Write a trigger which will fire before insert on the cities table which check that the pincode must be of 6 digit. (Raise user defined exception and give appropriate message).
2)Write a procedure to calculate city wise totalcost of each newspaper

Slip 4
Consider the following entities and their relationships.        
Client(client_no, client_name, address, birthdate)
Policy_info (policy_no, desc, maturity_amt, prem_amt, date)
Relation between Client and Policy_info is Many to Many
Constraint: Primary key, prem_amt and maturity_amt should be > 0.
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 having premium amount less than 5000.
2)Write a trigger which will fire before insert orupdate on policy_info having maturity amount less than premium amount. (Raiseuser defined exception and give appropriate message)

Slip 5
Consider the following entities and their relationships.        
Library(Lno, Lname, Location, Librarian, no_of_books)
Book(Bid, Bname, Author_Name, Price, publication)
Relation between Library and Book is one to many. Constraint: Primary key, Price 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 accept publication name from user and display total price of books of that publication.
2)Write a cursor which will display library wisebook details.(Use Parameterized Cursor)
   
Slip 6
Consider the following entities and their relationships.        
Employee (emp_id, emp_name, address)
Investment (inv_no, inv_name, inv_date, inv_amount)
Relation between Employee and Investment is One to Many.
Constraint: Primary key, inv_amount should be > 0.
Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following:
1)Write a procedure which will display details of employees invested amount in “Mutual Fund”
2)Write a cursor which will display date wiseinvestment details.

Slip 7
Consider the following entities and their relationships.
Bill (billno, day, tableno, total)
Menu (dish_no, dish_desc, price)
The relationship between Bill and Menu is Many to Many with quantity as descriptive attribute.
Constraint: Primary key, price should be > 0.
Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following:
1)Write a procedure to display menu details having price between 200 to 500 which were order on ‘Saturday’ .
2)Write a trigger which will fire before insert orupdate on Menu having price less than or equal to zero. (Raise user definedexception and give appropriate message)

Slip 8
Consider the following entities and their relationships.        
Plan (plan_no, plan_name, nooffreecalls, freecalltime, fix_amt)
Customer (cust_no, cust_name, mobile_no)
Relation between Plan and Customer is One to Many. Constraint: Primary key, fix_amt should be greater than 0.
Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following:
1)Write a function which will accept plan number from user and display all the details of the selected plan
2)Write a cursor which will display customer wiseplan details.(Use Parameterized Cursor)

Slip 9
Consider the following entities and their relationships.        
Project (pno, pname, start_date, budget, status)
Department (dno, dname, HOD, loc)
The relationship between Project and Department is Many to One.
 Constraint: Primary key.
Project Status Constraints: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 which accept department name and display total number of projects whose status is “p”(progressive).
2)Write a cursor which will display status wiseproject details of each department.

Slip 10
Consider the following entities and their relationships.        
Gym (Name, city, charges, scheme)
Member (ID, Name, phoneNo, address)
Relation between Gym and member is one to many. Constraint: Primary Key, charges must be greater than 0.
Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following:
1)Write a function which will accept member id and scheme from user and display charges paid by that member.
2)Write a trigger which will fire before insert orupdate on Gym having charges less than 1000. (Raise user defined exception and give appropriate message)
  
Slip 11
Consider the following entities and their relationships.        
Student (rollno, sname, class, timetable)
Lab (LabNo, LabName, capacity, equipment)
Relation between Student and Lab is Many to One. Constraint: Primary Key, capacity 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 accept Lab number from user and display total number of student allocated in that lab.
2)Write a cursor which will display lab wise studentdetails.

Slip 12
Consider the following entities and their relationships.        
Wholesaler (w_no, w_name, address, city)
Product (product_no, product_name, rate)
Relation between Wholesaler and Product is Many to Many with quantity as descriptive attribute.
Constraint: Primary key, rate should be > 0.
Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following:
1)Write a function which will accept wholesaler name from user and will display total number of items supplied by him.
2)Write a trigger which will fire before insert orupdate on product having rate less than or equal to zero (Raise user definedexception and give appropriate message)

Slip 13
Consider the following entities and their relationships.        
Country (CId, CName , no_of_states, area, location, population)
Citizen( Id, Name, mother_toung, state_name)
Relation between Country and Citizen is one to many. Constraint: Primary key, area 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 display name of the country having minimum population.
2)Write a cursor which will display county wisecitizen details.

Slip 14
Consider the following entities and their relationships.        
College (code, college_name, address)
Teacher (teacher_id, teacher_name, Qualification, specialization, salary, Desg)
Relation between Teacher and College is Many to One. Constraint: Primary Key, qualification 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 accept teacher name from user and display his/her college details.
2)Write a trigger which will fire before insert orupdate on Teacher having salary less than or equal to zero (Raise user definedexception and give appropriate message)

Slip 15
Consider the following entities and their relationships.        
Driver(driver_id, driver_name, address)
Car(license_no, model, year)
Relation between Driver and Car is Many to Many with date and time as descriptive attribute.
Constraint: Primary key, driver_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 display the total number of person who are using “Swift” car
2)Write a trigger which will fire before insert orupdate on year. If year value is more than current year. (Raise user definedexception and give appropriate message)

Slip 16
Consider the following entities and their relationships.        
Game (game_name, no_of_players, coach_name)
Player (pid, pname, address, club_name)
Relation between Game and Player is Many to Many. Constraint: Primary key, no_of_players should be > 0.
Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following:
1)Write a procedure which will display games details having number of players more than 5.
2)Write a trigger which will fire before insert orupdate on Game having no_of_players less than or equal to zero. (Raise userdefined exception and give appropriate message)

Slip 17
Consider the following Item_Supplier database       
Company (name , address , city , phone , share_value)
Person (pname ,pcity )
 Relationship between Company and Person is M to M relationship with descriptive attribute No_of_shares iConstraints: name,pname primary key
Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following:
1)Write a trigger before insert or update on No_of_shares field should not be zero.(Raise  user      defined exception            and      give     appropriate      message)
2)Write a function to display total no_of_shares ofa specific person.

Slip 18
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 wisedetails of competitions. (Use parameterized cursor)

Slip19
Consider the following entities and their relationships.        
Game(game_name, no_of_players, coach_name)
Player (pid, pname, address, club_name)
 Relation between Game and Player is Many to Many.
 Constraint: Primary key, no_of_players should be > 0.
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 football players of “Sports Club”.
2)Write a cursor which will display club wisedetails of players.

Slip 20
Consider the following entities and their relationships.        
Driver (driver_id, driver_name, address)
Car (license_no, model, year)
Relation between Driver and Car is Many to Many with date and time as descriptive attribute.
Constraint: Primary key, driver_name should not be null.
Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following:
1)Write a procedure to display car details used on specific day.
2)Write a cursor which will display driver wise cardetails in the year 2018.

Slip 21
Consider the following entities and their relationships.        
College(code, college_name, address)
Teacher(teacher_id, teacher_name, Qualification, specialization, salary, Desg)
Relation between Teacher and College is Many to One. Constraint: Primary Key, qualification 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 accept college name from user and display total number of “Ph.D” qualified teachers.
2)Write a cursor which will display college wiseteacher details.

Slip22
Consider the following entities and their relationships.        
Country (CId, CName , no_of_states, area, location, population)
Citizen( Id, Name, mother_toung, state_name)
Relation between Country and Citizen is one to many. Constraint: Primary key, area should not be null.
Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following:
1)Write a  procedure  to  display name  of   citizens  having mother  toung  “Marathi  “ and           from “India”;
2)Write a trigger which will fire before insert orupdate on country  having no_of_state lessthan equal to zero. (Raise user defined exception and give appropriate message)

Slip 23
Consider the following entities and their relationships.        
Wholesaler (w_no, w_name, address, city)
Product (product_no, product_name, rate)
Relation between Wholesaler and Product is Many to Many with quantity as descriptive attribute.
Constraint: Primary key, rate should be > 0.
Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following:
1)Write a procedure which will display details of products supplied by “Mr. Patil”
2)Write a cursor which will display wholesaler wiseproduct details.(Use Parameterized cursor)

Slip 24
Consider the following entities and their relationships.        
Student (rollno, sname, class, timetable)
Lab (LabNo, LabName, capacity, equipment)
Relation between Student and Lab is Many to One. Constraint: Primary Key, capacity should not be null.
Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following:
1)Write a procedure to display details of students which perform practical sessions in a given Lab.
2)Write a trigger which will fire before delete on Lab (Raise user defined exception and give appropriate message)

Slip 25
Consider the following entities and their relationships.        
Gym (Name, city, charges, scheme)
Member (ID, Name, phoneNo, address)
Relation between Gym and member is one to many. Constraint: Primary Key, charges must be greater than 0.
Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following:
1)Write a procedure to display member details of gym located at “Pimpri’”
2)Write a cursor which will display gym wise member details.(Use Parametrized Cursor)

Slip 26
Consider the following entities and their relationships.        
Project (pno, pname, start_date, budget, status)
 Department (dno, dname, HOD, loc)
The relationship between Project and Department is Many to One.
Constraint: Primary key.Project Status
Constraints:     C – Completed,P - Progressive, I – Incomplete
Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following:
1)Write a procedure to display the name of HOD who has completed maximum project.
2)Write a trigger which will fire before insert or update on project having budget less than or equal to zero. (Raise user defined exception and give appropriate message)

Slip 27
Consider the following entities and their relationships.        
Plan (plan_no, plan_name, nooffreecalls, freecalltime, fix_amt)
Customer (cust_no, cust_name, mobile_no)
Relation between Plan and Customer is One to Many. Constraint: Primary key, fix_amt should be greater than 0.
Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following:
1)Write a procedure to display the plan having minimum response.
2)Write a trigger which will fire before insert orupdate on mobile number having length less than or greater than10. (Raise userdefined exception and give appropriate message)

Slip 28
Consider the following entities and their relationships.        
Bill (billno, day, tableno, total)
Menu (dish_no, dish_desc, price)
The relationship between Bill and Menu is Many to Many with quantity as descriptive attribute.
Constraint: Primary key, price should be > 0.
Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following:
1)Write a function which accept a table number and display total amount of bill for a specific table
2)Write a cursor which will display table wise menudetails.

Slip 29
Consider the following entities and their relationships.        
Employee (emp_id, emp_name, address)
Investment (inv_no, inv_name, inv_date, inv_amount)
Relation between Employee and Investment is One to Many.
Constraint: Primary key, inv_amount should be > 0.
Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following:
1)Write a function which will return total investment amount of a particular client.
2)Write a trigger which will fire before insert orupdate on Investment having investment amount less than 50000. (Raise userdefined exception and give appropriate message)

Slip 30
 Consider the following entities and their relationships.       
Library(Lno, Lname, Location, Librarian, no_of_books)
Book(Bid, Bname, Author_Name, Price, publication)
Relation between Library and Book is one to many. Constraint: Primary key, Price should not be null.
Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following:
1)Write a procedure to display names of book written by “Mr. Patil” and are from “DPU Library”.
2)Write a trigger which will fire before insert orupdate on book having price less than or equal to zero. (Raise user definedexception and give appropriate message)


Subscribe to: Posts (Atom)