MCS 170 – The Nature of Computer Science
Fall 2006
Database Concepts

What is a Database?

Definition: A database is a collection of information/data, often organized within tables, structured in such a way that a user (often computer-based) can quickly retrieve desired pieces of the data or relationships among the data.

Note: A database is not simply a table of information. E.g., an HTML table is not a database – there is no way to extract information other than by viewing the table.

A database must have a Structure

Metadata specifies the kind of information stored in the columns

A database has a structure that is independent of the actual information

Column headings are called fields.

A database table could have structure, but no data - How ??

SQL Example – Structured Query Language

CREATE TABLE ADDR_BOOK (

NAME char(30), // Field 1

COMPANY char(20), // Field 2

E_MAIL char (25) // Field 3

)

This code produces an empty table named “ADDR_BOOK” with the three fields as shown:

+--------------------+---------------------+---------------------+

| NAME | COMPANY | E_MAIL |

+============+============+============+

| | | |

+--------------------+---------------------+---------------------+

| | | |

+--------------------+---------------------+---------------------+

Terminology

Databases store information about entities. An entity is represented by one row of the database table. (~Tuples)

Entities have attributes or properties.

An attribute can have a value.

SQL Example

Entities are addresses (rows)

attributes are NAME, COMPANY, E_MAIL

values = specific instances of attributes

“hvidsten@gac.edu” for E_MAIL.

+--------------------+---------------------+------------------------+

| NAME | COMPANY | E_MAIL |

+============+============+==============+

| | | |

+--------------------+---------------------+------------------------+

| | | |

+--------------------+---------------------+------------------------+

So what do we need to define to create a database?

Name: Describes the kind of things that are collected in the database table.

Field Specifiers: For each column, we have to give it a

Field name: decsribes the attribute.

Data Format: Specifies the type of information.

Optional Comments: Useful information for the maintainer of the database.

The Primary Key

Usually, there is one field that can be used to uniquely identify a row in the table.

We call this field the primary key.

Examples of common primary keys:

SSN

Telephone number.

Scientific name.

Specifying the Whale Database

Whales

Primary key: name

Picture of mature adult

GIF

image

Lower-upper adult length range, meters

Text 20

Length_range

Typical max. adult weight, kilograms

number

weight

Primary food source

Text 12

food

Genus and species

Text 30

Scientific_name

The common English name

Text 15

name

Atomic Field Data

When you query a database, it looks at the value of fields.

Note: it looks at the whole field. Each piece of data is atomic (Not divisible into smaller pieces).

It cannot look at just one sub-part of the field.

Example, if scientific_name is “Orcinus orca”, the query can only find that exact string.

It will not find “orca”.

Database Operations (SQL)

Database SELECT operator creates new databases (tables).

SELECT {list of fields} FROM {list of tables} WHERE {constraints on rows}

Example Database
(Partial Display)

SELECT Example

The Select operator will extract certain fields from rows of a database that match certain constraints.

SELECT Name, Dom, Capital, Lat, NS, Lon, EW, Word FROM Nations WHERE EW = ‘W’

Database Query Result

SELECT Name, Capital, Lat, Lon, FROM Nations WHERE EW = ‘W’

Name Capital Lat Lon

------------------------------------------------------

Ireland Dublin 52 7

Jamaica Kingston 17 77

In Class Project

Define the field names, data formats, and optional comments needed to create a table called “Course” that would represent a college course.

Include fields such as department, number, course code, credits, gen ed, and time.

What will be the primary key for your table?

The Union and Difference Operators

Union combines two databases

Table1 + Table2

Difference returns the rows in the 1st database that are not in the 2nd

Table1 – Table2

The Product Operator

Product creates a super table from two tables.

Each row in the first table is modified by appending a row from the second table.

If the 2nd table has three rows, the first row in the 1st table would be appended to three times (creating 3 rows).

Thus if the first table has 8 rows and the 2nd table has 3 rows, the super table would have 24 rows.

Table1 X Table2

PowerPoint Presentation

San Skulrattanakulchai

1600-12-31T19:00:00