Design for Databases & SQL

Instructor: Sudha Aravindan

Sudha is currently an Information Technology Specialist and a EdD student at the University of Delaware.

An SQL database is a relational database that makes uses of the Structured Query Language (SQL) to query or communicate with the database. In this lesson, we will learn more about SQL databases.

Databases and SQL

A database is a collection of data organized in tables. A flat database is where all the information about any one event or transaction is stored in a single row in one large single table. However, a database can use Structured Query Language (SQL) to access and update data in an organized fashion. SQL (pronounced as s-q-l) stands for Structured Query Language. It is a language for managing data in a database.

SQL is used to search and find data from multiple tables in an SQL database. In this configuration, data is separated into tables. This helps to breakup and organize the data. The data from the different tables can be combined and extracted using SQL.

You may often hear the term SQL Database to refer to these types of databases. Although this is an accurate reflection of how the databases manage and query data, there is no single SQL database per se. You can use SQL within most database management systems.

For example:

If the same student borrows books from a library on different days, the flat database will contain information about student name, student email, date, and book for day one; and the same information will be repeated for day two. In this case, the flat database may look something like this:

  • Joe, j@email.com, 01/01/2016, Elementary Math; Joe, j@email.com, 01/04/2016, Biology Text Book; Mary, m@email.com, 01/01/2016, Science Workbook; Joe, j@email.com, 01/05/2016, History Text...

The data can become difficult to read since data is repeated and also saved in the same row in the database.

Relational Databases

The databases that truly harness the power of SQL are called relational databases. In a relational database, data is stored in multiple tables instead of in a single row. The different tables are related or connected using a common field called the key that is common to one or more tables. Using SQL, the data from multiple tables can be combined to get the information that we need.

If the library data in the above example is stored in a relational database, we could break up the long row of data into multiple tables. In a relational database, one table can contain the student contact information, and the other table can contain information about the books. Both tables can then be linked by a common field or key so that we can connect the data from the two tables to find a list of all the books borrowed by Joe.

A relational database with Joe's data could look something like this:

Table 1: student-table

student-id student-name student-email
054 Joe j@email.com

Table 2: borrowed-books-table

student-id borrowed-date book-name
054 01/01/2016 Elementary Math
054 01/04/2016 Biology Text Book

Here the student-table and borrowed-books-table are related or connected through the common field student-id. Each time Joe borrows a book, since his name and email do not change, the student-table does not have to be updated, only the borrowed-books-table needs to be updated.

If another student, say Ann with student id 021, borrows a book, the first time one row will be added to the student-table and one row will be added to the borrowed-books-table. The next time Ann borrows a book only the borrowed-books-table will be updated.

Let's look at the two tables after Ann first borrows a book:

Table 1: student-table

student-id student-name student-email
054 Joe j@email.com
021 Ann a@email.com

Table 2: borrowed-books-table

student-id borrowed-date book-name
054 01/01/2016 Elementary Math
054 01/04/2016 Biology Text Book
021 01/03/2016 English Grammar

Primary Keys

In relational database table, there should be at least one field that has unique or non-repeating values to identify that table. This field is known as the primary key. In the student-table for example, we can use student-id as a primary key since that will be an unique value to identify each student. This allows you to use SQL to correctly select the right records.

In the borrowed-books table, you will notice that there is currently no unique field for each row of the table. So if there is more than one copy of the same book there is no way to uniquely identify each of the copies. To solve this we can create an unique ID and call it book-id where each copy of the book has its own identifying piece of information.

To unlock this lesson you must be a Study.com Member.
Create your account

Register to view this lesson

Are you a student or a teacher?

Unlock Your Education

See for yourself why 30 million people use Study.com

Become a Study.com member and start learning now.
Become a Member  Back
What teachers are saying about Study.com
Try it risk-free for 30 days

Earning College Credit

Did you know… We have over 160 college courses that prepare you to earn credit by exam that is accepted by over 1,500 colleges and universities. You can test out of the first two years of college and save thousands off your degree. Anyone can earn credit-by-exam regardless of age or education level.

To learn more, visit our Earning Credit Page

Transferring credit to the school of your choice

Not sure what college you want to attend yet? Study.com has thousands of articles about every imaginable degree, area of study and career path that can help you find the school that's right for you.

Create an account to start this course today
Try it risk-free for 30 days!
Create An Account
Support