-- Steven Carr
-- November 24th 2024
-- Homework 5 Question 1

-- Answer to part a
CREATE TABLE Articles(
        aid INT PRIMARY KEY,
        title VARCHAR(50) NOT NULL,
        author VARCHAR(30) NOT NULL,
        pubyear INT NOT NULL
);

CREATE TABLE Students(
        sid INT PRIMARY KEY,
        name VARCHAR(30) NOT NULL,
        city VARCHAR(50) NOT NULL,
        state VARCHAR(2) NOT NULL,
        age REAL NOT NULL,
        gpa REAL CHECK (gpa BETWEEN 1 AND 4) NOT NULL
);

CREATE TABLE Reads(
        aid INT NOT NULL,
        sid INT NOT NULL,
        rday DATE NOT NULL,
        PRIMARY KEY (aid, sid),
        FOREIGN KEY (aid) REFERENCES Articles(aid),
        FOREIGN KEY (sid) REFERENCES Students(sid)
);

-- Answer to part b
INSERT INTO Students VALUES(1, 'Steve', 'Boston', 'MA', 28, 3.8);
INSERT INTO Students VALUES(2, 'Zangar', 'Salem', 'MA', 29, 3.6);
INSERT INTO Students VALUES(3, 'Kendall', 'Raleigh', 'NC', 26, 3.9);

INSERT INTO Articles VALUES(101, 'Database Management', 'Cyrus', 2024);
INSERT INTO Articles VALUES(102, 'Artificial Intelligence', 'Wei', 2015);

-- Answer to part c
INSERT INTO Reads VALUES(101, 1, DATE '2024-11-23');
INSERT INTO Reads VALUES(102, 1, DATE '2024-11-24');

INSERT INTO Reads VALUES(101, 3, DATE '2024-11-01');

-- Answer to part d
CREATE VIEW MAStudents AS SELECT * FROM Students
WHERE state = 'MA';

-- Answer to part e
CREATE VIEW StudentsReads AS SELECT s.sid, s.name, s.city, a.aid, a.title
FROM Students s JOIN Reads r ON s.sid = r.sid
JOIN Articles a ON r.aid = a.aid;

-- Answer to part f
SELECT sid, COUNT(aid) AS article_count
FROM StudentsReads
GROUP BY sid;

-- Answer to part g
DROP VIEW MAStudents;
DROP VIEW StudentsReads;