Week 5 - Database design

Relational Databases

Primary Keys and Foreign Keys

Constraints

Database normalization

PostgreSQL setup

Joins

Java and databases

Practice

Assignment

• Teachers

Back end Track

Week 5 Assignment — Library database (Normalization → DDL → Java CRUD)

For this assignment, you will build a small library database from a messy export. On top of it, you’ll write a small application showing you can interact with the database using the JDBC. You will:

1. Normalize the data (1NF → 3NF)
2. Design relations (1:1, 1:N, N:M) + bridge tables
3. Add constraints (PK/FK/UNIQUE/NOT NULL/CHECK)
4. Write PostgreSQL DDL and create the database
5. Connect to the database with Java and JDBC CRUD

Task 1 — Normalize a library database

You are given a single export table called borrowings_export (imagine it came from a spreadsheet).

Columns:

• member_email
• member_name
• member_phone
• member_address
• card_number
• card_issued_on
• card_expires_on
• book_isbn
• book_title
• publisher_name
• author_names (semicolon-separated, e.g. Joshua Bloch; Martin Fowler)
• tag_names (semicolon-separated, e.g. java; best-practices)
• copy_barcode
• shelf_code
• borrowed_at
• due_date
• returned_at
• fine_eur

Here’s the full file:

library_loans.csv

Or you can find it on Github

Example rows:

Write DDL and create the database (deliverables: schema.sql)

From the export above, design a normalized schema in 3NF. Write PostgreSQL DDL to create your schema.

Must include:

• At least one 1:1 relationship
• At least one 1:N relationship
• At least two N:M relationships implemented with bridge tables

Include:

• PRIMARY KEY for every table
• FOREIGN KEY constraints for relationships
• UNIQUE constraints where sensible (e.g. member_email, card_number, book_isbn, copy_barcode, tag name)
• CHECK constraints where sensible (examples):
  • fine_eur >= 0
  • returned_at IS NULL OR returned_at >= borrowed_at
  • card_expires_on > card_issued_on

Then:

1. Create a database (e.g. library_db)
2. Run schema.sql

• 💡 Hints (you decide the exact design):

Task 2 — Java JDBC CRUD (deliverable: a minimal console demo)

Create a small Java program that demonstrates using JDBC to connect to the database and run few commands.

Requirements:

• Create a new JDBC connection to your database
• Create a new insertData() method:
  • Insert at least two rows to each table. You can use the CSV file as a reference.
• Create a new fetchData() method:
  • It will fetch and return a list of records from the database.
  • Use at least one join statement in your query.
• Call insertData() and fetchData() from your main function and display the results in console.
• Use JDBC + PreparedStatement everywhere

Submission

Follow the Assignment submission guide to submit

The HackYourFuture curriculum is licensed under CC BY-NC-SA 4.0 *https://hackyourfuture.net/*

Built with ❤️ by the HackYourFuture community · Thank you, contributors

Found a mistake or have a suggestion? Let us know in the feedback form.