The Relational Model and DDL
CS4380/7380 DBMS-I, DDL-1, C. R. Shyu
1
Why Study the Relational Model?
Most widely used model.
Vendors: IBM, Oracle, MySQL, Microsoft, Sybase,
Informix, PostgreSQL, etc.
Recent competitor: object-oriented model
ObjectStore,

Indexing -3
Spatial Data Using R-tree
CS4380/7380 DBMS-I, Indexing-III, C. R. Shyu
1
Introduction
For text-based and numeric data, one can use
either ISAM (static structure) or B+ tree (dynamic
structure).
How about objects on the map?
MySQL uses spatia

Agenda
Final project Proposal
Deadline 3/10/2014 23:59:59
Schedule a 30-minute group meeting with
the instructor between 3/11/2014 and
3/18/2014 (17:00-18:30 weekdays, 13:0017:00 Saturday)
Midterm Exam
Thursday, March 20, 2014
Evening review session s

Agenda
Exam 1 review session 6:00-8:00pm on Wednesday
Friday 10:00-11:00am Professor For A Day Lecture
Schedule your group meeting this and next week
Thursday 3/13: 3:00-5:00pm, 6:00-7:00pm
Friday 3/14: 3:30-6:00pm
Monday 3/17: 4:00-5:00pm
Tuesday 3/1

Schema Refinement and
Normal Forms - 2
CS4380/7380 DBMS-I
1
Levels of Normalization
1NF
2NF
3NF
BCNF
4NF
5NF
CS4380/7380 DBMS-I
An Intro. to DBS by C. J. Date
2
History of NF Development
1NF, 2NF, 3NF (Codd, 1972)
BCNF (Boyce and Codd, 1974)
4NF (Fagin,

In-Class Practice
1NF, 2NF
A relation is in 1NF if and only if every tuple in
this relation contains exact one value for each
attribute.
A relation is in 2NF if and only if this relation is in
1NF and every nonkey attribute is irreducibly
dependent on the

SQL: Queries, Programming,
Triggers, PL/SQL
You will be introduced to some important concepts of
SQL to write complex queries.
CS4380/7380 DBMS-I, SQL-1 C. R. Shyu
1
Again, we will use these three
relations.
sname
sid
rating
Boats
day
bname
age
did
reserv

About your Project 2
Due midnight 3/05/2014
CS4380/7380 DBMS-I, Indexing-I, C. R. Shyu
1
Indexing-I
Why the retrieval process is so slow?
Could you improve it?
What to index?
Whats going on at the system level (behind the scene)?
CS4380/7380 DBMS-I, Index

Triggers (Active database)
Trigger: A procedure that starts automatically if
specified changes occur to the DBMS
Analog to a "daemon" that monitors a database for
certain events to occur
Three parts:
Event (activates the trigger)
Condition (tests whether

Aggregate Operators
Significant extension of
relational algebra.
SELECT COUNT(*)
FROM Sailors S
COUNT (*)
COUNT ( [DISTINCT] A)
SUM ( [DISTINCT] A)
AVG ( [DISTINCT] A)
MAX (A)
MIN (A)
single column
SELECT AVG(S.age)
FROM Sailors S
WHERE S.rating=10
SELECT

Relational Algebra - 1
Basics and Concepts
CS4380/7380 DBMS-I, Relational Algebra - 1, C. R. Shyu
2
Algebra? Calculus? Why?
A query example:
SELECT S.name, E.cid
FROM Students S, Enrolled E
WHERE S.sid=E.sid AND E.grade=A;
What about a query looking for s

The Entity-Relationship Model
Mapping your clients enterprise
policy into database design.
CS4380/7380 DBMS-I, ERD, C. R. Shyu
1
Objective of this lecture
You will learn the definition of each element in ER
diagrams. Also you will be able to model an info

Review of Relational Operators
Selection ( ) Selects a subset of rows from relation.
Projection ( ) Select specific columns from relation.
Intersection ( ) Tuples occurs both in reln. 1 and in reln. 2.
Cross-product ( ) Allows us to combine two relations.

Logical DB Design: ER to Relational
Entity sets to tables.
ssn
name
lot
Employees
CS4380/7380 DBMS-I, DDL-2, C. R. Shyu
CREATE TABLE Employees
(ssn CHAR(11),
name CHAR(20),
lot INTEGER,
PRIMARY KEY (ssn)
1
Exercise 1:
Schema: Students(sid, sname, major,

Review of Relational Operators
Selection ( ) Selects a subset of rows from relation.
Projection ( ) Select specific columns from relation.
Intersection ( ) Tuples occurs both in reln. 1 and in reln. 2.
Cross-product ( ) Allows us to combine two relations.

Welcome to CS 4380/7380
Database Management Systems I
Spring 2013
Lecture 1
Chi-Ren Shyu, Ph.D.
Paul K and Dianne Shumaker Endowed Professor of Computer Science
Director of MU Informatics Institute
ShyuC@missouri.edu
CS4380/7380 DBMS-I, Lecture 1 Introduc