Test: ER Model- 1 - Computer Science Engineering (CSE) MCQ

[AD]⁺ = ADE. [BE]⁺ = BCEF. [B]⁺ = BC. [AF]⁺ = AFG. Nothing drives all the attributes, but if We add B in first key i.e. [ADB] then it will give all the attribute [ADB]⁺ = ABCDEFG. So, option (D) is correct.

The term ‘entity’ belongs to ER model and the term ‘relational table’ belongs to relational model. A and B both are true. ER model supports both multivalued and composite attributes See this for more details. (C) is false and (D) is true. In Relation model, an entry in relational table can can have exactly one value or a NULL.

Answer is B, i.e, 3 minimum tables. M, P are strong entities hence they must be represented by separate tables. Many-to-one and one-to-many relationship sets that are total on the many-side can be represented by adding an extra attribute to the “many” side, containing the primary key of the “one” side. ( This way no extra table will be needed for Relationship sets ) M table is modified to include primary key of P side(i.e. P1). N is weak entity, and is modified to include primary key of P (i.e, P1). Therefore there would be minimum of 3 tables with schema given below :
M ( M1, M2, M3, P1)
P ( P1, P2 )
N ( P1, N1, N2 )

Nested loop join is one of the methods to implement database in memory. A nested loop join is an  algorithm that joins two sets by using two nested loops.
According to nested join,given relation R and S For each tuple r in R do For each tuple s in S do If r and s satisfy the join condition Then output the tuple <r,s> Cost estimations for the above loop: – b(R) and  b(S) number of blocks in R and in S – Each block of outer relation is read once – Inner relation is read once for each block of outer relation Summing up : IO= b(R)+n(R)*b(S) total  IO operations Lets assume |R|>|S|  i.e b(R) =10 , n(R) = 100 , n(S)= 30 ,and b(s) =3 Now,   if R is outer relation then, IO= 10+100*3=340 if S is outer relation then IO=3+30*10=303 As it can be observed , that total IO is lesser if the value of outer variable is less and as it is already given that |R|<|S|.Therefore,  Relation r(R) should be in the outer loop to have fewer number of disk block accesses.

The below subquery after the subtraction sign produces id's of those employees who have at least one dependent with age greater than or equal the employee's age.

When the result of above subquery is subtracted from all employees, we get the employees whose age is greater than all dependents.

Logical schema is developed using diagrams that define the content of databases in the form of tables and it also describe how the tables are linked with each for data access and how actually data is stored in the database . A conceptual model is created during the first phase of logical design based on the needs assessment performed in stage one. Actually a conceptual model is an Entity -Relation (i.e., ER) diagram. It shows the tables, fields, and primary keys of the database, and how tables are related or linked with each other. While designing the logical scheme we avoiding data inconsistency i.e., data should be consistent as well as efficient (duplicate data is not allowed in a row) and query should be easily construct using the table . Hence, option (D) is correct among all.

The answer is B, i.e minimum 3 tables. Strong entities E1 and E2 are represented as separate tables. In addition to that many-to-many relationships(R2) must be converted as seperate table by having primary keys of E1 and E2 as foreign keys. One-to-many relaionship (R1) must be transferred to 'many' side table(i.e. E2) by having primary key of one side(E1) as foreign key( this way we need not to make a seperate table for R1). Let relation schema be E1(a1,a2) and E2( b1,b2). Relation E1( a1 is the key)
a1 a2
-------
1 3
2 4
3 4
Relation E2( b1 is the key, a1 is the foreign key, hence R1(one-many) relationship set satisfy here )
b1 b2 a1
-----------
7 4 2
8 7 2
9 7 3
Relation R2 ( {a1, b1} combined is the key here , representing many-many relationship R2 )
a1 b1
--------
1 7
1 8
2 9
3 9
Hence we will have minimum of 3 tables.

Relational algebra is a procedural query language where we input - relations and it yields relations as output. It provides method to get the result. It is performed recursively on a relation and the in between results are relations(output). Basic set of operations for the relational model. Relational calculus is a non - procedural query language. It provides the query to get result. Higher level declarative language for specifying relational queries. Tupple Relational Calculus operates on each tupple. Domain Relational Calculus operates on each column or attribute. Safe expression means fixed no. of tupple or column or attribute as a result But all of them has same expressive power. Just different ways to do so.

Maximum number of tables required is 3 in case of many to many relationships between entities. Minimum number of tables is 1 in case of unary relationship and total participation of atleast one entity. But in case of partial participation of both entities, minimum number of tables required is 2.

Simple candidate key means single attributed key. As (A)+ = {A, B, C, D}, (B)+ = {B,A, C, D}, (C)+ = {C} and (D)+ = {D, A, B, C}. So, A, B and D are candidate keys which are simple as well. So, correct option is 3.

In Given relational algebra, Tuple t should have two attributes A=10 and B=20 In A, we select tuples having A=10 or B=20 so, it is wrong choice. In B, we select tuples having A=10 or we can select tuples having B=20, so it is also wrong choice. D is also wrong choice. In C, we select tuples having A=10 and tuples having B=20, it is same as above relational algebra. Hence, option (C) is Correct.

When there is no foreign key constraint between two tables then the max and min number of tuples in their join is mn and 0 respectively.

D is the answer. If the selection condition c1 is on attribute A2, then we can not replace LHS expression by RHS expression. In RHS first projection is on A1 then selection condition c1 will fail because no A2 will be present there.

II and III statement can cause the violation of the referential integrity constraint as d is the foreign key of S that refers to the primary key of R. So, d should always be a subset of primary key of R. But insertion to S and deletion from R may cause the violation of subset property as after these operations d may contain a value that is not present in the primary key of A. So, option (B) is correct.

Given,

• Q → R(Primary Key)
• S → P (Primary Key)

Entry to be deleted - P (3) and Q(8)

• Q can be deleted directly
• Now, S → P but the relationship given is on delete set NULL, Therefore when  we delete 3 from  T1 ,the  entry in T2 having 3 will be NULL.

Therfore, Option A  - Answer is 0 entries