Registration no:
Total Number of Pages: 02 MCA
MCC304
3rd Semester Regular/Back Examination – 2015-16
DATA BASE SYSTEMS
BRANCH(S): MCA
Time: 3 Hours
Max Marks: 70
Q.code:T564
Answer Question No.1 which is compulsory and any five from the rest.
The figures in the right hand margin indicate marks.
Q1 Answer the following questions: (2 x 10)
a) What is data abstraction?
b) What is the difference between composite key and composite attribute?
c) What are basic functions of DBA?
d) Define and differentiate between Natural-join and Equi-join.
e) What the difference is between insert and update anomalies?
f) Why Armstrong’s axioms are sound and complete?
g) Define partial dependency and transitive dependency?
h) What are different states and actions of a transaction?
i) What is Parser? What is the basic function of a parser?
j) What are the advantages and disadvantages of 2PL?
Q2 a) Explain logical and physical data independence and their importance in (5)
database design.
b) What do you mean by a data model? Describe the different types of (5)
data models used.
Q3 Draw an ER diagram for a company that specializes in IT training. The (10)
company has 30 instructors and can handle up to 100 trainees per
training session. The company offers five advances technology
courses, each of which is taught by a teaching team of two or more
instructors. Each instructor is assigned to a maximum of two teaching
teams or may be assigned to do research. Each trainee undertakes
one advanced technology course per training session.
i) Identify the main entity types for the company.
ii) Identify the main relationship types and specify the multiplicity of
each relationship.
iii) Represent all the E-R diagrams described in (i), and (ii) as a
single diagram.
iv) Construct appropriate tables for the E-R diagram in part (iii).
Q4 Consider the following Schema: (10)
Movie(Title, Year, Length, StudioName)
MovieStar (Name, Address, Gender, BirthDate)
StarsIn (Title, Year, StarName)
Studio (Name, Address)
Express the following queries either in relational algebra or in SQL.
i) Get the stars that have appeared in at least three movies.
ii) Get the name and address of studios which have produced
movies of Saharukh Khan.
iii) Get the birth date and movie title of female stars who have
appeared in movies in 1996.
iv) Get the movies starred by both Amitabh Bachan and Rekha.
Q5 a) A realtion R (A, B, C, D) is given Consider the following two set of (4)
functional dependencies:
F = {A C, AC D, E AD, E H} and
G = {A CD, E AH}.
Check whether or not they are equivalent.
b) For a relation, R = {A, B, C, D, E, F, G, H} with the following FDs, (6)
F = {ABCD, AEF, EG, DH}
(i) Find the key.
(ii) In which normal form, Relation R, is?
(iii) Apply normalization on R until it cannot be decomposed
further.
Q6 a) For a relation 𝑅 (𝐴, 𝐵, 𝐶, 𝐷, 𝐸, 𝐹) the set of functional dependencies F is (4)
given as follows:
𝐴𝐵 → 𝐶
𝐶→𝐴
𝐵𝐶 → 𝐷
𝐴𝐶𝐷 → 𝐵
𝐵𝐸 → 𝐶
𝐶𝐸 → 𝐹𝐴
𝐶𝐹 → 𝐵𝐷
𝐷→𝐸
Find its canonical cover.
b) Explain how heuristic query optimization is performed with an example. (6)
Q7 a) Which of the following schedules is conflict serializable? For each (5)
serializable schedule, determine the equivalent serial schedules:
S1 : 𝑅1 (𝑋) 𝑅2 (𝑍) 𝑅1 (𝑍) 𝑅3 (𝑋) 𝑅3 (𝑌) 𝑊1 (𝑋) 𝑊3 (𝑌) 𝑅2 (𝑌) 𝑊2 (𝑍) 𝑊2 (𝑌)
S2 : 𝑅1 (𝑋) 𝑅2 (𝑍) 𝑅3 (𝑋) 𝑅1 (𝑍) 𝑅2 (𝑌) 𝑅3 (𝑌) 𝑊1 (𝑋) 𝑊2 (𝑍) 𝑊3 (𝑌) 𝑊2 (𝑌)
b) Describe with example the types of problem that can occur in a muli- (5)
user environment when concurrent access to the database is allowed.
Q8 Write short notes on (Any Two) (5 2)
i) OLAP & different styles of OLAP.
ii) ARIES algorithm.
iii) Distributed database system.
