Copyright

What is a Schema in SQL? - Definition & Design

Instructor: David Gloag

David has over 40 years of industry experience in software development and information technology and a bachelor of computer science

SQL provides an organized and systematic approach to accessing information. In this lesson, we'll take a look at SQL, what a schema is in relation to SQL, and how to go about creating one.

The Need for Order

Information comes to a business from all directions, and the business does its best to capture it when it does. Online retailers capture contact information from their customers, cell phone companies capture payment information from their users, and streaming companies capture subscription information from their subscribers. And why not? These activities ultimately generate revenue. But is it enough to simply capture the information? Does that provide everything necessary to see the money? No. In reality, the information must be organized, searched, and massaged to bring things to that point. Two of the tools necessary to complete this process are SQL and schemas.

What is SQL?

SQL is an acronym that represents structured query language. It is designed to access and manipulate information in a database. Released by Oracle in 1979 as a commercial product, it was actually developed by IBM in the early 1970's. As a language, it provides many commands that allow you to perform a wide variety of operations on your data. Some notable commands include:

  • SELECT - used to access and retrieve information in a database.
  • INSERT - used to add or augment information to a database.
  • UPDATE - used to change or adjust information that already exists in a database.
  • DELETE - used remove or dispose of information from a database.
  • CREATE - used to generate a new object in a database.

Collectively, SQL provides a structured environment for accessing and maintaining your information.

What is a Schema in SQL?

A schema in SQL is a template: A pattern that describes characteristics about the information a database will store. In particular it describes:

  • Type - the type of the information refers to a specific piece of information, and the general attributes of that information. For example, integers can be positive or negative and don't have a fractional part. This knowledge can make storing them more efficient.
  • Size - the size of each piece of information determines how much space it will occupy in the database. Even with the price of storage coming down, it is still not practical to have an infinite amount. This must be recognized when designing, building, and maintaining a database.
  • Organization - this refers to how the information is grouped and stored, and it is greatly affected by its intended use. Think of it like packing your vehicle for a vacation. Items that are used often along the way are placed where you can reach them. Items that are not go in the trunk.

Why are Schemas Important?

Schemas are important because they introduce structure to your information, structure that can help us to locate a specific piece of information when the need arises. Consider a library as an example. Would finding a specific piece of information on a particular topic be easy if the books weren't catalogued, arranged on shelves, sorted alphabetically, and individually numbered within each letter? Clearly, it wouldn't. It would be a mess, and you'd likely be pulling your hair out. The library paradigm provides a structured solution to the problem. The SQL schema provides the same capabilities for a database.

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 200 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