# 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 and javax.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 is CHAR(25)
- String with a fixed length of 25 characters
- Compatible with the String type in Java
ProdNum column data type is CHAR(10)
- String with a fixed length of 10 characters
- Compatible with the String type in Java
Price column data type is DOUBLE
- 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

Examples

	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

Example

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

Examples

	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 table
Value1, Value2, ... is a list of column values

Example

	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 names
Value1, Value2, ... is a list of corresponding column values

Example

	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 name
Column is a column name
Value is the value to store in the column
Criteria is a conditional expression

Example

	UPDATE Coffee
	SET Price = 9.95
	WHERE Description = 'Galapagos Organic Medium'

It is possible to update more than one row

Example

	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 name
Criteria is a conditional expression

Example

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 table
ColumnName1 is the name of the first column
DataType1 is the SQL data type for the first column
ColumnName2 is the name of the second column
DataType2 is the SQL data type for the second column
The PRIMARY KEY qualifier is used to specify a column as the primary key
The 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

Example

	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 columns Name , Address , City , State , and Zip

Example

	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 columns CustomerNumber , which is the primary key, Name , Address , City , State , and Zip

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

Example

DROP TABLE Customer

Deletes the Customer table from the CoffeeDB 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 a primary 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 the Customer table
The ProdNum column references the Coffee 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 the UnpaidOrder table must contain a valid CustomerNumber from the Customer table
- The ProdNum in the UnpaidOrder table must contain a valid ProdNum from the Coffee table

# 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.

# CoffeeDB Relationships Left to Right

One to many relationship between Customer and UnpaidOrder
- One row in the Customer table may be referenced by many rows in the UnpaidOrder table
Many to one relationship between the UnpaidOrder and Coffee tables
- Many rows in the UnpaidOrder table may reference a single row in the Coffee table.

# CoffeeDB Relationships Right to Left

One to many relationship between Coffee and UnpaidOrder
- One row in the Coffee table may be referenced by many rows in the UnpaidOrder table
Many to one relationship between UnpaidOrder and Customer
Many rows in the UnpaidOrder table may reference a single row in the Customer 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.

Field name	Field Type	Field Size	Primary Key?	Nulll?
id	Auto_increment		Y	N
ticketNum	Integer		N	Y
ticketDecsc	varchar	100

Note field name and datatype is required when listing fields in the create statement below.

	String sql = "CREATE TABLE tickets " +
	"(id INTEGER not NULL AUTO_INCREMENT, " +
	" ticketNum INTEGER, " +
	" ticketDesc VARCHAR(100), " +
	" PRIMARY KEY ( id ))";

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?
Field name Values Field Type Field Size Primary Key? Nulll?
id Auto_increment Y N
ticketNum Integer N Y
ticketDecsc varchar 100
Uid N
Start_date
End_date
priority H,M,L

Fill in the insert statement below to insert a record into your table that contains this data

Field name	Data
Id
ticketNum	1001
ticketDecsc	PC Virus

	String sql = "INSERT INTO tickets(ticketNum, ticketDesc) " +
	" VALUES (1001, 'PC VIRUS')";

Fill in the insert statement again to add a record to the table this time with variables called ticket_num and ticket_desc ?

	String sql = "INSERT INTO tickets(ticketNum, ticketDesc) " +
	" VALUES ('"+ticket_num+"','"+ticket_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;

# Notes

# WHY USE A DBMS?

# JDBC PROVIDES CONNECTIVITY TO THE DBMS

# USE SQL TO SEND COMMANDS TO DBMS

# JDBC Needs a DBMS

# JDBC CLASSES FOR PROCESSING

# Tables, Rows, and Columns

# Column Data Types

# Primary Keys

# the SQL SELECT Statement

# Specifying Search Criteria with the WHERE clause

# SQL Relational Operators

# String Comparisons in SQL

# Using the LIKE Operator

# Using AND and OR

# Sorting results of a Select query

# Mathematical Functions

# The AVG function

# The SUM function

# The MIN and MAX functions

# The COUNT function

# Inserting rows

# Updating an Existing Row

# Deleting Rows with the DELETE Statement

# Creating Tables with the CREATE TABLE Statement

# Removing a Table with the DROP TABLE Statement

# Relational Data

# Entity Relationship Diagrams

# CoffeeDB Relationships Left to Right

# CoffeeDB Relationships Right to Left

# Joining Data from Multiple Tables

# Transactions

# SQL | DDL, DQL, DML, DCL and TCL Commands

# DDL(Data Definition Language)

# DQL (Data Query Language)

# DML(Data Manipulation Language)

# DCL(Data Control Language)

# TCL(transaction Control Language)

# Review

Week 9. Streams API

Lecture 6. Aesthetics in HCI

# the SQL `SELECT` Statement

# Specifying Search Criteria with the `WHERE` clause

# Using the `LIKE` Operator

# Using `AND` and `OR`

# The `AVG` function

# The `SUM` function

# The `MIN` and `MAX` functions

# The `COUNT` function

# Deleting Rows with the `DELETE` Statement

# Creating Tables with the `CREATE TABLE` Statement

# Removing a Table with the `DROP TABLE` Statement