B0B36DBS, BD6B36DBS: Database Systems

h p://[Link].mff.[Link]/~svoboda/courses/192-B0B36DBS/

Lecture 2

Rela onal Model

Mar n Svoboda
mar [Link]@[Link]

25. 2. 2020

Czech Technical University in Prague, Faculty of Electrical Engineering

 Lecture Outline
• Logical database models
▪ Basic overview
• Model-Driven Development
• Relational model
▪ Description and features
▪ Transformation of ER / UML conceptual schemas

B0B36DBS, BD6B36DBS: Database Systems | Lecture 02: Relational Model | 25. 2. 2020 2
Logical Database Models
 Layers of Database Modeling
Abstraction

• Conceptual layer
▪ Models a part of the structured real world relevant for
applications built on top of our database
• Logical layer
▪ Specifies how conceptual components (i.e. entity
types, relationship types, and their characteristics)
are represented in logical data structures that are
interpretable by machines
• Physical layer
▪ Specifies how logical database structures are
implemented in a specific technical environment
Implementation

B0B36DBS, BD6B36DBS: Database Systems | Lecture 02: Relational Model | 25. 2. 2020 4
 Logical Layer
• What are these logical structures?
▪ Formally…
‒ Sets, relations, functions, graphs, trees, …
• I.e. traditional and well-defined mathematical structures
▪ Or in a more friendly way…
‒ Tables, rows, columns, …
‒ Objects, pointers, …
‒ Collections, …
‒ …

B0B36DBS, BD6B36DBS: Database Systems | Lecture 02: Relational Model | 25. 2. 2020 5
 Logical Models
• Models based on tables
▪ Structure
‒ Rows for entities
‒ Columns for attributes
▪ Operations
‒ Selection, projection, join, …
▪ Examples
‒ Relational model
‒ … and various derived table models introduced by:
• SQL (as it is standardized)
• and particular implementations like Oracle, MySQL, …

B0B36DBS, BD6B36DBS: Database Systems | Lecture 02: Relational Model | 25. 2. 2020 6
 Logical Models
• Models based on objects
▪ Structure
‒ Objects with attributes
‒ Pointers between objects
▪ Motivation
‒ Object-oriented programming (OOP)
‒ Encapsulation, inheritance, …
▪ Operations
‒ Navigation

B0B36DBS, BD6B36DBS: Database Systems | Lecture 02: Relational Model | 25. 2. 2020 7
 Logical Models
• Models based on graphs
▪ Structure
‒ Vertices, edges, attributes
▪ Operations
‒ Traversals, pattern matching, graph algorithms
▪ Examples
‒ Network model (one of the very first database models)
‒ Resource Description Framework (RDF)
‒ Neo4j, InfiniteGraph, OrientDB, FlockDB, …

B0B36DBS, BD6B36DBS: Database Systems | Lecture 02: Relational Model | 25. 2. 2020 8
 Logical Models
• Models based on trees
▪ Structure
‒ Vertices with attributes
‒ Edges between vertices
▪ Motivation
‒ Hierarchies, categorization, semi-structured data
▪ Examples
‒ Hierarchical model (one of the very first database models)
‒ XML documents
‒ JSON documents

B0B36DBS, BD6B36DBS: Database Systems | Lecture 02: Relational Model | 25. 2. 2020 9
 Overview of Logical Models
• There are plenty of (different / similar) models
‒ The previous overview was intended just as an insight into
some of the basic ideas and models
▪ Hierarchical, network, relational, object, object-
relational, XML, key-value, document-oriented,
graph, ...
• Why so many of them?
▪ Different models are suitable in different situations
▪ Not everything is (yet) standardized,
proprietary approaches or extensions often exist

B0B36DBS, BD6B36DBS: Database Systems | Lecture 02: Relational Model | 25. 2. 2020 10
 Logical Modeling
• Step 1: Selection of the right logical model

Conceptual schema

… ? …

Relational model RDF model

XML model

• Note that…
▪ Relational model is not always the best solution
B0B36DBS, BD6B36DBS: Database Systems | Lecture 02: Relational Model | 25. 2. 2020 11
 Logical Modeling
• Step 1: Selection of the right logical model
▪ According to…
‒ Data characteristics
• True nature of real-world entities and their relationships
‒ Query possibilities
• Available access patterns, expressive power, …
‒ Intended usage
• Storage (JSON data in document-oriented databases, …)
• Exchange (XML documents sent by Web Service, …)
• Publication (RDF triples forming the Web of Data, …)
• …
‒ Identified requirements

B0B36DBS, BD6B36DBS: Database Systems | Lecture 02: Relational Model | 25. 2. 2020 12
 Logical Modeling
• Step 2: Creation of a logical schema
Conceptual schema

… …

Relational schema XML schema OWL ontology

B0B36DBS, BD6B36DBS: Database Systems | Lecture 02: Relational Model | 25. 2. 2020 13
 Logical Modeling
• Step 2: Creation of a logical schema
▪ Goal
‒ Transformation of a conceptual schema to a logical one
▪ Real-world applications often need multiple schemas
‒ Focus on different parts of the real world
‒ Serve different components of the system
‒ Even expressed in different logical models
▪ Challenge: can this be achieved automatically?
• Or at least semi-automatically?
‒ Answer: Model-Driven Development

B0B36DBS, BD6B36DBS: Database Systems | Lecture 02: Relational Model | 25. 2. 2020 14
Model-Driven Development (MDD)
 Model-Driven Development
• MDD
▪ Software development approach
‒ Executable schemas instead of executable code
• I.e. schemas that can be automatically (or at least semi-
automatically) converted to executable code
‒ Unfortunately, just in theory… recent ideas, not yet fully
applicable in practice today (lack of suitable tools)

• MDD principles can be used for database modeling as well

B0B36DBS, BD6B36DBS: Database Systems | Lecture 02: Relational Model | 25. 2. 2020 16
 Terminology
• Levels of abstraction
▪ Platform-Independent Level Conceptual
‒ Hides particular platform-specific details layer

▪ Platform-Specific Level
‒ Maps the conceptual schema (or its part)
to a given logical model
‒ Adds platform-specific details Logical
layer
▪ Code Level
‒ Expresses the schema in a selected
machine-interpretable logical language
‒ SQL, XML Schema, OWL, …

B0B36DBS, BD6B36DBS: Database Systems | Lecture 02: Relational Model | 25. 2. 2020 17
 Real-World Example
• Information System for Public Procurement
▪ [Link]
▪ There are several logical models used:
‒ Relational data model
• for data storage
‒ XML data model
• for exchanging data with information systems of public
authorities which issue public contracts
‒ RDF data model
• for publishing data on the Web of Linked Data in a machine-
readable form (at least this is a goal…)

B0B36DBS, BD6B36DBS: Database Systems | Lecture 02: Relational Model | 25. 2. 2020 18
 Real-World Example
• Platform-independent schema
class P...

+tenderingSupplier
Organization Address
0..* 0..1 1
- legalName - streetName
+awardedSupplier - streetNumber
- officialNumber
- city
0..1
Tender +contractingAuthority 1 - country

- estimatedEndDate +mainAddress 1 0..1

- offeredPrice +tenderAddress

+issuedContract 0..*
+tenderedContract +suppliedContract
Contract
0..* 0..*
- referenceNumber
- title
- description 0..1
ItemType - mainObject
- additionalObject [0..*]
- code 0..* 0..* - startDate 0..1
- title
- endDate
- estimatedPrice +lot 0..*
- agreedPrice
- actualPrice
- numberOfTenders
+parentContract 1

B0B36DBS, BD6B36DBS: Database Systems | Lecture 02: Relational Model | 25. 2. 2020 19
 Real-World Example
• Platform-specific schema: relational model
class PSM_RELATIONAL

Organization +FK_Organization_Address

«column» 0..*
* legalName: VARCHAR2(50)
* officialNumber: NUMBER(9) (addressId = addressId)
+PK_Organization
*PK organizationId: NUMBER(8) +PK_Organization
«FK» «FK»
1 *FK addressId: NUMBER(8)
1
(tenderingSupplierId = organizationId) +PK_Address 1
«FK»
+ FK_Organization_Address(NUMBER)
Address
«PK»
+FK_Tender_Organization 0..* + PK_Organization(NUMBER) «column»
«unique» streetName: VARCHAR2(50)
Tender
+ UQ_Organization_officialNumbe(NUMBER) (awardedSupplierId = organizationId) streetNumber: VARCHAR2(50)
«FK» city: VARCHAR2(50)
«column» +PK_Organization 1 country: VARCHAR2(50)
* estimatedEndDate: DATE
(awardedSupplierId = organizationId) *PK addressId: NUMBER(8)
* offeredPrice: NUMBER(9)
«FK»
*PK tenderId: NUMBER(8)
+FK_Contract_Organization 0..*
*FK tenderingSupplierId: NUMBER(8) «PK»
*FK tenderedContractId: NUMBER(8) +FK_Contract_Organization + PK_Address(NUMBER)
Contract
1 +PK_Address 1
+PK_Address
«FK» «column» 0..*
ItemType + FK_Tender_Contract(NUMBER) * referenceNumber: NUMBER(8)
(tenderAddressId = addressId)
+ FK_Tender_Organization(NUMBER) * title: VARCHAR2(50)
«column» description: CLOB «FK»
«PK» (tenderAddressId = addressId)
*PK code: NUMBER(8) * startDate: DATE
+ PK_Tender(NUMBER) «FK»
* title: VARCHAR2(50) * endDate: DATE +FK_Contract_Address
+FK_Tender_Contract 0..* * estimatedPrice: NUMBER(9)
«PK» agreedPrice: NUMBER(9) 0..*
+ PK_ItemType(NUMBER) actualPrice: NUMBER(9) +FK_Contract_Address
numberOfTenders: NUMBER(2)
+PK_Contract
+PK_ItemType 1 *PK contractId: NUMBER(8) 0..*
«FK» 1 *FK contractingAuthorityId: NUMBER(8)
(contractId = code) (tenderedContractId = contractId) FK awardedSupplierId: NUMBER(8)
«FK» *FK mainAddressId: NUMBER(8)
FK tenderAddressId: NUMBER(8)
+PK_Contract
0..* FK parentContractId: NUMBER(8)
+FK_Item_ItemType
1
Item
«FK»
+ FK_Contract_Address(NUMBER)
«column» (parentContractId = contractId)
+ FK_Contract_Address(NUMBER)
* code: NUMBER(8) +FK_Item_Contract +PK_Contract
+ FK_Contract_Contract(NUMBER) «FK»
FK contractId: NUMBER(8)
+ FK_Contract_Organization(NUMBER)
0..* «FK» 1
+ FK_Contract_Organization(NUMBER)
(contractId = contractId)
«FK» +FK_Contract_Contract
«PK»
+ FK_Item_Contract(NUMBER)
+ PK_Contract(NUMBER) 0..*
+ FK_Item_ItemType(NUMBER)
«unique»
+ UQ_Contract_referenceNumber()

B0B36DBS, BD6B36DBS: Database Systems | Lecture 02: Relational Model | 25. 2. 2020 20
 Real-World Example
• Platform-specific schema: relational model
▪ Notes to the previous UML diagram
‒ It is a UML class diagram
• But enhanced with features for modeling logical schemas in
(object-)relational model
‒ Stereotypes allow us to add specific semantics to basic
constructs (class, attribute, association), e.g.,
• <<table>> specifies that a class represents a table
• <<PK>> specifies that an attribute models a primary key
• <<FK>> specifies that an attribute/association models a
foreign key
• etc.

B0B36DBS, BD6B36DBS: Database Systems | Lecture 02: Relational Model | 25. 2. 2020 21
 Real-World Example
• Code level: SQL (snippet)
CREATE TABLE Contract (
referenceNumber NUMBER(8) NOT NULL,
title VARCHAR2(50) NOT NULL,
description CLOB,
startDate DATE NOT NULL,
endDate DATE NOT NULL,
estimatedPrice NUMBER(9) NOT NULL,
...
);

ALTER TABLE Contract ADD CONSTRAINT PK_Contract

PRIMARY KEY (contractId);
ALTER TABLE Contract ADD CONSTRAINT FK_Contract_Address
FOREIGN KEY (mainAddressId) REFERENCES Address (addressId);
...

CREATE TABLE Organization(...);

...

B0B36DBS, BD6B36DBS: Database Systems | Lecture 02: Relational Model | 25. 2. 2020 22
 Real-World Example
• Code level: SQL (snippet)
▪ The previous code was generated fully automatically
‒ from a platform-specific diagram
• It has to contain all the necessary information
‒ using a CASE tool (Computer-Aided Software Engineering)
• Which can detect errors and
• helps with the specification

B0B36DBS, BD6B36DBS: Database Systems | Lecture 02: Relational Model | 25. 2. 2020 23
 Real-World Example
• XML

B0B36DBS, BD6B36DBS: Database Systems | Lecture 02: Relational Model | 25. 2. 2020 24
Relational Model
 Relational Model
• Relational model
▪ Allows to store entities,
relationships, and their
attributes in relations
▪ Founded by E. F. Codd in 1970
• Informally…
▪ Table = collection of rows, each row represents one
entity, values of attributes are stored in columns
▪ Tables are more intuitive,
but conceal important mathematical background

B0B36DBS, BD6B36DBS: Database Systems | Lecture 02: Relational Model | 25. 2. 2020 26
 Relational Model
• Definitions and terminology
▪ Schema of a relation
‒ Description of a relational structure (everything except data)
‒ S(A1:T1, A2:T2, ..., An:Tn)
• S is a schema name
• Ai are attribute names and Ti their types (attribute domains)
• Specification of types is often omitted
‒ Example:
• Person(personalId, firstName, lastName)
▪ Schema of a relational database
‒ Set of relation schemas (+ integrity constraints, …)

B0B36DBS, BD6B36DBS: Database Systems | Lecture 02: Relational Model | 25. 2. 2020 27
 Relational Model
• Definitions and terminology for data
▪ Relation
‒ Subset of the Cartesian product of attribute domains Ti
• I.e. relation is a set
‒ Items are called tuples
▪ Relational database
‒ Set of relations

B0B36DBS, BD6B36DBS: Database Systems | Lecture 02: Relational Model | 25. 2. 2020 28
 Relational Model
• Basic requirements (or consequences?)
▪ Atomicity of attributes
‒ Only simple types can be used for domains of attributes
▪ Uniqueness of tuples
‒ Relation is a set, and so two identical tuples cannot exist
▪ Undefined order
‒ Relation is a set, and so tuples are not mutually ordered
▪ Completeness of values
‒ There are no holes in tuples, i.e. all values are specified
• However, special NULL values (well-known from relational
databases) can be added to attribute domains

B0B36DBS, BD6B36DBS: Database Systems | Lecture 02: Relational Model | 25. 2. 2020 29
 Integrity Constraints
• Identification
‒ Every tuple is identified by one or more attributes
▪ Superkey = set of such attributes
‒ Trivial and special example: all the relation attributes
▪ Key = superkey with a minimal number of attributes
‒ I.e. no attribute can be removed so that the identification
ability would still be preserved
‒ Multiple keys may exist in one relation
• They even do not need to have the same number of attributes
‒ Notation: keys are underlined
• Relation(Key, CompositeKeyPart1, CompositeKeyPart2, …)
• Note the difference between simple and composite keys
B0B36DBS, BD6B36DBS: Database Systems | Lecture 02: Relational Model | 25. 2. 2020 30
 Integrity Constraints
• Referential integrity
▪ Foreign key = set of attributes of the referencing
relation which corresponds to a (super)key of the
referenced relation
‒ It is usually not a (super)key in the referencing relation
‒ Notation
• [Link] ⊆ [Link]
• foreignKey ⊆ [Link]

B0B36DBS, BD6B36DBS: Database Systems | Lecture 02: Relational Model | 25. 2. 2020 31
 Sample Relational Database
• Schema
Course(Code, Name, …)
Schedule(Id, Event, Day, Time, …), Event ⊆ [Link]
• Data
Id Event Day Time …
1 A7B36DBS THU 11:00
2 A7B36DBS THU 12:45
Code Name …
3 A7B36DBS THU 14:30
A7B36DBS Database systems
4 A7B36XML FRI 09:15
A7B36XML XML technologies
A7B36PSI Computer networks

B0B36DBS, BD6B36DBS: Database Systems | Lecture 02: Relational Model | 25. 2. 2020 32
 Relations vs. Tables
• Tables
▪ Table header ∼ relation schema
▪ Row ∼ tuple
▪ Column ∼ attribute
• However…
▪ Tables are not sets, and so…
‒ there can be duplicate rows in tables
‒ rows in tables can be ordered
▪ I.e. SQL and existing RDBMS do not (always) follow the
formal relational model strictly

B0B36DBS, BD6B36DBS: Database Systems | Lecture 02: Relational Model | 25. 2. 2020 33
 Object vs. (Object-)Relational Model
• Relational model
▪ Data stored in flat tables
▪ Suitable for data-intensive batch operations
• Object model
▪ Data stored as graphs of objects
▪ Suitable for individual navigational access to entities
• Object-Relational model
▪ Relational model enriched by object elements
‒ Attributes may be of complex data types
‒ Methods can be defined on data types as well

B0B36DBS, BD6B36DBS: Database Systems | Lecture 02: Relational Model | 25. 2. 2020 34
Transformation of UML / ER to RM
 Conceptual Schema Transformation
• Basic idea
▪ What we have
‒ ER: entity types, attributes, identifiers, relationship types,
ISA hierarchies
‒ UML: classes, attributes, associations
▪ What we need
‒ Schemas of relations with attributes, keys, and foreign keys
▪ How to do it
‒ Classes with attributes → relation schemas
‒ Associations → separate relation schemas or together with
classes (depending on cardinalities…)

B0B36DBS, BD6B36DBS: Database Systems | Lecture 02: Relational Model | 25. 2. 2020 36
 Classes
class Class

• Class → Person

▪ Separate table -
-
personalNumber
address
- age
‒ Person(personalNumber, address, age)

▪ Artificial keys
‒ Artificially added integer identifiers
• with no correspondence in the real world
• but with several efficiency and also design advantages
• usually automatically generated and assigned
‒ Person(personId, personNumber, address, age)

B0B36DBS, BD6B36DBS: Database Systems | Lecture 02: Relational Model | 25. 2. 2020 37
 Attributes
• Multivalued attribute → class Class

Person

- personalNumber
- phone: String [1..*]
▪ Separate table
‒ Person(personalNumber)
Phone(personalNumber, phone)
[Link] ⊆ [Link]

B0B36DBS, BD6B36DBS: Database Systems | Lecture 02: Relational Model | 25. 2. 2020 38
 Attributes
• Composite attribute →

▪ Separate table
‒ Person(personalNumber)
Address(personalNumber, street, city, country)
[Link] ⊆ [Link]
▪ Sub-attributes can also be inlined
‒ But only in case of (1,1) cardinality
‒ Person(personNumber, street, city, country)

B0B36DBS, BD6B36DBS: Database Systems | Lecture 02: Relational Model | 25. 2. 2020 39
 Binary Associations
• Multiplicity (1,1):(1,1) →
class 1_1

Person Mobile

- personalNumber - serialNumber
- address 1 1 - color
- age

▪ Three tables (basic approach)

‒ Person(personalNumber, address, age)
Mobile(serialNumber, color)
Ownership(personalNumber, serialNumber)
[Link] ⊆ [Link]
[Link] ⊆ [Link]

B0B36DBS, BD6B36DBS: Database Systems | Lecture 02: Relational Model | 25. 2. 2020 40
 Binary Associations
• Multiplicity (1,1):(1,1) →
class 1_1

Person Mobile

- personalNumber - serialNumber
- address 1 1 - color
- age

▪ Single table
‒ Person(personalNumber, address, age, serialNumber, color)

B0B36DBS, BD6B36DBS: Database Systems | Lecture 02: Relational Model | 25. 2. 2020 41
 Binary Associations
• Multiplicity (1,1):(0,1) →
class 1_1

Person Mobile

- personalNumber - serialNumber
- address 0..1 1 - color
- age

▪ Two tables
‒ Person(personalNumber, address, age, serialNumber)
[Link] ⊆ [Link]
Mobile(serialNumber, color)
‒ Why not just 1 table?
• Because a mobile phone can exist independently of a person

B0B36DBS, BD6B36DBS: Database Systems | Lecture 02: Relational Model | 25. 2. 2020 42
 Binary Associations
• Multiplicity (0,1):(0,1) →
class 1_1

Person Mobile

- personalNumber - serialNumber
- address 0..1 0..1 - color
- age

▪ Three tables
‒ Person(personalNumber, address, age)
Mobile(serialNumber, color)
Ownership(personalNumber, serialNumber)
[Link] ⊆ [Link]
[Link] ⊆ [Link]
‒ Note that a personal number and serial number are both
independent keys in the Ownership table
B0B36DBS, BD6B36DBS: Database Systems | Lecture 02: Relational Model | 25. 2. 2020 43
 Binary Associations
• Multiplicity (1,n)/(0,n):(1,1) →
class 1_1

Person Mobile

- personalNumber - serialNumber
- address 1..1 1..* - color
- age

▪ Two tables
‒ Person(personalNumber, address, age)
Mobile(serialNumber, color, personalNumber)
[Link] ⊆ [Link]
‒ Why a personal number is not a key in the Mobile table?
• Because a person can own more mobile phones

B0B36DBS, BD6B36DBS: Database Systems | Lecture 02: Relational Model | 25. 2. 2020 44
 Binary Associations
• Multiplicity (1,n)/(0,n):(0,1) →
class 1_1

Person Mobile

- personalNumber - serialNumber
- address 0..1 1..* - color
- age

▪ Three tables
‒ Person(personalNumber, address, age)
Mobile(serialNumber, color)
Ownership(personalNumber, serialNumber)
[Link] ⊆ [Link]
[Link] ⊆ [Link]
‒ Why a personal number is not a key in the Ownership table?
• Because a person can own more mobile phones
B0B36DBS, BD6B36DBS: Database Systems | Lecture 02: Relational Model | 25. 2. 2020 45
 Binary Associations
• Multiplicity (1,n)/(0,n):(1,n)/(0,n) →
class 1_1

Person Mobile

- personalNumber - serialNumber
- address 1..* 1..* - color
- age

▪ Three tables
‒ Person(personalNumber, address, age)
Mobile(serialNumber, color)
Ownership(personalNumber, serialNumber)
[Link] ⊆ [Link]
[Link] ⊆ [Link]
‒ Note that there is a composite key in the Ownership table

B0B36DBS, BD6B36DBS: Database Systems | Lecture 02: Relational Model | 25. 2. 2020 46
 Attributes of Associations
class How to model characteristics o...

• Attribute of an association → Person

- personNumber
- name

+has_member 1..*
▪ Stored together with Member

- from
a given association table +is_member_of 0..*
- to

‒ Person(personNumber, name) Team

Team(name, url) - name

- url
Member(personNumber, name, from, to)
[Link] ⊆ [Link]
[Link] ⊆ [Link]
▪ Multivalued and composite attributes are transformed
analogously to attributes of ordinary classes

B0B36DBS, BD6B36DBS: Database Systems | Lecture 02: Relational Model | 25. 2. 2020 47
 General Associations
class Nary associations

Person

• N-ary association → - personNumber

0..*

▪ Universal solution: worker

0..*
0..* Proj ect

N tables for classes +

1..* - projectNumber
0..*

0..1
1 association table Team

‒ Person(personNumber) - name

Project(projectNumber)
Team(name)
Worker(personNumber, projectNumber, name)
[Link] ⊆ [Link]
[Link] ⊆ [Link]
[Link] ⊆ [Link]
▪ Less tables? Yes, in case of nice (1,1) cardinalities…

B0B36DBS, BD6B36DBS: Database Systems | Lecture 02: Relational Model | 25. 2. 2020 48
 Hierarchies
class ISA

• ISA hierarchy → -
Person

personalNumber

▪ Universal solution:
- name

separate table for each type Professor Student

with specific attributes only - phone - studiesFrom

‒ Person(personalNumber, name)
Professor(personalNumber, phone)
Student(personalNumber, studiesFrom)
[Link] ⊆ [Link]
[Link] ⊆ [Link]
‒ Applicable in any case (w.r.t. covering / overlap constraints)
‒ Pros: flexibility (when attributes are altered)
‒ Cons: joins (when full data is reconstructed)

B0B36DBS, BD6B36DBS: Database Systems | Lecture 02: Relational Model | 25. 2. 2020 49
 Hierarchies
• ISA hierarchy →
▪ Only one table for a hierarchy source
‒ Person(personalNumber, name, phone, studiesFrom, type)
‒ Universal once again, but not always suitable
• Types of instances are distinguished by an artificial attribute
» Enumeration or event a set
depending on the overlap constraint
‒ Pros: no joins
‒ Cons: NULL values required (and so it is not a nice solution)

B0B36DBS, BD6B36DBS: Database Systems | Lecture 02: Relational Model | 25. 2. 2020 50
 Hierarchies
• ISA hierarchy →
▪ Separate table for each leaf type
‒ Professor(personalNumber, name, phone)
Student(personalNumber, name, studiesFrom)
‒ This solution is not always applicable
• In particular when the covering constraint is false
‒ Pros: no joins
‒ Cons:
• Redundancies (when the overlap constraint is false)
• Integrity considerations (uniqueness of a personal number)

B0B36DBS, BD6B36DBS: Database Systems | Lecture 02: Relational Model | 25. 2. 2020 51
 Weak Entity Types
• Weak entity type → code

▪ Separate table
‒ Institution(name)
Team(code, name)
[Link] ⊆ [Link]
‒ Recall that the cardinality must always be (1,1)
‒ Key of the weak entity type involves also a key (any when
more available) from the entity type it depends on

B0B36DBS, BD6B36DBS: Database Systems | Lecture 02: Relational Model | 25. 2. 2020 52