Relational Database

Relational database is a data store that represents data in a table like a format based on the relational model of data, as proposed by E. F. Codd in 1970. A software system used to maintain relational databases is a relational database management system (RDBMS). Virtually all relational database systems use SQL (Structured Query Language) for querying and maintaining the database.

Relational Database Management System

A relational database management system (RDBMS) is a database management system (DBMS) that is based on the relational model invented by Edgar F. Codd, of IBM's San Jose Research Laboratory.

A relational database management system can have many relational databases in it, a relational database can have many tables in it, a table can have numbers of rows(tuple) and columns(fields or attribute) in it and each column in a table can store only a particular type of data in it.

Database schema

The database schema of a database system is its structure described in a formal language supported by the database management system (DBMS). The term "schema" refers to the organization of data as a blueprint of how the database is constructed (divided into database tables in the case of relational databases). The formal definition of a database schema is a set of formulas (sentences) called integrity constraints imposed on a database. These integrity constraints ensure compatibility between parts of the schema. All constraints are expressible in the same language.

---

Question: how do you structure data in Java?

Answer: You write a java class that store the data of object of that class.

Let's understand how object model data is storing into the relational model data with example. Here in above diagram, we have book class which has isbn of type String So only string type of data can be assigned to the isbn attribute.
We structure the data in relational database management system which is designed to manage data in a relational database is quite different than the way we do it in java.

Object model data mapping to Relational model data

To store data into relational database we first need to create the database then we need to create the table structure same as we have created using the java classes.
We define structure of tables for data. We store data into tables using database schema. We use SQL to write database schema. SQL is a structure query language use to manage data in a relational database management system.

public class Book {

  private Long id;
  private String isbn;
  private String title;

  public Long getId() {
    return id;
  }

  public void setId(Long id) {
    this.id = id;
  }

  public String getIsbn() {
    return isbn;
  }

  public void setIsbn(String isbn) {
    this.isbn = isbn;
  }

  public String getTitle() {
    return title;
  }

  public void setTitle(String title) {
    this.title = title;
  }
}

CREATE TABLE BOOK (
        ID BIGINT(20) NOT NULL,
        ISBN VARCHAR(13) NOT NULL UNIQUE,
        TITLE VARCHAR(25) NOT NULL,
        PRIMARY KEY(ID)
)
    

This is a simple database schema with a book table in a relational database, we have SQL data type in relational database just like object-oriented language java has data type. We have three columns in BOOK table ID, ISBN, and TITLE columns, ID column store only big int type of data in it, similarly ISBN column and TITLE column store only VARCHAR type of data in it. The schema of BOOK table defines the types of columns data storing into them.

The SQL data type depending on the relational database management system we are using. For example MySQL and SQLite both are relational database management system but DATETIME SQL data type available only in MySQL.

Entity and Referential Integrity

While representing the data in a table like format a relational database follows two fundamental rule, first is Entity Integrity says that each table has a primary key in it and null values are not valid for that key and second is Referential Integrity, In a relational database two tables are associated with each other with the help of a foreign key and its point at a value that is primary key of another table.

For example here we have two tables BOOK table and PUBLISHER table, BOOK table is associated with PUBLISHER table with the help of PUBLISHER_ID column which is defined as foreign key to PUBLISHER table and It is referring to ID column of PUBLISHER table which is primary key of PUBLISHER table. So here PUBLISHER_ID column is the foreign key of the PUBLISHER table.

Another thing that the referential integrity rule says that null values are valid values of foreign key column but if a value exists in a foreign key column that it must refer to valid reference. So here we have null value for the publisher_id column which is the foreign key of the publisher table but for the value 1 and 2 in it foreign key column we have a valid reference in the Publisher table with value 1 and 2 in its primary key column.

Let's suppose PUBLISHER table does not have 2nd row with ID 2 then, in that case, it would not have been following the referential integrity because the value 2 in the foreign key column PUBLISHER_ID does not have a valid reference to PUBLISHER table anymore.