Database — Fundamentals (Part 2)
The first step towards understanding the databases (specifically relational databases) is understanding the basic features.
Wish you already came along the last part Database — Introduction (Part 1)
Database & Tables
Database
A database is a collection, or a set of tables. Each table has a formalized repeating list of data about one specific piece of information. For example a table for customers, students, orders, products, and so on. Visually, it’s often shown like a spreadsheet.
Tables
The table is the most basic building of a database. It’s the place where you will put your data, define their data type, and also their relationship with the other tables. It consists of rows and columns.
You may hear the term “entity” instead of table, but we’ll use tables for simplicity.
Rows & Columns
Within each table, every single row represents one single student, customer, order, or employee. But each of these rows is not free form. You must apply structure to this data.
So, you must say what every row is made of, and you do this by defining the columns in that table. And each column describes one piece of data. It gives it a name like name, id, email, date of birth, and a type, perhaps, a text, or a date, or a number.
Now, every row must follow that same structure, following that same format. It’s not allowed to deviate from the way that the columns are set up. And by defining these columns, we’re imposing rules on the data, and the DBMS won’t let us break them.
In a nutshell, columns define what’s the data that should be in the table, while the rows hold the actual values that you are going to retrieve, insert, update, and delete.
You may hear the term “tuple” instead of rows, and also you may hear the term “attribute” instead of column.
Before heading into the next topic, there’s something to be mentioned about columns, and that’s there are 3 types of columns:
1. Simple
It’s just a single value.
2. Composite
A value that’s composed of some other values. For example, you may have a name that’s composed of first name, middle name, and last name.
Any composite attribute will be decomposed into separate simple attributes.
3. Multi-valued
Multiple values for a single column. For example, the color of the car may be black and red.
Multi-valued attributes will be extracted in another table. This will be discussed later in Database Mapping.
Primary Key
Now, if you have a long list of rows, it’s essential to have something that uniquely identifies each row, and that’s called the “primary key”. A primary key is a column of unique values for each row.
You may have more than one student with the same name, but you can’t have more than one student with the same primary key. And if you tried to insert a duplicate value, this will be disallowed by the DBMS.
Usually you will see a primary key column called “id” of integer values.
The primary key is either naturally exists or generated by the DBMS. It means, for example, by default every customer has a unique SSN, assigned by the company. So, the SSN uniquely identifies every customer.
But, you may have a table that holds information about some products, and they don’t have ids by nature. So, you will ask the DBMS to generate a new unique column, like product id and you may want to mark it as “auto-increment”.
Auto-increment columns allows a unique number to be generated when a new record is inserted into a table by increment the value by 1 for each new record.
Primary keys are very important, not only to uniquely identify the rows, but also we are going to use them to connect between the tables and form relationships.
One-to-Many Relationship
Most of your tables will be naturally be connected, so we need to have a relationship between them. You’re not trying to invent relationships that don’t exist, you’re trying to describe what’s already there.
An example of a one-to-many relationship could be, a customer can place more than one order, but, an order is only placed by one and only one customer. You can’t have an order that’s placed by more than one customer.
The customer’s information exists in a table, and the order information also exists in another table, but they have a relationship. So, “How can we define a one-to-many relationship?”.
Do you remember when we said primary keys are used to connect between tables? So, here we will add a new column to the orders table (many side) called “foreign key”. This column has the primary key values of customers.
The values of the foreign key column could be redundant because a customer can place more than one order. And a customer can have one, or more or even nothing number of orders.
The benefit of defining this relationship is, it allows us to ask some questions like: “What are the orders placed by a customer whose first name is “Smith”?”, or go the other way, “Who is the customer who placed the order of id 1012?”.
Many-to-Many Relationship
You will be using a one-to-many relationship a lot between your tables. But, there is another way to relate tables together. What if you have a student, and each student can be enrolled in one or more courses, and at the same time, each course can have one or more students enrolled in it.
So, it’s no longer a one-to-many, it’s many from both sides. And, as usual, the primary keys are the way to connect the tables. But, this time we will create a new table. It’s usually called a “junction” or “linking” table.
This table exists only to connect the two tables, it has two foreign keys, one points to the primary key of the students table, and the second one points to the primary key of the courses table.
And the two foreign keys together will form the primary key of the new table.
Now, using this relationship, we can know the courses of a particular student, or go the other way and get the students who are enrolled in a specific course.
Officially, there is a third kind, a one-to-one relationship that is possible, but it’s not common.
If you think about it, if one row in one table is pointing to one and only one row and another table, well, you might as well just combine those tables so it’s one row in both places. This will be discussed later in Database Mapping.
These types of relationships (one-to-many, many-to-many, or one-to-one) are usually called “Cardinality Constraints”. They are constraints that specify the maximum participation between tables.
There are some other constraints like the “Existence Dependency Constraint” (also called “Participation Constraint”). They specify the minimum participation; zero (optional participation), or one or more (mandatory participation).
A relationship is when we say an employee “works on” a department, but, the constraints is when we say, one employee can work in many departments.
Structured Query Language (SQL)
It’s the language that’s used to create, read, update or delete data (falls under the acronym CRUD), and also to define the databases themselves.
It’s not a programming language, it’s considered as a declarative query language. You just need to say what you want, and you let the DBMS handle how that’s actually done for you.
On the other hand, in the programming language, you would have to do write the steps to do this; maybe some loops, and check to see if this is the required data or not.
We are going to cover SQL in later tutorials, the syntax is very easy to understand, but, most DBMS have their own different implementation of the SQL language, although basic SQL knowledge works with all of them.
Wrapping Up
We’ve covered the basic fundamentals of a relational database. Now, we are going to take this further and walk through the steps needed to create your actual database. Next is the “Database Design Process”.
References:
