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

Since given relation is many to one

Therefore, no entity in E1 can be related to more than one entity in E2 and an entity in E2 can be related to more than one entity in E1. Only option (A) is correct.

Functional dependency is a constraint that describes the relationship between attributes in a relation. Data integrity refers to the accuracy and consistency of data stored in a database, data warehouse, data mart or other construct. Referential integrity is a relational database concept, which states that table relationships must always be consistent. Normalization is a process of organizing the data in database to avoid data redundancy, insertion anomaly, update anomaly & deletion anomaly. 1NF, 2NF, 3NF and BCNF are types of normal forms. So, option (D) is correct.

If we find closure of A: A+ → All attribute except D. Similarly for other keys we can find closure, but D can't be derived from any key and it must be added to all keys to be derived from. That's why this relation is in 1NF, since there is partial dependency so, this relation is not in 2NF. So, option (A) is correct.

(A) An instructor Id. No. cannot be null, provided Instructor Id No. being primary key. Correct by Codd's rule (B) No two citizens have same Adhar-Id. Correct because Adhar is identification for citizens so it must be unique (C) Budget of a company must be zero. We cant say or it is not necessarily true So, option (C) is correct.

primary key ⊆ candidate key ⊆ super key

The sum of all employees' salaries can not be found using these above operations as to calculate sum we need aggregate functions and operators. So, option (C) is correct.

Consider the folloewing examples: Case 1: Maximum tuples- When the common attribute contain identical values
Relation R          Relation S        R ⋈ S
a       b                a       d             a     b    d
1       2                1       10           1     2    10   
1       3                1       11           1     2    11
1       4                                        1     3    10
                                                   1     3    11
                                                   1     4    10
                                                   1     4    11
Case 2 : Minimum Tuples- When Both the relations have a common attribute but no tuple in both relations match.
Relation R          Relation S        R ⋈ S
a       b                a       d             a     b    d
1       2                2       10           No tuple  
1       3                3       11           
1       4  
So, option (B) is correct.

• Relational database schema normalization is NOT for reducing the number of joins required to satisfy a query.
• Relational database schema normalization is for eliminating uncontrolled redundancy of data stored in the database.
• Relational database schema normalization is for eliminating number of anomalies that could otherwise occur with inserts and deletes.
• Relational database schema normalization is for ensuring that functional dependencies are enforced.

So, option (A) is correct.

If we convert an ER diagram into relational model then we can create table for each entity. We get two tables One for Entity Person - Person(NID,Name) One for Entity Exam - Exam(ExamID,ExamName) Now for relationship Entity Qualification, we can not add QualifiedDate in any of the two tables formed so we need to create a new table using Primary Key from both Entity Person and Entity Exam. We obtain Qualification(NID, ExamID, QualifiedDate) Hence Option (C) does not make any sense.

IN relational database model: (a) NULL values can be used to opt a tuple out of enforcement of a foreign key.Correct (b) Suppose that table T has only one candidate key. If Q is in 3NF, then it is also in BCNF.Correct (c) The difference between the project operator (Π) in relational algebra and the SELECT keyword in SQL is that if the resulting table/set has more than one occurrences of the same tuple, then Π will return only one of them, while SQL SELECT will return all.Correct So, option (D) is correct.

Association is a relationship where all objects have their own lifecycle and there is no owner. Aggregation is a specialised form of Association where all objects have their own lifecycle, but there is ownership and child objects can not belong to another parent object. Composition is again specialised form of Aggregation and we can call this as a “death” relationship. It is a strong type of Aggregation. Child object does not have its lifecycle and if parent object is deleted, all child objects will also be deleted.

So, option (B) is correct. See: Association, Composition and Aggregation

• Views in a database system are important because they help with access control by allowing users to see only a particular subset of the data in the database.Correct
• E-R diagrams are useful to logically model concepts.Inorrect
• An update anomaly is when it is not possible to store information unless some other, unrelated information is stored as well.Inorrect
• SQL is a procedural language.Inorrect

So, option (D) is correct.

Functional dependency is a relationship that exists when one attribute uniquely determines another attribute, therefore A→B. Multivalued dependency occurs when there are more than one independent multivalued attributes in a table. If A→ C is a dependency, it means for A, attribute C has more than one value. So, option (B) is correct.

Upon carefully observing the tuples in the relation, we can see that the functional dependency B →{A, C, D} holds. Hence {B} is one candidate key. A,D → {A, B, C, D} ; C,D → {A, B, C, D} and B,C→ {A, B, C, D} also holds true. Therefore, these all can be candidate keys. But in B,C → {A, B, C, D} , there exist a partial dependency of B→{A, B, C, D}. Hence {B, C} cannot be a candidate key. So option (C) is correct.

Drop Table cannot be used to drop a Table referenced by foreign key constraint. For more information on keys Refer:DBMS | Keys in Relational Model (Candidate, Super, Primary, Alternate and Foreign) and for information on sql operation Refer:SQL | ALTER (ADD, DROP, MODIFY) Option (C) is correct

• Weak entities can be deleted automatically when their strong entity is deleted. Correct
• Weak entity set avoids the data duplication and consequent possible inconsistencies caused by duplicating the key of the strong entity. Correct
• A weak entity set has no primary keys unless attributes of the strong entity set on which it depends are includedCorrect
• Tuples in a weak entity set are not partitioned according to their relationship with tuples in a strong entity set.This is Incorrect, because tuples in a weak entity set are partitioned according to their relationship with tuples in a strong entity set.

So, option (D) is correct.

Join is used to change content of one table using the content of another table. Join actually link two table on a common attribute field and join to binary operation that allow to combine two table. Option (B) is correct.

Armstrong inference rules refer to a set of inference rules used to infer all the functional dependencies on a relational database. It consists of the following axioms:
Axiom of Reflexivity:
This axiom states: if Y is a subset of X, then X determines Y
Axiom of Augmentation:
The axiom of augmentation, also known as a partial dependency,
states if X determines Y, then XZ determines YZ, for any Z
Axiom of Transitivity:
The axiom of transitivity says if X determines Y, and Y 
determines Z, then X must also determine Z.

A SQL Join statement is used to combine data or rows from two or more tables based on the selection of common field between them. Different types of Joins are: INNER JOIN LEFT JOIN RIGHT JOIN FULL JOIN So, option (A) is correct.

A subschema is a subset of the schema and inherits the same property that a schema has. The plan for a view is often called subschema. Subschema refers to an application programmer's view of the data item types and record types, which he or she uses. Option (B) is correct.