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 - hot 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
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