5. Conceptual Modelling and Relational Database Design

Introduction and Context

Database design is the intellectual process of converting an organisation’s information requirements into a structure that can be stored, queried, protected, and maintained reliably. A database is not merely a collection of tables. It is a formal representation of business rules, organisational entities, relationships, constraints, and permitted states of information. Poor design can produce duplicated data, contradictory values, difficult queries, weak security, and expensive maintenance. Sound design creates a stable foundation for transaction processing, reporting, analytics, and data integration.

Conceptual modelling provides a technology-independent description of an organisation’s data. The Entity–Relationship (ER) model is the principal vehicle for this description. Enhanced ER modelling extends it to include specialisation, generalisation, categories, aggregation, and inheritance. The conceptual model is subsequently mapped to a relational schema consisting of relations, attributes, keys, and integrity constraints. Functional and multivalued dependencies then provide a formal basis for evaluating redundancy and decomposing relations through normalisation. In practice, fully normalised designs may sometimes be modified for performance, a decision known as denormalisation.

The design process is therefore both technical and organisational. It requires communication with domain experts, interpretation of business rules, and judgement about integrity, performance, security, and future change.

Learning Objectives

After studying this chapter, a student should be able to:

• distinguish conceptual, logical, and physical database design;
• identify entities, attributes, keys, relationships, and cardinality constraints;
• construct ER and enhanced ER models for realistic requirements;
• map ER constructs to relational schemas;
• explain functional and multivalued dependencies;
• decompose relations into 1NF, 2NF, 3NF, and BCNF designs;
• recognise the purpose of fourth and fifth normal forms;
• evaluate decomposition using lossless-join and dependency-preservation criteria;
• assess when denormalisation may be justified; and
• apply professional, governance, and quality considerations to database design.

From Requirements to a Conceptual Model

Database design commonly proceeds through three levels. Conceptual design describes information requirements independently of a database management system. Logical design translates the conceptual model into a data model, such as the relational model. Physical design selects indexes, partitions, storage structures, access paths, and other implementation details for a particular DBMS.

These levels should not be confused. A decision to represent an employee as an entity is conceptual. Representing Employee as a relation with EmployeeID as its primary key is logical. Creating a clustered index on EmployeeID is physical. Keeping these decisions separate makes the design easier to evaluate and change.

Requirements may be collected from interviews, existing forms, reports, policies, transaction logs, application code, and regulatory documents. Designers should ask what objects the organisation recognises, what facts it records about them, which events occur, and what rules restrict those events. A useful model represents stable business meaning rather than merely reproducing current screens or spreadsheets.

Entities, Attributes, and Identifiers

An entity is a distinguishable object, event, or concept about which data is stored. A Student, Course, Invoice, Warehouse, or Payment may be an entity type; an individual student or invoice is an entity instance. An attribute describes an entity or relationship. StudentName, DateOfBirth, and CourseCredits are examples.

Attributes may be simple or composite. Address may consist of Street, City, and PostalCode. They may be single-valued or multivalued, such as a person’s several telephone numbers. A derived attribute, such as AccountBalance calculated from transactions, need not always be stored. Storing derived values can improve performance but introduces consistency risks.

An entity’s key is an attribute or set of attributes that uniquely identifies its instances. A candidate key is a minimal unique identifier; one candidate key is selected as the primary key. A surrogate key is an artificial identifier, such as a generated numeric value. Natural keys carry business meaning, such as a national registration number, but may change, be unavailable, or raise privacy concerns. A foreign key references a key in another relation and expresses a relationship between tuples.

A weak entity cannot be uniquely identified solely by its own attributes. It depends on an identifying owner entity and normally has a partial key. For example, an OrderLine may be identified by OrderID and LineNumber. The owner’s key becomes part of the weak entity’s identifying key.

Relationships and Constraints

A relationship represents an association among entity instances. In a university, Student enrols in Course; in a commerce system, Customer places Order. Relationship attributes describe the association itself. EnrolmentDate and Grade belong naturally to the relationship between Student and Course rather than to either entity alone.

Cardinality specifies the maximum participation: one-to-one (1:1), one-to-many (1:N), or many-to-many (M:N). Participation specifies whether participation is mandatory or optional. A department may employ many employees, while each employee may belong to exactly one department. If every employee must belong to a department, Employee has total participation in that relationship; if temporary employees may be unassigned, participation is partial.

Business rules should be stated precisely. “A project may have many employees” is incomplete unless the model also states whether an employee may work on many projects and whether assignment is compulsory. Constraints may include uniqueness, domain restrictions, minimum and maximum cardinalities, temporal conditions, and exclusion rules. A model that omits significant constraints may be syntactically correct but semantically weak.

ER Modelling and Enhanced ER Modelling

The ER model uses entity types, attributes, relationships, and constraints. A typical modelling sequence is:

1. identify candidate entities and clarify their meanings;

2. identify attributes and possible keys;

3. identify relationships and relationship attributes;

4. specify cardinality and participation constraints;

5. validate the model against business scenarios; and

6. remove redundancy or ambiguity before mapping it to relations.

Enhanced ER (EER) modelling supports specialisation, in which a superclass is divided into subclasses, and generalisation, in which common features of several entity types are abstracted into a superclass. For example, Account may have subclasses SavingsAccount and CurrentAccount. Common attributes belong to Account, while subclass-specific attributes belong to the appropriate subclass.

Specialisation can be disjoint or overlapping. In a disjoint specialisation, an instance belongs to at most one subclass. In an overlapping specialisation, it may belong to several. It may also be total, meaning every superclass instance belongs to a subclass, or partial, meaning some do not.

A category, or union type, represents a subclass whose instances come from different superclasses. For example, an Owner may be either a Person or a Company. Aggregation treats a relationship set as a higher-level object so that it can participate in another relationship. These constructs are valuable when ordinary relationships cannot express the required business meaning clearly.

EER models should be used selectively. Excessive inheritance can make rules difficult to understand, while failure to model a meaningful subtype can result in null-heavy relations and weak constraints.

Translating EER Models into Relational Schemas

The relational mapping process converts conceptual constructs into relations while preserving identifiers and constraints as far as possible.

For a strong entity, create a relation containing its simple attributes and designate its primary key. Composite attributes are represented by their components. Multivalued attributes normally become a separate relation containing the owner’s primary key and the multivalued value. Derived attributes may be omitted or stored under controlled procedures.

For a 1:N relationship, place the primary key of the entity on the N-side as a foreign key in that relation. Relationship attributes are also placed there. For an M:N relationship, create a new associative relation containing the primary keys of both participating entities and any relationship attributes. The combination of the two keys is often its primary key, although an additional identifier may be appropriate.

For a 1:1 relationship, the foreign key may be placed in either relation, preferably on the side with total participation or where it avoids nulls. A uniqueness constraint is required to preserve the one-to-one rule. A relationship with attributes may instead be represented by its own relation.

A weak entity becomes a relation containing its partial key, the owner’s primary key, and its attributes. The combined key identifies the weak entity. For EER specialisation, common strategies include:

• one relation for the superclass and one for each subclass, with the subclass key also a foreign key;
• one relation for the entire hierarchy with a type discriminator, suitable where subclasses share many attributes; or
• separate relations for concrete subclasses, suitable when the hierarchy is disjoint and superclass queries are less frequent.

The choice affects null values, integrity enforcement, query complexity, and performance. It should be made deliberately rather than automatically.

Dependencies: The Logic of Redundancy

A functional dependency (FD) is a constraint written X → Y. It means that whenever two tuples agree on attribute set X, they must also agree on attribute set Y. If StudentID → StudentName, a student identifier determines one student name. Functional dependency is a semantic property of the domain, not merely an observation in a small sample of data.

A determinant is the left-hand side of an FD. A candidate key functionally determines every attribute in its relation. A partial dependency occurs when a non-key attribute depends on part of a composite key. A transitive dependency occurs when a non-key attribute depends on another non-key attribute through a chain, such as StudentID → DepartmentID and DepartmentID → DepartmentName.

Armstrong’s axioms provide a sound and complete inference system for FDs: reflexivity, augmentation, and transitivity. Derived rules include union, decomposition, and pseudotransitivity. Attribute closure, written X+, is the set of attributes functionally determined by X. It is used to test whether X is a superkey and to identify candidate keys.

A multivalued dependency (MVD), written X ↠ Y, states that for a given X, the set of Y values is independent of the remaining attributes. Consider a relation StudentLanguageHobby(Student, Language, Hobby). If languages and hobbies are independent, storing every combination creates repetition. The MVD Student ↠ Language indicates that languages form an independent multivalued fact for each student. MVDs motivate Fourth Normal Form.

Normalisation as Structured Decomposition

Normalisation is a disciplined process of decomposing relations to reduce undesirable redundancy and update anomalies. It does not mean splitting tables without analysis. A good decomposition should normally be lossless, so that joining the decomposed relations recreates the original valid information, and preferably dependency-preserving, so important constraints can be enforced without reconstructing a join.

A relation is in First Normal Form (1NF) when attributes contain atomic values and repeating groups are removed. “Atomic” is context-dependent: a postal address might be treated as one value in one application and decomposed in another. 1NF does not eliminate all redundancy.

A relation is in Second Normal Form (2NF) when it is in 1NF and every non-prime attribute is fully functionally dependent on every candidate key. 2NF matters mainly when a candidate key is composite. If Enrolment(StudentID, CourseID, StudentName, Grade) has key (StudentID, CourseID), StudentName depends only on StudentID and should be placed in Student.

A relation is in Third Normal Form (3NF) when it is in 2NF and has no problematic transitive dependency of a non-prime attribute on a key. Formally, for every nontrivial FD X → A, either X is a superkey or A is prime (part of some candidate key). 3NF generally provides a useful balance between redundancy reduction and dependency preservation.

Boyce–Codd Normal Form (BCNF) is stricter: for every nontrivial FD X → A, X must be a superkey. A relation can satisfy 3NF but violate BCNF when a determinant is not a superkey and the dependent attribute is prime. BCNF decomposition often improves redundancy control but may fail to preserve every dependency.

For example, suppose Teaching(Student, Course, Instructor) has rules Course → Instructor and Instructor → Course. If a student can take a course and each course has one instructor, Course is a determinant but not a key because Student is also needed to identify a tuple. Repeated instructor information results. Decomposition into CourseInstructor(Course, Instructor) and StudentCourse(Student, Course) addresses the dependency, subject to the precise business rules.

Fourth Normal Form (4NF) addresses nontrivial multivalued dependencies. A relation is in 4NF if, for every nontrivial MVD X ↠ Y, X is a superkey. StudentLanguageHobby should be decomposed into StudentLanguage(Student, Language) and StudentHobby(Student, Hobby) when the two multivalued facts are independent.

Fifth Normal Form (5NF), or project-join normal form, concerns join dependencies that are not implied by candidate keys. It is relevant when a relation represents a complex fact that can be reconstructed from three or more projections, and decomposition prevents spurious combinations. 5NF is relatively uncommon in ordinary transactional designs but important in highly associative domains such as supplier–part–project agreements.

Normalisation should follow a dependency analysis rather than a mechanical sequence. Designers should identify candidate keys, establish a minimal cover of dependencies where useful, test lossless decomposition, and evaluate whether important dependencies remain enforceable.

Denormalisation and Performance Judgement

Denormalisation intentionally introduces controlled redundancy. It may involve storing a calculated total, copying a descriptive attribute, merging relations, creating summary tables, or maintaining a materialised view. The purpose is usually to reduce expensive joins, support reporting latency, simplify read-heavy access, or accommodate a data warehouse design.

The cost is increased write complexity and risk of inconsistency. A copied CustomerName must be updated whenever the authoritative name changes, unless it is deliberately treated as historical data. Denormalisation should therefore be evidence-based: measure workload, identify bottlenecks, document the authoritative source, define refresh or update procedures, and monitor data quality. Indexing, query optimisation, partitioning, caching, and materialised views should generally be considered before altering a well-designed logical schema.

Operational systems often favour normalised schemas, whereas dimensional analytical models commonly use denormalised star schemas. The correct design depends on workload, latency requirements, data volatility, governance, and operational capability.

Applied Case: Designing a University Research Administration Database

Background

A university research office manages researchers, projects, funding agencies, departments, publications, and project staff. A researcher may participate in several projects, and a project may involve researchers from multiple departments. Projects may receive several funding grants. The office also records each researcher’s areas of expertise and publications associated with projects.

Situation and Problem

The existing spreadsheet stores project details, investigator names, grant information, and expertise in repeated columns. Project names are copied across rows, and a researcher’s expertise is repeated for every project. Staff members report inconsistent grant totals and difficulty determining who was responsible for a project at a particular time.

Analysis

Candidate entities are Researcher, Department, Project, FundingAgency, Grant, and Publication. Researcher–Project is M:N and requires relationship attributes such as Role and StartDate. A project may have several grants, while each grant is issued by one agency. Researcher–Expertise is a multivalued relationship. Publication may be associated with several researchers and projects.

The model must also distinguish current project responsibility from historical participation. A rule stating that each project has exactly one principal investigator is different from the rule that many researchers may participate. These should not be collapsed into one vague relationship.

Approach and Schema

A suitable relational design is:

• Researcher(ResearcherID, Name, DepartmentID)
• Department(DepartmentID, DepartmentName)
• Project(ProjectID, Title, StartDate, EndDate, PrincipalInvestigatorID)
• ProjectStaff(ProjectID, ResearcherID, Role, AssignmentStart, AssignmentEnd)
• FundingAgency(AgencyID, AgencyName)
• Grant(GrantID, ProjectID, AgencyID, Amount, AwardDate)
• Expertise(ResearcherID, ExpertiseArea)
• Publication(PublicationID, Title, PublicationDate)
• ResearcherPublication(ResearcherID, PublicationID, ContributionRole)
• ProjectPublication(ProjectID, PublicationID)

Foreign keys enforce the associations. A uniqueness constraint on Project.PrincipalInvestigatorID is not appropriate because one researcher may lead many projects; instead, the foreign key ensures that the principal investigator exists. Check constraints can ensure nonnegative grant amounts and valid date ranges. ProjectStaff’s composite key prevents duplicate assignments, while temporal rules may require additional procedures to prevent overlapping leadership periods.

Outcome

The design removes repeated researcher and project data, permits reliable aggregation of funding, and preserves the distinction between project membership and principal responsibility. Expertise is separated because independent expertise values would otherwise create an MVD problem. Reports can join the normalised relations, while frequently used funding summaries may be exposed through a materialised view after performance measurement.

Learning Points

The case demonstrates that modelling begins with business rules, not table names. Relationship attributes must be placed where they belong, independent multivalued facts should not be forced into one relation, and historical requirements may require dates or temporal constraints. Normalisation improves integrity, but reporting performance can be addressed through controlled derived structures.

Common Errors, Governance, and Professional Practice

A common mistake is treating every noun in a requirements document as an entity. Some nouns are attributes, roles, or values. Another is confusing a many-to-many relationship with two independent one-to-many relationships without creating an associative relation. Designers also frequently omit optionality, assume that observed data proves a dependency, or use a surrogate key without retaining a required natural uniqueness constraint.

Null values deserve particular attention. A null may mean unknown, not applicable, not yet recorded, or intentionally withheld. These meanings are not equivalent. Excessive nulls may indicate that a supertype/subtype structure or separate relation is needed.

Database governance requires documented definitions, ownership of data elements, access controls, retention policies, auditability, and change management. Personal and sensitive data should be minimised, protected by appropriate authorisation and encryption, and retained only for legitimate purposes. Primary keys and foreign keys support integrity but do not by themselves provide confidentiality or lawful processing. Designers should also consider accessibility of data definitions, explainability of derived metrics, and the consequences of incorrect records.

Modern modelling tools can generate diagrams, schema definitions, migration scripts, and dependency documentation. Automated schema suggestions and AI-assisted code generation may accelerate routine work, but generated designs must be validated against domain rules, privacy requirements, and edge cases. A tool cannot infer an unstated business rule reliably. Version-controlled schema migrations, automated integrity tests, sample-data validation, and data-quality monitoring are valuable professional practices.

Emerging Directions

Contemporary database design increasingly spans relational, document, graph, and analytical platforms. Polyglot architectures may use different models for different workloads, but the underlying discipline of explicit entities, identifiers, dependencies, and governance remains relevant. Data products and lakehouse environments also require conceptual models, even when storage is distributed or schema enforcement is flexible.

Temporal databases, metadata catalogues, data lineage, privacy-enhancing techniques, and automated data-quality rules are becoming more important. Semantic layers for analytics seek to define measures and relationships consistently across dashboards. These developments do not replace normalisation; they expand the contexts in which designers must decide which structure is authoritative, which transformations are acceptable, and how meaning is preserved across systems.

Best-Practice Design Checklist

A professional design review should ask whether every important entity has a clear definition and identifier; whether relationship cardinalities and optionality reflect approved business rules; whether foreign keys and uniqueness constraints are enforceable; whether the schema has been tested for lossless decomposition; whether dependencies and candidate keys are documented; and whether sensitive attributes have appropriate protection.

The review should also test realistic insert, update, and delete scenarios. If changing one fact requires changes in many unrelated rows, redundancy may be excessive. If every important report requires complex, slow joins, workload analysis may justify a view, index, summary, or carefully documented denormalisation. Design decisions should be traceable to requirements and measurable operational needs.

Chapter Summary

Conceptual modelling converts organisational requirements into an understandable representation of entities, attributes, relationships, identifiers, and constraints. ER modelling handles ordinary structures, while EER modelling represents inheritance, categories, and aggregation. Mapping rules translate these constructs into relational schemas using primary keys, foreign keys, associative relations, and subtype strategies.

Functional dependencies explain how attributes determine one another and provide the foundation for identifying keys and redundancy. Normalisation progresses from 1NF through 2NF, 3NF, and BCNF, with 4NF addressing independent multivalued facts and 5NF addressing complex join dependencies. Lossless decomposition and dependency preservation are important quality criteria. Denormalisation may support performance, but it introduces maintenance and integrity costs and should be justified by evidence. Effective database design combines formal analysis with domain knowledge, governance, security, and continuous validation.

Key Terms

Entity: A distinguishable object, event, or concept about which data is stored.

Attribute: A property describing an entity or relationship.

Candidate key: A minimal attribute set that uniquely identifies a tuple.

Primary key: The candidate key selected as the principal identifier of a relation.

Foreign key: An attribute set referencing a key in another relation.

Weak entity: An entity whose identification depends on an owner entity.

Cardinality: The maximum number of instances participating in a relationship.

Participation: The minimum or mandatory involvement of an entity in a relationship.

EER model: An ER model extended with concepts such as specialisation and generalisation.

Specialisation: The division of a superclass into more specific subclasses.

Functional dependency: A constraint X → Y stating that X determines Y.

Multivalued dependency: A constraint X ↠ Y stating that Y values are independently associated with X.

Attribute closure: The set of attributes functionally determined by a given attribute set.

1NF: A normal form requiring atomic attribute values and no repeating groups.

2NF: 1NF plus the absence of partial dependency on a composite key.

3NF: 2NF plus the absence of problematic transitive dependencies.

BCNF: A normal form requiring every determinant of a nontrivial FD to be a superkey.

Lossless decomposition: Decomposition from which the original relation can be reconstructed without spurious tuples.

Dependency preservation: The ability to enforce dependencies using the decomposed relations without joins.

Denormalisation: Deliberate introduction of redundancy to meet performance or operational objectives.

Review Questions

Short Answer Questions

1. Distinguish conceptual, logical, and physical database design.

2. What is the difference between cardinality and participation?

3. Why is an associative relation required for an M:N relationship?

4. Define a functional dependency and give one example.

5. What problem does 4NF address?

Long Answer Questions

1. Explain the process of developing an ER model from organisational requirements.

2. Describe the principal strategies for mapping an EER specialisation to relational tables.

3. Explain 1NF, 2NF, 3NF, and BCNF using suitable dependency examples.

4. Discuss lossless decomposition and dependency preservation in relational design.

5. Evaluate the benefits and risks of denormalisation in operational and analytical systems.

Analytical / Application Questions

1. A relation StudentCourse(StudentID, StudentName, CourseID, CourseName, InstructorID, InstructorName, Grade) has a composite key of StudentID and CourseID. Identify likely dependencies and decompose the relation to at least 3NF.

2. A relation Person(Language, Hobby) stores all languages and hobbies for each person in every possible combination. Explain the dependency problem and propose a 4NF design.

3. A reporting system repeatedly joins eight normalised tables and fails its response-time target. Propose a structured decision process for determining whether indexing, materialised views, summary tables, or denormalisation is appropriate.

22. Multiple Choice Questions with Answers

Question 1

Which relation is normally created when two entity types participate in an M:N relationship?

A. A relation containing only the first entity's key

B. An associative relation containing the participating keys and relationship attributes

C. A relation containing only descriptive attributes

D. A weak entity with no foreign keys

Correct Answer: B. An associative relation containing the participating keys and relationship attributes

Question 2

If DepartmentID → DepartmentName and EmployeeID → DepartmentID, what kind of dependency does EmployeeID → DepartmentName represent?

A. Partial dependency

B. Multivalued dependency

C. Transitive dependency

D. Join dependency

Correct Answer: C. Transitive dependency

Question 3

Which condition is required by BCNF?

A. Every relation must have a single-attribute key

B. Every non-key attribute must be atomic

C. Every determinant of a nontrivial functional dependency must be a superkey

D. Every relation must contain at least two foreign keys

Correct Answer: C. Every determinant of a nontrivial functional dependency must be a superkey

Question 4

A student may have several independent languages and several independent hobbies. Which design best avoids repeating combinations?

A. Student(StudentID, Language, Hobby)

B. StudentLanguage(StudentID, Language) and StudentHobby(StudentID, Hobby)

C. Student(StudentID, Language1, Language2, Hobby1, Hobby2)

D. StudentHobby(StudentID, Language, Hobby)

Correct Answer: B. StudentLanguage(StudentID, Language) and StudentHobby(StudentID, Hobby)

Question 5

In mapping a 1:N relationship, where is the primary key of the one-side normally placed?

A. As a foreign key in the N-side relation

B. As a foreign key in a new relation only

C. As a multivalued attribute of the one-side

D. It is discarded after mapping

Correct Answer: A. As a foreign key in the N-side relation

Question 6

Which statement best describes a lossless decomposition?

A. It removes all foreign keys from the schema

B. It permits reconstruction of the original valid relation without spurious tuples

C. It guarantees that every dependency is preserved

D. It always produces the fewest possible relations

Correct Answer: B. It permits reconstruction of the original valid relation without spurious tuples

Question 7

A relation violates 2NF when a non-prime attribute depends on:

A. A complete candidate key only

B. Part of a composite candidate key

C. A foreign key that is also a superkey

D. A multivalued attribute only

Correct Answer: B. Part of a composite candidate key

Question 8

Which design choice is an example of controlled denormalisation?

A. Removing all integrity constraints

B. Splitting an M:N relationship into an associative relation

C. Maintaining a summary table for a frequently used, measured report

D. Replacing every primary key with a null value

Correct Answer: C. Maintaining a summary table for a frequently used, measured report

Question 9

What is the principal role of a subtype in an EER model?

A. To represent attributes or rules specific to a specialised category of a superclass

B. To eliminate all relationships from the model

C. To store only derived attributes

D. To guarantee that all instances belong to multiple categories

Correct Answer: A. To represent attributes or rules specific to a specialised category of a superclass

Question 10

Which statement about a surrogate key is most accurate?

A. It always replaces the need for business uniqueness constraints

B. It is an artificial identifier that may simplify identification but does not remove semantic rules

C. It can be used only for weak entities

D. It necessarily contains business meaning

Correct Answer: B. It is an artificial identifier that may simplify identification but does not remove semantic rules