ASSIGNMENT 3 SOLUTION WITH SOURCE CODE

Procedures

Contents:

  1. Set A
  2. Set B

Set A
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:

Q 1. Write a procedure to calculate city wise total cost of each newspaper

 Download code        Source code 

Q 2. Write a procedure which display details of news papers having cost greater than 2 Rs.

 Download code        Source code 

Q 3. Write a procedure which take display details of cities where English news paper is supplied.

 Download code        Source code 

Set B
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:

Q 1. Write a procedure which will accept publication name from user and display details of books published by it.

 Download code        Source code 

Q 2. Write a procedure which will accept Library number from user and display Book name and their price.

 Download code        Source code 

Q 3. Write a procedure to display names of Libraries having books written by “Mr. Patil”.

 Download code        Source code