Transcript create table - FSU Computer Science Department

COP 4710 Databases
Fall, 2000
Today’s Topic
Chapter 7: SQL
David A. Gaitros
October 9th, 2000
Department of Computer Science
1
Modifying Content with SQL

Insert queries
– insert into Customer values (555, 'Yu', 'Jia','540
Magnolia Hall','Tallahassee', 'FL', '32306')
– insert into Customer (firstName, lastName,
accountId) values ('Jia', 'Yu', 555)

Update queries
– update TimeCard set paid = true
where paid = false
– update HourlyEmployee set hourlyRate =
hourlyRate *1.1 where ssn = '145-09-0967'

Samples in Access
2
Creating Pay Statements with SQL

Find the number of hours worked for each
employee entry
– select TimeCard.ssn, sum((endTimestartTime)*24) as hoursWorked from TimeCard
where paid=false group by ssn

Create the Pay Statement entries for each
Employee
– select ssn, hourlyRate, hoursWorked,
hoursWorked * hourlyRate as amountPaid, today
from …

Insert into the PayStatement table
– Insert into PayStatement select …

Look at the Access example in BigHit.mdb
3
Defining queries for the PayStatement


A view is a named query
create view EmployeeHours as
– select TimeCard.ssn, sum((endTimestartTime)*24) as hoursWorked from TimeCard
where paid=false group by ssn

create view EmployeePay as
– select ssn, hourlyRate, hoursWorked,
hoursWorked * hourlyRate as amountPaid, today
from EmployeeHours h, HourlyEmployee e where
h.ssn=e.ssn

insert into PayStatement select * from
EmployeePay
4
Marking TimeCards as paid




update TimeCard set paid = true
update TimeCard set paid=true where
paid=false
updateTimeCard set paid=true where ssn in
(select ssn from EmployeePay)
What happens if time cards added while pay
statements are being created?
5
Delete Statements

Delete all time cards for non-hourly
employees
– delete from Timecard where not exists
(select * from HourlyEmployee
where TimeCard.ssn = HourlyEmployee.ssn)

More examples in BigHit Video Access
database
6
Create Table Statement

create table Customer (
accountId int,
lastName varchar(32),
firstName varchar(32),
street varchar(100),
city varchar(32),
state char(2),
zipcode varchar(9)
)

Note that SQL has specific types
7
Data types in SQL
Numeric types
Character-string
types
Bit-string types
integer
floating
point
formatted
fixed
length
varying
length
fixed
length
varying
length
Date and time
types
Large types
character
binary
integer, int, smallint,
long
float, real, double
precision
decimal(i,j), dec(i,j)
char(n), character(n)
varchar(n), char
varying(n), character
varying(n)
bit(n)
bit varying(n)
date, time, datetime,
timestamp, time with
time zone, interval
long varchar(n), clob,
text
blob
8
Key Constraints in SQL

Key declarations are part of create table
– create table Store (
storeId int primary key,
– create table Movie (
movieId varchar(10) primary key,
– create table Rental (
accountId int,
videoId varchar(10),
primary key (accountId, videoId)
9
Referential Integrity Constraints

A relationship is implemented by attributes
that reference the primary key of the related
table
– Enforcing referential integrity requires
guaranteeing that there is a referenced object
– An attempt to modify the relationship (insert,
update or delete) is potential violation

Declare foreign key constraints
– create table Store (
manager int references Employee
– create table Rental (
foreign key (accountId) references
Customer(accountId)
10
Maintaining Referential Integrity

What happens when an update violates
referential integrity
– update foreign key attribute
• change catalog id of a video
– insert new object
• add a new video
– delete related object
• delete catalog entry
– update primary key attribute
• change catalog id of a video title

Alternatives
– propagate changes
– set to null
11
Constraints on Values of Attributes

Not null constraints
– create table PreviousRental (
accountId int not null references Customer,
videoId int not null references Videotape,
dateRented datetime not null,
dateReturned datetime,
cost real,
primary key (accountId, videoId, dateRented))

Check constraints
– check (checkOut < dueDate)
– check (answer in (‘T’,’F’))
– check (questionId in (select questionId from
questions where quizId=…))
12
Strategies for Enforcing Constraints

Enforce always
– Never allow a violation of constraint
– Suppose 2 rentals are recorded wrong
• change the customerId of 2 records
– some violation will result

Enforce at end of transaction
– Allow violations during updates, but check and
enforce at the end of the process

Leads us to consider
– Chapter 14 Transactions in SQL
– Allow cancellation of updates
– Support concurrent access
13
COP 4710 Databases
Fall, 2000
Today’s Topic
Chapter 9: Java and Web Sites
David A. Gaitros
Department of Computer Science
14
Java Objects and variables

Objects are dynamically allocated
– Figures A.1 and A.2 show String variables
• Assignment (=) and equality (==)
a.
firstName="Fred"; String
b.
firstName="Jane";
Fred
String
Fred
firstName
firstName
obj1
String
String
Jane
Jane
String
Jane
obj2
String
obj3
Jane
15
Differences from C++

C++ has three kinds of object variables
– ObjClass fixedObj, & refObj, * ptrObj
– Java has only one
– Hence, no dereferencing, no address calculation,
no pointer or reference types

C++ methods, by default, are not virtual
– Java methods are virtual by default

C++ virtual method hierarchies follow class
hierarchy
– Java virtual methods can be based on interfaces

C++ has preprocessor, compiler and linker
– Java has compiler and RTE
16
Browser-Web server-DB architecture

Figure 8.2: Architecture of a Web site
supported by databases
User and Application layer
Web
Browser
Middleware
layer
Web
Browser
Web
Browser
Web
Browser
Web Server
Database
Application
File System
Database
Application
Database
Server layer
Database
Server
Database
Server
Database
Server
17
Java DB Connectivity (JDBC)
Java
Program
Local computer
java.sql
package

Figure 8.4
Strategies for
implementing
JDBC
packages
JDBC package
JDBC package
JDBC-ODBC
bridge
JDBC package
Oracle Database
client
Middleware
client
ODBC database
client
Access ODBC
client
Oracle ODBC
client
Middleware
server
Intermediary
computer
Oracle Database
client
Database
computer
Access
server
Oracle
server
Sybase Database
client
Sybase
server
18
Connecting to databases with Java

java.sql.Driver
– no methods for users
– DriverManager.Connect method create connection

java.sql.Connection
– createStatement

java.sql.Statement
– executeQuery returns table as ResultSet
– executeUpdate returns integer update count

Examples in class
19
Details on JDBC Connections

Loading driver classes
– Class.forName(“sun.jdbc.odbc.JdbcOdbcDriver”);
// Driver for JDBC-ODBC Bridge. Supports the bridge
between the ODBC Microsoft Driver and the Java Database
Driver.
– Class.forName(“oracle.thin.Driver”);
// This is the driver needed to connect directly to Oracle.
– Class.forName(“jdbc:z1MySQL:”);
// This is the Driver to connect to a database using MySQL.
20
Details on JDBC Connections
Database
connection URL
–jdbc:<subprotocol>:<subname>
–protocol example
•jdbc:odbc:mydatabase
–subname example
•//hostname:port/databasename
•//enp01.enp.fsu.edu:3306/gsim
–CS Oracle URL
•jdbc:oracle:thin:@oracle.cs.fsu.edu:1521:cop45
40
21
Examples of JDBC Applications


See SqlFilter.java
See Web sites
– http://enp01.enp.fsu.edu

See code in examples directory
22
Executing Insert and Update Statements

Create new customer, using String +
int rowcount = stmt.executeUpdate(
”insert into Customer ”
+”(accountId,lastName,firstName) ”
+”values (1239,’Brown’,’Mary’)”);
if (rowcount == 0) // insert failed

Update
– String updateSQL = “update TimeCard set “
+”TimeCard.paid = 'yes’ where “
+”paid<>'yes’”;
int count = stmt.execute(updateSQL);
// count is number of rows affected
23
Executing unknown SQL


Arbitrary SQL may return table (ResultSet) or
row count (int)
Statement.execute method
stmt.execute(sqlStatement);
result = stmt.getResultSet();
while (true) {// loop through all results
if (result != null) // process result
else {// result is not a ResultSet
rowcount = stmt.getUpdateCount();
if (rowcount == -1) break // no more results
else // process row count
}
result = stmt.getMoreResults())
}
24