ITM 321 database ch. 4

Normalization Hierarchy

1NF - 2NF - 3NF - BCNF (Boyce Codd normal form) - 4NF - 5NF A relation cannot be in a higher-level normal form unless it is also in all lower-level normal forms. A relation cannot be in 3NF unless it is in 2NF and 1NF. A relation cannot be in 5NF unless it is in 4NF, BCNF, 3NF, 2NF, and 1NF.

Mapping Unary One-to-Many Relationships

Create a relation for the entity. Follow the rules we've learned so far for dealing with the attributes. Then, put a second copy of the identifier into the relation. This is a foreign key that references the primary key in the same relation. The foreign key is called a recursive foreign key. Finally, think about the meaning of the recursive foreign key and give it a good name (different than the name of the primary key).

Mapping Supertype/Subtype Relationships

Create a separate relation for the supertype and for each of its subtypes. The relation for the supertype contains all of the attributes of the supertype. The relations for the subtypes contain the attributes of each subtype as well as the identifier of the supertype.

Mapping Associative Entities

Create three relations: one for each of the two regular entities and one for the associative entity. If the associative entity has an identifier, it is the primary key of the associative relation. If not, the primary keys of the other two relations make up a composite primary key in the associative relation. In either case, the primary keys of the other two relations are foreign keys in the associative relation.

Mapping Unary Many-to-Many Relationships

Create two relations: one for the entity and one for the relationship. Follow the rules we've learned for dealing with the relation for the entity. The relation for the relationship contains any attributes of the relationship as well as two copies of the identifier of the entity. (You will need to think about the meaning of this relation and give good names to the two copies of the identifier. They cannot share the same name; however, one of them can have the same name as the primary key of the relation you created for the entity.)

Characteristics of a Relational Database

Data are maintained in a tabular form (a relation). Each row of the table contains data about an entity instance. Each column of the table contains data about an attribute.

Domain Constraints

Domain constraints define the allowable values for an attribute. Examples: - Level = 'FR' OR 'SO' OR 'JR' OR 'SR' -Salary is between $20,000 and $90,000

Integrity Constraints

Domain constraints. Entity integrity. Referential integrity.

Mapping Weak Entities (cont.)

For each weak entity, create a relation and include all of the simple attributes (or simple components of composite attributes) as attributes of the relation. The primary key of this relation is the identifier of the owner relation plus the partial identifier of the weak entity. The primary key of the owner relation is a foreign key in this relation.

Mapping Regular Entities (cont.)

If a regular entity has a multivalued attribute, you will create two relations. The first relation contains all of the attributes of the entity except the multivalued attribute. The second relation contains two attributes: the entity identifier and the multivalued attribute. Give this relation an appropriate name. (The name does not appear in your conceptual data model).

Mapping Regular Entities (cont.)

If the regular entity has a composite attribute, the simple component attributes of the composite attribute become attributes of the relation. The composite attribute is not included in the relation.

Types of Logical Data Models

Network Hierarchical Relational *********** Object-oriented

Merging Relations

Normalized relations may be created from a number of separate conceptual data models. Putting these relations together is referred to as merging relations or view integration.

Sample Relation

STUDENT(Student_ID, First_Name, Last_Name, Class)

What is 1NF?

A database table is not in 1NF if it contains variable repeating fields. This is a matter of definition (if variable repeating fields exist you are not dealing with a relational database).

Foreign Key

A foreign key is an attribute (possibly composite) in a relation that serves as the primary key of another relation. A foreign key is indicated with a dashed underline. EMPLOYEE(Emp_ID, Name, Dept_Name) DEPARTMENT(Dept_Name, Location)

What is an anomaly?

A mistake that occurs during insertion, deletion, or update of data in the database. The mistake is a consequence of the design of the database. (Deletion anomaly, Insertion anomaly, Update anomaly)

Second Normal Form

A relation is in second normal form if it is in first normal form and there are no partial functional dependencies. A partial functional dependency exists when a nonkey attribute is functionally dependent on a part of the primary key.

Third Normal Form

A relation is in third normal form if it is in second normal form and no transitive dependencies exist. A transitive dependency exists when a nonkey attribute is functionally dependent on another nonkey attribute.

What is a functional dependency?

An attribute (B) is functionally dependent on another attribute (A), if the values of A uniquely determine the value of B. In other words, for every value of A, there is only value that B can have. Another way of saying this is that attribute B is functionally dependent on attribute A if it is invalid to have two rows with the same value for A but different values for B. A ---> B A + B ----> C

Primary Key

An attribute (or combination of attributes) that uniquely identifies each row in a relation. A composite key is a primary key that consists of more than one attribute. The primary key is underlined. STUDENT(Student_ID, First_Name, Last_Name, Class)

Enterprise Keys

An enterprise key is a key that is unique across the whole database (rather than unique within the relational table to which it applies). No two rows across all the relations in the database have the same value for an enterprise key.

Mapping Binary Many-to-Many Relationships

Create a relation for each of the two entities participating in the relationship. Create a third relation for the relationship between the two entities. This relation will contain the primary keys of the other two relations as well as any attributes of the relationship. Give this relation a meaningful name.

Mapping Binary One-to-One Relationships

Create a relation for each of the two entities participating in the relationship. Put the primary key of one of the entities into the relation for the other entity. If it is an optional relationship, put the primary key of the entity on the mandatory side of the relationship into the relation for the entity on the optional side of the relationship.

Mapping Binary One-to-Many Relationships

Create a relation for each of the two entities participating in the relationship. Put the primary key of the entity on the one-side in the relation that is on the many-side. This is a foreign key of the relation that is on the many-side.

Potential Problems When Merging Relations

Synonyms - two (or more) attributes may have different names but the same meaning. Homonyms - attributes with the same name on two different conceptual data models may actually have different meaning. Transitive dependencies - transitive dependencies may arise when merging relations. Supertype/subtype relationships - two relations from different conceptual data models may actually represent two different subtypes (of a supertype that is not even on either of the conceptual data models).

Referential Integrity

What happens if you attempt to enter a Dept_Name in the EMPLOYEE relation that does not exist in the DEPARTMENT relation? EMPLOYEE(Emp_ID, Name, Dept_Name) DEPARTMENT(Dept_Name, Location)

What is a deletion anomaly?

The deletion of an instance for one entity (e.g., an employee) results in the loss of information about another entity (e.g., project). ex:If Baker leaves the company and the Baker row is deleted, we lose the information about the Invest project.

What is an insertion anomaly?

The insertion of data about an instance of one entity (e.g., project) requires the insertion of data about an instance of another entity (e.g., employee). ex:If a new project is added, we cannot add a row for the new project until at least one person has been assigned to work on the project.

What is an update anomaly?

The modification of data about an entity instance requires a change to more than one row of a database table. Ex:Several rows in the table must be updated if a project gets a new manager.

Why normalize relational databases?

The objective is to structure data in ways that help to prevent problems (anomalies). Normalization is a tool for validating and improving logical designs so that problems are avoided.

Primary Key (relational table)

The primary key of a relational table must satisfy the following two properties: For every row, the value of the primary key must uniquely identify that row. (This is the property of unique identification.) If you delete any single attribute from the primary key, the property of unique identification would no longer be true.

Referential Integrity

The referential integrity rule states that a value of a foreign key in one relation must match a value of the primary key in the related relation (or be null).

Entity Integrity

The value of the primary key for an entity instance cannot be null. (A null value is a missing value.) In other words, a valid value must be entered for the primary key for every entity instance. In Access, when you define an attribute or attributes as the primary key, the "no nulls allowed" rules is automatically enforced.

Mapping Regular Entities

Transform each regular (strong) entity into a relation. Give the relation the entity's name. Each simple attribute of the entity becomes an attribute of the relation. The identifier of the entity type becomes the primary key of the relation.