Conceptual design with ER diagrams
Good databases start with understanding the real world they model. Entity-relationship modelling captures the things, their properties, and how they relate, before any table is created.
The design process
Database design usually moves from requirements analysis (what data and queries are needed), to conceptual design (an entity-relationship model independent of any DBMS), to logical design (tables, keys, and constraints), to physical design (indexes, storage, and partitioning). Mistakes caught early, on paper, are far cheaper to fix than mistakes in a live system.
Entities and attributes
The entity-relationship (ER) model was introduced by Peter Chen in 1976. An entity is a distinguishable thing, such as a particular student or book; an entity set or type groups similar entities, such as Student. Attributes describe entities: simple or composite (an address made of street, city, and postcode), single-valued or multivalued (several phone numbers), and stored or derived (age derived from birth date).
Keys and weak entities
Each entity set has a key attribute that uniquely identifies its entities, underlined in diagrams. A weak entity cannot be identified by its own attributes alone and depends on an owner entity: a room is identified by building plus room number, and an order line by its order plus line number. Weak entities are drawn with double rectangles.
Relationships
A relationship associates entities, such as Student enrols in Course. Relationships can have attributes, such as the grade or the enrolment date, which belong to the association, not to either entity. A relationship can involve the same entity set twice in different roles, such as Employee manages Employee.
Cardinality
Cardinality constraints state how many entities may participate. One-to-one: each person has at most one passport. One-to-many: a department employs many employees, each in one department. Many-to-many: students take many courses and courses have many students. Notations include crow's foot symbols, 1 and N labels, and (min, max) pairs.
Participation
Participation can be total or partial. If every employee must belong to a department, Employee's participation in Works_in is total, often drawn with a double line; if some departments have no projects, Department's participation in Runs is partial. Total participation usually becomes a NOT NULL foreign key in the final tables.
Specialisation and generalisation
Some entity sets have subtypes: Person may specialise into Student and Staff, each with extra attributes, while sharing common ones such as name. Constraints state whether subtypes are disjoint (a person is only one) or overlapping (a person can be both), and whether every person must belong to some subtype (total) or not (partial).
Worked example
A library: entity sets Member (member_id, name, email), Book (isbn, title, year), and Copy (a weak entity identified by isbn plus copy_number, owned by Book). Relationship Borrows between Member and Copy has attributes borrowed_on and due_on; a member can borrow many copies, and each copy is borrowed by at most one member at a time. Author and Book form a many-to-many relationship Writes.
Key takeaways
Design proceeds from requirements to conceptual (ER), logical (tables), and physical (storage) design. Peter Chen's 1976 ER model uses entities, entity sets, and attributes, which may be composite, multivalued, or derived. Keys identify entities; weak entities depend on owners. Relationships connect entities, may have attributes, and may involve roles. Cardinality is one-to-one, one-to-many, or many-to-many; participation is total or partial. Specialisation creates subtypes that may be disjoint or overlapping.
Flashcard
Discovering needed data and queries
Check your understanding
In a course database, a student's grade belongs to which part of the ER model?