Normalization MCQ / Verify whether the given functional dependencies are valid or not / what is not a functional dependency / relationship between LHS and RHS attributes in a FD.

Question:

Given the instance of a relation X above, which of the following functional dependencies are TRUE with respect to X?

a) startpage à endpage

b) TR-ID à ID

c) ID à startpage endpage journal issue

d) journal issue à year

Answer: (c) ID à startpage endpage journal issue and (d) journal issue à year

Functional dependency: In simpler terms, a functional dependency is a constraint (condition) where the Left Hand Side (LHS) attribute(s) uniquely determine the value of Right Hand Side (RHS) attribute(s). In other words, for a given LHS attribute (or combination of attributes) value, there should be only one RHS value.

Let us check each of these options;

a) Startpage à endpage. This is not a valid functional dependency (FD) because the constraint does not hold for the given instance of data (refer table). For example, in the given table, the startpage values for 5^th and 6^th records are same, ie., 1. As per the definition of FD, the RHS attribute cannot have more than one value. Contrast to this, we have two different values, namely 3 and 5 as endpage values. If the constraint is violated for at least one record, we cannot accept that as a valid FD.

Refer to 2^nd and 3^rd records where startpage is 69 and endpage is 85. That is, wherever the LHS repeats, the RHS must repeat as well. If this is TRUE for all records, then we can say that the FD holds, otherwise not.

b) TR-ID à ID. This is not a valid FD. For an TR-ID value 98, we have two ID values 77 and 78. Hence violated.

c) ID à startpage endpage journal issue. This is a valid functional dependency. For a given ID value, the combinations of RHS attributes values are unique.

d) Journal issue à year. This is a valid FD. Here, for every given combination of values of attributes journal and issue, there is only one year value.

************************

Related posts:

Go to Multiple Choice Questions in DBMS home

Go to Normalization - MCQs page

Go to General Mixed Quiz in all topics of DBMS page

Go to Advanced DBMS Concepts page

Quiz questions with answers on DBMS normalization concepts

Valid functional dependencies

Find the keys of relation in DBMS

Check whether the Functional dependency is valid or not

Find the valid FDs of given relation

Understanding SQL queries / what is the result of this sql query / difference between distinct query, normal query and subquery / check the following statements and justify whether true or false

Question:

Consider the relation schemas of a database X given below. Primary keys of all the tables are underlined.

COUNTRY (cid, cname)

LOCATION (locid, countryid, lname, population)

DEPARTMENT (did, dname, noOfEmp, location, country, manager)

Consider the following queries;

A. SELECT sum(population) FROM Department D, Location L WHERE D.location=L.locid AND D.country=L.countryid;
B. SELECT sum(DISTINCT population) FROM Department D, Location L WHERE D.location=L.locid AND D.country=L.countryid;
C. SELECT sum(population) FROM Location WHERE (locid,countryid) IN (SELECT location, country FROM Department);

Use the queries A, B, and C and the database X given above, answer the following questions;

Check for each of the following statements if it is true or false. Justify your answers.

1. A and B always return the same result.
2. B and C always return the same result.
3. One of A, B, and C has a correlated sub-query.
4. One of the queries computes the total population in all countries.

Answer:

1. False. Query A counts every occurrence of the population value for each department, whereas query B eliminates duplicates and counts unique values alone (because of DISTINCT).

2. False. They may differ if two locations have the same population. In such cases, query B counts the population only once whereas query C counts each population value.

3. False. The only query that has a sub-query is query C which is not correlated. Correlated sub-queries are sub-queries that are dependent on the outer query and are executed for each row of the main query.

Query C is considered as regular sub-query (non-correlated) that is independent of the outer query and is evaluated only once before the main query runs.

5. False. None of them counts the population in locations with NO departments.

That is, for example, let us suppose we have location X. X has a location id, country id and population. If this location id and country id are not part of department table (ie., there is no department in X), then X’s population is not counted. This is TRUE for all queries.

***************

Keywords:

Find the difference between two SQL queries
Why two similar queries behave differently
What difference a sub-query and a distinct keyword make in execution of an SQL query?

TOPICS (Click to Navigate)

Wednesday, June 19, 2024