CSCE 315 Lecture 5

« previous | Friday, January 27, 2012 | next »

Relational Databases

Relations are stored in tables.

sponsor
Senator	Bill
...	...

Schemas describe the relationship, and the entites that they relate.

From the Entity-Relationship Model, we describe both Entity Sets and Relationships with relations

Example

Senator

• Name
• Years
• Party

Bill

• Name
• Text

Supported

• Senator
• Bill

Relational Database

• Senator(Name, Party, State, Years)
• Bill(Name, Text)
• Supported(Senator, Bill)

Some relationships (many-to-one, one-to-one) can be combined with entity tables with something similar to foreign keys.

isa relationship:

1. Store data on two tables
2. use key in subtable that adds fields to superclass
3. allow NULL for fields that are optional

Structured Query Language (SQL)

A well-known, commonly used standard for relational databases. There are many extensions and variations (even platform-specific).

SQL is a 4th Generation Programming Language

Three characteristics of SQL

• Data Definition Language: creation of schema
• Data Manipulation Language: adding/editing information
• Querying Language: getting information from databases

Database Schema

The set of relations (tables) in the database.

Create, Delete, Change tables

CREATE TABLE table_name (
    element_list
);

Data Types

• INT, INTEGER
• FLOAT, REAL
• CHAR(n)
• VARCHAR(n)
• DATE, DATETIME

Keys

Keys Listed under PRIMARY KEY.

Fields can also be UNIQUE and have DEFAULT values

Example

CREATE TABLE HouseRep (
    Name VARCHAR(80) UNIQUE,
    Party CHAR(10),
    Birthdate DATE NOT NULL,
    YearsInCongress INT DEFAULT 0,
    Salary REAL DEFAULT 120000.00,
    PRIMARY KEY (Name, Birthdate)
);