Database Design and Development: Fundamentals and Lifecycle

Lecturer and Course Assessment

• Lecturer: Ambrose Njeru, BSc, MSc (KCA University School of Technology).

• Assessment Breakdown: The total grade is $100\%$ comprised of a $50\%$ Continuous Assessment ($30\%$ from two CATS and $20\%$ from assignments) and a $50\%$ Final Examination.

• Industry Context: Over $80\%$ of enterprise applications are backed by a database. Design defects are $60-80\%$ cheaper to fix during modelling than after deployment; production-level defects cost $10 \times$ more to resolve than those caught in the design phase.

Database Systems Fundamentals

• Definition: A database is an organised, persistent, structured, shared, and integral collection of related data managed by a Database Management System (DBMS).

• DBMS Services: Provisions concurrency control, query optimisation, crash recovery, backup/restore, access control, and data integrity rules.

• Historical Eras:

  • $1960s$: Hierarchical/Network systems (IDS, IMS).

  • $1970s$: Relational theory established by E. F. Codd.

  • $1980s$: SQL and commercial RDBMS (SQL Server, Ingres).

  • $1990s$: Object-oriented (OODBMS) and data warehousing.

  • $2000s$: NoSQL movement (MongoDB, Cassandra, Redis, Riak).

  • $2010s+$: Cloud (Snowflake), NewSQL (Spanner), AI/ML integration, and Vector DBs.

Three-Schema Architecture

• External/View Level: How end users see specific subsets of data.

• Conceptual/Logical Level: The global schema for designers, defining entities, attributes, and relationships independent of physical storage.

• Internal/Physical Level: How data is stored in files, indexes, and blocks.

• Data Independence: Logical independence allows conceptual schema changes without affecting views, while physical independence allows storage changes without affecting global logic.

Database Design Lifecycle ($DDLC$)

• Stage 1: Requirements Analysis – Collecting facts via stakeholder interviews, workflow observation, and JAD workshops.

• Stage 2: Conceptual Design – Building technology-neutral pictures using Entity-Relationship Diagrams (ERD). Chen notation uses rectangles for entities, diamonds for relationships, and ovals for attributes.

• Stage 3: Logical Design – Mapping the conceptual model to the relational model using tables, columns, and keys while applying normalisation.

• Stage 4: Physical Design – Selecting data types, indexing (B-tree, hash, GIN/GIST), partitioning, and clustering on real hardware.

• Stage 5: Implementation and Beyond – Utilising DDL for schema creation, CRUD operations, performance tuning, and maintenance.

Relational Modelling and Normalisation

• Key Classifications:

  • Primary Key: A chosen candidate key that is NOT NULL, UNIQUE, and immutable.

  • Foreign Key: A column that references a primary key in another table.

  • Candidate Key: A minimal super key with no redundant columns.

  • Surrogate Key: System-generated identifier (UUID or auto-increment).

• Normalisation Phases:

  • $1NF$: Atomic values only; no repeating groups.

  • $2NF$: $1NF$ plus no partial dependencies on composite keys.

  • $3NF$: $2NF$ plus no transitive dependencies (non-key attributes depending on other non-key attributes).

  • $BCNF$: Advanced form where every determinant is a candidate key.

• Anomalies Fixed: Proper design prevents Insertion, Update, and Deletion anomalies.

Management and Operation

• Database Languages:

  • DDL (Definition): CREATE, ALTER, DROP, TRUNCATE.

  • DML (Manipulation): INSERT, UPDATE, DELETE.

  • DQL (Query): SELECT.

  • DCL/TCL (Control): GRANT, REVOKE, COMMIT, ROLLBACK.

• ACID Properties of Transactions:

  • Atomicity: All operations in a unit of work succeed or fail together.

  • Consistency: Moves the database from one valid state to another.

  • Isolation: Concurrent transactions do not observe intermediate states.

  • Durability: Once committed, data survives power or system failures.

Security and Emerging Trends

• Security Best Practices: Minimum privilege access, encryption at rest (TDE) and in transit (TLS), and maintaining an immutable audit trail.

• Backup Strategy: Adherence to the 3-2-1 rule (3 copies, 2 media types, 1 off-site).

• Modern Trends:

  • NewSQL: Distributed systems with full ACID (CockroachDB, YugabyteDB).

  • Vector Databases: Similarity search for AI embeddings (Pinecone, Weaviate).

  • Serverless: Auto-scaling, pay-per-query infrastructure (Neon, D1).

  • Time-series: Optimized for IoT and metrics (InfluxDB, TimescaleDB).