Skip to main content

Design a SQL DB with DBeaver

Guided practical activity: Design your first relational database in SQL with DBeaver​

Objective​

Create a simple database from scratch to understand how basic concepts are applied such as: data types, primary and foreign keys, best practices in design, and simple SQL queries.

Example scenario​

You are going to model a database for a digital library, where you must record:

  • User information
  • Available books
  • Loans made

This will help you understand how different entities (users, books, and loans) relate to each other and how they are structured in SQL.

STEP-BY-STEP EXPLANATION​

1. Create the database in DBeaver​

Instruction:

CREATE DATABASE library_db;

Why? This step creates the main container where your tables will be. This way you keep your information organized and separated from other databases.

2. Select the database you are going to use​

USE library_db;

Why? Tells the system that all following operations will be performed on this database.

3. Create tables with best practices​

Table: users​

CREATE TABLE users (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(50),
  email VARCHAR(100) UNIQUE
);

Why is it used this way?

  • id is the primary key, uniquely identifying each user.
  • AUTO_INCREMENT allows a new ID to be automatically generated.
  • email is UNIQUE to prevent the same email from being repeated in multiple records.

Table: books​

CREATE TABLE books (
  id INT PRIMARY KEY AUTO_INCREMENT,
  title VARCHAR(100),
  author VARCHAR(50),
  publication_year INT,
  available BOOLEAN
);

Why are these types chosen?

  • title, author: text of reasonable length.
  • publication_year: an integer, not a full date.
  • available: boolean field (yes/no) indicating if the book can be loaned.

Table: loans​

CREATE TABLE loans (
  id INT PRIMARY KEY AUTO_INCREMENT,
  user_id INT,
  book_id INT,
  loan_date DATE,
  returned BOOLEAN,
  FOREIGN KEY (user_id) REFERENCES users(id),
  FOREIGN KEY (book_id) REFERENCES books(id)
);

What do you learn here?

  • How to use foreign keys to connect this table with the others.
  • loan_date: saved with DATE type.
  • returned: indicates if the book was returned.

4. Insert test data​

INSERT INTO users (name, email)
VALUES ('Camila Lopez', 'camila@example.com'),
       ('Pedro Ruiz', 'pedro@example.com');

INSERT INTO books (title, author, publication_year, available)
VALUES ('1984', 'George Orwell', 1949, true),
       ('The Little Prince', 'Antoine de Saint-Exupery', 1943, true);

INSERT INTO loans (user_id, book_id, loan_date, returned)
VALUES (1, 2, '2025-07-20', false),
       (2, 1, '2025-07-21', true);

Why do it? This test data allows you to visualize how the database works with real information. Without it, you cannot make queries or validate if the relationships are well made.

5. Make simple queries​

View all available books​

SELECT * FROM books WHERE available = true;

You learn to: filter data according to conditions.

View loans of a specific user​

SELECT u.name, b.title, l.loan_date, l.returned
FROM loans l
JOIN users u ON l.user_id = u.id
JOIN books b ON l.book_id = b.id
WHERE u.name = 'Camila Lopez';

You learn to:

  • Use JOIN to join multiple tables.
  • Relate entities using foreign keys.
  • Query combined information from different sources.

6. (Optional) Create the visual model of the schema​

You can use DBeaver's visual tool or a platform like dbdiagram.io to graphically represent your database design.

Why is it useful?

  • Reinforces understanding of relationships between tables.
  • Allows you to easily detect design errors.
  • It is a common practice in real projects.

Expected result​

At the end of this activity you should have:

  • A functional database with three related tables.
  • Example data correctly inserted.
  • Ability to make simple queries.
  • Clarity on the data types used, primary and foreign keys, and the general design of your schema.