Hamid-World

Course Code : CS-67

Course Title : RDBMS Lab

Assignment Number : BCA (4)-66/Assignment/ 2008

Maximum Marks : 25

Last Date of Submission : 30th April, 2008/30th October, 2008

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) <>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')