REL ATIONAL

DATABASE
MODEL
 TABLE

/mnlinan
CHARACTERISTICS OF RELATIONAL TABLE

/mnlinan
RELATIONAL DATABASE

/mnlinan
KEYS
• Each row in a table must be uniquely identifiable
• Key: one or more attributes that determine other
attributes
– Key’s role is based on determination
• If you know the value of attribute A, you can determine the value of
attribute B
STU_NUM → STU_LNAME

/mnlinan
RELATIONAL DATABASE
KEYS

/mnlinan
KEYS
• NULL values
– No data entry
– Not permitted in primary key
– Can represent
• An unknown attribute value
• A known, but missing, attribute value
• A “not applicable” condition

/mnlinan
INTEGRITY RULES

/mnlinan
INTEGRITY RULES
• To avoid nulls, some designers use special codes,
known as flags, to indicate the absence of some
value.
• Flags are implemented by designers to trigger a
required response, alert end users to specified
conditions, or encode values. Flags may be used to
prevent nulls by bringing attention to the absence of a
value in a table.

/mnlinan
INTEGRITY RULES

/mnlinan
RELATIONAL ALGEBRA
• Relational algebra defines theoretical way of manipulating table
contents using relational operators.
• Relational Set Operators
• SELECT
• PROJECT
• JOIN
• INTERSECT
• UNION
• DIFFERENCE
• PRODUCT
• DIVIDE

/mnlinan
SELECT OPERATION
• Yields values for all rows found in a table
• Can be used to list either all row values or it can yield
only those row values that match a specified criterion
• Yields a horizontal subset of a table

/mnlinan
SELECT OPERATION
• Select operation is denoted by
 <selection condition> (R)
where the symbol  (sigma) is used to denote the select operator, and
the selection condition is a Boolean expression specified on the
attributes of relation R
• Example:
 price<2.00(product) - determine product with price < 2.00

 p_code=311452(product) - select the product with product code equal t0 311452

/mnlinan
SELECT OPERATIONS

: product

/mnlinan
PROJECT OPERATIONS
• Yields all values for selected attributes
• Yields a vertical subset of a table

/mnlinan
PROJECT OPERATIONS
• The general form of the project operation is
<attribute list>(R)
where  (pi) is the symbol used to represent the project operation and
<attribute list> is the desired list of attributes from the attributes of
relation R.
• Example:
 price (product) - Display the prices of all products

 p_code, price(product ) - Display product code, price of all products

/mnlinan
PROJECT OPERATIONS

: product

/mnlinan
 EXAMPLE
Given the following table, write a relational algebra for the
following:

1. Display the booth product. 3. Select price of booth product greater than 1.25

 booth_product (booth)  booth_price>1.25 (booth)

2. Display the machine product. 4. Select price of machine product between 1 and 2

 (machine)
 machine_product (machine)
machine_price>=1.00 and machine_porducr= <2

/mnlinan
UNION OPERATIONS
• Combines all rows from two tables, excluding duplicate
rows
• Tables must have the same attribute characteristics.

• The result of this operation, denoted by R  S, is a relation that includes

all tuples that are either in R or in S or in both R and S. Duplicate tuples
are eliminated.
• Example

Product1  Product2

/mnlinan
UNION OPERATIONS

product1 product2

/mnlinan
INTERSECT OPERATIONS
• Yields only the rows that appear in both tables

• The result of this operation, denoted by R  S, is a relation that

includes all tuples that are in both R and S. The two operands must
be "type compatible“
• Example
student  employee

/mnlinan
INTERSECT OPERATIONS

student employee

/mnlinan
DIFFERENCE OPERATIONS
• Yields all rows in one table not found in the other
table—that is, it subtracts one table from the other
• The result of this operation, denoted by R - S, is a relation that
includes all tuples that are in R but not in S. The two operands
must be "type compatible”.
• Example
student-employee

/mnlinan
DIFFERENCE OPERATIONS

student employee

/mnlinan
 EXAMPLE
Given the following table, write a relational algebra for the
following:

Combine products in both table 3. Display product that is in Booth but not in Machine

Booth  Machine Booth - Machine

2. Display products common to both table.

Booth  Machine

/mnlinan
 PRODUCT
• Yields all possible pairs of rows from two tables
– Also known as the Cartesian product

/mnlinan
 PRODUCT
• This operation is used to combine tuples from two relations in
a combinatorial fashion. In general, the result of R(A 1, A2, . . .,
An) x S(B1, B2, . . ., Bm) is a relation Q with degree n + m
attributes Q(A1, A2, . . ., An, B1, B2, . . ., Bm), in that order. The
resulting relation Q has one tuple for each combination of
tuples—one from R and one from S.
• Hence, if R has nR tuples (denoted as |R| = n R ), and S has nS
tuples, then
| R x S | will have nR * nS tuples.
• The two operands do NOT have to be "type compatible”

/mnlinan
PRODUCT

product supplier

/mnlinan
 JOIN
• Allows us to combine information from two or more
tables
– Natural Join
– Equijoin
– Theta Join
– Outer Join
• Left Outer Join
• Right Outer Join

/mnlinan
 JOIN

/mnlinan
 NATURAL JOIN
• Links tables by selecting only rows with common
values in their common attribute(s)
• Result of a three-stage process:
– PRODUCT of the tables is created
– SELECT is performed on Step 1 output to yield only the rows for which
the AGENT_CODE values are equal
• Common column(s) are called join column(s)
– PROJECT is performed on Step 2 results to yield a single copy of each
attribute, thereby eliminating duplicate columns

/mnlinan
NATURAL JOIN, STEP 1:
PRODUCT

/mnlinan
NATURAL JOIN, STEP 2:
SELECT

/mnlinan
NATURAL JOIN, STEP 3:
PROJECT

/mnlinan
NATURAL JOIN
• Final outcome yields table that
– Does not include unmatched pairs
– Provides only copies of matches
• If no match is made between the table rows,
– The new table does not include the unmatched row

/mnlinan
OTHER FORMS OF JOIN
• Equijoin
– Links tables on the basis of an equality condition that
compares specified columns of each table
– Outcome does not eliminate duplicate columns
– Condition or criterion to join tables must be explicitly
defined
– Takes its name from the equality comparison operator (=)
used in the condition
• Theta join
– If any other comparison operator is used

/mnlinan
OUTER JOIN
• Matched pairs are retained and any unmatched values
in other table are left null
– Left outer join
– Right outer join

/mnlinan
LEFT OUTER JOIN

/mnlinan
RIGHT OUTER JOIN

/mnlinan
DIVIDE
• DIVIDE requires the use of one single-column table
and one two-column table

/mnlinan
 DIVIDE

/mnlinan
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

/mnlinan
DATA DICTIONARY

/mnlinan
SYSTEM CATALOG
• Contains metadata
• Detailed system data dictionary that describes all
objects within the database
• Can be queried just like any user/designer-created
table

/mnlinan
RELATIONSHIP WITHIN RELATIONAL DATABASES

• 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 1:M relationships

/mnlinan
1:M RELATIONSHIP
• Relational database norm
• Found in any database environment

/mnlinan
1:M RELATIONSHIP

/mnlinan
1:M RELATIONSHIP

/mnlinan
1:1 RELATIONSHIP
• One entity can be related to only one other entity, and
vice versa
• Often means that entity components were not defined
properly
• Could indicate that two entities actually belong in the
same table
• Sometimes 1:1 relationships are appropriate

/mnlinan
1:1 RELATIONSHIP

/mnlinan
1:1 RELATIONSHIP

/mnlinan
M:N RELATIONSHIP
• 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

/mnlinan
M:N RELATIONSHIP

/mnlinan
M:N RELATIONSHIP

/mnlinan
M:N RELATIONSHIP

/mnlinan
 EXAMPLE
Given the following table, draw the relational diagran

/mnlinan
EXAMPLE
EMPLOYEE BENEFIT
EMP_CODE EMP_CODE
EMP_LNAME PLAN_CODE
JOB_CODE

JOB PLAN
JOB_CODE PLAN_CODE
JOB_DESCRIPTIO PLAN_DESCRIPTIO
N N

/mnlinan