Copyright

What is a Database Schema? - Example & Definition

An error occurred trying to load this video.

Try refreshing the page, or contact customer support.

Coming up next: What is a Database Index? - Definition & Tutorial

You're on a roll. Keep up the good work!

Take Quiz Watch Next Lesson
 Replay
Your next lesson will play in 10 seconds
  • 0:00 What Is a Database Schema?
  • 0:37 Schema Theory
  • 2:04 A Simple Database
  • 2:55 A Better Database
  • 3:46 A Useful Database
  • 7:03 Lesson Summary
Add to Add to Add to

Want to watch this again later?

Log in or sign up to add this lesson to a Custom Course.

Login or Sign up

Timeline
Autoplay
Autoplay
Speed

Recommended Lessons and Courses for You

Lesson Transcript
Instructor: Louay Chebib
A database schema is the blueprint that defines the database structure. The schema tells the database engine, the program that runs the database, how to put and get information in a database. The schema provides the framework for the database operations and contents.

What Is a Database Schema?

Ever wonder how a database is able to store the information you give it, and give it back to you when you need it?

A database is a place to store information. Databases can store something as simple as a list of items or as complicated as all the data needed by a large company. Databases contain structured collections of information. The database schema contains the design or list of attributes and instructions that tells the database engine how the data is organized and how the components are related. Let's look a little closer at database schema.

Schema Theory

Schema theory is a bit complex, but let's go over a basic explanation and then look at a basic database example.

Under schema theory, knowledge is organized into units. Units of knowledge, or schemata, contain or store information. As such, a schema is a conceptual system for understanding knowledge or a generalized description of the knowledge. A schema contains a description of how knowledge is represented and how it's used.

The basic database unit is the table. A table is a unit consisting of rows of related information. Each row consists of fields of information where data is stored. Field attributes include information and rules that govern the data stored in the field. The field attributes and rules may limit the type of data stored in the field.

A field may be defined as a key or may be limited by rules requiring specific masks, such as a field may limited to dates, formatted numbers like telephone numbers, or be limited to a specific number of characters. The database schema contains these rules.

A database schema is best understood by the use of examples. In the following examples of database tables, the field names are listed in the headers, and the data contents (rows) are listed below the headers. In considering these examples, remember that the schema describes the attributes of the database; it does not include the data that the database contains.

A Simple Database

Consider your personal phonebook as a simple database example. The important information in a personal phonebook is a name and a phone number. This information may be kept in a list or table consisting of two fields: name and phone number. Sorting this list by name should help you quickly find the phone number you need. In these examples, the fields contain data (such as names and phone numbers). Some of the fields may be used as keys.

Simple Phonebook 1 Table

Name Phone Number
Aden K. Samson (555)234-1221
Joe Brown (555)234-1233
John Smith 234-1234
John White (Witty) (555)234-1236
Madge White (555)555-1234
Mickey M. (Diz) 888-1234
Zoe Snow (Red) (555)919-2234

The schema for this simple database describes a single table. This table has fields intended to store names and phone numbers. In this database, there is one table: Simple Phonebook 1 Table. This table has two fields: name and phone number. There is no specific indication, but either field may be a sort key. A sort key allows the database engine to quickly sort the table into a specific order.

A Better Database

The structure used in the previous simple database schema has its limits. In this database's table, all the name values are entered as first name and last name. What happens if you only remember the last name or nickname of the person you want to call? A better way is to separate the name into its components. Also, look at the phone numbers contained in the sample. Many of these entries do not have an area code. The following database design improves on the simple database by separating the data into their own fields.

Simple Phonebook 2 Table

Name-Last Name-Middle Name-First Name-Nick Phone-AreaCD Phone-Number
Aden K. Samson (Sam) (555) 234-1221
Joe Brown (555) 234-1233
John Smith 234-1234
John White (Witty) (555) 234-1236
Madge White (555) 555-1234
Mickey M. (Diz) 888-1234
Zoe Snow (Red) (555) 919-2234

The schema for this database again describes a single table: Simple Phonebook 2 Table. This table has six fields: name-last, name-middle, name-first, name-nick, phone-area-cd, phone-number. There is no specific indication, but any field may be a sort key.

A Useful Database

The structure used in the better database schema is much more useful since you can now sort your data into different ways depending on what you're looking for. But, what happens if someone has more than one phone number? You could keep adding phone number fields, or you could separate the phone numbers into their own table and associate these with the names using a linked key to associate the phone numbers with the names. A linked key is used to link entries in one table with the associated entries in another table.

In the following example, a new table is needed. The name-numbers x-ref table contains the information needed to associate the name information from the names table with the numbers information from the phone numbers table.

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