Lecture 4 - Relational Algebra and Normalization

Lecture 4 - Relational Algebra and Normalization#

Announcements#

  1. Lecture Exercise 4 due at midnight today.

  2. Lecture Exercise 5 is out later today, due on monday at 2pm.

  3. Homework #1 due on Monday 9/14

Today’s topics#

  • Relational Algebra, recap and natural join

  • Functional Dependencies

Relational Algebra#

  • SELECT_C ®)

  • PROJECT_X®

  • R UNION S

  • R INTERSECT S

  • R - S

  • R x S

  • R join_© S = SELECT_© (R x S)

Note:

  • Cartesian product and Theta-join (or join) requires the relations R and S to have no attributes in common. If there are attributes with the same name, they must be renamed. Otherwise, if there are attributes in R and S with the same name, the join conditions are ambiguous and the operation is not defined.

  • The result of R x S and R join S, is a relation with the schema containing all attributes in R and all attributes in S.

Natural Join: In natural join, the input relations R and S may have some attributes in common.

Suppose attributes A1,…,An are all the attributes common to R and S. Suppose X the remaining set of attributes in R (except for As) and Y is the remaining set of attributes in S.

We define the natural join R * S as follows.

1. Rename all common attributes A1,...,An in S as B1,...,Bn, and leave the remaining attributes the same name.

Sprime(B1,...,Bn,Y) = S(A1,...,An,Y)

2. Compute a join on the equality of all the common attributes:

T = R join_(A1=B1 and A2=B2 and ... and An=Bn) Sprime

3. Remove all the common attributes so that we do not repeat them twice:

Result = R * S = Project_(X,Y,A1,...,An) (T)

So, the natural join R*S is an equality join of all the common attributes and the resulting schema has all attributes from R and S, but does not repeat the common attributes.

Example Database#

  • CarTypes(CarId, Make, Model, Year, PkgId, HP, Doors, is4WD, MPG, IsSelfD, isAWD)

  • Cars(License, State, CarID, Color, Mileage, VIN)

  • StudentCars(RIN, License, State, parkingLot)

  • FacultyCars(RIN, License, State, parkingLot)

  • Faculty(RIN, Name, Email)

  • Students(RIN, Name, Email)

Find the RIN, name of all students who own a 2026 Red Kia, registered in NY and parks in ‘GLot’.

R1(carid1) = project_(carid) (select_(make=kia and year=2026) (CarTypes)))

R2(l1,s1) = project_(license,state) (select_(color=red) (Cars)) join_(carid=carid1) R1)

R3 = select_(parkinglot=Glot and state=‘NY’) StudentCars

R4(rin1) = project_(RIN) (R2 join_(license=l1 and state-s1) R3)

Result = project_(rin, email) (R4 join_(rin=rin1) Students)

Alternate solution

R1 = CarTypes * Cars * StudentCars * Students

R2 = select_(parkinglot=Glot and state=‘NY’ and make=kia and year=2026 and color=red) (R1)

Result = project_(rin, email) (R2)

  • CarTypes(CarId, Make, Model, Year, PkgId, HP, Doors, is4WD, MPG, IsSelfD, isAWD)

  • Cars(License, State, CarID, Color, Mileage, VIN)

  • StudentCars(RIN, License, State, parkingLot)

  • FacultyCars(RIN, License, State, parkingLot)

  • Faculty(RIN, Name, Email)

  • Students(RIN, Name, Email)

Find all parkinglots that have at least two cars of the same make from 2026 and one of the cars is owned by a faculty and the other one by a student.

R1 = project_(license,state,make) (Cars * select_(make=2026) (CarTypes))

R2 = project_(make, parkinglot) (R1 * StudentCars) /*student cars

R3 = project_(make, parkinglot) (R1 * FacultyCars) /*faculty cars

Result = project_(parkinglot) (R2 intersect R3)

Result = project_(parkinglot) (R2 * R3)

Find the RIN, name of all students who own two different cars of the same make.

R1 = project_(make, RIN, license, state) (Cars * CarTypes * StudentCars)

R2(m1, r1, l1, s1) = R1

R3(m2, r2, l2, s2) = R1

R4 = R1 join_(make=m1 and rin=r1 and (license<>l1 or state<>s1)) R2 /* students with at least two cars

R5 = R4 join_(make=m2 and rin=r2 and (license<>l2 or state<>s2) and (l1<>l2 or s1<>s2)) R3

Studentwithtwoormorecars = Project_(rin, name) (R4 * Students)

Studentwiththreeormorecars = Project_(rin, name) (R5 * Students)

Studentwithexactlytwocars = Studentwithtwoormorecars - Studentwiththreeormorecars

Find the license and state of all Rivians on campus that park in somewhere other than ‘GLot’.

Find all Rivians - (All Rivians that park in GLot)

Find the Faculty, who has a car in every parkingLot in the database.

Find where faculty parks in

R1 = project_(RIN, parkingLot) (FacultyCars)

Find all places all faculty can park!

R2 = project_(RIN) (Faculty) x ((project_(parkinglot) FacultyCars) union (project_(parkinglot) StudentCars))

All faculty who do not park in every lot!

R3 = R2 - R1

Result is the faculty who park in every lot!

Result = project_(RIN) (Faculty) - project(RIN) (R3)

rin parkingLot
1 A
2 B
3 G
4 A
4 B
4 G
5 B
5 G

R2:

rin parkingLot
1 A
1 B
1 G
2 A
2 B
2 G
3 A
3 B
3 G
4 A
4 B
4 G
5 A
5 B
5 G

R3:

rin parkingLot
1 A
1 B
2 G
3 A
3 B
5 A

Normalization#

Functional Dependency (fd): An fd is an expression of the form X -> Y for a relation R, where X and Y are sets of attributes where

X-> Y means if two tuples in R have the same values for X, then they must have the same values for Y.

MusicGroup(Group, Artist, Genre, DateFounded, DateJoined, DateofBirth)
Group -> Genre, DateFounded

Artist -> DateofBirth

Group Artist -> DateJoined

Group Artist -> Genre DateJoined

Artist -> Artist

Artist DateofBirth -> DateofBirth

Artist Group -> DateFounded DateofBirth

Artist Group -> Artist Group Genre DateFounded DateofBirth DateJoined

Assumes a single genre for a group

  • Key: Given a relation R, a key is the smallest set of attributes X such that X->Y is true for R where Y is the set of all attributes in R.

Students(RIN, Name, FirstMajor, Class, RCS)

F = {RIN-> Name FirstMajor Class RCS, RCS->RIN, RCS-> Name FirstMajor Class RIN}#

StudentHobbies(RIN, Hobby)

F = { }

CatalogClasses(DeptCode, CourseCode, CourseName, NumCredits, OfferedWhen)

F = { DeptCode CourseCode -> CourseName NumCredits }

Books(ISBN, Title, EditionNo, Author, Publisher, YearPublished)

F = {ISBN -> Title EditionNo Publisher YearPublished}

BirdSighting(birdername, snumber, birdname, latitude, longitude, recorded_date, description)

F = {birdername snumber -> birdname latitude longitude recorded_date description, recorded_date -> birdername snumber }

Functional Dependency Inference#

X-> Y means if two tuples in R have the same values for X, then they must have the same values for Y.

  1. Trivial fd: If

    \[Y \subseteq X, then X\rightarrow Y\]

(always true for all relations)

  1. Transivitity:

If X->Y and Y->Z, then X->Z.

  1. Decomposition

If X->YZ then X->Y and X->Z

Group Artist -> Genre DateJoined

Group Artist -> Genre Group Artist -> DateJoined

  1. Combining

If X->Y and X->Z, then X->YZ

  1. Augmentation

If X->Y then XZ->YZ

Given a set F of functional dependences, then F+ is the closure of F, the set of all functional dependencies implied by F.

Contents