Study Notes NepalPosted in Uncategorized

1. Normalize the following relation ( emp_id, emp_name, emp_zip, emp_state, emp_city, emp_district ) to 3NF

emp_id emp_name emp_zip emp_state emp_city emp_district
1001 Ram 282005 state7 Dipayal Doti
1002 Keshab 555780 state2 Nepalgunj Banke
1003 Pawan 822005 state7 Dhangadhi Kailali
1004 Aardi 772290 state4 Pokhara Kaski
1005 sunita 885009 state3 Kathmandu Ktm

 

Solution:

First Normal Form(1NF)

Here we consider emp_id as the Primary Key and all the columns have atomic values. Thus, the table is in 1NF.

R1

emp_id emp_name emp_zip emp_state emp_city emp_district
1001 Ram 282005 state7 Dipayal Doti
1002 Keshab 555780 state2 Nepalgunj Banke
1003 Pawan 822005 state7 Dhangadhi Kailali
1004 Aardi 772290 state4 Pokhara Kaski
1005 sunita 885009 state3 Kathmandu Ktm

Second Normal Form(2NF)

There aren’t any Partial Dependencies in R1 as emp_id is a Single Primary Key. Hence, R1 is also in Second Normal Form(2NF).

 

Third Normal Form(3NF)

Let’s check if R1 has Transitive Dependency.

There are multiple transitive dependencies caused by the column emp_zip.

emp_id → emp_zip

emp_zip → emp_city
emp_zip → emp_district
emp_zip → emp_state

i.e.,

emp_id → emp_zip → emp_city
emp_id → emp_zip → emp_district
emp_id → emp_zip → emp_state

Hence we decompose R1 into R2 and R3 to get rid of them.

R2

emp_id emp_name emp_zip
1001 Ram 282005
1002 Keshab 555780
1003 Pawan 822005
1004 Aardi 772290
1005 sunita 885009

R3

emp_zip emp_state emp_city emp_district
282005 state7 Dipayal Doti
555780 state2 Nepalgunj Banke
822005 state7 Dhangadhi Kailali
772290 state4 Pokhara Kaski
885009 state3 Kathmandu Ktm

The Transitive Dependency caused by emp_zip is now removed.

However, R3 could be decomposed further into R4 and R5 because of the following transitive dependency:

emp_zip → emp_district

emp_district → emp_state

i.e., emp_zip → emp_district → emp_state

R4

emp_zip emp_city emp_district
282005 Dipayal Doti
555780 Nepalgunj Banke
822005 Dhangadhi Kailali
772290 Pokhara Kaski
885009 Kathmandu Ktm

R5

emp_district emp_state
Doti state7
Banke state2
Kailali state7
Kaski state4
Ktm state3

Here, another transitive dependency (emp_zip → emp_district → emp_state) is removed. Hence, R2, R4 and R5 are in Third Normal Form(3NF).

 

2. Consider the following relational database:

  • Patient(PID, Pname, Paddress, Pgender, disease)
  • Doctor(DID, Dname, Daddress, Department, salary)
  • Appointment(PID, DID, time)

Write SQL statements for the following:

  1. Insert a new tuple in the relation patient.
  2. List all the patients who were checked by doctors Pankaj and Rubi.
  3. List name and id of doctors whose salary is less than Rs. 120,000.
  4. Find name of patients whose name begins with ‘Ru’.
  5. Increase salary of all doctors by 10% who works in Forensic department.

a. Insert a new tuple in the relation patient.

INSERT INTO Patient

VALUES (‘P008’, ‘Ramesh’, ‘Dhangadhi’, ‘Male’, ‘Fever’);

b. List all the patients who were checked by doctors Pankaj and Rubi.

SELECT      p.PID,

                      p.Pname

FROM         Patient p JOIN Appointment a

ON p.PID = a.PID

JOIN Doctor d ON d.DID = a.DID

WHERE    d.Dname = ‘Pankaj’

OR d.Dname = ‘Rubi’;

c. List name and id of doctors whose salary is less than Rs. 120,000.

SELECT      Dname, DID

FROM          Doctor

WHERE    salary < 120000;

d. Find name of patients whose name begins with ‘Ru’.

SELECT     Pname

FROM       Patient

WHERE Pname  LIKE  ‘Ru%’;

e. Increase salary of all doctors by 10% who works in Forensic department.

UPDATEDoctor

SET           salary = salary * 1.10

WHERE    department = ‘Forensic’;

3. Compute Closure of AB and DC for given Relation and Fucntional Dependencies

Let

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

and

F = { AB → C, A → D, D → E, AC → B }

Then compute {AB}+ and {DC}+.

Solution:

Given Relation:

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

Given Functional Dependencies:

AB → C
A → D
D → E
AC → B

 

Closure of AB

{AB}+ = {AB} (axiom of reflexivity)

= {ABC} (by union rule & given FD: AB → C)

= {ABCD} (by union rule & given FD: A → D)

      = {ABCDE} (by union rule & given FD: D → E)

Closure of DC

{DC}+ = {DC} (axiom of reflexivity)

      = {DCE} (by union rule & given FD: D → E)

Hence,

Closure of AB, {AB}+ = {ABCDE}

and

Closure of DC, {DC}+ = {CDE}

 

4. Write Relational Algebra for relation student (sid, sname, address, gender, age)

Consider the relation:

student (sid, sname, address, gender, age)

Write relational algebra(RA) for the following:

  1. To display name of student whose age is greater than 15.
  2. To remove the record of the student who are from Kathmandu.
  3. To update address of student to Kathmandu whose sid is ‘S1101’.

a. To display name of student whose age is greater than 15.

π sname ( σ age > 15 ( student ) )

b. To remove the record of the student who are from Kathmandu.

student ← student – ( σ address = ‘Kathmandu’ ( student ) )

c. To update address of student to Kathmandu whose sid is ‘S1101’.

student ← π sid, sname, address = ‘Kathmandu’, gender, age ( σ sid = ‘S1101’ ( student ) ) ∪ ( σ sid ≠ ‘S1101’ ( student ) )

 

5. Normalize the following table to 3NF.

Sid Cid SName C_Name Grade Faculty F_Phone
1 IS208 Adams Database A Padam B. Karki 601245
1 IS301 Adams Programming B Ravi Gurung 458695
2 IS208 Jones Database A Padam B. Karki 601245
3 IS208 Smith Database B Padam B. Karki 601245
4 IS301 Baker Programming A Ram Sundar 452368
4 IS208 Baker Database B Padam B. Karki 601245

Steps for Normalization

  1. First Normal Form(1NF)
    1. R1
  2. Second Normal Form(2NF)
    1. R2
    2. R3
    3. R4
  3. Third Normal Form(3NF)
    1. R5
    2. R6

1. First Normal Form(1NF)

Let’s check if there are any non-atomic values. Looking at the original question paper, it is easy to get confused and think that the column ‘Faculty‘ has multiple values.

DBMS_normalization_Question_NCC_College

We must decide whether Padma B. Karki is a single person or Padma B. and Karki are a pair. In some of the rows, ‘Padma B’ and ‘Karki’ are separated by a comma (,), however ‘Padma B. Karki’ is used in other rows.

We can confirm that the comma(,) is a printing mistake and it’s a single name ‘Padma B. Karki‘ because:

  1. ‘Karki’ is a common surname in Nepal. So, it’s more likely to be Padma B’s surname.
  2. The corresponding F_Phone column has only single values.

After deciding that, we can say that all the attributes are atomic. Also, we utilize Sid and Cid as the composite primary key in our table R1. Thus the table R1 is in First Normal Form(1NF).

1. R1

Sid Cid SName C_Name Grade Faculty F_Phone
1 IS208 Adams Database A Padam B. Karki 601245
1 IS301 Adams Programming B Ravi Gurung 458695
2 IS208 Jones Database A Padam B. Karki 601245
3 IS208 Smith Database B Padam B. Karki 601245
4 IS301 Baker Programming A Ram Sundar 452368
4 IS208 Baker Database B Padam B. Karki 601245

 

2. Second Normal Form(2NF)

In relation R1, the following partial dependencies occur.

{ Sid, Cid } → SName
because SName depends only on Sid

{ Sid, Cid } → C_Name
because C_Name depends only on Cid

Let’s decompose the relation R1 into R2, R3 and R4 to get rid of partial dependencies.

1. R2

Sid SName
1 Adams
2 Jones
3 Smith
4 Baker

 

2. R3

Cid C_Name
IS208 Database
IS301 Programming

 

The other attributes are dependent on both Sid and Cid. In other words, the functional dependencies:

{ Sid, Cid } → Grade
{ Sid, Cid } → Faculty
{ Sid, Cid } → F_Phone

are fully functional dependencies because (Grade, Faculty, and F_Phone) can be derived only from the combination of {Sid, Cid}. That’s why the relation R4 will still use {Sid, Cid} as the composite primary key.

3. R4

Sid Cid Grade Faculty F_Phone
1 IS208 A Padam B. Karki 601245
1 IS301 B Ravi Gurung 458695
2 IS208 A Padam B. Karki 601245
3 IS208 B Padam B. Karki 601245
4 IS301 A Ram Sundar 452368
4 IS208 B Padam B. Karki 601245

All the partial dependencies are removed and R2, R3 and R4 are in Second Normal Form(2NF).

 

3. Third Normal Form(3NF)

The relations R2 and R3 do not have any transitive dependencies.

R4 however, has the following transitive dependencies:

{ Sid, Cid } → Faculty
Faculty → F_Phone

i.e., { Sid, Cid } → Faculty → F_Phone

 

So, we need to decompose R4 into R5 and R6 to get rid of the transitive dependency.

Due to the fact that person names are not unique in this situation, the column Faculty cannot serve as a primary key. In these circumstances, it is preferable to include a substitute (fake) primary key for it. As a result, we are making Faculty_ID a primary key for R6.

1. R5

Sid Cid Grade Faculty_Id
1 IS208 A 1
1 IS301 B 2
2 IS208 A 1
3 IS208 B 1
4 IS301 A 3
4 IS208 B 1

 

2. R6

Faculty_Id Faculty F_Phone
1 Padam B. Karki 601245
2 Ravi Gurung 458695
3 Ram Sundar 452368

 

The final relations are R2, R3, R5 and R6 in the Third Normal Form(3NF).

 

6. Normalize the following table(question corrected).

Cust_Id Cust_name city Zip_code Cust_address House_no Items Company Price
1 Bob Ktm, Pok 001 Sanothimi 009 Mobile Samsung 50000
2 John But, Bhair 002 Lalitpur 010 TV Sony 80000
3 Cristina Narayangadh 003 Baneshwor 111 Camera LG 55000
4 Katrina Dang 004 New Road 112 Laptop Dell 65000

Steps for normlization

  1. First Normal Form(1NF)
    1. R1
    2. R2
  2. Second Normal Form(2NF)
    1. Check for partial dependencies.
  3. Third Normal Form(3NF)
    1. R3
    2. R4

1. First Normal Form(1NF)

The column city has non-atomic values (‘Ktm, Pok’ and  ‘But, Bhair’), which means multiple cities. Let’s decompose the table and bring the multi-valued attribute ‘city’ to a different table R2 and the rest of the attributes will be in R1.

1. R1

Cust_Id Cust_name Zip_code Cust_address House_no Items Company Price
1 Bob 001 Sanothimi 009 Mobile Samsung 50000
2 John 002 Lalitpur 010 TV Sony 80000
3 Cristina 003 Baneshwor 111 Camera LG 55000
4 Katrina 004 New Road 112 Laptop Dell 65000

2. R2

Cust_Id city
1 Ktm
1 Pok
2 But
2 Bhair
3 Narayangadh
4 Dang

Now, the tables R1 and R2 are in First Normal Form since there aren’t any no-atomic values.

 

2. Second Normal Form(2NF)

  • Check for partial dependencies.

In R1, there is a single(non-composite) primary key. Hence R1 does NOT have partial dependencies.
In R2, there are only prime attributes. Hence, R2 does NOT have partial dependencies too.

Thus, R1 and R2 are in the Second Normal Form.

 

3. Third Normal Form(3NF)

There are transitive dependencies in the relation R1.

Cust_Id → Items

Items → Company

i.e., Cust_Id → Items → Company

Also,
Items → Price
i.e., Cust_Id → Items → Price

Let’s decompose R1 into R3 and R4 to bring it to the Third Normal Form.

Note: Here, the column Items is not suitable to be a Primary Key because item names are not unique. It is always a safer option to add a surrogate(artificial) primary key for it. So, we are adding the extra column Item_ID as a primary key.

1. R3

Item_Id Items Company Price
1 Mobile Samsung 50000
2 TV Sony 80000
3 Camera LG 55000
4 Laptop Dell 65000

 

2. R4

Cust_Id Cust_name Zip_code Cust_address House_no Item_Id
1 Bob 001 Sanothimi 009 1
2 John 002 Lalitpur 010 2
3 Cristina 003 Baneshwor 111 3
4 Katrina 004 New Road 112 4

 

There aren’t any transitive dependencies anymore in R3 and R4. Hence, our final tables are R2, R4 and R5 which are in the Third Normal Form(3NF). The question has not targeted any particular normal form. In such cases, the relations in 3NF are considered good enough.

 








	


	

			




	



		

		
Leave a Reply 
Your email address will not be published. Required fields are marked *