COP4710 QUIZ 2

Integrity constraints Examples

A checking account must have a balance greater than $10,000.00 A salary of a bank employee must be at least $4.00 an hour A customer must have a (non-null) phone number

What is a schedule?

A sequence of instructions that specify the chronological order in which instructions of concurrent transaction are executed.

What's a transaction

A transaction is a unit of program execution that accesses and possibly updates various data items.

Transaction states and what do they mean

Active: The initial state; the transaction status in this state while it is executing. Partially committed: After the final statement has been executed. Failed: After the discovery that normal execution can no longer proceed. Aborted: After the transaction has been rolled back and the database restored to its state prior to the start of the transation. Two options after it has been aborted: Restart the transaction Kill the transaction Committed: After successful completion.

Why is the shadow-database schemea bad idea?

Assumes that only one transaction is active at a time. Assumes disks do not fail. Useful for text editors but extremely inefficient for large databases Does not handle concurrent transactions

ACID Properties (Be able to name them and explain them)

Atomicity: Transactions are often composed of multiple statements. Consistency: Consistency ensures that a transaction can only bring the database from one valid state to another, maintaining database invariants Isolation: Transactions are often executed concurrently Durability: This usually means that completed transactions (or their effects) are recorded in non-volatile memory.

Advantages of concurrent execution

Concurrent execution: Multiple transactions are allowed to run concurrently in the system. Advantages: Increased processor and disk utilization, leading to better transaction throughput. Reduced average response time for transactions; short transactions need not wait behind long ones.

Difference between date, time and timestamp types

Date: Dates, containing a (4 digit) year, month and date (ie. date '2005-7-27') Time: Time of day, in hours, minutes and seconds. (ie. time '09:00:30') Timestamp: date plus time of day (ie. timestamp '2005-7-27 09:00:30.75')

Referential Integrity-what is it?

Ensures that a value that appears in one relation for a given set of attributes also appears for a certain set of attributes in another relation.

Main issues transactions deal with

Failures of various kinds, such as hardware failures and system crashes Concurrent execution of multiple transactions

Integrity Contraints

Integrity constraints guard against accidental damage to the database, by ensuring that authorized changes to the database do not result in a loss of data consistency.

What is are ODBC and JDBC

ODBC and JDBC are API's which allows for programs to interact with a database server. ODBC (Open Database Connectivity) JDBC (Java Database Connectivity)

Be able to draw theTransaction State graph from slide 9

Partially committed ———>committed /\ | | | | | | | | Active | | | | | | | \/ \/ Failed ————————>aborted

Authorization: Forms of authorization

Read (allows reading, but no modification of data.) Insert (allows insertion of new data, but no modification of existing data.) Update (allows modification, but no deletion of data.) Delete (allows deletion of data.)

Assertion example

Syntax: Create assertion <assertion-name> check <predicate> | \/ Create assertion balance_constraint check ( select sum(balance) from account ) >= 0

Authorization query example

Syntax: grant <privilege list> on <relation name or view name> to <user list> Example: (allows read access to relation tbl_branch for users 1,2, and 3) Grant select on tbl_branch to U1, U2, U3

What is the recovery management component of a database system responsible for?

The recovery-management component of a database system implements the support for atomicity and durability.

Difference between user defined type and user defined domain

Types and domains are similar. Domains can have constraints, such as not null.

Assertions

a predicate expressing a condition that we wish the database always to satisfy.

List integrity constraints (4)

not null (Branch_name char(15) not null) primary key (Id char(15) primary key) unique (email varchar(50) unique) check (P ), where P is a predicate (DOB date check (dob > '1900-01-01'))

Which commands are used to apply referential integrity?

primary key unique key foreign key