Announcements:
--------------------

Exam #1 locations:

Last names: Akinyemi - Smith: Classroom (Academy Hall Aud)

Last names: Stadtler - Zhao: JROWL (Science Center) 1C13

Accommodations: you will be notified separately


Exam #1 open book and open notes

IPAD/Computers are allowed, but no typing or taking notes, working on
draft solutions. Open all relevant tabs beforehand.

-----------
Lecture 8 exercise


----

Hierarchies

University: People // Faculty // Students

Movie Database: People // Staff // Actors

Cities  // Capitals

Subclasses inherit the attributes and the key of the parent class of entity.

Is hierarchy covering? Is the union of all subclasses make up the parent class?

Is hierarchy disjoint? Can the same entity belong to more than one subclass?


Converting hierarchies to relational data model:
---------------------------------------------------

1. Map each entity to a new relation:

People(id, name, dob)
Actor(id, name, dob, stagename)
Staff(id, name, dob,knownfor)

ActorMovieAwards
StaffMovieAwards

2. May top of the hierarchy to a new relation, and only store the
extra attributes in the other relations.

People(id, name, dob)
Actor(id, stagename)
Staff(id, knownfor)

PeopleMovieAwads
(Must join relations to get all the attributes for actors or staff)

3. Flatten the hierarchy into a single relation

People(id, name, dob, isActor, stagename, isStaff, knownfor)
PeopleMovieAwads

---------------------
Exam #1 Review

Key:

A set of attributes such that:

1. the attributes are unique in the relation: no two tuples have the
same value for these attributes but different value for the rest of
the attributes

2. the attributes are minimal: we cannot remove any attributes to get
property 1.

Superkey:

Only satisfies 1. (2 may or may not be true)

By definition all keys are superkeys.

-----------------------

A relation is a set of tuples.

Lego bricks that no one has ==>

    All lego bricks - (Bricks that someone has)

Lego bricks that everyone has ==>

    All bricks that someone has - 
    (Bricks that at least one person does not have)

    All bricks that someone has - 
    (Project_brick ( T1 (person,brick) - T2(person, brick)))

T1 is all the bricks each person can potentially have
T2 is all the bricks each person actually has

UI(userid, partid)

T1 = project_userid (UI) x project_partid (UI)
T3 = project_partid(T1 - UI)  ##bricks at least one person does not have
Result = project_partid(UI) - T3

-----------

Kits that have at least two different colors of the same brick

    KitContents join  KitContents'  same brick, same kit but different color

-----------

R(A,B)
S(A,B)

R intersect S = R * S

-------

Normalization

fds:

X->Y: whenever two tuples have the same values for X then they must
have the same values for Y.

R, F = {...}

- trivial   (AB->A, A->A)
- decomposition: A->BC  A->B A->C
- combination
- transitivity A->B, B->C, A->C
- augmentation A->B, AC->BC

F+ is the closure of fds (everything implied by F)

If F1+ and F2+ are the same, then F1 and F2 are equivalent, same meaning.

Keys:

1. A set of attributes X such that, X+ is all the attributes in the
relation with respec to F.

2. the attributes are minimal: we cannot remove any attributes to get
property 1.


R(A,B,C,D,E,F,G)  F = {AB->C, DE->B, B->F, C->D}

Key:  ADEG, ACEG

BCNF:

A relation is in BCNF, for every functional dependency X->Y

- either X->Y is trivial
- or X is a superkey


R(A,B,C,D,E,F) F = {ABC->DE, B->DF, F->AC}

Key:  B

ABC->DE   - ok because ABC is a superkey
B->DF     - ok because B is a superkey
F->AC     - not ok because F is not a superkey

3NF:

A relation is in 3NF, for every functional dependency X->Y

- either X->Y is trivial
- or X is a superkey
- or all attributes in Y are prime (attributes that are in at least one key)

--- -
Decomposition

R(A,B,C,D,E)  F = {B->CDE,D->AB,ABD->E}

R1(A,B,C)  = project_{A,B,C}(R)
R2(B,C,D)  = project_{B,C,D}(R)
R3(D,E)    = project_{D,E}(R)

The decomposition is lossless if you are guaranteed

R1 * R2 * R3 = R

  a   b  c  d  e
  a   b  c  d  e
  a   b  c3 d  e

{B->CDE,D->AB,ABD->E}

R1(A,B,C)  {B->C}


BCNF decomposition:
X->Y is an fd that violates BCNF

R1: X+ -> create a relation with X+
R2: All attributes in R except for (X+ - X)


R(A,B,C,D,E,F) F = {ABC->DE, B->DF, F->AC}

Key: B

F->AC

R1(A,C,F)
R2(B,D,E,F)

-------

MVDs

R(A,B,C)

A=>B

R1(A,B)
R2(A,C)

-----

TVShows((TVShowID), Name, TagLine)

Actors((Actorid), Name, AgentId)

Agents((AgentId), Name)

FeaturedIn((TVshowid, Actorid))

Seasons((TVSHowId, SeasonId), Channel)

Episodes((TVSHowId, SeasonId, EpisodeId),Title, DateFirstAired)

-------

Sheep((Name), eweorram, dob)

Breeds((Name), desc)

Shearers((Name), phone)

Shearings((SheepName, Date), woolweight, shearerName)

BelongsTo((SheepName, BreedName))