2

Trying to make a database that allows for a course to have several sections, and a section to have several events/periods as to when they meet. Everything is linked properly, however I am having a problem with a PostgreSQL error which I realize is telling me that I need UNIQUE attributes in the "Sections" and "ClassEvent" Tables, but I can't have them be UNIQUE because it is information that should/can be repeated.

    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;

I try to do this and I get the following error:

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
Improve this question

1 Answer 1

Reset to default
2

You do not need to have year and term as foreign keys in the class events table. This information is redundant as it can be looked up in sections table via the crn. I suggest changing your create statement for ClassEvents as follows:

CREATE TABLE ClassEvent (
    crn         INT    REFERENCES Sections(crn)                  NOT NULL,        
    professorID INT    REFERENCES Professors(professorID)        NOT NULL,
    day         TEXT NOT NULL,
    startTime   TIME NOT NULL,
    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, day, startTime,professorID)
);

I have changed primary key to be a combination of course,date/time and professor - as a professor cannot be teaching more than course on any given point in time.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

That works perfectly! Thanks! I have another problem now though. PostgreSQL is telling me that when I populate "Sections" with a professorID, such as 4, there is a unique constraint violation. However, I can't refer to the previous tables if I don't have that ID. Professors are allowed to teach several sections of a course. Any idea on how to fix that?
Also check is likely to fail: OR day = NULL would better be OR day IS NULL, unless you have transform_null_equals setting to true, which is not the default
@ZiggyCrueltyfreeZeitgeister thanks for the advise, I will change that. The other probem, I was able to fix, I just had to remove UNIQUE from the "Sections" table. Thanks for all the help!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.