Assignment 5
Problem statement
Consider the following relations for the details maintained by a book dealer.
Author (author-id: int, name: string, city: string, country: string)
Publisher (publisher-id: int, name: string, city: string, country: string)
Catalog (book-id: int, title: string, author-id: int, publisher-id: int, category-id: int, year: int,
price: int)
Category (category-id: int, description: string)
Order-details (order-no : int, book-id: int, quantity: int)
a) Create the above tables by properly specifying the primary keys and the foreign keys.
b) Enter at least five tuples for each relation.
c) Give the details of the authors who have 2 or more books in the catalog and the price
of the books is greater than the average price of the books in the catalog and the year
of publication is after 2000.
d) Find the author of the book which has maximum sales.
e) Demonstrate how you increase the price of books published by a specific publisher by
10%.
Assignment 6
Schema description
Branch Customer
branch_name Branch_cit assets
y customer_name customer_street city
Account Depositor
accno branch_name balance customer_name accno
Loan Borrower
loan_number branch_name amount customer_nam loan_number
e
Consider the following database for a banking enterprise
Branch (branch_name: string, branch_city: string, assets: real)
Account (accno: int, branch_name: string, balance: real)
Customer (customer_name: string, customer_street: string, city:
string)
Depositor (customer_name: string, accno: int)
Loan (loan_number: int, branch_name: string, amount: real)
Borrower (customer_name: string, loan_number: int)
i) create the above tables by properly specifying the primary keys
and the foreign keys.
ii) enter atleast five tuples for each relation.
iii) Display the names of all the customer who have taken loan , count of the number
of loans taken and the total amount taken up each customer.
iv) Find the names of all the customers who have taken the loan in a particular
branch.
v) Update the balance for the customer with a given customer name to 10000.
