A relational database is a database schema that stores data in tables related to each other. The relationship between tables is formed based on the logical relationship of data between one tables.
The relationship between tables in a relational database aims to reduce data repetition/redundancy so that data storage, retrieval, and processing can be more effective and efficient. Relational databases are managed using a programming language called SQL (Structured Query Language).
Table Structure in Relational Databases
Tables in relational databases consist of columns and rows. Columns are arranged horizontally, while rows are arranged vertically. Columns in a relational database are a set of attributes as a reference to storing data in the rows connected to them. Meanwhile, rows represent data recorded in a table according to the associated column’s attributes.
Column
Column attributes define the data that can be stored in its rows. These attributes include:
- Column Name : Each column must have a name representing the data stored in its rows. Using the right name to represent the data will make it easier for developers to retrieve and manage data. For example, we can use “StudentName”, “address”, “PhoneNumber”, etc. as the column name.
- Data Type : The data type is a reference for how data can be called and managed by the database.
- Auto Increment: auto increment usually uses an integer data type. a column with auto Increment means that each row of data recorded in that column will automatically be added as +1 from the number value in the previous row.
- Default Value : A value that will automatically be stored in a row if it is left blank/not filled in.
- Not Null : Not-Null is an option used if each row in a column must be filled in and cannot be empty/Null. We can use this attribute for columns that store must-have things such as Full Name, Date of Birth, and Gender.
- Unique : Each row in a column cannot store the same value. It means the value of each row in a column must always be different (unique). We can use this attribute for columns storing ID numbers, email addresses, or cellphone numbers.
- Primary Key : The primary key is the identity of each record. The primary key column must be unique and cannot be empty. The primary key aims to make it easier to call or manipulate data. It usually also functions as a referenced key and acts as a connector between tables.
- Foreign Key : Foreign Key is a column used to store referenced keys from another table. The Foreign Key and Referenced key columns are what will connect one table to another.
Row
In a relational database, data are stored in rows corresponding to a specific column. As more data are recorded, the number of rows will increase. Usually, each row is accessed by referencing a value in a primary key or index column.
Data normalization
Data normalization is a process for managing how data can be stored, processed, and retrieved effectively and efficiently. This process breaks one table into several tables to reduce data repetition/redundancy. Data normalization also makes storing, processing, and retrieving data easier.
Data normalization has several levels. Normalization for each level can only be done if the previous level has been normalized.
The process of normalization creates tables that are related to each other. This relationship will integrate the stored data in every table.
Types of Relationship Schemes
Relations between tables are carried out by storing Referenced Keys values from one table as Foreign Key in another table.
There are 3 possible ways to create relationships between tables:
One to One
A one-to-one relationship connects one row of data in one table with one row of data in another table. This relationship means that each row of data in one table can only have a relationship with one row of data in another table.
We can do one-on-one relation by storing one Referenced key in one table as a foreign key in only one row of data in another table. As we can see in the illustration below, one Referenced Key from Table A can only be stored as one Foreign Key in Table B.
One to Many
A one-to-many relationship connects one row of data in one table with many rows in another table. This relationship means that each row of data in one table can have relationships with multiple rows of data in other tables.
We can create one-to-many relations by storing one Referenced key in one table as a foreign key in many rows of data in another table. As we can see in the illustration of the one-to-many relationships below, one Referenced Key from Table A can be stored in multiple rows as a Foreign Key in Table B.
Many to Many
Many-to-many relationships connect many rows of data in one table with multiple rows in another table. We can do many-to-many relations by creating a middle/join table to connect data from one table to another table.
Middle/join tables in a many to many relationship function to store/connect referenced keys from both tables in many rows of data. As we can see in the illustration of the Many to Many relationship below, one Referenced Key from table A can be stored in multiple rows as a Foreign Keys in the join table and one Referenced Key from table B can also be stored in multiple rows as Foreign Keys in the join table. The meeting of the referenced keys from the two tables in this join table allows for a many to many relationship between table A and table B
Advantages of Relational Databases
- Easy to use
The use of SQL in Relational database operations makes it easy to understand and use to add, delete, call, manage and manipulate data and tables. Apart from that, relational databases also have many communities and forums that can help users solve problems they face when using relational databases.
- Prevent data redundancy
Data normalization in relational databases prevents data redundancy. This normalization also helps minimize memory usage and makes storing and processing data more efficient. - Data validity
Column attributes in relational database tables ensure that the data entered is as it should be. These attributes help ensure that the data stored is valid data.
- Backup & Security
Relational database management systems have features for carrying out backups by exporting or importing data. This backup system reduces the chances of data loss due to user error or system damage. Apart from that, there is also an authentication system and user levels to be able to access and manage data in the database.
Disadvantages of Relational Databases
- complex structure
Relational databases are built by breaking one table into several tables that are logically related to each other through data normalization. Apart from that, relational databases also have attributes that must be embedded in each column. The structure formed through this process makes the relational database have rules that must be followed to store, retrieve and process data so that the relational database becomes less flexible. - Maintenance
As data or database structures increase, relational databases require maintenance to optimize and ensure system performance runs well. This maintenance usually requires extra focus of time and energy so that the maintenance runs well and doesn’t cause other problems. - Not suitable for storing unstructured data
As the data or database structure increases, relational databases require maintenance to optimize and ensure system performance runs well. This maintenance usually requires extra focus of time and energy so that the maintenance runs well and doesn’t cause other problems. - Hard to develop
Structural changes need to be made to develop a relational database. Structural changes require careful design and planning so as not to damage the existing database structure. This design and planning of course requires time, money and energy so this is one of the obstacles to developing a relational database structure.
To better understand the relational database schema, you can see an example of using a relational database here.
Refereces:
– https://www.techtarget.com/searchdatamanagement/definition/relational-database
– https://en.wikipedia.org/wiki/Database_normalization
– https://www.w3schools.com/sql/sql_datatypes.asp
