Designing and Creating a Database

This chapter introduces you to the process of designing and creating a database. We'll walk through the guidelines for the naming conventions and learn about the SQL code errors. You will learn how to format SQL code for readability, apply data types, and apply data integrity in your tables. You will also learn about the different types of table relationships and how to build entity-relationship diagrams. You will also learn how to create a database and a table in a database. Going further, we will discuss the concept and usage of indexing. Then, you will gain an understanding of how indexing helps database performance.

In this chapter, we will cover the following topics:

• Creating a database
• Understanding table relationships
• Creating a table in the database
• Understanding indexes

Technical requirements

The code files of this chapter can be found at https://github.com/PacktPublishing/learn-sql-database-programming/tree/master/chapter-4.

Creating a database

In Chapter 1, Introduction to Relational Database Management Systems, we learned what a database is and its essential parts, such as tables, columns, rows, and fields. In this section, we will learn how to create a database. We'll also go through the guidelines for naming conventions and discuss SQL code errors.

Guidelines for naming conventions

Naming conventions are essential for multiple reasons. You must ensure that you name things accurately and descriptively, but, at the same time, avoid keywords that will create confusion. You should avoid spaces, choose the proper case, and stick to one convention. You should make sure that you use only permitted characters when naming a database object. Each...

Understanding table relationships

As we covered in Chapter 1, Introduction to Relational Database Management Systems, relational databases contain tables that are related to one another. Once you need to query more than one table, you can rely on these relationships to join the tables together. There are three types of table relationships: one-to-one, one-to-many, and many-to-many. First, let's go over how to display these relationships in a diagram.

Understanding entity-relationship diagrams

You can use entity-relationship diagrams to visualize table relationships. This is sometimes referred to as an E-R diagram, or an ERD. In MySQL Workbench, the entity-relationship diagram, is referred to as an enhanced entity-relationship...

Creating a table in the database

Now that we've created a database, we can walk through how to create a table in the database. We need to remember what we learned in Chapter 3, Understanding Data Types, regarding how to correctly apply data types. We also need to remember what we covered in Chapter 1, Introduction to Relational Database Management Systems, about data integrity. This information will form a vital part of our method when we properly create a table.

Understanding how to apply data types and data integrity to your table

Let's briefly review what we covered about data integrity in Chapter 1, Introduction to Relational Database Management Systems.

Database integrity refers to the consistency and accuracy...

Understanding indexes

This section will take the reader through an explanation of what indexing is, how it relates to data integrity, and how it impacts performance.

Indexing is a method of optimizing database performance to boost the speed of database queries. Indexes are placed on column(s) in a table. Tables can have more than one index, but there tends to be an optimal number of indexes before indexes impair performance instead of helping it. The optimal number can vary depending on the table. This is why index tuning can be an art as well as a science. To properly index a table, you need an understanding of how to streamline the process of query results that are returned to a user.

It's good to plan out the indexing that you will need before adding data, if possible. When you add an index to an empty table, it adds it pretty much instantaneously. It can take quite a...

Summary

This chapter covered how to design and create a database. We discussed the guidelines regarding naming conventions, understanding SQL code errors, formatting SQL code for readability, and how to apply data types and integrity to your tables.

You learned about the types of table relationships, including one-to-one, one-to-many, and many-to-many relationships. You also learned about entity relationship diagrams, including how to create them and how to understand table relationships in them.

We also discussed indexing, and learned about what it is, how to use it, and what it means for database performance. We also went through the steps of creating a database and table using MySQL Workbench and SQL scripts.

In the next chapter, you will be introduced to importing and exporting data. There are many ways to import and export data in MySQL. You will learn how to import and...

Further reading

For more information:

• For more information on SQL Server, visit https://docs.microsoft.com/en-us/sql/t-sql/lesson-1-creating-database-objects?view=sql-server-ver15.
• For more information on Oracle, visit ;https://docs.oracle.com/en/database/oracle/oracle-database/18/cncpt/tables-and-table-clusters.html#GUID-F845B1A7-71E3-4312-B66D-BC16C198ECE5.
• For more information on PostgreSQL, visit https://www.postgresql.org/docs/11/ddl-basics.htm.
• For more information on filtered indexes, visit https://docs.microsoft.com/en-us/sql/relational-databases/indexes/create-filtered-indexes?view=sql-server-ver15. In PostgreSQL, this is called a partial index. For more information on this, visit https://www.postgresql.org/docs/11/indexes-partial.html.

Questions

1. What things should you avoid when naming database objects?
2. What characters are allowed when naming database objects?
3. What is the SQL syntax to create a new database?
4. Where can you find SQL error codes in MySQL Workbench?
5. What are natural and surrogate primary keys?
6. How do you make a single-line comment?
7. How do you make a multiline comment?
8. What's the difference between a clustered and a nonclustered index?
9. What is deadlocking?
10. Does the order of columns in an index matter?