Home

Introduction to Databases
 

Overview of Lists
 

Introduction

A list is an arrangement of items that fit in the same category. For example, you may be given a list of employees of a company. The list of such employees can be made of names such as Hermine, Walter, Alain, Suzie. Such a list is also said to be single dimensional because it includes only a specific category of items. In this case, the list is made of their (first) names.

Imagine you want to create a better list of employees of a company. You can use a piece of paper on which you would draw vertical and horizontal lines. The vertical lines are used to create categories of information. The first vertical line can be used to separate the first names from the last names. The second line can be used to separate the last names from employees addresses. Such categories can be created on a piece of paper as follows:

Each category created as such is called a column.

With the categories designed, you can enter information under each category. This display makes it easy to organize information for data entry. You can then fill out the columns. Here is an example:

Such a list is said to be multi-dimensional because the list include various categories of information.

To make information easily readable, you would include horizontal lines that separate each entry from the other. Here is an example:

As you can see, the horizontal lines allow you to see what information is entered and what is missing.
 

Introduction to Tables

A table is a 2-dimensional list of items as represented in the above example. Each vertical list organizes information by category. A category of information in a table is also called a column. The horizontal lines in our example organize information into items called records. A record in a table is also called a row. Each record is spread horizontally and should have an entry for each column unless the entry for the category is not available or is not provided, for any reason. Each entry into the list is referred to as a cell or field.

The information that is part of a database is stored in tables.
 

Databases Fundamentals
 

Introduction to Computer Databases

To make a list easier and faster to use, it can be stored in a computer file. Such a file is fancily called a database. The computer is meant to help a human being with this type of list. The computer has advantages and disadvantages.

Advantages of the computer are:

  • The computer can store millions of pieces of information, more than any human being can
  • Everything considered right, the computer cannot forget. That is, the computer cannot have a short memory
  • Because the computer cannot think as we will mention next, it cannot confuse one piece of information with another piece of information
  • The computer can faithfully follow directives (again, this is because the computer cannot think)
  • The computer can offer a (high or very high) level of security to prevent intruders from messing with your list(s)

Disadvantages of the computer are:

  • The computer cannot think. It can only store and process the information it is given
  • The computer can "get sick" (crash) or even die. If this happens, all the information it was holding can be lost (of course, there are always techniques used to retrieve information from a dead computer)
 

Practical Learning Practical Learning: Starting MSDE
  1. If you are using MSDE, open the Command Prompt
  2. To start MSDE, type osql -E and press Enter
     

Practical Learning Practical Learning: Starting MySQL
  1. To launch MySQL, on the taskbar, you can click Start (All) Programs -> MySQL -> MySQL Server 4.1 -> MySQL Command Line Client
  2. Type the password you provided for the root account and press Enter
     
 
From now on, for the rest of these lessons, unless indicated otherwise, we will assume that you can open the command prompt for the SQL interpreter you are using.
 

Database Creation

A database is primarily a file like any other.

To create a database, you use the following formula:

CREATE DATABASE DatabaseName

The CREATE DATABASE expression is required. It lets the SQL interpreter know that you are attempting to create a database file. The DatabaseName factor is also required since every file in a computer must have a name.

Compared to other languages, SQL is highly flexible when it comes to names. Still, there are rules you must follow when naming the objects in your databases:

  • A name can start with either a letter (a, b, c, d, e, f, g, h, i, j, k, l, m, n, o, p, q, r, s, t, u, v, w, x, y, z, A, B, C, D, E, F, G, H, I, J, K, L, M, N, O, P, Q, R, S, T, U, V, W, X, Y, or Z), a digit (0, 1, 2, 3, 4, 5, 6, 7, 8, or 9), an underscore (_) or a non-readable character. Examples are _n, act, %783, Second
  • After the first character (letter, digit, underscore, or symbol), the name can have combinations of underscores, letters, digits, or symbols. Examples are _n24, act_52_t
  • A name cannot include space, that is, empty characters. If you want to use a name that is made of various words, start the name with an opening square bracket and end it with a closing square bracket. Example are [Full Name] or [Date of Birth]

Names

Because of the flexibility of SQL, it can be difficult to maintain names in a database. Based on this, there are conventions we will use for our objects. In fact, we will adopt the rules used in C/C++, C#, Pascal, Java, Visual Basic, J#. In our databases:

  • A name will start with either a letter (a, b, c, d, e, f, g, h, i, j, k, l, m, n, o, p, q, r, s, t, u, v, w, x, y, z, A, B, C, D, E, F, G, H, I, J, K, L, M, N, O, P, Q, R, S, T, U, V, W, X, Y, or Z) or an underscore
  • After the first character, we can use any combination of letters, digits, or underscores
  • A name will not start with two underscores (this rule is not enforced in the other languages
  • A name will not include one or more empty spaces. That is, a name will be made in one word
  • If the name is a combination of words, each word part will start in uppercase. Examples are DateHired, _RealSport, or DriversLicenseNumber

Practical Learning Practical Learning: Creating a Database
  1. At the command prompt, type the following statement:
      
    CREATE DATABASE CarRental1;
  2. If you are using MSDE, press Enter, type GO, and press Enter
    If you are using MySQL, simply press Enter
  3. To create another database, execute the following statement:
      
    CREATE DATABASE Exercises;
 
From now on, for the rest of these lessons, unless indicated otherwise, we will simply ask you to execute a statement. To be conform to SQL, here is how we will proceed:

If you are given a statement such:

  • Execute the following statement:
     
    SELECT * FROM Mamma;

To execute it, first type the statement at the prompt, then:

  • If you are using MySQL, simply press Enter
  • If you are using MSDE, press Enter, type GO, and press Enter again
 
 
Previous Copyright © 2004-2012, FunctionX Next