Docsity
Log inSign up
Docsity
Prepare for your exams
Prepare for your exams

Study with the several resources on Docsity

Earn points to download
Earn points to download

Earn points by helping other students or get them with a premium plan

Guidelines and tips
Guidelines and tips
Sell on Docsity
Sell on Docsity
Log inSign up

Prepare for your exams

Study with the several resources on Docsity

Find documents

Prepare for your exams with the study notes shared by other students like you on Docsity

Search Store documents

The best documents sold by students who completed their studies

Search through all study resources
Docsity AINEW

Summarize your documents, ask them questions, convert them into quizzes and concept maps

Explore questions

Clear up your doubts by reading the answers to questions asked by your fellow students

Earn points to download

Earn points by helping other students or get them with a premium plan

Share documents
download point icon20 Points

For each uploaded document

Answer questions
download point icon5 Points

For each given answer (max 1 per day)

All the ways to get free points
Get points immediately

Choose a premium plan with all the points you need

Study Opportunities

Choose your next study program

Get in touch with the best universities in the world. Search through thousands of universities and official partners

Community

Ask the community

Ask the community for help and clear up your study doubts

University Rankings

Discover the best universities in your country according to Docsity users

Free resources

Our save-the-student-ebooks!

Download our free guides on studying techniques, anxiety management strategies, and thesis advice from Docsity tutors

From our blog

Exams and Study
Go to the blog

Design Guidelines for Relational Schema and Normalization in Database Systems, Study notes of Mobile Computing

University of GreenwichMobile Computing

The objectives of a database design lecture, focusing on guidelines for creating efficient relational schemas, functional dependencies, and normalization up to the boyce-codd normal form. The slides are based on the textbook 'fundamentals of database systems' by elmasri & navathe.

Typology: Study notes

2010/2011

Uploaded on 09/08/2011

rossi46
rossi46 šŸ‡¬šŸ‡§

4.5

(10)

313 documents

1 / 7

Toggle sidebar

This page cannot be seen from the preview

Don't miss anything!

bg1
DBDI /FD & Norm 30/05/2007
Lecture_5 1
DBDI/ Lecture 5
Functional Dependencies &
Normalization
Dr. Ala Al-Zobaidie
The slides are based on the textbook:
Fundamentals of Database Systems by Elmasri & Navathe
30/05/2007 DBDI /FD & Norm 2
Lectureā€™s Objectives
ā€¢ Design guidelines for relational schema
ā€¢ Definition of partial and full dependencies
ā€¢ Transitive dependency
ā€¢ Dependency Diagrams
ā€¢ Rules about functional dependencies
ā€¢ Logical vs. Storage design
ā€¢ The three normal forms
ā€¢ Boyce-Codd Normal Form
30/05/2007 DBDI /FD & Norm 3
Design guidelines for relational
schema
ā€¢ Guideline 1:
ā€“ Semantic of the relation attributes
ā€¢ Do not mix attributes from distinct real
world
ā€¢ Guideline 2:
ā€“ Reducing the redundant values in tuples
ā€¢ Reducing storage area
ā€¢ Avoiding update anomalies
30/05/2007 DBDI /FD & Norm 4
Guideline 2: Reducing storage area
ā€¢ Reducing storage area
ā€“Compare space for 2 schemas below:
30/05/2007 DBDI /FD & Norm 5
Update Anomalies
ā€¢ Create a training dept#6
ā€¢ Deleting Employee James E. Bong
ā€¢ Change name of dept#5 from Research to Research &
Development
30/05/2007 DBDI /FD & Norm 6
Guideline 2: Avoiding update
anomalies
ā€¢ Insertion:
ā€¢ We cannot insert partial new data, as we must have a
value for the PK.
ā€¢ PK id not necessarily related to the new data!
ā€“ Means: cannot create a dept without EMPs, or vice versa
ā€“ e.g. Create a training dept#6
ā€¢ Deletion:
ā€¢ Deleting the last record may cause permanent loss of
information
ā€“ e.g. dept#1
ā€¢ Modification:
ā€¢ Update one instance entails correctly updating all copies
ā€“ e.g. change the name of dept#5 to Research &
Development
pf3
pf4
pf5

Partial preview of the text

Download Design Guidelines for Relational Schema and Normalization in Database Systems and more Study notes Mobile Computing in PDF only on Docsity!

DBDI/ Lecture 5

Functional Dependencies &

Normalization

Dr. Ala Al-Zobaidie The slides are based on the textbook: Fundamentals of Database Systems by Elmasri & Navathe

30/05/2007 DBDI /FD & Norm 2

Lectureā€™s Objectives

  • Design guidelines for relational schema
  • Definition of partial and full dependencies
  • Transitive dependency
  • Dependency Diagrams
  • Rules about functional dependencies
  • Logical vs. Storage design
  • The three normal forms
  • Boyce-Codd Normal Form

30/05/2007 DBDI /FD & Norm 3

Design guidelines for relational

schema

  • Guideline 1:
    • Semantic of the relation attributes
      • Do not mix attributes from distinct real world
  • Guideline 2:
    • Reducing the redundant values in tuples
      • Reducing storage area
      • Avoiding update anomalies

30/05/2007 DBDI /FD & Norm 4

Guideline 2: Reducing storage area

  • Reducing storage area
    • Compare space for 2 schemas below:

Update Anomalies

  • Create a training dept#
  • Deleting Employee James E. Bong
  • Change name of dept#5 from Research to Research & Development

Guideline 2: Avoiding update anomalies

  • Insertion:
    • We cannot insert partial new data, as we must have a value for the PK.
    • PK id not necessarily related to the new data!
      • Means: cannot create a dept without EMPs, or vice versa
      • e.g. Create a training dept#
  • Deletion:
    • Deleting the last record may cause permanent loss of information - e.g. dept#
  • Modification:
    • Update one instance entails correctly updating all copies
      • e.g. change the name of dept#5 to Research & Development

30/05/2007 DBDI /FD & Norm 7

Emp-Dept & Emp_Prj: Suffers from Update Anomalies due to Redundancy

30/05/2007 DBDI /FD & Norm 8

Guideline 3: Reducing the Null values in

tuples

  • Avoid Null values
    • Waste space at storage level
    • No semantic:
      • Does it apply to the instance?
      • Applicable but absent?
      • Zeros or Blanks?
    • Problems with specifying Join operations at logical level (natural vs. outer joins)
    • May pause problems with aggregate functions:
      • Nulls are eliminated from all aggregate functions apart from count(*)

30/05/2007 DBDI /FD & Norm 9

Guideline 4: Disallowing the generation of

spurious tuples

  • Theā€œLossless Joinā€ property:
    • Design relations so that they can be JOINed with equality condition ā€œEquijoinā€ on attributes that are either PKs or FKs
    • This will guarantee no generation of spurious tuples.

30/05/2007 DBDI /FD & Norm 10

Guideline 4: Ggeneration of spurious

tuples/1 ā†’ Lossless Join

Query: List the name & hours of all projects that John Smith worked on.

3

Guideline 4: Ggeneration of spurious

tuples/2 ā†’ Lossless Join

Query: List the name & hours of all projects that John Smith worked on.

3 Ā² Ā² Ā²

3

Guideline 4: Ggeneration of spurious

tuples/

Ā² Ā² Ā²

3 3

30/05/2007 DBDI /FD & Norm 19

Dependency Inference Rules/

  • A1. (Reflexive): If Y āŠ† X, then X ā†’ Y
  • A2. (Augmentation): If X ā†’ Y, then XZ ā†’ YZ (Notation: XZ stands for X āˆŖ Z)
  • A3. (Transitive): If X ā†’ Y and Y ā†’ Z, then X ā†’ Z A1, A2, A3 form a sound and complete set of inference rules 30/05/2007 DBDI /FD & Norm 20

Dependency Inference Rules/

Some additional inference rules that are

useful:

  • A4. (Decomposition):

If X ā†’^ YZ, then X^ ā†’^ Y and X^ ā†’^ Z

  • A5. (Union):

If X ā†’ Y and X ā†’ Z, then X ā†’ YZ

  • A6. (Psuedo-transitivity):

If X ā†’ Y and WY ā†’ Z, then WX ā†’ Z

Also expressed as

  • If X ā†’ Y and W ā†’ Z, then XW ā†’ YZ (Composition)

30/05/2007 DBDI /FD & Norm 21

Logical vs. Storage design

  • Our aim in relational DB design is:
    • The grouping of attributes to form ā€œgoodā€ relation schema
  • Two level of relation schema:
    • The logical ā€œuser viewā€ level
    • The storage ā€œbase relationā€ level
  • Design is concerned mainly with base

relations

30/05/2007 DBDI /FD & Norm 22

The Process of Normalization

  • Formal technique for analyzing a relation based on its PK & FDs between its attributes.
  • A series of steps. Each corresponds to a specific normal form, which has known properties.
  • As normalization proceeds, relations become progressively more restricted (stronger) in format and also less vulnerable to update anomalies.

Unnormalised Form (UNF)

  • A table that contains one or more repeating groups.
  • To create an unnormalized table:
  • transform data from information source (e.g. form) into table format with columns and rows. A relation schema which is not in Normalised form

Example of a relation instance

Multi Value

First Normal Form (1NF)

  • A relation in which intersection of each row and column contains one and only one value. - Disallows composite attributes, multivalued attributes & nested relations
  • UNF to 1NF
    • Nominate an attribute or group of attributes to act as the key for the unnormalized table.
    • Identify repeating group(s) in unnormalized table which repeats for the key attribute(s).

30/05/2007 DBDI /FD & Norm 25

UNF to 1NF/

  • Remove repeating group by: entering appropriate data into the empty columns of rows containing repeating data (ā€˜flatteningā€™ the table).

1 st^ NF relation with redundancy

30/05/2007 DBDI /FD & Norm 26

UNF to 1NF/ Or by

  • placing repeating data along with copy of the original key attribute(s) into a separate relation.

30/05/2007 DBDI /FD & Norm 27

Second Normal Form (2NF)

  • Based on concept of full functional dependency:
    • Prime attribute: attribute that is member of the PK
    • Full FD: a FD Y ā†’ Z where removal of any attribute from Y means FD does not hold any more
    • Examples:
      • {SSN, Pnumber} ā†’ Hours is a Full FD since neither SSN ā†’ Hours nor Pnumber ā†’ Hours hold.
      • {SSN , Pnumber} ā†’ Ename is a Partial dependency as only SSN ā†’ Ename
  • 2NF - A relation that is in 1NF and every non- primary-key attribute is fully functionally dependent on the primary key.

30/05/2007 DBDI /FD & Norm 28

1NF to 2NF

  • Identify primary key for the 1NF relation.
  • Identify functional dependencies in the relation.
  • If partial dependencies exist on the primary key remove them by placing them in a new relation along with copy of their determinant.

SSN PNUMBER HOURS ENAME PNAME PLOCATION

Examples of transformation into 2NF

Into 2NF

EMP_PROJ

FD FD FD

Third Normal Form (3NF)

  • Based on concept of transitive

dependency:

  • 3NF - A relation that is in 1NF and 2NF

and in which no non-primary-key

attribute is transitively dependent on

the primary key.

  • 3NF - All non-prime attributes are fully &

directly dependent on the PK.

A B C

30/05/2007 DBDI /FD & Norm 37

BCNF / Example /

  • Consider this scenario:
    • The DSD company provides end user software training in Database, Network & Spreadsheets
    • DSD employs several trainers in each of the three subject.
  • Each trainer teaches only one subject, that is a Database trainer teaches Database only.
  • Corporate customers may elect to purchase training contracts for one or more subjects.

1004 Database Alun

1003 Spreadsheet Phil

1002 Database Ala

1001 Network Sati

1001 Database Ala

Client Subject Staff

30/05/2007 DBDI /FD & Norm 38

  • 2 composite candidate keys:
    • FD1:{Client, Subject} ā†’ Staff
    • FD2:{Client & Staff} ā†’ Subject
  • These candidate keys are overlapping on Client.

Staff is a determinate but not a candidate key

BCNF / Example /

Client Subject Staff

Client Staff Subject

30/05/2007 DBDI /FD & Norm 39

BCNF / Example /

  • What is the Problem? - Anomalies
  • Delete client 1004 will also delete Tony teaches Database. So is for client 1001 on Network.
  • Hence, we need to decompose table into two to get rid of redundancies.

1004 Database Alun

1003 Spreadsheet Phil

1002 Database Ala

1001 Network Sati

1001 Database Ala

Client Subject Staff

30/05/2007 DBDI /FD & Norm 40

BCNF / Example /

  • Avoid mixing attributes belongs to different entities

Alun Database

Phil Spreadsheet

Sati Network

Ala Database

Staff Subject

Client Staff (^) Staff Subject

1002 Ala

1004 Alun

1003 Phil

1001 Sati

1001 Ala

Client Staff

30/05/2007 DBDI /FD & Norm 41

Summary

ā€¢ We looked at:

ā€“Informal Design guidelines

ā€“Partial, Full & Transitive functional

dependencies

ā€“Dependency Diagrams

ā€“The three normal forms

ā€“Boyce-Codd Normal Form