Back To CourseRelational Databases Tutorial
10 chapters | 112 lessons
As a member, you'll also get unlimited access to over 75,000 lessons in math, English, science, history, and more. Plus, get practice tests, quizzes, and personalized coaching to help you succeed.Try it risk-free
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 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.
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
|Aden K. Samson||(555)234-1221|
|John White (Witty)||(555)234-1236|
|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.
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
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.
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.
|Names Table||<= Name-Numbers X-ref Table =>||Phone Numbers Table|
A good linked key needs to be unique. Some of the simplest are unique sequential numbers: 1, 2, 3, 4, 5, and so on. In this example, name-numbers x-ref table is used to match the numbers with the names, although the primary use of this table is to match multiple phone numbers for the same person. You can also make the x-ref table work the other way when two persons share the same phone number. There are copies of the linked keys in both the name-numbers x-ref table and in each of the linked tables.
Under the new design, the name-key is a handle used to access the names information, and the names table only contains names information.
Simple Phonebook 3: Names Table
Likewise, the phone-key is a handle used to access the phone numbers information, and the phone numbers table only contains phone numbers information.
Simple Phonebook 3: Phone Numbers Table
And the name-numbers x-ref table contains a linkage that ties the names to the phone numbers.
Simple Phonebook 3: Name-Numbers X-ref Table
The schema for this database describes three tables: Simple Phonebook 3, names table, phone numbers Table, and name-numbers x-ref table. The schema describes the names table as containing five fields: name-key, name-last, name-middle, name-first, and name-nick. This table has a unique key: name-key. There is no specific indication, but any other field may be a sort key.
The schema for phone numbers table has three fields: phone-key, phone-area cd, and phone-number. This table has a unique key: phone-key. There is no specific indication, but any other field may be a sort key.
The schema for the name-numbers x-ref table has three fields: x-ref-key, name-key, and phone-key. This table has a unique key: x-ref-key. Name-key and phone-key are sort keys that allow the database engine to quickly link or join entries in the names table with their corresponding entries in the phone number table.
Let's take this useful information for a test drive by looking up a phone number. Here's our question: What is Mickey's phone number or numbers?
When designing the data structures needed for any system, make sure to include the ability to store any data you anticipate needing. Remember that it's much more difficult to add missing information later.
Let's do a quick review. A database schema provides the blueprint that defines what the database is. The schema is at the heart of database operations and tells the database engine how the data is organized. A well-designed schema lets the database work effectively in storing and retrieving data. The schema provides the framework for the database operations and contents.
To unlock this lesson you must be a Study.com Member.
Create your account
Already a member? Log InBack
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
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.
Back To CourseRelational Databases Tutorial
10 chapters | 112 lessons