Understanding Dependencies & Lossless Decomposition in Relational Databases for BCNF, Slides of Database Management Systems (DBMS)

Download Understanding Dependencies & Lossless Decomposition in Relational Databases for BCNF and more Slides Database Management Systems (DBMS) in PDF only on Docsity!

Normalisation to BCNF

Normalisation so Far

• First normal form
  • All data values are atomic
• Second normal form
  • In 1NF plus no non-key attribute is partially dependent on a candidate key - Third normal form - In 2NF plus no non-key attribute depends transitively on a candidate key

Relational algebra reminder:

selection

A B

R

C D

1 c c 2 y d e 3 z a a 4 u b c 5 w c d

x

A B C D

1 c c 3 z a a

x

σC=D (R)

Relational algebra reminder:

product

A B

R

2 y

x

A C

2 v

w

R

3 u

A B A C

1 x 1 w 1 x 2 v 1 x 3 u 2 y 1 w 2 y 2 v 2 y 3 u

R1×R

Relational algebra: natural join

R1⋈R2 = πR1.A,B,C σR1.A = R2.A (R1×R2)

A B

R

2 y

x

A C

2 v

w

R

3 u

A B C

1 x w 2 y v

R1 ⋈ R

When is decomposition lossless:

Module → Lecturer

Module Lecturer Text DBS nza CB DBS nza UW RDB nza UW APS rcb B

R

Module Lecturer DBS nza RDB nza APS rcb

π (^) Module,LecturerR

Module Text DBS CB

RDB

UW

APS

UW

π (^) Module,TextR

DBS

B

When is decomposition is not

lossless: no fd

First Age John Smith 20

π (^) First,Last S ⋈ π (^) First,Last S

First Last John Smith

π (^) First,Last S

First Age John 20

Mary

30

Tom

20

π (^) First,Age S

John

10

Last

John Brown 30 Mary Smith 20 Tom Brown 10

John Brown Mary Smith Tom Brown

John Smith 30 John Brown 20

Normalisation Example

• We have a table representing orders in an online store
• Each entry in the table represents an item on a particular order - Columns - Order - Product - Customer - Address - Quantity - UnitPrice - Primary key is {Order, Product}

Normalisation to 2NF

• Second normal form means no partial dependencies on candidate keys - {Order} → {Customer, Address} - {Product} → {UnitPrice} - To remove the first FD we project over {Order, Customer, Address} (R1) and {Order, Product, Quantity, UnitPrice} (R2)

Normalisation to 2NF

• R1 is now in 2NF, but there is still a partial FD in R {Product} → {UnitPrice} - To remove this we project over {Product, UnitPrice} (R3) and {Order, Product, Quantity} (R4)

Normalisation

• 1NF:

• {Order, Product, Customer, Address, Quantity, UnitPrice}
• 2NF:
• {Order, Customer, Address}, {Product, UnitPrice}, and {Order, Product, Quantity}
• 3NF:
• {Product, UnitPrice}, {Order, Product, Quantity}, {Order, Customer}, and {Customer, Address}

The Stream Relation

• Consider a relation, Stream, which stores information about times for various streams of courses
• For example: labs for first years - Each course has several streams - Only one stream (of any course at all) takes place at any given time - Each student taking a course is assigned to a single stream for it

FDs in the Stream Relation

• Stream has the following non-trivial FDs
• {Student, Course} → {Time}
• {Time} → {Course}
• Since all attributes are key attributes, Stream is in 3NF

Anomalies in Stream

• INSERT anomalies
  • You can’t add an empty stream
• UPDATE anomalies
  • Moving the 12: class to 9:00 means changing two rows
• DELETE anomalies
  • Deleting Rebecca removes a stream

Student Course Time John Databases 12: Mary Databases 12: Richard Databases 15: Richard Programming 10: Mary Programming 10: Rebecca Programming 13: