1

COP 4710 DatabasesFall, 2000

Today’s Topic

Chapter 7: SQL

David A. GaitrosOctober 9th, 2000Department of Computer Science

2

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

3

Creating Pay Statements with SQL Find the number of hours worked for each

employee entry– select TimeCard.ssn, sum((endTime-

startTime)*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

4

Defining queries for the PayStatement A view is a named query create view EmployeeHours as

– select TimeCard.ssn, sum((endTime-startTime)*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

5

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?

6

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

7

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

8

Data types in SQLinteger integer, int, smallint,

longfloatingpoint

float, real, doubleprecision

Numeric types

formatted decimal(i,j), dec(i,j)fixedlength

char(n), character(n)Character-stringtypes

varyinglength

varchar(n), charvarying(n), charactervarying(n)

fixedlength

bit(n)Bit-string types

varyinglength

bit varying(n)

Date and timetypes

date, time, datetime,timestamp, time withtime zone, interval

character long varchar(n), clob,text

Large types

binary blob

9

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)

10

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)

11

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

12

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=…))

13

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

14

COP 4710 DatabasesFall, 2000

Today’s Topic

Chapter 9: Java and Web Sites

David A. GaitrosDepartment of Computer Science

15

Java Objects and variables Objects are dynamically allocated

– Figures A.1 and A.2 show String variables• Assignment (=) and equality (==)

b. firstName="Jane"; String

Fred

String

Jane

firstName

a. firstName="Fred"; String

Fred

String

Jane

firstName

obj1

obj2

obj3

String

Jane

String

Jane

16

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

17

Browser-Web server-DB architecture Figure 8.2: Architecture of a Web site

supported by databases

WebBrowser

Web Server

Middlewarelayer

DatabaseServer

DatabaseServer

DatabaseServer layer

DatabaseServer

User and Application layer

WebBrowser

DatabaseApplication

DatabaseApplication

WebBrowser

WebBrowser

File System

18

Java DB Connectivity (JDBC)

Figure 8.4 Strategies for implementing JDBC packages

Intermediarycomputer

Databasecomputer

Local computer JavaProgram

java.sqlpackage

JDBC-ODBCbridge

ODBC databaseclient

Oracle Databaseclient

Middlewareclient

Middlewareserver

Oracleserver

Accessserver

Sybaseserver

Oracle Databaseclient

Sybase Databaseclient

Oracle ODBCclient

Access ODBCclient

JDBC package JDBC package JDBC package

19

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

20

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.

21

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:cop4540

Details on JDBC Connections

22

Examples of JDBC Applications See SqlFilter.java See Web sites

– http://enp01.enp.fsu.edu See code in examples directory

23

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

24

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())

}