Use LEFT and RIGHT arrow keys to navigate between flashcards;
Use UP and DOWN arrow keys to flip the card;
H to show hint;
A reads text to speech;
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 |
