# Notes
DATABASE MANAGEMENT SYSTEM (DBMS)
# WHY USE A DBMS?
Storing data in traditional text or binary files has its limits
- well suited for applications that store only a small amount of data
- not practical for applications that must store a large amount of data
- simple operations become cumbersome and inefficient as data increases
A database management system (DBMS) is software that is specifically designed to work with large amounts of data in an efficient & organized manner
- Data is stored using the DBMS
- Applications written in Java or other languages communicate with the DBMS rather than manipulate the data directly
- DBMS carries out instructions and sends the results back to the application
# JDBC PROVIDES CONNECTIVITY TO THE DBMS
- JDBC stands for Java database connectivity
- It is the technology that makes communication possible between the Java application and DBMS
- The Java API contains numerous JDBC classes that allow your Java applications to interact with a DBMS
# USE SQL TO SEND COMMANDS TO DBMS
- SQL stands for structured query language
- A standard language for working with database management systems
- Not used as a general programming language
- Consists of several key words, used to construct statements known as
queries
- Statements or queries are passed from the application to the DBMS using API method calls
strings
- Serve as instructions for the DBMS to carry out operations on its data
# JDBC Needs a DBMS
To use JDBC to work with a database you will need a DBMS
# JDBC CLASSES FOR PROCESSING
- Java comes with a standard set of JDBC classes
java.sql
andjavax.sql
Using JDBC in a Java app requires
- Get a connection to the database
- Pass a string containing an SQL statement to the DBMS
- If the SQL statement has results to send back, they will be sent back as a result set
- When finished working with the database, close the connection
# Tables, Rows, and Columns
- A database management system stores data in a database
- A database is organized into one or more tables
- Each table holds a collection of related data, organized into rows and columns
- A row is a complete set of information (tuple) about a single item, divided into columns
- Each column is an individual piece of information about the item
# Column Data Types
Columns in a database are assigned an SQL data type
SQL data types are generally compatible with Java data types
The Coffee Table Column Data Types
Description
column data type isCHAR(25)
- String with a fixed length of 25 characters
- Compatible with the
String
type in Java
ProdNum
column data type isCHAR(10)
- String with a fixed length of 10 characters
- Compatible with the
String
type in Java
Price
column data type isDOUBLE
- Double-precision floating-point number
- Compatible with the
double
data type in Java
# Primary Keys
A primary key is a column that holds a unique value for each row in a database table
In the Coffee table,ProdNum
is the primary key- Each type of coffee has a unique product number
- Used to identify any coffee stored in the table
A primary key can be the combination of several columns in a table
# the SQL SELECT
Statement
The SELECT
statement is used to retrieve the rows in a table
Columns
is one or more column names- Multiple column names are separated with a comma
- The
*
character can be used to retrieve all columns in the table
Table
is a table name
SELECT Description FROM Coffee | |
SELECT Description, Price FROM Coffee | |
SELECT * FROM Coffee |
# Specifying Search Criteria with the WHERE
clause
The WHERE
clause can be used with the SELECT
statement to specify a search criteria
SELECT Columns FROM Table WHERE Criteria |
Criteria
is a conditional expression
SELECT * FROM Coffee WHERE Price > 12.00 |
- Only the rows that meet the search criteria are returned in the result set
- A result set is an object that contains the results of an SQL statement
# SQL Relational Operators
Standard SQL supports the following relational operators:
Operator | Meaning |
---|---|
> | Greater than |
< | Less than |
>= | Greater than or equal to |
<= | Less than or equal to |
= | Equal to |
<> | Not equal to |
Notice a few SQL relational operators are different than in Java
- SQL equal to operator is
=
- SQL not equal to operator is
<>
# String Comparisons in SQL
SELECT * FROM Coffee WHERE Description = 'French Roast Dark' |
- In SQL, strings are enclosed in single quotes
Warning!
SELECT * FROM Coffee WHERE Description = 'french roast dark' |
- String comparisons in SQL are case sensitive
SELECT * FROM Coffee | |
WHERE UPPER(Description) = 'FRENCH ROAST DARK' |
- The
UPPER()
orLOWER()
functions convert the string to uppercase or lowercase and can help prevent case sensitive errors when comparing strings
SELECT * FROM Coffee WHERE Description ='Joe''s Special Blend' |
- If a single quote
'
is part of a string, use two single quotes''
# Using the LIKE
Operator
In SQL, the LIKE
operator can be used to search for a substring
SELECT * FROM Coffee WHERE Description LIKE '%Decaf%' |
- The
%
symbol is used as a wildcard for multiple characters
SELECT * FROM Coffee WHERE ProdNum LIKE '2_-00_' |
- The underscore
_
is a used as a wildcard for a single character
SELECT * FROM Coffee | |
WHERE Description NOT LIKE '%Decaf%' |
- The
NOT
operator is used to disqualify the search criteria
# Using AND
and OR
The AND
and OR
operators can be used to specify multiple search criteria in a WHERE
clause
SELECT * FROM Coffee | |
WHERE Price > 10.00 AND Price < 14.00 |
- The
AND
operator requires that both search criteria be true
SELECT * FROM Coffee | |
WHERE Description LIKE '%Dark%' OR ProdNum LIKE '16%' |
- The
OR
operator requires that either search criteria be true
# Sorting results of a Select query
Use the ORDER BY
clause to sort results according to a column
- Sorted in ascending order
ASC
by default - Use the
DESC
operator to sort results in descending order
SELECT * FROM Coffee ORDER BY Price | |
SELECT * FROM Coffee WHERE Price > 9.95 ORDER BY Price DESC |
# Mathematical Functions
# The AVG
function
calculates the average value in a particular column
SELECT AVG(Price) FROM Coffee |
# The SUM
function
calculates the sum of a column's values
SELECT SUM(Price) FROM Coffee |
# The MIN
and MAX
functions
calculate the minimum and maximum values found in a column
SELECT MIN(Price) FROM Coffee | |
SELECT MAX(Price) FROM Coffee |
# The COUNT
function
can be used to determine the number of rows in a table
SELECT COUNT(*) FROM Coffee |
# Inserting rows
In SQL, the INSERT
statement inserts a row into a table
INSERT INTO TableName VALUES (Value1, Value2, ...) |
TableName
is the name of the database tableValue1, Value2, ...
is a list of column values
INSERT INTO Coffee | |
VALUES ('Honduran Dark', '22-001', 8.65) |
- Strings are enclosed in single quotes
- Values appear in the same order as the columns in the table
If column order is uncertain, the following general format can be used
INSERT INTO TableName | |
(ColumnName1, ColumnName2, ...) | |
VALUES | |
(Value1, Value2, ...) |
where....
ColumnName1, ColumnName2, ...
is a list of column namesValue1, Value2, ...
is a list of corresponding column values
INSERT INTO Coffee | |
(ProdNum, Price, Description) | |
VALUES | |
('22-001', 8.65, 'Honduran Dark') |
- Keep in mind that primary key values must be unique
- For example, a duplicate
ProdNum
is not allowed in the Coffee table
# Updating an Existing Row
In SQL, the UPDATE
statement changes the contents of an existing row in a table
UPDATE Table | |
SET Column = Value | |
WHERE Criteria |
Table
is a table nameColumn
is a column nameValue
is the value to store in the columnCriteria
is a conditional expression
UPDATE Coffee | |
SET Price = 9.95 | |
WHERE Description = 'Galapagos Organic Medium' |
It is possible to update more than one row
UPDATE Coffee | |
SET Price = 12.95 | |
WHERE ProdNum LIKE '21%' |
- Updates the price of all rows where the product number begins with 21
Warning!
UPDATE Coffee | |
SET Price = 4.95 |
- Because this statement does not have a
WHERE
clause, it will change the price for every row
# Deleting Rows with the DELETE
Statement
In SQL, the DELETE
statement deletes one or more rows in a table
DELETE FROM Table WHERE Criteria |
Table
is the table nameCriteria
is a conditional expression
DELETE FROM Coffee WHERE ProdNum = '20-001' |
- Deletes a single row in the Coffee table where the product number is 20-001
DELETE FROM Coffee WHERE Description = 'Sumatra%' |
- Deletes all rows in the Coffee table where the description begins with Sumatra
Warning!
DELETE FROM Coffee |
- Because this statement does not have a
WHERE
clause, it will delete every row in the Coffee table
# Creating Tables with the CREATE TABLE
Statement
In SQL, the CREATE TABLE
statement adds a new table to the database
CREATE TABLE TableName | |
(ColumnName1 DataType1, | |
ColumnName2 DataType2, ...) |
TableName
is the name of the tableColumnName1
is the name of the first columnDataType1
is the SQL data type for the first columnColumnName2
is the name of the second columnDataType2
is the SQL data type for the second columnThe
PRIMARY KEY
qualifier is used to specify a column as the primary keyThe
NOT NULL
qualifier is used to specify that the column must contain a value for every row- Qualifiers should be listed after the column's data type
CREATE TABLE Customer | |
( Name CHAR(25), Address CHAR(25), | |
City CHAR(12), State CHAR(2), Zip CHAR(5) ) |
- Creates a new table named
Customer
with the columnsName
,Address
,City
,State
, andZip
CREATE TABLE Customer | |
( CustomerNumber CHAR(10) NOT NULL PRIMARY KEY | |
Name CHAR(25), Address CHAR(25), | |
City CHAR(12), State CHAR(2), Zip CHAR(5) ) |
- Creates a new table named
Customer
with the columnsCustomerNumber
, which is the primary key,Name
,Address
,City
,State
, andZip
Example: CreateCustomerTable.java
# Removing a Table with the DROP TABLE
Statement
In SQL, the DROP TABLE
statement deletes an existing table from the database
DROP TABLE TableName |
TableName
is the name of the table you wish to delete
DROP TABLE Customer |
- Deletes the
Customer
table from theCoffeeDB
database - Useful if you make a mistake creating a table
- Simply delete the table and recreate
# Relational Data
- A
foreign key
is a column in one table that references aprimary key
in another table - Creates a relationship between the tables
Example:
UnpaidOrder table:
CustomerNumber | CHAR(10) | Foreign Key |
ProdNum | CHAR(10) | Foreign Key |
OrderDate | CHAR(10) | |
Quantity | DOUBLE | |
Cost | DOUBLE |
- The
CustomerNumber
column references theCustomer
table - The
ProdNum
column references theCoffee
table - This creates a relationship between the tables of the
CoffeeDB
database
The following SQL statement creates the UnpaidOrder table in the CoffeeDB database:
CREATE TABLE UnpaidOrder | |
(CustomerNumber CHAR(10) NOT NULL | |
REFERENCES Customer(CustomerNumber), | |
ProdNum CHAR(10) NOT NULL | |
REFERENCES Coffee(ProdNum), | |
OrderDate CHAR(10), | |
Quantity DOUBLE, | |
Cost DOUBLE ) |
- The
REFERENCES
qualifier ensures referential integrity between tables- The
CustomerNumber
in theUnpaidOrder
table must contain a validCustomerNumber
from theCustomer
table - The
ProdNum
in theUnpaidOrder
table must contain a validProdNum
from theCoffee
table
- The
# Entity Relationship Diagrams
An entity relationship diagram shows the relationships between tables
- Primary keys are denoted with (PK)
- Lines drawn between tables show how they are related
- The ends of each line show either a 1 or an infinity symbol (∞)
- The infinity symbol means many and number 1 means one.
- A one to many relationship means that for each row in table A there can be many rows in table B that reference it.
- A many to one relationship means that many rows in table A can reference a single row in table B.
- The ends of each line show either a 1 or an infinity symbol (∞)
# CoffeeDB Relationships Left to Right
- One to many relationship between
Customer
andUnpaidOrder
- One row in the
Customer
table may be referenced by many rows in theUnpaidOrder
table
- One row in the
- Many to one relationship between the
UnpaidOrder
andCoffee
tables- Many rows in the
UnpaidOrder
table may reference a single row in theCoffee
table.
- Many rows in the
# CoffeeDB Relationships Right to Left
- One to many relationship between
Coffee
andUnpaidOrder
- One row in the
Coffee
table may be referenced by many rows in theUnpaidOrder
table
- One row in the
- Many to one relationship between
UnpaidOrder
andCustomer
Many rows in theUnpaidOrder
table may reference a single row in theCustomer
table.
# Joining Data from Multiple Tables
In SQL, you must use qualified column names in a SELECT statement if the tables have columns with the same name
A qualified column name takes the following form: TableName.ColumnName
SELECT | |
Customer.CustomerNumber, Customer.Name, | |
UnpaidOrder.OrderDate, UnpaidOrder.Cost, | |
Coffee.Description | |
FROM | |
Customer, UnpaidOrder, Coffee | |
WHERE | |
UnpaidOrder.CustomerNumber = Customer.CustomerNumber | |
AND | |
UnpaidOrder.ProdNum = Coffee.ProdNum |
- The search criteria tell the DBMS how to link the rows in the tables
The Place Order application uses a relational database CoffeeDB
Requires the Coffee
, Customer
, and UnpaidOrder
tables
# Transactions
- An operation that requires multiple database updates is known as a transaction.
需要多次数据库更新的操作称为事务。 - For a transaction to be complete
- All of the steps involved in the transaction must be performed.
- If any single step within a transaction fails
- None of the steps in the transaction should be performed.
- When you write transaction-processing code, there are two concepts you must understand:
- Commit
- Rollback
- The term
commit
refers to making a permanent change to a database - The term
rollback
refers to undoing changes to a database
# SQL | DDL, DQL, DML, DCL and TCL Commands
Structured Query Language(SQL) as we all know is the database language by the use of which we can perform certain operations on the existing database and also we can use this language to create a database. SQL uses certain commands like Create, Drop, Insert etc. to carry out the required tasks.
These SQL commands are mainly categorized into four categories as:
- DDL – Data Definition Language
- DQl – Data Query Language
- DML – Data Manipulation Language
- DCL – Data Control Language
Though many resources claim there to be another category of SQL clauses TCL – Transaction Control Language. So we will see in detail about TCL as well.
# DDL(Data Definition Language)
DDL or Data Definition Language actually consists of the SQL commands that can be used to define the database schema. It simply deals with descriptions of the database schema and is used to create and modify the structure of database objects in the database.
Examples of DDL commands:
CREATE
- is used to create the database or its objects (like table, index, function, views, store procedure and triggers).
DROP
- is used to delete objects from the database.
ALTER
- is used to alter the structure of the database.
TRUNCATE
- is used to remove all records from a table, including all spaces allocated for the records are removed.
COMMENT
- is used to add comments to the data dictionary.
RENAME
- is used to rename an object existing in the database.
# DQL (Data Query Language)
DML statements are used for performing queries on the data within schema objects. The purpose of DQL Command is to get some schema relation based on the query passed to it.
Example of DQL:
SELECT
- is used to retrieve data from the a database.
# DML(Data Manipulation Language)
The SQL commands that deals with the manipulation of data present in the database belong to DML or Data Manipulation Language and this includes most of the SQL statements.
Examples of DML:
INSERT
- is used to insert data into a table.
UPDATE
- is used to update existing data within a table.
DELETE
- is used to delete records from a database table.
# DCL(Data Control Language)
DCL includes commands such as GRANT and REVOKE which mainly deals with the rights, permissions and other controls of the database system.
Examples of DCL commands:
GRANT
- gives user's access privileges to database.
REVOKE
- withdraw user's access privileges given by using the GRANT command.
# TCL(transaction Control Language)
TCL commands deals with the transaction within the database.
Examples of TCL commands:COMMIT
~ commits a Transaction.
ROLLBACK
- rollbacks a transaction in case of any error occurs.
SAVEPOINT
- sets a savepoint within a transaction.
SET TRANSACTION
- specify characteristics for the transaction.
# Review
DB table creations & queries
Fill in the create table statement below to create a table called tickets given the field listings below.
Note field name and datatype is required when listing fields in the create statement below.
What additional fields / datatypes would you add as a suggestion when building your table?
Any additional tables you would think would be necessary for an app that is for a trouble ticket system?
Fill in the insert statement below to insert a record into your table that contains this data
Fill in the insert statement again to add a record to the table this time with variables called
ticket_num
andticket_desc
?When would you perform a commit as a command?
- The COMMIT command is the transactional command used to save changes invoked by a transaction to the database.
COMMIT 命令是用于将事务调用的 “更改” 保存到数据库的事务性命令。 - The COMMIT command saves all transactions to the database since the last COMMIT or ROLLBACK command.
COMMIT 命令将自上一个 COMMIT 或 ROLLBACK 命令以来的所有事务保存到数据库中。 - The syntax for COMMIT command is as follows:
语法如下COMMIT;
- The COMMIT command is the transactional command used to save changes invoked by a transaction to the database.