Relational Databases

September 26, 2022

Relational Database

A database is a collection of information related to a particular subject or purpose, like tracking customer orders or maintaining a music collection.

A relational database management system (RDBMS) provides a way to store, maintain, and retrieve data.

Tables

Data in a relational database are stored in a number of 2-dimensional tables.

A table is a collection of fields related to a single subject or purpose where each row is considered a record about a single instance and each column is a field or data element of the instance.

Example Table

id	first_name	last_name	birth_date
1	Georgi	Facello	1953-09-02
2	Bezalel	Simmel	1964-06-02
3	Parto	Bamford	1959-12-03
4	Chirstian	Koblick	1954-05-01
5	Kyoichi	Maliniak	1955-01-21

Tables

A table will contain information about one subject (e.g. Customers, Products, Orders, Employees, etc.)

The fields within a table are information about the subject (e.g. First Name, Birthdate, Address, etc.)

Usually, each table has a primary key, a field (or combination of fields) whose value uniquely identifies each record in a table (the id field in the previous example).

Fields

Each field has a data type. There are many different kinds. Examples:

int - normal sized integer
numeric - arbitrary precision number
date - a date
datetime - a date and time
character varying(n), varchar(n) - a variable length string
text - an unlimited length string

Relations

Relational databases are “relational” because tables can be related to each other We can relate rows of different tables by linking the primary key from the primary table to a field called the foreign key in the related table

Keys

Primary Key - value which uniquely identifies a row in a table Foreign key - a primary key field from one table that is included in a second table to form a relationship

Customers

id	first_name	last_name
1313	Owen	Hawes
1314	Melissa	Caputo
1315	Karen	O’Brien

Orders

id	customer_id	amount
3012	1313	$300
3013	1315	$6,500
3014	1313	$4,000

Relationship Types

One-To-Many

One record in the primary table can have 0, 1, or many records in the related table.

Course to Section (each course can be offered many times as a section)
Customer to Orders (each customer can have many orders

Implemented by having a primary key in the primary table and a foreign key in the related table.

Many-To-Many

Multiple matching records in both tables.

Orders to Products (each order can have multiple products and each product can be on multiple orders)
Section Enrollment to Student (each section can have multiple students and each student can be enrolled in multiple sections)

Implemented by having a junction table inbetween (also called a glue, join, or associative table) that is in a one-to-many relationship between the main tables to the junction table.

Orders

id	customer_id	order_date
3012	Joe Smith	2022-05-11
3013	Tom Jones	2022-01-28

Products

id	name	category
3005	airpods	electornics
3110	toaster	appliances
2958	knife	utensils

order_lines

id	order_id	product_id
601	3012	3005
602	3012	3110

One-To-One

One record in the primary table has 0 or 1 records in the related table

Country to Capital City (each country has one capital city and each capital city has one country)
Employee to Health Coverage Selection (each employee has one selection and that selection has one employee)

Often implemented like a one-to-many relationship where the related table has a foreign key, but a unique constraint is placed on the foreign key so that it cannot be duplicated.