This is some kind of study database in sql file. I use oracle 11g and process it in sqlplus.
I wrote two function for check the course number and the department number, so its depend on the major and the minor department of each student.
For example, I am student of CS department (major) and BIO department (minor), so I can not enroll the course which is about math department.
When I call it in CHECK but I don't know why it told that.
This is output when I create all table, (from sqlplus)
....
....
....
table created.
ALTER TABLE sections ADD CONSTRAINT CK_course_depart CHECK (FindMYDeparture(stuid,find_dno_from_cno(cno)) = 'true');
ERROR at line 1:
ORA-0094: "FIND_DNO_FROM_CNO":invalid identifier
This is in .sql file
DROP TABLE department CASCADE CONSTRAINTS;
CREATE TABLE department (
dnumber number(4) not null,
dname varchar(25) not null,
primary key (dnumber)
);
DROP TABLE courses CASCADE CONSTRAINTS;
CREATE TABLE courses (
cno number(4) not null,
cname varchar(15) not null,
credit number(1) not null,
dnumber number(4) not null,
primary key (cno),
foreign key (dnumber) references department(dnumber),
CONSTRAINT credits CHECK (credit > 0 AND credit <= 5)
);
DROP TABLE student CASCADE CONSTRAINTS;
CREATE TABLE student (
stuid char(9) not null,
fname varchar(15) not null,
lname varchar(15) not null,
dMjno number(4) not null,
dMnno number(4),
primary key (stuid),
CONSTRAINT depart_M_n CHECK (dMjno <> dMnno),
CONSTRAINT dMinor_check CHECK (dMnno = 1 OR dMnno = 2 OR dMnno = 3)
);
DROP TABLE sections CASCADE CONSTRAINTS;
CREATE TABLE sections (
sno number(4) not null,
cno number(4) not null,
stuid char(9) not null,
semester varchar(6) not null,
year varchar(4) not null,
instructor varchar(15) not null,
CONSTRAINT combine_pk primary key (sno,stuid),
foreign key (cno) references courses(cno),
foreign key (stuid) references student(stuid),
CONSTRAINT cant_enroll CHECK (semester <> 'Spring' AND year <> 2007)
);
DROP TABLE grading CASCADE CONSTRAINTS;
CREATE TABLE grading (
sno number(4) not null,
stuid char(9) not null,
grade numeric(1,2),
foreign key (sno,stuid) references sections(sno,stuid),
foreign key (stuid) references student(stuid),
CONSTRAINT grading_check CHECK (grade >= 0 AND grade <= 4)
);
DROP FUNCTION FindMYDeparture;
CREATE OR REPLACE FUNCTION FindMYDeparture(stuid_in IN char,depart_course IN NUMBER)
RETURN NUMBER AS
departMa_no NUMBER;
departMi_no NUMBER;
report varchar(10);
CURSOR cdno is
SELECT dMjno,dMnno FROM student WHERE stuid = stuid_in;
BEGIN
OPEN cdno;
LOOP
FETCH cdno INTO departMa_no,departMi_no;
IF (departMa_no = depart_course OR departMi_no = depart_course)
THEN
report := 'true';
EXIT;
ELSE
report := 'flase';
END IF;
EXIT WHEN cdno%NOTFOUND;
END LOOP;
CLOSE cdno;
RETURN report;
END;
/
DROP FUNCTION find_dno_from_cno;
CREATE OR REPLACE FUNCTION find_dno_from_cno(cno_in IN NUMBER)
RETURN NUMBER AS
depart_no NUMBER;
CURSOR cdno is
SELECT dnumber FROM courses WHERE cno = cno_in;
BEGIN
OPEN cdno;
FETCH cdno INTO depart_no;
CLOSE cdno;
RETURN depart_no;
END;
/
ALTER TABLE sections ADD CONSTRAINT CK_course_depart CHECK (FindMYDeparture(stuid,find_dno_from_cno(cno)) = 'true');
