Femke Stokkink Databases Course
2666619
2022-03-21
Summary
1 The Relational Model
Table entries are values that conform to some data type.
Examples of SQL data types: strings, numbers, binary data, date and time
The domain dom(D) of a type D is the set of possible values. (e.g. dom(int), dom(numeric(2,0))
A relation schema s defines a (finite) sequence A1 , ... An of distinct attribute names and it defines for each
attribute Ai a data type or domain Di .
s = (A1 : D1 , ..., D1 : Dn
Example: Exercises(catergory, number, topic, maxPoints)
A relational database schema S defines:
• a finite set of relation names R1 , ..., Rm (e.g. Students, Exercises, Results)
• a relation schema schema(Ri ) for every relation Ri
• a set of integrity constraints C
S = (R1 , ..., Rm , schema, C)
Tuples are used to formalise table rows. A tuple t with respect to the relation schema is a sequence t = (d1 , ..., dn )
of values such that di ∈ dom(Di )
e.g. t.Ai -¿ (’exam’, 1, ’SQL’, 14).topic = ’SQL’
A database state I for database schema S defines for ervery relation name Ri , a finite set of tuples I(Ri ) with
respect to schema(Ri )
Databases state is the set of tables conforming to the schema. Except, there is no order on the tuples (rows)
and tables contain no duplicate tuples.
The relational model allows missing attribute values: table entries may be empty.
Formally, the domain is extended by a new special value null.
Null values are used to model a variety of scenarios:
• No value exists: student might not have an e-mail address.
• The attribute is not applicable for this tuple: example exercise that is not worth any points.
• A value exists but is not known, address of a student might be unknown
• Any value will do
To get the rows with null values, use where A is null.
You can also declare attributes as not null.
Integrity constraints (IC) are conditions which every database state has to satisfy. This restricts the set of
possible database states. The integrity constraints are specified in the database schema.
SQL create table allows the following constraints:
• Not null: no value in the column can be the null value.
1
, • Key constraints: each key value can only appear once.
• Foreign key constraints: values in a column must also appear as key values in another table.
• Check constraints: column values must satisfy a given predicate, like check(price ¿ 0)
A key of a relation R is a set of attributes A1 , ..., An that uniquely identify the tuples in R. Any superset of a
key is itself a key. A key A1 , ..., An is minimal if no proper subset is a key.
A foreign key is used to refer from a relation R to tuples of S: add the primary key attributes of S to the
attributes of R. A foreign key implements a one-to-many relationship.
The foreign key constraint ensures that for every tuple t ∈ Results where t.sid is not null, there exists a tuple
u ∈ Students such that t.sid = u.sid.
Foreign keys are denoted with arrows (→) in the schema: Students(sid, first, last, address)
Exercises(category, number, topic, maxPoints)
Results(sid → Students, (category, number) → Exercises, points)
2 Data Modelling
Entity-Relationship Model has three main ingredients: entity sets, attributes, relationship sets.
• Rectangles represent entity sets
• Ellipses represent attributes (double = multi-value, dashed = derived)
• Diamonds represent relationship sets
• Lines link attributes an relationship sets to entity sets
• Underline indicates primary key attributes
A relationship is an association among several entities. The relationship set connections can be annotated
with role indicators. A relationship set of degree 2 is called binary, a relationship set of degree 3 is called
ternary.
EXAMPLE
Cardinality limits express the number of entities to which another entity can be associated via a relationship
set.
INSERT Every entity a from A is connected to at least N1 and at most N2 entities in B. Every entity b from B is
connected to at least M1 and at most M2 entities in A.
Typical cardinality constraints:
0..1 = zero or one
1..1 = precisely one 0..* = any number 1..* = at least one
Cardinality limits:
• Many-to-Many: A – 0..* – R – 0..* – B every a in A can be linked to an arbitrary number of B’s and every
b in B can be linked to an arbitrary number of A’s
• One-to-One: A – 1..1 – R – 1..1 – B every a in A is connected to precisely one b in B and every b in B is
connected to precisely one a in A
• Zero or One-to-Zero or one: A – 0.1* – R – 0..1 – B every a in A is connected to at most one (=0 or 1) b
in B and every b in B is connected to at most one (=0 or 1) a in A
• One-to-many: A – 1..1 – R – 0..* – B every a in A is related to an arbitrary number of b’s in B and every
B is connected to precisely one a in A
2
2666619
2022-03-21
Summary
1 The Relational Model
Table entries are values that conform to some data type.
Examples of SQL data types: strings, numbers, binary data, date and time
The domain dom(D) of a type D is the set of possible values. (e.g. dom(int), dom(numeric(2,0))
A relation schema s defines a (finite) sequence A1 , ... An of distinct attribute names and it defines for each
attribute Ai a data type or domain Di .
s = (A1 : D1 , ..., D1 : Dn
Example: Exercises(catergory, number, topic, maxPoints)
A relational database schema S defines:
• a finite set of relation names R1 , ..., Rm (e.g. Students, Exercises, Results)
• a relation schema schema(Ri ) for every relation Ri
• a set of integrity constraints C
S = (R1 , ..., Rm , schema, C)
Tuples are used to formalise table rows. A tuple t with respect to the relation schema is a sequence t = (d1 , ..., dn )
of values such that di ∈ dom(Di )
e.g. t.Ai -¿ (’exam’, 1, ’SQL’, 14).topic = ’SQL’
A database state I for database schema S defines for ervery relation name Ri , a finite set of tuples I(Ri ) with
respect to schema(Ri )
Databases state is the set of tables conforming to the schema. Except, there is no order on the tuples (rows)
and tables contain no duplicate tuples.
The relational model allows missing attribute values: table entries may be empty.
Formally, the domain is extended by a new special value null.
Null values are used to model a variety of scenarios:
• No value exists: student might not have an e-mail address.
• The attribute is not applicable for this tuple: example exercise that is not worth any points.
• A value exists but is not known, address of a student might be unknown
• Any value will do
To get the rows with null values, use where A is null.
You can also declare attributes as not null.
Integrity constraints (IC) are conditions which every database state has to satisfy. This restricts the set of
possible database states. The integrity constraints are specified in the database schema.
SQL create table allows the following constraints:
• Not null: no value in the column can be the null value.
1
, • Key constraints: each key value can only appear once.
• Foreign key constraints: values in a column must also appear as key values in another table.
• Check constraints: column values must satisfy a given predicate, like check(price ¿ 0)
A key of a relation R is a set of attributes A1 , ..., An that uniquely identify the tuples in R. Any superset of a
key is itself a key. A key A1 , ..., An is minimal if no proper subset is a key.
A foreign key is used to refer from a relation R to tuples of S: add the primary key attributes of S to the
attributes of R. A foreign key implements a one-to-many relationship.
The foreign key constraint ensures that for every tuple t ∈ Results where t.sid is not null, there exists a tuple
u ∈ Students such that t.sid = u.sid.
Foreign keys are denoted with arrows (→) in the schema: Students(sid, first, last, address)
Exercises(category, number, topic, maxPoints)
Results(sid → Students, (category, number) → Exercises, points)
2 Data Modelling
Entity-Relationship Model has three main ingredients: entity sets, attributes, relationship sets.
• Rectangles represent entity sets
• Ellipses represent attributes (double = multi-value, dashed = derived)
• Diamonds represent relationship sets
• Lines link attributes an relationship sets to entity sets
• Underline indicates primary key attributes
A relationship is an association among several entities. The relationship set connections can be annotated
with role indicators. A relationship set of degree 2 is called binary, a relationship set of degree 3 is called
ternary.
EXAMPLE
Cardinality limits express the number of entities to which another entity can be associated via a relationship
set.
INSERT Every entity a from A is connected to at least N1 and at most N2 entities in B. Every entity b from B is
connected to at least M1 and at most M2 entities in A.
Typical cardinality constraints:
0..1 = zero or one
1..1 = precisely one 0..* = any number 1..* = at least one
Cardinality limits:
• Many-to-Many: A – 0..* – R – 0..* – B every a in A can be linked to an arbitrary number of B’s and every
b in B can be linked to an arbitrary number of A’s
• One-to-One: A – 1..1 – R – 1..1 – B every a in A is connected to precisely one b in B and every b in B is
connected to precisely one a in A
• Zero or One-to-Zero or one: A – 0.1* – R – 0..1 – B every a in A is connected to at most one (=0 or 1) b
in B and every b in B is connected to at most one (=0 or 1) a in A
• One-to-many: A – 1..1 – R – 0..* – B every a in A is related to an arbitrary number of b’s in B and every
B is connected to precisely one a in A
2
