mysql - Course scheduler

Question

Having Issues designing a proper table

CREATE TABLE program(
  id varchar(40) NOT NULL,
  PRIMARY KEY(id)
);

CREATE TABLE courses (
  id int(4) NOT NULL, 
  name varchar(40) NOT NULL,
  PRIMARY KEY (id)
);

CREATE TABLE pre_req(
  id int(4) NOT NULL, 
  id2 int(4) NOT NULL, 
  grade varchar(2) NOT NULL,
  PRIMARY KEY(id,id2) 
);

CREATE TABLE time_slots(
  id int(4) NOT NULL, 
  start_T TIME,
  end_T TIME,
  PRIMARY KEY(id) 
);

CREATE TABLE transcripts(
  id int(4) NOT NULL, 
  grade varchar(2) NOT NULL
);

INSERT INTO program(id) VALUES
('Associate of Computer Science');

SELECT * FROM program;
        
INSERT INTO courses(id,name) VALUES
(2150,'Algorithms and Data Structures II'),
(1181,'Object-oriented Computing'),
(1160,'Algorithms and Data Structures I'),
(1150,'Program Design'),
(1050,'Introduction to Computer Science'),
(1155,'Program Design for Engineers'),
(9999,'Precalculus 12');

SELECT * FROM courses;

INSERT INTO pre_req(id,id2,grade) VALUES
(2150,1181,'C'),
(2150,1160,'C');


SELECT pre_req.id2, pre_req.grade FROM pre_req where pre_req.id = 2150;

How they should relate

Program(1:M) -> Classes(M:M) -> Pre_req  

Issue being if I had something like

 A minimum "C" grade in one of CPSC 1150 or 1155; and one of the following: a minimum "B" grade in Precalculus 12; or a minimum "C" grade in MATH 1170, 1171, 1173, or 1174; or a minimum "C+" in Precalculus 12 and a minimum "C-" grade in Calculus 12; or MDT 85.

How would I implement the logical operators AND and OR to link up my courses together.

Answer 1

等待大神答复