Relational Databases

Example:

Entity "Student"
ID first name last name
123 Peter Miller
345 Mary Smith
456 John Brown
Entity "Course"
section nr school course nr
45 informatics Q200
73 comp. sci C360
84 informatics Q205
Relationship "Enroll"
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 NOT.

  • AND is used for combining several (different) attributes. A selected instance must have the exact values for all requested attributes. 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