ID || first name || last name |
123 || Peter || Miller |
345 || Mary || Smith |
456 || John || Brown
section nr || school || course nr |
45 || informatics || Q200 |
73 || comp. sci || C360 |
84 || informatics || Q205
ID || section nr |
123 || 73 |
123 || 84 |
345 || 73 |
456 || 45
entity: a table.
(called class in object-oriented design.)
instance: a row of a table.
(called object in
object-oriented design, also called record.)
attribute: a column of a table.
(called property in object-oriented design.)
key attribute: an attribute that uniquely identifies instances,
for example an ID.
value: a value of an attribute. For example, "firstname" can
have values "Peter", "Mary", "John".
relationship: a special kind of table that relates entities. It
has the key attributes of the entities as its attributes.
Cardinality of relations
Relations can be one-to-one (for example, car/driver),
one-to-many (for example, instructor/course),
or many-to-many (for example, student/course).
Single table queries
A single table can be queried using Boolean AND, OR,
AND is used for combining several (different) attributes.
A selected instance must have the exact values for all requested
For example, school=informatics AND course_nr=Q200. Answer: the
first row in table "course".
OR is used for allowing for several possibilities among attribute
values. A selected instance must have the exact value for at least
one of the requested attributes.
For example, school=informatics OR school=comp. sci. Answer:
all rows in table "course".
NOT is usually used in the sense of AND NOT.
A selected instance must not have the exact values for the requested
attributes. For example, school=informatics AND NOT course_nr=Q200.
Answer: the last row in table "course".
Queries that involve relationships
A query can join several entities via a relationship.
For example, "find the last names of students in Q200". This
query contains three sub-queries:
Select section_nr from course where course_nr = Q200.
The answer is section_nr = 45.
Select ID from enroll where section_nr = 45.
The answer is ID = 456.
Select last_name from student where ID = 456.
The answer is last_name = Brown.
This can be expressed in one query:
Select last_name from student, enroll, course where
course_nr = Q200 AND
course.section_nr = enroll.section_nr AND
enroll.ID = student.ID