Everything You Need to Know about Relational Databases

Basic Functionality

The basic functions of a DBMS are to read (view data via queries), create (add data, tables, rows, or columns), update (change data, tables, rows, or columns), and delete data. Conditions can be added to perform more functions such as the following:

• View data that meet certain criteria. A store manager could view all items that have fewer than 10 units in stock, or that have been in the warehouse for more than three months.
• Create a new table that is a subset of a base table. The marketing department could create a table that shows only customers who live within a 10-mile radius of a new location.
• Connect the contents of two different tables. A contractor could view all the subcontractors who worked on a project and the amount each billed for their work, or a principal could see all students who have a GPA of 3.5 or better.
• Combine data in two tables: HR could view all actives employees and all retired employees to create an invitation list for the company holiday party.
• View data that have no relationship: This might be a list of all customers who created an account but who never placed an order, then delete them.
• Add new data to existing records: Find all customers who’ve ordered more than $1,000 worth of products in a year and give them an automatic discount on their next order.
• Delete data from existing records: Delete the automatic discount from customer accounts after its expiration date.

Some Common SQL Commands

To accomplish the actions above, database developers and admins use the commands listed below to query or update the database:

• ALTER TABLE: Adds new columns to an existing table.
• BETWEEN: Filters results based on two parameters — e.g., view employees whose birthdays are between June 1 and June 30, or all cars that cost between $20,000 and $25,000.
• CREATE TABLE: Adds a new table to the database. Parameters are added to the command to specify the number and names of the columns.
• DELETE: Removes rows from a table.
• JOIN: Allows the data from multiple tables to be combined. Read more about SQL JOINS here.
• INSERT: Adds a new row to a table. Parameters are added to the command to specify the data in the columns.
• LIMIT: Sets the maximum number of rows that will be displayed when querying a database. You could see the best-selling books of 2014, but limit it to the top 10.
• ORDER BY: Sorts the results by a certain column. For example, when looking at the 10 top-selling books of 2014, you could sort the list by title, by the author’s last name, or by sales volume.
• SELECT: All SQL queries begin with SELECT.
• UNION: Combines the results of multiple queries.
• UPDATE: Edit rows in a table.
• WHERE: A conditional statement that allows queries, additions, deletions, and changes to be limited to data that meet certain criteria. The BETWEEN, LIMIT, and ORDER BY examples above would be accomplished by using WHERE. It could also be used to limit rows removed in a DELETE command.

Data Models, Designs, and Schemas

A database model is an abstract representation of how data will be stored. It shows how data elements will be organized and the relationships between them. The term is also used to describe high-level concepts like relational, flat-file, or object-relational database systems.

A database schema is the implementation of the data model (the first sense above) in the database. It includes data types, constraints, and keys (both primary and foreign).

Database design is the production of a database model (again, in the first sense above) based on the needs of the users.

Locking

When data is being modified, locking prevents another user or transaction from altering that data. This prevents data from falling out of sync.

Database Normalization

Database normalization is the foundation of relational databases. It’s the process that organizes data into tables that can then be connected to each other. Each table should be about a specific topic, and only those columns that support the topic are included in the table.

Database normalization provides the following benefits:

• Duplicate data is minimized, and data storage needs decrease.
• Changes to data have to occur in only one place.
• Access to the data is quicker and more efficient.

Once the data schema and business rules for the database are established, the data is processed in steps that create tables and primary keys, eliminate repetitive data, and build relationships between the data by splitting the data into new tables and creating foreign keys for those tables. The steps are called First Normal Form (1NF), Second Normal Form (2NF), and Third Normal Form (3NF). Each step attempts to further refine the data and narrow the focus of each table.

Complicated data schemas may require fourth and fifth normal forms. There are variations of forms, such as the Boyce–Codd Normal Form (BCNF), which is a more robust version of the 3NF and is used in unique situations.

A full description of the normalization process is beyond the scope of this article, but an excerpt from the 2002 book Absolute Beginner's Guide to Databases by John Petersen, available on the informIT website, covers it well.

Null and Duplicate Values

Null values are data points that don’t exist. For example, not everyone has a middle name, a customer may not provide a phone number, or data may have been lost. A relational database must accommodate these exceptions to rules (it’s the third of Codd's 12 Rules). Null values do not equal zero or a space. However, a null row should not be allowed, even though some RDBMSs do.

Duplicate values will exist. There may be two employees with the same first and last name, and maybe even the same birthday. That’s OK. But having duplicate rows that match, including the key, is not. Again, some RDBMSs allow this, but it violates the basic principles of a relational database.

Database Security

There are a few facets to database security, all of which are vital to database operations:

• Ensuring that users can see only the data they are authorized to see and use only commands they are authorized to run
• Keeping unauthorized users from accessing the database
• Detecting and stopping malware and other attacks
• Preventing data corruption
• Ensuring database availability by preventing system crashes

Detailed information on the second and third concepts can be found here.

Views and Reports

A view is a generally a screen or a set of screens that is customized to show data that meet the needs of a specific user group, and prevents them from seeing data they don’t have access to. Views can allow users to query the contents of the database without having to know any SQL or other commands.

Reports are queries that are run and delivered to a person or group that can be used to guide business decisions. Reports can be scheduled or ad-hoc, and can show either real-time data or data from the past (such as the previous week or quarter).

Transactions and Stored Procedures

Transactions occur when data is added, updated, or deleted. Relational database transactions should follow the ACID guidelines, meaning they should be:

• Atomic: The transaction is indivisible, or all operations in the transaction must occur, or the transaction fails and all changes must be rolled back to their original state.
• Consistent: All steps must meet the defined rules for any changes to data fields, record uniqueness, data constraints, etc.
• Isolated: Data that’s being changed by the transaction can’t be updated by other transactions or by users until the first transaction is completed or cancelled.
• Durable: Once the transaction is complete, the changes must not be lost in the event of a power outage or other catastrophic event.

Examples of transactions could include adding an order record to a table once a customer hits the buy button, or changing the work location of an employee if they've moved from one office to another.

Stored procedures are compiled pieces of code that usually perform a number of defined steps. Code runs faster when it compiled (i.e., when it’s been translated from the developer's programming language into machine language). A stored procedure can include transactions. An example of a stored procedure might be a monthly sweep to move customer records from an active customer table to an inactive table if the customer hasn’t purchased anything in the last year.

Coupling: A measure of the amount of dependence between two items. A column of data (e.g., employee birth date) is tightly coupled to any stored procedure (e.g., “show all employees who have a birthday in the coming month”) that will reference the column, so changes to one will impact the other. If all dates are changed to a new format, but the stored procedure isn’t updated, employees won’t get their cake.

Data integrity: Also known as data quality. Data should be complete, accurate, and correctly formatted; if it’s not, it can be useless (or even harmful). Ensuring data integrity is a key part of database design.

Referential integrity: This is an important subset of data integrity. Every column in a table that holds a foreign key must contain a null value or a value in another table that is considered that table’s primary key. These are the basic rules:

1. A key (primary or foreign) must match the key in a row in another table, or be null.
2. If a primary key in one table changes, all tables where that key is listed as a foreign key must be updated.
3. If a row is updated, all rows linked to that row must be deleted or relinked to other rows.

Metadata

Metadata are data about the database itself. Just like data, metadata are stored in tables, but only used by the DBMS. As data is added, deleted, or changed, the metadata is updated. The metadata allows the DBMS to find data, create new tables, manage resources, and enforce security.