Rangkuman Chapter 5 Logical Database Design and The Relational Model

Chapter 5: Logical Database Design and the Relational Model Modern Database Management
Jeffrey A. Hoffer, Mary B. Prescott,
Fred R. McFadden

Objectives Definition of terms List five properties of relations State two properties of candidate keys Define first, second, and third normal form Describe problems from merging relations Transform E-R and EER diagrams to relations Create tables with entity and relational integrity constraints Use normalization to convert anomalous tables to well-structured relations.

1. Basic Definitions

The Relational data model represents data in the form of tables. The relational model is based on mathematical theory and therefore has a solid theortical foundation. However, we need only a few simplle concepts to describe the relational model, and it is therefore easily understood and used by those unfamiliar with the underflying theory. The relational data imodel consists of the following three components (Fleming and von Halle, 1989): 

1. Data strucure Data are organized in the form of tables with rows and columns. 

2. Data manipulation Powerful operations (using the SQL. language) are used to manipulate data stored in the relations.

3. Data integrity Facilities are included to specify business rules that maintain the integrity of data when they are manipulated. We discuss data structure and data integrity in this section. Data manipulation is dis- cussed in Chapters 7, 8, and 10.

Relational Data Structure A relation is a named, two-dimensional table of data. Each relation (or table) consists of a set of named columns and an arbitrary number of unnamed rows. Relational Data Structure A relation is a named, two-dimensional table of data. Each relation (or table) consists of a set of named columns and an arbitrary number of unnamed rows. Each row of a relation corresponds to a record that contains data (attribute) values for a single entity.

Table consists of rows (records), and columns (attribute or field)

Requirements for a table to qualify as a relation:

1. It must have a unique name.

2. Every attribute value must be atomic (not multivalued, not composite)

3. Every row must be unique (can’t have two rows with exactly the same values for all their fields)

4. Attributes (columns) in tables must have unique names

5. The order of the columns must be irrelevant

6. The order of the rows must be irrelevant

2.  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.

3. Key Fields Keys are special fields that serve two main purposes:

Primary keys are unique identifiers of the relation in question. Examples include employee numbers, social security numbers, etc. This is how we can guarantee that all rows are unique

Foreign keys 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)

Keys can be simple (a single field) or composite (more than one field)

Keys usually are used as indexes to speed up the response to user queries (More on this in Ch. 6)

4. Foreign Key (implements 1:N relationship between customer and order)

Primary Key

Foreign Key (implements 1:N relationship between customer and order)

Combined, these are a composite primary key (uniquely identifies the order line)…individually they are foreign keys (implement M:N relationship between order and product).

5. Integrity Constraints

Referential Integrity – rule that 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.

6. Transforming EER Diagrams into Relations

1.) Mapping Regular Entities to Relations

2.) Simple attributes: E-R attributes map directly onto the relation

3.) Composite attributes: Use only their simple, component attributes

4.) Multivalued Attribute - Becomes a separate relation with a foreign key taken from the superior entity

7. Figure 5-8 Mapping a regular entity

(a) CUSTOMER entity type with simple attributes

(b) CUSTOMER relation

8. Figure 5-9 Mapping a composite attribute

(a) CUSTOMER entity type with composite attribute

(b) CUSTOMER relation with address detail

9. Figure 5-10 Mapping an entity with a multivalued attribute

(a) Multivalued attribute becomes a separate relation with foreign key

(b) One–to–many relationship between original entity and new relation

10. Transforming EER Diagrams into Relations

Mapping Weak Entities

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

Primary key composed of:

- Partial identifier of weak entity

- Primary key of identifying relation (strong entity)

www.ubpkarawang.ac.id