Relational Databases

—

A relational database is a collection of data that is managed and maintained in a database management system such as Oracle or MySQL. A relational database enables you to retrieve a stored data, by specifying the name of the table, the name of the column, the primary key of the table.

CREATE DATABASE automobile;
Use automobile;
CREATE TABLE vehicle (
    vehicleID VARCHAR (10),
    ownerID VARCHAR (10),
    plateNumber VARCHAR (10),
    phoneNumber INT,
    primary key (vehicleID)
)
Show tables;
Show columns from vehicle;

Keys

The primary key is what we use to identify the table. A primary key must contains a unique instance value in each row of the table. There can be various candidate keys for this primary key. Any candidate key, not chosen as the primary key of the table, is called an alternate key. A primary key can be composed of one or multiple attributes (a composite key). When there is no single attribute that can be found to act as a primary key, we may create a key asociating two or more attributes, so it composes a unique key. A foreign key is one or more columns used to connect two tables in order to create cross-referencing between them.

  • Primary key: vehicleID
  • Alternate key: plateNumber or phoneNumber
  • Foreign key: ownerID

Constraints

In the relational model, every relation needs to meet 3 conditions, called relational integrity constraints, to be valid.
Key constraints: a key attribute refers to a record, it must be unique and cannot have NULL values.
Domain constraints: store the value with the correct data type (numeric, text, etc).
Referential integrity constraints: a database has multiple tables that refer to one another, and this is the foreign key (the primary key of another table), that states how these two tables are related. The referential integrity constraint consists in making sure the two values of this attribute match in the two tables.

Data normalization

A database must be easy to access and query, well-structured, well-organized, consistent, and without data duplications. Normalization is a process that aims to make the database more efficient, to reduce the database storage space, and to ensure the queries on a database run as fast as possible. Therefore, we need to make sure that the data is accurate, that the same data is not stored in more than one place (insert anomaly), that updating data does not require further updates (update anomaly), and that deleting one record won't delete other required data sets (delete anomaly).

First Normal Form (1NF)

The goal is to enforce the data atomicity rule and eliminate repeating data groups. Data atomicity means that in any table cell, there is one single instance value of the column attribute.

Second Normal Form (2NF)

The goal is to remove partial dependency relationships between data. Partial dependency is when a non-key attribute value depends only on one part of the composite primary key.

Third Normal Form (3NF)

The goal is to make sure there is no transitive dependency. Transitive dependency is when any non-key attribute in a table may not be dependent on another non-key attribute in the same table.

Types of relationships

There are various types of relationships.

One-to-one (1:1)

A one-to-one (1:1) relationship is when each record in Table A relates to one, and only one, record in Table B. Likewise, each record in Table B relates to one, and only one, record in Table A.
User -> Password (1:1)

One-to-many (1:N)

A one-to-many (1:N) relationship means a record in Table A can relate to zero, one, or many records in Table B. Many records in Table B can relate to one record in Table A.
Class -> Student (1:N)
Many experts don’t separate a many-to-one relationship as a class of its own as there is not much difference between one-to-many and many-to-one relationships. It’s just a matter of focus.
Student -> Class (N:1)

Many-to-many (N:N)

A many-to-many (N:N) relationship means many records in Table A can relate to many records in Table B. And many records in Table B can relate to many records in Table A. Usually, many-to-many relationships are not kept in a data model. They are broken down into two one-to-many relationships by introducing a junction or middle table AB. The primary keys of both the Table A and Table B entities are included in the connecting table AB.
Customer <-> Product (N:N)
A → AB → B
Customer -> Order -> Product
Customer -> Order (1:N) / Order -> Product (N:1)

Order table:
OrderID
Customer ID
Product ID

Resources: