Chapter 17 - Methodology-Logical Database Design for the Relational Model

mandatory participation on one side of 1:1 relationship

"Optional" is parent, primary key should be posted to child (mandatory)

Mapping strong entity types

"Regular" entities: - Simple - Composite - Multi-valued

One-to-one (1:1) binary relationship types

- mandatory participation on both sides of 1:1 relationship - mandatory participation on one side of 1:1 relationship - optional participation on both sides of 1:1 relationship.

Types of integrity constraints

- required data (when an entity must contain valid data) - attribute domain constraints (legal values) - multiplicity (a branch has many staff and a member of staff works at a single branch) -entity integrity (primary key can not be null) - referential integrity (if the foreign key contains a value, that value must refer to an existing tuple in the parent relation (candidate key)) - general constraints

Relation structure types

- strong entity types - weak entity types - one-to-many (1:*) binary relationship types - one-to-one (1:1) binary relationship types - one-to-one (1:1) recursive relationship types - superclass/subclass relationship types - many-to-many (*:*) binary relationship types - complex relationship types - multi-valued attributes.

Deriving relations for logical data model results in

A relational schema

Three phases of database design:

Conceptual Logical Physical

This phase of database design is primarily concerned with developing the ER diagram

Conceptual (ER = Entity Relationship)

The logical design is said to have this with the conceptual design....

Correspondence with E-R Model Relations (tables) correspond with entity types and with many-to-many relationship types Rows correspond with entity instances and with many-to-many relationship instances Columns correspond with attributes NOTE: The word relation (in relational database) is NOT the same as the word relationship (in E-R model)

logical data model includes

ER diagram(s), relational schema, and supporting documentation such as the data dictionary, which is produced throughout the development of the model.

functional dependencies

Indicate important relationships between the attributes of a relation. It is those functional dependencies and the primary key for each relation that are used in the process of normalization.

Relation anomalies

Insertion (adding new rows forces user to create duplicate date) Deletion (deleting rows may cause a loss of data that would be needed for other future rows) Modification (changing data in a row forces changes to other rows because of duplication) *Normalization is a method to remove anomalies

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.

This phase of database design is primarily concerned transforming the ER diagram into relations

Logical

asterisk:asterisk is

Many to many

(1:*)

One-to-many "One" is parent, primary key should be posted to child

optional participation on both sides of 1:1 relationship

Parent/child assignment arbitrary

global logical data model

Represents the data requirements for all user views

local logical data model

Represents the data requirements of one or more but not all user views of a database (Step 2) Build a logical data model based on the conceptual data model for each user view of the enterprise, and then validate the model using the technique of normalization and against the required transactions.

Steps in Building Logical Data Model

Step 2.1 Derive relations for logical data model Step 2.2 Validate relations using normalization Step 2.3 Validate relations against user transactions Step 2.4 Check integrity constraints Step 2.5 Review logical data model with user Step 2.6 Merge logical data models into global data model (optional step) Step 2.7 Check for future growth

Integrity constraints are

The constraints that we wish to impose in order to protect the database from becoming incomplete, inaccurate, or inconsistent

Logical database design

The process of constructing a model of the data used in an enterprise based on a specific data model but independent of a particular DBMS and other physical considerations.

relational schema validation

The structure of the relational schema is validated: - using normalization - then checked to ensure that the relations are capable of supporting the transactions given in the users' requirements specification - next check whether all important integrity constraints are represented by the logical data model - then validated by the users to ensure that they consider the model to be a true representation of the data requirements of the enterprise

The purpose of normalization is

To ensure that the set of relations has a minimal yet sufficient number of attributes necessary to support the data requirements of the enterprise with minimal redundancy The objective of this step is to ensure that each relation derived from the logical data model is in at least Boyce-Codd Normal Form (BCNF). [Most just validate to 3rd normal form, 1 normal - remove repeating groups, 2nd normal - remove partial dependencies, 3rd normal - remove transitive dependencies]

Foreign Key

an attribute (possible composite) in a relation of a database that serves as the primary key of another relation in the same database

mandatory participation on both sides of 1:1 relationship

combine the entities involved into one relation and choose one of the primary keys of the original entities to be the primary key of the new relation, while the other (if one exists) is used as an alternate key

database design methodology main phases:

conceptual, logical, and physical database design

Relational modeling uses these to maintain relationships

primary keys and foreign keys Primary keys are typically the unique identifier noted on the conceptual model Foreign keys are the primary key of another entity to which an entity has a relationship Composite keys are primary keys that are made of more than one attribute Weak entities Associative entities

relational schema

set of relations

In deciding where to post (or place) the foreign key attribute(s), we must first identify

the "parent" and "child" entities involved in the relationship