Database Design
Immanuel Trummer
itrummer@[Link]
[Link]
Database Design Process
• Requirement analysis
• Based on use cases, business process descriptions
• Conceptual design
• Model what the DB is about, e.g. via ER diagrams
• Schema normalization
• E.g., reduce data redundancy via transformation
• Physical tuning
• E.g., decide which indices to create or sort order
Slides by Immanuel Trummer, Cornell University
Database Design Process
• Requirement analysis
• Based on use cases, business process descriptions
• Conceptual design
• Model what the DB is about, e.g. via ER diagrams
• Schema normalization
• E.g., reduce data redundancy via transformation
• Physical tuning
• E.g., decide which indices to create or sort order
Slides by Immanuel Trummer, Cornell University
Database Design Process
• Requirement analysis
• Based on use cases, business process descriptions
• Conceptual design
• Model what the DB is about, e.g. via ER diagrams
• Schema normalization
• E.g., reduce data redundancy via transformation
• Physical tuning
• E.g., decide which indices to create or sort order
Slides by Immanuel Trummer, Cornell University
Entities and Attributes
• Entity set: multiple entities of same type
• Represented as rectangle in ER diagram
• Attribute: a property connected to an entity set
• Represented as oval in ER diagram
• Connected via lines to associated entity
• Underlined if (part of) a key attribute
• Attributes have simple values (e.g., integer)
Slides by Immanuel Trummer, Cornell University
Example Entity Set
Name
NetID GPA
Student
Slides by Immanuel Trummer, Cornell University
Relationships
• A relationship connects entities
• Relationships are represented as diamonds
• Connecting lines indicate targeted entities
• May connect two or more entities
Slides by Immanuel Trummer, Cornell University
Binary Relationship
Examples
Lecturer Lecturer
Teaches Teaches
Course Room
Slides by Immanuel Trummer, Cornell University
Ternary Relationship
Example
Lecturer
Teaches
Course Room
Slides by Immanuel Trummer, Cornell University
What is the
Difference?
Slides by Immanuel Trummer, Cornell University
Classifying Relationships
• Can constrain number of relationships per entity
• Participation constraint: entity must relate at least once
• Represented by a thick line (entity to relationship)
• At-most-one constraint: entity relates at most once
• Represented by arrow (from entity to relationship)
Slides by Immanuel Trummer, Cornell University
Binary Relationship
Examples
Lecturer Student
Teaches Favorite
Course Course
Slides by Immanuel Trummer, Cornell University
Binary Relationship
Examples
Lecturer Student
Semantics?
Teaches Favorite
Course Course
Slides by Immanuel Trummer, Cornell University
More Relationship Features
• Can associate relationships with attributes
• Same representation as for entity attributes
• Refers to related entity combinations
• Can assign entities to roles
• Represent role as label for connecting edge
• Required when connecting entities of same type
Slides by Immanuel Trummer, Cornell University
More Features Examples
Employee
Supervisor Supervised
Supervises
Start_Time
Slides by Immanuel Trummer, Cornell University
Sub-Classes
• You most likely know concept from OO languages
• Sub-classing allows to reduce redundancy in diagram
• Sub-classes inherit the attributes from parent
• Sub-classes inherit relationships from parent
• Represent sub-classes via triangles ("Is-A")
• No multiple inheritance (sub-classes form tree)
Slides by Immanuel Trummer, Cornell University
Sub-Classes Example
NetID Employee Name
Supervised
Supervisor
Is-
A
Supervises
Instructor NrCourses
Teaches
Course
Slides by Immanuel Trummer, Cornell University
Weak Entities
• Weak entity can only be uniquely identified by considering
the primary key of another ("owner") entity
• Weak entity connects to owner via identifying relationship
• Weak entity must participate in identifying relationship
• Also, each weak entity can appear at most once in it
Slides by Immanuel Trummer, Cornell University
Weak Entity Example
HW_Nr
Homework
Belongs-
To
Course
Course_ID
Slides by Immanuel Trummer, Cornell University
Aggregation
• Models relationship of a relationship
• Surround relationship with dashed rectangle
• Now connect dashed rectangle with other items
Slides by Immanuel Trummer, Cornell University
Aggregation Example
EmployeeID Employee Name
Monitors Until
Since Department DepID
Sponsors
Project
Slides by Immanuel Trummer, Cornell University
Why Not Use Ternary
Relationship ... ?
Slides by Immanuel Trummer, Cornell University
Design Choices:
Entities vs. Attributes
• Often can choose between entities and attributes
• E.g., model address as attribute or connected entity?
• Use entity if employees can have multiple addresses
• Attribute values cannot be set valued
• Model as entity if we want to structure address further
• Can model components as attributes
Slides by Immanuel Trummer, Cornell University
Subtleties of ER Diagrams
Employee
From
WorksFor
To
Department
Slides by Immanuel Trummer, Cornell University
Subtleties of ER Diagrams
Employee
From
h i s . . . ?
W it h T
Wron g WorksFor
W ha t'
Tos
Department
Slides by Immanuel Trummer, Cornell University
Better Representation
Employee
From
WorksFor TimePeriod
To
Department
Slides by Immanuel Trummer, Cornell University
Subtleties of ER diagrams II
Manager
Budget Manages
Project
Slides by Immanuel Trummer, Cornell University
Subtleties of ER diagrams II
Manager
h i s . . . ?
Budget
W
Manages
it h T
Wron g
W ha t' s
Project
Slides by Immanuel Trummer, Cornell University
Better Representation
Employee
Budget Is-
A
Manager Manages Project
Slides by Immanuel Trummer, Cornell University
Exercise
• Draw an ER diagram describing the following situation
• Customers have an ID (unique) and a name
• Each order is associated with exactly one customer
• An order consists of at least one item
• Items have an ID (unique), a name, and a price
Slides by Immanuel Trummer, Cornell University
Solution
ID Name
Customer
Order Issues
PartOf Item
Number ID Name Price
Slides by Immanuel Trummer, Cornell University
ER diagrams as Relations
• Need to translate ER diagrams to relations
• Introduce relations for entity types
• Each entity becomes row in relation
• Properties are represented as columns
• Underlined attributes part of primary key
Slides by Immanuel Trummer, Cornell University
Translating Relationships
• Generic method: introduce relation capturing relationships
• Columns store primary keys of all connected entities
• Row represents relationship between specific entities
• Primary key combines primary keys of entities
• Additional attributes become columns as well
Slides by Immanuel Trummer, Cornell University
Example
NetID Student Sname
Since Favorite
CourseID Course Cname
Slides by Immanuel Trummer, Cornell University
Example
NetID Student Sname
Student(NetID, Sname)
Favorite(NetID,
Since Favorite
CourseID, Since)
Course(CourseID, Cname)
CourseID Course Cname
Slides by Immanuel Trummer, Cornell University
Translating Sub-Classes
• Entities of sub-class may have additional attributes
• Can be represented in multiple different ways
• Separate relations for superclass and sub-class
• Introduce multiple relations linking key to attributes
• Use relation for sub-class, set unused attributes to null
Slides by Immanuel Trummer, Cornell University
Sub-Classing Example
EmpID Employee JobTitle
Budget Is-
A
Manager
Employee(EmpID, JobTitle)
Manager(EmpID, JobTitle, Budget)
Slides by Immanuel Trummer, Cornell University
Sub-Classing Example
EmpID Employee JobTitle
Budget Is-
A
Manager
JobTitles(EmpID, JobTitle)
Budgets(EmpID, Budget)
Slides by Immanuel Trummer, Cornell University
Sub-Classing Example
EmpID Employee JobTitle
Budget Is-
A
Manager
EmployeeOrManager(EmpID, JobTitle, Budget)
Slides by Immanuel Trummer, Cornell University
Translating Weak Entities
• Introduce new relation for storing weak entities
• Add foreign key columns referencing owner entity
• In SQL: cascading delete depending on owner
Slides by Immanuel Trummer, Cornell University
Weak Entities Example
HW_Nr
Homework GradeMean
Belongs-
To
Course Course_ID
Homeworks(HwNr, CourseID, GradeMean)
Slides by Immanuel Trummer, Cornell University
Weak Entities Example
HW_Nr
Homework GradeMean
Belongs-
To
Course Course_ID
Create table Homeworks(HwNr int, courseID int, gradeMean numeric,
primary key(HwNr, courseID),
foreign key (courseID) references courses on delete cascade)
Slides by Immanuel Trummer, Cornell University
ER diagrams in Practice
• Lots of tools available for drawing ER diagrams
• [Link]
tools/postgresql
• Many of them export automatically SQL statements
• Precise visualization may differ, concepts are similar
Slides by Immanuel Trummer, Cornell University
MusicBrainz Database: Simplified Schema