Chapter 5: Logical Database Design and the Relational Model - PowerPoint PPT Presentation

1 / 29
About This Presentation
Title:

Chapter 5: Logical Database Design and the Relational Model

Description:

State two properties of candidate keys. Define first, second, and third normal form ... Relationship between tables are defined through the use of foreign keys. ... – PowerPoint PPT presentation

Number of Views:26
Avg rating:3.0/5.0
Slides: 30
Provided by: miche244
Category:

less

Transcript and Presenter's Notes

Title: Chapter 5: Logical Database Design and the Relational Model


1
Chapter 5Logical Database Design and the
Relational Model
2
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

3
Logical database design
  • Transforming the conceptual data model
    (understanding the organization) into a logical
    data model which is compatible with a database
    technology (creating stable data structures)
  • Our emphasis is on relational data model
  • E-R data model is conceptual model, relational
    data model is logical model

4
The Relational Data Model history
  • Introduced in a journal article written in 1970
    by E. F. Codd, a scientist with IBMA Relational
    Model for Large Shared Data Banks'
  • Early research prototypes of relational systems
    were developed throughout the 1970s --- system R,
    Ingres
  • Commercial RDBMS emerged in the 1980s (Oracle)
    and came to dominate the database market, a
    situation that continues to the present time

5
Components of the Relational Data Model
  • 1. Data Structure - Data are organized in
    two-dimensional tables with rows and columns
  • 2. Data Manipulation - Data stored in the tables
    may be manipulated through the use of a command
    language (e.g., SQL)
  • 3. Data Integrity - Business rules may be defined
    that maintain the integrity of data when they are
    manipulated

6
Relation
  • Definition A relation is a named,
    two-dimensional table of data
  • Table consists of rows (records) and named
    columns (attribute or field)
  • Example employee1 (Emp_ID, Name, Dept_Name,
    Salary)

7
Relation
  • Requirements for a table to qualify as a
    relation
  • It must have a unique name
  • Every attribute value must be atomic (not
    multivalued)
  • Every row must be unique (cant 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
  • NOTE all relations are in 1st Normal form

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

9
Key Fields
  • Keys are special fields that serve two main
    purposes
  • Primary key is an attribute that uniquely
    identifies each row of the relation in question.
    Examples include employee numbers, social
    security numbers, etc. This is how we can
    guarantee that all rows are unique (Notation
    underline)
  • Foreign key is an attribute in a relation of a
    database that serves as the primary key of
    another relation in the same database. Used to
    represent relationship between two tables.
    (Notation dashed underline)
  • 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)

10
Figure 5-3 Schema for four relations (Pine Valley
Furniture Company)
11
Instance of a relational schemeFigure 5-4
12
Removing multivalued attributes
  • There can be no multivalued attribute in a
    relation
  • Remove multivalued attributes by adding separate
    records for each instance of multivalued data

13
Removing multivalued attributes
14
Integrity Constraints
  • Domain Constraints
  • Domain name, meaning, data type, size, and
    allowable values for an attribute.

15
Integrity Constraints
  • Entity Integrity
  • Ensure that every relation has a primary key, and
    data values for the primary key are all valid
  • Null a value that may be assigned to an
    attribute when no other value applies or when the
    applicable value is unknown.
  • No primary key attribute may be null. All primary
    key fields MUST have data
  • Action Assertions
  • Business rules. Recall from Ch. 4

16
Integrity Constraints
  • Relationship between tables are defined through
    the use of foreign keys.
  • Referential Integrityrule 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)
  • When foreign key can be null?
  • If the relationship is mandatory, foreign key can
    not be null. (an order must be placed by a
    customer)
  • If the relationship is optional, foreign key can
    be null.

17
Figure 5-5 Referential integrity constraints
(Pine Valley Furniture)
Referential integrity constraints are drawn via
arrows from dependent to parent table
18
Referential integrity
  • Delete Rules (for example, delete a customer who
    has orders)
  • Restrictdont allow delete of parent side if
    related rows exist in dependent side
  • Cascadeautomatically delete dependent side
    rows that correspond with the parent side row
    to be deleted
  • Set-to-Nullset the foreign key in the dependent
    side to null if deleting from the parent side ?
    not allowed for weak entities

19
Figure 5-6 SQL table definitions
20
Well-Structured Relations
  • A relation that contains minimal redundancy and
    allows users to insert, modify, and delete the
    rows in a table without errors or
    inconsistencies
  • Thus, such relations avoid
  • Insertion Anomalies
  • Deletion Anomalies
  • Modification Anomalies

21
A well structured relation
  • Employee1 is a well structured relation
  • Any modification to an employees data such
    as a change in salary, is confined to one
    row of the table

EMPLOYEE1
22
Is this a well structured relation?
  • EMPLOYEE2
  • EMPLOYEE2

- This table has a considerable amount of
redundancy e.g., EMP_ID, NAME, DEPT, and
SALARY appear in two separate rows for some
employees - If the salary of those employees
change, we must record this information in two
or more rows - Therefore, this is not a well
structured relation
- This table has a considerable amount of
redundancy e.g., EMP_ID, NAME, DEPT, and
SALARY appear in two separate rows for some
employees - If the salary of those employees
change, we must record this information in two
or more rows - Therefore, this is not a well
structured relation
23
Why minimize redundancies?
  • Redundancies in a table may result in errors and
    inconsistencies (called anomalies) when a user
    attempts to update the data in the table
  • Three types of anomalies
  • Insertion anomaly
  • Deletion anomaly
  • Modification anomaly

24
Anomalies
  • Insertion Anomalies
  • are experienced when we attempt to store a value
    for one attribute but cannot because the value of
    another attribute is unknown

25
Insertion anomaly
  • If we want to add a new employee to EMPLOYEE2,
    the user must supply values for EMPID and COURSE
    which are composite primary key
  • This is because the primary key values cannot be
    Null
  • In reality, employee should be able to enter
    employee data without supplying course data

26
Anomalies
  • Deletion Anomalies
  • are experienced when a value for one attribute we
    wish to keep is unexpectedly removed when a value
    for another attribute is deleted

27
Deletion anomaly
  • If the data for employee number 234 is deleted,
    we will also lose the information that this
    employee completed the course 111
  • In fact, we lose information about the course
    altogether

28
Anomalies
  • Modification Anomalies
  • are experienced when changes to multiple
    instances of an entity (rows of a table) are
    needed to effect an update to a single value of
    an attribute

29
Modification anomaly
  • Suppose that employee number 100 gets a salary
    increase, we must record this increase in each of
    the rows for that employee
  • Otherwise the data will be inconsistent
Write a Comment
User Comments (0)
About PowerShow.com