CREATE TABLE Employee
(
Id NUMBER PRIMARY KEY AUTOINCREMENT,
First_Name TEXT,
Last_Name TEXT
);
CREATE TABLE Topic
(
Name TEXT CHECK(Name = LOWER(Name)) PRIMARY KEY,
Description TEXT
);
CREATE TABLE Session
(
Teacher INTEGER REFERENCES Employee,
Start DATETIME,
Finish DATETIME,
Topic TEXT NOT NULL REFERENCES Topic,
PRIMARY KEY(Teacher, Start)
);
CREATE TABLE Session_Learner
(
Teacher NUMBER,
Start DATETIME,
Learner NUMBER NOT NULL REFERENCES Employee,
--
FOREIGN KEY(Teacher, Start) REFERENCES Session,
PRIMARY KEY(Teacher, Start, Learner)
);
CREATE VIEW
Session_Employee
AS
WITH
Session_Teacher_Learner
AS
(
SELECT
Session.Teacher,
Session.Start,
Session.Topic,
Session_Learner.Learner
FROM
Session,
Session_Learner
WHERE
Session_Learner.Teacher = Session.Teacher
AND Session_Learner.Start = Session.Start
)
SELECT
Teacher,
Start,
Topic,
Teacher Employee
FROM
Session_Teacher_Learner
UNION ALL
SELECT
Teacher,
Start,
Topic,
Learner
FROM
Session_Teacher_Learner;
|