Samenvatting
Summary Databases Entire Course
- Vak
- Databases
- Instelling
- Vrije Universiteit Amsterdam (VU)
Everything you need to know for the databases exam!
[Meer zien]Voorbeeld 2 van de 12 pagina's
Voorbeeld 2 van de 12 pagina's
In winkelwagengesponsord bericht van onze partner
Voorbeeld van de inhoud
Femke Stokkink Databases Course2666619
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
Voordelen van het kopen van samenvattingen bij Stuvia op een rij:
Verzekerd van kwaliteit door reviews
Stuvia-klanten hebben meer dan 700.000 samenvattingen beoordeeld. Zo weet je zeker dat je de beste documenten koopt!
Snel en makkelijk kopen
Je betaalt supersnel en eenmalig met iDeal, creditcard of Stuvia-tegoed voor de samenvatting. Zonder lidmaatschap.
Focus op de essentie
Samenvattingen worden geschreven voor en door anderen. Daarom zijn de samenvattingen altijd betrouwbaar en actueel. Zo kom je snel tot de kern!
Veelgestelde vragen
Wat krijg ik als ik dit document koop?
Je krijgt een PDF, die direct beschikbaar is na je aankoop. Het gekochte document is altijd, overal en oneindig toegankelijk via je profiel.
Tevredenheidsgarantie: hoe werkt dat?
Onze tevredenheidsgarantie zorgt ervoor dat je altijd een studiedocument vindt dat goed bij je past. Je vult een formulier in en onze klantenservice regelt de rest.
Van wie koop ik deze samenvatting?
Stuvia is een marktplaats, je koop dit document dus niet van ons, maar van verkoper femkestokkink. Stuvia faciliteert de betaling aan de verkoper.
Zit ik meteen vast aan een abonnement?
Nee, je koopt alleen deze samenvatting voor €6,49. Je zit daarna nergens aan vast.
Is Stuvia te vertrouwen?
4,6 sterren op Google & Trustpilot (+1000 reviews)
Afgelopen 30 dagen zijn er 53068 samenvattingen verkocht
Opgericht in 2010, al 14 jaar dé plek om samenvattingen te kopen