I have a table named classes with a field named available_seats set to 10 (the m

Question

I have a table named classes with a field named available_seats set to 10 (the max class size).

I need to decrease the number by 1 everytime a student enrolls in the class.

The student is enrolled in the class through the enroll table.

I want the available_seats to decrease any time that the enroll.classes_id = the classes.classes_id

I am using oracle 11g and Oracle SQL developer

Here are the classes, student and enroll tables

CREATE TABLE classes
(classes_id int NOT NULL,
classes_start_dt date NOT NULL,
classes_grad_dt date NOT NULL,
classes_location varchar2(25),
classes_time varchar2(10),
classes_day varchar2(15),
available_seats int DEFAULT '10' NOT NULL,
course_id int NOT NULL,
instructor_id int NOT NULL,
CONSTRAINT classes_pk PRIMARY KEY (classes_id),
CONSTRAINT course_fk FOREIGN KEY (course_id) REFERENCES course(course_id),
CONSTRAINT instructor_fk FOREIGN KEY (instructor_id)
REFERENCES instructor(instructor_id));

CREATE TABLE student
(student_id int NOT NULL,
student_lname varchar2(25) NOT NULL,
student_fname varchar2(25) NOT NULL,
student_gender varchar2(10),
student_areacode varchar2(3),
student_pnumber varchar2(8),
student_DoB date NOT NULL,
student_email varchar2(50),
student_comments varchar(100),
family_id int NOT NULL,
CONSTRAINT student_pk PRIMARY KEY (student_id),
CONSTRAINT fk_family FOREIGN KEY (family_id)REFERENCES family(family_id));

CREATE TABLE enroll
(enroll_id int NOT NULL,
classes_id int NOT NULL,
student_id int NOT NULL,
enroll_date date NOT NULL,
enroll_grade varchar2(4),
CONSTRAINT enroll_pk PRIMARY KEY (enroll_id),
CONSTRAINT classes_fk FOREIGN KEY (classes_id) REFERENCES classes(classes_id),
CONSTRAINT student_fk FOREIGN KEY (student_id) REFERENCES student(student_id));

Explanation / Answer

create a sequnce ad below

CREATE SEQUENCE studentNumberSeq

   INCREMENT BY -1
   START WITH 10
   MAXVALUE 10
   MINVALUE 1
   NOCACHE
   NOCYCLE;

the sequence will starts from 0 and each time the entry is inserted in the table the value of the sequence will be decreented by 1 and the min value can be 1;

while inserting the value in the table for the column available_seats use studentNumberSeq.nextVal

Related Questions

Navigate

• Browse (All)
• Browse I
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.