Cmpt 354 - Database Creation

Front 1

Name five types of constraints.

Back 1

1. Primary Key

2. Foreign Key

3. General table constraints

4. Domain constraints.

5. Assertions

Front 2

Name two characteristics about primary key constraints.

Back 2

1. The attributes of the primary key cannot be null.

2. The primary key must be unique.

Front 3

Name two ways to create a primary key constraint.

Back 3

1. CREATE or ALTER TABLE statement

eg. PRIMARY KEY

2. Selecting the attribute(s) and selecting primary key in the context menu.

Front 4

What is a foreign key constraint?

Back 4

Table R contains a FK on attr {a} that ref. table S.

1. Values for {a} in R must also exist in S except that if {a} is not part of the primary key of R it may be null.

2. There may be values for {a} in S that are not in R.

Front 5

How do you specify actions for foreign keys?

Back 5

branchName CHAR(20) DEFAULT 'Granville'

FOREIGN KEY (branchName) REFERENCES Branch

ON DELETE SET DEFAULT

ON UPDATE CASCADE

Front 6

What happens in a cascading deletion or update if a violation is thrown?

Back 6

The entire transaction is aborted.

Front 7

What are the four actions possible for updates and deletions?

Back 7

1. NO ACTION

2. CASCADE

3. SET NULL

4. SET DEFAULT

Front 8

How can you reference non-primary keys in another table?

Back 8

Specify a list of attributes as UNIQUE (candidate key).

Front 9

What is a general table constraint?

How is it defined?

Back 9

A constraint over a single table.

Defined by CHECK keyword.

Front 10

What is the limitation for SQL server check constraints?

Back 10

They are limited to scalar (single-valued) expressions.

Front 11

What is a UDF? What is their purpose?

Back 11

User Defined Function.

CHECK constraints can run against them (if they return a scalar value).

Front 12

Name three benefits of UDFs.

Back 12

1. Modular programming.

2. Faster execution.

3. Reduced network traffic.

Front 13

Write a UDF.

Back 13

CREATE FUNCTION name()

RETURNS int

AS

BEGIN

DECLARE @result int

....

RETURN @result

END

Front 14

What statement is used to create a new domain?

Back 14

CREATE DOMAIN.

Front 15

What four things are required for a domain?

Back 15

1. Name

2. Base Type

3. A restriction (CHECK statement)

4. Default Optional Value

Front 16

Are domains attached to individual table schemata?

Back 16

No. They are part of the DB schema however.

Front 17

What is this domain?

CREATE DOMAIN minorAge INTEGER DEFAULT 10

CHECK (VALUE > 0 AND VALUE <=18)

Back 17

A domain for minors, who have ages between 0 and 18.

Front 18

Can distinct types be compared?

Back 18

No. Types are distinct so values of different types cannot be compared.

Front 19

Write a create type statement 'ID' as an integer.

Back 19

CREATE TYPE IS AS INTEGER

Front 20

Name 8 useful base types.

Back 20

1. bigint

2. bit

3. date

4. char(n)

5. float

6. real

7. int

8. nvarchar(n | max)

Front 21

What is an assertion?

Back 21

A table constraint that apply to only one table.

Front 22

When are assertions tested?

Back 22

Whenever the DB is updated.

Front 23

Does Transact-SQL implement assertions?

Back 23

No.

Front 24

CHECK ASSERTION assetCoverage

CHECK (NOT EXISTS

SELECT *

FROM Branch B

WHERE assets <

(SELECT SUM(A.balance)

FROM Account A

WHERE A.branchName = B.branchName)))

Back 24

Check that a branch's assets are greater than the total account balances held in the branch.

Front 25

What kind of constraints can't be modeled with table constraints or assertions?

Back 25

Participation constraints.

Front 26

What is a trigger?

Back 26

A procedure that is invoked by the DBMS as a response to a specified change.

Front 27

What is different, in general, in triggers vs constraints?

Back 27

Constraints only reject transactions, triggers carry out actions.

Front 28

What are the three components to a trigger?

Back 28

1. Event

2. Condition

3. Action

Front 29

CREATE TRIGGER reminder1

ON Sales.Customer

AFTER INSERT, UPDATE

AS RAISERROR('Notify Customer Relations', 16, 10)

Back 29

Write a message when a customer is added or updated.

Front 30

What is the general SQL syntax for a trigger?

Back 30

CREATE TRIGGER trigger_name

ON {table | view}

{ FOR | AFTER | INSTEAD OF } { INSERT, UPDATE, DELETE }

AS {sql_statement}

Front 31

What is the difference between AFTER and INSTEAD OF for triggers?

Back 31

AFTER executes after the transaction, INSTEAD OF replaces the transaction.

Front 32

What is the order for the AFTER trigger?

Back 32

1. Enforce constraints

2. Enforce foreign key constraints

3. Create inserted and deleted tables

4. Execute the triggering statement

5. Execute after trigger

Front 33

What is the inserted table? Deleted table?

Back 33

Inserted table contains records that were inserted by a transaction.

Deleted table contains records that were deleted by a transaction.

Front 34

What is a drawback of triggers?

Back 34

They carry overhead. PKs, FKs and simple CHECK constraints should be used where possible

Front 35

What is an index?

Back 35

An index is a data structure that provides efficient access to records.

Front 36

What is a table scan?

Back 36

An inefficient lookup of table records.

Front 37

Typically OLTP indexes are variations on which two data structures?

Back 37

1. Hash tables

2. (Binary) search trees

Front 38

What is a hash index? What is a drawback?

Back 38

A hash table where the hash function maps attribute values to record IDs.

They cannot be used for range searches.

Front 39

What is a tree index? In practice, what data type is used?

Back 39

Directs searches in much the same way as a binary search tree.

B-trees (n-ary) trees.

Front 40

How is n derived in a b-tree?

Back 40

The size of the attribute values, record IDs and disk pages.

Front 41

What are the benefit / drawback of tree indexes?

Back 41

1. Supports range searches

2. Not as fast as hash indexes

Front 42

What is a clustered index? How many are allowed?

Back 42

A clustered index is one where the underlying data file is sorted on the index search key.

One.

Front 43

What is the benefit of indexes? Drawback?

Back 43

1. Increase the efficiency of operations requiring look-up of attribute values.

2. Index management.