Question : 1 An organization has multiple ratail stores. These stores have to keep track of products, suppliers, customers and employees. Or else makes discounts offers on several items during festivals. Make an ER-diagramme statics assumptions used, if any. Use concept of keys, aggregation, generalisation cardinality etc. in a proper way.
     Perform the following queries in SQL based on your ER-diagramme :
-- Find the details of products which have goy more than discount.
-- Find the details of stores which have maximum sales  monthly.
-- Find details of employee who have been working with retails stores more than 15 years.

Ans:-
An organizational has multiple retail stores system stores information about each "retail stores" like Name-of store, Address, City, Owner, store_code. Store_code is unique for each retail store. So, store_code is a key attribute.

We create an Entity type "Retail Store" as belowb :


Fig 1




Organisation have to store information about customer and employees for each Retail store. So, create strong entities like customer and Employee.

Customer Entity can be described using information like SSN (Customer's identity), Customer name, customer-Phone, Customer-address. SSN is unique for any citizen, so,


Fig 2


This Database system shows customer-retail store relationship as follow. There are number of customer making order to a retail store. And, it is also possible that a customer buy products from more than one retail store.

so, It shows many-to-many relationship between customer and retail store entity.

It is also very clear, only those customer details are recorded who have placed atleast one order with any of these retail store. Alternatively it can be said that customer entity is totally participated in these relationship set.


fig 3


System also keep track of information regarding employees work for each retail store. The Database system records Employee information like his name, address, phone number. Every Employee must have been assigned some ID. That ID must be unique for that retail store. So, Employee identification among retail store can be done using sote ID + employee ID jointly. So, here Emp_ID is prime attribute. It is denoted by dashes underline in below diagram.

Employee can work at at-most one retail store. But, One retail store can employ number of employees. One-to-many relationship is found between Employee and Retail store entity.

Employee relationship is dependent on retail store. There is no existence of employee, if retail store does not exist. So, Employee relationship can be considered as weak entity.


Fig 4.


Organization also keep track of products information for all product consumed.
Create a entity product


fig 5


Each store has its own stock record for each product can be sold. It is shown in following ER diagram


fig 6


Organization keeps track of supplier and product supplied by each supplier


fig 7


Relationship both supplier and product must be maintained.
so,


fig 8


Customer can place any number of order to any retail store. Order may contain many product purchase information.

Order details like order_id, order_date, order_amount are maintained. Order_amount is derived attribute, which is sum of price of all product ordered in that order. Organization introduce discount on festival, so, discount rate can be entered on all order.


fig 9


final ER diagram can be constructed as follow for organization which has multiple stores :


fig 10


SQL Query based on ralations of ER diagram.

Query 1 : Find the details of products which have got more that 50% discount.
SQL ;
select from product
where product_id in  (select product_id
from store_stock
where product_id in
(select product_id
from order_no in
(select order_no
from order
where discount > 50)
)
)

Query 2 : Find the details of stores which have maximum sales monthly.
SQL :
select * from retail_store
where store_code in
(
select store_code
from order, place
where order.order_no = place.order_no
group by store_code, to_char(order_dt, 'MM')
having sum(order_amt) >= all (select sum(order_amt)
from order, place
where order.order_no=place.order_no
group by store_code, to_char(order_dt, 'MM')
)
)

Query 3 : Find details of employee who have been working with retail stores more than 15 years.
SQL :
select * from employee
where (sysdate - doj) / 365 > 15



Question : 2 
a) What is timestamp ? How does the system generate timestamp ? Explain the timestamp ordering protocol for concurrency control. 
b) Explain the major anomalies in database.

Ans:- 
a)
What is timestamp?
A timestamp is the current time of an event that is recorded by a computer. A timestamp is a value expressed in milliseconds. It stores date, month, year, hour, minute, second, and fraction of second alos.

How does the system generate timestamp?
Its typically the time since the last server reset. Timestamp values wrap around (after about 49 days). The server, given its current time is represented by timestamp T, always interpreted timestamps from clients by treating half of the timestamps space as being earlier in time than and half of the timestamps space as being later in time than T.

Timestamp ordering protocol for concurrency control
The timestamp ordering protocol ensures that any conflicting read and write operations are excuted in timestamp order.


Fig 11.


The protocol operates as follows.

1. Suppose a trasaction T issues a read(Q)
    a. If TS(T) < W-timestamp(Q), then T needs to read a value of Q that was already overwritten. Hence, the read operation is rejected, and T is rolled back.
    b. If TS(T) = W-timestamp(Q), then the read operation is executed, and R-timestamp(Q) set to the miximum of Rtimestamp(Q) and TS(T).

2. Suppose that transaction Ti issues write(Q).
    a. If TS(T) < R-timestamp(Q), then the value of Q that T i is producing was need previously, and the systme assumed that the value would never be produced. Hence, write operation is rejected, and T is rolled back.
     b. If TS(T) < W-timestamp(Q), then T is attempting to write an obsolete value of timestamp. Hence, this write operation is rejected, and T is rolled back.

Otherwise, the write opetation is executed, and W-timestamp(Q) is set to TS(T).

3. A transatin Ti that is rolled backed by the concurrency control scheme, is assigned a new time stamp and is restarted.

(b)
Database Anomalies
Database anomalies are the problems in relations that occur due to redundancy in the ralations. These anomalie affect the process of inserting, deleting and modifying data in the relations. Some important data may be lost if a relations is updated that contains database anomalies. It is important to remove these anomalies in order to perform different processing on the relations without an problem.

Types of anomalies :-
Different types of database anomalies are as follows :

Insertin anomaly:
The insertion anomaly occurs when a new record is inserted in the ralation. In this anomaly, the user cannot insert a fact about an entity untile he has an additional fact about another entity.
Deletion anomaly:
The deletion anomaly occurs when a record is deleted from the relation. In this anomaly, the deletion of facts about an entity automatically deleted the fact of another entity.
Modification Anomamly:
The modification anomaly occurs when the record is updated in the relation. In this anomaly modification in the value of specific attribute requires modification in all record in where value occurs




Question : 3 Construct an E-R diagram for a hospital with a set of patients and a set of medical doctors. Associated 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 ralation and functional dependency set generated ny you and suggest which normal form they support.
d) say the relations developed by you are not in 2NF then how will you check that the relation status after normalisation in lossless and dependency preserving.

Ans


fig 12


(a) Transform the diagram created by you into respective ralations.
Ans.
Relations for above drawn ER diagram
Relation 1 : For entity Doctor
Name : Doctor
Attributes : DId (Doctor identification),
               Dname(Name of Doctor),
               Desg(Designation of Doctor in Hospital)
               DAdd(Doctor's Residence Address)
               DPhone (Doctor's Phone number for contact)
               Specialization
Key Attribute : Did (Primary key)

Relation 2 : For Entity Patient
Name : Patient
Attributes : PId (Patient Identification),
                 PName (Name of Patient),
                 PAdd (Patient's Residence address)
                 Pphone (Patient's phone number for contact)
                 Status (Patient's Health status : ill or fit)
Key attributes : Did, Pid, Examine_Date (Primary key)

Relation 3 : For relationship Examine
Name : Examine
Attributes : PId (Patient Identification),
                 Did(Doctor's Identification)
                 Examine_date
                 Examine_result
                 Prescription
Key attributes : Did, Pid, Examine_Date (Primary Key)

Relation 4 : For Entity Test
Name : Test
Attributes : Test_ID (Test Identification)
                 Pid (Patient Identification)
                 Test_Date
                 Diagnosis

(b) Create functional dependency set by considering the attributes used in ER diagram.
Ans
set of functionality F=
{
Did --> DName, DAdd, DPhone, Desg, Specialization
Pid --> PName, Padd, PPhone, status,
(Did, PId)--> Examine_Date, Examine_result, prescription
Test_ID --> Test_Name, fees
(Test_ID, PId) --> Test_Date, Diagnosis
}

(c) Analyse the relation and functional dependency set generated by you and suggest which normal form they support.
Ans
All relations are in BCNF as all have one Functional dependency and all non-key attributes are fully depend on complete key only.

(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.
Ans
As such all relations are in BCNF. So, they are already in 2NF.
But, consider a scenario if Test relations is defined as
Test (Test_ID, Test_Name, fees, Pid, Test_Date, Diagnosis)
With set of functional dependency F(test)=
{
Test_ID --> Test_Name, fees
(Test_ID, PId) --> Test_Date, Diagnosis
}
and as per Definition of 2NF, all non-key attribute of relations should depend on complete (full) key, not on part of key.

Here, Key of Test Relation is (Test_id, PId), which derive all attributes of relations.

But, functional dependency Test_Id --> Test_Name, fees violates rule of 2NF. It is partial functional dependency. So, This test relation is not in 2NF.

To decompose relation in 2NF, create a new relation for each partial functional dependency, and all its attributes in this newly created relation.

Here, Create new relation Test1 for partial Fuctional dependency Test_ID --> Test_Name, fees Test1 (Test_ID, Test_Name, fees)

And, a relation for key attributes and full dependent attributes. For this example, Test2 (Test_ID, Pid, Test_Date, Diagnosis).

(e) How will you check that the relation status after normalization is Lossless and dependency preserving.
Ans.
Relation is lossless if it satifies following two rules (1), and (2).

(1) R1 U R2 U ...... Rn = R
(2) R1 ∩ R2 --> R1/R2
(R1 ∩ R2) ∩ R3 --> (R1 U R2)/R3, .... and so on.

For Test in (d),
     Test1 U Test2
=   (Test_ID, Test_Name, fees) U (Test_ID, Pid, Test_date, Diagnosis)
=   (Test_ID, Test_Name, fees, Pid, Test_date, Diagnosis)
=   Test
So, rule(1) is satisfied.

     Test1 ∩ Test2
=   (Test_Id)
=   key attribute of Test1
So, rule (2) is also satisfied
Rule(1), and Rule(2) says Relation decomposition is lossless join.

Relation is dependency preserving, if it satisfies
F+= (F1 U F2 U ........ U Fn)+

For Test in (d),
F(Test1)= {Test_ID --> Test_name, fees}
F(test2)= {(Test_ID, Pid) --> Test_date, Diagnosis}
F(test1) U F(test2)
=Ftest
So, this decomposition is dependency preserving also.



Question 4

Ans
Relations Given :
Employee (emp_name, address, age)
Works(bank_name, emp_name, salary, designation, date_of_join)
Bank(bank_name, city, manager_name)

(i) Find the names of all bank employees in the database who live in the same city as the bank for which they work.
Ans
SQL :
select emp_name
from wmployee E, works W, bank B
where E.emp_name = W.emp_name
and W.bank_name = W.bank_name
and E.city = B.city

(ii) Find the name of all bak employee whose salary --> 200,00 and working as a branch manager.

Ans
SQL :
select emp_name
from works W
where W.salary > 20000
and W.emp_name in(
select manager_name
from bank)

(iii) List the employees who have exceeded 50 years and working as a Zonal Manager Delhi branch of the bank.
Ans
SQL :
select emp_name
from works W
where (sysdate-date_of_join)/365 > 50
and upper(designation)='zonal manager'
and W.emp_name in (
select manager_name
from bank
where upper(city)='delhi')