Database Management Systems 2

TL;DR

Lets take a look at what we will cover next.

To note, carrying over from the last post we took a high level approach to what a database, database management system and its architecture looks like.

In short, we are ready to cover the last point on our agenda from last post which is learning simple SQL Queries you can try on your own and also cover some new material in relation to previous topics.

Introduction

By the end of this tutorial, you will

• How to run simple Database queries
• Understanding Relationship Models
• Looking over Normalization

SQL Queries

Notice how our queries end with semicolons this is a must for sql statements, if you wish to learn more about syntax w3 schools offers great documentation on getting up to speed quickly!

• Select Statements: used to retrieve data from your database

SELECT column1, column2, ...
FROM table_name;

• Where Statements: used to filter results

SELECT column1, column2, ...
FROM table_name
WHERE condition;

• Insert Into Statements: used to insert new data into a table

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

• Update Statements: used to update records in a table

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

• Delete Statements: used to delete records from a table

DELETE FROM table_name WHERE condition; 

Core Concepts

Relationship Models

Each database table (entity) will be comprised of relational design from one row to another. This is what makes up whats known as one-to-one or one-to-many relationships among tables.

Relational Schema's will be used to represent the structure of our relations in a table. For example, a student relation will or can be represented as student_id, name, phone, age.

In short, relational models will represent the data to be stored in a relational database (Google Search: SQL vs NoSQL).

When looking at these relationships and their specific references within tables we begin to talk about primary vs foreign keys. This is important to note as a primary key is a unique identifier for a particular row/attribute or data point in a table where as a foreign key is a related identifier to a shared attribute/row in another table.

Normalization

To start, database normalization is the process of organizing data/attributes to reduce or eliminate redundancy.

Looking at the issues data redundancy can cause one major problem is capacity. As data is duplicated inside our database queries can get bogged down traversing or iterating over duplicates.

When looking to break down what's known as normal forms we can see that a normal form is just a way of breaking down unstructured data into a more structured format.

Normal Forms

1. 1NF - First Normal Form
2. 2NF - Second Normal Form
3. 3NF - Third Normal Form

In summary, each "level" of normal form has rules that our data has to follow starting with 1. attributes in a row must be composed of sing values 2. 1NF must be met and has no partial dependency. (Google search: Normal Forms)

This is considered to be part 2 of Database Management Systems, I will post more on part 3 shortly