SfC Home > Web Design > SQL >
Database Basics
by Ron Kurtus (revised 17 March 2016)
A database is a structured collection of data that is stored in a computer system. You typically give your database some sort of identifying name, as well as its file name. Some database applications are Microsoft Access and MySQL databases.
Data is organized in one or more tables within the database. Each table consists of lists of information or data in its columns and rows. Columns are named and each must have its data-type defined. Rows are typically numbered according to the primary key column.
Computer languages, such as SQL, allow the user to retrieve 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.
Database tables
A table is structured file that stores a specific type of data.
For example, consider an auto_supply database that an auto repair shop may have on their computer or website. This database could consist of the following tables:
- customer_information
- parts_information
- auto_brands
Each table is a listing of one type of data, separate from the others. A typical table could be:
| id | part | part_no | cost |
|---|---|---|---|
| 1 | hood | 03-060 | 500 |
| 2 | fender | 07-340 | 750 |
| 3 | bumper | 05-021 | 135 |
parts_information table
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.
Access information with SQL
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 and references
Websites
Books
(Notice: The School for Champions may earn commissions from book purchases)
Students and researchers
The Web address of this page is:
www.school-for-champions.com/sql/
database_basics.htm
Please include it as a link on your website or as a reference in your report, document, or thesis.
Where are you now?
Database Basics