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

~ Implementing Migrations & Designing DAO: The Week That Connects DB and App ~

In Week 36, based on the ER diagram and DDL created last week, we worked on building the foundation for operating the database from the application.
The theme was migrations (creating tables) and DAO (data access layer) design.
This was a hands-on session to learn the “glue” process that bridges data design and implementation.

■ Teacher’s Introduction: “Code That Touches Data Must Be Careful and Deliberate”

Mr. Tanaka: “DAO is ‘boundary code’ between the app and the DB.
It must be designed with safety, readability, and resilience to change in mind.”

The teacher started by sharing a real example where mixing business logic and SQL made later modifications very difficult, and then explained why we use DAO (Repository pattern).

■ Key Takeaways of the Day (Summary)

• Execute the DDL to correctly create tables in the local / learning environment
• Separate CRUD (Create / Read / Update / Delete) into a DAO layer
• Always use placeholders to prevent SQL injection
• Clearly define transaction boundaries
• Standardize logging and exception handling to improve traceability in operations

■ Exercise ①: Running Migrations (DDL)

Each group executed the DDL they had created and checked whether the tables were created as expected.

Checklist

• Are PK and FK correctly defined?
• Are NOT NULL constraints enforced correctly?
• No missing UNIQUE or CHECK constraints?
• Do sample INSERTs work successfully?
• Do JOINs behave as expected?

Student A: “We forgot to set an FK, so the JOIN behaved weirdly… glad we caught it!”
Student B: “I realized that just applying constraints can prevent errors early.”

■ Exercise ②: DAO Design (Interface Definition)

Next, we discussed the class structure for DAOs (Data Access Objects).

Role of DAO (Teacher’s Explanation)

• Centralize the management of SQL
• Decouple business logic from the DB
• Make testing easier (easy to mock)
• Maintain a structure that doesn’t collapse as the application grows

Basic DAO Methods (Example: BooksDao)

• get(book_id)
• find_by_title(keyword)
• create(book)
• update(book)
• delete(book_id)

Student feedback: “It’s nice to have SQL in one place instead of scattered around. I’m starting to understand ‘separation of responsibilities’!”

■ Exercise ③: Implementing a DAO

We implemented CRUD operations in a learning environment using SQLite / PostgreSQL.

Example: BooksDao (Excerpt, for Learning)

class BooksDao:
    def __init__(self, conn):
        self.conn = conn

    def get(self, book_id):
        cur = self.conn.cursor()
        cur.execute("SELECT id, title, author, is_available FROM books WHERE id = %s", (book_id,))
        row = cur.fetchone()
        return row

    def create(self, title, author):
        cur = self.conn.cursor()
        cur.execute(
            "INSERT INTO books (title, author) VALUES (%s, %s) RETURNING id",
            (title, author)
        )
        new_id = cur.fetchone()[0]
        self.conn.commit()
        return new_id

Points of Attention in DAO Implementation

• Roll back transactions when exceptions occur
• Invalid data should be rejected before reaching the DAO (in the service layer)
• Any classes using caching should be placed outside the DAO (separation of responsibilities)
• Do not log raw SQL directly (security consideration)

■ Exercise ④: Collaboration with the Service Layer (Responsibility Separation)

Instead of calling DAOs directly from the UI, we practiced a structure that inserts a Service layer (business logic) in between.

Responsibilities of BooksService

• Check whether a book can be loaned out
• Handle exceptions for overdue items
• Apply validation to title searches
• Convert DAO return values into UI-friendly formats

Student C: “Actually writing it helped me understand where the boundary between DAO and service should be!”

■ Exercise ⑤: Mini Functional Test (Table → DAO → Service)

Finally, we ran a mini test with the following flow:

Insert sample data
→ Read via DAO
→ Apply logic in Service
→ Verify the result

Teams that encountered exceptions investigated the cause from the logs and fixed the issues on the spot.
Most errors were related to FK integrity and NULL constraints, which turned out to be a great learning opportunity.

■ Review and Feedback

• Good points: DAO responsibilities were respected, SQL was well organized, and rollbacks on exceptions were appropriately implemented.
• Areas for improvement: Return types were sometimes ambiguous; some teams had services growing too large.
• Teacher: “Ideally, DAOs are ‘thin,’ Services are ‘well-structured,’ and Controllers/UI are ‘minimal.’”

■ Teacher’s Closing Comment

“The design that connects the database and the application is a crucial part that directly affects quality.
The ‘careful boundary design’ you learned today will definitely help you in large-scale development as well.”

■ Homework (For Next Week)

1. Create and submit a list of DAO classes and their methods for each team.
2. Draw a simple class diagram for DAO and Service on a single sheet.
3. Prepare a processing flow diagram for one major use case, in preparation for next week’s implementation exercise.

■ Next Week’s Preview: Implementing Use Cases for the Whole App (Integration)

Next week we’ll connect the DAO and service layer with the actual UI (Web/CLI),
and move into implementation exercises to assemble the application into a “working system” per use case.

Week 36 was an intensive week of experiencing the bridge from “design → DB → implementation.”
Students began to understand full-fledged application architecture and are steadily preparing
for the next phase: “use case integration.”