Database Application
Exam #1 Practice Exam (with answers)

1.	Implement tables, relationships, and constraints using SQL's data definition language.
 	SQL Code
?	Create database
?	Create table (with domain constraint)
?	Input mask
?	Foreign key
?	Data type
?	Identify field
?	Compound primary key
?	Alter table

?Create database
	Create database MusicDiscounters

?Create table (with domain constraint)
Create tblStates
	(StateCode char(2)
		Primary Key
		Check (StateCode Like '[A-Z,a-z][A-Z,a-z]'),
	StateName varchar(30)
		Not Null,
	TaxRate decimal (2,2)
		Not Null Default 0)

?Input mask
PhoneNumber varchar(12)
	Check (PhoneNumber is Null
		Or PhoneNumber Like
			'[0-9] [0-9] [0-9]- [0-9] [0-9] [0-9]- [0-9] [0-9] [0-9] [0-9]'
		or PhoneNumber Like
		'[0-9] [0-9] [0-9]- [0-9] [0-9] [0-9] [0-9]'))

?Foreign key
ProductID varchar(5)
	References tblInventory(ProductID),

?Data type
StateCode char(2)
StateCode = field name
Char = data type
2 = field length

?Identity field
OrderNumber integer
	Identity(100,1) Primary Key,

?Compound Primary Key
Constraint PKOD Primary Key (OrderNumber, ProductID)
*Note:  You don't specify a primary key anywhere else.

?Alter table
	Alter Table tblOrderDetails Drop Column Extension


2.	Discuss the notion of a tiered architecture. Explain the advantages and disadvantages of 
single-tier, 2-tier, and n-tier client server architectures. Discuss how the various parts of 
an application get distributed among the tiers.
There are three parts of a database:
?	GUI (or user front-end) ("user interface")
?	Application Logic ("business logic") ("process")
?	Data management functions ("shared data") ("database")

A 2-tier database stores the three parts of the database application in two different places.
An n-tier database stores the three different parts of the database application in more than two 
different places.

In a 2-tier system, the application logic is buried either inside the user interface on the client or 
within the database on the server (or both).

In a 3-tier system, the application logic (or process) lives in the middle tier.  It is separated from 
the data and the user interface.

In an n-tier system, the middle layer (application logic) is implemented as a collection of 
components.

2-tier advantages
?	Simple
?	Easy to develop.  Good for creating application using visual building tools
?	We want to be able to keep the user out of any part of the application they don't need so 
they don't screw anything up.  Anything that's 2-tier or above is better because we can do 
this.

3-tier advantages
?	Easier to manage and deploy on the network.
?	Meets the requirements of large-scale Internet and intranet client/server applications.
?	More scalable, more robust, more flexible
?	Can integrate data from multiple places
?	Minimize network interchanges by creating abstract levels of service
?	Substitutes a few server calls for many SQL queries and updates, so it performs better 
than a 2-tier
?	Provides better security by not exposing the DB schema to the client and by enabling more 
fine-grained authorization on the server

n-tier advantages
?	There are no scalability limits in this architectural model
?	You can develop big applications in small steps
?	Applications can reuse components
?	Clients can access data and functions easily and safely
?	Custom applications can incorporate off-the-shelf components
?	Component environments don't get older – only better
 
3.	Discuss the need for integrity and domain constraints. Explain and give examples of how 
constraints are implemented.

Integrity constraints ensure referential integrity and entity integrity.  Referential integrity 
ensures that all foreign keys point to valid existing primary keys in other tables.  Entity integrity 
mainly ensures that each row of a table is unique.

Domain constraints ensure that domain rules are followed – such as input masks and business 
rules (like that you can have only 9 digits in a social security number, for example).
 
4.	Explain the effects of cascade, restrict, and nullify delete and update operations.
1.	Restrict delete/update -- just says you can't do it
2.	Cascade delete/update – says if you delete/update a primary key, delete/update all the 
related foreign keys
3.	Nullify delete – if you delete a primary key, make the foreign keys null.  In some cases, this 
will work – like if a sales rep leaves the company.  The customer's sales rep can become null.  
This doesn't work very often.

5.	Discuss the need for transactions and give implementation examples.
A transaction is a sequence of operations performed as a single logical unit of work.  This logical 
unit of work must exhibit the four properties:
Atomicity – All or nothing.
Consistency – A transaction must leave data in a consistent state upon completion.  All rules 
must be applied, and all internal data structures must be correct at the end of the transaction.
Isolation – Modifications made by concurrent users must be isolated from modifications being 
made by other concurrent users.
Durability – Changes are permanent after a transaction is complete.

 	Transactions are important because it's important to maintain the integrity of the database.  For 
example, if you were to try to add an order to the system, it's important that either all the steps 
happen – or none of them.  For example, if you were trying to enter an order in the system for an 
item you have in stock, it's important to update the inventory table when you add to the order 
table.  At the same time, if something goes wrong during the process, it's important to NOT 
update the inventory table if the order doesn't ever get completely entered into the system.

Implementation examples:  Insert, Update, Delete

6.	Discuss locking and deadlocks.

Deadlock is a condition that can occur during concurrent processing in which each of two (or 
more) transactions is waiting to access data that the other transaction has locked. 

Locking prevents multiple applications from obtaining copies of the same record when the record 
is about to be changed.  It's a remedy for the inconsistencies caused by concurrent processing.

Locks placed by the DBMS are called implicit locks.
Those placed by commands are called explicit locks.

The size of a lock is referred to as its lock granularity.  Locks can be applied at different levels.  
They can be applied at the row level, the page level, at the table level, or the database level.
?	RID – Row identifier.  Used to lock a single row within a table.
?	Key – Row lick within an index.  Used to protect key ranges in serializable transactions.
?	Page – 8 kilobyte (8 KB) data page or index page.
?	Extent – Contiguous group of eight data pages or index pages.
?	Table – Entire table, including all data and indexes.
?	DB – Database.

Locks with large granularity are easy for the DBMS to administer but frequently cause conflicts.
Locks with small granularity are difficult to administer, but conflicts are less common.

There are two locking strategies:  pessimistic and optimistic.

Pessimistic locking takes place as soon as a record is accessed.
Optimistic locking takes place right before the modifications are about to be written to disk.

Exclusive lock locks the item from access of any kind (read, write, execute). 
Shared lock locks the item from change only (but not from being read).

	SQL uses these resource lock modes:
	Shared (S) – Used for operations that do not change or update data (read-only operations), such 
as SELECT statement.
Update (U) – Used on resources that can be updated.  Prevents a common form of deadlock that 
occurs when multiple sessions are reading, locking, and potentially updated resources later.
Exclusive (X) – Used for data-modification operations, such as INSERT, UPDATE, or DELETE.  
Ensures that multiple updates cannot be made to the same resource at the same time.
Intent – Used to establish a lock hierarchy.  The types of intent locks are:  intent shared (IS), 
intent exclusive (IX), and shared with intent exclusive (SIX).
Schema – Used when an operation dependent on the schema of a table is executing.  The types 
of schema locks are:  schema modification (Sch-M) and schema stability (Sch-S).
Bulk Update (BU) – Used when bulk-copying into a table and the TABLOCK hint is specified.

7.	Cursors
Cursor is what allows you to move through a recordset.  
Cursor types:
-	Forward Only – Application can only mmove forward through the recordset.  Changes made 
by other cursors in this transaction or in other transactions will be visible only if they occur on 
rows ahead of the cursor.
-	Static –Application sees the data as  it was at the time the cursor was opened.  Changes 
made by this cursor are visible.  Changes from other sources are not visible.  Backward and 
forward scrolling are allowed.
-	Keyset – When the cursor is opened, aa primary key value is saved for each row in the 
recordset.  When the application accesses a row, the key is used to fetch the current values 
for the row.  Updates from any source are visible.  Inserts from sources outside this cursor 
are not visible (there is no key for them in the keyset).  Inserts from this cursor appear at the 
bottom of the recordset.  Deletions from any source are visible.  Changes in row order are not 
visible.  If the isolation level is dirty read, then uncommitted updates and deletions are visible; 
otherwise only committed updates and deletions are visible.
-	Dynamic – I'm always checking back wiith the DB to see if anything has been changed.  
Changes of any type and from any source are visible.  All inserts, updates, deletions, and 
changes in recordset order are visible.  If the isolation level is dirty read, then uncommitted 
changes are visible.  Otherwise, only committed changes are visible.

Exam 1 – Wednesday, Oct 31