SE2121 - Database Systems

Central Philippine University | Software Engineering | Iloilo City, PH 5000

RELATIONAL MODEL - represents data as a collection of interrelated relations(or two-
dimensional tables). It is a logical representation of the database.

PARTS:
• Structure - or also called schema, is the definition of the relations and its
content.
• Integrity - ensures the database’s contents satisfy constraints.
• Manipulation - how to access and modify databases’ contents.

Central Philippine University | Software Engineering | Iloilo City, PH 5000

Relational Model ATTRIBUTE

RELATION NAME TUPLE

Relation = set of tuples

Actual DB COLUMN

TABLE NAME ROW/

RECORD

Table = list of rows

Central Philippine University | Software Engineering | Iloilo City, PH 5000

 ATTRIBUTE

RELATION NAME TUPLE

Relation -> is a set of tuples (no duplicates)

Tuples -> represents a single entity(a set of domains or attribute values) in a relation.
• values are normally atomic or scalar
• special value NULL is a member of every domain
n-ary relation = table with n columns
Cardinality -> number of tuples in a relation
Degree -> number of attributes
Central Philippine University | Software Engineering | Iloilo City, PH 5000
TUPLE SCHEMA

INSTANCE

Schema -> describes the relation and it is fixed

Instance -> data stored at a given point in time (set of tuples) and it is changing
Schema customer(cust_last_name, cust_first_name, cust_birthday, cust_address, cust_email, cust_contact_num)
customer(cust_last_name:string, cust_first_name:string, ... , cust_contact_num:integer)

Tuple (“Garcia”, “Russel”, “1995-06-23”, “Bacolod City, PH”, “russ@[Link]”, 639324563789)

Instance {(“Garcia”, “Russel”, “1995-06-23”, “Bacolod City, PH”, “russ@[Link]”, 639324563789),

(“Reyes”, “Joy”, “1996-11-02”, “Makati City, PH”, “joy@[Link]”, 639223456792)}

Central Philippine University | Software Engineering | Iloilo City, PH 5000

 Database Constraints – are rules enforced on a table column to meet data integrity.
 COMPOSITE KEY – a key composed of more than one attribute
 ex. cust_last_name + cust_first_name
 SUPER KEY – an attribute or composite key (s) that can uniquely identify a tuple
 ex. cust_last_name + cust_first_name, cust_birthday, cust_last_name, cust_email , etc…
 CANDIDATE KEY – filtered super key
 ex. cust_last_name + cust_first_name, cust_last_name, cust_email
 PRIMARY KEY – the most important candidate key that can hold the uniqueness of a tuple
 ex. [cust_email] or add a column referring to an id such as [cust_id]

Central Philippine University | Software Engineering | Iloilo City, PH 5000

 Some DBMSs, by default, create an internal primary key if none is defined.

id

…..

Central Philippine University | Software Engineering | Iloilo City, PH 5000

 Foreign key – defines that an attribute from one relation has to map to a tuple in
another relation.

CUSTOMER(name, birthday, id)

PRODUCT(name, price, id)

TRANSACTION(customer_id, product_id, product_price, quantity, coupon_used, amount_paid)

Central Philippine University | Software Engineering | Iloilo City, PH 5000

Properties of a Primary Key
 Has to be a candidate key, unique, not NULL, and doesn’t change

 Why? Each row will have a 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.

Central Philippine University | Software Engineering | Iloilo City, PH 5000

Properties of a Foreign Key
 A foreign key may have either a null entry, as long as
 it is not a part of its table’s primary key
 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)

 Why? It is possible for an attribute not to have a corresponding value, but it will be
impossible to have an invalid entry.
 Example: A customer might not yet have an assigned sales representative (number), but it
will be impossible to have an invalid sales representative (number).

Central Philippine University | Software Engineering | Iloilo City, PH 5000

 Characteristics of a Relational Model
 originally defined with SET semantics (no duplicates)
 Attributes are atomic and scalar (INTEGER, FLOAT, …)
 Tables are flat
 Order of tuples does not matter

Central Philippine University | Software Engineering | Iloilo City, PH 5000

 Posted on Canvas

Central Philippine University | Software Engineering | Iloilo City, PH 5000

 The data in relational tables can be manipulated in two(2) ways:
 Procedural (Relational Algebra) – defines how to obtain the resulting data set

 Non-Procedural (Relational Calculus) – defines what information the data set must contain

Note: Relational Algebra and Relational Calculus are logically equivalent: for any algebraic expression, there is an
equivalent expression in the calculus, and vice versa. This result is known as Codd's theorem.

Central Philippine University | Software Engineering | Iloilo City, PH 5000

 Relational Set Operators
 SELECT (denoted by Greek letter sigma, σ)
 PROJECT (denoted by Greek letter pi, π)
 RENAME (denoted by Greek letter rho, ρ)
 UNION (denoted by the symbol, ∪)
 INTERSECT (denoted by the symbol, ∩)
 DIFFERENCE (denoted by the minus symbol, -)
 PRODUCT (denoted by the multiplication symbol, x)
 JOIN (denoted by the symbol, ⋈)
 DIVIDE (denoted by the division symbol, ÷)

Note: The relational operators have the property of closure; that is, the use of relational
algebra operators on existing relations (tables) produces new relations.

Central Philippine University | Software Engineering | Iloilo City, PH 5000

SELECT, also known as RESTRICT, is referred to as a unary operator because it only
uses one table as input. It yields values for all rows found in the table that satisfy a
given condition.

σ is the predicate
written as, σp(r) r stands for relation
p is prepositional logic

For example, to SELECT all of the rows in the CUSTOMER table that have the value
“10010” in the CUS_CODE attribute, you would write the following:
σcus_code = 10010 (customer)

Central Philippine University | Software Engineering | Iloilo City, PH 5000

PROJECT yields all values for selected attributes. It is also a unary operator,
accepting only one table as input. PROJECT will return only the attributes requested,
in the order in which they are requested.

π is the predicate
written as, πp(r) r stands for relation
p is prepositional logic

For example, to PROJECT the CUS_FNAME and CUS_LNAME attributes in the

CUSTOMER table, you would write the following:
πcus_fname, cus_lname (customer)

Central Philippine University | Software Engineering | Iloilo City, PH 5000

RENAME is a unary operation used for renaming attributes of a relation or a certain
output relation.

ρ is the predicate
written as, ρp(r)
r stands for relation
p is prepositional logic

For example:
ρnewname,newbranch(πname,branch( student)) change attribute name
ρlatestcustomer (σcus_code = 10010 (customer)) change relation name
ρnewname,newbranch/newstudent(πname,branch( student)) change both

Central Philippine University | Software Engineering | Iloilo City, PH 5000

UNION combines all rows from two tables, excluding duplicate rows. To be used in
the UNION, the tables must have the same attribute characteristics; in other words,
the columns and domains must be compatible.

written as, relationA ∪ relationB

For example, assume the SUPPLIER and VENDOR tables are not union-compatible. If
you wish to produce a listing of all vendor and supplier names, then you can PROJECT
the names from each table and then perform a UNION with them.

πsupplier_name (supplier) ∪ πvendor_name (vendor)

Central Philippine University | Software Engineering | Iloilo City, PH 5000

UNION, example:

Central Philippine University | Software Engineering | Iloilo City, PH 5000

INTERSECT yields only the rows that appear in both tables. As with UNION, the tables must
be union-compatible to yield valid results. Also, for the rows to be considered the same in
both tables and appear in the result of the INTERSECT, the entire rows must be exact
duplicates.

written as, relationA ∩ relationB

For example, again assume the SUPPLIER and VENDOR tables are not union-compatible. If
you wish to produce a listing of any vendor and supplier names that are the same in both
tables, then you can PROJECT the names from each table and then perform an INTERSECT
with them.

πsupplier_name (supplier) ∩ πvendor_name (vendor)

Central Philippine University | Software Engineering | Iloilo City, PH 5000

INTERSECT, example:

Central Philippine University | Software Engineering | Iloilo City, PH 5000

DIFFERENCE yields all rows in one table that are not found in the other table; that is,
it subtracts one table from the other. As with UNION, the tables must be union-
compatible to yield valid results.

written as, relationA - relationB

For example, assume the SUPPLIER and VENDOR tables are not union-compatible. To
produce a list of any supplier names that do not appear as vendor names, use a
DIFFERENCE operator.

πsupplier_name (supplier) - πvendor_name (vendor)

Central Philippine University | Software Engineering | Iloilo City, PH 5000

DIFFERENCE, example:

Central Philippine University | Software Engineering | Iloilo City, PH 5000

PRODUCT yields all possible pairs of rows from two tables—also known as the
Cartesian product.

written as, relationA x relationB

If Relation A has 6 tuples and the Table B has 3 tuples, the PRODUCT yields a list
composed of 6 × 3 = 18 tuples.

Central Philippine University | Software Engineering | Iloilo City, PH 5000

PRODUCT, example:

Central Philippine University | Software Engineering | Iloilo City, PH 5000

JOIN or NATURAL JOIN allows information to be intelligently combined from two or
more tables.
written as, relationA ⋈ relationB
For example, let us JOIN Customer and Agent.

Central Philippine University | Software Engineering | Iloilo City, PH 5000

JOIN, example:

Central Philippine University | Software Engineering | Iloilo City, PH 5000

JOIN, example:

Also note that, as described above, JOIN is not a fundamental relational algebra operator. It can be derived
from other operators as follows:
πcus_code, cus_lname, cus_zip, agent_code, agent_phone (σcustomer.agent_code = agent.agent_code (customer × agent))

Central Philippine University | Software Engineering | Iloilo City, PH 5000

JOIN has various types:
- Inner Joins
- Theta Join
- Equijoin
- Natural Join
- Outer Joins
- Left Outer Join
- Right Outer Join
- Full Outer Join

Central Philippine University | Software Engineering | Iloilo City, PH 5000

EQUIJOIN links tables on the basis of an equality condition that compares specified
columns of each table. The outcome of the equijoin does not eliminate duplicate
columns, and the condition or criterion used to join the tables must be explicitly
defined.
If any other comparison operator is used, the join is called a THETA JOIN.
written as,

Note: This can also be classified as CONDITIONAL JOIN. You can combine conditions
by using (^) denoting AND operator and (v) denoting OR operator.

Central Philippine University | Software Engineering | Iloilo City, PH 5000

LEFT OUTER JOIN allows keeping all tuples in the left relation.
written as, relationA relationB

Note: If there is no matching tuple is found in right relation, then the attributes of
right relation in the join result are filled with null values.

Central Philippine University | Software Engineering | Iloilo City, PH 5000

LEFT OUTER JOIN, example:

Central Philippine University | Software Engineering | Iloilo City, PH 5000

LEFT OUTER JOIN, example:

Central Philippine University | Software Engineering | Iloilo City, PH 5000

RIGHT OUTER JOIN allows keeping all tuples in the right relation.
written as, relationA relationB

Note: If there is no matching tuple is found in left relation, then the attributes of
left relation in the join result are filled with null values.

Central Philippine University | Software Engineering | Iloilo City, PH 5000

RIGHT OUTER JOIN, example:

Central Philippine University | Software Engineering | Iloilo City, PH 5000

RIGHT OUTER JOIN, example:

Central Philippine University | Software Engineering | Iloilo City, PH 5000

FULL OUTER JOIN allows keeping all tuples from both relations, irrespective of the
matching condition.
written as, relationA relationB

Central Philippine University | Software Engineering | Iloilo City, PH 5000

DIVIDE operator is used to answer questions about one set of data being associated
with all values of data in another set of data. 「written as, relationA ÷ relationB」
For example,

Central Philippine University | Software Engineering | Iloilo City, PH 5000

Relational Calculus has two(2) variations:

- Tuple Relational Calculus

- Domain Relational Calculus

Central Philippine University | Software Engineering | Iloilo City, PH 5000