Databases 06

Database design

Work process

• Design - entities, relationships, constraints
• Create DB schema
• Use in applications
  • A good design minimizes changing the DB and the systems using it

Communication

An intuitive yet precise modeling language

Correctness

Avoid duplicates/lost data, support efficient analysis

Entity Relation (E/R) diagram

Entities, attributes, relationships

ER diagrams represent entity sets (i.e. tables, rectangle nodes), attributes (ellipse nodes) of these sets and relationships (diamond nodes) between these sets

There are many types of relationships (denoted with different types of arrows):

• One-to-One: one entity from A relates to one entity from B
• One-to-Many: one entity from B relates to many entities from A (note that order is important)
• Many-to-One: symmetrical to One-to-Many
• Many-to-Many: many entities from A relate to many entities from B One is a directed arrow, Many is an undirected arrow (line)

Attributes on relationships

It is possible to add attributes on relationships, i.e. each pair of A, B is associated with attribute x This attribute can also be Identifying

Multiway relationships

A relationships between more than two entity sets is called multiway Multiway relationships can represent multi-dependencies:

• Agency X Invoice X Person X date Car
• Agency X Invoice X Car X date Person

It is possible to convert a multiway relationship to a set of binary relationships, but some information will be lost(!)

Subclasses in E/R

Subclasses are represented with an IsA relationship (triangle nodes) between two entity sets

Constraints in E/R

• Keys
• Single value
• Referential integrity
• Others

Referential integrity

Referential integrity refers to a NOT NULL constraint on relationships, i.e. FKs It is denoted with a special kind of arrow with rounded tip

Referential integrity and weak entity sets

One entity identifier might depend on another, i.e. relationship is part of the key In this case, entity and relationship have a double outline.

Other constraints

Other constraints are listed as annotations on edges of relationships

---

Converting E/R diagram to a DB Schema

Translations

• Entity sets are always converted to relations(tables)
• Many-to-Many relationships are also converted to connecting “helper” tables
• One-to-Many relationships are recorded in the “Many” table as an additional column(attribute)
• Subclasses are converted into separate tables with the same key as the superclass(!)
• Constraints
  • Keys are converted to Primary keys
  • Relationships
    • Many-to-Many: FKs in the connecting table, note that multiway relationships are also converted into one connecting table with more FKs
    • Many-to-One: FK in the “Many” relation referencing the “One” relation
    • One-to-One: unidirectional Many-to-One + UNIQUE or bidirectional Many-to-One
  • Referential integrity is converted to NOT NULL
  • Other constraints are converted to CHECK and assertions

Finding the keys

• Key of an Entity set
  • direct or inherited key attributes of the entity set
  • key attributes of entity sets it depends on (recursively)
• Key of a Many-to-Many relationship
  • direct key attributes of the relationship
  • keys of related entity sets
• Key of a Many-to-One relationship
  • direct key attributes of the relationship
  • keys of related “Many” entity sets