Database

In the IT industry, database concepts and knowledge play a pivotal role in ensuring efficient and effective management of data. Databases serve as the backbone of virtually every information system, allowing organizations to store, organize, retrieve, and analyze vast amounts of data. Understanding database concepts is essential for professionals working with data, as it enables them to design, develop, and maintain robust and scalable database systems.

Moreover, a solid understanding of database concepts is critical for ensuring data integrity, security, and privacy, as it allows IT professionals to implement proper access controls, backup and recovery mechanisms, and data normalization techniques. In a world driven by data-driven decision-making, possessing knowledge of database concepts equips individuals with the expertise to harness the power of information and contribute to the growth and success of organizations across various industries.

This chapter highlights why understanding database concepts is essential for IT professionals to design scalable, secure, and robust systems while addressing challenges like data integrity, security, and concurrency. It explores the necessity of databases as data volumes grow, introduces Database Management Systems (DBMS) like Oracle and MySQL, and categorizes databases into relational (SQL) and non-relational (NoSQL) types. The chapter provides a detailed examination of relational database components—tables, rows, columns, and keys—alongside relationships, SQL syntax, normalization, and design processes. It concludes with a comparison of SQL and NoSQL databases, offering guidance on their contextual applications.

Key Points

• Database Necessity: Databases address data management challenges like size, accuracy, security, redundancy, concurrency, and transactions.
• DBMS: Database Management Systems (e.g., Oracle, MySQL, MongoDB) are software that manage databases.
• Relational Databases (SQL):
  • Data is organized in tables with rows and columns.
  • Database keys (primary, foreign, candidate, super) establish relationships and ensure data integrity.
  • Table relationships: One-to-one, one-to-many, many-to-many.
  • SQL (Structured Query Language) is used for data manipulation and querying.
  • Database design involves requirement analysis, logical design (ER diagrams, normalization), physical design, implementation, and maintenance.
  • Normalization reduces data redundancy and anomalies (1NF, 2NF, 3NF, BCNF).
• Non-Relational Databases (NoSQL):
  • Flexible schemas for unstructured or semi-structured data.
  • Types: Key-value, graph, document, wide-column.
  • Scalable and adaptable to evolving data requirements.
• SQL vs. NoSQL:
  • SQL: Structured data, predefined schemas, vertical scalability, complex queries, ACID compliance.
  • NoSQL: Unstructured data, dynamic schemas, horizontal scalability, fast data operations.
  • Hybrid systems can leverage both SQL and NoSQL for optimal performance.

Why do we need a database?

When you have some data, which could be anything like customers, products, employees…, and you want to store this data somewhere. This data could be in text format, numbers, dates, documents, images, audios, videos etc. This is easy to manage when they are small. Yet, this suddenly starts to become a problem when the amount of data is growing fast. Let’s consider some potential perspectives here.

• Size – Large amount of data, hundreds of thousands of data, and still growing
• Accuracy – Are the data being inputted correct?
• Security – Different users with different roles should be granted different levels of privileges
• Redundancy – redundancy (multiple copies of data) provides backup in case something bad happens. What if you have conflicts among the copies of your data.
• Concurrency – how do you handle multiple users writing to the same data?
• Transaction – When a workflow is halfway finished and you want to cancel the workflow due to some reason, how do you handle the part which has already taken place?

The list could go longer and the real-world scenario is very complicated.

Database Management System (DBMS)

We often refer to our database as Oracle, MySQL, SQL Server, MongoDB etc. Actually, they are Database Management Systems (DBMS).

We can simply think DBMS is the software that would be installed on your personal computer or server, which is then used to manage one or more databases.

Database Types

There are different ways to categorize databases, but in general, databases can be divided into two types: Relational and Non-relational. They are also called SQL Database and Nosql Database.

We will first look at Relational Database, which is more traditional.

Tables

Table is the basic building block of a database and a database can be considered as a collection of tables. It is where you put your data, define data type, and relationship with other tables.

Rows & Columns

A table consists of rows, and columns.

• Row – represent entries of data
• Column – defines what kind of data it is specifically

For example, in the below table, it describes the temperature of different cities in the US. Each row represents an entry of the cities and each column defines the meaning of the values.

Database Keys

A database key is an attribute (column) or set of attributes which allows you to establish a relationship between and identify the relation between tables.

In a real-world application, a table could contain thousands of records. Moreover, the records could be duplicated. Keys ensure that you can uniquely identify a table record despite these challenges. It helps to enforce identity and integrity in the relationship.

There are various keys in database system:

• Primary Key
• Foreign Key
• Super Key
• Candidate Key
• Alternate Key
• Compound Key
• Composite Key
• Surrogate Key

We’ll take a look at these – Primary Key, Foreign Key, Super Key and Candidate Key.

Primary Key

For a table, you need something unique to identify a row and that’s called “primary key”. A column called “name” may have duplicate values since people can have the same name. This will create a problem when we refer to entries with names, as it will return more than one record which will create ambiguity.

• Primary Key – a minimal set of columns in a table that uniquely identifies rows in that table

To deal with that issue, there is often a column called “ID” in the table, which is used as the primary key for that table. The primary key is usually generated by DBMS.

Another note is that a primary key does NOT have to be one column. It can be the combination of several columns, as long as the combination of them uniquely identifies each row in the table and that combination is minimal.

Using the combination of columns as the primary key might not be that often, since we can always add an ID column.

Foreign Key

• Foreign Key - a column in one table that refers to a Primary Key in another table.

One table can have more than one foreign key.

For example, in the following two tables, the left table has “OrderID” as primary key and the right table has “PersonID” as primary key. Furthermore, the “PersonID” is also a column in the left table. In this case, we say “PersonID” is a foreign key in the left table, since it is not the primary key for that table but is a primary key for another table.

Super Key

• Super key – a combination of one or more columns, which can uniquely identify a row in a table

For example, let’s look at the below table:

Assume DL – Driver License, SID – StudentID, FN – First Name, the super keys are:

• {DL}
• {SID}
• {DL, SID}
• {DL, FN}
• {SID, FN}
• {DL, SID, FN}

Because all of them can uniquely identify each row of the table.

However, this is NOT super key:

• {FN}

Because First Name alone doesn’t uniquely identify each row of the table.

Candidate Key

• Candidate Key – A super key with no redundant column.

We need to understand what redundant means here. It basically means minimal super key. Let’s take the above super key and examine each of them here.

• {DL}, Not redundant
• {SID}, Not redundant
• {DL, SID}, Redundant because either DL or SID can uniquely identify each row
• {DL, FN}, Redundant because FN is not needed and just DL is enough to identify each row
• {SID, FN}, Redundant because FN is not needed
• {DL, SID, FN}, Redundant

In this case, the candidate keys are:

• {DL}
• {SID}

A primary key can be selected from candidate keys and decisions can be made by Database Admin.

Table Relationship

Tables don’t just exist in the database separately. A lot of times, they are connected and the relationship between each table plays a vital role in a database. For example, establishing consistent relationships between database tables helps ensure data integrity as well as contributing to database normalization.

There are 3 types of relationships in a relational database:

• One-to-One
• One-to-Many (or Many-to-One)
• Many-to-Many

One-to-Many

Let’s look at One-to-Many first, as this is most common. In this type of relationship, a row in table A can have many matching rows in table B, but a row in table B can have one matching row in table A, as illustrated in the below ER Diagram (Entity-Relationship Diagram).

What is ER Diagram?

An entity relationship diagram (ERD) shows the relationships of entity sets stored in a database. One entity usually corresponds one table.

By defining the entities, their attributes, and showing the relationships between them, an ER Diagram illustrates the logical structure of databases.

Below table could be the two tables (entities) in the database.

In the “City” table, “CityId” is its primary key. In the “Customer” table, “CustomerID” is its primary key and “CityID” is its foreign key, as it is referring to another primary key in the “City” table.

Since the “CityID” is uniquely identifying an entry in the “City” table, we can find the referenced city information for each of the entries in “Customer” table.

The interesting thing is that we see there are multiple customers with the same city record. That makes sense because we can have several different customers in one city. In other words, One city can have Multiple Customers. This is looking from “City” to “Customer”.

Now, let’s look from “Customer” to “City”. Can one customer have multiple cities? That is, can one customer reside in multiple cities? This is becoming even more interesting, as we can simply think that a customer as a person can travel from one city to another.

From this perspective, one customer can correspond to multiple cities. However, this may not fit the business context. The business context indicates that one customer will reside in one city and if the customer moves to another city, we can change the value to another city. In this sense, One customer will correspond to One city.

We look at it from both sides, one is One-to-Many and the other is One-to-One. Hence, the relationship between the two entities is One-to-Many (or Many-to-One).

Now, what if both sides are One-to-One? Then, the relationship is One-to-One. What if both sides are Many-to-Many? Then, the relationship is Many-to-Many.

The important thing here is that you can always justify a relationship by your own perspective, but a lot of times, we should find the best case fitting the business context. Even more, sometimes, there is not only proper design for the entity relationships. They are all good as long as they are “reasonable”. By “reasonable”, I mean it follows the principles and best practices for database design.

One-to-One

One-to-One relationships are not very common but are still valid.

For example, let’s consider driver and license. To verify that, we look from “driver” to “license” and we know that one specific driver corresponds to (own) one license; from “license” to “driver”, one specific license corresponds to (belong) to one driver. This is a One-to-One relationship.

Be aware that we are referring to a specific driver and that specific license. Although there are many drivers and many licenses out there, the relationship between them is One-to-One.

Many-to-Many

Many-to-Many relationships are more common compared to One-to-One.

For example, let’s consider an online bookstore, where buyers buy books from that store. First, we look from “buyer” to “book” and we know that one specific buyer can buy multiple books. Then, we look from “book” to “buyer”, one specific book can be bought by multiple different buyers. The relationship between “buyer” and “book” is Many-to-Many.

The interesting thing again comes with context. When we say “specific” book, are we physically referring to that copy buyer A has bought? Or, are we referring to the book considered as a product with the same title? In this business context, we would be better off considering it as the latter case.

SQL (Structured Query Language)

SQL is the standard language for dealing with Relational Databases. SQL can be used to insert, search, update and delete database records. SQL can do lots of other operations including optimizing and maintenance of databases.

SQL Example

SELECT * FROM Members WHERE Age > 30

SQL syntaxes used in different databases are similar to each other. However, a specific database will still have its own ‘Dialect’ when you are writing for that.

Why divide tables?

We know a foreign key is a column from one table acting as primary key in another table. Have you thought about why we divide the table into two tables?

Just think about it. Jamming data in one table will make the table larger and thus inflexible. Also, it would be more difficult to maintain. For example, if we extract repeated values from one table and put it into another table. Then we simply use IDs from other table instead of real values. In this case, when we need to update the value, we change need to change the value in the reference table; otherwise, we need to update every field that has this value.

On the other hand, dividing tables creates more tables and thus the complexity of relationships in the database. A well-designed database usually finds a balance between the two sides of a coin.

Database Design Flow

There are generally 5 stages to design a database.

Database Design Steps:

1. Requirement Analysis – first and most important stage. It involves assessing the informational needs of an organization so that a database can be designed to meet the needs

2. Logical Design – this involves 3 sub-steps

   • a. Conceptual model – description of the structure of a database

   • b. ER (Entity Relationship) Diagram – illustrate the entities and their relationships among each other

   • c. Normalization – the process that organizes tables in a manner that reduces redundancy and dependency of data

3. Physical Design – make decisions about database environments, application environments, etc. The result of this step is a physical design specification.

4. Implementation – implement the proposed database, activities involve database creation, table creation, load data into tables etc.

5. Monitor & Maintenance – monitor and evaluate the database is running as expected and update it as needed

Database Normalization

Normalization is a process to decompose tables to eliminate data redundancy as well as undesirable characteristics such as insertion, update and deletion anomalies. It involves 4 major steps.

Problem Before Normalization

Let’s take a look at an example.

We have a table here with student information. As we can see, Department and Major values are repeated and this repetition will probably happen for other students as well. This is Data Redundancy. We might just have another Department table and Major table and reference their values here.

Anomalies - Insertion, Updating and Deleting

There are different types of anomalies which can occur in referencing and referenced relation:

• Insertion Anomaly - If we have to insert 100 new students of the same department, then the department information will be repeated for all those 100 students.
• Updating Anomaly – Suppose professor Baker is replaced with another one, we need to update for all those records
• Deletion Anomaly – If we consider Student and Department as two different information and we delete the last record, the Department information will be lost as well

Normalization Rule

The process of database normalization consists of following several normalization rules:

• First Normal Form
• Second Normal Form
• Third Normal Form
• BCNF

The number of forms can still be developed. Yet, in many practical cases, normalization achieves its best in 3rd Normal Form.

First Normal Form (1NF)

First normal form requires:

1. Single (atomic) valued attributes/columns
2. Values in a column should be the same type
3. All columns should have unique names

First rule basically means every value in a table cell should be a single (atomic) value. That is, there should not be multiple values.

Second rule requires values in the same column should be the same kind of type, which is kind of intuitive.

Third rule expects each column in a table should be unique. That is, no duplicate column names.

For example, in the below table, the second and third rules are satisfied but the first rule is violated, that is there are multiple values in some cells.

To fix that issue, we can break the cell into atomic values like this.

Let’s continue to look at the next form.

Second Normal Form (2NF)

Second Normal Form requires:

1. First Normal form should be satisfied
2. No Partial Dependency

Let’s first look at what Prime Attribute and Non-prime Attribute means. Here, attribute is equivalent to column.

• Prime Attribute – Any attribute which is used to build candidate keys. That is to say, the building blocks of candidate keys.
• Non-prime Attribute – Attributes that are NOT Prime Attributes.

Consider this table again mentioned in Super Key section:

From the previous Candidate Key section, we know the candidate keys are: DL and SID. Since DL and SID are building blocks of candidate keys, DL and SID are prime attributes. Hence, FN is a non-prime attribute.

Now, for the definition of Partial Dependency:

• Partial Dependency – A non-prime attribute is dependent on part of the candidate keys.

Simply put, if {A, B} are candidate keys and {C} is a non-prime attribute. In this case, if {A, B} -> {C} and also {A} -> {C}, then {C} is partially dependent on {A, B}.

Looking at the previous table, we know that {DL, SID} is the candidate key and {FN} is a non-prime attribute. {DL, SID} can obviously determine {FN} for each row. Furthermore, {SID} can determine {FN} for each row as well. Hence, {FN} is partially dependent on {DL, SID}. In fact, {DL} can determine {FN} too.

On the other hand, a dependency, which isn’t partial, is full. In other words, if we don’t have {A} -> {C} or {B} -> {C}, that is to say, only {A, B} -> {C}, then we say {C} is fully dependent on {A, B}.

Why {SID} determines {FN}?

When we say {SID} can determine {FN}, that is because we know from a business perspective, every student in a school system will be assigned a unique StudentID and that StudentID alone can identify a student and hence his first name.

The point here is again, database design largely depends on the scenario and business context. A thorough analysis on the business situation will be paramount to the success of database design. A well-executed database design flow will effectively result in a proper delivery.

To fix the partial dependency issue in the previous case, we can remove the “duplicated” column and put it in a separate table. The following is one option.

Let’s look at the Third Normal Form.

Third Normal Form (3NF)

Third Normal Form requires:

1. Second Normal form should be satisfied
2. No Transitive Dependency

A transitive dependency can be seen as: A -> B and B -> C; therefore A -> C.

A transitive dependency happens with at least three items.

An example would be

Here BID (BookID) doesn’t determine AN (Author_Nationality) directly. However, BID determines Author and Author determines AN. This is a transitive dependency.

Transitive dependencies are BAD database design because it can contribute to data anomalies and inconsistencies, for example:

• If you delete second row, you would delete “THampton” and “Canada” from database, which is deletion anomaly
• You can’t add a new author to database unless you also add a book
• If “JDoe” changes Nationality, you would need to change every place where it appears
• You can’t delete an author such as “RKansas” without deleting the BookID “2154348”

To fix this, we can remove AN and create it in a separate table.

Now we have two tables, “Book” and “Author”. AuthorID in the Book table is a foreign key, as it is the primary key in the Author table.

This will provide better data flexibility and integrity. For example:

• If you delete “2154348” from Book table, the author information with AuthorID “7804” won’t be lost
• If you want to update an author’s information, you can just find the author in the Author table and update it. You don’t need to search for every place
• You can add a new author as you want in Author table and reference it later

Let’s look at the next form.

Boyce-Codd Normal Form (BCNF)

BCNF Normal Form is sometimes referred as 3.5NF, and it requires:

1. Third Normal form should be satisfied
2. For every dependency A -> B, A should be the super key of the table

Simply put, the second point means, for a dependency A -> B, if B is a prime attribute, A must be a prime Attribute too.

It’s rare if a table satisfies 3NF but does not satisfy BCNF. BCNF is a slightly stronger version of the third normal form.

For example:

Here two different students can select the same subject and one student can select two different subjects. That is to say, ‘StudentID + Subject’ can be used as the primary key, as the two together uniquely identifies each row.

In this case, StudentID and Subject are prime attributes (See what prime attributes is from 2NF section) while Professor is non-prime. Yet, as professors determine subjects (Professor -> Subject), we have A -> B while B is a prime attribute but A is a non-prime attribute. This doesn’t satisfy BCNF and might trigger anomalies.

To fix that, we can remove the Professor column and put it into a separate table, which forms:

BCNF Normal Form is a more restricted form of normalization and provides even better database integrity.

NoSQL Database

NoSQL, which stands for “Non-SQL” or “Not Only SQL”, is a database design approach that provides flexible schemas for the storage and retrieval of data beyond the traditional table structures found in relational databases.

Even if we call it NoSQL, it in general still supports SQL like queries and in a modern world of microservices, NoSQL and traditional SQL databases are commonly used together in a single application.

The fundamental differences between SQL and NoSQL are not all that complicated. Each just has a different way of storing and retrieving data.

With SQL databases, all data has an inherent structure, a schema – a formal definition of how data should be inserted into a database. For example, a column in a table is integer type and will accept only integer values. This clear definition is somewhat rigid, but on the other hand provides easy aggregation as well as reliability.

In the world of NoSQL, data can be stored in a schema-less or free-form fashion. That is, there will be no rigid rules around, rather flexible data models and operations. Generally, there will be four common types of NoSQL models:

• Key-value store: Stores data with simple indexed keys and values. Examples include Oracle NoSQL database, Redis, Amazon DynamoDB
• Graph database: Presents interconnected data as a logic path. Examples include GraphDB, Neo4j
• Document database: A more complex and structured version of key-value model, where each document has its retrieve key. Examples include: MongoDB, Couchbase
• Wide Column store: Uses tables, rows and columns. But the format and naming of the columns can vary in different rows within the same table. Examples include Apache Cassandra, Apache HBase

SQL VS NoSQL

Here is a summary with a comparison between SQL and NoSQL.

Use SQL if:

• You are working with complex queries and reports. With SQL, you can build one script that retrieves and presents your data. NoSQL doesn’t support relation between data types. Running queries in NoSQL is doable, but slower.
• You have a high transaction application. SQL databases are a better fit for heavy duty or complex transactions because it’s more stable and ensures data integrity.
• You need to ensure ACID compliance (Atomicity, Consistency, Isolation, Durability) or defining exactly how transactions interact with a database.
• You don’t anticipate a lot of changes or growth. If you are not working with a large volume of data or many data types, NoSQL would be an overkill.

Use NoSQL if:

• You are constantly adding new features, functions, data types and it’s difficult to predict how the application will grow over time. Changing data models in SQL is troublesome which requires code changes. A lot of time is invested designing the data model because changes will impact all or most of the layers in the application. Yet, in NoSQL, we are working with a highly flexible schema design or no predefined schema. The data modeling process is iterative and adaptive. Changing the structure or schema will not impact development cycles or create any downtime for the application.
• You are not concerned about data consistency and 100% data integrity is not the top goal.
• You have a lot of data, many different data types, and your data needs will only grow over time. NoSQL makes it easy to store all different types of data together and without having to invest time into defining what type of data you’re storing in advance.
• Your data needs to scale up, out, and down. NoSQL provides much greater flexibility and the ability to control costs as your data needs change.

Using both SQL and NoSQL together in a system is doable when you want to leverage advantages of both databases. For example, you can have a fat, non-distributed, unscalable relational database as the single source of truth in your system, where you use NoSQL as the layer to intercept the bulk of the traffic for database operations. The point is to fit the right database for your contextual needs.