SQL stands for Structured Query Language. SQL is the language used to create, edit and manipulate a database. In other words, SQL is used to manage data held within a relational database management system (RDBMS).
Because this is a database design series, we will not be working with SQL directly, but will design our database to work with SQL in the future (once it is completely designed and ready to be programmed).
SQL is the general language used to communicate with relational database management systems. This means that we use SQL to communicate to MySQL, Oracle, SQL Server, etc… So learning about SQL will help you with a lot of different things! A RDBMS takes SQL and uses it to do something with the database. The SQL can come directly from us hand-typing it or it can come from another source (such as a PHP script).
For those of you who need help learning SQL, check out my MySQL series (67 videos!) But remember, these videos are not required to understand what is written in this blog, only for those who want to study deeper:
Within SQL, we have two forms of languages. These forms differ in that one is used to build and edit the structure of the database while the other is used to create and edit the actual data within the database. These two languages are known as data definition language and data manipulation language.
Data Definition Language (DDL)
Data definition language is one of the subcategories of SQL. It is used to define and work with the database schema (structure). This includes the attributes (columns) within each table, the name of each table, the name of the database, and the connection of keys between tables. Here are general explanations of the types of commands in DDL:
- CREATE – used to create the database, the tables, and the columns within each table. Within the create statement we also define the data type of each column. A data type is literally the type of data we are supposed to store within each column, whether it be an integer, a date, or a string.
- ALTER – used to alter existing database structures. This includes adding columns and more.
- RENAME – This is used to…rename. haha
- DROP – This is used to destroy your database or table.
Another feature of SQL is maintaining referential integrity. Referential integrity connects IDs from multiple table to tell the database that they are referring to the same thing:
|Sale ID||Buyer ID||Item|
|324||2032||A new bicycle|
With referential integrity, we can make it so that we can not have a buyer who is not also in the users table! This connection is known as a foreign key constraint. A foreign key references the primary key of another table! In this case, the primary key is the user ID and the foreign key is the Buyer ID.
Although this is a pretty small example, SQL can understand that the Buyer ID is connected to the User ID and that they are both talking about the same entity (person in this example).
Referential integrity is defined within the CREATE or ALTER statement of DDL.
When we are designing a database, we only have to think about how things are connected, we do not have to worry about the actual programming needed to create referential integrity. Data definition language and data manipulation is only something you need to know about! You will not need to use DDL or DML unless you are actually programming with SQL! Sometimes you are responsible for programming the database and other times you hand the design to a programmer.
Data Manipulation Language (DML)
Data manipulation language is used to work with the actual data within the database. if we looked at an example with a users table, the table is created with DDL while the value “Caleb Curry” is entered using DML.
The main statement in DML are:
- SELECT – this is used to select data from our database. We first say SELECT and then we say what columns to select. After we say what columns, we specify what tables using FROM. After we select what columns and what tables we can limit our results using a WHERE clause. Example: SELECT user_id FROM users WHERE user_id = 5;.
- INSERT INTO – This is used to insert new values.
- UPDATE – This is used to change values.
- DELETE – this is used to delete values (the database structure stays the same, only inserted values are removed).
The difference between DDL and DML:
An important thing that I have not yet explained is naming conventions. A naming convention is a common naming pattern to help keep things consistent…not only within our database but all databases. When it comes to database design, I usually write everything in English first for clarity. Before I program a database, I will convert titles to the appropriate titles to program with SQL. When I program in SQL, all man-made values are lower-case with no spaces. All SQL keywords are in all capitals. This allows us to clearly see the difference between keywords and man-made words:
SELECT user_id FROM users WHERE user_id = 5;
Looking at this, you can tell that SELECT, FROM, and WHERE are all SQL keywords. An SQL keyword is a predefined word that is used to write SQL code. You can also tell that user_id and users are man-made values. When creating this database we could have named the table and columns anything we wanted! Because we have this flexibility, we keep these words separate from the SQL keywords for clarity.
Keywords = CAPITAL
man-made words = lowercase_with_no_spaces.
This is a naming convention, not a requirement. I would also like to add that many different naming conventions exist depending upon the RDMBS system that you use! Be open to learning new conventions! This means that different people will do different things, such as capitalize ID in user_ID, or make it UserID. The uppercase keywords and lowercase man-made words is the convention I have chosen, but you are free to experiment with your own.
Although SQL conventions are important, SQL is only a part of creating a database. It is more important to have a good database design. Keep in mind that for a lot of the design concepts we will be using things in “plain English.” I do this because we want to understand completely what each column/table is supposed to do/have!
Database Design is the process of both planning the structure of the database and also how it will be used or manipulated. Keep in mind that you don’t need to know the actual SQL code though unless you want to create the database. We will not actually be doing any SQL programming in this blog series but only design concepts that are extremely important in databasing. You should never start programming a database with SQL before you plan everything. If you plan things, you can figure out exactly what code to use to create the database as planned. You’ll want to do this because once you create the database you don’t want to have to keep changing the underlying structure…It can cause problems and can take forever!
- SQL is given to the RDBMS to work with the database structure and values.
- SQL has two subcategories, DDL and DML.
- DDL is used for the database structure.
- DML is used for the database values.
- Naming conventions are important.
- The naming convention I use for SQL is keywords=capital and man-made words=lowercase with no spaces.
- Database design is important before working with SQL!
- Plan everything!
- When designing, sometimes it is best to use plain English so we are sure exactly what each table or column is used for.