Database Design
The course combines lectures with a 30-hour project. Students develop a database for a system of their choice, working individually or in teams. The lecturer acts as the client, reviewer, and consultant throughout the project.
The course covers requirements analysis, conceptual modelling, logical design, physical implementation, and verification. Students explain their assumptions, compare possible solutions, and justify their design decisions.
Course organisation
- Attendance during the first three weeks is mandatory.
- After the first three weeks, a maximum of five absences are allowed for lectures and 8 absences for the project.
-
- Consultation hours are published on my website; USOS also redirects there. Please check the website before coming.
- Project activities may be completed early. However, each activity has only one final submission attempt — repetitions and resubmissions are not allowed.
- The deadline for submissions mark the obligatory attendence for project classes.
- During project meetings, students present their progress, discuss open questions, and explain their design decisions.
Lectures
The lectures use a food-delivery system as a running example, showing how requirements and business rules affect the database design.
| Date | Topic |
|---|---|
| 5 October 2026 | Requirements and ER modelling |
| 12 October 2026 | Relational and object modelling |
| 19 October 2026 | Normalisation, model quality, and tools |
| 26 October 2026 | Conceptual and logical design |
| 9 November 2026 | Physical design, integrity, and verification |
| 17 November 2026 (Tuesday) | Non-relational design |
| 23 November 2026 | DBMS selection and database engineering |
| 30 November 2026 | Final lecture test |
Lecture assessment
- The final test includes scenario questions and questions requiring students to explain and justify database designs.
- The minimum passing test score is 50%.
- Up to 20 additional percentage points may be awarded for lecture activities.
- Detailed activity and grade-conversion rules will be published in Moodle.
Project
Students choose a domain and develop a database supporting a clearly defined set of user needs. Work begins with requirements and business rules, continues through modelling and implementation, and ends with verification and a demonstration.
The project consists of 30 academic hours, organised as 15 meetings of two academic hours each.
Proposed project activities
| Meeting | Activity | Expected result |
|---|---|---|
| 1 | Project proposal | System idea, problem statement, intended users, and project participants. |
| 2 | Domain analysis | Key workflows, use cases, business rules, and questions for the client. |
| 3 | Requirements and MVP | Scope, assumptions, requirements, and acceptance criteria. |
| 4 | Conceptual model | ER model with entities, attributes, relationships, cardinalities, and optionality. |
| 5 | Model review | Verification of the model against workflows, exceptional cases, and business rules. |
| 6 | Logical design | Relational schema, primary and foreign keys, and relationship mapping. |
| 7 | Normalisation | Analysis of dependencies, normalisation, and justification of design decisions. |
| 8 | Physical implementation | DBMS selection, executable DDL, data types, and integrity constraints. |
| 9 | Test data | Repeatable population scripts and realistic data covering relevant scenarios. |
| 10 | Queries | Meaningful queries supporting the defined workflows and information needs. |
| 11 | Transactions | Transaction scenarios, consistency requirements, and failure handling. |
| 12 | Verification | Tests of business rules, integrity constraints, and valid, invalid, and boundary cases. |
| 13 | Performance analysis | Execution plans, indexing experiments, and justification of a performance decision. |
| 14 | Final package | Documentation, editable source files, scripts, and database recreation instructions. |
| 15 | Demonstration and defence | Working database demonstration and explanation of the main design decisions. |
Calendar deadlines will be announced separately. Students may discuss their work during meetings and consultations before making the final submission of an activity.
Proposed project scope
The project should include:
- A coherent domain with sufficient complexity to demonstrate meaningful modelling decisions.
- One-to-many and many-to-many relationships, with cardinalities and optionality explained.
- A schema in third normal form, or a justified alternative.
- Primary keys, foreign keys, uniqueness constraints, checks, and mechanisms enforcing the relevant business rules.
- A working implementation in PostgreSQL or another relational DBMS agreed with the lecturer.
- Realistic test data, meaningful queries, transaction scenarios, verification, and performance analysis.
- Scripts and instructions allowing the database to be recreated and populated.
A frontend is optional.
Submitting project activities
The proposed submission format is:
- One PDF report describing the requirements, solution, important design decisions, and verification results.
- One archive containing the relevant editable model sources, DDL, population scripts, queries, transactions, tests, and execution instructions.
Submit one package per project team, identifying all participants and their contributions.
The submission destination and calendar deadlines will be published before the relevant activities are due.
Project assessment
The assessment criteria are:
- Consistency between requirements, models, and implementation.
- Quality of the conceptual and logical design.
- Correct enforcement of integrity constraints and business rules.
- Usefulness and correctness of queries and transactions.
- Quality of verification and performance analysis.
- Completeness and reproducibility of the submitted package.
- Ability to explain and defend the work.
Project point allocations, passing requirements, and grade-conversion rules will be announced at next page update.
Materials
Loading...