Each student must design and implement a complete relational database using PostgreSQL. The submission must include a database schema definition file (DDL.sql), a data population file (DML.sql), at least one SQL view, at least one trigger, and at least one non-primary-key index. The final database design must satisfy Third Normal Form (3NF).
All required components must be present. Missing any component will result in loss of marks.
For the database schema component, the following deliverables are required.
Visual Database Schema Diagram: You must submit a drawn relational schema diagram representing the final database design. The diagram must display all tables and their columns. Primary keys and foreign keys must be clearly identified. Foreign key relationships must be visually represented using arrows or connectors. The diagram must be readable, unambiguous, and reflect the database structure used by the application.
DDL.sql File: You must submit a file named DDL.sql. This file must implement the database schema shown in the visual schema diagram. When executed on a fresh PostgreSQL database, the file must create the complete database structure required by the system, including tables, columns, keys, constraints, views, triggers, and indexes.
DML.sql File: You must submit a file named DML.sql. This file must populate the database with sample data sufficient to test all required application operations.
Normalization Evidence: You must provide evidence that the final database schema satisfies Third Normal Form (3NF). This evidence may be presented as a brief written explanation or annotated diagrams. The purpose is to justify the correctness of the final schema, not to document a normalization procedure.
The visual schema diagram and DDL.sql file must describe the same database structure. Any inconsistency between them will be treated as an error.
The database schema must be consistent with the ER model submitted for this project. The ER model and the database schema represent the same system design at different levels of abstraction. Any mismatch between the ER model and the implemented schema will be treated as a design error.
All data required by the application must be stored in the database. Required data must not be hard-coded in the application.
You must submit a file named DDL.sql that defines the complete database structure.
When executed on an empty PostgreSQL database, DDL.sql must create the entire schema required by the system, including tables, constraints, views, triggers, and indexes. The file must execute without errors and must not require manual modification.
The schema must include appropriate tables and attributes to support the system requirements. Entity identity, data validity, and referential integrity must be enforced at the database level. Attribute data types must be suitable for the stored data.
Designs that rely on application logic to enforce data correctness instead of database constraints are not acceptable.
The DDL.sql file must be self-contained. Running the file on a clean PostgreSQL database must fully initialize the schema.
You must submit a file named DML.sql containing sample data.
The purpose of this file is to populate the database with realistic data that allows the required application operations to be demonstrated.
The sample data must cover all tables and respect all defined constraints. The dataset must be sufficient to demonstrate normal system behavior as well as constraint enforcement.
The DML.sql file must execute successfully immediately after DDL.sql on a clean database.
The database must demonstrate the use of SQL features beyond basic table creation.
At least one SQL view must be defined. The view must provide information that is meaningful for the application and must not simply replicate a single table. The view must be used during application execution.
At least one trigger must be implemented. The trigger must automatically enforce a data consistency or correctness rule in response to a database modification.
At least one non-primary-key index must be defined. The index must be relevant to application queries and improve performance for at least one operation.
The final database schema must satisfy Third Normal Form (3NF). The schema must avoid unnecessary redundancy and unintended dependencies among attributes.
Schemas that introduce avoidable redundancy, store derived values, or combine unrelated concepts in the same table will be penalized.
Data integrity must be enforced at the database level. Appropriate constraints must be used to ensure correctness of stored data. Business rules that affect data validity must not rely solely on application code.
Both DDL.sql and DML.sql must be executable on a fresh PostgreSQL database. SQL must be clearly written and readable. All required database features must be actively used by the application.