Transcript Databases

Databases
Enterprise Systems
Programming
Some database terminology
Database: integrated collection of data
 Database Management System (DBMS):
environment that provides mechanisms
for storage and access of data
 Relational Database Model: views a
database as a collection of relations or
tables where each table stores
information on an entity

Tables, attributes and columns
A table represents an entity in a system
 The rows of the table represent records or
instances of the entity
 The columns of the table represent the
entity’s attributes or characteristics


How do you determine a system’s entities?

Spot the nouns (people, places, things) in the
system
Example: The STUDENT table
Columns/Attributes
ID
LastName FirstName Year
QPI
081111
Cruz
Juan
2
3.5
072222
Smith
John
3
2.2
089999
Cruz
Maria
3
4.0
077676
Santos
Linda
4
3.0
There are four rows/records in this example
Column types/domains
Each column in a table has an associated
type indicating the possible values for the
attribute
 Most common types




Strings: CHAR, VARCHAR
Numbers: INTEGER, DOUBLE, NUMERIC
Date and time: DATE, TIME, TIMESTAMP
Primary key, foreign key, relationships

Primary key: column or attribute that
uniquely determines a row in the table


STUDENT table example: ID is the primary key
Foreign key: attribute in one table that
refers to a record in another table


Used to store relationships
(relationships are verbs in the system)
Example: a department code in the EMPLOYEE
table refers to the DEPARTMENT table,
signifying that the employee belongs to the
department
Relationship example
EMPLOYEE
EmpID
Name
Salary
DeptCode
123
CRUZ, JUAN
12345.00
HR
222
SANTOS, MARIA
11111.00
HR
545
MATIPID, PETE
30000.00
ACCT
DEPARTMENT
Employee SANTOS, MARIA
works for the
PERSONNEL department
DeptCode
DeptName
ACCT
ACCOUNTING
HR
PERSONNEL
MAINT
MAINTENANCE
Structured Query Language

SQL: Structured Query Language



Also called “SeQueL”
Standard underlying language for database
definition, manipulation, and query
SQL statements





SELECT (query)
INSERT
UPDATE
DELETE
Others (Data Definition)
The SELECT statement
A query that returns a table
 Simplest form:
SELECT column1, column2, … FROM table



List all records, all columns
SELECT * FROM table


SELECT EmpID, Salary FROM EMPLOYEE
SELECT * FROM DEPARTMENT
List particular record/s
SELECT * FROM table WHERE criteria

SELECT * FROM STUDENT WHERE year=3
The WHERE clause


Indicates selection criteria or condition against
which records on the table will be checked
Contains operators such as <, >, <=, >= =, <>
and LIKE




<> means not equal
LIKE performs a pattern match with strings
( _ matches a single character, % matches several
characters)
Condition may have logical operators (AND, OR, NOT)
Note the importance of the column type when
specifying the condition

Literal strings (e.g., CHAR) need to be delimited by
quotes or double quotes
ORDER BY clause
Can indicate ORDER BY in the SELECT
statement to specify ghe order in which
the records will be listed
 Syntax: append
ORDER BY col1,col2,…
to the SELECT statement
 Indicate ORDER BY col1,col2,… DESC
to list records in descending order

More examples





SELECT Name FROM EMPLOYEE
WHERE SALARY > 12000
SELECT FirstName,Lastname FROM STUDENT
WHERE LastName LIKE ‘S%’
SELECT * FROM STUDENT
WHERE year <> 2
SELECT * FROM STUDENT WHERE year=3
ORDER BY LastName, FirstName
SELECT Name FROM EMPLOYEE ORDER BY Salary
SELECT on multiple tables




FROM clause can contain multiple tables
Use when processing relationships
Tables are joined; indicate join condition in WHERE clause
Example:
SELECT Name, DeptName FROM EMPLOYEE, DEPARTMENT
WHERE EMPLOYEE.DeptCode = DEPARTMENT.DeptCode
AND Salary >10000
returns all names of employees (and the name of the
departments they belong to) who make more than 10000
The INSERT Statement
Inserts a record into a table
 Syntax:




INSERT INTO table VALUES(val1,val2,…)
INSERT INTO table(col1,col2,…)
VALUES(val1,val2,…)
Examples:


INSERT INTO DEPARTMENT
VALUES(“CS”, “COMPUTER SCIENCE”)
INSERT INTO EMPLOYEE(EmpID, Salary)
VALUES(143,10000.00)
The UPDATE Statement
Updates an existing record
 Syntax:




UPDATE table SET col1=expr1,col2=expr2,…
UPDATE table SET col1=expr1,col2=expr2,…
WHERE criteria
Examples


(updates all records)
UPDATE EMPLOYEE SET Salary=Salary+100
(updates some record/s)
UPDATE STUDENT SET QPI = 4.0 WHERE
FirstName = “Matalino”
The DELETE Statement
Deletes records from a table
 Syntax:



DELETE FROM table WHERE criteria
(warning: without a WHERE clause, all records
are deleted)
Example

DELETE FROM EMPLOYEE WHERE
DeptCode=“CS”
Back to JDBC


Methods of the Statement class require a string
parameter containing the SQL statement
executeQuery()



requires a String argument (a SELECT statement)
returns a ResultSet object representing the table
returned
executeUpdate()


requires a String argument
(an INSERT, UPDATE, or DELETE statement)
returns an int (row count, in most cases)
executeQuery( ) example
…
Statement s = con.createStatement();
ResultSet rs = s.executeQuery(
“SELECT * FROM STUDENT WHERE QPI > 3.0”
);
while ( rs.next() )
{
String name = rs.getString(“LastName”);
int y = rs.getInt(“Year”);
double qpi = rs.getDouble(“QPI”);
System.out.println( name + “ ” + y + “ ” + qpi);
}
executeUpdate( ) example
…
Statement s = con.createStatement();
int result;
result = s.executeUpdate(
“DELETE FROM EMPLOYEE WHERE DeptCode=‘CS’”
);
System.out.println( result + “ rows deleted.” );
The PreparedStatement class
PreparedStatement: a Statement that
specifies parameters through Java code
 The SQL statements take different forms
when you specify different parameter
values
 Useful when query is performed
repeatedly
 Formatting of literal values is easier

Version 1 (Statement)
// suppose lastName is a String variable
Statement s = con.createStatement();
Query string is built manually
ResultSet rs = s.executeQuery(
“SELECT * FROM STUDENT WHERE LastName = ‘” + lastName +”’”
);
while ( rs.next() )
{
String name =
rs.getString(“LastName”) + rs.getString(“FirstName”);
int y = rs.getInt(“Year”);
double qpi = rs.getDouble(“QPI”);
System.out.println( name + “ ” + y + “ ” + qpi);
}
Version 2 (PreparedStatement)
// suppose lastName is a String variable
PreparedStatement s = con.prepareStatement(
“SELECT * FROM STUDENT WHERE LastName = ?”
);
s.setString( 1, lastName );
the appropriate literal
ResultSet rs = s.executeQuery();
is “inserted” in the query
while ( rs.next() )
{
String name =
rs.getString(“LastName”) + rs.getString(“FirstName”);
int y = rs.getInt(“Year”);
double qpi = rs.getDouble(“QPI”);
System.out.println( name + “ ” + y + “ ” + qpi);
}
Summary





A relational database consists of tables that store
records of a system
The database is manipulated through SQL, the
underlying query and manipulation language of
relational databases
SELECT statements carry out queries
INSERT, UPDATE, and DELETE statements affect
the database
In Java/JDBC, use the Statement and
PreparedStatement classes to execute SQL
statements against a relational database