Version Control GitHub Collaboration Branching Workflow

Week 4 Reading

Database Design and Integration

Study how relational databases are structured, how ER models guide schema design, how normalization improves consistency, how SQL reads and changes data, and how Spring Data JPA connects Java applications to the database.

Explore Topics Open Presentation
Topics5Core collaboration concepts
Objectives15Learning goals across the week
Review Prompts10Questions for revision
Workflow Stages4Plan, branch, review, merge

Week 4 Content

Relational design, SQL, and Java database integration

Each topic combines objectives, discussion notes, a diagram, an applied example, and review questions so the book can support reading and teaching.

Git, GitHub, branching, workflow
Topic 4.1 3 deep dives 2 review questions

Introduction to Relational Databases

Relational databases organize data into structured tables so applications can store, relate, and retrieve information consistently.

Topic Build
Objectives3
Deep dives3
Review prompts2
Learning Objectives
  • Explain what a relational database is and why it is widely used.
  • Describe tables, rows, columns, keys, and relationships.
  • Recognize how relational structure supports consistency and reuse.
Discussion Notes
01

A relational database stores data in tables, where each table represents one type of thing such as students, courses, or orders.

02

Rows represent individual records, while columns describe the fields stored for each record.

03

Primary keys identify records uniquely, and foreign keys connect related tables together.

04

Relational structure helps applications keep data organized, searchable, and less repetitive.

Team Insights

Each table should focus on one clear subject. When tables are designed well, they can be linked by keys rather than by repeating the same information in many places.

Keys keep records identifiable and connected. Database constraints use those keys to prevent broken references and accidental duplication.

A relational database gives teams a predictable way to model business data, query it, and evolve it as the application grows.
Workflow Diagram Click to zoom
Relational Model StudentsStudentID PKName, Email EnrollmentsEnrollmentID PKStudentID FK CoursesCourseID PKTitle, Credits 1 *
Applied Example

A student portal keeps students, modules, and registrations in separate related tables so each registration points to the right student and course without copying the same details again.

Review Questions
  1. Why are tables a good way to organize application data?
  2. How do primary keys and foreign keys work together?
Key Takeaways
  • Relational databases store data in tables.
  • Keys connect related records safely.
  • A good table design reduces repetition.
Topic 4.2 3 deep dives 2 review questions

ER Modeling

Entity-relationship modeling shows the objects in a system, the details they carry, and how those objects are related before the database is built.

Topic Build
Objectives3
Deep dives3
Review prompts2
Learning Objectives
  • Describe entities, attributes, and relationships in an ER model.
  • Explain cardinality such as one-to-one, one-to-many, and many-to-many.
  • Use ER diagrams to plan a database before implementation.
Discussion Notes
01

An entity is a real-world thing the database must store, such as a student, lecturer, product, or order.

02

Attributes describe the data about an entity, such as name, email, price, or date.

03

Relationships describe how entities connect, such as one student enrolling in many courses.

04

ER modeling helps the team think clearly about structure before writing SQL tables.

Team Insights

ER modeling starts by naming the core things in the system and the information each thing needs to store. That keeps the schema focused on real business needs.

Cardinality explains how many records can connect. Knowing whether a relationship is one-to-one, one-to-many, or many-to-many affects the final table design.

Once the ER model is clear, the team can translate entities into tables and relationships into foreign keys or junction tables.
Workflow Diagram Click to zoom
ER Diagram StudentStudentID, Name EnrollmentLinks students and courses CourseCourseID, Title 1 1 * *
Applied Example

A library system models Book, Member, and Loan. The ER diagram shows that one member can have many loans and one loan belongs to one book and one member.

Review Questions
  1. What is the difference between an entity and an attribute?
  2. Why is cardinality important in ER modeling?
Key Takeaways
  • ER models describe the data before implementation.
  • Cardinality guides table relationships.
  • A clear model makes schema design easier.
Topic 4.3 3 deep dives 2 review questions

Database Normalization

Normalization organizes tables so repeated data is reduced, update errors are limited, and the database becomes easier to maintain.

Topic Build
Objectives3
Deep dives3
Review prompts2
Learning Objectives
  • Explain why normalization reduces duplication and inconsistency.
  • Describe first, second, and third normal form at a basic level.
  • Identify when a table can be split into smaller related tables.
Discussion Notes
01

Normalization reduces repeated data by placing each fact in the most appropriate table.

02

First normal form removes repeating groups and keeps each field atomic.

03

Second normal form removes partial dependency on a composite key.

04

Third normal form reduces transitive dependency so non-key data depends only on the key.

Team Insights

When the same value appears in many rows, updates become risky because one change might not reach every copy. Normalization lowers that risk.

The goal is not to memorize every rule blindly, but to notice when a table mixes too many ideas and needs to be divided into cleaner parts.

Good design balances normalization with query needs. In practice, most systems normalize well first and only denormalize when there is a measured reason.
Workflow Diagram Click to zoom
Normalization Stages UnnormalizedRepeating data 1NFAtomic values 2NFNo partial dependency 3NFNo transitive dependency
Applied Example

A registration table that repeats student name, course title, and lecturer details can be split so the student, course, and lecturer data live in their own tables.

Review Questions
  1. What problem does normalization try to solve?
  2. Why should a table avoid repeating the same fact many times?
Key Takeaways
  • Normalization reduces redundancy.
  • Smaller focused tables are easier to maintain.
  • A normalized design protects data consistency.
Topic 4.4 3 deep dives 2 review questions

SQL Basics

SQL is the language used to create tables, insert records, query data, and update database content.

Topic Build
Objectives3
Deep dives3
Review prompts2
Learning Objectives
  • Recognize the core SQL commands used in everyday database work.
  • Write simple SELECT queries with filters and sorting.
  • Understand how CRUD operations map to SQL statements.
Discussion Notes
01

SELECT reads data from a table, while INSERT adds new rows, UPDATE changes rows, and DELETE removes rows.

02

WHERE filters rows, ORDER BY sorts them, and LIMIT reduces the number returned.

03

JOIN combines data from related tables so one query can read connected information.

04

SQL is declarative, which means you describe what data you want rather than how to fetch it step by step.

Team Insights

SELECT is the starting point for most database queries. Adding WHERE, ORDER BY, and JOIN helps you shape the result into something useful.

INSERT, UPDATE, and DELETE are the core commands for data changes. They must be used carefully because they directly affect stored information.

Almost every database-backed application depends on SQL either directly or through an abstraction layer such as an ORM or repository system.
Workflow Diagram Click to zoom
SQL Query Flow SELECTRead rows WHEREFilter rows JOINCombine tables CRUDCreate, update, delete
Applied Example

A course search page uses SELECT with WHERE clauses to show only modules from a selected semester, then JOINs the module table to the lecturer table.

Review Questions
  1. What does SELECT do?
  2. How does JOIN help a query read related data?
Key Takeaways
  • SQL is the standard database query language.
  • CRUD actions map to INSERT, SELECT, UPDATE, and DELETE.
  • JOIN lets queries combine related tables.
Topic 4.5 3 deep dives 2 review questions

Spring Data JPA Overview

Spring Data JPA simplifies Java database access by building on JPA entities and repository interfaces so applications can persist data with less boilerplate.

Topic Build
Objectives3
Deep dives3
Review prompts2
Learning Objectives
  • Explain what Spring Data JPA adds to a Java application.
  • Recognize the role of entities, repositories, and queries.
  • Understand how repository abstractions speed up database integration.
Discussion Notes
01

JPA defines a standard way to map Java objects to database tables.

02

Spring Data JPA builds on JPA and adds repository interfaces with ready-made CRUD operations.

03

Developers can declare custom queries or rely on query methods for common lookup patterns.

04

This approach reduces repetitive data-access code and keeps the application easier to maintain.

Team Insights

Entities describe the data model, while repositories provide the methods used to save, find, update, and delete that data.

Spring Data JPA removes a lot of repetitive JDBC code, which lets developers focus more on business rules and less on low-level database wiring.

A typical application uses controller, service, and repository layers so data access stays separate from user interface and business logic.
Workflow Diagram Click to zoom
Spring Data JPA Flow EntityMaps to table RepositoryCRUD methods ServiceBusiness logic DatabasePersist data
Applied Example

A web system stores Student and Course entities, then uses a StudentRepository to save student records and look them up by matric number.

Review Questions
  1. What does Spring Data JPA help reduce in a Java project?
  2. Why are repositories useful for database access?
Key Takeaways
  • JPA maps Java objects to relational tables.
  • Spring Data JPA provides repository-based data access.
  • The approach reduces boilerplate and improves structure.
7 questions True / False Auto-marked

Week 4 Quiz

Enter your details, start the quiz, and then review the score with the correct answers shown underneath.

Quiz Rules
Total items7
Pass mark70%
GradingInstant

Enter your Name and Matric No, then press Start Quiz to begin the attempt.

The quiz stays hidden until you start it, just like Quiz 1.

Press Start Quiz to open the questions and begin the attempt.