1.
Extended Database Schema
Authors Table
CREATE TABLE Authors (
AuthorID INT PRIMARY KEY,
Name VARCHAR(100),
Nationality VARCHAR(50),
BirthYear INT
);
Books Table (linked to Authors)
sql
Copy code
CREATE TABLE Books (
ISBN VARCHAR(13) PRIMARY KEY,
Title VARCHAR(150),
AuthorID INT,
Genre VARCHAR(50),
Quantity INT,
FOREIGN KEY (AuthorID) REFERENCES Authors(AuthorID)
);
Members Table
sql
Copy code
CREATE TABLE Members (
MemberID INT PRIMARY KEY,
Name VARCHAR(100),
Email VARCHAR(100),
Phone VARCHAR(20)
);
Loans Table
sql
Copy code
CREATE TABLE Loans (
LoanID INT PRIMARY KEY,
MemberID INT,
ISBN VARCHAR(13),
LoanDate DATE,
ReturnDate DATE,
FOREIGN KEY (MemberID) REFERENCES Members(MemberID),
FOREIGN KEY (ISBN) REFERENCES Books(ISBN)
);
2. Populate Tables with Sample Data
Insert Authors (5 records)
INSERT INTO Authors VALUES
(1, 'George Orwell', 'British', 1903),
(2, 'J.K. Rowling', 'British', 1965),
(3, 'Harper Lee', 'American', 1926),
(4, 'Jane Austen', 'British', 1775),
(5, 'Mark Twain', 'American', 1835);
Insert Books (10 records)
sql
Copy code
INSERT INTO Books VALUES
('9780451524935', '1984', 1, 'Dystopian', 5),
('9780451526342', 'Animal Farm', 1, 'Political Satire', 4),
('9780747532743', 'Harry Potter and the Philosopher''s Stone', 2, 'Fantasy', 10),
('9780439064873', 'Harry Potter and the Chamber of Secrets', 2, 'Fantasy', 8),
('9780061120084', 'To Kill a Mockingbird', 3, 'Fiction', 6),
('9780141439518', 'Pride and Prejudice', 4, 'Romance', 7),
('9780141439600', 'Sense and Sensibility', 4, 'Romance', 5),
('9780486280615', 'Adventures of Huckleberry Finn', 5, 'Adventure', 6),
('9780486400778', 'The Adventures of Tom Sawyer', 5, 'Adventure', 6),
('9780141036137', 'Emma', 4, 'Romance', 4);
Insert Members (20 records)
sql
Copy code
INSERT INTO Members VALUES
(1, 'Alice Brown', 'alice@[Link]', '555-1001'),
(2, 'Bob Smith', 'bob@[Link]', '555-1002'),
(3, 'Carol White', 'carol@[Link]', '555-1003'),
(4, 'David Green', 'david@[Link]', '555-1004'),
(5, 'Emma Black', 'emma@[Link]', '555-1005'),
(6, 'Frank Miller', 'frank@[Link]', '555-1006'),
(7, 'Grace Wilson', 'grace@[Link]', '555-1007'),
(8, 'Henry Moore', 'henry@[Link]', '555-1008'),
(9, 'Ivy Taylor', 'ivy@[Link]', '555-1009'),
(10, 'Jack Anderson', 'jack@[Link]', '555-1010'),
(11, 'Karen Thomas', 'karen@[Link]', '555-1011'),
(12, 'Leo Martin', 'leo@[Link]', '555-1012'),
(13, 'Mia Harris', 'mia@[Link]', '555-1013'),
(14, 'Nick Clark', 'nick@[Link]', '555-1014'),
(15, 'Olivia Lewis', 'olivia@[Link]', '555-1015'),
(16, 'Paul Walker', 'paul@[Link]', '555-1016'),
(17, 'Quinn Hall', 'quinn@[Link]', '555-1017'),
(18, 'Rachel Young', 'rachel@[Link]', '555-1018'),
(19, 'Sam King', 'sam@[Link]', '555-1019'),
(20, 'Tina Scott', 'tina@[Link]', '555-1020');
3. SQL Queries
a. Retrieve All Books Written by a Specific Author
(Example: George Orwell)
SELECT [Link]
FROM Books b
JOIN Authors a ON [Link] = [Link]
WHERE [Link] = 'George Orwell';
b. Drop the Newly Created Authors Table
DROP TABLE Authors;
⚠️This will fail if foreign key constraints still exist. You may need to drop constraints or
dependent tables first.
c. Identify Members Who Borrowed a Specific Book
(Example: 1984)
SELECT DISTINCT [Link]
FROM Members m
JOIN Loans l ON [Link] = [Link]
JOIN Books b ON [Link] = [Link]
WHERE [Link] = '1984';
4. Alter Members Table to Add MembershipType
ALTER TABLE Members
ADD MembershipType VARCHAR(20);
(Optional default value)
ALTER TABLE Members
ADD MembershipType VARCHAR(20) DEFAULT 'Standard';
Summary
This extended schema improves normalization by separating author information into its own
table, supports meaningful relationships through foreign keys, and demonstrates practical
SQL operations such as JOIN, ALTER TABLE, and DROP TABLE. The added
MembershipType attribute enhances flexibility by allowing differentiated member services in
the library system.