Lecture2.pdf

💡Recall

The Relational Data Model

• advantages
  • simple data representation
  • queries, even complex ones, can be easily expressed, using a simple, high-level language
  • even non-technical users can understand the contents of a database

Relations

• in the relational model, the data collection is organized as a set of relations (tables)

• a relation has:

  • a relation schema
    • describes the table's column heads
    • specifies the
      • relation's name
      • the name and domain for each field
        • the domain of a field is specified through its name and has a set of associated values

  and

  • a relation instance
    • can be thought of as a table

• the order of the rows is not important

• the values of an attribute are atomic and scalar

• the rows in a table are not ordered

  e.g. 2 representations of the same Students relation instance

  

• the records in a table are distinct - a relation is defined as a set of distinct tuples (however, DBMSs allow tables to contain duplicates)

• the cardinality of an instance is the number of tuples it contains

Syntax

• {A1, A2, ..., An} - a set of attributes

• Di = Dom(Ai) U {?}

  • domain of possible values for attribute Ai
    • the undefined (null) value is denoted here by {?}
      • unknown / not applicable
      • can be used to check if a value has been assigned to an attribute (the attribute could have the undefined value)
      • this value doesn't have a certain data type
      • attribute values of different types can be compared against this value (numeric, string, etc.)

• relation of arity (degree) n:

  • R ⊆ D1 × D2 × ... × Dn

• R[A1, A2, ... , An] - the relation schema

• can be stores in a table of the form:

  

Example

Schema example for the Students relation:

Students[name, year_of_study, email]

or notation:

Students(name: string, year_of_study: integer, email: string)

Instance example for the Students relation:

Integrity Constraints

Integrity constraints (restrictions)

• conditions specified on the database schema, restricting the data that can be stored in the database

• checked when the data is changed

• operations that violate the constraints are not allowed (e.g. introducing a student with a CNP identical to a different student's CNP, entering data of the wrong type in a column, etc.)

• e.g.

  domain constraints, key constraints, foreign key constraints

Domain constraints

• conditions that must be satisfied by every relation instance (a column's values belong to its associated domain)

Key constraint

• a constraint stating that a subset of the attributes in a relation is a unique identifier for every tuple in the relation

• K ⊆ {A1, A2,..., An} is a key for relation R[A1, A2, ... , An]

  if:

  • the attributes in K can be used to identify every record in R
  • no subset of K has this property

• this subset of attributes is minimal

• two different records are not allowed to have identical values in all the fields that constitute a key, hence specifying a key is a restriction for the database

• superkey - a set of fields that contains the key (e.g. the set {bid, title} is a superkey)

e.g.

1. In the Doctors[dep, did, lastname, firstname] relation, we can define the constraint: a doctor is uniquely identified by his / her department and did, i.e., 2 different doctors can have the same department or the same did, but not both; the key is the group of attributes {dep, did}

2. Books[author, title, publisher, year]

   

   a possible key is the group of attributes {author, title, publisher, year}; in such a case, the schema can be augmented to include another attribute (with distinct values; the latter can be automatically generated when records are added)

3. Books[author, title, publisher, year]

   

   {bid} can now be chosen as the key - two different tuples will always have different values in the bid column

   the user must be careful not to define a key constraint that prevents the storage of correct tuple sets

   e.g. if {title} is declared to be a key, the Books relation can't contain tuples describing different books with the same title

Primary key and Candidate keys

• a relation can have multiple keys: one key (an attribute or a group of attributes) is chosen as the primary key, while the others are considered candidate keys e.g.

  Schedule[day, hour, room, teacher, group, subject] with the schedule for a week the following sets of attributes can be chosen as keys:

  • {day, hour, room}, {day, hour, teacher}, {day, hour, group}

Foreign key constraints

• the values of some attributes in a relation can also appear in another relation
• referential integrity

e.g.

In the figure below, A is defined as a foreign key in R2, it refers to R1's key

R1 and R2 need not be distinct

Departments[did, name, budget]

• establishing a link between Departments (as the parent relation) and Doctors (child relation):