Transcript ch2

What did we cover in the last class?
 Can’t escape databases at this point in time!
 DBMS versus databases
 Databases versus File Systems
 Transactions, query processing
 ACID properties
 Data Models
Chapter 2: Relational Models
Models - again
 What is a model?
 Relational model
 Other ways:

Hierarchies

Networks

Objects (object oriented, XML)

Entities and their relationships (E-R).
 Entities, relationships, constraints.

E-R Model  Relational Model
Relational Model
 Terminology

Relations

Tuples

Attributes

Domains

Schema

Instances

Keys

Order independence: tuples, attributes
 Data Types: CHAR, VARCHAR, …., DATE, TIME, etc….
 Logical level – [not a physical data structure]
Example Schema
 Product(maker, model, type)
 PC(model, speed, ram, hd, price)
 Laptop(model, speed, ram, hd, screen, price)
 Printer(model, color, type, price)
 Classes(class, type, country, numGuns, bore, displacement)
 Ships(name, class, launched)
 Battles(name, data)
 Outcomes(ship, battle, result)
SQL: Data Definition
 An SQL relation is defined using the create table command:
create table r (A1 D1, A2 D2, ..., An Dn,
(integrity-constraint1),
...,
(integrity-constraintk))
 r is the name of the relation

each Ai is an attribute name in the schema of relation r
 Di is the data type of values in the domain of attribute Ai
 Example:
create table Instructor (
ID
char(5),
name
varchar(20) not null,
dept_name varchar(20),
salary
numeric(8,2))
 insert into instructor values (‘10211’, ’Smith’, ’Biology’, 66000);
 insert into instructor values (‘10211’, null, ’Biology’, 66000);
Integrity Constraints

not null

primary key (A1, ..., An )

foreign key (Am, ..., An ) references r
Example: Declare ID as the primary key for instructor
create table Instructor (
ID
char(5),
name
varchar(20) not null,
dept_name varchar(20),
salary
numeric(8,2),
primary key (ID))
primary key declaration on an attribute automatically ensures not null
Integrity Constraints
create table Department(
dept_name varchar(20),
num_instructors integer,
num_students integer,
primary key (dept_name))
create table Instructor (
ID
char(5),
name
varchar(20) not null,
dept_name varchar(20),
salary
numeric(8,2),
primary key (ID),
foreign key (dept_name) references department)
Relational Model
 Drop Relation

drop R (R: relation name)

drop department
 Alter Relation

alter relation r add A D

A: attribute D: domain
– alter department add address varchar(30)

alter relation r drop A
alter department drop address

Schema, Instances Keys

Order independence: tuples, attributes
 Data Types: CHAR, VARCHAR, …., DATE, TIME, etc….
 Logical level – [not a physical data structure]
Next: Relational Algebra
 Select
 Project
 Union
 Intersection
 Subtraction
Other operators.