尝试创建一个允许课程包含多个部分的数据库，以及一个部分，以便在他们见面时有几个事件/句点 . 一切都正确链接，但是我遇到了PostgreSQL错误的问题，我意识到它告诉我在“Sections”和“ClassEvent”表中需要UNIQUE属性，但我不能让它们独一无二，因为它是信息应该/可以重复 .
DROP TABLE IF EXISTS Stats;
DROP TABLE IF EXISTS ClassEvent;
DROP TABLE IF EXISTS Enrollment;
DROP TABLE IF EXISTS DegreeReq;
DROP TABLE IF EXISTS DegreeInstance;
DROP TABLE IF EXISTS Sections;
DROP TABLE IF EXISTS Students;
DROP TABLE IF EXISTS Degrees;
DROP TABLE IF EXISTS Professors;
DROP TABLE IF EXISTS People;
DROP TABLE IF EXISTS Courses;
-- HOLDS A SPECIFIC COURSE WITHOUT THE INSTANCES OF THE CLASS --
CREATE TABLE Courses (
courseID SERIAL UNIQUE NOT NULL,
department VARCHAR(4) NOT NULL,
courseNumber VARCHAR(5) NOT NULL,
courseName TEXT UNIQUE NOT NULL,
credits INT NOT NULL,
PRIMARY KEY(courseID)
);
-- PEOPLE SUPERTYPE --
CREATE TABLE People (
pid SERIAL UNIQUE NOT NULL,
fname TEXT NOT NULL,
lname TEXT NOT NULL,
PRIMARY KEY(pid)
);
-- HOLDS THE DIFFERENT PROFESSORS TEACHING AT THE SCHOOL --
-- SUBTYPE OF PEOPLE --
CREATE TABLE Professors (
professorID SERIAL UNIQUE NOT NULL,
status TEXT NOT NULL,
CHECK(status = 'Full-Time' OR status = 'Part-time'),
PRIMARY KEY(professorID)
);
-- HOLDS THE SPECIFIC INSTANCES OF THE CLASS DEPENDING ON THE YEAR AND TERM --
CREATE TABLE Sections (
crn INT UNIQUE NOT NULL,
courseID SERIAL REFERENCES Courses(courseID) NOT NULL,
year INT NOT NULL,
term TEXT NOT NULL,
sectionNumber INT NOT NULL,
startDate DATE NOT NULL,
endDate DATE NOT NULL,
professorID INT REFERENCES Professors(professorID) UNIQUE NOT NULL,
PRIMARY KEY(crn, courseID)
);
-- HOLDS THE EVENT OF THE CLASS --
-- A CLASS MAY HAVE DIFFERENT DAYS ON WHICH --
-- THEY MEET ON, SO THIS ALLOWS A CERTAIN --
-- SECTION TO HAVE SEVERAL DAYS WITHOUT CONFLICT --
CREATE TABLE ClassEvent (
crn INT REFERENCES Sections(crn) NOT NULL,
year INT REFERENCES Sections(year) NOT NULL,
term TEXT REFERENCES Sections(term) NOT NULL,
professorID INT REFERENCES Professors(professorID) NOT NULL,
day TEXT,
startTime TIME,
endTime TIME,
location TEXT,
campus TEXT,
CHECK(day = 'Monday' OR day = 'Tuesday' OR day = 'Wednesday' OR day = 'Thursday' OR day = 'Friday' OR day = 'Saturday' OR day = 'Sunday' OR day = NULL),
PRIMARY KEY(crn, year, term)
);
-- HOLDS THE STUDENTS THAT WILL BE TAKING THE CLASSES --
-- SUBTYPE OF PEOPLE --
CREATE TABLE Students (
studentID INT REFERENCES People(pid) UNIQUE NOT NULL,
studentName TEXT NOT NULL,
gradYear DATE UNIQUE NOT NULL,
PRIMARY KEY(studentID)
);
-- HOLDS A CLASS RECORD FOR STUDENTS (AND POSSIBLY PROFESSORS) --
CREATE TABLE Enrollment (
studentID INT REFERENCES Students(studentID) UNIQUE NOT NULL,
crn INT REFERENCES Sections(crn) NOT NULL,
grade TEXT NOT NULL,
PRIMARY KEY(studentID, crn)
);
-- HOLDS THE DIFFERENT DEGREES THAT CAN BE ATTAINED AT THE COLLEGE/UNIVERSITY --
CREATE TABLE Degrees (
degreeID SERIAL UNIQUE NOT NULL,
degreeName TEXT NOT NULL,
degreeType TEXT NOT NULL,
degDepartment VARCHAR(4) NOT NULL,
CHECK(degreeType = 'Major' OR degreeType = 'Minor' OR degreeType = 'Masters'),
PRIMARY KEY(degreeID)
);
-- HOLDS THE CLASSES THAT WILL MAKE UP A DEGREE --
CREATE TABLE DegreeReq (
degreeID INT REFERENCES Degrees(degreeID) UNIQUE NOT NULL,
courseID INT REFERENCES Courses(courseID) UNIQUE NOT NULL,
PRIMARY KEY(degreeID, courseID)
);
-- HOLDS THE INSTANCE OF A DEGREE FOR A CERTAIN STUDENT --
-- FOR EXAMPLE: A STUDENT CAN HAVE A MAJOR AND A MINOR --
-- SO HE/SHE CAN STORE THEM SEPARATELY --
CREATE TABLE DegreeInstance (
degreeID INT REFERENCES Degrees(degreeID) UNIQUE NOT NULL,
studentID INT REFERENCES Students(studentID) UNIQUE NOT NULL,
startDate DATE NOT NULL,
endDate DATE NOT NULL,
creditsRequired INT NOT NULL,
PRIMARY KEY(degreeID, studentID)
);
-- HOLDS ALL THE RATE MY PROFESSOR STATS --
CREATE TABLE Stats (
professorID INT REFERENCES Professors(professorID) UNIQUE NOT NULL,
dateSubmitted TIMESTAMP UNIQUE NOT NULL,
rating FLOAT NOT NULL,
helpfulness FLOAT NOT NULL,
clarity FLOAT NOT NULL,
easiness FLOAT NOT NULL,
PRIMARY KEY(professorID, dateSubmitted)
);
INSERT INTO Courses(department, courseNumber, courseName, credits)
VALUES
('CMPT', '120L', 'Intro to Programming', 4),
('CMPT', '220L', 'Software Development I', 4),
('CMPT', '221L', 'Software Development II', 4),
('CMPT', '230L', 'Software Systems + Analysis', 4),
('CMPT', '306N', 'Data Communications', 4),
('CMPT', '308L', 'Database Management', 4),
('CMPT', '307N', 'Internetworking', 4),
('CMPT', '330L', 'System Design', 4),
('CMPT', '422L', 'Computer Organization and Architecture', 4),
('CMPT', '435L', 'Algorithm Analysis and Design', 4),
('CMPT', '424L', 'Operating Systems', 4),
('CMPT', '432L', 'Design of Compilers', 4),
('CMPT', '331L', 'Theory of Programming Languages', 4),
('CMPT', '440L', 'Formal Languages and Computability', 4),
('CMPT', '333L', 'Language Study', 4),
('CMPT', '475L', 'CS Project I', 4),
('CMPT', '476L', 'CS Project II', 4),
('BUS', '100N', 'Intro to Business & Management', 4),
('MATH', '130L', 'Intro to Statistics I', 3),
('MATH', '241L', 'Calculus I', 4),
('MATH', '242L', 'Calculus II', 4),
('FYS', '101L', 'First Year Seminar', 4),
('ENG', '120L', 'Writing for College', 3),
('PHIL', '101L', 'Philosophical Perspectives', 3),
('MUS', '120L', 'Theory of Music I', 3),
('ECON', '103L', 'Principles of Microeconomics', 3),
('ECON', '104L', 'Principles of Macroeconomics', 3),
('MATH', '343L', 'Calculus III', 4),
('MATH', '310L', 'Intro to Mathematics Reasoning', 3),
('CMPT', '416N', 'Intro to Cybersecurity', 4),
('CMPT', '446N', 'Computer Graphics', 4),
('CMPT', '404L', 'Artificial Intelligence', 4),
('CMPT', '305L', 'Technology, Ethics and Society', 3),
('CMPT', '192N', 'Competitive Programming', 1);
INSERT INTO People(fname, lname)
VALUES
('Marcos', 'Barbieri'),
('James', 'Crowley'),
('Kaylee', 'Pope'),
('Pablo', 'Rivas'),
('Matthew', 'Johnson'),
('Anne', 'Matheus'),
('Joe', 'Smith'),
('Mike', 'Emmet'),
('Michael', 'Jordan'),
('Alan', 'Labouseur');
INSERT INTO Professors(professorID, status)
VALUES
(4, 'Full-Time'),
(5, 'Full-Time'),
(6, 'Full-Time'),
(7, 'Full-Time'),
(10, 'Full-Time');
INSERT INTO Sections(crn, courseID, year, term, sectionNumber, startDate, endDate, professorID)
VALUES
(11111, 1, 2016, 'Fall', 111, '2016-08-29', '2016-12-15', 4),
(11112, 1, 2016, 'Fall', 112, '2016-08-29', '2016-12-15', 4),
(11113, 1, 2016, 'Fall', 113, '2016-08-29', '2016-12-15', 4),
(11121, 2, 2016, 'Fall', 111, '2016-08-29', '2016-12-15', 5),
(11122, 2, 2016, 'Fall', 112, '2016-08-29', '2016-12-15', 5),
(11123, 2, 2016, 'Fall', 113, '2016-08-29', '2016-12-15', 5),
(11211, 3, 2016, 'Fall', 111, '2016-08-29', '2016-12-15', 6),
(11212, 3, 2016, 'Fall', 112, '2016-08-29', '2016-12-15', 6),
(11213, 3, 2016, 'Fall', 113, '2016-08-29', '2016-12-15', 6),
(11214, 4, 2016, 'Fall', 111, '2016-08-29', '2016-12-15', 7),
(11215, 4, 2016, 'Fall', 112, '2016-08-29', '2016-12-15', 7),
(11216, 5, 2016, 'Fall', 111, '2016-08-29', '2016-12-15', 10),
(11217, 6, 2016, 'Fall', 111, '2016-08-29', '2016-12-15', 10),
(11118, 6, 2016, 'Fall', 112, '2016-08-29', '2016-12-15', 4),
(11119, 6, 2016, 'Fall', 113, '2016-08-29', '2016-12-15', 5),
(11120, 6, 2016, 'Fall', 114, '2016-08-29', '2016-12-15', 4),
(11200, 7, 2016, 'Fall', 111, '2016-08-29', '2016-12-15', 5),
(11201, 7, 2016, 'Fall', 112, '2016-08-29', '2016-12-15', 5),
(11202, 8, 2016, 'Fall', 111, '2016-08-29', '2016-12-15', 10),
(11203, 9, 2016, 'Fall', 111, '2016-08-29', '2016-12-15', 10),
(11204, 10, 2016, 'Fall', 111, '2016-08-29', '2016-12-15', 6),
(11205, 10, 2016, 'Fall', 112, '2016-08-29', '2016-12-15', 6),
(11206, 10, 2016, 'Fall', 113, '2016-08-29', '2016-12-15', 6),
(11207, 10, 2016, 'Fall', 114, '2016-08-29', '2016-12-15', 6),
(11208, 11, 2016, 'Fall', 111, '2016-08-29', '2016-12-15', 4),
(11209, 11, 2016, 'Fall', 112, '2016-08-29', '2016-12-15', 4),
(11222, 12, 2016, 'Fall', 111, '2016-08-29', '2016-12-15', 4),
(11333, 12, 2016, 'Fall', 112, '2016-08-29', '2016-12-15', 5),
(11445, 12, 2016, 'Fall', 113, '2016-08-29', '2016-12-15', 7),
(11111, 12, 2016, 'Fall', 111, '2016-08-29', '2016-12-15', 4),
(11678, 13, 2016, 'Fall', 111, '2016-08-29', '2016-12-15', 10),
(11679, 13, 2016, 'Fall', 112, '2016-08-29', '2016-12-15', 4);
SELECT * FROM Sections;
我尝试这样做，我得到以下错误：
ERROR: there is no unique constraint matching given keys for referenced table "sections"
********** Error **********
ERROR: there is no unique constraint matching given keys for referenced table "sections"
SQL state: 42830
1 回答
您不需要在类事件表中将年份和术语作为外键 . 这些信息是多余的，因为它可以通过crn在section表中查找 . 我建议更改ClassEvents的create语句，如下所示：
我已将主键改为课程，日期/时间和教授的组合 - 因为教授在任何给定的时间点都不能教授课程 .