Jamie Balfour

Welcome to my personal website.

Find out more about me, my personal projects, reviews, courses and much more here.

Part 2.1The basics of an RDBMS

Part 2.1The basics of an RDBMS

MySQL is considered to be an RDBMS:

Relational Database Management System

SQL

SQL is made up of two different components used to control and manipulate a database; one called the Data Defintion Language and the other called the Data Manipulation Language. The DDL is used to create, delete and change the databases and tables that make up the database whereas the DML is used to store, retrieve and update the data in the database.

What is an RDBMS?

In a relational database, data is represented as tuples and are grouped into relations.

SQL is a declarative language. This means that the user declares what data should be stored in the database and the database management system will figure out how to store the data. SQL is an approximation of what Edgar F. Codd described as the relational model for databases, so it can be considered a relational database management system.

MySQL stores rows and columns of data in tables within databases. A relationship happens between tables:

The following tables have a relationship:

Product_Info

product_id product_name
001 Intel Core i7 2600
002 Intel Core i5 2400
003 Intel Core i3 2120

Purchases

product_id price
001 $270.00
001 $252.00
002 $120.00
002 $190.00

The relationship here is that they both have a product identifier. Since these match, it's easy to find out the name of a product purchased at $270.

This is one of the key principles of an relational database management system.

Primary and foreign keys

Relations are made between keys. In the example above, the two tables are matched on the product_id fields in the two tables. This is called a foreign key.

A primary key is a unique identifier in a table. In otherwords, each row has a unique way of accessing it. If it is not specified, the MySQL database management system will create a field automatically which uniquely identifies each row but it will not be accessible other than to MySQL.

Keys create constraints on a table.

Feedback 👍
Comments are sent via email to me.