Perform the following queries in SQL based on your ER-diagram:
Find the details of products which have got more than discount.
Find the details of stores which have maximum sales monthly.
Find details of employee who have been working with retail stores more than 15 years.
Question 2: (a) What is a timestamp? How does the system generate
timestamp? Explain the timestamp ordering protocol for co concurrency
control.
(b) Explain the major anomalies in database?
Question 3: Construct an E-R diagram for a hospital with a set of patients and a set of medial doctors. Associate with each patient a log of the various tests and examination conducted.
(a) Transform the Diagram created by you into respective relations.
(b) Create functional dependency set by considering the attributes
used in ER Diagram.
(c) Analyse the relation and functional dependency set generated by
you and suggest which normal form they support.
(d) Say the relations developed by you are not in 2NF then how will
you do bring them in 2NF. Suggest any technique to achieve 2NF.
(e) How will you check that the relation status after normalsation
is Lossless and Dependency preserving.
Question 4: Assume that you are developing a prototype database of Bank employee system, for that you need to create the following tables:
(a) employee (emp_name, address, age)
(b) works (emp_name, salary)
(c) bank (bank_name, city)
Write the queries for performing the following function:
(i) Find the names of all bank employees in the database who live
in the same city as the bank for which they work.
(ii) Find the name of all bank employees whose salary > 200,00
and working as a branch Manager.
(iii) List the employees who have exceeded 50 years and working as
a Zonal Manager in Delhi branch of the Bank.