Java - SQL and JDBC (Week 3)

What are the steps needed when creating a database connection?

1. Register the JDBC driver 2. Open a connection using: -Database URL -Username -Password 3. Execute some SQL statement using either: -Statement -PreparedStatement -CallableStatement 4. Retrieve the results that are returned in a ResultSet object

What are the normal forms of normalization?

1NF, 2NF, and 3NF

What is a read uncommitted level?

A disaster. Dirty reads are normal, any transaction can see any uncommitted data. Very inconsistent.

What is a One-to-Many relationship?

A one to many (or vice versa, many to one) relationship is where one entity can belong to, own, or otherwise relate to multiple other entities. Example: In our school, a Student could have many books, so this would be a one to many relationship. To create this in the database, we add the foreign key only on the many side of the relationship - so a book entity would have a field such as student_id as a foreign key to identify the owning student.

What are serializable levels?

Allowed in Oracle Read/Write locks Applies range locks even in the WHERE clauses of a select statement. Phantom reads can't happen because of this. Table that is being read can't be modified until the reading is done (no INSERTS, no UPDATES, no DELETES).

What is a self join?

An INNER JOIN performed matching two columns existing in the same table. They represent hierarchies.

What are the four properties of a transaction?

Atomicity, Consistency, Isolation, and Durability (ACID)

What are common binary data types in SQL?

BINARY VARBINARY IMAGE

What are common numeric data types in SQL?

BIT TINYINT BIGINT DECIMAL NUMERIC FLOAT REAL

What is Auto-Commit?

By default, when a connection is created it is in auto-commit mode, so every SQL statement acts as a transaction and is committed immediately after execution. In order to manually group statements into a transaction, simply call:

What are the character/string data types in SQL?

CHAR NCHAR VARCHAR NVARCHAR NTEXT

What are common miscellaneous data types in SQL?

CLOB BLOB XML JSON

What are common date/time data types in SQL?

DATE TIMESTAMP DATETIME TIME YEAR

What is read phenomena?

Dirty Read: reading data that is uncommitted. Non-repeatable read: when a row is read twice in a transaction and the values are different. Phantom Read: reading data that is being added or modified by a running transaction.

What are JDBC classes and interfaces?

DriverManager class - to make a connection with a database driver DataSource interface - for retrieving connections, an alternative to DriverManager Connection interface - represents a physical connection with a database SQLException class - a general exception thrown when something goes wrong when accessing the database Statement interface - used for executing static SQL statements PreparedStatement interface - represents pre-compiled SQL statements CallableStatement interface - used to execute stored procedures ResultSet interface - represents data returned from the database

How do you set up a JDBC database driver?

JDBC is a Java language API, it is database agnostic. It uses database drivers which implement the interfaces defined in the JDBC API for the given database. Many JDBC drivers are available through Maven's central repository and can be added as a dependency in the pom.xml file.

What is JSON?

JavaScript Object Notation

What is the repeatable read level?

Not used often Read/Write locks Doesn't provide range locks, that means phantom reads can happen. Doesn't lock the whole SELECT statement, nor INSERTS, nor UPDATES, nor DELETS.

What are common SQL datatypes?

Numeric, Date/Time, Character/String, Binary, and Miscellaneous

When can you write SQL statements?

Once we have the Connection object, we can write our SQL and execute it: Example: Statement stmt = conn.createStatement(); String sql = "SELECT * FROM employees"; ResultSet rs = stmt.executeQuery(sql);

What are the different types of relationships between tables in relational databases?

One-to-One One-to-Many Many-to-One Many-to-Many

What does the intersect operator do?

Only returns records in common between the queries

What is a read committed level?

Oracle default Write only locks Only data that is committed will be seen by other transactions. Dirty reads can't happen, but Phantom reads can. This is why it is recommended to not perform very long transactions.

What are the different types of constraints in SQL?

PRIMARY KEY FOREIGN KEY NOT NULL UNIQUE CHECK DEFAULT AUTO INCREMENT

What does the minus operator do?

Removes from the first result set any rows that appear in the second result set and returns what remains

What does the except operator do?

Removes from the first result set any rows that appear in the second result set and returns what remains, but for SQLServer instead of Oracle.

What is REST?

Representational State Transfer

What is full [outer] join?

Returns all rows from both tables specified including the ones which had null values on either side.

What is a cross join?

Returns the cartesian product two or more tables.

What is a left [outer] join?

Returns the matching rows plus the ones that where null in the first table.

What is a right [outer] join?

Returns the matching rows plus the ones that where null on the second table.

What is the case convention in SQL?

SQL is a case-insensitive language, but the convention is to use UPPERCASE to refer to SQL keywords and lowercase for non-SQL specific entities (like table or column names).

What are the different types of isolation levels?

Serializable, Repeatable Reads, Read Committed, and Read Uncommitted.

What are set operators?

Set operators are different from joins. Instead of combining columns of two tables, set operators combine the rows of different result sets. Essentially, set operators perform some kind of (set) operation on two different queries.

What is an outer join?

The OUTER keyword can be used with LEFT, RIGHT or FULL keywords to obtain rows which some of the join columns are NULL. However, in Oracle, this word is optional. LEFT, RIGHT or FULL will be automatically OUTER.

What is a JDBC string?

The database URL is an address pointing to the database to be used, also known as the JDBC String. The format of this URL varies between database vendors. Example: jdbc:mysql://hostname/databaseName jdbc:oracle:thin:@hostname:portNumber:databaseName jdbc.SQLServerDriverjdbc:sqlserver://serverName:portNumber;property=value jdbc:postgresql://hostname:port/databaseName

What is an inner join?

The most commonly used type of join, returns rows only if the columns specified in the join clause match.

What does the union [all] operator do?

UNION does not keep duplicates, but UNION ALL will

What are the different kinds of set operations?

Union [All] Intersect Minus Except

What is a natural join?

Used as a shortcut so that the join predicate is not needed to be specified. The tables are joined on matching column names.

What are joins in SQL?

Used to combine two or more tables, joins are a database technique used in SELECT statements. Joins are normally performed comparing primary keys to foreign keys, however, they can be performed with any type of column, as long as the types match. Joins are a way of denormalizing a set of tables in order to aggregate or process some query. Example: SELECT A.FIRSTNAME, C.NAME FROM STUDENT S INNER JOIN COURSE C ON S.COURSEID = C.ID; SELECT A.FIRSTNAME, C.NAME FROM STUDENT S, COURSE C WHERE S.COURSEID = C.ID; SELECT A.FIRSTNAME, C.NAME FROM STUDENT S, COURSE C WHERE S.COURSEID = C.ID(+);

What is a candidate key?

Usually, a primary key consists of a single column. Multiple columns that together create a primary key to uniquely identify rows is known as a ____________.

What is exception/error mapping in PostgreSQL?

When an error occurs in a block, PostgreSQL will abort the execution of the block and also the surrounding transaction. SQLException is used for handling.

Atomicity

all transactions will execute successfully or none of them will. "All or nothing."

What is Auto Increment?

allows a unique number to be generated automatically when a new record is inserted into a table. Very often the primary key of a table needs to be created automatically. Example: CREATE TABLE table_name ( Column1 DataType AUTO_INCREMENT PRIMARY KEY, Column2 DataType, );

What are constraints in SQL?

are rules placed on specific columns in our database when defining tables, which allow us to enforce the schema by ensuring consistency and integrity of the data in the table.

What are aggregate functions?

are the built-in functions in SQL. Input provided to these functions are usually all values in a column that produce a single value output. Examples: COUNT() SUM() AVG() MIN() MAX()

What are scalar functions?

are the built-in functions in SQL. Input provided to these functions produce a single value output. Examples: ROUND() CIEL() FLOOR() LENGTH() UCASE() LCASE()

What does the 'ROLLBACK' keyword do?

completely erases DML operations between two COMMIT statements (something like Ctrl + Z that will stop only when it reaches last time you opened the specific file). Committed transactions cannot be rollbacked.

Consistency

constraints are enforced for every committed transaction. All keys, data types, checks, and triggers are successful and no constraint violation is triggered.

What is the Data Access Object (DAO) design pattern?

defining an interface which declares methods through which the database will be queried. Then, concrete implementation classes can implement the interface and contain the data access logic to return the required data. Example: If we have an Employee table in our database we'd like to query, we would create a EmployeeDAO interface: public interface EmployeeDAO { // define some CRUD operations here public List<Employee> getAllEmployees(); public List<Employee> getEmployeesByLocation(String location); public void updateEmployeeById(int id); public void deleteEmployeeById(int id); public void addEmployee(Employee e); } ------------------------------------------------------------- This interface would be implemented for a specific database - e.g. Oracle: public class EmployeeDAOImplOracle implements EmployeeDAO { public List<Employee> getAllEmployees() { List<Employee> list = new ArrayList<>(); // JDBC code here... return list; }; public List<Employee> getEmployeesByLocation(String location) { List<Employee> list = new ArrayList<>(); // JDBC code here... return list; }; public void updateEmployeeById(int id) { // JDBC code here... }; public void deleteEmployeeById(int id) { // JDBC code here... }; public void addEmployee(Employee e) { // JDBC code here... }; } ------------------------------------------------------------- Now whenever we need to query the Employee table in the database, we have a simple, clean interface which abstracts the data access logic: EmployeeDAO dao = new EmployeeDAOImplOracle(); List<Employee> allEmpls = dao.getAllEmployees(); allEmpls.forEach( e -> System.out.println(e)); List<Employee> NYEmpls = dao.getEmployeesByLocation("New York"); NYEmpls.forEach( e -> System.out.println(e)); ------------------------------------------------------------- Also, we can simply swap out the concrete class EmployeeDAOImplOracle for another database-specific class if we need to at some point in the future, since we rely only on the EmployeeDAO interface. The implementation doesn't even need to be talking to a database - it could be reading and writing to files for all we know! We don't care how the data is being read or written, we just care what operations are defined for the object. That is the benefit the DAO design pattern brings to the table.

What does the 'CASCADE DELETE' statement do?

deleting a record in the table will also cascade that operation and delete any records in tables that reference that record via foreign keys. This method is used to prevent orphan records.

What is multiplicity?

describes the relationship between two tables in a database.

What is a One-to-One relationship?

each entity in the table only relates to a single entity in the other table. Example: If we are modeling a school, where each classroom has a single projector in it, we would want to make this relationship a one to one between the Classroom and the Projector tables. In our database, we can provide the classroom table a projector_id foreign key and provide the projector table a classroom_id foreign key. To enforce the one to one aspect, we should also apply a unique constraint on the foreign key columns. Otherwise, a user could add another projector record with the same classroom_id as an existing record, and then our one to one relationship would be broken.

Where are the JDBC classes and interfaces located?

in the java.sql and javax.sql packages.

What does the 'INSERT' keyword do?

inserts a new row into a table. Example: INSERT INTO table_name VALUES(V1, V2, ... , VN) INSERT INTO table_name (C1, C2, C3) VALUES (V1, V2, V3)

What is a foreign key?

is a constraint signifying that a column represents a reference to the primary key of another table. This allows us to create relationships between tables. Example: If we are modeling cars and the owners of those cars, we might have a Car table with an owner_id foreign key that references the user_id field in the People table. We can then lookup the owner of any car by fetching the owner_id of the car and finding the matching user_id in the People table.

What is a DEFALUT constraint?

is a constraint that allows setting default values on columns for records that are inserted into the table.

What is the CHECK constraint?

is a constraint that provides a way of performing validation on values before records are entered into the table. Example: We may want to ensure that a bank account can never have a negative balance, so we might set a check constraint (CHECK (balance >= 0)).

What is the NOT NULL constraint?

is a constraint that simply enforces that all records must have a field for the column on which this constraint is applied. Example: We know that every person has a social security number, so we might want to consider placing a not null constraint on that field in our users table (assuming we want to store the social security numbers). This prevents users of the database from leaving the table in an inconsistent or invalid state. The unique constraint works similarly - records cannot be inserted if another record already has the same value for the column on which this is declared.

What is a primary key?

is a constraint that uniquely identifies a record in a table. Often, this constraint will be enforced on some sort of "ID" field, such as "employee_id".

What is an Entity Relationship Diagram (ERD)?

is a diagram that provide visualization of the schema of relational databases.

What is 2nd normal form (2NF)?

is a form where a table in a database must already be in 1NF, plus have no partial dependencies meaning that columns are not dependent on only one part of the key. If there are no composite primary keys, you are automatically in 2NF.

What is 3rd normal form (3NF)?

is a form where a table in a database must already be in 2NF, plus have no transitive dependencies. Example: If column C relates to column B which relates to column A which is the primary key, this is not in 3NF because C is related to the primary key but indirectly (it is a transitive dependency).

What is 1st normal form (1NF)?

is a form where a table in a database must have a primary key, no repeating groups, and atomic columns. "Atomic" means "cannot be divided or split in smaller parts". Applied to 1NF this means that a column should not contain more than one value.

What is a Many-to-Many relationship?

is a one-to-many relationship in both directions on the entities. Example: A Teacher can have many Students, but a Student could have many Teachers as well. In this case, we cannot provide a direct link between the tables in the database - instead, we need to create what is called a junction table or bridge table to relate the two tables. So, in our student-teacher example, we could create a Class table which contains two foreign keys - one that refers to the Teacher table's primary key and one that refers to the Student table's primary key. This creates a list of unique Teacher-Student mappings that can be used to look up which students a particular teacher teaches, or which teachers a particular student has. An example is shown below. Class Table ClassId TeacherId StudentId 1 1 1 1 1 2 2 1 3 3 2 1 3 2 3 We can see above that Teacher 1 teaches both Student 1 and 2 in the same class. Teacher 2 teaches Student 1 and 3 in a different class. Teacher 1 also has another class where he just teaches Student 3.

What is an orphan record?

is a record whose foreign key does not point to an existing record in another table. This is caused when referential integrity is broken.

Transaction Control Language (TCL)

is a sublanguage of SQL that is utilized to manage transactions made by DML statements within a relational database.

Data Definition Language (DDL)

is a sublanguage of SQL utilized to define the database schema or skeleton.

Data Manipulation Language (DML)

is a sublanguage of SQL utilized to perform create, read, update, and delete (CRUD) operations on the actual data. Operations are normally performed by row in a relational database.

What is Amazon Web Services (AWS) Relational Database Service (RDS)?

is a web service that makes it easier to set up, operate, and scale a relational database in the AWS Cloud. It organizes the data within tables in rows and columns. It provides cost-efficient, resizable capacity for an industry-standard relational database and manages common database administration tasks. Example: Instances include T3, T2, M6g, and M5

What is PostgreSQL?

is an open-source relational database management system (RDBMS) emphasizing extensibility and SQL compliance.

Normalization

is an optimization process of structuring a relational database in a way that reduces redundancy of data and improves data integrity and consistency.

Data Query Language (DQL)

is associated with querying data from tables. The SELECT keyword is the only keyword in this sublanguage. DQL is not considered a sublanguage by Oracle. The SELECT statement is a part of DML in Oracle.

What is a primary key composed of?

is inherently composed of two other constraints - unique and not null. MUST be provided when inserting a record into a table, unless the RDBMS system is generating it automatically behind the scenes.

What is referential integrity?

is keeping data in the database in a consistent state. Example: We never want a record on our class table to be pointing to a record in either the Teacher or the Student table that does not exist.

What is a good way of remembering these normal forms in order?

is to remember the legal proceeding of swearing to tell the truth, the whole truth, and nothing but the truth. In relational databases, we must have the key (1NF), the whole key (2NF), and nothing but the key (3NF).

What is the PreparedStatement interface?

is used for executing pre-compiled SQL statements.

What is the Statement interface?

is used for executing static SQL statements.

What does the 'GRANT' keyword do?

is used to give permissions to an existing user. Example: GRANT PERMISSION TO USERNAME

Data Control Language

is used to manage security and control of databases. The GRANT and REVOKE keywords are used in this sublanguage.

What does the 'SELECT' keyword do?

is used to select a specific group or element from a database. Example: SELECT UPPER(NAME) FROM STUDENT; SELECT NAME FROM STUDENT WHERE UPPER(NAME) LIKE 'P%'.

What does the 'REVOKE' keyword do?

is used to take away permissions from an existing user. Example: REVOKE PERMISSION TO USERNAME

What does the 'SAVEPOINT' keyword do?

is utilized to ROLLBACK to a specific point in time. Example: [Many DML Operations] SAVEPOINT A [Many DML Operations] ROLLBACK TO A

What is a composite key?

is when we identify the actual combination of columns to use as the primary key, we call this a ________________. Example: If you are modeling your CD collection, you might have fields such as track_no, album_id, and genre in the Track table. The track_no itself cannot work as a primary key because many different albums can have a track #1, for example. So we would need to create a composite key here, consisting of the track_no and the album_id columns. Using both of these columns together we can find the specific track we are looking for.

What does the 'CREATE' keyword do?

it creates new objects or tables. Example: CREATE TABLE table_name (C_Name C_Type C_Syze [Null | Not Null], [Constraint])

What does the 'TRUNCATE' keyword do?

it deletes all the data existing within a table leaving the skeleton of the table only OR delete decimal points. Example: TRUNCATE table_name TRUNCATE value by 2 decimal places. Value is 3.14, Trunc(Value) is 3.

What does the 'DROP' keyword do?

it deletes an existing object or table. Example: DROP TABLE table_name [Cascade]

What does the 'ALTER' keyword do?

it modifies an existing object or table. Example: ALTER TABLE table_name [Add | Modify | Drop] C_name ALTER user identified by password.

What is the Connection Interface?

java.sql.Connection interface represents a session between java application and database. All SQL statements are executed and results are returned with in the context of a Connection object. We can use the DriverManager class to get a Connection to the database, given that we have the JDBC URL, username, and password. Generally these parameters should be stored in an external configuration file that can be loaded dynamically and changed without affecting the application code.

What are isolation levels?

levels are applied in RDBMS to provide consistency and avoid certain read phenomena.

What does the 'COMMIT' keyword do?

makes any DML operations that were executed before the statements to be persisted permanently. These transactions can not be rolled back.

Durability

once a transaction is complete, it guarantees that all of the changes are recorded in the database. Persisted data should be saved permanently, even in the case of power loss or catastrophic software or hardware failure.

What is schema?

refers to the formal structure of data defined in a relational database including the tables, columns, data types, and relationship between tables.

Integration Test

test that tests parts of your code that connect to external services. This is to test that those external services are behaving as expected.

What are Data Access Objects (DAO)?

the code that accesses the database.

Isolation

transactions executing concurrently and working on the same data will not affect each other.

What is a good way to advance into higher normal forms?

we typically "break up" tables into multiple tables and relate them to each other via foreign keys.