Chapter 4: Logical Database Design and the Relational Model

Second Normal Form

- 1NF plus every non key attribute is fully functionally dependent on the ENTIRE primary key - Every non key attribute must be defined by the entire key, not by only part of the key - No partial functional dependencies

Candidate Key

- A unique identifier. One of the candidate keys will become the primary key - Each non key field is functionally dependent on every candidate key.

What normal form is this?

1st normal form

Partial Dependency

A condition in which an attribute is dependent on only a portion (subset) of the primary key.

Functional Dependency

A constraint between two attributes in which the value of one attribute is determined by the value of another attribute.

Relation

A named, two-dimensional table of data. Each relation consists of a set of named columns (attribute or field) and an arbitrary number of unnamed rows (records).

Example of a Candidate Key

If there is both a credit card number and a SS# in a table, then in this case they are both candidate keys

Data integrity

Mechanisms for implementing business rules that maintain integrity of manipulated data

Entity Integrity

No primary key attribute may be null. All primary key fields MUST contain data values.

Is this table in 1st normal form?

No; it has multi value attributes

Is this table in 2nd normal form?

No; this features partial dependencies

Referential Integrity

Rules that maintain consistency between the rows of two related tables.

What happens to non-key determinants with transitive dependencies?

They go into a new table; non-key determinant becomes primary key in the new table and stays as foreign key in the old table

True or False: Keys can be simple (a single field) or composite (more than one field).

True

When mapping regular entities to relations, what is the rule for composite attributes?

Use only their simple, component attributes

How are referential integrity constraints drawn?

Via arrows from dependent to parent table

Is this table in 2nd normal form?

Yes; this features only transitive dependencies

Is this table in 1st normal form?

Yes; this is an example of a relation

Is this table in 3rd normal form?

Yes; transitive entities are not present

Is this a relation?

Yes; unique rows and no multivalued attributes

Insertion Anomaly

adding new rows forces user to create duplicate data

Deletion Anomaly

deleting rows may cause a loss of data that would be needed for other future rows

An issue with Transitive dependencies when merging entities

even if relations are in 3NF prior to merging, they may not be after merging

Example of Modification Anomaly

giving a salary increase to employee 100 forces us to update multiple records

Foreign keys

identifiers that enable a dependent relation (on the many side of a relationship) to refer to its parent relation (on the one side of the relationship).

What is the insertion anomaly for this relation?

if new product is ordered for order 1007 of existing customer, customer data must be re-entered causing duplication

What is the deletion anomaly for this relation?

if we delete the Dining Table from Order 1006, we lose information concerning this item's finish and price

Example of Deletion Anomaly

if we remove employee 140, we lose information about the existence of a Tax Acc class

An issue with Supertype/subtype relationships when merging entities

may be hidden prior to merging

Is this well-designed or poorly-designed?

poorly designed

Why are transitive dependencies named that way?

primary key is a determinant for another attribute, which in turn is a determinant for a third

Referential Integrity Rule

rule states that any foreign key value (on the relation of the many side) MUST match a primary key value in the relation of the one side.(Or the foreign key can be null.)

View Integration

Combining entities from multiple E-R models into common relations

What's the primary keys here?

Composite Key of EmpID and CourseTitle

When mapping regular entities to relations, what is the rule for simple attributes?

E-R attributes map directly onto the relation

Homonyms

tributes with same name but different meanings

Synonyms

two or more attributes with different names but same meaning

Primary keys

unique identifiers of the relation. Examples include employee numbers, social security numbers, etc. This guarantees that all rows are unique.

What are primary Key of Weak Entities composed of?

▪ Partial identifier of weak entity ▪ Primary key of identifying relation (strong entity)

Referential Integrity Delete Rules

▪ Restrict - don't allow delete of "parent" side if related rows exist in "dependent" side ▪ Cascade - automatically delete "dependent" side rows that correspond with the "parent" side row to be deleted ▪ Set-to-Null - set the foreign key in the dependent side to null if deleting from the parent side → not allowed for weak entities

Why is this relation poorly designed one?

- Deletion anomaly: if an order were to be deleted the corresponding data would deleted as well if they are not present in other orders - Modification anomaly: if you need to modify an information related to product 4, for example, then you would need to manually edit all rows with product 4.

Requirements for a table to qualify as a relation

- It must have a unique name. - Every attribute value must be atomic (not multivalued, not composite). - Every row must be unique (can't have two rows with exactly the same values for all their fields). - Attributes (columns) in tables must have unique names. - The order of the columns must be irrelevant. - The order of the rows must be irrelevant.

First Normal Form

- No multivalued attributes - Every attribute value is atomic - All relations are in 1st Normal Form

Mapping Ternary (and n-ary) Relationships

- One relation for each entity and one for the associative entity - Associative entity has foreign keys to each entity in the relationship

Mapping Supertype/Subtype Relationships

- One relation for supertype and for each subtype - Supertype attributes (including identifier and subtype discriminator) go into supertype relation - Subtype attributes go into each subtype; primary key of supertype relation also becomes primary key of subtype relation - 1:1 relationship established between supertype and each subtype, with supertype as primary table

Many Unary Relationships: Many-to-Many

- One relation for the entity type - One relation for an associative relation in which the primary key has two attributes, both taken from the primary key of the entity

Mapping Unary Relationships

- One-to-Many - Many-to-Many

Mapping Binary Relationships

- One-to-Many: Primary key on the one side becomes a foreign key on the many side - Many-to-Many: Create a new relation with the primary keys of the two entities as its primary key - One-to-One: Primary key on the mandatory side becomes a foreign key on the optional side

Issues to watch out for when merging entities from different E-R models:

- Synonyms - Homonyms - Transitive Dependencies - Supertype/subtype relationships

Third Normal Form

2NF PLUS no transitive dependencies (functional dependencies on non-primary-key attributes)dependencies on non-primary-key attributes)

Domain Constraints

Allowable values for an attribute (includes data types and restrictions on values)

When mapping regular entities to relations, what is the rule for multivalued attributes?

Become a separate relation with a foreign key taken from the superior entity

Mapping Weak Entities

Becomes a separate relation with a foreign key taken from the superior entity

Data manipulation

Powerful SQL operations for retrieving and modifying data

Mapping Unary Relationships: One-to-Many

Recursive foreign key in the same relation

Well-Structured Relations

Relations that contain minimal data redundancy and allow users to insert, delete, and update rows without causing data inconsistencies

Data Structure

Tables (relations), rows, columns

Example of Insertion Anomaly

can't enter a new employee without having the employee take a class (or at least empty fields of class information)

Modification Anomaly

changing data in a row forces changes to other rows because of duplication

What is the update anomaly for this relation?

changing the price of product ID 4 requires update in multiple records

Mapping Associative Entities With Identifier Not Assigned

• Default primary key for the association relation is composed of the primary keys of the two entities (as in M:N relationship)

Mapping Associative Entities With Identifier Assigned

• It is natural and familiar to end-users • Default identifier may not be unique

Data Normalization

• Primarily a tool to validate and improve a logical design so that it satisfies certain constraints that avoid unnecessary duplication of data • The process of decomposing relations with anomalies to produce smaller, well-structured relations