SfC Home > Web Design > SQL >
Explanation of Database Basics - Succeed in Using SQL. Also refer to Structured Query Language, Access, MySQL, MS SQL Server, information, column, field, data type, datatype, row, record, primary key, search, update, Ron Kurtus, School for Champions. Copyright © Restrictions
Database Basics
by Ron Kurtus (8 May 2009)
A database is a structured collection of data that is stored in a computer system. Data is organized in one or more tables within a database.
Each table consists of lists of information or data in its columns and rows. Columns are named and each must have its datatype defined. Rows are typically numbered according to the primary key column.
Computer languages, such as SQL, allow the user to get information from the database.
Questions you may have include:
- What is a database table?
- What are properties of columns and rows?
- How can the user get information from the database?
This lesson will answer those questions. There is a mini-quiz near the end of the lesson.
Database and tables
A database is a collection of data stored in an organized manner in a computer. Some database applications are Microsoft Access and MySQL databases.
You typically give your database some sort of identifying name, as well as its file name.
Tables
A database consists of one or more tables.
A table is structured file that stores a specific type of data.
For example, consider the "auto_supply" database that an auto repair shop may have on their computer or website. This database consists of the following tables:
- customer_information
- parts_information
- auto_brands
Thus, the customer_information is a listing of one type of data, separate from the parts_information list.
A typical table is:
| id | part | part_no | cost |
|---|---|---|---|
| 1 | hood | 03-060 | 500 |
| 2 | fender | 07-340 | 750 |
| 3 | bumper | 05-021 | 135 |
A database table consists of columns and rows, similar to tables you may see in a document or like a spreadsheet grid. In fact, a spreadsheet is a special form of a database.
Columns and rows
A database table consists of columns and rows.
Columns
Each column is given a distinct name. Columns are also called fields.
In the parts_information table, the names of the fields are id, part, part_no and cost.
Data types
A specific data type (or datatype) must be defined for each column. Simply put, the id is an integer, part is a text datatype, part_no is a number and cost is a number.
There is a whole list of datatypes available for a database table. By identifying the datatype, you allow for better control and manipulation of the data.
Keys
Every table much have at least one key column, which will identify the items in the row. The key may be a part number or a primary key integer.
The id column is called a primary key. It identifies each row in the table with a distinct integer. A null value is not allowed in a primary key field, and the numbers are listed in order.
The part_no column is a key column, since it can be used to identify the part. For example, there may be several available fenders
Rows
A row indicates a record of specific data. For example, in the parts_information table, row id 2 is a record of the cost ($750) of a fender.
Database language
Although a database is used to store data and information in an organized manner, you all need to have a way to access the information you want. The Structured Query Language (SQL) is a common computer language used to obtain specific information from the database.
With SQL, you can search the records (rows) in a database table for a specific identifying number or word. You can list and sort information, as well as add new records, edit existing ones or completely delete a record.
Summary
A database is a structured collection of data organized in one or more tables. Each table consists of columns and rows. Columns are named and each must have its datatype defined. Rows are typically numbered according to the primary key column. A computer language, such as SQL, allows the user to get information from the database.
Always start with the basics
Resources
The following are resources on this subject.
Websites
Books
Mini-quiz to check your understanding
If you got all three correct, you are on your way to becoming a Champion in working with SQL. If you had problems, you had better look over the material again.
What do you think?
Do you have any questions, comments, or opinions on this subject? If so, send an email with your feedback. We will try to get back to you as soon as possible.
Share link
Feel free to establish a link from your website to pages in this site.
Or use our form to send this link to yourself or a friend.
Students and researchers:
The Web address of this page is
www.school-for-champions.com/sql/database_basics.htm.
Please include it as a reference in your report, document, or thesis.
Where can you go from here?
Database Basics