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