Relational databases present information as a collection of tables. Unless empty, each table contains related data.
Sample Tables
This diagram shows the EMPLOYEE, SKILL, DEPARTMENT, and PROJECT tables from the database for Commonwealth Auto:
EMPLOYEE DEPARTMENT ┌────────┬───────────┬───────────┬─────────┐ ┌─────────┬─────────────────────┐ │EMP_ID │EMP_LNAME │EMP_FNAME │DEPT_ID │ │DEPT_ID │DEPT_NAME │ ├────────┼───────────┼───────────┼─────────┤ ├─────────┼─────────────────────┤ │2096 │CARLSON │THOMAS │4600 │ │5200 │CORPORATE MARKETING │ │ │ │ │ │ │ │ │ │2437 │THOMPSON │HENRY │4600 │ │4600 │MAINTENANCE │ │ │ │ │ │ │ │ │ │2598 │JACOBS │MARY │5100 │ │5100 │BILLING │ └────────┴───────────┴───────────┴─────────┘ └─────────┴─────────────────────┘ SKILL PROJECT ┌──────────┬────────────────┐ ┌─────────┬───────────────────┐ │SKILL_ID │SKILL_NAME │ │PROJ_ID │PROJ_DESC │ ├──────────┼────────────────┤ ├─────────┼───────────────────┤ │4250 │DATA ENTRY │ │C200 │NEW BRAND RESEARCH │ │ │ │ │ │ │ │4370 │FILING │ │C240 │SERVICE STUDY │ │ │ │ │ │ │ │4490 │GENERAL LEDGER │ │D880 │SYSTEM ANALYSIS │ └──────────┴────────────────┘ └─────────┴───────────────────┘
The EMPLOYEE table contains data about employees. The SKILL table contains information about skills that are used in Commonwealth Auto. The DEPARTMENT table contains information about the departments in the company. The PROJECT table contains information about projects.
A table is made up of columns and rows. A portion of the EMPLOYEE table in the Commonwealth Auto database looks like this:
Columns ┌──────────┬────────────┐ │ │ │ ┌───▼────┬─────▼─────┬──────▼────┐ │EMP_ID │EMP_LNAME │EMP_FNAME │ ├────────┼───────────┼───────────┤ ┌──►2096 │CARLSON │THOMAS │ │ │ │ │ │ Rows ├──►2437 │THOMPSON │HENRY │ │ │ │ │ │ └──►2598 │JACOBS │MARY │ └────────┴───────────┴───────────┘
Columns
A table has one or more columns. Each column:
For example, the employee ID (EMP_ID) column contains employee IDs, each of which is a number. The employee IDs are listed one below the other. At the top of the column is a heading based on the kind of data in the column.
Rows
A table has zero or more rows. Each row:
The first part of one row from the EMPLOYEE table looks like this:
┌────────┬───────────┬───────────┐ │2096 │CARLSON │THOMAS │ └────────┴───────────┴───────────┘
Primary Keys
A business often needs to prevent duplicate rows of data from being stored in the same table. For example, each employee in the company needs an employee ID different from all other IDs. This is a way of distinguishing two employees who have the same name. You do not want to store two employees who have the same employee ID.
To ensure that duplicate rows are not stored, a column or combination of columns is identified as a primary key of the table when the table is defined. Each entry in the primary key column or columns must be unique; there can be no duplicates. As a result, the primary key uniquely identifies each row in the table.
A row of employee information in the EMPLOYEE table is uniquely identified by the employee ID. There is only one row with employee ID 2096 and only one row with employee ID 2437. However, there can be more than one employee with a first name of Mary. The column containing the first name is not a unique key:
┌────────┬───────────┬───────────┐ │EMP_ID │EMP_LNAME │EMP_FNAME │ ├────────┼───────────┼───────────┤ │2096 │CARLSON │THOMAS │ │ │ │ │ │2437 │THOMPSON │HENRY │ │ │ │ │ │2598 │JACOBS │MARY │ └────────┴───────────┴───────────┘ ▲ │ │ Primary key
When you request data from a table and specify a value for the primary key, you see only one row returned.
|
Copyright © 2014 CA.
All rights reserved.
|
|