Tag: dbms

ER Diagrams to Tables | Practice Problems

Database Management System

ER Diagrams to Tables-

 

Before you go through this article, make sure that you have gone through the previous article on ER Diagrams to Tables.

 

After designing an ER Diagram,

  • ER diagram is converted into the tables in relational model.
  • This is because relational models can be easily implemented by RDBMS like MySQL , Oracle etc.
  • The rules used for converting an ER diagram into the tables are already discussed.

 

In this article, we will discuss practice problems based on converting ER Diagrams to Tables.

 

PRACTICE PROBLEMS BASED ON CONVERTING ER DIAGRAM TO TABLES-

 

Problem-01:

 

Find the minimum number of tables required for the following ER diagram in relational model-

 

 

Solution-

 

Applying the rules, minimum 3 tables will be required-

  • MR1 (M1 , M2 , M3 , P1)
  • P (P1 , P2)
  • NR2 (P1 , N1 , N2)

 

Problem-02:

 

Find the minimum number of tables required to represent the given ER diagram in relational model-

 

 

Solution-

 

Applying the rules, minimum 4 tables will be required-

  • AR1R2 (a1 , a2 , b1 , c1)
  • B (b1 , b2)
  • C (c1 , c2)
  • R3 (b1 , c1)

 

Problem-03:

 

Find the minimum number of tables required to represent the given ER diagram in relational model-

 

 

Solution-

 

Applying the rules, minimum 5 tables will be required-

  • BR1R4R5 (b1 , b2 , a1 , c1 , d1)
  • A (a1 , a2)
  • R2 (a1 , c1)
  • CR3 (c1 , c2 , d1)
  • D (d1 , d2)

 

Problem-04:

 

Find the minimum number of tables required to represent the given ER diagram in relational model-

 

 

Solution-

 

Applying the rules, minimum 3 tables will be required-

  • E1 (a1 , a2)
  • E2R1R2 (b1 , b2 , a1 , c1 , b3)
  • E3 (c1 , c2)

 

Problem-05:

 

Find the minimum number of tables required to represent the given ER diagram in relational model-

 

 

Solution-

 

Applying the rules that we have learnt, minimum 6 tables will be required-

  • Account (Ac_no , Balance , b_name)
  • Branch (b_name , b_city , Assets)
  • Loan (L_no , Amt , b_name)
  • Borrower (C_name , L_no)
  • Customer (C_name , C_street , C_city)
  • Depositor (C_name , Ac_no)

 

Next Article- Constraints in DBMS

 

Get more notes and other study material of Database Management System (DBMS).

Watch video lectures by visiting our YouTube channel LearnVidFun.

Types of Attributes | DBMS

Database Management System

Attributes in ER Diagram-

 

Before you go through this article, make sure that you have gone through the previous article on Introduction to ER Diagrams.

 

  • Attributes are the descriptive properties which are owned by each entity of an Entity Set.
  • There exist a specific domain or set of values for each attribute from where the attribute can take its values.

 

Types of Attributes-

 

In ER diagram, attributes associated with an entity set may be of the following types-

 

 

  1. Simple attributes
  2. Composite attributes
  3. Single valued attributes
  4. Multi valued attributes
  5. Derived attributes
  6. Key attributes

 

1. Simple Attributes-

 

Simple attributes are those attributes which can not be divided further.

 

Example-

 

 

Here, all the attributes are simple attributes as they can not be divided further.

 

2. Composite Attributes-

 

Composite attributes are those attributes which are composed of many other simple attributes.

 

Example-

 

 

Here, the attributes “Name” and “Address” are composite attributes as they are composed of many other simple attributes.

 

3. Single Valued Attributes-

 

Single valued attributes are those attributes which can take only one value for a given entity from an entity set.

 

Example-

 

 

Here, all the attributes are single valued attributes as they can take only one specific value for each entity.

 

4. Multi Valued Attributes-

 

Multi valued attributes are those attributes which can take more than one value for a given entity from an entity set.

 

Example-

 

 

Here, the attributes “Mob_no” and “Email_id” are multi valued attributes as they can take more than one values for a given entity.

 

5. Derived Attributes-

 

Derived attributes are those attributes which can be derived from other attribute(s).

 

Example-

 

 

Here, the attribute “Age” is a derived attribute as it can be derived from the attribute “DOB”.

 

6. Key Attributes-

 

Key attributes are those attributes which can identify an entity uniquely in an entity set.

 

Example-

 

 

Here, the attribute “Roll_no” is a key attribute as it can identify any student uniquely.

 

Next Article- Converting ER Diagrams to Tables

 

Get more notes and other study material of Database Management System (DBMS).

Watch video lectures by visiting our YouTube channel LearnVidFun.

Recoverability in DBMS | Recoverable Schedule

Database Management System

Schedules in DBMS-

 

Before you go through this article, make sure that you have gone through the previous article on Schedules in DBMS.

 

We have discussed-

  • A schedule is the order in which the operations of multiple transactions appear for execution.
  • Non-serial schedules may be serializable or non-serializable.

 

 

In this article, we will discuss about Non-Serializable Schedules.

 

Also read- Serializability in DBMS

 

Non-Serializable Schedules-

 

  • A non-serial schedule which is not serializable is called as a non-serializable schedule.
  • A non-serializable schedule is not guaranteed to produce the the same effect as produced by some serial schedule on any consistent database.

 

Characteristics-

 

Non-serializable schedules-

  • may or may not be consistent
  • may or may not be recoverable

 

Irrecoverable Schedules-

 

If in a schedule,

  • A transaction performs a dirty read operation from an uncommitted transaction
  • And commits before the transaction from which it has read the value

then such a schedule is known as an Irrecoverable Schedule.

 

Example-

 

Consider the following schedule-

 

 

Here,

  • T2 performs a dirty read operation.
  • T2 commits before T1.
  • T1 fails later and roll backs.
  • The value that T2 read now stands to be incorrect.
  • T2 can not recover since it has already committed.

 

Recoverable Schedules-

 

If in a schedule,

  • A transaction performs a dirty read operation from an uncommitted transaction
  • And its commit operation is delayed till the uncommitted transaction either commits or roll backs

then such a schedule is known as a Recoverable Schedule.

 

Here,

  • The commit operation of the transaction that performs the dirty read is delayed.
  • This ensures that it still has a chance to recover if the uncommitted transaction fails later.

 

Example-

 

Consider the following schedule-

 

 

Here,

  • T2 performs a dirty read operation.
  • The commit operation of T2 is delayed till T1 commits or roll backs.
  • T1 commits later.
  • T2 is now allowed to commit.
  • In case, T1 would have failed, T2 has a chance to recover by rolling back.

 

Checking Whether a Schedule is Recoverable or Irrecoverable-

 

Method-01:

 

Check whether the given schedule is conflict serializable or not.

  • If the given schedule is conflict serializable, then it is surely recoverable. Stop and report your answer.
  • If the given schedule is not conflict serializable, then it may or may not be recoverable. Go and check using other methods.

 

Thumb Rules

  • All conflict serializable schedules are recoverable.
  • All recoverable schedules may or may not be conflict serializable.

 

Method-02:

 

Check if there exists any dirty read operation.

(Reading from an uncommitted transaction is called as a dirty read)

  • If there does not exist any dirty read operation, then the schedule is surely recoverable. Stop and report your answer.
  • If there exists any dirty read operation, then the schedule may or may not be recoverable.

 

If there exists a dirty read operation, then follow the following cases-

 

Case-01:

 

If the commit operation of the transaction performing the dirty read occurs before the commit or abort operation of the transaction which updated the value, then the schedule is irrecoverable.

 

Case-02:

 

If the commit operation of the transaction performing the dirty read is delayed till the commit or abort operation of the transaction which updated the value, then the schedule is recoverable.

 

Thumb Rule

No dirty read means a recoverable schedule.

 

Next Article- Cascading Schedule | Cascading Rollback | Cascadeless Schedule

 

Get more notes and other study material of Database Management System (DBMS).

Watch video lectures by visiting our YouTube channel LearnVidFun.

View Serializability in DBMS

Database Management System

Schedules in DBMS-

 

Before you go through this article, make sure that you have gone through the previous article on Serializability in DBMS.

 

We have discussed-

  • The concept of serializability helps to identify the correct non-serial schedules that maintains the consistency of the database.
  • There are two types of serializability-

 

 

In this article, we will discuss about View Serializability.

 

View Serializability-

 

If a given schedule is found to be view equivalent to some serial schedule, then it is called as a view serializable schedule.

 

Also read- Schedules in DBMS

 

View Equivalent Schedules-

 

Consider two schedules S1 and S2 each consisting of two transactions T1 and T2.

Schedules S1 and S2 are called view equivalent if the following three conditions hold true for them-

 

Condition-01:

 

For each data item X, if transaction Ti reads X from the database initially in schedule S1, then in schedule S2 also, Ti must perform the initial read of X from the database.

 

Thumb Rule

“Initial readers must be same for all the data items”.

 

Condition-02:

 

If transaction Ti reads a data item that has been updated by the transaction Tj in schedule S1, then in schedule S2 also, transaction Ti must read the same data item that has been updated by the transaction Tj.

 

Thumb Rule

“Write-read sequence must be same.”.

 

Condition-03:

 

For each data item X, if X has been updated at last by transaction Ti in schedule S1, then in schedule S2 also, X must be updated at last by transaction Ti.

 

Thumb Rule

“Final writers must be same for all the data items”.

 

Checking Whether a Schedule is View Serializable Or Not-

 

Method-01:

 

Check whether the given schedule is conflict serializable or not.

  • If the given schedule is conflict serializable, then it is surely view serializable. Stop and report your answer.
  • If the given schedule is not conflict serializable, then it may or may not be view serializable. Go and check using other methods.

 

Thumb Rules

  • All conflict serializable schedules are view serializable.
  • All view serializable schedules may or may not be conflict serializable.

 

Method-02:

 

Check if there exists any blind write operation.

(Writing without reading is called as a blind write).

  • If there does not exist any blind write, then the schedule is surely not view serializable. Stop and report your answer.
  • If there exists any blind write, then the schedule may or may not be view serializable. Go and check using other methods.

 

Thumb Rule

No blind write means not a view serializable schedule.

 

Method-03:

 

In this method, try finding a view equivalent serial schedule.

  • By using the above three conditions, write all the dependencies.
  • Then, draw a graph using those dependencies.
  • If there exists no cycle in the graph, then the schedule is view serializable otherwise not.

 

Next Article- Practice Problems On View Serializability

 

Get more notes and other study material of Database Management System (DBMS).

Watch video lectures by visiting our YouTube channel LearnVidFun.

Conflict Serializability | Practice Problems

Database Management System

Conflict Serializability-

 

Before you go through this article, make sure that you have gone through the previous article on Conflict Serializability.

 

We have discussed-

  • The concept of serializability helps to identify the correct non-serial schedules that will maintain the consistency of the database.
  • There are two types of serializability-

 

 

In this article, we will discuss practice problems based on conflict serializability.

 

PRACTICE PROBLEMS BASED ON CONFLICT SERIALIZABILITY-

 

Problem-01:

 

Check whether the given schedule S is conflict serializable or not-

S : R1(A) , R2(A) , R1(B) , R2(B) , R3(B) , W1(A) , W2(B)

 

Solution-

 

Step-01:

 

List all the conflicting operations and determine the dependency between the transactions-

  • R2(A) , W1(A)              (T2 → T1)
  • R1(B) , W2(B)              (T1 → T2)
  • R3(B) , W2(B)              (T3 → T2)

 

Step-02:

 

Draw the precedence graph-

 

 

  • Clearly, there exists a cycle in the precedence graph.
  • Therefore, the given schedule S is not conflict serializable.

 

Problem-02:

 

Check whether the given schedule S is conflict serializable and recoverable or not-

 

 

Solution-

 

Checking Whether S is Conflict Serializable Or Not-

 

Step-01:

 

List all the conflicting operations and determine the dependency between the transactions-

  • R2(X) , W3(X)              (T2 → T3)
  • R2(X) , W1(X)              (T2 → T1)
  • W3(X) , W1(X)             (T3 → T1)
  • W3(X) , R4(X)              (T3 → T4)
  • W1(X) , R4(X)              (T1 → T4)
  • W2(Y) , R4(Y)              (T2 → T4)

 

Step-02:

 

Draw the precedence graph-

 

 

  • Clearly, there exists no cycle in the precedence graph.
  • Therefore, the given schedule S is conflict serializable.

 

Checking Whether S is Recoverable Or Not-

 

  • Conflict serializable schedules are always recoverable.
  • Therefore, the given schedule S is recoverable.

 

Alternatively,

  • There exists no dirty read operation.
  • This is because all the transactions which update the values commits immediately.
  • Therefore, the given schedule S is recoverable.
  • Also, S is a Cascadeless Schedule.

 

Problem-03:

 

Check whether the given schedule S is conflict serializable or not. If yes, then determine all the possible serialized schedules-

 

 

Solution-

 

Checking Whether S is Conflict Serializable Or Not-

 

Step-01:

 

List all the conflicting operations and determine the dependency between the transactions-

  • R4(A) , W2(A)              (T4 → T2)
  • R3(A) , W2(A)              (T3 → T2)
  • W1(B) , R3(B)              (T1 → T3)
  • W1(B) , W2(B)             (T1 → T2)
  • R3(B) , W2(B)              (T3 → T2)

 

Step-02:

 

Draw the precedence graph-

 

 

  • Clearly, there exists no cycle in the precedence graph.
  • Therefore, the given schedule S is conflict serializable.

 

Finding the Serialized Schedules-

 

  • All the possible topological orderings of the above precedence graph will be the possible serialized schedules.
  • The topological orderings can be found by performing the Topological Sort of the above precedence graph.

 

After performing the topological sort, the possible serialized schedules are-

  1. T1 → T3 → T4 → T2
  2. T1 → T4 → T3 → T2
  3. T4 → T1 → T3 → T2

 

Problem-04:

 

Determine all the possible serialized schedules for the given schedule-

 

 

Solution-

 

The given schedule S can be rewritten as-

 

 

This is because we are only concerned about the read and write operations taking place on the database.

 

Checking Whether S is Conflict Serializable Or Not-

 

Step-01:

 

List all the conflicting operations and determine the dependency between the transactions-

  • R1(A) , W2(A)              (T1 → T2)
  • R2(A) , W1(A)              (T2 → T1)
  • W2(A) , W1(A)             (T2 → T1)
  • R2(B) , W1(B)              (T2 → T1)
  • R1(B) , W2(B)              (T1 → T2)
  • W1(B) , W2(B)             (T1 → T2)

 

Step-02:

 

Draw the precedence graph-

 

 

  • Clearly, there exists a cycle in the precedence graph.
  • Therefore, the given schedule S is not conflict serializable.
  • Thus, Number of possible serialized schedules = 0.

 

Next Article- View Serializability in DBMS

 

Get more notes and other study material of Database Management System (DBMS).

Watch video lectures by visiting our YouTube channel LearnVidFun.