Logical Database DesignRelational Model

Logical Database Design

• Logical database design: process of transforming conceptual data model into a logical data model.

• Relational data model is the most commonly used in contemporary applications.

Referential Integrity

When referential integrity is enforced• value of foreign key field must exist in primary

key of primary table, else the foreign key value must be null

• can’t delete a record from the primary table if matching records exist in related table

• can’t change primary key value in the primary key table if that record has related records

Transforming E-R Diagrams to Relational Model -- Entities

• Each regular entity type in an ER diagram is transformed into a relation

• For weak entities, create a new relation, but in addition to the simple attributes, must include primary key of owner relation as a foreign key attribute.

Transforming E-R Diagrams to Relational Model -- Attributes

• Each simple attribute of the entity type becomes an attribute of the relation (the identifying attribute becomes the primary key)

• Only simple component attributes of a composite attribute are included

• When the entity type contains a multivalued attribute, two new relations are created

General Rule for Mapping Binary One-to_Many

• Create a relation (table) for each entity.

• Have the primary key of the table on the one side appear as a foreign key in the table on the many side.

ERD: Binary One-Many

CUSTOMER LOAN

ISSUED

Logical Schema: Binary One-Many

Customer_ID Name Address Phone#

CUSTOMER

LOAN

Loan_ID Date Amount Interest_rate Term Type Customer_ID

Access Implementation: Binary One-to-Many

ERD: Binary Many-to-Many

EMPLOYEE PROJECT

BillingRate

ASSIGNED_TO

Logical Schema: Binary Many-to-Many

Employee_ID Name Address Birthdate

EMPLOYEE

Project_ID Project_Name Start_Date

PROJECT

Employee_ID Project_ID Billing_Rate

ASSIGNED_TO

Logical Schema; Text

EMPLOYEE(Employee_ID, Name, Address, Birthdate)

ASSIGNED_TO(Employee_ID, Project_ID, Billing_Rate)

PROJECT(Project_ID, Project_Name, Start_Date)

Access Database

Access Database

Access Database

Access Implementation: Binary Many-to-Many

Logical Database DesignNormalization

Problem with Unnormalized Design

• No satisfactory primary key -- proj_num contains nulls, emp_num is duplicated

• Too easy for data entry to be inconsistent• Data redundancy gives rise to the following anomalies:

– Update anomalies– Insertion anomalies– Deletion anomalies

Normalization

• Process of assigning attributes to entities• Reduces data redundancy• Helps eliminate data anomalies that result from

redundancies

Normal Forms

• Normalization works through a series of stages called normal forms (1NF, 2NF, 3NF are most frequently encountered in business designs)

• From a structural point of view, 2NF is better than 1NF, and 3NF is better than 2NF

• Generally, the higher the normal form, the more joins are required to produce a specified output and the more slowly the system responds to end-user demands. Therefore, sometimes we may have to denormalize some portions of a database design in order to meet performance requirements.

Functional Dependency

• Normalization is based on the analysis of functional dependencies. A functional dependency is a constraint between two attributes, or two sets of attributes.

• For any relation R, attribute B is functionally dependent on attribute A if, for every valid instance of A, the value of A uniquely determines the value of B. This is notated as: A B

• Each normal form represents a state of a relation that results from applying simple rules regarding functional dependencies to that relation.

Examples of Functional Dependency

ISBN Title, First_Author_name

VIN Make, Model, Color

SSN Name, Address, Birthdate

Emp_ID, Course_Name Date_Completed

Candidate Keys

A candidate key is an attribute, or combination of attributes, that uniquely identifies a row in a relation. It must satisfy the following properties:

• Unique identification. For every row, the value of the key must uniquely identify that row. This implies that each nonkey attribute is functionally dependent on that key.

• Nonredundancy. No attribute in the key can be deleted without destroying the property of unique identification.

First Normal Form

Remove repeating groups (multivalued attributes). A table with multivalued attributes or repeating groups is converted to a relation in first normal form by extending the data in each column to fill the cells that are empty.

Insertion Anomaly

In order to complete a row definition, a new employee must be assigned to a project. If an employee is not yet assigned, a phantom project must be created in order to complete the employee data entry.

Deletion Anomaly

If an employee quits, deletions must be made for every entry in which that emp_num appears. As such deletions are made, other vital data are lost.

For example, in our sample data, Darlene M. Smithson is the only DSS Analyst. If she leaves the company, we have to delete her in two places, and we will lose the information that a DSS Analyst is billed at $46 per hour.

Update Anomaly

Modifying data for an employee or a project requires updating many occurrences. For example, if Annelise Jones changes from Applications Designer to Database Designer, we would have to record this change in every project that Ms. Jones partakes in.

Second Normal FormA relation is in second normal form (2NF) if it is in first normal form and every

nonkey attribute is fully functionally dependent on the primary key. Thus no nonkey attribute is functionally dependent on part (but not all) of the primary key.

A relation that is in 1NF will be in 2NF if any one of the following conditions exist:• the primary key consists of only one attribute• no nonkey attributes exist in the relation• every nonkey attribute is functionally dependent on the full set of primary key

attributes

Partial Functional Dependency

A partial functional dependency is a functional dependency in which one or more nonkey attributes are functionally dependent on part (but not all) of the primary key.

Analyzing Functional Dependencies

Proj_Num Proj_Name Emp_Num Emp_Name Job_ClassChg_Hour Hours

Partial Dependency Partial Dependency

Full Dependency

Transitive Dependency

Decomposing 1NF Relation to Convert to 2NF

Proj_Num Proj_Name

Emp_Num Emp_Name Job_ClassChg_Hour

Proj_Num Emp_Num Hours

Primary key consists of only one attribute

Every nonkey attribute is functionallydependent on the full set of primary keyattributes

PROJECT RELATION

Primary key consists of only one attributeEMPLOYEE RELATION

ASSIGN RELATION

Third Normal Form

A relation is in third normal form (3NF) if it is in second normal form and no transitive dependencies exist.

Transitive DependencyA transitive dependency involves a functional dependency between two (or

more) nonkey attributes. For example, in the Employee relation, CHG_HOUR is functionally

dependent on JOB_CLASS. This causes the following problems• A new job class and its associated rate cannot be entered until an

employee has been assigned to the job class• If we delete an employee who is the only member of a particular job class,

we lose information about that job class and its associated rate

Decomposing 2NF Employee Relation to Convert to 3NF

Emp_Num Emp_Name Job_ClassChg_Hour

EMPLOYEE RELATION Transitive Dependency

Emp_Num Emp_Name Job_Class

EMPLOYEE RELATION

Job_Class Chg_Hour

JOB RELATION

Emp_Num Emp_Name Job_Code

EMPLOYEE RELATION

Job_Code Job_Class

JOB RELATION

Chg_Hour

Putting it back together: