IST 331/504 DB Design,
Implementation and
Mgmt.
CHAPTER 3: THE RELATIONAL DATABASE MODEL
FALL 2025
A Logical View of Data
The relational data model allows the designer to focus on the logical
representation of the data and its relationships, rather than on the
physical storage details
The relational model enables you to view data logically rather than
physically
Logical simplicity tends to yield simple and effective database design
methodologies
Tables
Table: a two-dimensional structure composed of
rows and columns
A table is also called a relation
You can think of a table as a persistent representation
of a logical relation
A relation whose contents can be permanently saved for
future use
Domain: the set of allowable values for an attribute
Primary Key (PK):an attribute or combination of
attributes that uniquely identifies any given row
Characteristics of a Relational Table
Table 3.1 Characteristics of a Relational Table
1 A table is perceived as a two-dimensional structure composed of rows and columns
2 Each table row (tuple) represents a single entity occurrence within the entity set
3 Each table column represents an attribute, and each column has a distinct name
4 Each intersection of a row and column represents a single data value
5 All values in a column must conform to the same data format
6 Each column has a specific range of values known as the attribute domain
7 The order of the rows and columns is immaterial to the DBMS
8 Each table must have an attribute or combination of attributes that uniquely identifies each row
Keys
Keys consist of one or more attributes that
determine other attributes
Ensure that each row in a table is uniquely
identifiable
Establish relationships among tables and to ensure
the integrity of the data
Primary key: attribute or combination of attributes
that uniquely identifies a row.
Dependencies
Determination
State in which knowing the value of one attribute makes it possible to
determine the value of another
Establishes the role of a key
Based on the relationships among the attributes
Example: revenue – cost = profit
Functional dependence: value of one or more attributes determines the
value of one or more other attributes
Determinant: attribute whose value determines another
Dependent: attribute whose value is determined by the other attribute
Full functional dependence: entire collection of attributes in the determinant
is necessary for the relationship
Types of Keys
Several different types of keys are used in the relational model
Composite key: key that is composed of more than one
attribute
Key attribute: attribute that is a part of a key
Superkey: key that can uniquely identify any row in the
table
Candidate key: minimal superkey
Entity integrity: condition in which each row in the table
has its own unique identity
All of the values in the primary key must be unique
No key attribute in the primary key can contain a null
Nulls and Other Keys
Null: the absence of any data value, and it is never allowed in any part of a
primary key
A null could represent any of the following:
An unknown attribute value
A known, but missing, attribute value
A “not applicable” condition
Foreign key(FK): primary key of one table that has been placed into
another table to create a common attribute
Referential integrity: a condition by which a dependent table’s foreign
key entry must have either a null entry or a matching entry in the
primary key of the related table
Secondary key: key used strictly for data retrieval purposes and does
not require a functional dependency
Relational Model and Keys
Figure 3.2 An Example of
a Simple Relational
Database
Types of Keys
Key Type Definition
Superkey An attribute or combination of attributes that uniquely identifies
each row in a table.
Candidate key A minimal (irreducible) superkey; a superkey that does not contain a
subset of attributes that is itself a superkey.
Primary key A candidate key selected to uniquely identify all other attribute
values in any given row; cannot contain null entries.
Foreign key An attribute or combination of attributes in one table whose values
must either match the primary key in another table or be null.
Secondary key An attribute or combination of attributes used strictly for data
retrieval purposes.
Checkpoint #1
What is the difference between a database and a table?
Integrity Rules
Relational database integrity rules are very important to good
database design
Relational database management systems (RDBMSs): enforce
integrity rules automatically
Much safer to make sure the application design conforms to entity
and referential integrity rules
Handling Nulls
Flags: special codes used to indicate the absence of some value
Constraints
NOT NULL constraint: placed on a column to ensure that every row
in the table has a value for that column
UNIQUE constraint: restriction placed on a column to ensure that no
duplicate values exist for that column
Entity and Referential Integrity
Table 3.4 Integrity Rules
Entity Integrity Description
Requirement All primary key entries are unique, and no part of a primary key may be null.
Purpose Each row will have a known, unique identity, and foreign key values can properly reference primary key values.
Example No invoice can have a duplicate number, nor can it be null; in short, all invoices are uniquely identified by their invoice
number.
Referential Description
Integrity
Requirement A foreign key may have either a null entry, as long as it’s not part of its table’s primary key, or an entry that matches
the primary key value in a table to which it is related (every non-null foreign key value must reference an existing
primary key value).
Purpose The purpose is to ensure that every reference by a foreign key is a valid reference to the related primary key. It is
possible for an attribute not to have a corresponding value, but it will be impossible to have an invalid entry; the
enforcement of the referential integrity rule makes it impossible to delete a row in one table whose primary key has
mandatory matching foreign key values in another table.
Example A customer might not yet have an assigned sales representative (number), but it will be impossible to have an invalid
sales representative (number).
Illustration of
Integrity Rules
Figure 3.3 An Illustration
of Integrity Rules
Relational Algebra
Relational algebra: a set of mathematical
principles that form the basis for
manipulating relational table contents
The following are the eight main
functions:
SELECT
PROJECT
JOIN
INTERSECT
UNION
DIFFERENCE
PRODUCT
DIVIDE
Relvar
A relation is the data that you see in your tables
Relvar: a variable that holds a relation
It is a container (variable) for holding a relation data, not the relation itself
The term is short for relation variable
Closure: use of relational algebra operators on existing relations produces
new relations
A relvar has the following two parts:
The heading contains the names of the attributes
The body contains the relation
Relational Set Operators: SELECT
SELECT:
Unary
operator that
yields a
horizontal
subset of a
table
Also known as
RESTRICT
Relational Set Operators: PROJECT
PROJECT:
Unary
operator that
yields a
vertical
subset of a
table
Relational Set Operators: UNION
UNION
Combines all rows from two tables, excluding duplicate rows
Union-compatible: tables share the same number of columns, and their
corresponding columns share compatible domains
Relational Set Operator: INTERSECT
INTERSECT
Yields only the rows that appear in both tables
Tables must be union-compatible to yield valid results
Relational Set Operators: DIFFERENCE
Difference
Yields all rows in one table that are not found in the other table
Tables must be union-compatible to yield valid results
Relational Set Operators: PRODUCT
Product : yields all possible pairs of rows from two tables
Relational Set Operators: JOIN Tables
JOIN allows information to be intelligently combined from two or more
tables
Relational Set Operators: NATURAL JOIN
Natural JOIN, Step 1:
PRODUCT
natural JOIN: links
tables by selecting
only the rows with
common values in
their common
attribute(s)
Relational Set Operators: JOINS
JOINS allow information to be intelligently combined from two or more tables
NATURAL JOIN: links tables by selecting only the rows with common values in their
common attribute
EQUIJOIN: links tables on the basis of an equality condition that compares specified
columns of each table
THETA JOIN: links tables using an inequality comparison operator
INNER JOIN: only returns matched records from the tables that are being joined
OUTER JOIN: matched pairs are retained and unmatched values in the other table
are left null
LEFT OUTER JOIN: yields all of the rows in the first table, including those that
do not have a matching value in the second table
RIGHT OUTER JOIN: yields all of the rows in the second table, including those
that do not have matching values in the first table
Relational Set Operator: DIVIDE
DIVIDE
► Uses one double-column
table as the dividend and
one single-column table
as the divisor
► Output is a single column
that contains all values
from the second column
of the dividend that are
associated with every
row in the divisor
Checkpoint #2
Which relational algebra
operators can be applied to a
pair of tables that are not
union-compatible?
Data Dictionary and System Catalog
Data dictionary: description of all tables in the database
created by the user and designer
It is sometimes described as “the database designer’s
database” because it records the design decisions about
tables and their structures
System catalog: system data dictionary that describes all
objects within the database
Homonyms and synonyms must be avoided to lessen confusion
Homonym: same name is used to label different attributes
Synonym: different names are used to describe the same
attribute
Relationships within the Relational DB
One-to-many (1:M) : Norm for relational databases
One-to-one (1:1) : One entity can be related to only one
other entity and vice versa
Many-to-many (M:N)
Implemented by creating a new entity in 1:M relationships
with the original entities
Composite entity (i.e., bridge or associative entity):
helps avoid problems inherent to M:N relationships
Includes the primary keys of tables to be linked
Relationships within the Relational DB:
(1:M)
The 1:M Relationship between COURSE
and CLASS
One-to-many (1:M) : Norm for
relational databases
Implemented 1:M Relationship
between PAINTER and PAINTING
Relationships within the Relational DB:
(M:N)
ERM’s M:N Relationship
between STUDENT and CLASS
Wrong Implementation of the
M:N Relationship between
STUDENT and CLASS
Relationships within the Relational DB:
Converting M:N to 1:M
Many-to-Many (M:N)
Implemented by creating a new entity in 1:M relationships
with the original entities
Converting the M:N Relationship into Two 1:M
Relationships
Because the ENROLL table links two tables, it is also called a
linking table
Changing the M:N Relationships to Two 1:M Relationships
Relationships within the Relational
DB
The Relational Diagram
Expanded for the Ch03_TinyCollege
ER Model Database
Data Redundancy Revisited
The relational database facilitates control of data redundancies
through use of foreign keys
foreign keys are common attributes that are shared by
tables
Data redundancy should be controlled except in the following
circumstances:
Sometimes data redundancy must be increased to make the
database serve crucial information purposes
Sometimes data redundancy exists to preserve the historical
accuracy of data
Data Redundancy Diagrams
The Relational Diagram for
the Invoicing System
A Small Invoicing
System
Indexes
Index: an orderly arrangement to logically access rows in a table
Index key: index’s reference point that leads to data location identified
by the key
Unique index: the index key can have only one pointer value
associated with it
Pointer: a special kind of variable which are meant to stores addresses
only, instead of values(integers, decimal numbers, etc.)
A table can have many indexes, but each index is associated with only
one table
The index key can have multiple attributes
Components of an Index
Codd’s Relational Database Rules (1-7)
Table 13.8 Dr. Codd’s 12 Relational Database
Rules
Rule Rule Name Description
1 Information All information in a relational database must be logically represented as column values
in rows within tables.
2 Guaranteed access Every value in a table is guaranteed to be accessible through a combination of table
name, primary key value, and column name.
3 Systematic treatment of nulls Nulls must be represented and treated in a systematic way, independent of data type.
4 Dynamic online catalog based The metadata must be stored and managed as ordinary data—that is, in tables within
on the relational model the database; such data must be available to authorized users using the standard
database relational language.
5 Comprehensive data The relational database may support many languages; however, it must
sublanguage support one well-defined, declarative language as well as data definition,
view definition, data manipulation (interactive and by program), integrity
constraints, authorization, and transaction management (begin, commit,
and rollback).
6 View updating Any view that is theoretically updatable must be updatable through the
system.
7 High-level insert, update, and delete The database must support set-level inserts, updates, and deletes.
Codd’s Relational Database Rules (8 -13)
Table 13.8 Dr. Codd’s 12 Relational Database
Rules
Rule Rule Name Description
8 Physical data independence Application programs and ad hoc facilities are logically unaffected when physical
access methods or storage structures are changed.
9 Logical data independence Application programs and ad hoc facilities are logically unaffected when changes are
made to the table structures that preserve the original table values (changing order of
columns or inserting columns).
10 Integrity independence All relational integrity constraints must be definable in the relational language and
stored in the system catalog, not at the application level.
11 Distribution independence The end users and application programs are unaware of and unaffected by the data
location (distributed vs. local databases).
12 Nonsubversion If the system supports low-level access to the data, users must not be allowed to
bypass the integrity rules of the database.
13 Rule zero All preceding rules are based on the notion that to be considered relational, a database
must use its relational facilities exclusively for management.
Checkpoint #3
Explainwhy the data dictionary is
sometimes called "the database
designer's database.“
Summary
Tables are the basic building blocks of a relational database
Keys are central to the use of relational tables
Each table row must have a primary key
Although tables are independent, they can be linked by common attributes
The relational model supports several relational algebra functions
A relational database performs much of the data manipulation work behind
the scenes
Once you know the basics of relational databases, you can concentrate on
design
