xc cx cx cx cx cx cx cx cx cxdsvc xxccx cx xc

ddsvsvsvdsvsdvdsvss. zxcxczzxdcdsvsvsdvsdvsd

1. SQLite Foreign Keys and Constraints Instructor-Led Activities Activity 1: Enabling and Demonstrating Foreign Keys Activity 2: The Constraint Challenge Activity 3: Designing a Relational Schema

2. Activity 1: Enabling and Demonstrating Foreign Keys Objective: Understand importance of foreign key constraints. Outcome: See how foreign keys prevent orphaned records. SQLite requires PRAGMA foreign_keys = ON to enforce foreign key rules.

3. Without Foreign Key Example CREATE TABLE Authors ( AuthorID INTEGER PRIMARY KEY, Name TEXT); CREATE TABLE Books ( BookID INTEGER PRIMARY KEY, Title TEXT, AuthorID INTEGER); INSERT INTO Authors (Name) VALUES ('J.K. Rowling'); INSERT INTO Books (Title, AuthorID ) VALUES ('Harry Potter', 1); INSERT INTO Books (Title, AuthorID ) VALUES ('Unknown Book', 99); -- Works!

4. With Foreign Key Constraint PRAGMA foreign_keys = ON; CREATE TABLE Authors ( AuthorID INTEGER PRIMARY KEY, Name TEXT); CREATE TABLE Books ( BookID INTEGER PRIMARY KEY, Title TEXT, AuthorID INTEGER, FOREIGN KEY ( AuthorID ) REFERENCES Authors( AuthorID )); INSERT INTO Books (Title, AuthorID ) VALUES ('Orphan Book', 99); -- ERROR

5. Activity 2: The Constraint Challenge Objective: Practice column constraints. Outcome: Understand NOT NULL, UNIQUE, DEFAULT, and CHECK. CREATE TABLE Users ( UserID INTEGER PRIMARY KEY, Username TEXT NOT NULL UNIQUE, Password TEXT NOT NULL, AccountStatus TEXT DEFAULT 'Active', Age INTEGER CHECK(Age >= 18) );

6. Testing Constraint Violations INSERT INTO Users (Username, Password, Age) VALUES (NULL, 'test', 22); -- NOT NULL fail INSERT INTO Users (Username, Password, Age) VALUES ('JohnDoe', 'xyz', 30); -- UNIQUE fail INSERT INTO Users (Username, Password, Age) VALUES ('Jane', NULL, 19); -- NOT NULL fail INSERT INTO Users (Username, Password, Age) VALUES ('Mark', 'pw', 15); -- CHECK fail

7. Activity 3: Designing a Relational Schema Objective: Create a normalized schema for a music library. Outcome: Proper use of primary and foreign keys. Artists → Albums → Tracks (1-to-many relationships) Each table connected with foreign key constraints.

8. Example Schema CREATE TABLE Artists ( ArtistID INTEGER PRIMARY KEY, Name TEXT NOT NULL ); CREATE TABLE Albums ( AlbumID INTEGER PRIMARY KEY, Title TEXT NOT NULL, ArtistID INTEGER, FOREIGN KEY ( ArtistID ) REFERENCES Artists( ArtistID ) ); CREATE TABLE Tracks ( TrackID INTEGER PRIMARY KEY, Title TEXT NOT NULL, AlbumID INTEGER, Duration INTEGER, FOREIGN KEY ( AlbumID ) REFERENCES Albums( AlbumID ) );