Quiz 4 Databases

Operation that lets you select cols (Note about what is returned)

Only returns distinct stuff

Operation that lets you filter cols

Operation for renaming

Group by operator

Two ways of joining

Superkey vs key

Superkey: Set of attributes that uniquely identifies a row

Key: Minimal subset of superkey (Can’t remove anything and still have a superkey)

Functional dependency meaning of X → Y

Knowing X means we know what Y is

Closure of X (X+) meaning (Note about trivial thing)

X+ = {…,…,…}

Where everything in that set is stuff that is determined by X

Trivial thing: X is included in the closure. So X+ = {X, …,…}

BCNF Requirement

For all functional dependencies, LHS should be a superkey

Entity in ERD

Square

Attributes in ERD

Circle

Relationships in ERD

Diamond

Derived attribtues in ERD

Dotted circle

Composite

Lines from other attributes connecting to the attribute

How to turn 1:N from ERD into a table

For the table that has the N, put the 1 side in as a FK

How to turn N:N from ERD into a table

1. Create a cross reference table

2. Put the PK’s of both sides into it as FK

3. Also include stuff that might be coming out of the relationship

How to handle recursive

1. Create a cross reference table

2. Put the pk and source_pk

How to handle multivalued

1. Create a cross reference table

2. Put the PK of the entity as a FK for the table

3. Put the multivalued attribute in

Basically, each row in the table is the PK of the entity (labeled as a FK), and one value of the multivalue. Repeats for that one PK for each attribute

Remembering which side the 1 and N goes on for ERD

“One ___ can have many ___”

Whatever answers the one gets the 1

BCNF Decomposition Algorithm (Note about result)

1. Start with one BCNF violating FD. LHS is X

2. Y = X+ = Closure of X

3. Z = Everything else

One table becomes Y

The other table becomes X U Z

Gets it closer to BCNF, but not guranteed.