This chapter introduces the concepts required to understand the basics of relational database management systems (RDMS). It will introduce foundational topics such as SQL, the relational model, data integrity, database normalization, and the types of relational database management systems. It will provide you with fundamental knowledge about SQL and databases that will be required throughout this book.
In this chapter, we will cover the following topics:
- Understanding SQL
- Understanding databases
- Understanding data integrity
- Understanding database normalization
- Types of RDMS
Structured Query Language, or SQL (pronounced see-quel), is the language that is used for querying and manipulating data and defining structures in databases. Initially developed at IBM in the early 1970s, SQL became an ANSI and ISO standard in 1986.
SQL is a powerful, yet simple language, and can do many things, such as execute queries, retrieve, insert, update, and delete data, create databases and tables, and much more.
These types of activities can be grouped into different subdivisions of SQL: Data Definition Language (DDL), Data Manipulation Language (DML), and Data Control Language (DCL):
- Use DDL commands to specify database schema:
- CREATE: This is used to create a new database or objects in a database.
- ALTER: This is used to alter a database or objects in a database.
- DROP: This is used to delete a database or objects in a database.
- TRUNCATE: This is...
A database is a collection of data. You store databases in a relational database management system (RDMS). The RDMS is the basis for modern database systems like MySQL, SQL Server, Oracle, PostgreSQL, and others. These will be covered in more detail later in this chapter.
In an RDMS, objects called tables store data. Tables are a collection of related data stored in columns and rows. The following screenshot is a cross-section of a table that contains data about baseball players' appearances in all-star games:
A NULL value in a table is a value that appears to be blank. It doesn't represent a string of blank spaces, zero, or a zero-length character string: it's a missing or unknown...
Data integrity refers to the consistency and accuracy of the data. It is typically enforced by the procedures and guidelines in the database design phase. In RDMS, keys enforce data integrity. A key is user-defined and forces values in a table to conform to a specified standard. This standard will allow only certain kinds of values to be in the database.
Data integrity refers to the consistency and accuracy of data and table relationships. The following table lists the types of integrity you can use:
Not null constraint
Database normalization is the process of putting your raw data into tables using rules to avoid redundant data, optimize database performance, and ensure data integrity.
Without proper normalization, not only can you have data redundancy, which uses additional storage space, but it can be more difficult to update and maintain the database without data loss.
Normalization requires forms. Forms are sets of rules to follow to normalize your data into database tables. There are three forms that we will discuss: the first normal form, the second normal form, and the third normal form. Each of these forms has a set of rules to ensure that your database complies with the form. Each of the forms builds on the previous forms.
An RDMS is a database that stores data in tables using rows and columns. The values in the tables are related to one other, and the tables may also be related to one another, hence the term relational. This relationship makes it possible to access data across multiple tables with a single query.
In this section, we will review the top four relational database management systems. The top four are Oracle, MySQL, SQL Server, and PostgreSQL.
According to the DB-Engines Ranking, here are the scores for the top RDMSes at the time of writing this book:
The preceding screenshot can be found at https://db-engines.com/en/ranking.
Oracle was first released in 1979. Oracle was the first commercially available SQL...
This chapter introduced the concepts required to understand the basics of relational database management systems. It introduced you to foundational topics such as understanding SQL, what SQL can do, and its basic components. You learned that there are three subdivisions of SQL called DML, DDL, and DCL, and that the SQL language is comprised of several elements that make up a statement. We walked through a description of the relational model, what a database is, and what is in a database, including what a table, row, column, and field are.
We followed with an explanation of data integrity, including the different types of data integrity, such as entity, referential, and domain integrity, and looked at how to use keys and constraints. This understanding of data integrity helped you understand database normalization, including the different forms of normalization, 1NF, 2NF...
- What is SQL?
- What are the different subdivisions of SQL?
- What are the elements of a SQL statement?
- What are the reasons to normalize a database?
- What are the levels of database normalization?
- What is data integrity?
- What are the different ways you can enforce data integrity?
- What types of RDMS exist?
- What is the main advantage of MySQL?
- What is the main disadvantage of Oracle and SQL Server?