The Relational Database Model

1
The Relational Database Model
2
Learning Objectives

• The relational database model as a logical view
  of data
• The relational database models basic components
• entities and their attributes, and
• relationships among entities
• How entities and their attributes are organized
  into tables
• The necessity of defining keys
• What are integrity rules
• What relational database operators exist

3
Logical View of Data

• Relational Database Model history
• Proposed by Codd in 1970
• Pioneer projects such as at IBM and UC-Berkeley
  in mid-1970s
• Today, still the dominant database model
• IBM DB2, ORACLE, INFORMIX, SYBASE
• MICROSOFT Access, SQL Server
• FOXBASE, PARADOX

4
Logical View of Data

• Relational Database
• Advantages designer focuses on logical
  representation rather than physical, simple, easy
  query representation
• A database is a collection of relations, each
  represented in a table with rows and columns
• Use of table advantageous
• Structural and data independence
• Related records stored in independent tables
• Logical simplicity
• Allows for more effective design strategies

5
Logical View of Data (cont.)

• Entities and Attributes
• An entity is a person, place, event, or thing
  about which data is collected
• An entity set is a named collection of entities
  sharing common characteristics
• Attributes are characteristics of the entity
• Tables
• A table holds related entities or an entity set
• Also called relations
• Comprised of rows and columns

6
Logical View of Data (cont.)

• A table / entity set / relation is not a
  relationship as in E-R diagrams
• Attributes are also called fields

7
Table Characteristics

• Two-dimensional structure with rows and columns
• Each row (tuple) represents a single entity
• Columns represent attributes
• Row/column intersection represents single value
• Tables must have an attribute to uniquely
  identify each row, a unique key

8
Table Characteristics (cont.)

• Column values all have same data format
• Each column has range of values called attribute
  domain
• Order of the rows and columns is immaterial to
  the DBMS

9
Example Tables
Figure 2.1
10
Table Characteristics (cont.)

• Each RDBMS has its rules for table and column
  names.Example Access 2000 Table
  names lt (8 is classical)
  Column names lt 64 (10 is classical)
  Column names cannot start with digit,
  or contain special characters
  except underscore
• Each RDBMS has its rules for associating a data
  type to an attribute, but there are classical
  ones text, character, number, date, boolean

11
Table Characteristics (cont.)
12
Keys

• One or more attributes that determine other
  attributes
• Key attribute
• Composite key
• Full functional dependence from key to any other
  attribute.
• Entity integrity
• Uniqueness
• No null value in key

13
Keys

• Keys can be
• SINGLE
• COMPOSITE, composed of several key
  attributesExample STU_LNAME, STU_FNAME,
  STU_INIT, STU_PHONE ? STU_HRS, STU_CLASSfull
  functional dependence B is functionally
  dependent on a composite key A, but not on any
  subset of A

14
Keys

• The attribute B is functionally dependent on the
  attribute A if each value in column A determines
  one and only one value in column B. Notation A ?
  B (A determines B).
• Attribute A determines attribute B (B is
  functionally dependent on A) if all rows in the
  table that agree in value for attribute A must
  also agree in value for attribute B.
• Attribute B is functionally dependent on A if A
  determines B.

15
Simple Relational Database
Figure 2.2
16
Keys (cont.)

• Superkey
• One or several attributes that uniquely
  identifies each entity
• Candidate key
• Minimal superkey (no redundancies)
• Primary key
• Candidate key to uniquely identify all other
  attributes in a given row (entity integrity)
• Must contain a value (no null, or no value
  assigned)
• Secondary key
• Used only for data retrieval (need not be unique)
• Foreign key
• Values must match primary key in another table
• Represents a relationship

17
Integrity Rules

• Entity integrity
• Ensures all entities are unique
• Each entity has unique key (primary key), that
  cannot be null
• Referential integrity
• Foreign key must have null value or match primary
  key values
• Makes it impossible to delete row whose primary
  key has mandatory matching foreign key values in
  another table

18
Integrity Rules
19
Relational Database Operators

• Relational algebra determines
• table manipulations
• Key operators (minimally relational RDBMS)
• SELECT
• PROJECT
• JOIN
• Other operators
• INTERSECT
• UNION (union compatible tables)
• DIFFERENCE
• PRODUCT
• DIVIDE

20
Union

• Combines all rows

Figure 2.5
21
Intersect

• Yields rows that appear in both tables

Figure 2.6
22
Difference

• Yields rows not found in other tables

Figure 2.7
23
Product

• Yields all possible pairs from two tables

Figure 2.8
24
Select

• Yields a subset of rows based on specified
  criterion

Figure 2.9
25
Project

• Yields all values for selected attributes

Figure 2.10
26
Join

• Information from two or more tables is combined

Figure 2.11
Figure 2.14
27
Natural Join Process

• Links tables by selecting rows with common values
  in common attribute(s)
• Three-stage process
• Product creates one table
• Select yields appropriate rows
• Project yields single copy of each attribute to
  eliminate duplicate columns
• Eliminates duplicates
• Does not include rows that are unmatched

28
Other Joins

• EquiJOIN
• Links tables based on equality condition that
  compares specified columns of tables
• Does not eliminate duplicate columns
• Join criteria must be explicitly defined
• Theta JOIN
• EquiJOIN that compares specified columns of each
  table using operator other than equality one
• Outer JOIN
• Matched pairs are retained
• Unmatched values in other tables left null
• Right and left

29
Other Joins
30
Divide

• Requires user of single-column table and
  two-column table
• A value in the unshared column must be associated
  with each value in the single-column table

Figure 2.17