Chapter 5: The Relational Data Model

1
(No Transcript)
2
Part III Database Design

• Chapter 5 Logical Database Design and the
  Relational Model
• Chapter 6 Physical Database Design and
  Performance

3
Chapter 5 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
(No Transcript)
9
Properties of Relations

• Each relation table in a database has a unique
  name
• An entry at the intersection of each row and
  column is atomic no multivalued or composite
  attributes
• Each row is unique no two rows with same values
  for all their fields
• Each attribute column has a unique name
• The sequence of columns is insignificant
• The sequence of rows is insignificant

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

11
(No Transcript)
12
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
• Action assertions business rules for which logic
  must be embedded in the system

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

14
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
• insertion
• deletion
• update (or modification)

15
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

16
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) primary key multivalued attribute

17
Regular Entity, Simple Attributes
18
Regular Entity, Composite Attribute
19
Regular Entity, Multivalued Attribute
20
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

21
Weak Entity Diagram
22
Weak Entity Relation
NOTE the domain constraint for the foreign key
should NOT allow null value if DEPENDENT is a
weak entity
Foreign key
23
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(optional-max principle)

24
1M Diagram
25
1M Relation
26
MN Diagram
27
MN Relation
New intersection relation
28
11 Diagram
29
11 Relation
30
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

31
Associative Entity Diagram
32
Associative Entity Relation
33
Step 5 Map Unary Relationships

• Recursive relationships
• 1M
• recursive foreign key, same domain as primary key
• MN or 11
• create a new relation to represent the MN or 11
  relationship
• primary key of new relation determined using same
  rules as associative entities (cf. Step 4)

34
Unary 1M
35
Unary MN
36
Step 6 Map Ternary Relationships

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

37
Ternary Diagram
38
Ternary Relation
39
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

40
Supertype/Subtype Diagram
41
Supertype/Subtype Relation
42
Normalization

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

43
Anomalies

• Insertion cant enter a new employee without
  having the employee take a class
• Deletion if we remove employee 140, we lose
  information about the existence of a Tax Acc
  class
• Modification giving a salary increase to
  employee 100 forces us to update multiple records

44
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

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

46
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

47
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. BOAT (BOAT_ID, BOAT_NAME)
• 2. Remove the attribute on the right side of the
  FD from the original relation. OWNER_SHARE
  (PERSON_ID, BOAT_ID, PERCENTAGE)
• 3. Repeat if necessary.
• 4. Combine relations that have the same FD.

48
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

49
Achieving Third 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. BOAT_MODEL (MODEL_ID,
  LENGTH, BEAM)
• 2. Remove the attribute(s) on the right side of
  the FD from the original relation. BOAT
  (BOAT_ID, NAME, MODEL_ID, SLIP)
• 3. Repeat if necessary.
• 4. Combine relations that have the same FD.

50
MISA PresentsWashington Mutual

• Presenting Simon Blackwell.
• First Vice President and Delivery Team Lead for
  E-Commerce and Marketing Solutions, and Western
  Grad!
• Topics
• Software quality assurance
• Software project management
• Technology selection process
• Perks FREE subway subs and pop
• Location PH 104, Wednesday April 17th, 600 p.m.
• Notes Sign up on the MISA website
• http//www.cbe.wwu.edu/clubs/misa
• Washington Mutual has MIS positions available on
  its website.

51
(No Transcript)