Relational Model MCQ Quiz - Objective Question with Answer for Relational Model - Download Free PDF

The correct answer is option 1.

Concept:

Option 1: A relation with only two attributes is always in BCNF.

True, Relation with two attributes always in BCNF

Example:

A relation R(XY) and functional dependency are {X→Y} it is in BCNF.

{Y→X} it is in BCNF.

{x→Y, Y→X} it is in BCNF.

Option 2: If all attributes of a relation are prime attributes, then the relation is in BCNF.

False, If all prime attributes then the relation is always in 3NF but may not be in BCNF.

Example:

A relation R(ABCD) and functional dependency are {AB→C, B→D, D→B } it is in 3NF.

Candidate key= AB, AD

AB→C is BCNF

B→D is in 3NF

D→B is in 3NF

Hence the relation is 3NF.

Option 3: Every relation has at least one non-prime attribute.

False, It is not mandatory for at least one non-prime attribute in the Relational database management system table.

Option 4: BCNF decompositions preserve functional dependencies.

False, It is not every relation can decompose into BCNF with dependency preserving. Every non-prime attribute in BCNF should be functionally dependent on one of the schema's super keys. If there is any FD that does not follow this, we must divide it into a new relationship in that case. Now, if any other FD employs the prior FD, the FD will not be preserved in BCNF.

The correct answer is A is a candidate key.

Key Points

• A candidate key is a column, or set of columns, in a table that can uniquely identify any database record without referring to any other data. A candidate key's values are unique and can be used as a primary key.
• In the given relational schema R (A, B, C), column A has distinct values including NULL values. This means that each value in column A is unique among all entries in that column, which is a requirement for being a candidate key.
• A candidate key must have unique values, and while it can have NULL values, it means it is not suitable to be a primary key (as primary keys cannot have NULL values). Hence, column A qualifies as a candidate key.
• A primary key is a candidate key that is chosen by the database designer to uniquely identify records in a table. Since A contains NULL values, it cannot be a primary key.

Additional Information

• Relational database systems typically require that each table has a primary key. This key is used to enforce entity integrity.Other candidate keys can be used as alternative keys or for creating unique constraints.
• NULL values in candidate keys can be useful in certain scenarios where not all records need to have a value for the key column.
• In SQL, the UNIQUE constraint can be used to designate a column (or combination of columns) as a candidate key.
• When designing a database schema, it's essential to identify candidate keys to ensure data integrity and efficient data retrieval.

The correct answer is Both I and II are true.

Key Points

• The primary key of a relation cannot contain null values. This is because the primary key uniquely identifies each record in a database table, and having a null value would violate this uniqueness constraint.
• A unique key can have null values. While unique keys ensure that all values in a column are different, they do allow for null values, as nulls are not considered equal to any other value, including other nulls.

Additional Information

• Primary keys are used to establish and enforce entity integrity, ensuring that each record in a table is unique and identifiable.
• Unique keys help in ensuring the uniqueness of the column values but can be used in scenarios where null values are permissible.
• Both primary keys and unique keys can be used to enforce constraints and indexes, improving the performance of database queries.
• In a relational database, both primary and unique keys are crucial for maintaining data integrity and efficient data retrieval.

The correct answer is The most complex candidate key available.

Key Points

• The primary key in a database is a unique identifier for a record within a table.
• It is chosen from the candidate keys available for the table.
• The primary key ensures that each record can be uniquely identified, preventing duplicate entries.
• It is crucial for maintaining the integrity and reliability of the database.
• The most complex candidate key is often chosen to ensure that the primary key is unique and less likely to have duplicates.

Additional Information

• All candidate keys in a table are considered before selecting the primary key.
• While simplicity is valued, the uniqueness of the key is paramount.
• Primary keys can be a single attribute or a combination of attributes (composite key).
• It is essential for establishing relationships between different tables in a relational database.
• Indexes are often created on primary keys to enhance search and query performance.

The correct option is (3)

View

Concept:-

A view is a result set of a stored query on the data in which the database users can query just as they would in a database collection.

Key Points

• In SQL, a view is a virtual table based on the result-set of an SQL statement.
• A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database.
• Views are used for security purposes because they provide encapsulation of the name of the table.
• An index is a database structure that you can use to improve the performance of database activity. A database table can have one or more indexes associated with it.
• System base tables are the underlying tables that actually store the metadata for a specific database.
• A base table is any table in the database where you are positioned and working at a certain time, for example for retrieving or modifying its data.

Answer: Option 3

Concept: 

Foreign Key :is the set of attributes in a particular relation whose values are belongs to primary key of same relation or other relation.

Explanation: 

Statement 1: A relation scheme can have at most one foreign key.

There is no such restriction on how many number of Foreign keys a particular relation can have. A relation can have as many number of Foreign keys as Required. 

So this statement is false.

Statement 2: foreign key in a relation scheme R cannot be used to refer to tuples of R.

There is no such constraint. Foreign key can be used to refer to primary key of the same relation. Self-referencing relations are examples of such foreign key. So this statement is also false.

So option 3 is the correct answer.

Function Dependencies:

{(E, F} → {G}, {F} → {I, J}, {E, H} → {K, L}, {K} → {M}, {L} → {N}}

Option 1: {E, F}

{E, F}⁺ = {E, F, G, I, J}

Since K, L, M and N is missing in RHS ∴ it is not a key

Also, {E} cannot be a key because {E} is subset of {E, F}

Option 2: {E, F, H}

{E, F, H}⁺ = {E, F, H, G, I, J, K, L, M, N}

∴ it is a key

Key for R is {E, F, H}.

Important Points:

In relation algebra, key is primary key or candidate key.

{E, F, H, K, L} is super key. 

• Attributes of the relation which exist in at least one of the possible candidate keys, are called prime or key attributes
• Candidate key is a minimal super key and a Super key is a set of attributes that uniquely identify a tuple in a relation.
• Therefore, a prime attribute of a relation scheme R is an attribute that appears in some candidate key of R

Important Point:

• Primary key is selected from a set of candidate keys of a relation.

Mistake Points 
Option 1) is not correct. Because a prime attribute is an attribute that can appear in any candidate key. The attribute need not appear in all candidate keys of R. Example:- if A is a prime attribute then for a relationship it is not required for A to be present in all candidate keys of relation R.

Concept:

Superkey is a set of attributes within a table whose values can be used to uniquely identify a tuple. A candidate key is a minimal superkey.

Superkey is superset of candidate key or primary key.

Explanation:

Primary key is VY. (given)

All superkeys must contain this primary key VY. From the given keys, key, which doesn’t contain

the VY.

Here, option 2: VWXZ

The correct option is (2)

Candidate key

Concept:-

The candidate key can be called a super key, as each candidate key is a subset of the super key. The super key with all necessary attributes is known as the candidate key. The super key with unnecessary attributes cannot be considered a candidate key.

Key Points

• A Candidate key is a minimal super key, meaning that it would cease to be a super key if you removed any attribute from the set.
• A minimum super key is referred to as a candidate and the main key since the primary key is chosen from the candidate keys.
• The minimal set of attributes that can uniquely identify a tuple is known as candidate key. For example, STUD_NO in STUDENT relation. It is a minimal super key.

Additional InformationForeign keys:- The characteristic that establishes the relationship between tables is the foreign key of a table. A foreign key is a column or columns of data in one table that connects to the primary key data in the original table.

Primary key:- The very minimum set of characteristics necessary to identify each row in a database is known as the primary key. It is chosen from a list of potential keys. The primary key might be any candidate's key.

Reference key:- The primary key that is used as a reference in the other table is known as the Reference key.

Super Key

• It is an attribute (or set of attributes) that is used to uniquely identifies all attributes in a relation. 
• All super keys can’t be candidate keys but its reverse is true. 
• There can be more than one super key.
• In relation, the number of super keys is always greater than or equal to the number of candidate keys.
• There always exists at least one super key in a table.

Candidate key

• It is a minimal set of attributes necessary to identify a tuple; this is also called a minimal super key.
• Candidate key can be more than one.
• One of the candidate keys is designated as the primary key.

Primary key

• Candidate key from the table selected by the database administrator to uniquely identify tuples in a table known as the primary key.
• Since the candidate is a minimal set of attributes necessary to identify a tuple therefore the primary key is also  a minimal set of attributes necessary to identify a tuple and hence primary key cannot be obtained by removing one or more attributes from a candidate key.

Therefore option 4 is false

If closure defined the complete relation then become the candidate key.

• Closure of AB =  (AB)⁺ = {A,B,C,D}         /candidate key
• Closure of BC =  (BC)+ = {A,B,C,D}        /candidate key
• Closure of CD = (CD)⁺ = {C,D,A} 
• Closure of DB = (DB)⁺= {B,C,D,A}         /candidate key
• Closure of ABCD = (ABCD)⁺= {A,B,C,D}    /super key
• Closure of AD = (AD)⁺ = {A,B,D}

The candidate keys are AB. BC, BD

Concept:

Candidate key: it is the set of attributes which uniquely identifies a relation. It is also known as superkey with no repeated attributes.

Explanation:

Given relation R = {A, B, C, D, E}

A ➝ B

B, C ➝ D

E ➝ C

D ➝ A

Find the closure of keys given. If all the attributes of relation are present in the closure, then it will be the candidate key of that relation.

Option1: AEF, BEF and DEF

(AEF)⁺ = {A, E, F, B, C, D}

(BEF)⁺ = {B, E, F, C, D, A}

(DEF)⁺ = {D, E, F, C, A, B}

These are the candidate keys of given relation.

Option2: AEF, BEF and BCF

(AEF)⁺ = {A, E, F, B, C, D}

(BEF)⁺ = {B, E, F, C, D, A}

(BCF)⁺ = {B, C, F, D, A}        / E is not present in the closure. Not the candidate key

Option3: AE and BE

(AE)⁺ = {A, E, C, B, D, }       /F is not present, not a candidate key

(BE)⁺ = {B, E, C, D, A}        /F is not present, not a candidate key

Option4: AE, BE and DE

(AE)⁺ = {A, E, C, B, D, }       /F is not present, not a candidate key

(BE)⁺ = {B, E, C, D, A}        /F is not present, not a candidate key

(DE)⁺ = {D, E, A, C, B}        /F is not present, not a candidate key

• A candidate key is a minimal super key or a super key with no redundant attribute.
• It is called a minimal super key because we select a candidate key from a set of super keys such that the selected candidate key is the minimum attribute required to uniquely identify the table.
• It is selected from the set of the super key which means that all candidate keys are super key.
• Primary key is selected from the set of candidate keys and hence A minimal super key is called Candidate and Primary Key.

The correct option is (1)

Keys

Concept:-

Every relation has at least one candidate key because at least the combination of all its attributes has a unique property. 

Key Points

•  In the case of base relations, one candidate key is designated as the primary key and the remaining candidate keys are called alternate keys.
• The terms 'key' has too many meanings in the database world. In the relational model alone, there are candidate keys, primary keys, alternate keys, foreign keys, search keys, and so on.
• A null value is a special marker used in SQL to indicate that a data value does not exist in the database.
• A related variable is a sign that can refer to various values at various times, hence the word variable.
• The NOT NULL constraint ensures that the null value is never allocated to a column in a table.