Database Design Example | MySQL (Part 3)

5 min readJul 28, 2022

Continuation of the article “Database Design Example | MySQL (Part 2)”.

3. Formation of queries to be DBMS

3.1 Creating tables in the database and setting indexes

To create tables in accordance with a given database schema in MySQL DBMS, you can use queries, automatically generated according to the database schema in the MySQL Workbench environment (here the database is called the library):

DROP SCHEMA IF EXISTS `library` ;

CREATE SCHEMA IF NOT EXISTS `library` DEFAULT CHARACTER SET utf8 ;
SHOW WARNINGS;
USE `library` ;

DROP TABLE IF EXISTS `library`.`administrators` ;

CREATE TABLE IF NOT EXISTS `library`.`administrators` (
  `id` INT NOT NULL,
  `logins` VARCHAR(45) NOT NULL,
  `password` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;

DROP TABLE IF EXISTS `library`.`readers` ;

CREATE TABLE IF NOT EXISTS `library`.`readers` (
  `id` INT NOT NULL,
  `name` VARCHAR(45) NOT NULL,
  `passport` VARCHAR(45) NOT NULL,
  `address` VARCHAR(45) NOT NULL,
  `phone` VARCHAR(45) NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;

DROP TABLE IF EXISTS `library`.`rooms` ;

CREATE TABLE IF NOT EXISTS `library`.`rooms` (
  `id` INT NOT NULL,
  `name` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;

DROP TABLE IF EXISTS `library`.`librarians` ;

CREATE TABLE IF NOT EXISTS `library`.`librarians` (
  `id` INT NOT NULL,
  `login` VARCHAR(45) NOT NULL,
  `password` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;

DROP TABLE IF EXISTS `library`.`books` ;

CREATE TABLE IF NOT EXISTS `library`.`books` (
  `id` INT NOT NULL,
  `author` VARCHAR(45) NOT NULL,
  `publication_year` INT NOT NULL,
  `publisher` VARCHAR(45) NOT NULL,
  `name` VARCHAR(45) NOT NULL,
  `isbn` VARCHAR(45) NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;

DROP TABLE IF EXISTS `library`.`librarian_rooms` ;

CREATE TABLE IF NOT EXISTS `library`.`librarian_rooms` (
  `id_room` INT NOT NULL,
  `id_librarian` INT NOT NULL,
  PRIMARY KEY (`id_room`, `id_librarian`),
  INDEX `id_librarian_idx` (`id_librarian` ASC),
  CONSTRAINT `id_lr_room`
    FOREIGN KEY (`id_room`)
    REFERENCES `library`.`rooms` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `id_lr_librarian`
    FOREIGN KEY (`id_librarian`)
    REFERENCES `library`.`librarians` (`id`)
    ON DELETE NO ACTION
    ON UPDATE CASCADE)
ENGINE = InnoDB
DEFAULT CHARACTER SET = tis620
COLLATE = tis620_bin;

DROP TABLE IF EXISTS `library`.`booking_cards` ;

CREATE TABLE IF NOT EXISTS `library`.`booking_cards` (
  `id` INT NOT NULL,
  `id_reader` INT NOT NULL,
  `id_book` INT NOT NULL,
  `id_librarian` INT NOT NULL,
  `time` DATETIME NOT NULL,
  `period` DATETIME NULL,
  PRIMARY KEY (`id`),
  INDEX `id_reader_idx` (`id_reader` ASC),
  INDEX `id_book_idx` (`id_book` ASC),
  INDEX `id_librarian_idx` (`id_librarian` ASC),
  CONSTRAINT `id_bc_reader`
    FOREIGN KEY (`id_reader`)
    REFERENCES `library`.`readers` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `id_bc_book`
    FOREIGN KEY (`id_book`)
    REFERENCES `library`.`books` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `id_bc_librarian`
    FOREIGN KEY (`id_librarian`)
    REFERENCES `library`.`librarians` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

DROP TABLE IF EXISTS `library`.`issue_cards` ;

CREATE TABLE IF NOT EXISTS `library`.`issue_cards` (
  `id` INT NOT NULL,
  `id_reader` INT NOT NULL,
  `id_book` INT NOT NULL,
  `time` DATETIME NOT NULL,
  `period` DATETIME NULL,
  `issue_cardscol` VARCHAR(45) NULL,
  PRIMARY KEY (`id`),
  INDEX `id_reader_idx` (`id_reader` ASC),
  INDEX `id_book_idx` (`id_book` ASC),
  CONSTRAINT `id_ic_reader`
    FOREIGN KEY (`id_reader`)
    REFERENCES `library`.`readers` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `id_ic_book`
    FOREIGN KEY (`id_book`)
    REFERENCES `library`.`books` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

DROP TABLE IF EXISTS `library`.`book_places` ;

CREATE TABLE IF NOT EXISTS `library`.`book_places` (
  `id_book` INT NULL,
  `id_room` INT NOT NULL,
  `quantity` INT NOT NULL,
  `shell_number` INT NOT NULL,
  PRIMARY KEY (`id_book`, `id_room`),
  INDEX `id_room_idx` (`id_room` ASC),
  CONSTRAINT `id_bp_book`
    FOREIGN KEY (`id_book`)
    REFERENCES `library`.`books` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `id_bp_room`
    FOREIGN KEY (`id_room`)
    REFERENCES `library`.`rooms` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

The above query was successfully executed on the server, tables created. Note the naming constraints for foreign keys, they must be unique in the database. Therefore, the name of the relation for which it is described is encoded in the constraint name, for example — CONSTRAINT `id_ic_reader`
sets the limit on the id_reader field in relation to issue_cards.

All key fields in the database are indexed — due to this, records are ordered by these fields and the search is faster (binary search instead of linear).

3.2 Designing the most demanded requests

Before creating queries, MySQL Server was installed and running, configured to connect to this MySQL Workbench server. Data has been added to the database to check the correctness of query execution. The addition was made using MySQL Workbench, as a result the following queries were generated:

INSERT INTO `library`.`administrators` (`id`, `logins`, `password`) VALUES (1, 'tom', '12345');
INSERT INTO `library`.`administrators` (`id`, `logins`, `password`) VALUES (2, 'jack', '54321');

INSERT INTO `library`.`readers` (`id`, `name`, `passport`, `address`, `phone`) VALUES (1, 'Lina', '0402 892322', 'New Jersey, Borrow st.', '214 34 12');
INSERT INTO `library`.`readers` (`id`, `name`, `passport`, `address`, `phone`) VALUES (2, 'Kerry', '4561 455311', 'Spb, Mira 11', '8 909 999 99 99');

INSERT INTO `library`.`rooms` (`id`, `name`) VALUES (1, 'Room С++');
INSERT INTO `library`.`rooms` (`id`, `name`) VALUES (2, 'Design room');

INSERT INTO `library`.`librarians` (`id`, `login`, `password`) VALUES (1, 'bob', '11111');
INSERT INTO `library`.`librarians` (`id`, `login`, `password`) VALUES (2, 'lola', '22222');

INSERT INTO `library`.`books` (`id`, `author`, `publication_year`, `publisher`, `name`, `isbn`) VALUES (1, ' E. Gamma, R. Helm, R. Jonson', 2009, 'SPB.: New York', 'Example ОО- design', NULL);
INSERT INTO `library`.`books` (`id`, `author`, `publication_year`, `publisher`, `name`, `isbn`) VALUES (2, 'Jayson Malcolm Smith', 2013, 'Williams', 'Elementary design patterns', NULL);
INSERT INTO `library`.`books` (`id`, `author`, `publication_year`, `publisher`, `name`, `isbn`) VALUES (3, 'Stiven Prata', 2020, 'Williams', 'Programming language C++ (C++11). ', NULL);
INSERT INTO `library`.`books` (`id`, `author`, `publication_year`, `publisher`, `name`, `isbn`) VALUES (4, 'Mayer S.', 2014, 'DMK Press', 'Effective use С++', NULL);
INSERT INTO `library`.`books` (`id`, `author`, `publication_year`, `publisher`, `name`, `isbn`) VALUES (5, 'Andrew Aleksandrewsky', 2002, 'Williams', 'Modern C++ Design.', NULL);

INSERT INTO `library`.`librarian_rooms` (`id_room`, `id_librarian`) VALUES (1, 2);
INSERT INTO `library`.`librarian_rooms` (`id_room`, `id_librarian`) VALUES (2, 1);

INSERT INTO `library`.`book_places` (`id_book`, `id_room`, `quantity`, `shell_number`) VALUES (1, 1, 10, 555);
INSERT INTO `library`.`book_places` (`id_book`, `id_room`, `quantity`, `shell_number`) VALUES (1, 2, 5, 333);
INSERT INTO `library`.`book_places` (`id_book`, `id_room`, `quantity`, `shell_number`) VALUES (2, 1, 4, 111);
INSERT INTO `library`.`book_places` (`id_book`, `id_room`, `quantity`, `shell_number`) VALUES (3, 2, 60, 222);

INSERT INTO `library`.`booking_cards` (`id`, `id_reader`, `id_book`, `id_librarian`, `time`, `period`) VALUES (1, 1, 1, 2, '2019-10-20', '2019-11-20');

When preparing these data, the problem of the developed database was revealed — not all book titles fit in 45 characters. I had to shorten the names. Also, due to the fact that users are distributed over three tables, when creating a new user, it is necessary to check the absence of a login in all tables. To get books by filter, queries like this should be executed:

select * from books where name like '%C++%'

To search for debtors, you can run the following query:

select rd.* from readers rd, booking_cards bc 
where rd.id = bc.id_reader and bc.period < '2021-10-20';

Instead of a constant, the current date must be supplied.

To display the books owed by a particular user, you can run the following query:

select bk.* from booking_cards bc, books bk 
where 
    bk.id = bc.id and 
    bc.period < '2021-10-20' and 
    bc.id_reader = 1;

For this request, the information system must first find the user in the database (get his id) and substitute this value instead of 1, instead of the date constant, the current date should be substituted.

This is the last part of the article “Database Design Example | MySQL (Part 1)”.

That’s all. I hope it was helpful…

Database Design Example | MySQL (Part 3)

3. Formation of queries to be DBMS

3.1 Creating tables in the database and setting indexes

3.2 Designing the most demanded requests

Written by Islam Ataballyyev