Podcast
Questions and Answers
What happens to the 'Dept' column in the Employee table if a referenced department in the Department table is deleted?
What happens to the 'Dept' column in the Employee table if a referenced department in the Department table is deleted?
- The employee record is deleted.
- The 'Dept' field is set to null. (correct)
- No action is taken on the employee record.
- The employee's salary is set to zero.
Which of the following SQL statements correctly alters the Department table to add a new column?
Which of the following SQL statements correctly alters the Department table to add a new column?
- alter table Department add (NoOfOffices integer);
- alter Department add NoOfOffices integer;
- add column NoOfOffices integer to Department;
- alter table Department add column NoOfOffices integer; (correct)
What does 'on update cascade' do in the context of foreign key constraints?
What does 'on update cascade' do in the context of foreign key constraints?
- It resets the child foreign key entries to their default value.
- It prevents changes to the referenced key in the parent table.
- It deletes all records in the child table related to the updated parent key.
- It automatically updates the foreign key in the child table when the referenced key is updated. (correct)
Which SQL command is used to remove a table along with any dependent structures?
Which SQL command is used to remove a table along with any dependent structures?
In the given Employee table definition, which columns are part of a unique constraint?
In the given Employee table definition, which columns are part of a unique constraint?
What is the purpose of intra-relational constraints?
What is the purpose of intra-relational constraints?
Which of the following is NOT an example of intra-relational constraint?
Which of the following is NOT an example of intra-relational constraint?
What does the 'unique' constraint do in a database table?
What does the 'unique' constraint do in a database table?
What do foreign keys facilitate in relational databases?
What do foreign keys facilitate in relational databases?
What would happen if a foreign key is violated by deleting a referenced row?
What would happen if a foreign key is violated by deleting a referenced row?
Which reaction policy would you use to prevent updates to a foreign key table when referenced attributes change?
Which reaction policy would you use to prevent updates to a foreign key table when referenced attributes change?
How do you define an inter-relational constraint in SQL?
How do you define an inter-relational constraint in SQL?
Which of the following correctly describes the primary key in a database table?
Which of the following correctly describes the primary key in a database table?
Flashcards are hidden until you start studying
Study Notes
Constraints
- Constraints are conditions that must be verified by every database instance.
- There are two constraint types: Intra-relational and Inter-relational constraints.
Intra-relational Constraints
- These constraints involve a single relation.
- Not Null: Ensures an attribute cannot be null.
- Unique: Defines keys, can be applied to single or multiple attributes.
- Single Attribute:
uniqueis used after the domain definition. - Multiple Attributes:
unique(Attribute {, Attribute})
- Single Attribute:
- Primary Key: Defines the primary key which is unique and cannot be null.
- The syntax is similar to the unique constraint.
- Check: Described later.
Inter-relational Constraints
- These constraints involve multiple relations.
- Check: Described later.
- References and Foreign Key: Define Referential integrity constraints.
- Single Attribute:
referencesis applied after the domain definition. - Multiple Attributes:
foreign key (Attribute {, Attribute }) references ...
- Single Attribute:
- Reaction policies can be associated with violations of Referential integrity.
Reaction Policies for Referential Integrity Constraints
- Reactions apply to the table containing the foreign key after changes to the referenced table.
- Violations can be introduced by:
- Updating referenced attributes.
- Deleting rows in the referenced table.
- Reactions:
- Cascade: Propagates the change to the related tables.
- Set Null: Nullifies the referring attributes.
- Set Default: Assigns the default value to the referring attributes.
- No Action (Restrict): Forbids changes to the external table.
- Reactions depend on the event, syntax:
on < delete | update > < cascade | set null | set default | no action >
Schema Updates
- Two SQL statements are used for schema updates:
- Alter: Used to modify domains, tables, views, and assertions.
- Drop: Used to remove schemas, domains, tables, views, assertions.
- Syntax:
drop < schema | domain | table | view | assertion > ComponentName [ restrict | cascade ]
- Example:
- Alter table Department add column NoOfOffices integer
- Drop table TempTable cascade
Studying That Suits You
Use AI to generate personalized quizzes and flashcards to suit your learning preferences.
