ER-Model
Question 1 |
Which one of the following is used to represent the supporting many-one relationships of a weak entity set in an entity-relationship diagram?
Ovals that contain underlined identifiers
| |
Rectangles with double/bold border
| |
Diamonds with double/bold border
| |
Ovals with double/bold border
|
Question 1 Explanation:
An entity set that does not have sufficient attributes to form a primary key is termed as a weak entity and an entity set that has a primary key is termed as strong entity set. For a weak entity set to be meaningful, it must be associated with another entity set, called identifying or owner entity set. The relationship associating the weak entity set with the identifying entity set is called the identifying relationship and it is represented by double diamond. The identifying relationship is many-to-one from the weak entity set to the identifying entity set and the participation of weak entity set in the relationship is total.
Question 2 |
In an Entity-Relationship (ER) model, suppose R is a many-to-one relationship from entity set E1 to entity set E2. Assume that E1 and E2 participate totally in R and that the cardinality of E1 is greater than the cardinality of E2.
Which one of the following is true about R?
Every entity in E1 is associated with exactly one entity in E2.
| |
Some entity in E1 is associated with more than one entity in E2.
| |
Every entity in E2 is associated with exactly one entity in E1.
| |
Every entity in E2 is associated with at most one entity in E1.
|
Question 2 Explanation:
The M : 1 relationship holds between two entities E1 and E2, in which each tuple from E2 is in relation with many tuples of E1. One tuple from E1 is in relation with only one tuple of E2. It is given that participation from both the sides is total and the cardinality of E1 is greater than E2.
Therefore, every entity E1 is associated with exactly one entity in E2.
Question 3 |
An ER model of a database consists of entity types A and B. These are connected by a relationship R which does not have its own attribute. Under which one of the following conditions, can the relational table for R be merged with that of A?
Relationship R is one-to-many and the participation of A in R is total. | |
Relationship R is one-to-many and the participation of A in R is partial. | |
Relationship R is many-to-one and the participation of A in R is total. | |
Relationship R is one-to-many and the participation of A in R is partial. |
Question 3 Explanation:
The relational table for R be merged that of A, if the relationship R is Many-to-one and the participation of A in R is total.
Question 4 |
Consider an Entity-Relationship (ER) model in which entity sets E1 and E2 are connected by an m : n relationship R12, E1 and E3 are connected by a 1 : n (1 on the side of E1 and n on the side of E3) relationship R13. E1 has two single-valued attributes a11 and a12 of which a11 is the key attribute. E2 has two single-valued attributes a21 and a22 is the key attribute. E3 has two single-valued attributes a31 and a32 of which a31 is the key attribute. The relationships do not have any attributes. If a relational model is derived from the above ER model, then the minimum number of relations that would be generated if all the relations are in 3NF is ___________.
4 | |
6 | |
7 | |
8 |
Question 4 Explanation:
Question 5 |
What is the optimized version of the relation algebra expression πA1(πA2(σF1(σF2(r)))), where A1, A2 are sets of attributes in with A1 ⊂ A2 and F1, F2 are Boolean expressions based on the attributes in r?
πA1 (σ(F1∧F2) (r)) | |
πA1 (σ(F1∨F2) (r)) | |
πA2 (σ(F1∧F2) (r)) | |
πA2 (σ(F1∨F2) (r)) |
Question 5 Explanation:
Since A1 ⊂ A2 will get only attribute A1 as it is in the outside. So we can remove project A2.
Two Selects with Boolean expression can be combined into one select with AND of two Boolean expressions.
Two Selects with Boolean expression can be combined into one select with AND of two Boolean expressions.
Question 6 |
A prime attribute of a relation scheme is an attribute that appears
in all candidate keys of R. | |
in some candidate key of R. | |
in a foreign key of R. | |
only in the primary key of R . |
Question 6 Explanation:
A prime attribute of a relation scheme R is an attribute that appears in some candidate keys of R. Need not to appear in all the candidate keys.
Ex: AB, BC, CD are candidate keys of R(ABCD). In the FDs set one attribute may not be part of all the FDs.
Ex: AB, BC, CD are candidate keys of R(ABCD). In the FDs set one attribute may not be part of all the FDs.
Question 7 |
Consider the relational schema given below, where eId of the relation dependent is a foreign key referring to empId of the relation employee. Assume that every employee has at least one associated dependent in the dependent relation.
employee (empId, empName, empAge)
dependent(depId, eId, depName, depAge)
Consider the following relational algebra query:
∏empId(employee)-∏empId(employee⋈(empId = eID)∧(empAge ≤ depAge)dependent)
The above query evaluates to the set of empIds of employees whose age is greater than that of
some dependent. | |
all dependents. | |
some of his/her dependents. | |
all of his/her dependents. |
Question 7 Explanation:
The inner query selects the employees whose age is less than or equal to at least one of his dependents. So, subtracting from the set of employees, gives employees whose age is greater than all of his dependents.
Question 8 |
Consider the following releation schema
I, II, III and IV | |
I, II and III only | |
I, II and IV only | |
II, III and IV only |
Question 8 Explanation:
Four queries given in SQL, RA, TRC and DRC and all four statements will retrieve the required information.
Question 9 |
Given the basic ER and relational models, which of the following is INCORRECT?
An attribute of an entity can have more than one value | |
An attribute of an entity can be composite | |
In a row of a relational table, an attribute can have more than one value | |
In a row of a relational table, an attribute can have exactly one value or a NULL value |
Question 9 Explanation:
Option (A): In ER-model, multivalued attribute of an entity can have more than one value.
Option (B): In ER model, the attribute which can be further broken down into some other attributes is called composite attribute.
Option (C): In Relational model, the intersection of one row and column should contain only one value. So, option (C) is INCORRECT.
Option (D): In Relational model, the intersection of one row and column should contain either exactly one value or NULL.
Option (B): In ER model, the attribute which can be further broken down into some other attributes is called composite attribute.
Option (C): In Relational model, the intersection of one row and column should contain only one value. So, option (C) is INCORRECT.
Option (D): In Relational model, the intersection of one row and column should contain either exactly one value or NULL.
Question 10 |
Consider the following ER diagram
The minimum number of tables needed to represent M, N, P, R1, R2 is
The minimum number of tables needed to represent M, N, P, R1, R2 is2 | |
3 | |
4 | |
5 |
Question 10 Explanation:
➝ M, P are entities so they require individual tables.
➝ Here N is a Weak entity, but it need to modify the primary key of P such as P1
M = {M1, M2, M3, P1}
P = {P1, P2}
N = {N1, N2, P1}
➝ Relationship set has its own attribute, then no need to create a separate table.
➝ Finally we require 3 minimum tables to represent M,P,N,R1,R2.
➝ Here N is a Weak entity, but it need to modify the primary key of P such as P1
M = {M1, M2, M3, P1}
P = {P1, P2}
N = {N1, N2, P1}
➝ Relationship set has its own attribute, then no need to create a separate table.
➝ Finally we require 3 minimum tables to represent M,P,N,R1,R2.
Question 11 |
Consider the following ER diagram
Which of the following is a correct attribute set for one of the tables for the correct answer to the above question?
Which of the following is a correct attribute set for one of the tables for the correct answer to the above question?
{M1, M2, M3, P1} | |
{M1, P1, N1, N2}
| |
{M1, P1, N1} | |
{M1, P1}
|
Question 11 Explanation:
Possible set of attributes is
For M = {M1, M2, M3, P1}
P = {P1, P2}
N = {N1, N2, P1}
For M = {M1, M2, M3, P1}
P = {P1, P2}
N = {N1, N2, P1}
Question 12 |
Let E1 and E2 be two entities in an E/R diagram with simple single-valued attributes. R1 and R2 are two relationships between E1 and E2, where R1 is one-to-many and R2 is many-to-many. R1 and R2 do not have any attributes of their own. What is the minimum number of tables required to represent this situation in the relational model?
2 | |
3 | |
4 | |
5 |
Question 12 Explanation:
R1 and R2 two relationships between E1 and E2.
R1 is one to many.
R2 is many to many.
→ E1 and E2 have separate table because they need to store multiple values.
→ R2 also have separate table by considering Primary keys E1 and E2 as foreign keys.
→ R1 is converted to many side table i.e., E2 as Primary key and E1 as Foreign key.
So, totally we need 3 tables to store the value.
R1 is one to many.
R2 is many to many.
→ E1 and E2 have separate table because they need to store multiple values.
→ R2 also have separate table by considering Primary keys E1 and E2 as foreign keys.
→ R1 is converted to many side table i.e., E2 as Primary key and E1 as Foreign key.
So, totally we need 3 tables to store the value.
Question 13 |
Consider the entities 'hotel room', and 'person' with a many to many relationship 'lodging' as shown below:
If we wish to store information about the rent payment to be made by person (s) occupying different hotel rooms, then this information should appear as an attribute of
If we wish to store information about the rent payment to be made by person (s) occupying different hotel rooms, then this information should appear as an attribute of
Person | |
Hotel Room | |
Lodging | |
None of these |
Question 13 Explanation:
The information should appear as an attribute of lodging, because this is the only common attribute that relating to the hotel room and person.
Question 14 |
In a schema with attributes A, B, C, D and E following set of functional dependencies are given
A → B A → C CD → E B → D E → A
Which of the following functional dependencies is NOT implied by the above set?
CD → AC | |
BD → CD | |
BC → CD | |
AC → BC |
Question 14 Explanation:
Apply membership test for all the functional dependencies.
Option (B):
BD → CD
BD+ = BD
i.e., BD cannot derive CD and hence is not implied.
Option (B):
BD → CD
BD+ = BD
i.e., BD cannot derive CD and hence is not implied.
Question 15 |
Consider the following entity relationship diagram (ERD), where two entities E1 and E2 have a relation R of cardinality 1 : m.
The attributes of E1 are A11, A12 and A13 where A11 is the key attribute. The attributes of E2 are A21, A22 and A23 where A21 is the key attribute and A23 is a multi-valued attribute. Relation R does not have any attribute. A relational database containing minimum number of tables with each table satisfying the requirements of the third normal form (3NF) is designed from the above ERD. The number of tables in the database is
2 | |
3 | |
5 | |
4 |
Question 15 Explanation:
One table for E1, two tables for E2 (A21, A22 and A21, A23) because we need to make a separate table for multivalued attribute to satisfy minimum 1NF condition that requires atomic attributes.
Then, we get
T1: {A11, A12, A13} - key is A11
T2: {A21, A22, A11} - key is A21
T3: {A21, A23} - key is {A21, A23}
Then, we get
T1: {A11, A12, A13} - key is A11
T2: {A21, A22, A11} - key is A21
T3: {A21, A23} - key is {A21, A23}
Question 16 |
Consider two tables in a relational database with columns and rows as follows:
Roll_no is the primary key of the Student table, Dept_id is the primary key of the Department table and Student.Dept_id is a foreign key from Department.Dept_id What will happen if we try to execute the following two SQL statements?
| ROLL_NO | NAME | DEPT_ID |
|---|---|---|
| 1 | ABC | 1 |
| 2 | DEF | 1 |
| 3 | GHI | 2 |
| 4 | JKL | 3 |
| DEPT_ID | DEPT_NAME |
|---|---|
| 1 | A |
| 2 | B |
| 3 | C |
- update Student set Dept_id = Null where Roll_on = 1
- update Department set Dept_id = Null where Dept_id = 1
Both (i) and (ii) will fail | |
(i) will fail but (ii) will succeed | |
(i) will succeed but (ii) will fail | |
Both (i) and (ii) will succeed |
Question 16 Explanation:
Here in (i), when we update in Student table, Dept_id = Null, then it will not cause any problem to referenced table.
But in (ii) if we set in Department table, Dept_id = Null, then it will produce inconsistency because in Student table we will still have the tuples containing the Dept_id = 1.
But in (ii) if we set in Department table, Dept_id = Null, then it will produce inconsistency because in Student table we will still have the tuples containing the Dept_id = 1.
There are 16 questions to complete.