Ch 4 Logical Database Design

Domain Constraints

Allowable values for an attribute (includes data types and restrictions on values, e.g., NOT NULL)

Simple attributes

E-R attributes map directly onto the relation

Homonyms

attributes with same name but different meanings

Modification Anomaly

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

Deletion Anomaly

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

Third Normal Form

• 2NF PLUS no transitive dependencies (functional dependencies on nonprimary-key attributes) • Note: This is called _____, because the primary key is a determinant for another attribute, which in turn is a determinant for a third • Solution: Non-key determinant with transitive dependencies go into a new table; non-key determinant becomes primary key in the new table and stays as foreign key in the old table

Relational Data Model

• It is a database model that presents data in 2-D tables using *common data to link tables.* • Tables and nothing but tables; connections used data called foreign keys - not pointers • Non-programmers could navigate through the data

First Normal Form

• No multivalued attributes. • Every attribute value is atomic. • All relations are in 1st Normal form.

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 • Notice: Normally used if you start from a table without considering the ER rules.

Transitive Dependency

A condition in which an attribute is dependent on another attribute that is not part of the primary key.

partial dependency

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

Entity Integrity

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

Conceptual data modeling

The process of transforming the conceptual data model into a logical data model - one that is consistent and compatible with a specific type of database technology. Is about understanding the organization ad creating stable database structures -- correctly expressing the requirements in a technical language

Functional Dependency

The value of one attribute (the determinant) determines the value of another attribute

Primary keys

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

2 requirements to be a relation

Unique rows No multivalued attributes

Composite attributes

Use only their simple, component attributes

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). Dashed underline

Enterprise key

A primary key whose value is unique across all relations.

Well-Structured Relation

A relation that contains minimal data redundancy and allows users to insert, delete, and update rows without causing data inconsistencies • Goal is to avoid anomalies • General rule of thumb: A relation should not pertain to more than one entity type.

Synonyms

two or more attributes with different names but same meaning

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 (can still have transitive dependencies) • Solution: For each partial dependency, create a new table that includes the partial key and the attributes dependent on that part of the key. Each new table has a primary key that is a subset of the original composite key.

Candidate Key

• A unique identifier. One of the ______ will become the primary key • E.g., perhaps there is both credit card number and SS# in a table...in this case both are _______. • Each non-key field is functionally dependent on every _____

Relation

• a named, two-dimensional table of data. • Consists of rows (records) and columns (attribute or field) • Note: All ______ are in 1st Normal form. • Correspond with entity types and M:N relationship types

View Integration

Combining entities from multiple ER models into common relations

Columns

Correspond with attributes

Rows

Correspond with entity instances and M:N relationship instances

Data integrity

Mechanisms for implementing business rules that maintain integrity of manipulated data

Insertion Anomaly

adding new rows forces user to create duplicate data

Multivalued Attribute

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

Data manipulation

Powerful SQL operations for retrieving and modifying data

Referential Integrity

Rules that maintain consistency between the rows of two related tables. Drawn via curved arrows from dependent to parent table • 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.) • For example: 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)

Data structure

Tables (relations), rows, columns

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.