DBMS structured query language chapter, Study notes of Database Management Systems (DBMS)

Download DBMS structured query language chapter and more Study notes Database Management Systems (DBMS) in PDF only on Docsity!

Database Management System

Unit- 4

Mrs. Kiran Bala Dubey

Assistant Professor

Department of Computer Science

Govt. N. P. G. College of Science,

Raipur

UNIT - IV: Structured Query Language (SQL)

Normalization concept in logical model; Pitfalls in database design,

update anomalies: Functional dependencies, Join dependencies,

Normal forms (1NF, 2NF, 3NF). Boyce Codd Normal form,

Decomposition, Multi-Valued Dependencies, 4NF, 5NF.De-

normalization.

Functional Dependency

• Functional Dependency (FD) is a constraint that determines the

relation of one attribute to another attribute in a Database

Management System.

• Functional dependency defines the dependency of attributes of a

table with other attributes or Primary key attribute of the same table.

• Functional Dependency helps to maintain the quality of data in the

database. It plays a vital role to find the difference between good and

bad database design.

• A functional dependency is denoted by an arrow "→". The functional

dependency of X on Y is represented by X → Y.

There are 5 kinds of dependencies-

1.Fully Functional dependency – In it all the non key attribute depends on the

primary key attribute.

Stud(Rno,snam, class, marks) Here rno is primary key.

Rno->snam

Rno->class

Rno->marks

2. Partial functional dependency – In it the non-key attribute besides depending

on primary key also depend on any other attribute of the table.

Rno, course_name,Stud_name,address,date_of_completion

Here rno is the primary key. The functional dependencies are-

Rno->stud_name

Rno->address

Rno,course_name->date_of_completion

So it is partial dependency.

5. Join dependency – In this hierarchical structure in which two or more attribute

depends on one thing but they themselves are not related.

Nursing Home

/\

Ward Facility

I

Patients

/\

Treatment Doctor

In this example, the ward and facility depends on Nursing Home but these two

are not dependent each other. This type of dependency is known as join

dependency.

Decomposition - Decomposition in DBMS removes redundancy,

anomalies and inconsistencies from a database by dividing the table

into multiple tables.

• Good decomposition (Lossless Decomposition)

The information will not lose from the relation when decomposed.

Decomposition is lossless if it is feasible to reconstruct relation R from

decomposed tables using Joins. The join would result in the same original

relation.

• Bad decomposition (Lossy Decomposition)

As the name suggests, when a relation is decomposed into two or more

relational schemas, the loss of information is unavoidable when the original

relation is retrieved.

First Normal Form (1NF)

• relation will be 1NF if it contains an atomic value.
• It states that an attribute of a table cannot hold multiple values. It

must hold only single-valued attribute.

• Example: Relation EMPLOYEE is not in 1NF because of multi-valued

attribute EMP_PHONE.

The decomposition of the EMPLOYEE table into 1NF has been shown

below:

EMP_ID EMP_NAME EMP_PHONE EMP_STATE

14 John 7272826385 UP 14 John 9064738238 UP 20 Harry 8574783832 Bihar 12 Sam 7390372389 Punjab 12 Sam 8589830302 Punjab

EMP_ID EMP_NAME EMP_PHONE EMP_STATE

14 John 7272826385, 9064738238

UP

20 Harry 8574783832 Bihar 12 Sam 7390372389, 8589830302 Punjab

In the given table, non-prime attribute TEACHER_AGE is dependent on

TEACHER_ID which is a proper subset of a candidate key.

To convert the given table into 2NF, we decompose it into two tables:-

1. Teacher detail table 2. Teacher subject table

TEACHER_ID TEACHER_AGE

TEACHER_ID SUBJECT

25 Chemistry 25 Biology 47 English 83 Math 83 Computer

Third Normal Form (3NF)

• A relation will be in 3NF if it is in 2NF and not contain any transitive

dependency.

• 3NF is used to reduce the data duplication. It is also used to achieve

the data integrity.

EMP_ID EMP_NAME EMP_ZIP

222 Harry 201010 333 Stephan 02228 444 Lan 60007 555 Katharine 06389 666 John 462007 EMP_ZIP EMP_STATE EMP_CITY 201010 UP Noida 02228 US Boston 60007 US Chicago 06389 UK Norwich 462007 MP Bhopal EMPLOYEE_ZIP table: EMPLOYEE table:

Boyce Codd normal form (BCNF)

• BCNF is the advance version of 3NF and is also known as 3.5 Normal Form.
• A table is in BCNF if every functional dependency X → Y, X is the super key of the table.
• For BCNF, the table should be in 3NF, if an attribute of a composite key is dependent on an attribute of the other composite key.
• Example: Professor table

Prof_code Dept HOD Percent

P1 Physics Ghosh 50

P1 Maths Krishnan 50

P2 Chemistry Rao 25

P2 Physics Ghosh 75

P3 Maths Krishnan 100

Fourth normal form (4NF)

• A relation will be in 4NF if it is in Boyce Codd normal form and has no

multi-valued dependency.

• For a dependency A → B, if for a single value of A, multiple values of B

exists, then the relation will be a multi-valued dependency.

Student table

STU_ID COURSE HOBBY

21 Computer Dancing 21 Math Singing 34 Chemistry Dancing 74 Biology Cricket 59 Physics Hockey

In the STUDENT relation, a student with STU_ID, 21 contains two

courses, Computer and Math and two hobbies, Dancing and Singing.

So there is a Multi-valued dependency on STU_ID, which leads to

unnecessary repetition of data. So to make the above table into 4NF,

we can decompose it into two tables:

STU_ID COURSE

21 Computer 21 Math 34 Chemistry 74 Biology 59 Physics

STUDENT_COURSE