SQL Server Database Creation, Constraints, and Referential Integrity Demonstration

This project illustrates the process of creating a relational database in SQL Server, defining data integrity rules, and testing referential integrity constraints through various insert, update, and delete operations.

The script covers:

Database and Table Creation

Creation of the ITI002 database.

Creation of the Department table with a primary key, along with a custom rule (LocationRule) bound to restrict location values to specific options (NY, DS, KW).

Creation of the Employee table with primary key, NOT NULL constraints, UNIQUE constraint on salary, and a foreign key reference to the Department table.

Binding a SalaryRule to ensure salaries remain below 6000.

Data Insertion

Insertion of sample records into both the Department and Employee tables.

Referential Integrity Tests

Attempting to insert into the Works_On table with a non-existent employee ID (demonstrates foreign key violation).

Attempting to update an employee number in Works_On to a non-existent ID (foreign key violation).

Attempting to modify an employee ID in the Employee table when it’s referenced in Works_On (update restriction without cascade).

Attempting to delete an employee record that is referenced in Works_On (delete restriction without cascade).

Table Structure Modification

Adding a TelephoneNumber column to the Employee table.

Dropping the TelephoneNumber column from the Employee table.

ER Diagram

Includes instructions for building a diagram to visualize relationships between tables, highlighting primary key–foreign key connections.

This project serves as a practical demonstration of database creation, constraint application, and referential integrity enforcement in SQL Server.