Chapter 4: logical Database Design and the Relational Model

Mapping Supertype/Subtype Relationships Relation

1. Create one entity for the supertype, and one entity for each subtype. 2. Supertype attributes (including identifier and subtype discriminator) go into supertype relation. 3. Subtype attributes go into each subtype; primary key of supertype relation also becomes primary key of subtype relation. 4. 1:1 relationship established between supertype and each subtype, with supertype as primary table.

Three components of the relational data model:

1. Data Structure 2. Data Manipulation 3. Data Ingegrity

All relations are in

1st Normal Form

Composite Key

A primary key that consists of more than one attribute.

First Normal Form

All multivalued attributes have been removed, so there is a single value at the intersection of each row and column of the table.

Map Binary many-to-many relationships

Create a new relation with the primary keys of the two entities as its primary key, and any attributes associated with the new relation are included. 4-13

Second Normal Form

Every non-key attribute is fully functionally dependent on the primary key. Every non-key attribute must be defined by the entire key, not by only one part. No partial dependencies. Ex:// composite key exists. So primary key is only dependent on some of the attributes not all of the attributes.

Synonyms

two or more attributes that have different names but the same meaning.

Normalization is a tool to

validate and improve a logical design so that it satisfies certain constraints that avoid unnecessary duplication of data.

Partial Dependency

when a nonkey attribute is functionally dependent on part (but not all) of the primary keys

Determinants

the attribute on the left side of the arrow in a functional dependency.

However, using OLTP databases for analysis is generally not very efficient, because in order to retrieve data from multiple tables at the same time, a query containing ________ must be used.

JOINS

Normalization

the process of decomposing relations with anomalies to produce small, well-structured relations.

Denormalization

the process of intentionally combining some tables into a single table in spite of the fact that this may introduce duplicate data in some columns.

Tables can not contain______, fix problem by

Multivalued attributes. creating separate column for repeating groups. see 4.2

Entity Integrity Constraint

No primary key attribute may be null.

Most relational databases which are designed to handle a high number of reads and writes (updates and retrievals of information) are referred to as ________ (OnLine Transaction Processing) systems.

OLTP

To show foreign keys in relation

Underline with dotted line arrow to primary key

Maping Composite Attributes to Relations Ex:// CUSTOMER Customer ID (PK) bold and UD Customer Name Customer Address (customer street, customer city, customer state) Customer Postal Code

Use only their simple, component attributes. Use all of Attributes except for Customer address. (4-9)

Transitive Dependency

a functional dependency between the primary key and one or more key attributes that are dependent on the primary key via another nonkey attribute.

Relation

a named, two-dimensional table of data. Table is made up of rows (records) and columns (atrribute or field). Not all tables qualify as relations.

Enterprise key

a primary key whose value is unique across all relations.

Well-structured relationn

a relation that contains minimal redundancy and allows users to insert, modify, and delete the rows in a table without errors or inconsistencies.

Homonym

an attribute that ay have more than one meaning.

Foreign Key

are 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)

Columns correspond with

attributes

Rows correspond with

entity instances and with many-to-many relationship instances

Relations (tables) correspond with

entity types and with many-to-many relationship types

OLTP systems are very efficient for

high volume activities such as cashiering, where many items are being recorded via bar code scanners in a very short period of time.

A table is in Boyce-Codd normal form (BCNF)

if every determinant in the table is a candidate key.

By relating tables to one another, we can reduce ____________ of data and improve database performance.

redundancy

Mapping Associative Entities when identifier is assigned

1. Create three relations, one for each of the entity types and one for the associative entity. 2. Primary keys of two entities are foreign keys in associative relation. Associative primary attribute remains primary attribute. 3. add any other attributes associate with associative entity

Mapping Associative Entities when identifier is NOT assigned

1. Create three relations, one for each of the entity types and one for the associative entity. 2. The default primary key for the associative relation consists of the two primary key attributes from the other relation. 3. any other attributes attached to associative entity

Seven Cases of Transforming EE-R diagrams into Relations

1. Map Regular Entities 2. Map Weak Entities 3. Map Binary Relationships 4. Map Associative Entities 5. Map Unary Relationships 6. Map Ternary (and n-ary) Relationships 7. Map Supertype/Subtype Relationships

Map Ternary Relationships

1. One relation for each entity and one for the associative entity. 2. Associative entity has foreign keys to each entity in the relationship and then primary keys from the associative entity.

Requirements of relations: (6)

1.Every relation has a unique name. 2. Every attribute value is atomic (not multivalued, not composite) 3. Every row is unique (can't have two rows with exactly the same values for all their fields) 4. Attributes (columns) in tables have unique names 5. The order of the columns is irrelevant 6. The order of the rows is irrelevant

Map one-to-many Unary relationships

4-17 1. 1 relational table 2. Primary key is same as in table 3. Foreign key attribute added to same relation, references the primary key values in the same relation. Must have same domain as the PK.

Map Binary one-to-one relationships

Primary key on the mandatory side, becomes a foreign key on the optional side. and any other attributes link two together, are put with optional relation. 4-14.

Map Binary one-to-many relationships

Primary key on the one side becomes a foreign key on the many side.

Every relation must have a ____, in order that

Primary key, we can store and retrieve a row of data in a relation

Issues to watch out for when merging entities from different ER models:

Synonyms: Different names, same meaning. Homonyms: Same name, different meanings. Transitive Dependencies: Supertype/subtype

Functional Dependency

The value of one attribute, determines the value of another. Ex:// SSN determines the Name, address, and birthdate. So name, address, and birthdate are functionally dependent. EX:// ISBN determines title, author, publisher. Title, author, publisher all depend on ISBN.

Referential Integrity constraint

a rules that states that either each foreign key value must match a primary key value in another relation or the foreign key value must be null. Show by drawing lines from foreign key to primary key.

Modification Anomaly

if data changed in primary key, we must record the new number in each of the rows for that employee (both occurences in table) otherwise the data will be inconsistent.

Data manipulation of Relational data model

powerful operations (typically implemented using SQL language) are used to manipulate data stored in the relations

The following anomalies should be removed for a well-structed relation: (3)

1. Insert Anomaly 2. Deletion Anomaly 3. Modification Anomaly

A relation that is in first normal form will be in second normal form if any one of the following conditions apply: (3)

1. The primary key consists of only one attribute. 2. No nonkey attributes exist in the relation ( all attributes in the relation are components of the primary key) 3. Every nonkey attribute is functionally dependent on the full set of primary key attributes.

Map Many-to-Many urinary realtionships (bill-of-materials)

1. Two relations, one for the entity type and one for associative relation. 2. Associative relation has two primary key attributes, both taken from the primary key of the entity.

Rules of FK and PK

1. You can't add a record to the table with fk, unless there is a corresponding record in the table with pk 2. you can't delete a record in the table with pk if there is a record in the table with fk

Primary Key

An attribute or a combination of attributes that uniquely identifies each row in a relation. How we guaranttee that all all rows are unique.

Maping Regular Entities

E-R attributes map directly onto the realtion

How to solve 2NF:

Split up composite keys into new relations

General rule of thumb about normalization:

a table should not pertain to more than one entity type.

Null

a value that may be assigned to an attribute when no other value applies or when the applicable value is unknown.

Candidate Key

an atrribute, or combination of attributes, that uniquely identifies a row in a relation. Must be unique and nonredundant.

Domain Constraints

allowable values for an attribute.

Alias

an alternative name used for an attribute.

In order to keep our transactional databases running quickly and smoothly, we may wish to create a data warehouse. A data warehouse is a type of large database (including both current and historical data) that has been _____________ and archived.

denormalized

Each non-key field is functionally

dependent on every candidate key.

A domain definition contains:

domain name, data type, size, meaning, and allowable values/range.

Insertion Anomaly

inserting a new row, the primary key is the combination of EmpID and CourseTitle. Therefore, to insert new row, user must supply values for both EmpId and Course title. Since they are both primary key values, they cannot be null or nonexistent. Anamoly because the user should be able to enter employee data without supplying course data. Basically, user has to enter data for entity did not intend to enter info for.

Third Normal Form

no transitive dependencies, nonkey attributes are identified by only the primary key

The process of breaking tables apart and thereby reducing data redundancy is called _______________.

normalization

Data integrity of Relational data Model

the model include mechanisms to specify business rules that maintain the integrity of data when they are manipulated.

Mapping Weak Entities

For each weak entity type, create a new relation and include all of the simple attributes as attributes of this relation. Then include the primary key of the identifying relation as a foreign key attribute in this new relation. Primary key of new relation is the partial identifiers of weak entity and the primary key of identifying relation. 4-11. Partial Identifiers = composite key.

Deletion Anomaly

If have composite key and try to delete employee, will also delete the course information that he completed.

Mapping multi-valued Attributes into Relations ex:// EMPLOYEE Employee Id (PK) Employee Name Employee Address {skill}

Create two new realtions. The first realtion contains all of the attributes of the entity type except the multivalued attribute, the second relation contains two attributes that form the primary key of the second relation. The first of these attributes is the primary key from the first relation, which becomes a foregn key in the second relation. The second is the multivalued attribute. Both form primary key.

Data Structure of Relational data model

data are organized in the form of tables, with rows and columns.

If multi-value attribute contain multiple multivalued attributes,

each of them will convert to a separate relation.

Referential integrity contraints are implemented with...

foreign key to primary key references