Summary
Summary Databases Entire Course
- Course
- Institution
Everything you need to know for the databases exam!
[Show more]
Seller
Follow
femkestokkink
Reviews received
Content preview
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
The benefits of buying summaries with Stuvia:
Guaranteed quality through customer reviews
Stuvia customers have reviewed more than 700,000 summaries. This how you know that you are buying the best documents.
Quick and easy check-out
You can quickly pay through credit card or Stuvia-credit for the summaries. There is no membership needed.
Focus on what matters
Your fellow students write the study notes themselves, which is why the documents are always reliable and up-to-date. This ensures you quickly get to the core!
Frequently asked questions
What do I get when I buy this document?
You get a PDF, available immediately after your purchase. The purchased document is accessible anytime, anywhere and indefinitely through your profile.
Satisfaction guarantee: how does it work?
Our satisfaction guarantee ensures that you always find a study document that suits you well. You fill out a form, and our customer service team takes care of the rest.
Who am I buying these notes from?
Stuvia is a marketplace, so you are not buying this document from us, but from seller femkestokkink. Stuvia facilitates payment to the seller.
Will I be stuck with a subscription?
No, you only buy these notes for $6.96. You're not tied to anything after your purchase.
Can Stuvia be trusted?
4.6 stars on Google & Trustpilot (+1000 reviews)
53068 documents were sold in the last 30 days
Founded in 2010, the go-to place to buy study notes for 14 years now