Code:
-- Create the database
CREATE DATABASE HistoricalArchive;
USE HistoricalArchive;
-- Table for Historical Figures
CREATE TABLE HistoricalFigures (
HistoricalFigureID INT PRIMARY KEY AUTO_INCREMENT,
Name VARCHAR(255) NOT NULL,
BirthYear INT,
DeathYear INT,
Role VARCHAR(255)
);
-- Table for Events
CREATE TABLE Events (
EventID INT PRIMARY KEY AUTO_INCREMENT,
Name VARCHAR(255) NOT NULL,
EventDate DATE,
Location VARCHAR(255),
Description TEXT
);
-- Table for Categories (Classifying Artifacts)
CREATE TABLE Categories (
CategoryID INT PRIMARY KEY AUTO_INCREMENT,
CategoryName VARCHAR(255) NOT NULL
);
-- Table for Locations (Where Artifacts are Stored)
CREATE TABLE Locations (
LocationID INT PRIMARY KEY AUTO_INCREMENT,
LocationName VARCHAR(255) NOT NULL,
Country VARCHAR(255)
);
-- Table for Artifacts
CREATE TABLE Artifacts (
ArtifactID INT PRIMARY KEY AUTO_INCREMENT,
Name VARCHAR(255) NOT NULL,
Description TEXT,
OriginDate DATE,
HistoricalFigureID INT,
EventID INT,
CategoryID INT,
LocationID INT,
FOREIGN KEY (HistoricalFigureID) REFERENCES HistoricalFigures(HistoricalFigureID)
ON DELETE SET NULL,
FOREIGN KEY (EventID) REFERENCES Events(EventID) ON DELETE SET NULL,
FOREIGN KEY (CategoryID) REFERENCES Categories(CategoryID) ON DELETE SET
NULL,
FOREIGN KEY (LocationID) REFERENCES Locations(LocationID) ON DELETE SET NULL
);
-- Table for Ownership History (Who owned the artifact and when)
CREATE TABLE OwnershipHistory (
OwnershipID INT PRIMARY KEY AUTO_INCREMENT,
ArtifactID INT,
OwnerName VARCHAR(255),
OwnershipStartDate DATE,
OwnershipEndDate DATE,
FOREIGN KEY (ArtifactID) REFERENCES Artifacts(ArtifactID) ON DELETE CASCADE
);
-- Table for References (Sources & Documentation for Artifacts and Events)
CREATE TABLE References (
ReferenceID INT PRIMARY KEY AUTO_INCREMENT,
ArtifactID INT,
EventID INT,
Source TEXT,
FOREIGN KEY (ArtifactID) REFERENCES Artifacts(ArtifactID) ON DELETE CASCADE,
FOREIGN KEY (EventID) REFERENCES Events(EventID) ON DELETE CASCADE
);
-- Table for Contributors (Historians, Researchers, or Museums)
CREATE TABLE Contributors (
ContributorID INT PRIMARY KEY AUTO_INCREMENT,
Name VARCHAR(255) NOT NULL,
Institution VARCHAR(255)
);
-- Insert sample data into Categories
INSERT INTO Categories (CategoryName) VALUES ('Painting'), ('Sculpture'), ('Weapon');
-- Insert sample data into Locations
INSERT INTO Locations (LocationName, Country) VALUES ('Louvre Museum', 'France'),
('British Museum', 'UK'), ('Smithsonian', 'USA');
-- Insert sample data into HistoricalFigures
INSERT INTO HistoricalFigures (Name, BirthYear, DeathYear, Role) VALUES
('Leonardo da Vinci', 1452, 1519, 'Artist & Inventor'),
('Cleopatra VII', -69, -30, 'Queen of Egypt'),
('Isaac Newton', 1643, 1727, 'Physicist');
-- Insert sample data into Events
INSERT INTO Events (Name, EventDate, Location, Description) VALUES
('Renaissance Era', '1500-01-01', 'Europe', 'A cultural and intellectual movement'),
('Fall of the Ptolemaic Kingdom', '30-08-12', 'Egypt', 'The end of the Ptolemaic rule in Egypt'),
('Publication of Principia Mathematica', '1687-07-05', 'England', 'Isaac Newton publishes his
laws of motion and gravity');
-- Insert sample data into Artifacts
INSERT INTO Artifacts (Name, Description, OriginDate, HistoricalFigureID, EventID,
CategoryID, LocationID) VALUES
('Mona Lisa', 'A famous portrait painting by Leonardo da Vinci', '1503-01-01', 1, 1, 1, 1),
('Cleopatra's Crown', 'A golden crown belonging to Cleopatra VII', '30-01-01', 2, 2, 2, 2),
('Newton's Prism', 'A glass prism used in Newton's optics experiments', '1666-01-01', 3, 3, 3,
3);
-- Insert sample data into OwnershipHistory
INSERT INTO OwnershipHistory (ArtifactID, OwnerName, OwnershipStartDate,
OwnershipEndDate) VALUES
(1, 'King Francis I of France', '1516-01-01', '1547-01-01'),
(2, 'Ptolemaic Dynasty', '30-01-01', '30-08-12'),
(3, 'Isaac Newton', '1666-01-01', '1727-03-20');
-- Insert sample data into References
INSERT INTO References (ArtifactID, EventID, Source) VALUES
(1, 1, 'Louvre Museum Archives'),
(2, 2, 'Egyptian History Records'),
(3, 3, 'Newton's Optics Manuscripts');
-- Insert sample data into Contributors
INSERT INTO Contributors (Name, Institution) VALUES
('Dr. Jane Smith', 'Louvre Museum'),
('Prof. Robert Johnson', 'Oxford University'),
('Dr. Emily Carter', 'Smithsonian Institute');
Sample output: