Summary Data Management - Relational Model: Part 2

Relatonal Models
Data Modelling:
- Development of a conceptual data model
o i.e. capturing the semantcs of the real orld, ithout being concerned about
(database) sof areerelated issues.
So: technology independent
o e.g. the ER model
- In a later stadium, the ER model can be transformed into a relatonal database model
and eventually in a relatonal database system (e.g. MySql, MS SQL Server,
Oracle Database or MS Access)




Overview Data Models & Terminology




1

,Note:
- If you get it right in the ER model, you get it right throughout the entre o to the DB.

Relational model
- Origin
o Edgard F. Codd, A relatonal model of data for large shared data banks,
Communicatons of the ACM, 17 0.
- The relatonal model is a formal model ith a sound and solid mathematcal
underpinning
o Based on set theory
o Relatonal algebra, relatonal calculus
- No graphical representaton
o Table representaton
o Not suitable for requirements collecton (in contrast to (E)ER model)
- But: model implemented in various commercial (relatonal) database sof are
o Oracle, Microsof, IBM, …
- End user needs to kno syntax to retrieve informaton from relatonal database
o SQL Query language

Note:
Use keys (foreign and ____) to translate the ER model to a relatonal model to help the DB
understand the ER model.
Employee: The employee number is a unique identier for each employee. Which then you
can have name, address, etc.
Unique number is called the primary key. For the tuple/instance, it is the unique identier
for the table in a database.
Department: Use a department number as the unique identier as a primary key. Each has a
name/locaton, etc.
DBMS can understand the primary keys.
Have tables and use foreign keys to kno hich employee is orks in hich department.
If you have a primary key in another table, it is called a foreign key.
So, you can put the foreign key of a department in the employee table to link the t o tables
together.

Relations and tupless tables and rows:
- A relatio should be interpreted as a table.
- Each tuple corresponds to a riw in the table.
- Attributes (types) can be seen as the cilumo oames.
- Each attribute vaalue corresponds to a single cell.
- Table names and column names are used to help in interpretng the meaning of the
values in each ro .




2

,Ex: Table Representation

PRODN PRODNAME AVAILABLE_QUANTITY
R

014 Ricon fax 10 136

007 Siemens Fx 12 17

02 Canon CX 30 e
Note:
You are a tuple in the ERP of kuleuven.
A domain speciies the range of admissible values for an attribute type

Relationss tupless domains:
- A relaton speciies the object for hich e ant to collect data (similar to an entty
type in ER)
- A relaton is deined as a set of tuples
o A relaton (e.g. EMPLOYEE) has as many tuples as the number of employees in a
company at a certain moment in tme.
o Tuples are uoique and have oi irder
- Mathematcally, a relaton is a subset of the Cartesian product of diferent sets:
o We call these sets dimaios
o A domain speciies the raoge if admissible vaalues fir ao attribute type
o Example: domain prodnr: integer bet een 1…7777
o A domain can be atimic (e.g. domain prodnr) or cimpisite (e.g. domain address)
Note:
BILLOFMATERIAL is a document that has all of the materials combined that are used to
produce a product.

Attributes types
- Every attribute type of a relaton is deined according to a speciic domain.
o Relatio PRODUCT
attribute type product_number dimaio product_number
o Relatio BILLOFMATERIAL
attribute type major_product_number dimaio product_number
attribute type minor_product_number dimaio product_number
attribute type quantty dimaio quantty
- Attribute value
o The elements of a domain hich occur in the relaton are the attribute vaalues of
an attribute type.
o These values are a subset of the corresponding domain.
- For each attribute type, each tuple has exactly ioe attribute value
o NULL values represent the values of attributes that may be unkno n or may not
apply to a tuple

3

,Note:
The value of the primary key must allo you to identfy one tuple hich relates to one
instance in a table. (Not more than one)
Each part of the candidate key can be more than one tuple. Slide 10

Candidate keys and primary keys:
- Candidate key: a minimal determinant of a relaton
o i.e. a (set of) attribute types of a relaton, for hich
 The value of the candidate key can be used to uniquely identfy each tuple in
the relaton
 While each part of the candidate key can identfy more than one tuple.
o relatio PRODUCT(product_number, product_name, available_quantty, …)
Caodidate key: product_number
Caodidate key: product_name
o orelatio PO_LINE (po_number, product_number, ordered_quantty, …)
Caodidate key: (po_number, product_number)
- Primary key:
o One of the candidate keys is designated as the primary key of the relaton
o Other candidate keys then become the alternatve key
o Example:
relatio PRODUCT(product_number, product_name, available_quantty, …)
Candidate key: product_number, product_name
Primary key: product_number
Alternatve key: product_name
- Entty integrity constraint
o Because the primary key is used to identfy individual tuples in a relaton, no
primary key value can be NULL
Cartesian product by example




Foreign keys
- i.e. a (set of) attribute type(s) in a relaton R is a foreign key of R that refers to a
candidate key in relaton S if the attributes of the foreign key in R and candidate key in S
have the same domains. The attributes of the foreign key are said to refer to the
relaton S.



4

, - In the relatonal model, relatonships bet een enttes are represented using foreign
keys.
- Example
(S) DEPARTMENT(dnr, dname, dlocaton, ...)


(R) EMPLOYEE(ssn, name, dor)


- Referental integrity constraint
o The value of the foreign key in a tuple R is
 equal to the value of the candidate key in a tuple of S, or
 NULL
-i.e. a (set of) attribute type(s) in a relaton R is a foreign key of R that refers to a candidate
key in relaton S if the attributes of the foreign key in R and candidate key in S have the
same domains. The attributes of the foreign key are said to refer to relaton S
-In the relatonal model, relatonships bet een enttes are represented using foreign keys.
-Example
(S) DEPARTMENT(dnr, dname, dlocaton, ...)
(R) EMPLOYEE(ssn, name, dor)
e Referental integrity constraint
e The value of the foreign key in a tuple R is
o equal to the value of the candidate key in a tuple of S, or
o NULL

Foreign key example




Note:
In set theory (and, usually, in other parts
of mathematcs), a Cartesian product is
a mathematcal operaton that returns
a set (or product set or simply product) from multple
sets. That is, for sets A and B, the Cartesian

5