10.

11.

B4E561 Pages: 4

order lists one or more items. An item may be listed in many orders. An item is assembled from
different parts and parts can be common for many items. One or more employees assemble an
item from parts. A supplier can supply different parts incertain quantities. A part may be supplied
by different suppliers.

(i) Identify and list entities, suitable attributes, primary keys, foreign keys and
relationships to represent the scenario.
(ii) Draw an ER diagram to model the scenario using min-max notation.

a. Justify the importance of weak entity sets with the help of an example. (3) b. In the ER diagram
below, names of entity sets and relationships are shown in capital (6) and corresponding attributes
are listed under each such name. Key attributes are underlined. All the participations are total.
Use the standard synthesis procedure to convert the ER diagram into the corresponding relational
schema. Clearly show primary and foreign keys.

STORES

Store ‏مل‎

LS
Manager ID

IN-CITIES
Qty_in_cites

PART C
Answer all questions
Illustrate the GROUP BY clause with the help of areal example. இ
Consider the query SELECT NAME, AGE FROM STUDENT WHERE GENDER = (3) ‘Male’ on
the table STUDENT(ROLLNO, NAME, AGE, GENDER, ADDRESS). Give a relational algebra
expression corresponding to the query. Is result produced by the query and your expression
always the same? Why?

Determine any two candidate keys of the relation R(A,B,C,D,E,F) with FDs (3) க ‏ر۸0 ت0‎
D-EF, FB.

Give an example for a relation that has insertion, deletion and update anomalies. Which (3)
type(s) of functional dependency can formally model these anomalies? Quote one such
dependency from your example
PART D
Answer any two full questions

Page 2 of 4