5 Questions
What is the purpose of the foreign key constraint in a database schema?
The purpose of the foreign key constraint is to enforce referential integrity by ensuring that the values in a column (or a group of columns) of one table match the values in another table's primary key or unique constraint.
What are the primary keys for the 'employee', 'department', 'dept_locations', 'project', and 'works_on' tables?
The primary keys are 'ssn' for 'employee', 'dnumber' for 'department', a composite key of 'dnumber' and 'dlocation' for 'dept_locations', 'pnumber' for 'project', and a composite key of 'essn' and 'pno' for 'works_on'.
What is the purpose of the unique constraint in a database table?
The purpose of the unique constraint is to ensure that all values in a column (or a group of columns) are unique, i.e., no two rows can have the same value(s) for the specified column(s).
Explain the role of the 'mgrssn' and 'mgrstartdate' columns in the 'department' table.
The 'mgrssn' column in the 'department' table serves as a foreign key referencing the 'ssn' column in the 'employee' table, representing the manager's social security number. The 'mgrstartdate' column represents the date when the manager started in the department.
What is the significance of the 'superssn' column in the 'employee' table?
The 'superssn' column in the 'employee' table serves as a foreign key referencing the 'ssn' column in the same table, indicating the social security number of the supervisor for a particular employee.
Study Notes
Database Constraints
- The foreign key constraint ensures that a relationship between two tables is valid, by referencing the primary key of another table.
Primary Keys
- The primary key for the 'employee' table is not specified.
- The primary key for the 'department' table is not specified.
- The primary key for the 'dept_locations' table is not specified.
- The primary key for the 'project' table is not specified.
- The primary key for the 'works_on' table is not specified.
Unique Constraint
- The unique constraint ensures that each value in a column is unique, preventing duplicate values.
Department Table
- The 'mgrssn' column in the 'department' table stores the social security number of the manager.
- The 'mgrstartdate' column in the 'department' table stores the start date of the manager.
Employee Table
- The 'superssn' column in the 'employee' table stores the social security number of the supervisor.
Test your knowledge of SQL-99 schema definition, basic constraints, queries, and views with this quiz based on material from 'Fundamentals of Database Systems' by Elmasri and Navathe.
Make Your Own Quizzes and Flashcards
Convert your notes into interactive study material.
Get started for free
