SQL Assignment - Databases, Tables,
Relationships, and Data
Step 1: Create the Databases
CREATE DATABASE SchoolDB;
CREATE DATABASE HospitalDB;
CREATE DATABASE LibraryDB;
Step 2: Create Tables (4 per database)
1. SchoolDB
USE SchoolDB;
CREATE TABLE Students (
StudentID INT PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50),
Age INT
);
CREATE TABLE Teachers (
TeacherID INT PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50),
Subject VARCHAR(50)
);
CREATE TABLE Courses (
CourseID INT PRIMARY KEY,
CourseName VARCHAR(50),
TeacherID INT,
FOREIGN KEY (TeacherID) REFERENCES Teachers(TeacherID)
);
CREATE TABLE Enrollments (
EnrollmentID INT PRIMARY KEY,
StudentID INT,
CourseID INT,
FOREIGN KEY (StudentID) REFERENCES Students(StudentID),
FOREIGN KEY (CourseID) REFERENCES Courses(CourseID)
);
2. HospitalDB
USE HospitalDB;
CREATE TABLE Patients (
PatientID INT PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50),
Age INT
);
CREATE TABLE Doctors (
DoctorID INT PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50),
Specialty VARCHAR(50)
);
CREATE TABLE Appointments (
AppointmentID INT PRIMARY KEY,
PatientID INT,
DoctorID INT,
Date DATE,
FOREIGN KEY (PatientID) REFERENCES Patients(PatientID),
FOREIGN KEY (DoctorID) REFERENCES Doctors(DoctorID)
);
CREATE TABLE Prescriptions (
PrescriptionID INT PRIMARY KEY,
AppointmentID INT,
Medicine VARCHAR(50),
FOREIGN KEY (AppointmentID) REFERENCES Appointments(AppointmentID)
);
3. LibraryDB
USE LibraryDB;
CREATE TABLE Members (
MemberID INT PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50)
);
CREATE TABLE Books (
BookID INT PRIMARY KEY,
Title VARCHAR(100),
Author VARCHAR(50)
);
CREATE TABLE Loans (
LoanID INT PRIMARY KEY,
MemberID INT,
BookID INT,
LoanDate DATE,
FOREIGN KEY (MemberID) REFERENCES Members(MemberID),
FOREIGN KEY (BookID) REFERENCES Books(BookID)
);
CREATE TABLE Staff (
StaffID INT PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50),
Position VARCHAR(50)
);
Step 3: Populate Each Table (5 rows each)
INSERT INTO Students VALUES
(1, 'John', 'Doe', 16),
(2, 'Mary', 'Smith', 17),
(3, 'Paul', 'Brown', 15),
(4, 'Linda', 'Johnson', 16),
(5, 'James', 'Wilson', 18);
-- Repeat similar INSERT INTO for all other tables across the 3 databases.