Database Management Systems: Quiz 3

Join Selectivity

(# of records in join result)/(# of records in product of that table)

Join Operation

- Binary - Takes result of product and filters out what is needed

Update Anomaly

- if you change one entry, you have to change it everywhere; if not, you don't know if you have the correct information

Insertion Anomaly

- insert new Student w/o course; cannot be done because part of the primary key would be null (Entity Constraint prevents primary key to be null)

Partial Dependency

- some attribute can be removed from A and yet dependency still holds.

Characteristics of Normalization

1. Minimal number of attributes necessary to support data requirements. 2. Attributes with a close logical relationship are found in the same relation 3. Minimal redundancy

Characteristics of Functional Dependency

1. One-to-one relationship 2. Hold for all time 3. Determinant has minimum number of attributes necessary

6 Stages of Normalization

1st NF -> 2nd NF -> 3rd NF -> Boyce-Codd NF (BCNF) -> 4th NF -> 5th NF (Usually stop at 3rd NF or it gets too slow)

Some things you should know about 1st NF

A relation can neither have multivalued attributes nor composite attributes

Advantages of Normalization

As you go higher through forms, you have less redundancy/low levels of data anomaly

Disadvantages of Normalization

As you go higher through forms, you have more tables

Deletion Anomaly

Delete rows for Jessica (Jess why can't you see?) -> 1 course deleted and gone forever which may be needed later (also makes part of primary key null) Think of it this way: If Jessica is the last person in the class, and you delete that row, that class is deleted too, but what if someone else wants to enroll? Well shit.

Purpose of Normalization

Identify a suitable set of relations that supports data requirements of an enterprise.

Many-to-Many (Structural Constraint on Relationship)

If in one to many in both directions. - may find something with no relationship or many

Right Outer Join

Join result and appends unmatched tuples from right table

Full Outer Join

Join results appends unmatched duplex from left and right tables

Transitive Dependency (Definition)

a condition where A, B, and C are attributes of a relation such that if A is functionally dependent on B and B is functionally dependent on C, then C is transitively dependent on A via B.

Entity Type (Definition)

a group of objects with the same properties, which are identified by the enterprise as having an independent existence.

Attribute (Definition)

a property of an entity or a relationship type.

First Normal Form (1NF)

a relation in which the intersection of each row and column contains one and only one value.

Second Normal Form (2NF)

a relation that is in 1NF and every non-candidate-key attribute is fully functionally dependent on any candidate key.

Third Normal Form (3NF)

a relation that is in first and second normal form and in which no non-candidate key attribute is transitively dependent on any candidate key.

Recursive Relationship (Definition)

a relationship type in which the same entity type participates more than once in different roles.

Relationship Type (Definition)

a set of meaningful associations among entity types.

Normalization (Definition)

a technique for producing a set of relations with desirable properties, given the data requirements of an enterprise.

Relationship Occurrence (Definition)

a uniquely identifiable association that includes one occurrence from each participating entity type.

Entity Occurrence (Definition)

a uniquely identifiable object of an entity type.

Simple Attribute (Definition)

an attribute composed of a single component with an independent existence.

Composite Attribute (Definition)

an attribute composed of multiple components, each with an independent existence.

Single-Valued Attribute (Definition)

an attribute that holds a single value for each occurrence of an entity type.

Multi-Valued Attribute (Definition)

an attribute that holds multiple values for each occurrence of an entity type.

Non-key Attribute

an attribute that is neither a key nor part of a key

Derived Attribute (Definition)

an attribute that represents a value that is derivable from the value of a related attribute or set of attributes, not necessarily in the same entity type.

One-to-One (Structural Constraint on Relationship)

an entity from E1 will be related at most one entity from E2 and vice versa - can be combined in one table - FK could be in either

One-to-Many (Structural Constraint on Relationship)

an entity of type one can be related to many entities of type two, but an entity of type 2 will be related at most one entity of type 1. - must be 2 separate tables, foreign and primary keys

Weak Entity Type (Definition)

an entity type that is existence-dependent on some other entity type.

Strong Entity Type (Definition)

an entity type that is not existence-dependent on some other entity type.

Join Attributes

attributes that match between two tables for a Join operation

Multiplicity (complex relationship) (Definition)

describes the maximum number of possible relationships occurrences for an entity participating in a given relationship type.

Functional Dependency (Definition)

describes the relationship between attributes in a relation.

Participation (Definition)

determines whether all or only some entity occurrences participate in a relationship

Full Functional Dependency (Definition)

indications that if A and B are attributes of a relation, B is fully functionally dependent on A if B is functionally dependent on A, but not any proper subset of A. - Weak entities have full participation

Natural Join

obtained by removing one of the join attributes from the result of the equi-join. (Basically, remove one of the require skill because there's a duplicate)

Determinant (Definition)

refers to the attribute, or group of attributes, on the left-hand side of the arrow of a function dependency

Left Outer Join

takes the result of Join operation and appends matching tuples to the left table

Primary Key (Definition)

the candidate key that is selected to uniquely identify each occurrence of an entity type. - can be more than 1 - Alternate key is candidate keys not select as primary key (Be able to give examples when he gives you a problem)

Candidate Key (Definition)

the minimal set of attributes that uniquely identifies each occurrence of an entity type. (Be able to give examples when he gives you a problem)

Multiplicity (Definition)

the number (or range) of possible occurrences of an entity type that may relate to a single occurrence of an associated entity type through a particular relationship.

Degree of Relationship Type (Definition)

the number of participating entity types in a relationship.

Attribute Domain (Definition)

the set of allowable values for one or more attributes.