CSC 5559
Database Programming
Instructor: Bert Hinrichs
Phone: 303-344-6458, 303-550-4077
Email: [email protected]
Textbook:
There is no required textbook for this class. Materials will be taken from a variety of sources,
including several textbooks
Assignment for Module 4
The graded problems in Module 4 involve SELECT statements for single table problems
with conditions, joins with two tables, and row summaries involving single tables. You should
execute the statements using either Oracle or MySQL.
Temporal Data
Temporal Data Concepts
User Defined Time
Instant
Interval
Period
Valid Time
Transaction Time
Instants
An anchored location or point on a timeline
A datetime is an Instant in SQL
Generally 3 specific forms
Date, Time, Timestamp
Differ by the
Querying Temporal Data
Querying State
Extracting Current State
Extracting Sequenced State
Extracting Nonsequenced State
Modifying Temporal State
Updating and Deleting
Current
Sequenced
Nonsequenced
Use the PDF Book and the CDROM
We are going to be going t
drop table if exists S;
create table S
(
SNO char(2) not null,
SNAME varchar(10) not null,
STATUS integer not null,
CITY varchar(10)
);
insert
insert
insert
insert
insert
into
into
into
into
into
S
S
S
S
S
values('S1',
values('S2',
values('S3',
values('S4
Midterm Review Homework
CSC 5559
Assume you have the following table definitions:
sales
(
date
bc
cc
sn
pc
qty
datetime,
char(2),
char(2),
numeric(2),
char(2),
numeric(3)
# Date of sale
# Branch Code
# Customer Code
# Salesperson Number
# Product Code
# Q
Using Logic to Create SQL
What Are We Trying To Do
Create Logical Expressions (or Relational
Calculus Expressions) to define what Queries
or Constraints we want to express
Using the fact that Relational Databases are Set
Oriented
Transform them into SQL E
Logic
Set Theory
Quantification
Unified
Logic is Logic:
Boolean Truth Values
Set Theory is Set Theory:
Manipulation of Sets
Quantification is a way to tie Logic and Set Theory together.
Special Case: The Empty Set
The Empty Set contains no elements.
Beca
Logic
Set Theory
Quantification
Unified
Logic is Logic:
Boolean Truth Values
Set Theory is Set Theory:
Manipulation of Sets
Quantification is a way to tie Logic and Set Theory together.
Special Case: The Empty Set
The Empty Set contains no elements.
Beca
Logic and SQL
Propositions and Predicates
Proposition
Predicate
A statement that evaluates unconditionally to TRUE
or FALSE
A truth valued function
A function that when invoked or instantiated
returns a truth value (TRUE or FALSE)
Every Proposition is a P
Database Programming
CSC 5559
Overloaded Concepts
Relation and derived terms
Normal Forms
Domains
Database types
Relational
Relational Database
Entity Relationship Diagram (ERD)
Relational Design
What is a Relation
What is a Relationship
Normal Forms
1st,
Assignment for Module 5
The graded problems in module 5 involve SELECT statements for multiple tables and row
summaries and data manipulation statements using the INSERT, UPDATES, and DELETES
statements of SQL. You should execute the statements using eith