Databases

Lecture 4: Entity-Relationship modelling

• Lecture notes: chapter 3
• Book: chapter 4

Blackboards

Database design process

• Domain description Informal text

  ⟹

  High-level design E-R model (diagram)

  ⟹

  Relational schema

  ⟹

  Implementation in a DBMS SQL code

Entity-Relationship model

The building blocks of E-R models

• Entity sets
  • The "things" we have information about
• Relationships
  • How entities are related to each other, connections among entities
  • Note on terminology: relationships vs relations (tables)
• Attributes
  • The information we have that will go into the database tables in the end
  • Both entities and relationships can have attributes
  • Attributes are NOT NULL unless otherwise stated

E-R diagrams

• Not standardized
• Variation exists
  • What features are included
  • How the are drawn

Multiplicity

How many things are related by a relationship?

• Many-to-many
  • A country can have any number of official languages, and
  • a language can be used in many countries.
• Many-to-one
  • A country has exactly one official language.
  • Every country has a capital.
• Many-to-at-most-one
  • A country may have an official language.
  • Some countries have an offical language.

Weak entities

• Entities that can not be uniquely identified without referring to another entitiy.
  • City names are only uniqe within a country
    • Countries(name,...)

Cities(name,country)
  country -> Country.name
• Always in a weak many-to-one relationship with a supporting entity.

ISA relationships

An X is a Y

• A speical one-to-one relationship.
• A bit like subclasses in object-oriented languages.
• A subentity has all the attributes of the superentity.
• A subentity has no key attributes.
• Examples:
  • Some cities are coastal cities (and are next to a sea).
  • Some cities are capitals.
  • Some countries are EU countries (and have a joining date).
  • Some EU countries are former EU countries (and have an exit date).