Chapter 5: The Relational Data Model

1
(No Transcript)
2
Part III Database Design

• Chapter 6 Logical Database Design and the
  Relational Model
• Chapter 7 Physical Database Design

3
Chapter 6 Logical Database Design and the
Relational Data Model
4
Basic Definitions

• Three components of the Relational Data Model
• Data Structure
• tables with rows and columns
• Data Manipulation
• operations used to manipulate (e.g., create,
  read, update, delete) data stored in the
  relations
• Data Integrity
• business rules that maintain the integrity of the
  data when they are manipulated

5
Relational Data Structure

• Relation a named two-dimensional table
• a set of named columns
• an arbitrary number of unnamed rows
• An attribute is a named column of the relation
• Each row of a relation corresponds to a record
  that contains attribute values for a single
  entity
• WORKER (WORKER-ID, NAME, HOURLY-RATE, SKILL-TYPE,
  SUPV-ID)

6
Keys

• Primary key a set of attributes that uniquely
  identifies each row in a relation
• Composite key more than one attribute
• Candidate key any set of attributes
• WORKER (WORKER-ID, NAME, HOURLY-RATE, SKILL-TYPE,
  SUPV-ID)

7
Foreign Keys

• Foreign keys
• a set of attributes in one relation that
  constitutes a key in some other (or possibly the
  same) relation
• used to indicate logical links
• Recursive foreign key references its own
  relation
• WORKER (WORKER-ID, NAME, HOURLY-RATE, SKILL-TYPE,
  SUPV-ID)
• Foreign Keys SKILL-TYPE REFERENCES SKILL
• SUPV-ID REFERENCES WORKER

8
Relational Database Schema

• WORKER (WORKER-ID, NAME, HOURLY-RATE, SKILL-TYPE,
  SUPV-ID)
• Foreign Keys SKILL-TYPE REFERENCES SKILL
• SUPV-ID REFERENCES WORKER
• ASSIGNMENT (WORKER-ID, BLDG-ID, START-DATE,
  NUM_DAYS)
• Foreign Keys WORKER-ID REFERENCES WORKER
• BLDG-ID REFERENCES BUILDING
• BUILDING (BLDG-ID, BLDG-ADDRESS, TYPE,
  QLTY-LEVEL, STATUS)
• SKILL (SKILL-TYPE, BONUS-RATE, HOURS-PER-WEEK)

9
Integrity Constraints

• Domain constraints the set of values that can be
  assigned data type, size, allowable values
• Entity integrity no key attribute may be null
• Referential integrity the value of a non-null
  foreign key must be an actual key value in some
  relation
• Operational constraints business rules for which
  logic must be embedded in the system

10
SQL Table Definitions

• CREATE TABLE CUSTOMER (CUSTOMER_ID
  VARCHAR(5) NOT NULL, CUSTOMER_NAME
  VARCHAR(25) NOT NULL, CUSTOMER_ADDRESS
  VARCHAR(30) NOT NULL,PRIMARY KEY
  (CUSTOMER_ID))
• CREATE TABLE ORDER (ORDER_ID CHAR(5) NOT
  NULL, ORDER_DATE DATE NOT NULL,
  CUSTOMER_ID VARCHAR(5) NOT NULL,PRIMARY KEY
  (ORDER_ID),FOREIGN KEY (CUSTOMER_ID) REFERENCES
  CUSTOMER(CUSTOMER_ID))
• CREATE TABLE ORDER_LINE (ORDER_ID CHAR(5)
  NOT NULL, PRODUCT_ID CHAR(5) NOT NULL,
  QUANTITY INT NOT NULL,PRIMARY KEY
  (ORDER_ID, PRODUCT_ID),FOREIGN KEY (ORDER_ID)
  REFERENCES ORDER(ORDER_ID),FOREIGN KEY
  (PRODUCT_ID) REFERENCES PRODUCT(PRODUCT_ID))
• CREATE TABLE PRODUCT (PRODUCT_ID CHAR(5) NOT
  NULL, PRODUCT_DESCRIPTION VARCHAR(25),
  PRODUCT_FINISH VARCHAR(12), UNIT_PRICE
  DECIMAL(8,2) NOT NULL ON_HAND INT NOT
  NULLPRIMARY_KEY (PRODUCT_ID))

11
A Well-Structured Relation

• Contains minimal redundancy and allows users to
  insert, modify, and delete without errors or
  inconsistencies
• Anomalies
• often occur when relation contains data about two
  entities
• update
• deletion
• insertion

12
Transforming EER Diagrams into Relations

• Three types of entities
• Regular independent existence, generally
  represent real-world objects
• Weak cannot exist except with an identifying
  relationship
• Associative formed from MN relationships

13
Step 1 Map Regular Entities

• Each regular entity type is transformed into a
  relation
• simple attribute ? attribute
• identifier ? primary key
• composite attribute ? simple component attributes
• multivalued attribute ? two relations
• (1) attributes of entity except multivalued
  attribute
• (2) multivalued attribute

14
Step 2 Map Weak Entities

• simple attribute ? attribute
• composite attribute ? simple component attributes
• identifier of owner ? foreign key
• identifier of weak entity identifier of owner ?
  primary key

15
Step 3 Map Binary Relationships

• 1M
• primary key attribute(s) of one side becomes
  foreign key in many side
• MN
• associative entity
• primary keys of participating entities become
  foreign key of associative entity
• 11
• primary key of mandatory side becomes foreign key
  in optional side

16
Step 4 Map Associative Entities

• No identifiers assigned default primary key is
  primary keys of participating entities (as well
  as foreign key)
• Identifier assigned use as a surrogate key
• when natural identifier exists
• when default does not uniquely identify instances
  of the associative entity

17
Step 5 Map Unary Relationships

• Recursive relationships
• 1M
• recursive foreign key, same domain as primary key
• MN
• create a new relation to represent the MN
  relationship
• primary key of new relation is primary key plus
  recursive key

18
Step 6 Map Ternary Relationships

• Create new associative relation
• default primary key is primary keys of
  participating relations
• primary keys of participating relations are also
  foreign keys

19
Step 7 Map Supertype/Subtype Relationships

• Create a separate relation for the supertype and
  each of its subtypes
• Assign the supertype relation common attributes,
  including primary key
• Assign each subtype relation attributes unique to
  that subtype and the primary key of the supertype
• Assign attribute(s) to supertype to serve as
  subtype discriminator

20
Normalization

• Normalization a formal process for deciding
  which attributes should be grouped together in a
  relation
• Minimizes data redundancy
• Increases data integrity

21
Functional Dependency and Keys

• A constraint between two attributes (or sets of
  attributes)
• A ? B means that knowing A, we also know B
• A is the determinant
• B is functionally dependent on A
• Candidate key attribute (or combination) that
  uniquely identifies a row in a relation

22
First Normal Form

• All attribute values must be atomic.
• no repeating groups (multivalued attributes)
• A table with multivalued attributes is converted
  to a relation in first normal form by extending
  the data in each column to fill cells that are
  empty because of the multivalued attributes.

23
Second Normal Form

• No nonkey attribute may be functionally dependent
  on just a part of the key.OWNER-SHARE
  (PERSON-ID, BOAT-ID, PERCENTAGE, BOAT-NAME)
• Whats the error?
• What problems could arise?
• In 2NF if
• primary key composed of only one attribute
• no nonkey attributes in the relation
• every nonkey attribute functionally dependent on
  full set of primary key attributes

24
Achieving Second Normal Form

• 1. Create a new relation using the attributes
  from the offending FD as the attributes in the
  new relation, with the determinant of those
  attributes as the key.
• 2. Remove the attribute on the right side of the
  FD from the original relation.
• 3. Repeat if necessary.
• 4. Combine relations that have the same FD.

25
Third Normal Form

• Every determinant is a key.BOAT (BOAT-ID, NAME,
  MODEL, LENGTH, BEAM, SLIP)
• Whats the error?
• What problems could arise?
• transitive dependency functional dependency
  between two or more nonkey attributes
• calculated
• lookup

26
Merging Relations

• View integration problems
• synonyms (two names for same attribute)
• homonyms (same name for two attributes)
• transitive dependencies (merge two 3NF relations)
• supertype/subtype relationships (merge two 3NF
  relations that do not represent the same entity
  even though they share same key attribute)

27
(No Transcript)