Integrity constraints in DBMS Theory - Mini Project: Build & Apply

Understanding Integrity Constraints in Databases

📖 Scenario: You are designing a simple database for a small library. To keep the data accurate and reliable, you need to apply rules called integrity constraints.These rules help ensure that the data entered into the database follows certain conditions, like no duplicate book IDs or valid publication years.

🎯 Goal: Build a basic understanding of integrity constraints by creating a table with specific rules that keep the library data correct and consistent.

📋 What You'll Learn

Create a table named Books with columns BookID, Title, Author, and YearPublished

Add a primary key constraint on BookID to prevent duplicate entries

Add a check constraint on YearPublished to allow only years from 1500 to the current year

Add a not null constraint on Title and Author to ensure these fields are always filled

💡 Why This Matters

🌍 Real World

Libraries, schools, and businesses use integrity constraints to keep their data trustworthy and consistent.

💼 Career

Database administrators and developers use integrity constraints to design robust databases that prevent errors and maintain data quality.

1

Create the Books table with columns

Write a SQL statement to create a table called Books with columns BookID as integer, Title as text, Author as text, and YearPublished as integer.

DBMS Theory

CREATE TABLE Books (
    BookID INTEGER,
    Title TEXT,
    Author TEXT,
    YearPublished INTEGER
);
-- Your code here

Need a hint?

Use CREATE TABLE followed by the table name and define each column with its data type inside parentheses.

2

Add primary key and not null constraints

Modify the Books table creation to add a primary key constraint on BookID and add NOT NULL constraints on Title and Author columns.

DBMS Theory

CREATE TABLE Books (
    BookID INTEGER PRIMARY KEY,
    Title TEXT NOT NULL,
    Author TEXT NOT NULL,
    YearPublished INTEGER
);
-- Your code here

Need a hint?

Add PRIMARY KEY after BookID INTEGER and NOT NULL after Title TEXT and Author TEXT.

3

Add a check constraint on YearPublished

Add a CHECK constraint to the YearPublished column to allow only years between 1500 and 2024 inclusive.

DBMS Theory

CREATE TABLE Books (
    BookID INTEGER PRIMARY KEY,
    Title TEXT NOT NULL,
    Author TEXT NOT NULL,
    YearPublished INTEGER CHECK (YearPublished >= 1500 AND YearPublished <= 2024)
);
-- Your code here

Need a hint?

Use CHECK (YearPublished >= 1500 AND YearPublished <= 2024) after the YearPublished INTEGER column definition.

4

Complete the Books table with all constraints

Ensure the Books table creation includes the primary key on BookID, NOT NULL on Title and Author, and the CHECK constraint on YearPublished from 1500 to 2024.

DBMS Theory

CREATE TABLE Books (
    BookID INTEGER PRIMARY KEY,
    Title TEXT NOT NULL,
    Author TEXT NOT NULL,
    YearPublished INTEGER CHECK (YearPublished >= 1500 AND YearPublished <= 2024)
);
-- Your code here

Need a hint?

Review all constraints together to make sure the table enforces data integrity.