CSc 460 - Exam 2

RAID Level 3

Byte-interleaved Parity (n data, 1 check disk) - Uses a striping unit of 1 byte - Can recover from a disk failure - Parity disk is a bottleneck

Create a domain for the Room table that restricts the type to Single, Double, or Family.

CREATE DOMAIN RoomType AS CHAR(1) CHECK(VALUE IN ('S', 'F', 'D'));

What is "vacuuming" a database?

Removes leftover space from past DELETE operations.

Definition: Primary Key

The selected Candidate Key

What is rule-based optimization?

"Rules of Thumb". A form of query optimization where the query plan is set by a predetermined set of rules.

How do you prevent a SQL injection attack?

- Sanity-check all user input - trim excess characters - accept raw input only when necessary

What are the advantages of maintaining a materialized view rather than using the view resolution process?

- may be faster than trying to perform view resolution. - may also be useful for integrity checking and query optimization

What are the three conditions to determine if a set is a minimal set?

1. All FD's have 1 attribute on their RHS 2. No FD's can be removed from the LHS without changing it's cover 3. All FD's in the set are needed to retain the closure

Disadvantages of DBMSes

1. DB Design is complex 2. Cost 3. Availability (can be a single point of failure) 4. Speed vs. Flexibility

Why use a DBMS?

1. Data Sharing 2. Redundancy Control 3. Centralized control 4. Data Integrity 5. Data Security 6. Views 7. Data Independence

Definition: B-Tree of Order M (5 parts)

1. Each node contains at most 2M keys (so at most 2M+1 pointers) 2. Each node (except the root) must contain at least M keys 3. A non-leaf root node has at least 2 children 4. All leaf nodes are at the same level 5. A non-leaf node with n keys has exactly n+1 children

Disadvantages of Triggers

1. Hard to write appropriate actions 2. Specified separately from relation so they are easy to forget about 3. Can reduce DBMS' concurrency 4. Hard to see how triggers interact 5. Rule termination - triggers can trigger each other indefinitely

Why are Functional Dependencies important?

1. Helps place attributes into relations 2. Foundation of most of the common forms of data normalization 3. Helps ID candidate keys

Minimal Cover Algorithm

1. Minimize the RHS of each FD 2. Minimize the LHS of each FD 3. Remove redundant FD's

DBMS Components

1. The Database 2. Database Administrator 3. Application programs (JDBC) 4. Hardware

What is a closure of a set of attributes?

A closure of A is the set of attributes A functionally determines. So, if A -> {B, C, D} A+ = {A, B, C, D}

What is SQL injection?

A common DBMS attack where a user tries to add (inject) SQL into an incomplete query to get the DBMS to reveal something about its underlying schema.

What is cost-based optimization?

A form of query optimization that builds its query plan by estimating the expense of executing each operator/algorithm. To do this, it uses knowledge of cardinalities and selectivity for each query and asses the expense in terms of CPU, memory, disk, etc.

Definition: Candidate Key

A key able to uniquely identify a record

Definition: 2nd Normal Form

A relation R is in 2NF if every non-prime attribute is FFD upon every CK of R.

Definition: Third Normal Form

A relation R is in 3NF if for every non trivial FD X->A that holds in R either: A) X is a superkey of R or B) A is a prime attribute of R

Definition: Boyce-Codd Normal Form

A relation R is in BCNF if, for every non-trivial FD x->A in R, X is a superkey of R. (Same as 3NF but without B)

Definition: First Normal Form

A relation is in 1NF if its attributes are not set-valued.

What is a Functional Determination?

A set X functionally determines Y if whenever any two tuples of a relation agree on their X values, they must agree on their Y values.

Definition of Database

A shared collection of logically related data and its description, design to meet the information needs of an organization.

What is the goal of a distributed DBMS?

A single server can be a single point of failure, so spreading the load to multiple servers can reduce the chance of data loss.

Definition of Database Management System

A software system that enables users to define, create, maintain, and control access to the database.

Definition: Superkey

A superkey is a set of attributes that includes a CK.

B+-Tree - advantages and disadvantages over B-Trees

Advantages: - Built-in disk pointers for keys in leaf nodes - Supports 2 kids of queries: exact-match and range Disadvantages: - "Waste" of upper-level node storage - Insertions/deletions are more complex

Parity Bits - advantages and disadvantages

Advantages: Can recover from a drive failure Disadvantages: Only 1 drive failure, Cost - 1 extra drive

Disk Striping - advantages and disadvantages

Advantages: Speed (parallelism) Disadvantages: Cost, probability of disk failure (all files corrupt), no data replication

Definition: Trivial Functional Dependency

An FD X->A is a trivial FD when A is an element of X. Example: {S#, P#} -> S# is trivial.

Definition: Full Functional Dependency

An FD X->Y is a FFD if removing any attribute from X destroys the dependency.

Definition: Prime Attribute

An attribute that is a member of any CK of the relation is a prime attribute. Otherwise, not prime or non-prime.

What are the ACID properties of Transactions?

Atomicity: transactions are all or nothing Consistency: a transactions actions retain the validity of the database Isolation: your transaction appears to be the only one running Durability: A completed transaction's changes are permanent

RAID Level 5

Block-interleaved Distributed Parity - Striping unit is 1 block - Parity blocks are spread across all disks (n + 1 disks) - Controller design is fairly complex

Create a database trigger for the following situation: The price of all double rooms must be greater than $100.

CREATE TRIGGER DouleRoomPrice BEFORE INSERT ON Room FOR EACH ROW WHEN (new.type = 'D' AND new.price < 100) BEGIN raise_application_error(-20000, 'Price for double room must be over 100); END;

Discretionary Access Control features of SQL

CREATE USER - To add limits on access for certain user, insert password for user, capabilities of that user GRANT <priviledge> [ON <object>] TO <user> - can also revoke privledges

Describe service-oriented architecture.

Client locates and uses a service from the service registry. Goal is to be flexible with the adoption of new business processes.

Mandatory Access Controls

DBMS has default security procedures that must be followed. A hierarchy of security classes: Top Secret > Secret > Classified > Unclassified

Two categories of access controls

Discretionary and Mandatory

Disk Mirroring - advantages and disadvantages

Disk Mirroring: 1 write writes the same data to two disks Advantages: Can keep working with a failed drive, read in parallel Disadvantages: Expense of 2x the drives

"ECA" model of a trigger

Event - causes trigger activation Condition - if true, action will be performed Action - SQL statements to be executed

What are the two restrictions enforced on MAC security classes?

For a subject (S) and an object (O): 1. Simple Security Property S can read O only when class(S) >= class(O) 2. Star Property S can write O only when class(S) <= class(O)

Describe the Nested-Loops Join algorithm and its pros and cons.

For each tuple of r: For each tuple of s: output tuples that match on join attribute pro: works well when inner relation fits in RAM con: repeated scans of inner relation

How can you tell if two FD's are equal?

G and H are equal if G+ = H+.

What is a cover of a set of FD's?

G covers H if every FD in H is also in G+.

Describe the Hash Join algorithm and its pros and cons.

Idea: pre-group tuples by join attr.values Hash each relation using the same hash function on the join attrs For each of the M corresponding pairs of buckets: Build an in-memory hash index on the contents of the first bucket in the pair. For each tuple in the second bucket: Probe the index with its join attribute For each matching tuple found: Ouput the marriage pros: easy to parallelize the process because the buckets are processed in pairs cons: lot of hashing

In the ANSI/SPARC Architecture, what do the level-to-level mappings provide, and why does a DBMS need that?

It provides data abstraction so the low-level schema is not revealed to the user. A DBMS needs this to ensure confidentiality in the DB.

How do you ensure Integrity in a DB?

Maintain off-site data backups, log DBMS actions, use a non-zero RAID level to ensure data replication

Describe the Sort-Merge Join algorithm and its pros and cons.

Make the first tuple of relation r the current tuple. Loop until one of the relations is exhausted: Make a set of all tuples from relation r that have the same join values as the current tuple. For each tuple from relation s that has a join match: Output marriages of it to all set members The next tuple of relation r that is not a member of the set becomes the new current tuple pros: each element is only read once cons: both relations must be able to be sorted on the join attribute

What is a materialized view?

Materialized view is a temporary table that is stored in the database to represent a view, which is maintained as the base table(s) are updated.

RAID Level 1

Mirrored (n data disk + n mirror disks) - Optimal for parallel reading - Cost is an issue (2x disks)

RAID Level 0

Non-redundant (n data disks) - Striping only - Good performance - No data redundance

Definition: Normalization

Normalization is the process of decomposing relations into relations of lower degree that no longer possess certain undesirable properties.

What is Integrity in a DB?

Prevent data corruption and/or loss. Be able to recover DBs after accident or disaster.

How do you ensure Confidentiality in a DB?

Quality passwords, 2-factor auth, data encryption (ex. public-key encryption, digital signatures)

List the bookings for which no dateTo has been specified. (SQL)

SELECT * FROM Booking WHERE dateTo IS NULL;

What is the average price of a room? (SQL)

SELECT AVG(price) FROM Room;

What is the lost income from unoccupied rooms at the Grosvenor Hotel?

SELECT SUM(price) FROM Room r WHERE roomNo NOT IN (SELECT roomNo FROM Booking b, Hotel h WHERE (dateFrom <= CURRENT_DATE AND dateTo >= CURRENT_DATE) AND b.hotelNo = h.hotelNo AND hotelName = 'Grosvenor Hotel');

List the number of rooms in each hotel in London

SELECT hotelNo, COUNT(roomNo) AS count FROM Room r, Hotel h WHERE r.hotelNo = h.hotelNo AND city = 'London' GROUP BY hotelNo;

What is Confidentiality in a DB?

Security. Preserve secrecy of the DB.

Definition: Blocking Factor

The # of whole records a block can store. bf = Floor(block size/record size)

What is a closure of a set of Functional Dependencies?

The closure of a set of FD's is the set of FD's that are implied by G, including those of G itself.

What is a closure of a set of Functional Dependencies?

The closure of a set of FD's is the set of FD's that are implied by H, including those of G itself.

Describe the difference between the two-tier and three-tier Client-Server Architecture.

The three-tier architecture adds an extra server. This distributes the capabilities among multiple servers and maps very well to the web. The browser acts as a thin client and web server acts as the application server. The DB is the third layer.

Definition: Union Compatible

Two relations that have the same degree and the same domains on corresponding pairs of attributes are union compatible.

Definition: Internal Fragmentation

Unallocatable storage in a block

What two groups are MAC security classes applied to?

Users (subjects): eg. users, accounts Objects: eg. tables, indices, triggers

Discretionary Access Controls

Views are a basic form of DAC. They give users access to necessary info while hiding the origins of the values "security by obscurity"

Subjects have ____, Objects have _____. (Mandatory Access Controls)

clearances, classifications

DRC: List the names and addresses of all employees who are managers.

{ fName, lName, address | Employee(empNo, fName, lName, address, DOB, sex, position, deptNo) ^ (position='manager')}

Domain Relational Calculus: List all single rooms with a price below $20 a night.

{roomNo, hotelNo, type, price | (Room(roomNo, hotelNo, type, price) ^ type = 'S' ^ price < 20)}