Conflict Serializability

Find out conflict serializability for the given transactions

T1	T2	T3
R(X)
		R(Y)
		R(X)  t1 <- t3
	R(Y) t3 <- t2
	R(Z) t1 <- t2
		W(Y)
	W(Z) t1 <- t2 and  t1 <- t2
R(Z)
W(X)
W(Z)

Step 1: 

Conditions for read(R) and write(W)

R-W

W-R

W-W ->W-R

        ->W-W

Step 2:

Check out the conflict pairs in other transactions and then draw edges using Precedence Graph.

Precedence Graph:

Therefore the indegree of T1=2

Therefore the indegree of T2=0

Therefore the indegree of T3=1

Step 3:

Remove the indegree of T2 whose degree is 0 then the Precedence graph

Therefore the transaction to the given set is 

T2 --- T3 --- T1

Since there is no loop or cycle so it is conflict serializable which is serializable consistent.

T1 --- T2 --- T3

T1 --- T3 --- T2

T2 --- T1 --- T3

T2 --- T3 --- T1

T3 --- T1 --- T2

T3 --- T2 --- T1

Question:

check out whether the given schedule is conflict serializable or not  

T1	T2	T3
R(A) t2<-t1 t3<-t1
	W(A) t1<-t2
W(A) t3<-t1
		W(A)

Step 1: 

Conditions for read(R) and write(W)

R-W

W-R

W-W ->W-R

        ->W-W

Step 2:

Check out the conflict pairs in other transactions and then draw edges using Precedence Graph.

Precedence Graph:

Loop occurred i.e T1 --T2 -- T2 --T1 means it is not a conflict serializable

Normalization - Problem

Rules to check out in each Normalization Form layers:

BCNF:

1. It should be 3NF

2. Left hand side must be a candidate key or super key or primary key.

3NF:

1. It should be 2NF

2. Left hand side must be a candidate key or Right hand side must be a Prime Attribute 

2NF:

1.It should be 1NF

2.Left hand side must be a proper subset of a candidate key and Right hand side must be a Non - Prime Attribute 

QUESTION) 

Schema 1:Registration( rollno, course) 

given Functional Dependency { rollno->course }

Answer:

Since Left hand side rollno is a Primary key so it is in BCNF 

Schema 2:Registration( rollno, courseid, email) 

given Functional Dependency{ rollno,courseid -> email, 

                                             email->rollno }

Answer:

candidate key ={rollno,courseid}

prime attributes = {rollno,courseid}

non-prime attribute = { email }

Left hand side rollno is a candidate key or Right hand side  email (non prime) rollno (prime) so it is in 3NF 

Schema 3:Registration( rollno, courseid,marks,grade )

given Functional Dependency { rollno, courseid -> marks, grade,

                                              marks ->grade }

Answer:

candidate key ={rollno,courseid}

prime attributes = {rollno,courseid}

non-prime attribute = { marks,grade }

Left hand side marks must be a proper subset of a candidate key and Right hand side grade must be a Non - Prime Attribute so it in 2NF 

Schema 4:Registration( rollno, courseid, credit) 

given Functional Dependency{ rollno, courseid->credit,

                                             courseid->credit }

Answer:

candidate key ={courseid}

prime attributes = {rollno,courseid}

non-prime attribute = { credits }

Left hand side credit must be a proper subset of a candidate key and Right hand side rollno and courseid must be a Non - Prime Attribute so it in 1NF 

QUESTION) 

For the Given R(ABCDEF) Check out the highest Normal form for the given Functional Dependency

{ AB->C , C->DE , E->F , F->A }

Answer:

Step 1: Find all the candidate keys in relation

(BA) ⁺=BACDEF

(BC) ⁺=BCDEFA

(BD) ⁺=BD

(BE) ⁺=BEFACD

(BF) ⁺=BFACDE

Therefore, the candidate keys are {BA, BC, BE, BF}

Step 2: Write down all the prime attributes

{A, B, C, E, F}

Step 3: Write down all the non-prime attributes

{D}

Now to check the highest normal form we start with BCNF

Rules to check out in each NF layer:

BCNF:

1. It should be 3NF

2. Left hand side must be a candidate key or super key or primary key.

3NF:

1. It should be 2NF

2. Left hand side must be a candidate key or Right hand side must be a Prime Attribute 

2NF:

1.It should be 1NF

2.Left hand side must be a proper subset of a candidate key and Right hand side must be a Non - Prime Attribute 

Note:

1NF is a subset of 2NF

2NF is a subset of 3NF

3NF is a subset of BCNF

BCNF is a subset of 4NF

4NF is a subset of 5NF 

Given Functional Dependency	AB->C	C->DE	E->F	F->A
BCNF	ü	X	X	X
3NF	ü	X	ü	ü
2NF	ü	X	ü	ü
1NF	ü	ü	ü	ü

SQL - Hospital Management System

 ER Model For Hospital Management System:

1.Patient table as entity and pat_id,pat_name,pat_disease as fields:

SQL> conn scott/tiger

Connected.

SQL> create table patient(pat_id number(3),pat_name varchar2(11),pat_disease varchar2(22));

Table created.

SQL> desc patient

 Name                                      Null?    Type

 ---------------------------------- -------- ----------------------------

 PAT_ID                                             NUMBER(3)

 PAT_NAME                                           VARCHAR2(11)

 PAT_DISEASE                                        VARCHAR2(22)

SQL> insert into patient values(101,'raju','tb');

1 row created.

SQL> insert into patient (pat_id,pat_disease) values(102,'malaria');

1 row created.

SQL> insert into patient values(&pat_id,'&pat_name','&pat_disease');

Enter value for pat_id: 103

Enter value for pat_name: rani

Enter value for pat_disease: dengue

old   1: insert into patient values(&pat_id,'&pat_name','&pat_disease')

new   1: insert into patient values(103,'rani','dengue')

1 row created.

SQL> /

Enter value for pat_id: 104

Enter value for pat_name: vani

Enter value for pat_disease: fever

old   1: insert into patient values(&pat_id,'&pat_name','&pat_disease')

new   1: insert into patient values(104,'vani','fever')

1 row created.

SQL> select * from patient;

    PAT_ID PAT_NAME    PAT_DISEASE

---------- ----------- ----------------------

       101 raju        tb

       102             malaria

       103 rani        dengue

       104 vani        fever

SQL> update patient set pat_name='sita' where pat_id=102;

1 row updated.

SQL> select * from patient;

    PAT_ID PAT_NAME    PAT_DISEASE

---------- ----------- ----------------------

       101 raju        tb

       102 sita        malaria

       103 rani        dengue

       104 vani        fever

SQL> ttitle patient

SQL> select * from patient;

Tue Nov 08                                                             page    1

                                    patient

    PAT_ID PAT_NAME    PAT_DISEASE

---------- ----------- ----------------------

       101 raju        tb

       102 sita        malaria

       103 rani        dengue

       104 vani        fever

SQL> set colsep |

SQL> select * from patient;

Tue Nov 08                                                             page    1

                                    patient

    PAT_ID|PAT_NAME   |PAT_DISEASE

----------|-----------|----------------------

       101|raju       |tb

       102|sita       |malaria

       103|rani       |dengue

       104|vani       |fever

SQL> set underline =

SQL> select * from patient;

Tue Nov 08                                                             page    1

                                    patient

    PAT_ID|PAT_NAME   |PAT_DISEASE

======|===========|======================

       101|raju       |tb

       102|sita       |malaria

       103|rani       |dengue

       104|vani       |fever

SQL> drop table patient;

Table dropped.

SQL> show recyclebin;

ORIGINAL NAME    RECYCLEBIN NAME   OBJECT TYPE  DROP TIME

--------------      -----------------------     ------------      -------------

PATIENT          BIN$DIFMHBErRTe9I+nlpHbQdA==$0 TABLE        2022-11-08:17:13:08

SQL> select * from patient;

select * from patient

              *

ERROR at line 1:

ORA-00942: table or view does not exist

SQL> flashback table patient to before drop;

Flashback complete.

SQL> select * from patient;

Tue Nov 08                                                             page    1

                                    patient

    PAT_ID|PAT_NAME   |PAT_DISEASE

========|===========|====================

       101|raju       |tb

       102|sita       |malaria

       103|rani       |dengue

2.Doctor table as entity and doc_id,doc_name,pat_id as fields:

SQL> create table doctor(doc_id number(3),doc_name varchar2(11),pat_id number(3));

Table created.

SQL> desc doctor

 Name                                      Null?    Type

 ------------------------------------- -------- -------------------------

 DOC_ID                                             NUMBER(3)

 DOC_NAME                                           VARCHAR2(11)

 PAT_ID                                             NUMBER(3)

SQL> insert into doctor values(201,'rk',101);

1 row created.

SQL> insert into doctor values(202,'pk',102);

1 row created.

SQL> insert into doctor values(203,'kk',103);

1 row created.

SQL> insert into doctor values(204,'ck',104);

1 row created.

SQL> ttitle doctor

SQL> select * from doctor;

Tue Nov 08                                                             page    1

                                     doctor

    DOC_ID|DOC_NAME   |    PAT_ID

==========|===========|==========

       201|rk         |       101

       202|pk         |       102

       203|kk         |       103

       204|ck         |        104

3.Ward table as entity and ward_id,pat_id as fields:

SQL> create table ward(ward_id number(3),pat_id number(3));

Table created.

SQL> desc ward;

 Name                                      Null?    Type

 -------------------------------- -------- ----------------------------

 WARD_ID                                            NUMBER(3)

 PAT_ID                                             NUMBER(3)

SQL> insert into ward values(301,101);

1 row created.

SQL> insert into ward values(302,102);

1 row created.

SQL> insert into ward values(303,103);

1 row created.

SQL> insert into ward values(304,104);

1 row created.

SQL> ttitle ward

SQL> select * from ward;

Tue Nov 08                                                             page    1

                                      ward

   WARD_ID|    PAT_ID

==========|==========

       301|       101

       302|       102

       303|       103

       304|       104

SQL> create table nurse(nurse_id number(3),ward_id number(3),pat_id number(3));

Table created.

SQL> desc nurse;

 Name                              Null?    Type

 ---------------------------- -------- --------------------------

 NURSE_ID                                        NUMBER(3)

 WARD_ID                                         NUMBER(3)

 PAT_ID                                             NUMBER(3)

SQL> insert into nurse values(401,301,101);

1 row created.

SQL> insert into nurse values(402,302,102);

1 row created.

SQL> insert into nurse values(403,303,103);

1 row created.

SQL> insert into nurse values(404,304,104);

1 row created.

SQL> select * from nurse;

  NURSE_ID    WARD_ID     PAT_ID

---------- ---------- ----------

       401        301        101

       402        302        102

       403        303        103

       404        304        104

SQL> ttitle nurse

SQL> select * from nurse;

Wed Nov 09                                                             page    1

                                     nurse

  NURSE_ID    WARD_ID     PAT_ID

---------- ---------- ----------

       401        301        101

       402        302        102

       403        303        103

       404        304        104

SQL> set colsep |

SQL> select * from nurse;

Wed Nov 09                                                             page    1

                                     nurse

  NURSE_ID|   WARD_ID|    PAT_ID

----------|----------|----------

       401|       301|       101

       402|       302|       102

       403|       303|       103

       404|       304|       104

SQL> set underline =

SQL> select * from nurse;

Wed Nov 09                                                             page    1

                                     nurse

  NURSE_ID|   WARD_ID|    PAT_ID

==========|==========|==========

       401|       301|       101

       402|       302|       102

       403|       303|       103

       404|       304|       104

Queries:

SQL> ttitle "Hospital Management System"
SQL> select * from patient;

Wed Nov 09                                                             page    1
                           Hospital Management System
    PAT_ID|PAT_NAME   |PAT_DISEASE
==========|===========|======================
       101|raju       |tb
       102|sita       |malaria
       103|rani       |dengue
       104|vani       |fever
SQL> select * from doctor;
Wed Nov 09                                                             page    1
                           Hospital Management System
    DOC_ID|DOC_NAME   |    PAT_ID
==========|===========|==========
       201|rk         |       101
       202|pk         |       102
       203|kk         |       103
       204|ck         |       104
SQL> select * from ward;
Wed Nov 09                                                             page    1
                           Hospital Management System
   WARD_ID|    PAT_ID
==========|==========
       301|       101
       302|       102
       303|       103
       304|       104
SQL> select * from nurse;
Wed Nov 09                                                             page    1
                           Hospital Management System
  NURSE_ID|   WARD_ID|    PAT_ID
==========|==========|==========
       401|       301|       101
       402|       302|       102
       403|       303|       103
       404|       304|       104

SQL> select * from patient where pat_disease='tb';

Wed Nov 09                                                             page    1

                           Hospital Management System

    PAT_ID|PAT_NAME   |PAT_DISEASE

==========|======|===================

       101|raju       |tb

SQL> select * from patient where pat_name like 'r%';

Wed Nov 09                                                             page    1

                           Hospital Management System

    PAT_ID|PAT_NAME   |PAT_DISEASE

==========|========|===================

       101|raju       |tb

       103|rani       |dengue

SQL> select * from doctor where doc_name like '%k';

Wed Nov 09                                                             page    1

                           Hospital Management System

    DOC_ID|DOC_NAME   |    PAT_ID

==========|===========|==========

       201|rk         |       101

       202|pk         |       102

       203|kk         |       103

SQL> select * from patient where pat_name like '%i';

Wed Nov 09                                                             page    1

                           Hospital Management System

    PAT_ID|PAT_NAME   |PAT_DISEASE

==========|=========|=================

       103|rani       |dengue

       104|vani       |fever