[Class Report] System Development (2nd Year) Week 35

~ Database Design (ER Diagrams) and Mapping to Persistence ~

In Week 35, we did a practical exercise to derive database (persistence) design from the class diagrams created last week. This is an important session where students design which classes become which tables and how relationships are defined, effectively shaping the backbone of a working system.

■ Teacher’s Intro: “Designing how to store the ‘boxes’ in memory onto disk”

Mr. Tanaka: “The data defined in classes will ultimately become tables and records. It’s not enough to just map UML attributes directly to tables – what matters is designing with operation in mind: normalization, indexes, transactions, and so on.”

■ Today’s Key Points (Summary)

• Define entities (tables) and relationships using an ER diagram.
• Reduce redundancy by keeping normalization (1NF–3NF) in mind.
• Design primary keys / foreign keys and referential integrity (FK constraints).
• Index design (placing indexes appropriately based on query frequency).
• Try simple migrations (CREATE TABLE / ALTER) and check behavior with sample data.
• Consider operations such as transactions, backups, and security (SQL injection countermeasures).

■ Exercise ①: Mapping UML → ER Diagrams

In groups, students discussed how to map class attributes to tables. Key points:

• Deciding what becomes an entity: If a class has its own lifecycle or is referenced by multiple objects, it should become a separate table (e.g., Book, Member, Loan).
• Handling many-to-many: Many-to-many relationships (e.g., Member and Book lending) are represented by a junction table (Loan).
• Normalizing attributes: Consider splitting addresses and multiple phone numbers into separate tables to eliminate repeating attributes.

Example: Simple ER Outline for a Library System

• members (id PK, name, member_id, created_at)
• books (id PK, title, author, is_reference, is_available)
• loans (id PK, member_id FK → members.id, book_id FK → books.id, borrowed_at, due_date, returned_at)

Student realization: “By introducing a Loan table, it becomes natural to handle lending history and overdue checks!”

■ Exercise ②: Checking Normalization (1NF → 3NF)

Each group checked whether their table design was properly normalized.

• 1NF: Columns hold atomic values and do not contain repeating columns (avoid patterns like phone1, phone2).
• 2NF: Eliminate partial functional dependency (be careful when using composite primary keys).
• 3NF: Non-key attributes should depend only on the key (derived data should be moved to another table or a view).

Example: If books contains a library_branch_name column, students noted that it is more appropriate to move it into a separate branches table.

■ Exercise ③: Build and Run with SQL (Migration and Sample Data)

In class, we wrote simple SQL, applied it to a learning DB environment, and checked how it behaved (assuming a SQLite / PostgreSQL training setup). Example used in class:

-- members table
CREATE TABLE members (
  id SERIAL PRIMARY KEY,
  member_id VARCHAR(20) UNIQUE NOT NULL,
  name VARCHAR(100) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- books table
CREATE TABLE books (
  id SERIAL PRIMARY KEY,
  title VARCHAR(200) NOT NULL,
  author VARCHAR(100),
  is_reference BOOLEAN DEFAULT FALSE,
  is_available BOOLEAN DEFAULT TRUE
);

-- loans table (junction table)
CREATE TABLE loans (
  id SERIAL PRIMARY KEY,
  member_id INTEGER NOT NULL REFERENCES members(id),
  book_id INTEGER NOT NULL REFERENCES books(id),
  borrowed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  due_date DATE,
  returned_at TIMESTAMP NULL
);

We then inserted sample data and used JOIN queries to verify behavior.

INSERT INTO members (member_id, name) VALUES ('M001', 'Tanaka');
INSERT INTO books (title, author) VALUES ('Harry Potter', 'J.K. Rowling');

-- Lending process (within a transaction)
BEGIN;
INSERT INTO loans (member_id, book_id, due_date) VALUES (1, 1, '2025-12-01');
UPDATE books SET is_available = FALSE WHERE id = 1;
COMMIT;

-- Get currently borrowed books
SELECT b.title, m.name, l.borrowed_at, l.due_date
FROM loans l
JOIN books b ON l.book_id = b.id
JOIN members m ON l.member_id = m.id
WHERE l.returned_at IS NULL;

Student reaction: “If we handle lending and stock updates within a transaction, even if something fails halfway, data consistency is preserved!”

■ Exercise ④: Basics of Indexes and Performance

Assuming larger data volumes after creating tables, we discussed which columns should have indexes.

• Consider indexes on frequently searched columns, such as books.title and members.member_id.
• Columns used in JOINs like loans.book_id and loans.member_id tend to benefit greatly from indexes.
• On the other hand, avoid unnecessary indexes on frequently updated columns or columns with low selectivity (e.g., boolean flags).

Simple SQL examples:

CREATE INDEX idx_books_title ON books(title);
CREATE INDEX idx_loans_member_id ON loans(member_id);

Student realization: “Indexes make reads faster, but they introduce a trade-off by slowing down writes.”

■ Exercise ⑤: Operational Considerations (Backups, Migrations, Security)

• Backups: Concepts such as regular dumps (pg_dump, etc.) and point-in-time recovery.
• Migrations: Schema changes should be phased (be careful with ALTER and prepare rollback plans).
• Security: Use parameterized queries with placeholders to prevent SQL injection (e.g., cursor.execute("SELECT ... WHERE id=%s", (id,))).
• Access control: Apply the principle of least privilege to DB users (e.g., read-only users).

Mr. Tanaka: “Even a great design is meaningless if data is lost or leaked during operations. Persistence is also about taking ‘ultimate responsibility’ for the data.”

■ Teacher’s Closing Comment

“Data design has a huge impact on application reliability and performance. If you design with the full flow in mind—from class diagrams to ER diagrams, from ER diagrams to DDL, and then to operations—implementation and testing both become much smoother. Next week, we’ll finalize the ER diagrams and have each group create actual migration scripts.”

■ Homework (For Next Class)

1. Submit your group’s final ER diagram (PNG/PDF) and the CREATE TABLE (DDL) scripts you created this week.
2. Propose at least three indexes across your tables and explain the reason for each (20–60 characters per explanation).
3. Write up a one-page document describing the rollback procedure in case of data loss (flow from backup to restore).

Week 35 was all about driving the design down to the level of persistence and gaining hands-on experience with practical SQL and operational considerations. Students deepened their understanding that “design = a set of promises” and that “the database is an asset that must be handled with care,” preparing themselves for the next implementation phase.