CREATE TABLE District (
    DistrictCode VARCHAR(5) PRIMARY KEY,
    DistrictName VARCHAR(50) NOT NULL
);

CREATE TABLE School (
    SchoolID INT,
    SchoolName VARCHAR(50),
    DateConstructed DATE,
    OperatingBudget DECIMAL(3,1) UNSIGNED,
    Classification VARCHAR(1),
    President VARCHAR(20),
    DistrictCode VARCHAR(5),
    FOREIGN KEY (DistrictCode) REFERENCES District(DistrictCode)
);

CREATE VIEW SchoolOverview AS
SELECT 
    s.SchoolID,
    s.SchoolName,
    s.DateConstructed,
    s.OperatingBudget,
    s.Classification,
    s.President,
    d.DistrictName
FROM 
    School s
JOIN 
    District d ON s.DistrictCode = d.DistrictCode;


INSERT INTO District(DistrictCode, DistrictName) VALUES 
('D001', 'North Valley District'), 
('D002', 'Central Plains District'), 
('D003', 'Lakeside Unified'), 
('D004', 'Mountain Ridge District'); 


INSERT INTO School(SchoolID, SchoolName, DateConstructed, OperatingBudget, Classification, President, DistrictCode) VALUES 
(101, 'North Valley High', '1995-08-15', 45.5, 'A', 'Dr. Smith', 'D001'), 
(102, 'Central Plains District', '2002-06-10', 30.2, 'B', 'Ms. Johnson', 'D002'), 
(103, 'Lakside Unified', '1988-09-01', 55.9, 'A', 'Mr. Lee', 'D003'),
(104, 'Mountain Ridge Academy', '2010-03-22', 60.0,'C', 'Dr. Patel', 'D004');


CREATE INDEX idx_Classification ON School(Classification); 
CREATE INDEX idx_DistrictCode ON School(DistrictCode);