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
