Solved Exercises in DBMS

Solved exercises in DBMS / Solved exercises in all areas of DBMS / Solved exercises on ER Diagrams, Normalization, Query Processing, Transaction Processing, SQL, Relational algebra etc. / Solved exercises in DBMS with answers explained.

TOPICS

• Entity - Relationship Model (ER Model)

• Relational Algebra

• Multiple Choice Questions (MCQs) in Relational algebra

• SQL and RA short exercises 1

• Relational algebra - solved exercise 2 

• Normalization

• Normalization all exercises - Home page

• Closure Finding Exercises

• TRUE/FALSE Questions

• Boyce Codd Normal Form (BCNF) Examples

• Minimal cover (Canonical cover)

• Multiple Choice Questions (MCQs) in Normalization 

• Disk Storage and Access
• Multiple Choice Questions (MCQs) in Storage and access

• Find the number of blocks exercise  

• Find the required number of buffer pages in external sorting

• Find the memory required for external sorting 

• Transaction Processing

• Lost update problem exercise 1

• Given Schedule is conflict serializable or not - solved example 1  

• Given Schedule is conflict serializable or not - solved example 2

• Given schedule is conflict serializable or not - solved exercise 3 

• Given schedule is conflict serializable or not - solved exercise 4 

• Given schedule is recoverable serializable or not - solved exercise 

• Find the transaction isolation levels of the given schedules

• Conflict serializable schedule solved example

• Transaction schedule that is conflict serializable exercise

• Non-conflict serializable solved exercise

• View serializable exercise - Non-conflict serializable exercise

• Query Processing

• Find the cost of executing SQL queries  
  

• Find the IO cost for iteration join and merge join - exercise

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

Go to Normalization - Solved Exercises page

Go to Advanced DBMS Concepts page

 

Go to Multiple Choice Questions (MCQ) in DBMS home

List of solved exercises in RDBMS

Solved exercises in Entity relationship modeling

solved problems in relational algebra

solved exercises in normalization in dbms

solved exercises in disk access and memory management in DBMS

solved exercises in transaction processing in DBMS

solved exercises in query processing in RDBMS

Important exercises with answers in RDBMS

Top interview questions and exercises in RDBMS with solutions

Normalize the table into 3NF

Normalize the given table into third normal form, 3nf example, 3nf solved exercise, How to normalize a relational schema into 3NF? Third Normal Form Normalization Process, Normalize to 3nf

Normalization Exercises

Consider the relation PLAYER with relational schema PLAYER (Player-no, Player-name, Team, Team-color, Coach-no, Coach-name, Player-position, Team-captain) and set of functional dependencies as follows;

F = {Player-no → Player-name, Player-no → Player-position, Player-no → Team, Coach-no → Coach-name, Team → Team-color, Team → Coach-no, Team → Team-captain}

Answer the questions given below;

a) Is PLAYER in 2NF? If not, convert into 2NF.

b) Is PLAYER in 3NF? If not, convert into 3NF.

a) Is PLAYER in 2NF?

To answer this question, we need to find the key for PLAYER. (Refer here - How to find key?)

Let us find the closure for all the left hand side attributes of all the FDs of F.

(Player-no)⁺ = Player-no, Player-name, Player-position, Team, Team-color, Coach-no, Team-captain, Coach-name.

(Team)⁺ = Team, Team-color, Team-captain, Coach-no, Coach-name

(Coach-no)⁺ = Coach-no, Coach-name

When we find closure, only Player-no can uniquely determine all the attributes of PLAYER. Hence, Player-no is the only candidate key.

Question to think: (Team, Player-no)+ = Player-no, Player-name, Player-position, Team, Team-color, Coach-no, Team-captain, Coach-name. (Team, Player-no) combination, as given above can uniquely determine all the attributes of PLAYER. Hence, (Team, Player-no) can be a candidate-key? NO. Reason - The proper subset of (Team, Player-no) is already a candidate key (Player-no).

As the key (Player-no) is single and simple attribute, there is no possibilities for partial-key dependencies. Hence, PLAYER is in 2NF.

b) Is PLAYER is in 3NF?

To answer this question, we need to check for non-key dependencies or transitive dependencies. That is, we have to look for dependencies like the one follows;

Non-key attribute(s) → Non-key attribute(s)

From the given set of functional dependencies F, we could derive the following non-key dependencies;

Team → Team, Team-color, Team-captain, Coach-no, Coach-name

Coach-no → Coach-no, Coach-name

Hence, PLAYER is not in 3NF.

Solution:

Decompose PLAYER into more tables based on the non-key dependencies. Then we shall get the tables as follows;

PLAYER (Player-no, Player-name, Player-position, Team)

TEAM (Team, Team-color, Team-captain, Coach-no, Coach-name)

The key for PLAYER is Player-no, and all the others are non-key attributes. Hence, PLAYER is in 2NF (no partial dependencies) and 3NF (no transitive dependencies).

The key for TEAM is Team. All the other attributes are non-key attributes and depends on Team-no. Hence, TEAM is in 2NF. TEAM has following transitive dependency;

Team → Coach-no → Coach-name.

Hence, TEAM is not in 3NF. To convert, decompose TEAM as follows;

TEAM (Team, Team-color, Team-captain, Coach-no)

COACH (Coach-no, Coach-name)

Now, TEAM and COACH are both in 2NF and 3NF.

Final set of decomposed tables that are in 3NF are;

PLAYER (Player-no, Player-name, Player-position, Team)

TEAM (Team, Team-color, Team-captain, Coach-no)

COACH (Coach-no, Coach-name)

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

Go back to Normalization – solved exercises page.

Is the given table in second normal form 2nf?

Is the given table in Second Normal Form (2NF)? Normalization solved exercise, second normal form example, normalize the given table to 2nf.

Normalization Solved Exercise

Consider a relation Movies_Screened with attributes Theatre_name, Movie, Day, Time, and Age_restriction. A sample table is given below;

Theatre_name	Movie	Day	Time	Age_restriction
Abirami	Gethu	Wed	10:00	15
Abirami	Gethu	Wed	15:00	15
Palazzo	Theri	Fri	10:00	15
Palazzo	Enthiran	Fri	10:00	12
Sathyam	Enthiran	Thu	10:00	12

A theatre can screen more than one movie on the same day and time but on different screens. Age_restriction provides the minimum age limit to watch that particular movie as instructed by the central board of film certification. Does this relation satisfy 2NF conditions? Or, is Movies_Screened in 2NF?

Solution:

The properties to be satisfied for 2NF relations are;

• Table should be in 1NF

• All non-key attributes must fully functionally dependent on the whole key (primary key).

Is Movies_Screened in 1NF?

YES. All the attributes contain only atomic values. Hence, Movies_Screened is in 1NF.

Is Movies_Screened in 2NF?

To check this, first we need to find the key for Movies_Screened. To find the key we need the possible functional dependencies that are held in the given table.

No attributes uniquely identify any other attributes in the given table.

For example,

• Theatre_name → Movie – Not hold, because it is given that a theatre may show more than one movies on different screens.

• Theatre_name → Day – Not hold, because it is given that a theatre may show more than one shows on the same day.

And so on. [Readers are requested to work on other attributes/attribute sets like this.]

• Movie → Age_restriction – Holds, because the value of Age_restriction can be uniquely identified by the value of Movie.

• Theatre_name, Movie, Day, Time → Age_restriction – Holds, because all these attributes Theatre_name, Movie, Day, and Time as a whole (together) uniquely determines the value of Age_restriction. Hence, this is the valid functional dependency.

From the above functional dependency, it is very clear that (Theatre_name, Movie, Day, Time) is the key for Movies_Screened table. It is a composite primary key.

For a table to be in 2NF, all non-key attributes must fully functionally dependent on the whole key. In our table, Age_restriction is the only non-key attribute. Also, it can be identified by the whole key and by Movie attribute. That means, the functional dependency

Movie → Age_restriction,

shows partial dependency.

Hence, the table Movies_Screened is not in 2NF.

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

Go back to Normalization – solved exercises page.