ICT200
INTRODUCTION TO DATABASE
MANAGEMENT SYSTEM
By :
Miss Noorfadzilah Arifin
Chapter 3 The Relational Database Model
Tables and Their Characteristics
2
Table: two-dimensional structure
composed of rows and columns
Contains group of related entities = an
entity set
✓ Terms entity set and table are often used
interchangeably
Tables and Their Characteristics
3
Table also called a relation because the
relational model’s creator, Codd, used the
term relation as a synonym for table
Think of a table as a persistent relation:
✓ A relation whose contents can be permanently
saved for future use
Tables and Their Characteristics
4
Tables and Their Characteristics
5
Keys
6
Consists of one or more attributes that
determine other attributes
Primary key (PK) is an attribute (or a
combination of attributes) that uniquely
identifies any given entity (row)
Key’s role is based on a concept known as
determination
✓ If you know the value of attribute A, you can look
up (determine) the value of attribute B
Keys
7
A ➔B
✓ A determines B
✓ B is functionally dependent on A
A ➔ B,C,D
✓ attribute A determines attributes B,C,D
✓ B,C,D are functionally dependent on A
A,B ➔ C
✓ attributes A,B determine attribute C
✓ attributes C is fully functionally dependent on A,B
Keys
8
Composite key
✓ Composed of more than one attribute OR multi-
attribute key
Key attribute
✓ Any attribute that is part of a key
Superkey
✓ Any key that uniquely identifies each row
Candidate key
✓ A superkey without redundancies OR a minimal
superkey
Keys
9
Foreign key (FK)
✓ An attribute whose values match primary key
values in the related table
Referential integrity
✓ FK contains a value that refers to an existing
valid row in another relation
Secondary key
✓ Key used strictly for data retrieval purposes
Keys
10
Nulls:
✓ No data entry
✓ Not permitted in primary key
✓ Should be avoided in other attributes
✓ If used improperly, can create problems because they
have many different meanings. For example, can
represent
◼ An unknown attribute value
◼ A known, but missing, attribute value
◼ A “not applicable” condition
✓ Can create problems when functions such as COUNT,
AVERAGE, and SUM are used
✓ Can create logical problems when relational tables are
linked
Keys
11
✓ Controlled redundancy make the relational db work
✓ Referential integrity means that if the foreign key contains
value that refers to an existing row in another relation
Keys
12
Keys
13
Integrity Rules
14
Integrity Rules
15
Integrity Rules
16
✓ To avoid nulls, some designers are used special codes,
known as flags which is to indicate the absence of some
value/does not yet assigned
✓ Ex: If such a flag is used, the AGENT table must contain
a dummy row with an AGENT_CODE value of -99.
✓ The AGENT table’s first record might contain the values.
The Data Dictionary and
System Catalog
17
Data dictionary
✓ Provides detailed accounting of all tables found
within the user/designer-created database
✓ Contains (at least) all the attribute names and
characteristics for each table in the system
✓ Contains metadata—data about data
✓ Sometimes described as “the database designer’s
database” because it records the design decisions
about tables and their structures
The Data Dictionary and
System Catalog
18
A Sample Data Dictionary
The Data Dictionary and
System Catalog
19
System catalog
✓ Contains metadata
✓ Detailed system data dictionary that describes all
objects within the database (ex: data about tables
name, the table’s creator and creation date, the
number of columns in each table and etc)
✓ Terms “system catalog” and “data dictionary” are
often used interchangeably
✓ Can be queried just like any user/designer-created
table
Relationships within the Relational
Database
20
1:M relationship
✓ Relational modeling ideal
✓ Should be the norm in any relational database
design
1:1 relationship
✓ Should be rare in any relational database design
M:N relationships
✓ Cannot be implemented as such in the relational
model
✓ M:N relationships can be changed into two set of
1:M relationships
Relationships within the Relational
Database
21
The 1:M Relationship
Relational database norm
Found in any database environment
Relationships within the Relational
Database
22
The 1:M Relationship
Relationships within the Relational
Database
23
The 1:M Relationship
✓ 1:M relationship is easily implemented in the relational model by putting the
primary key of the “1” side in the table of the “many” side as a foreign key
Relationships within the Relational
Database
24
The 1:1 Relationship
One entity can be related to only one other
entity, and vice versa
Sometimes means that entity components were
not defined properly
As rare as 1:1 relationships should be, certain
conditions absolutely require their use
Relationships within the Relational
Database
25
The 1:1 Relationship
Relationships within the Relational
Database
26
The M:N Relationship
More troublesome proposition in the relational
environment
Can be implemented by breaking it up to produce a
set of 1:M relationships
Can avoid problems inherent to M:N relationship
by creating a composite entity or bridge entity
Relationships within the Relational
Database
27
The M:N Relationship
Relationships within the Relational
Database
28
The M:N Relationship
Relationships within the Relational
Database
29
The M:N Relationship
Implementation of a composite entity
Yields required M:N to 1:M conversion
Composite entity table must contain at least the
primary keys of original tables
Linking table contains multiple occurrences of
the foreign key values
Additional attributes may be assigned as
needed
Relationships within the Relational
Database
30
The M:N Relationship
Relationships within the Relational
Database
31
The M:N Relationship
Relationships within the Relational
Database
32
The M:N Relationship
Data Redundancy Revisited
33
Data redundancy leads to data anomalies
✓ Such anomalies can destroy the
effectiveness of the database
Foreign keys
✓ Control data redundancies by using common
attributes shared by tables
Sometimes, data redundancy is necessary
Data Redundancy Revisited
34
Data Redundancy Revisited
35
Indexes
36
Arrangement used to logically access rows
in a table
Index key
✓ Index’s reference point
✓ Points to data location identified by the key
Unique index
✓ Index in which the index key can have only one pointer
value (row) associated with it
A table can have many indexes, but each
index is associated with only one table
Indexes
37
