Database Systems (資料庫系統) Lecture #3

(1)

1

Database Systems

( 資料庫系統

₎

October 4, 2005

Lecture #3

(2)

Course Administration

• Please download the

updated

HW #1

• This lecture:

– R&G Chapter 3

• Next week reading:

(3)

3

Ubicomp Project of the Week:

I/O Brush (Ryokai, MIT Media

Lab)

• Remove the boundary between digital &

physical world

(4)

Relational Model

(5)

5

Lecture Outline

• Relational Model

– Definitions: schema, instance, tuple, field,

domain, etc.

– Basic SQL Commands (Data Definition

Language)

– Integration Constraints

(6)

Relational Model

• Mostly widely used model

– Vendors: Oracle, Microsoft, IBM (DB2), Sybase, …

• Simple

– A relational database is a collection of relations.

– Each relation is a table with rows and columns.

• Why do people like it?

– Simple tabular data representation, easy to understand.

(7)

7

Example of a Relation

• This is a “Students relation”.

• A relation has two parts:

– Schema defines column heads of the table.

– Instance contains the data rows (called tuples or

records) of the table.

sid name

login

age gpa

53666 Jones jones@cs

18

3.4 53688 Smith smith@eecs

18

3.2 53650 Smith smith@math 19

3.8

(8)

Relational Schema (1)

• A relational schema specifies:

– name of the relation: Students

– names of fields: (sid, name, login, age, gpa)

– domain of each field: it is type of possible values (some

of built-in types in SQL are integer, real, string, and date.)

• A field can also be called an

attribute

or a

column

.

sid name

login

age gpa

53666 Jones jones@cs

18

3.4 53688 Smith smith@eecs

18

3.2

(9)

9

Relational Schema (2)

• We can refer to the field by

– Field name (more common): the order of fields does not matter.

– Position of the field (less common): the order of fields matters.

• A relational schema can be written using the

following notation:

– relation-name (1: domain-name-1, field-name-2: domain-name-2, …, field-name-n: domain-name-n)

– Example:

(10)

Relational Instance

• A relation instance contains a set of

tuples

– A relation instance is referred to as a relation. – A tuple can also be called a record or a row.

– A relation instance is a set, and it has no duplicate tuples.

– Order of tuples is not important.

sid name

login

age gpa

53666 Jones jones@cs

18

3.4

(11)

11

Degree and Cardinality

• Degree

is the number of fields in schema (=5

in the table below)

• Cardinality

is the number of tuples in relation

(=3 in the table below)

(12)

Domain Constraints

• Values in the tuples’ fields must satisfy the

specified domain in the schema.

– Similar to type matching in compilation

• Example of domain constraint violation:

– Schema: Students(sid: string, name: string, login: string, age: integer, gpa: real)

– A tuple: <sid: ‘50000’, name: 38, login: ‘dave@cs’, age: 18.5, gpa: 3.3>

(13)

13

Outline on SQL Basics

• History

• Basic commands

(14)

SQL History

• It is a query language for relational databases.

• Developed by IBM (system R) in the 1970s

• Need for a standard since it is used by many

database vendors.

• Two standard organizations

– ANSI (American National Standard Institutes)

– ISO (International Organization for Standardization)

• Standards:

(15)

15

SQL Basic Commands

• create table: create a table

• drop table: delete a table

• alter table: alter a field in a table

• insert: add a tuple

• delete: delete a tuple

(16)

SQL: create table

create table Students

(sid char(20), name char(20),

login char(10), age integer, gpa real)

• Use built-in types: integer, real, char(#)

– Similar to type definition in programming language – You can define your own types (describe in CH5)

(17)

17

SQL: delete table

drop table Students

(18)

SQL: alter table

• Add a new field in a table

alter table Students add dept char[20]

• Delete a field in a table

alter table Students drop gpa

(19)

19

SQL: insert

insert into Students (sid, name, login, age, gpa)

values (‘53688’, ‘Smith’, ‘smith@cs’, 18, 3.2)

or you can omit the fields

insert into Students

values (‘53688’, ‘Smith’, ‘smith@cs’, 18, 3.2)

sid name login age gpa

(20)

SQL: delete

delete from Students as S where S.name = ‘Smith’

or you can omit as and the tuple variable S

delete from Students where name = ‘Smith’

53666 Jones Jones@cs 18 3.3 53688 Smith Smith@cs 18 3.2

(21)

21

SQL: update

update Students S

set S.age = S.age + 1, S.gpa = S.gpa – 1

where S.sid = 53688

53666 Jones Jones@cs 18 3.3 53688 Smith Smith@cs 18 3.2 53650 Smith Smith@ma

th 19 3.7

(22)

Integrity Constraints (IC)

• IC

: condition that must be true for

any

instance of

the database; e.g., domain constraints.

• Why ICs?

– Prevent data entry errors or command errors.

•

_{ICs are specified when schema is defined, (create}

table)

•

ICs are checked when relations are modified (add,

remove, and update).

(23)

23

Types of Integrity

Constraints

• Domain constraint

• Key constraint

• Foreign key constraint (referential

integrity)

(24)

Key Constraint

• A

key

is a set of

minimal fields

that can

uniquely

identify a tuple in a relation.

1. No two distinct tuples can have same values in all key fields, and

2. It is minimal, (no subset of the key is another key).

– _{Part 2 false? A}_superkey_.

– If #key >1, one of the keys is chosen (by DBA) to be the

primary key.

•

Why is this a constraint?

(25)

25

Examples of Keys

50000 Dave Dave@cs 19 3.2 53666 Jones Jones@cs 18 3.3 53688 Smith Smith@cs 18 3.2 53650 Smith Smith@math 19 3.7 53831 Madayan Madayan@mu sic 11 1.8

• Valid keys: {sid}, {name, age} and {login}

• Invalid keys: {name} and {age}

• Valid Superkeys: {sid, gpa}, {sid, age}, and

{sid, name, login, age, gpa)

(26)

Primary and Candidate

Keys

• A relation can have many possible keys, called

candidate keys

. But only one is chosen as the

primary key

.

– DBMS may create an index on primary key to optimize tuple lookup (using primary key).

• Specify keys in SQL:

create table Students

(sid char(20), name char(20), login char(10), age integer, gpa real,

(27)

27

Foreign Key Constraint

• Specify constraint:

– Only the Students listed in the Students relation can enroll for courses.

• Fields [studid] in one relation refer to some fields [sid] in another relation

• Why is it a constraint?

– Delete a tuple from Students?

– A tuple in Enrolled relation becomes invalid. Why?

sid name login age gpa 53666 Jones Jones@cs 18 3.3 53688 Smit h Smith@cs 18 3.2 53650 Smit h Smith@math 19 3.7 cid grad e studid Database1 41 B 53666 Topology1 12 A 53650

Enrolled Relation

Students Relation

They are related

Invalid

studid

(28)

Foreign Key (Definition)

• Studid is called a

foreign key

.

• A foreign key is like a “

pointer

” in C, referencing

a

unique tuple

/ field in the

referenced relation

.

– A foreign key constraint makes sure that there is no dangling pointer.

sid name login age gpa 53666 Jones Jones@cs 18 3.3 53688 Smit Smith@cs 18 3.2

cid grad

e studid Database1 B 5366

(29)

29

More on Foreign Key

• The foreign key must refer to

primary

key

in the

referenced relation

– Why?

– Must be able to uniquely identify the tuple in the referenced relation.

• The foreign key needs not be a candidate key.

– Why?

– Only used to uniquely identify a tuple in the referenced relation.

•

If all foreign key constraints are enforced,

referential

integrity

is achieved.

– _{A data model w/o referential integrity?}

(30)

Specify Foreign Keys in

SQL

• Constraint: only students listed in the Students

relation should be allowed to enroll for courses.

create table Enrolled

(studid char(20), cid char(20), grade char(20), primary key (studid, cid),

foreign key (studid) references Students)

3.3 18 Jones@cs Jones 53666 3.2 18 Smith@cs Smith 53688 gpa age login name sid 53666 B Database141 53650 A Topology112 studid grade cid

(31)

31

Foreign Key Constraint

Violations

• When can they happen? Delete? insert?

update?

sid name login age gpa 53666 Jones Jones@cs 18 3.3 53688 Smit h Smith@cs 18 3.2 53650 Smit h Smith@math 19 3.7 cid grad e studid Database1 41 B 53666 Topology1 12 A 53650

Primary key

Foreign key

sid name login age gpa 53666 Jones Jones@cs 18 3.3 53688 Smit h Smith@cs 18 3.2 53650 Smit h Smith@math 19 3.7 cid grad e studid Database1 41 B 53666 Topology1 12 A 53650 History 123 B 12345

insert

54321

(32)

Self-Referral Foreign Key

• A foreign key can refer to the same relation.

• Example: each student could have a partner.

– If a student hasn’t found a partner, the value can be set to null.

– It is ok to have null value in foreign key field.

• Is it okay to have null value in primary key field?

sid name login age partner gpa 53666 Jones Jones@cs 18 null 3.3

Foreign key

Primary key

(33)

33

General Constraints

• An example : students ages must be over

16 years old.

create table Students ( sid char(20),

name char(20), login char(10), age integer, gpa real,

unique (name, age),

constraint StudentsKey primary key (sid),

check (age > 16)

(34)

More on General

Constraints

• Two types

– Table constraint

: associate with a single

table

– Assertions

: associate with multiple tables

• if 1/3 of courses taken by a student has a grade = F in the Enrolled relation, the status of the student in the Students relation must be set to “In

(35)

35

Enforcing Referential

Integrity

• What should be done if an Enrolled tuple with a non-existent student id is inserted?

– Reject it!

• What should be done if a Students tuple is deleted while leaving a dangling enrolled tuple?

– _{Option 1: Also delete all Enrolled tuples that refer to it} – Option 2: Disallow deletion

– Option 3: Set studid in Enrolled tuples that refer to it to a default

sid.

– Option 4: Set studid in Enrolled tuples that refer to it to NULL.

3.3 18 Jones@cs Jones 53666 3.2 18 Smith@cs Smith 53688 3.7 19 Smith@math Smith 53650 gpa age login name sid 53666 B Database141 53650 A Topology112 studid grade cid Enrolled Relation Students Relation

(36)

Referential Integrity in SQL

• Option 1: CASCADE

(also delete all tuples that refer to deleted tuple)

• Option 2: Default is NO

ACTION (delete/update is rejected)

• Options ¾: SET NULL /

SET DEFAULT (sets foreign key value of

CREATE TABLE Enrolled

(studid CHAR(20)

default “00000”,

cid CHAR(20),

grade CHAR(2),

PRIMARY KEY (sid,cid),

FOREIGN KEY (sid)

REFERENCES Students

ON DELETE CASCADE

ON UPDATE SET

(37)

37

Translate ER Model to

Relational Model

• An entity set to table(s)

• A relationship set without constraints to table(s)

• A relationship set with only key constraints to

table(s)

• A relationship set with participation constraints

to table(s)

• A weak entity set to table(s)

• ISA hierarchies to table(s)

• Aggregates to table(s)

(38)

Entity Sets to Tables

CREATE TABLE Employees (ssn CHAR(11), name CHAR(20), lot INTEGER, PRIMARY KEY (ssn)) Employees ssn name lot

attributes

key attribute

(39)

39

Translate ER Model to

Relational Model

• An entity set to table(s)

• A relationship set without constraints to table(s)

• A relationship set with only key constraints to

table(s)

• A relationship set with participation constraints

to table(s)

• A weak entity set to table(s)

• ISA hierarchies to table(s)

• Aggregates to table(s)

(40)

Relationship Sets (without

Constraints) to Tables

CREATE TABLE Works_In(

ssn CHAR(11),

did INTEGER,

since DATE,

PRIMARY KEY (ssn, did),

FOREIGN KEY (ssn)

REFERENCES Employees,

FOREIGN KEY (did)

REFERENCES Departments

)

dname budget did since name Works_In Departments Employees ssn

descriptive attribute

(41)

41

Relationship Sets to Tables

• Fields (attributes) of a

table must include:

–

All descriptive

attributes.

–

Keys for each

participating entity set

(as

foreign keys

).

CREATE TABLE

Works_In(

ssn

CHAR

(11),

did

INTEGER

,

since

DATE

,

PRIMARY KEY

(ssn, did),

FOREIGN KEY

(ssn)

REFERENCES

Employees,

FOREIGN KEY

(did)

(42)

Translate ER Model to

Relational Model

• An entity set to table(s)

• A relationship set without constraints to table(s)

• A relationship set with only key constraints to

table(s)

• A relationship set with participation constraints

to table(s)

• A weak entity set to table(s)

• ISA hierarchies to table(s)

(43)

43

Review: ER Key Constraints

• Describe

at most once (entitity)

relationship

– Manages relationship: each department has at most

one manager (okay to have none).

– One department can appear at most once in Manages relationship set, also called one-to-many relation.

dname budget did since name ssn

Employees Manages Departments

Joe Alice Mary Peter Finance Accountin g Research Legal 3/3/9 3 2/2/9 2 3/1/9 2

(44)

Relationship Sets (with key

Constraints) to Table

• Map a relationship set to a table:

– _{Note that}_did_{is the}

key now! Why?

– Since each

department has a unique manager, we could instead combine Manages and

Departments.

• Second approach:

– Map Manages into the

Departments table.

CREATE TABLE Manages( ssn CHAR(11),

did INTEGER, since DATE,

PRIMARY KEY (did),

CREATE TABLE Dept_Mgr( did INTEGER,

dname CHAR(20), budget REAL,

ssn CHAR(11), // can be

null -> at most one since DATE,

PRIMARY KEY (did), FOREIGN KEY (ssn) dname budget did since name ssn

(45)

45

Translate ER Model to

Relational Model

• An entity set to table(s)

• A relationship set without constraints to table(s)

• A relationship set with only key constraints to

table(s)

• A relationship set with participation constraints

to table(s)

• A weak entity set to table(s)

• ISA hierarchies to table(s)

• Aggregates to table(s)

(46)

Review: Participation

Constraints

• Describe

all (entitity)

participation

relationship

– Must every department have a manager?

• _{If yes, this is a participation constraint}

– All Departments entities must participate in the Manages

relationship set (total participation).

lot name dname budget did since name dname budget did since Manages Departments Employees ssn

(47)

47

Participation Constraints

to Table

CREATE TABLE Dept_Mgr( did INTEGER;

dname CHAR(20), budget REAL,

ssn CHAR(11) NOT NULL, // must have one!

since DATE,

PRIMARY KEY (did),

FOREIGN KEY (ssn) REFERENCES Employees)

lot name dname budget did since name dname budget did since Manages Departments Employees ssn

(48)

Translate ER Model to

Relational Model

• An entity set to table(s)

• A relationship set without constraints to table(s)

• A relationship set with only key constraints to

table(s)

• A relationship set with participation constraints

to table(s)

• A weak entity set to table(s)

(49)

49

Review: Weak Entities

• A

weak entity

can be identified uniquely only by

considering the key of another

(owner) entity.

– _{Pname = partial key}

– Owner entity set and weak entity set must participate in a

one-to-many relationship set (one owner, many weak entities).

– Weak entity set must have total participation in this identifying

relationship set. name age pname Dependents Employees ssn Policy cost

(Alicia)

(2)

(Hao)

(50)

Weak Entity Sets to Table

• Weak entity set and identifying relationship set

are translated into a single table.

– When the owner entity is deleted, all owned weak entities must also be deleted.

CREATE TABLE Dependent_Policy ( pname CHAR(20),

age INTEGER, cost REAL,

ssn CHAR(11) NOT NULL, PRIMARY KEY (pname, ssn),

name age pname Dependents Employees ssn Policy cost

(51)

51

Translate ER Model to

Relational Model

• An entity set to table(s)

• A relationship set without constraints to table(s)

• A relationship set with only key constraints to

table(s)

• A relationship set with participation constraints

to table(s)

• A weak entity set to table(s)

• ISA hierarchies to table(s)

(52)

Review: ISA Hierarchies

• As in C++, or other

PLs, attributes are

inherited.

• If we declare A

ISA

B,

every A entity is also

considered to be a B

entity.

name ssn Employees lot hourly_wages ISA contractid hours_worked

(53)

53

ISA Hierarchies to Tables

• General approach:

– _{3 tables: Employees, Hourly_Emps and Contract_Emps.}

– Hourly_Emps: Every employee is recorded in Employees. For hourly emps, extra info recorded in Hourly_Emps (hourly_wages, hours_worked, ssn).

– Must delete Hourly_Emps tuple if referenced Employees tuple is deleted).

Contract_Emps name ssn Employees lot hourly_wages ISA Hourly_Emps contractid hours_worked

CREATE TABLE employees ( ssn CHAR(11),

name CHAR(20),

lot INTEGER,

PRIMARY KEY (ssn)) CREATE TABLE hourly_emps ( hourly_wages INTEGER, hours_worked INTEGER, ssn CHAR(11),

PRIMARY KEY (ssn)

FOREIGN KEY (ssn) REFERNECES employees)

(54)

Translate ER Model to

Relational Model

• An entity set to table(s)

• A relationship set without constraints to table(s)

• A relationship set with only key constraints to

table(s)

• A relationship set with participation constraints

to table(s)

• A weak entity set to table(s)

• ISA hierarchies to table(s)

(55)

55

Review: Aggregation

•

Create relationship set

from relationship sets.

•

Aggregation

:

relationship set turns

into an entity set

– So that they can

participate in (other) relationships. budget did pid started_on pbudget dname until Departments Projects Sponsors Employees Monitors name ssn since

(56)

Aggregation to Tables

CREATE TABLE monitors ( ssn CHAR(11),

until DATE, did INTEGER,

pid INTEGER,

PRIMARY KEY (ssn, did, pid)

FOREIGN KEY (ssn) REFERNECES Employees

FOREIGN KEY (did, pid) REFERNECES Sponsors ) started_on _dname until Employees Monitors name ssn since