Databases Overview

Databases

A database is a collection of information that is organized so that it can be easily accessed, managed and updated. The are set up in tables, made of columns and rows.

Rows are classified as Records - A record is composed of fields and contains all the data about one particular person, company, or item in a database.

Columns are classified as Fields - A field is part of a record and contains a single piece of data for the subject of the record. Examples - first name, last name, address, phone, category, time, date etc.

Primary Keys - This is a field that is unique to every record in the table. It reduces data redundancy and data integrity. This is normally an auto incremented number. Each table requires a primary key to identify each record and allow relationships with other related data in another table.

Database Overview

Data Dictionary

When planning a database, we need to create a data dictionary that will help us to create the fields and data types of setting up a project.

See the example below for a table about students.

Data Types

In a database strict rules on data types in fields must be kept to keep the integrity of the data.

VARCHAR - string
INT (11) - whole number
FLOAT - decimal number
DATE/TIME - date and time

Relational Databases

In a relational database there are multiple tables called entities that connect to each other using primary and foreign keys. In the example above the relationships between the tables are describe in the following way:

One book can be borrowed many times
Borrowers can borrow many books

This creates a many to many relationship between borrowers and books. So we create a borrowings table to record the borrower and book when a book is borrowed from the library.

The benefits of this method include:

Less data is needed to stored as source data can now be looked up via relationships in data
Data integrity - data is more accurate. If a borrower changes their last name, all records can be changed so their is no invalid data

Relationships

To create a relationship each table requires a primary key to uniquely identify each record.

In the transactional table (Borrowings in the example above) needs to stores the primary keys from the books table and the borrowers table. We call these the foreign keys as they keys are coming from other tables.

In the transactions table where we want to store a value a reference to data in another table we create a foreign key. The foreign key stores the primary key value from the related table.

There are other types of joins that can be used which provided different outcomes, however the example above will be the main type that we will be using.

See SQL Joins - https://www.w3schools.com/sql/sql_join.asp

Report abuse