1. Data Modelling

Phases of Database Design

• Conceptual design: ER/EER diagrams
• Logical design: conversion into DBMS-specific form (SQL schema)
• Physical design: hardware the data is stored on

Entity-Relationship

Attributes:

• Composite attributes: Name(Sub, Property)
  • Decompose into atomic attributes
• Multivalued: { attributeName }
  • Double eclipse
• Derived: dotted eclipse
• Primary Key
  • Can be composite
  • Must be single valued
  • Underlined

Relationships:

• Degree: number of participating entity types
  • Recursive relationships are unary
    • Require labels for the roles
• Can have attributes, but not key attributes
• Relationship type: description of schema, constraints etc.
• Relationship set: current set of relationships

Weak Entities:

• Exactly one partial key
  • May be composite
  • Unique for a given owner
• Can have multiple owners in its identifying relationship

Cardinality Ratio:

• ‘{A} can be in {B cardinality} relationships with {B}’

Structural Constraints:

• (min, max). max can be n (or m)
• Read the opposite way to cardinality constraints
• Use for n-ary relationships

Enhanced Entity-Relation

• Subclass ----\subset ---- Superclass: optional subclass
• (Subclass1 ----\subset, Subclass2 ----\subset)----d----Superclass: partial disjoint specialization
• (Subclass1 ----\subset, Subclass2 ----\subset)----d====Superclass: total disjoint specialization
• (Subclass1 ----\subset, Subclass2 ----\subset)----o----Superclass: partial overlapping specialization

Two subclasses can merge if they have the same unique identifier - they share a root superclass. In this case, the structure that is formed is called a lattice.

• Category====\subset====u----(Class1-----, Class2=====): class 1 is partial, class 2 is total

Union: each instance of the category is a member of one of the superclasses. The category will have an artificial surrogate key.