Transcript Ch3_RM - Fordham University Computer and Information

Database Management Systems
Chapter 3
The Relational Model
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke
1
Why Study the Relational Model?

Most widely used model.

Vendors: IBM, Informix, Microsoft, Oracle,
Sybase, etc.
Simple data representation and it can express
complex queries.
 Recent competitor: object-oriented model



ObjectStore, Versant, Ontos
A synthesis emerging: object-relational model
• Informix Universal Server, UniSQL, O2, Oracle, DB2
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke
2
Relational Database: Definitions


Relational database: a set of relations with distinct names.
Relation: made up of 2 parts:


Instance : a table, with rows and columns.
#Rows = cardinality, #fields = degree.
Schema : specifies name of relation, plus name and
domain/type of each column.
• E.G. Students(sid: string, name: string, login: string,
age: integer, gpa: real).

Can think of a relation as a set of rows or tuples (i.e.,
all rows are distinct).
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke
3
Example Instance of Students Relation
sid
53666
53688
53650
name
login
Jones jones@cs
Smith smith@eecs
Smith smith@math
age
18
18
19
gpa
3.4
3.2
3.8
 Cardinality = 3, degree = 5, all rows distinct
The order of fields does not matter if the fields are
named.
 A relation is a set of rows, so the order of rows is not
important.

Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke
4
Relational Query Languages
A major strength of the relational model:
supports simple, powerful querying of data.
 Queries can be written intuitively, and the
DBMS is responsible for efficient evaluation.



The key: precise semantics for relational queries.
Allows the optimizer to extensively re-order
operations, and still ensure that the answer does
not change.
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke
5
The SQL Query Language
Developed by IBM (system R) in the 1970s
 Need for a standard since it is used by many
vendors
 Standards:





SQL-86
SQL-89 (minor revision)
SQL-92 (major revision)
SQL-99 (major extensions, current standard)
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke
6
Creating Relations in SQL

Creates the Students relation. Observe that
the type (domain) of each field is specified,
and enforced by the DBMS whenever tuples
are added or modified.
CREATE TABLE Students
(sid CHAR(20),
name CHAR(30),
login CHAR(20),
age INTEGER,
gpa REAL);
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke
string type with
different length
7
Creating Relations in SQL

As another example, the Enrolled table holds
information about courses that students take.
CREATE TABLE Enrolled
(sid CHAR(20),
cid CHAR(20),
grade CHAR(10));
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke
8
Adding Tuples

We can insert a single tuple into the Students table as
follows:
INSERT INTO Students (sid, name, login, age, gpa)
VALUES (53666, ‘Jones’, ‘jones@cs’, 18, 3.4);
sid
53666
53688
53650
name
login
Jones jones@cs
Smith smith@eecs
Smith smith@math
age
18
18
19
gpa
3.4
3.2
3.8
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke
9
Deleting Tuples

We can delete all tuples satisfying some condition
(e.g., name = Smith):
DELETE S
FROM Students S
WHERE S.name = ‘Smith’;
sid
name
login
53666 Jones jones@cs
53688 Smith smith@eecs
53650 Smith smith@math
age
18
18
19
gpa
3.4
3.2
3.8
 Powerful variants of these commands are available; more later!
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke
10
Updating Tuples

We can modify the column values in an existing
row:
Old value
UPDATE Students S
SET S.age = S.age +1, S.gpa = S.gpa -1
WHERE S.sid = 53688;
19
sid
name
login
53666 Jones jones@cs
53688 Smith smith@eecs
53650 Smith smith@math
age
18
18
19
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke
gpa
3.4
3.2
3.8
2.2
11
Updating Tuples

Where clause is applied first and determines which
rows to be modified.
UPDATE Students S
SET S.gpa = S.gpa - 0.1
WHERE S.gpa >= 3.3;
sid
name
login
53666 Jones jones@cs
53688 Smith smith@eecs
53650 Smith smith@math
age
18
18
19
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke
gpa
3.4
3.2
3.8
3.3
3.7
12
Integrity Constraints (ICs)

An IC is a condition specified on a database schema
and restricts the data that can be stored in an instance
of the database, such as domain constraints.



A legal instance of a relation is one that satisfies all
specified ICs.


ICs are specified when schema is defined.
ICs are checked when relations are modified.
DBMS should not allow illegal instances.
If the DBMS checks ICs, stored data is more faithful to
real-world meaning.

Avoids data entry errors, too!
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke
13
Primary Key Constraints

A set of fields is a candidate key for a relation if :
1. No two distinct tuples can have same values in all key fields,
and
2. No subset of the set of fields in a key is a unique identifier
for a tuple.
 Part 2 false? A superkey (a set of fields containing a key).
 If there’s >1 key for a relation, one of the keys is chosen (by
DBA) to be the primary key.


E.g., sid is a key for Students. (What about name?)
The set {sid, gpa} is a superkey.
Every relation is guaranteed to have a key.
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke
14
Primary and Candidate Keys in SQL


UNIQUE: Candidate keys (you could have many)
PRIMARY KEY: one of candidate keys is chosen as
the primary key.
CREATE TABLE Students
(sid CHAR(20),
name CHAR(30),
login CHAR(20),
age INTEGER,
gpa REAL,
UNIQUE (name, age),
PRIMARY KEY (sid));
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke
15
Name a constraint

We could name a constraint in a table. If the
constraint is violated, the constraint name is
returned and can be used to identify the
error.
 CONSTRAINT constraint-name
CREATE TABLE Students
(sid CHAR(20),
name CHAR(30),
login CHAR(20),
age INTEGER,
gpa REAL,
UNIQUE (name, age),
CONSTRAINT StudentsKey PRIMARY KEY (sid));
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke
16
Primary and Candidate Keys in SQL


“For a given student and course,
CREATE TABLE Enrolled
there is a single grade.” vs.
(sid CHAR(20)
“Students can take only one
cid CHAR(20),
course, and receive a single grade
grade CHAR(2),
for that course; further, no two
PRIMARY KEY (sid, cid) );
students in a course receive the
same grade.”
CREATE TABLE Enrolled
Used carelessly, an IC can prevent (sid CHAR(20)
the storage of database instances
cid CHAR(20),
that arise in practice!
grade CHAR(2),
PRIMARY KEY (sid),
UNIQUE (cid, grade) );
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke
17
Foreign Keys, Referential Integrity
Foreign key : Set of fields in one relation that is used
to `refer’ to a tuple in another relation. (Must
correspond to primary key of the second relation.)
Like a `logical pointer’.
 E.g. sid is a foreign key referring to Students:



Enrolled(sid: string, cid: string, grade: string)
If all foreign key constraints are enforced, referential
integrity is achieved, i.e., no dangling references.
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke
18
Foreign Keys in SQL

Only students listed in the Students relation should
be allowed to enroll for courses.
CREATE TABLE Enrolled
(sid CHAR(20), cid CHAR(20), grade CHAR(2),
PRIMARY KEY (sid, cid),
FOREIGN KEY (sid) REFERENCES Students(sid) );
Enrolled
sid
53666
53666
53650
53666
cid
grade
Carnatic101
C
Reggae203
B
Topology112
A
History105
B
Students
sid
53666
53688
53650
name
login
Jones jones@cs
Smith smith@eecs
Smith smith@math
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke
age
18
18
19
gpa
3.4
3.2
3.8
19
Enforcing Integrity Constraints


ICs are specified when a relation is created and enforced
when a relation is modified.
Violating the primary key constraint, the transaction
will be rejected.
 Inserting a tuple with an existing sid.
INSERT INTO Students (sid, name, login, age, gpa)
VALUES (53688, ‘Mike’, ‘mike@ee’, 17, 3.4);
 Inserting a tuple with primary key as null.
INSERT INTO Students (sid, name, login, age, gpa)
VALUES (null, ‘Mike’, ‘mike@ee’, 17, 3.4);
 Updating a tuple with an existing sid.
UPDATE Students S SET S.sid = 50000 WHERE S.sid=53688;
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke
20
Enforcing Integrity Constraints

Deletion of Enrolled tuples do not violate
referential integrity, but insertions could.
 Inserting a tuple with an un-exist sid in Students.
INSERT INTO Enrolled (sid, cid, grade) VALUES (51111, ‘Hindi101’, ‘B’);

Insertion of Students tuples do not violate
referential integrity, but deletions could.
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke
21
Ways to handle foreign key violations


If an Enrolled row with un-existing sid is inserted, it
is rejected.
If a Students row is deleted/updated,
 Option 1: Delete/Update all Enrolled rows that refer to the
deleted sid in Students (CASCADE). Both are affected
 Option 2: Reject the deletion/updating of the Students row
if an Enrolled row refers to it (NO ACTION ). [The default
action for SQL]. None is affected.
 Option 3: Set the sid of Enrolled to some existing (default)
sid value in Students for every involved Enrolled row (SET
NULL / SET DEFAULT ). Both are affected.
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke
22
Referential Integrity in SQL
When a Students row
is deleted, all Enrolled
rows that refer to it are
to be deleted as well.
 When a Students sid is
modified, the update is
to be rejected if an
Enrolled row refers to
the modified Students
row.

CREATE TABLE Enrolled
(sid CHAR(20),
cid CHAR(20),
grade CHAR(2),
PRIMARY KEY (sid,cid),
FOREIGN KEY (sid)
REFERENCES Students (sid)
ON DELETE CASCADE
ON UPDATE NO ACTION );
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke
23
The SQL Query Language


A relational database query is a question about the data, and the
answer consists of a new relation containing the result.
To find information about all 18 years old students :
sid
name
login
age gpa
SELECT *
FROM Students S
WHERE S.age=18;

53666 Jones
jones@cs
18
3.4
53688 Smith smith@ee 18
3.2
To find just names and logins:
SELECT S.name, S.login
FROM Students S
WHERE S.age=18;
name
login
Jones
jones@cs
Smith smith@ee
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke
24
Querying Multiple Relations

What does the
following query
compute?
SELECT S.name, E.cid
FROM Students S, Enrolled E
WHERE S.sid=E.sid AND E.grade=‘A’;
Given the following instances
of Enrolled and Students:
sid
53666
53688
53650
name
login
age gpa
Jones jones@cs
18 3.4
Smith smith@eecs 18 3.2
Smith smith@math 19 3.8
sid
53831
53831
53650
53666
cid
grade
Carnatic101
C
Reggae203
B
Topology112
A
History105
B
we get:
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke
name
Smith
cid
Topology112
25
Example
SELECT S.fname As Student_fname,
S.lname As Student_lname,
E.call_num As course_call_num
FROM Students S, Enrolled E
WHERE S.sid = E.sid AND S.points > 100;
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke
26
Logical DB Design: ER to Relational

Entity sets to tables:
ssn
name
Employees
lot
CREATE TABLE Employees
(ssn CHAR(11),
name CHAR(20),
lot INTEGER,
PRIMARY KEY (ssn));
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke
27
Relationship Sets to Tables

In translating a relationship set to a relation, attributes
of the relation must include:
 Keys for each participating entity set (as foreign
keys).
• This set of attributes forms a superkey for the
relation.
• The primary key is decided by the key constraint of the
relationship.

All descriptive attributes.
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke
28
Binary Relationship
since
name
ssn
dname
lot
Works_In
Employees

did
Each dept has at least
one employee, and each
employee works for at
least one department,
according to the key
constraint on Works_In.
budget
Departments
Translation to
relational model?
Many-to-Many
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke
29
since
name
ssn
dname
lot
Employees
budget
did
Works_In
Departments
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);
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke
30
Binary Relationship
since
name
ssn

Each dept has at
most one manager,
according to the
key constraint on
Manages.
dname
lot
Employees
did
Manages
budget
Departments
Translation to
relational model?
1-to Many
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke
31
Translating ER Diagrams with
Key Constraints

Map relationship to a
table:


Since each
department has
most one manager,
there are no two
tuples with the
same did but differ
on the ssn value,
did is the key now!
Separate tables for
Employees and
Departments.
since
name
ssn
dname
lot
Employees
did
Manages
budget
Departments
CREATE TABLE Manages(
ssn CHAR(11),
did INTEGER,
since DATE,
PRIMARY KEY (did),
FOREIGN KEY (ssn) REFERENCES Employees,
FOREIGN KEY (did) REFERENCES Departments);
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke
32
Translating ER Diagrams with Key Constraints

Since each
department has a
unique manager,
we could instead
combine Manages
and Departments.
 ssn can take null
values since
several
departments have
no managers.
since
name
ssn
dname
lot
Employees
did
Manages
budget
Departments
CREATE TABLE Dept_Mgr(
did INTEGER,
dname CHAR(20),
budget REAL,
ssn CHAR(11),
since DATE,
PRIMARY KEY (did),
FOREIGN KEY (ssn) REFERENCES Employees);
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke
33
Binary Relationship to RM
Many-to-many: The primary key of R
includes all the attributes in the primary keys
of A and B.
 One-to-many: The primary key of R is the
same as B (i.e., the entity set on the “many”
side).
 One-to-one: R has two candidate keys. The
first (second) one is the same as A (B). One is
primary key and the other is unique.

Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke
34
Multiple-way Relationship
Multi-way relationship set R: Create a table
that includes the candidate keys of the
participating entity sets the attributes of R (if
any).
 The primary key of the table includes all the
attributes of the primary keys of the
participating entity sets.

Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke
35
since
name
ssn
dname
lot
Employees
address
budget
did
Works_In2
Departments
Locations
capacity
CREATE TABLE Works_In2(
ssn CHAR(11),
did INTEGER,
address CHAR(20),
since DATE,
PRIMARY KEY (ssn, did, address),
FOREIGN KEY (ssn) REFERENCES Employees,
FOREIGN KEY (did) REFERENCES Departments,
FOREIGN KEY (address) REFERENCES Locations);
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke
36
name
ssn
lot
Employees
supervisor
subordinate
Reports_To
CREATE TABLE Reports_To(
supervisor_ssn CHAR(11),
subordinate_ssn CHAR(11),
PRIMARY KEY (supervisor_ssn, subordinate_ssn),
FOREIGN KEY (supervisor_ssn) REFERENCES Employees (ssn),
FOREIGN KEY (subordinate_ssn) REFERENCES Employees(ssn));
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke
37
Review: Participation Constraints

Does every department have a manager?

If so, this is a participation constraint: the participation of
Departments in Manages is said to be total (vs. partial).
• Every did value in Departments table must appear in a
row of the Manages table (with a non-null ssn value!)
since
name
ssn
dname
did
lot
Employees
Manages
budget
Departments
Works_In
since
Database Management Systems 3ed, R. Ramakrishnan and
J. Gehrke
38
Participation Constraints in SQL

This approach is good for one-to-many relationships, when
entity set with key constraint also has a total participation
constraint. (Two tables are combined)
CREATE TABLE Dept_Mgr(
did INTEGER,
dname CHAR(20),
budget REAL,
An Employees tuple cannot
ssn CHAR(11) NOT NULL,
be deleted while it is pointed
to by a Dept_Mgr tuple
since DATE,
PRIMARY KEY (did),
FOREIGN KEY (ssn) REFERENCES Employees,
ON DELETE NO ACTION);
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke
39
Review: Weak Entities

A weak entity can be identified uniquely only by
considering the primary key of another (owner) entity.


Owner entity set and weak entity set must participate in a
one-to-many relationship set (1 owner, many weak entities).
Weak entity set must have total participation in this
identifying relationship set.
name
ssn
lot
Employees
cost
Policy
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke
pname
age
Dependents
40
Translating Weak Entity Sets

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 Dep_Policy (
pname CHAR(20),
age INTEGER,
cost REAL,
ssn CHAR(11) NOT NULL,
PRIMARY KEY (pname, ssn),
FOREIGN KEY (ssn) REFERENCES Employees,
ON DELETE CASCADE);
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke
41
Review: ISA Hierarchies
name
ssn
lot
Employees
As in C++, or other PLs,
hourly_wages
attributes are inherited.
 If we declare A ISA B, every A
entity is also considered to be a B
entity.



hours_worked
ISA
contractid
Hourly_Emps
Contract_Emps
Overlap constraints: Can Joe be an Hourly_Emps as well as
a Contract_Emps entity? (Allowed/disallowed)
Covering constraints: Does every Employees entity also have
to be an Hourly_Emps or a Contract_Emps entity? (Yes/no)
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke
42
Translating ISA Hierarchies to Relations

General approach:

3 relations: 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).
• Queries involving all employees easy, those
involving just Hourly_Emps require a join to
get some attributes.
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke
43
name
ssn
lot
Employees
hourly_wages
hours_worked
ISA
contractid
Hourly_Emps
Contract_Emps
CREAT TABLE Employees
(ssn CHAR(10) NOT NULL DEFAULT ‘999999999’,
name CHAR(20),
lot INTEGER,
CONSTRAINT EmployeesKey PRIMARY KEY (ssn));
CREATE TABLE Hourly_Emps
(ssn CHAR(10) NOT NULL DEFAULT ‘999999999’,
hourly_wages REAL,
hours_worked INTEGER,
CONSTRAINT HourlyEmplsKey PRIMARY KEY (ssn),
FOREIGN KEY (ssn) REFERENCES Employees ON DELETE CASCADE);
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke
44
Translating ISA Hierarchies to Relations

Alternative: Just Hourly_Emps and
Contract_Emps.




Hourly_Emps: ssn, name, lot, hourly_wages,
hours_worked.
Contract_Emps: ssn, name, lot, contractId.
Each employee must be in one of these two
subclasses.
If an employee is both an Hourly_Emps and a
Contract_Emps entity, then the same name and
lot values are stored in two tables.
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke
45
Translating ER Diagram with
Aggregation
name
ssn
lot
Employees
Monitors
since
started_on
pid
pbudget
Projects
until
dname
did
Sponsors
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke
budget
Departments
46
CREATE TABLE Employees
(ssn CHAR(11), name CHAR(20), lot INTEGER,
PRIMARY KEY (ssn));
CREATE TABLE Departments
(did INTEGER, dname CHAR(20), budget REAL,
PRIMARY KEY (did));
CREATE TABLE Projects
(pid INTEGER, started_on DATE, pbudget REAL,
PRIMARY KEY (pid));
CREATE TABLE Sponsors
(did INTEGER, pid INTEGER, since DATE,
PRIMARY KEY (did, pid),
FOREIGN KEY (did) REFERENCES Departments,
FOREIGN KEY (pid) REFERENCES Projects);
CREATE TABLE Monitors
(ssn CHAR(11), did INTEGER, pid INTEGER, until DATE,
PRIMARY KEY (ssn, did, pid),
FOREIGN KEY (ssn) REFERENCES Employees,
FOREIGN KEY (did) REFERENCES Departments,
FOREIGN KEY (pid) REFERENCES Projects);
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke
47
Review: Binary vs. Ternary
Relationships
name
ssn
Employees

What are the
additional
constraints in
the 2nd
diagram?
pname
lot
Dependents
Covers
Bad design
Policies
policyid
cost
name
ssn
age
pname
lot
age
Dependents
Employees
Purchaser
Better design
policyid
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke
Beneficiary
Policies
cost
48
Binary vs. Ternary Relationships (Contd.)


CREATE TABLE Policies (
The key
policyid INTEGER,
constraints allow
cost REAL,
us to combine
ssn CHAR(11) NOT NULL,
Purchaser with
PRIMARY KEY (policyid).
Policies and
FOREIGN KEY (ssn) REFERENCES Employees,
Beneficiary with
ON DELETE CASCADE);
Dependents.
Participation
CREATE TABLE Dependents (
constraints lead to pname CHAR(20),
NOT NULL

constraints.
What if Policies is
a weak entity set?
age INTEGER,
policyid INTEGER,
PRIMARY KEY (pname, policyid).
FOREIGN KEY (policyid) REFERENCES Policies,
ON DELETE CASCADE);
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke
49
Policies is a weak entity set
CREATE TABLE Dependents (
pname CHAR(20),
ssn CHAR(11),
age INTEGER,
policyid INTEGER NOT NULL,
PRIMARY KEY (pname, policyid, ssn),
FOREIGN KEY (policyid, ssn) REFERENCES Policies,
ON DELETE CASCADE);
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke
50
Views

A view is just a relation, but we store a definition,
rather than a set of tuples.
CREATE VIEW YoungActiveStudents (name, grade)
AS SELECT S.name, E.grade
FROM Students S, Enrolled E
WHERE S.sid = E.sid and S.age<21;

Views can be dropped using the DROP VIEW
command.
 How to handle DROP TABLE if there’s a view on the
table?
•
View becomes invalid if its base table is dropped.
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke
51
Views and Security

Views can be used to present necessary
information (or a summary), while hiding
details in underlying relation(s).

Given YoungStudents, but not Students or
Enrolled, we can find students s who have are
enrolled, but not the cid’s of the courses they are
enrolled in.
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke
52
Destroying and Altering Relations

Destroys the relation Students. The schema
information and the tuples are deleted.
DROP TABLE Students;

The schema of Students is altered by adding a
new field; every tuple in the current instance
is extended with a null value in the new field.
ALTER TABLE Students
ADD COLUMN firstYear: integer;
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke
53
Relational Model: Summary



A tabular representation of data.
Simple and intuitive, currently the most widely used.
Integrity constraints can be specified by the DBA,
based on application semantics. DBMS checks for
violations.




Two important ICs: primary and foreign keys
In addition, we always have domain constraints.
Powerful and natural query languages exist.
Rules to translate ER to relational model
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke
54