Study your flashcards anywhere!

Download the official Cram app for free >

  • Shuffle
    Toggle On
    Toggle Off
  • Alphabetize
    Toggle On
    Toggle Off
  • Front First
    Toggle On
    Toggle Off
  • Both Sides
    Toggle On
    Toggle Off
  • Read
    Toggle On
    Toggle Off
Reading...
Front

How to study your flashcards.

Right/Left arrow keys: Navigate between flashcards.right arrow keyleft arrow key

Up/Down arrow keys: Flip the card between the front and back.down keyup key

H key: Show hint (3rd side).h key

A key: Read text to speech.a key

image

Play button

image

Play button

image

Progress

1/14

Click to flip

14 Cards in this Set

  • Front
  • Back

What is a base table? Compare to a view table.

A named table that exists in its own right, as opposed to a view table which is defined in terms of a query on other tables.

Command for defining a new base table?

CREATE TABLE SUPPLIER
(SNUM VARCHAR(5) NOT NULL PRIMARY KEY,
STATUS NUMERIC(5) DEFAULT 20 NOT NULL
CHECK (STATUS > 0))

Attributes followed by constraints. If a CHECK/PRIMARY KEY/ UNIQUE constraint only involves a single column then it can be defined as part of the column, as above.

Ways of constraining values?



- value type


- NOT NULL

- CHECK

- UNIQUE (alternate candidate keys)


- PRIMARY KEY, FOREIGN KEY

Foreign key constraints (3)
- Foreign key values can either be found in the referenced key (which may be in the same table), or part of the foreign key is null.
- # of columns and column types must be the same, names can be different
- if no columns are explicitly mentioned then the primary key is used

What does ON UPDATE/DELETE CASCADE do?

If the delete of a referenced primary key value causes a foreign key constraint violation then the referencing foreign key rows are deleted.




Updates from referenced tables are passed on to referencing tables.

What does ON UPDATE/DELETE RESTRICT do?

Does not allow statements to execute if there is even a temporary violation of constraints.

What can you do with ALTER TABLE? (3)

- Add or remove a column,


- Change the default value,



- Add or remove an integrity constraint

3 conceptual uses for views?

- Logical data independence by letting applications only see relevant tables and not changes to unrelated base tables,


- Security mechanism


- Reduced complexity

Conditions for updating a view? (4)

- The top-level SELECT expression does not specify DISTINCT.
- The top-level SELECT list consists of column references rather than aggregate functions or other general expressions.
- The top-level FROM clause only refers to one table, which is itself a base table or updatable view.
- The table referred to in the top-level FROM clause is not itself referenced in the FROM clauses of nested queries, though it may be referenced in the WHERE clauses of nested queries.

What does CHECK OPTION do?

View updates & insertions are rejected if the modified rows do not satisfy the view definition.

e.g. A view exists for all parts heavier than 15 units. An update is carried out to change a weight from 16 to 12, which results in the exclusion of this row from the view. If WITH CHECK OPTION was included when defining the view, this operation would be prevented.

What does ON UPDATE/DELETE NO ACTION do?

If the update/delete applies to a referenced key then an error is generated and the transaction is rolled back.

How can the timing of integrity constraints be changed? What is the default?

- Constraints are usually checked after execution of each SQL statement. When defining a table, this can be specified by CONSTRAINT ... INITIALLY IMMEDIATE NON DEFERRABLE


- other options at table creation are:


CONSTRAINT ... INITIALLY IMMEDIATE DEFERRABLE


CONSTRAINT ... INITIALLY DEFERRED


- constraints can be set dynamically:


SET CONSTRAINTS [constraint names/ALL] IMMEDIATE/DEFERRED

Why would constraint checking settings be changed?

Constraints may be violated temporarily, and then restored by the end of the transaction.




An example could be 2 base tables that each have a foreign key referencing a candidate key of the other.

Give an example of granting and revoking privileges.


What statement can be included to give a user the capability to grant other users privileges?

GRANT [SELECT/INSERT/UPDATE/DELETE] ON TABLE [table name] TO [users/roles]




REVOKE [SELECT/INSERT/UPDATE/DELETE] ON TABLE [table name] FROM [users/roles]




WITH GRANT OPTION