INFORMATION SYSTEMS 2A
PRESENTED BY PROF TANIA PRINSLOO
WEEK 3
Database Systems:
Design,
Implementation, and
Management, 14e
Module 3: The Relational
Database Model
Footnote Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023 Cengage. All Rights 2
Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. 2
A Logical View of Data
The relational data model allows
the designer to focus on the The relational model enables you
logical representation of the data to view data logically rather than
and its relationships, rather than physically
on the physical storage details
Logical simplicity tends to yield
This section explores the details
simple and effective database
of table structure and contents
design methodologies
Footnote 3
Tables and Their Characteristics (1 of 3)
A table is perceived as 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 A relation whose contents can be permanently saved for future use
representation of a logical relation
The domain is the set of allowable values for an attribute
The primary key (PK) is an attribute or combination of attributes that uniquely identifies any given row
The characteristics of a relational table are summarized in Table 3.1 on the following slide
Footnote 4
Tables and Their Characteristics (2 of 3)
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
Footnote 5
Tables and Their Characteristics (3 of 3)
Footnote 6
Keys
A key consists of one or more attributes that determine other
attributes
Keys are important because they They are also used to establish
are used to ensure that each row relationships among tables and to ensure
the integrity of the data
in a table is uniquely identifiable
Footnote 7
Dependencies
The role of a key is based on the
concept of determination,
which is the state in which Functional dependence means
The attribute whose value
knowing the value of one that the value of one or more
determines another is called the
attribute helps to determine the attributes determines the value
determinant or the key
value of another of one or more other attributes
• Example: revenue – cost = profit
Full functional dependence is
used to refer to functional
The attribute whose value is
dependencies in which the entire
determined by the other is called
collection of attributes in the
the dependent
determinant is necessary for the
relationship
Footnote 8
Types of Keys (1 of 3)
A composite key is a key that is composed of more than one attribute
An attribute that is part of a key is called a key attribute
A superkey is a key that can uniquely identify any row in the table
A candidate key is a minimal superkey; that is, a key that does not contain a subset of attributes that is itself a
superkey
Entity integrity is the condition in which each row in the table has its own known, unique identity
A null is the absence of any data value, and it is never allowed in any part of a primary key
Footnote 9
Types of Keys (2 of 3)
An unknown attribute value
A null could represent any of the A known, but missing, attribute value
following: A “not applicable” condition
A foreign key (FK) is a primary key of one table that has been placed in another
table
Referential integrity is 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
A secondary key is a key that is used strictly for data retrieval purposes and does
not require a functional dependency
Footnote 10
Types of Keys (3 of 3)
Footnote Figure 3.2 An Example of a Simple Relational Database 11
Integrity Rules (1 of 2)
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).
Footnote 12
Integrity Rules (2 of 2)
Footnote Figure 3.3 An Illustration of Integrity Rules 13
The Data Dictionary and the System Catalog
The data dictionary provides a detailed It is sometimes described as “the database
designer’s database” because it records the
description of all tables in the database design decisions about tables and their
created by the user and designer structures
The system catalog is a detailed system data dictionary that describes all
objects within the database
Homonym – same name is used to label
Homonyms and synonyms must be different attributes
avoided to lessen confusion Synonym – different names are used to
describe the same attribute
Footnote 14
Relationships within the Relational Database (1 of 6)
• The one-to-many (1:M) relationship is the norm for relational databases
• In the one-to-one (1:1) relationship, one entity can be related to only one other entity and vice versa
• The many-to-many (M:N) relationship can be implemented by creating a new entity in 1:M
relationships with the original entities
• A composite entity (i.e., bridge or associative entity) helps avoid problems inherent to M:N
relationships
• It includes the primary keys of tables to be linked
Footnote 15
Relationships within the Relational Database (2 of 6)
• Figure 3.19 The 1:M Relationship between COURSE
and CLASS
• Figure 3.20 The Implemented 1:M Relationship between
COURSE and CLASS
Footnote 16
Relationships within the Relational Database (3 of 6)
• Figure 3.21 The 1:1 Relationship between PROFESSOR
and DEPARTMENT
• Figure 3.22 The Implemented 1:1 Relationship between
PROFESSOR and DEPARTMENT
Footnote 17
Relationships within the Relational Database (4 of 6)
• Figure 3.23 The ERM’s M:N Relationship between
STUDENT and CLASS
• Figure 3.24 The Wrong Implementation of the M:N
Relationship between STUDENT and CLASS
Footnote 18
Relationships within the Relational Database (5 of 6)
• Figure 3.25 Converting the M:N Relationship into Two
1:M Relationships
• Because the ENROLL table links two tables, it is also
called a linking table
• Figure 3.26 Changing the M:N Relationships to Two 1:M
Relationships
Footnote 19
Relationships within the Relational Database (6 of 6)
• Figure 3.27 The Expanded ER Model
• Figure 3.28 The Relational Diagram for the
Ch03_TinyCollege Database
Footnote 20
Data Redundancy Revisited (1 of 2)
The relational database facilitates
Recall that foreign keys are common attributes that
control of data redundancies are shared by tables
through use of foreign keys
Sometimes data redundancy must be increased to
Data redundancy should be make the database serve crucial information
controlled except in the following purposes
Sometimes data redundancy exists to preserve the
circumstances: historical accuracy of data
Footnote 21
Data Redundancy Revisited (2 of 2)
• Figure 3.29 A Small Invoicing System
• Figure 3.30 The Relational Diagram for the Invoicing
System
Footnote 22
Indexes (1 of 2)
An index is an orderly arrangement to logically access rows in a table
The index key is the index’s reference point that leads to data location identified by the key
In a unique index, the index key can have only one pointer value associated with it
A table can have many indexes, but each index is associated with only one table
The index key can have multiple attributes
Footnote 23
Indexes (2 of 2)
Figure 3.31 Components of an Index
Footnote 24
Cobb’s Relational Database Rules (1 of 2)
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 the database;
on the relational model 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.
Footnote 25
Cobb’s Relational Database Rules (2 of 2)
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.
Footnote 26
Questions???
Footnote 27
