L.09 Flashcards
Transactions, JDBC and ORM
What are functional dependencies?
A set of attributes X functionally determines a set of attributes Y if the value of X determines a unique value for Y. Mathematically: X → Y.
Give an example of a functional dependency.
Social Security Number (SSN) determines an employee name: SSN → ENAME.
What are the conditions for an acceptable functional dependency?
It is acceptable if
(1) X or a subset of X is a key and X == Y,
or (2) X or a subset of X is a foreign key pointing at a key of Y.
What are the three normal forms?
1NF: No composed or multi-valued attributes.
2NF: Every non-key attribute is functionally dependent on the primary key.
3NF: No non-key attribute is functionally dependent on another non-key attribute.
What is a transaction in databases?
A transaction is an atomic set of statements that must either all be executed or none at all.
What are transaction boundaries?
Transaction boundaries are defined using:
BEGIN TRANSACTION
COMMIT TRANSACTION
ROLLBACK TRANSACTION
What are ACID properties of transactions?
-Atomicity: All changes are applied, or none.
-Consistency: The database remains in a valid state.
-Isolation: Transactions do not interfere with each other.
-Durability: Committed changes persist even after failures.
What are some transactional problems?
Lost updates: Two transactions update the same data, leading to a loss of one update.
Temporary updates: One transaction reads an uncommitted update.
Nonrepeatable reads: A transaction reads the same data twice but gets different values.
What are the database isolation levels in PostgreSQL?
SERIALIZABLE
REPEATABLE READ
READ COMMITTED (default in PostgreSQL)
What is JDBC?
JDBC (Java Database Connectivity) is a Java API that allows interaction with databases.
What are the steps in a JDBC interaction?
1) Load the driver class.
2) Establish a connection.
3) Execute queries.
4) Process results.
5) Close the connection.
Give an example of a JDBC connection string.
jdbc:postgresql://localhost:5432/company (for PostgreSQL)
What is a PreparedStatement in JDBC?
A precompiled SQL statement that allows parameterized queries, improving performance and security.
What is Object-Relational Mapping (ORM)?
ORM maps database tables to Java objects, making database interaction more object-oriented.
What is JPA (Java Persistence API)?
JPA is a specification that automates ORM, allowing Java objects to be mapped to database tables using annotations.
How do you define an entity in JPA?
@Entity
class Employee {
@Id private String ssn;
@NotNull private String lname;
private String fname;
}
What are the advantages of using JPA?
Simplifies database interactions.
Reduces boilerplate SQL code.
Supports automatic entity mapping.
What are the common ways to execute queries in JPA?
By ID ( em.find(EntityType, PrimaryKeyValue) ).
Using SQL ( em.createNativeQuery(query, EntityType) ).
Named queries ( em.createNamedQuery(“myQuery”) ).
Criteria API ( query by example ).
What are the different inheritance mapping strategies in JPA?
Joined (table-per-subtype).
Table-Per-Class (table-per-type).
Single-Table (all data in one table).
What is the key takeaway from Transactions, JDBC, and ORM?
Transactions ensure database consistency with ACID properties.
JDBC provides a low-level way to interact with databases using SQL.
JPA simplifies database interactions with ORM and entity mapping.
What is connection pooling in JDBC?
Connection pooling is a technique that reuses database connections instead of opening and closing them repeatedly, improving performance.
What is the difference between Statement and PreparedStatement in JDBC?
Statement: Used for simple queries, created every time.
PreparedStatement: Precompiled and reused, preventing SQL injection and improving performance.
How does JDBC handle transactions?
JDBC transactions can be managed manually by setting auto-commit to false, executing statements, and committing or rolling back as needed.
What is a ResultSet in JDBC?
A ResultSet is a linked list of rows returned from a query, iterated using a cursor ( e.g., rs.next() ).
