Database Class Help

Spoiler

So I have to create a database with 5 entities and 5 attributes each with only 1:M relationships.

My database is about managing a nutrition store inventory. What do yall think?

 

sorry I missed a relation in the first ERD.

Spoiler

 

so the idea is that one store can have many products, and multiple quantities of that product. also, product x can be sold at 0 stores, or many stores.

we can't have M:N relationships, so i tried to break it down into two 1:M relations.

i am not sure if i did that right though.

 

alright so i got another assignment due. it's my first time writing DDL thingies.

what am I doing wrong with this? isqlplus won't create the tables.

Code:

create table school (
school_code	numeric,
school_name 	varchar (20),
CONSTRAINT PK_SCHOOL PRIMARY KEY (school_code),
CONSTRAINT FK_SCHOOL_PROFESSOR FOREIGN KEY (PROF_NUM) references PROFESSOR (PROF_NUM)
);

create table professor (
prof_num 	numeric, 
prof_specialty 	varchar (20),
prof_rank 	varchar (20),
prof_lname 	varchar (20),
prof_fname 	varchar (20),
prof_initial 	char (1),
prof_email 	varchar (30),
CONSTRAINT PK_PROFESSOR PRIMARY KEY (prof_num),
CONSTRAINT FK_PROFESSOR_DEPARTMENT FOREIGN KEY (DEPT_CODE) references DEPARTMENT (DEPT_CODE)
);

create table department (
dept_code 	numeric,
dept_name	varchar(20),
CONSTRAINT PK_DEPARTMENT PRIMARY KEY (DEPT_CODE),
CONSTRAINT FK_DEPARTMENT_SCHOOL FOREIGN KEY (SCHOOL_CODE) references SCHOOL (SCHOOL_CODE),
CONSTRAINT FK_DEPARTMENT_PROFESSOR FOREIGN KEY (PROF_NUM) references PROFESSOR (PROF_NUM)
);

CREATE TABLE STUDENT (
stu_num		numeric,
stu_lname 	varchar (20),
stu_fname 	varchar (20),
stu_initial 	char (1),
stu_email 	varchar (30),
constraint PK_student primary key(stu_num),
constraint FK_student_department foreign key (dept_code) references department (dept_code),
constraint FK_student_professor foreign key (prof_num) references professor (prof_num)
);

create table course (
crs_code 	numeric,
crs_title	varchar(20),
crs_description varchar(20),
crs_credit	varchar(20),
constraint PK_course	primary key (crs_code),
constraint fk_course_department foreign key (dept_code) references department (dept_code)
);

create table class (
class_code 	numeric,
class_section	varchar(20),
class_time	varchar(20),
constraint pk_class primary key (class_code),
constraint fk_class_course foreign key (crs_code) references course (crs_code),
constraint fk_class_professor foreign key (prof_num) references  professor (prof_num),
constraint fk_class_room foreign key (room_code) references room(room_code)
);

create table enroll (
enroll_date 	date,
enroll grade	char(1),
constraint pk_enroll primary key(class_code,stu_num),
constraint fk_enroll_class foreign key(class_code) references class(class_code),
constraint fk_enroll_student foreign key(stu_num) references student(stu_num)
);

create table room (
room_code 	numeric,
room_type	varchar(20),
constraint PK_room Primary Key (room_code),
constraint FK_room_building Foreign Key (bldg_code) references Building (bldg_code)
);

create table building (
bldg_code	numeric,
bldg_name	varchar(20),
bldg_location	varchar(20),
constraint PK_building primary key (bldg_code)
);

this is the error i get

Code:

ERROR at line 1: 
ORA-00942: table or view does not exist 
CONSTRAINT FK_SCHOOL_PROFESSOR FOREIGN KEY (PROF_NUM) references PROFESSOR (PROF_NUM)
                                            *
ERROR at line 5: 
ORA-00904: "PROF_NUM": invalid identifier 
CONSTRAINT FK_PROFESSOR_DEPARTMENT FOREIGN KEY (DEPT_CODE) references DEPARTMENT (DEPT_CODE)
                                                *
ERROR at line 10: 
ORA-00904: "DEPT_CODE": invalid identifier 
CONSTRAINT FK_DEPARTMENT_SCHOOL FOREIGN KEY (SCHOOL_CODE) references SCHOOL (SCHOOL_CODE),
                                             *
ERROR at line 5: 
ORA-00904: "SCHOOL_CODE": invalid identifier 
constraint FK_student_department foreign key (dept_code) references department (dept_code),
                                              *
ERROR at line 8: 
ORA-00904: "DEPT_CODE": invalid identifier 
constraint fk_course_department foreign key (dept_code) references department (dept_code)
                                             *
ERROR at line 7: 
ORA-00904: "DEPT_CODE": invalid identifier 
constraint fk_class_course foreign key (crs_code) references course (crs_code),
                                        *
ERROR at line 6: 
ORA-00904: "CRS_CODE": invalid identifier 
enroll grade	char(1),
            	*
ERROR at line 3: 
ORA-00907: missing right parenthesis 
constraint FK_room_building Foreign Key (bldg_code) references Building (bldg_code)

thanks/reps.

 

thanks for the notepad++ suggestion. I am using it now.

i figured this is my issue..i don't know how to though. haha. which table would come first? it seems like they are all referencing some other table.

 

Got it to work.

Spoiler

Code:

create table building (
bldg_code	numeric,
bldg_name	varchar(20),
bldg_location	varchar(20),
constraint PK_building primary key (bldg_code)
);

create table room (
room_code 	numeric,
bldg_code	numeric,
room_type	varchar(20),
constraint PK_room Primary Key (room_code),
constraint FK_room_building Foreign Key (bldg_code) references building (bldg_code)
);

create table school (
school_code 	numeric,
school_name 	varchar(20),
prof_num	numeric,
CONSTRAINT PK_SCHOOL PRIMARY KEY (school_code)
);

create table professor (
prof_num 	numeric, 
dept_code	numeric,
prof_specialty 	varchar(20),
prof_rank 	varchar(20),
prof_lname 	varchar(20),
prof_fname 	varchar(20),
prof_initial 	char(1),
prof_email 	varchar(30),
CONSTRAINT PK_PROFESSOR PRIMARY KEY (prof_num)
);

create table department (
dept_code 	numeric,
dept_name	varchar(20),
school_code	numeric,
prof_num	numeric,
CONSTRAINT PK_DEPARTMENT PRIMARY KEY (dept_code),
CONSTRAINT FK_DEPARTMENT_SCHOOL FOREIGN KEY (SCHOOL_CODE) references SCHOOL (SCHOOL_CODE),
CONSTRAINT FK_DEPARTMENT_PROFESSOR FOREIGN KEY (PROF_NUM) references PROFESSOR (PROF_NUM)
);

ALTER table school
	ADD CONSTRAINT FK_School_Professor
	FOREIGN KEY (PROF_NUM)
	REFERENCES PROFESSOR (PROF_NUM);

ALTER table professor
	ADD CONSTRAINT FK_Professor_Department
	FOREIGN KEY (dept_code) 
	REFERENCES DEPARTMENT (dept_code);

create table course (
crs_code 	numeric,
dept_code	numeric,
crs_title	varchar(20),
crs_description varchar(20),
crs_credit	varchar(20),
constraint PK_course	primary key (crs_code),
constraint FK_course_department foreign key (dept_code) references department (dept_code)
);

CREATE TABLE STUDENT (
stu_num		numeric,
stu_lname 	varchar(20),
stu_fname 	varchar(20),
stu_initial 	char(1),
stu_email 	varchar(30),
prof_num	numeric,
dept_code	numeric,
constraint PK_student primary key(stu_num),
constraint FK_student_department foreign key (dept_code) references department (dept_code),
constraint FK_student_professor foreign key (prof_num) references professor (prof_num)
);

create table class (
class_code 	numeric,
class_section	varchar(20),
class_time	varchar(20),
crs_code numeric,
prof_num numeric,
room_code numeric,
constraint pk_class primary key (class_code),
constraint fk_class_course foreign key (crs_code) references course (crs_code),
constraint fk_class_professor foreign key (prof_num) references  professor (prof_num),
constraint fk_class_room foreign key (room_code) references room (room_code)
);

create table enroll (
class_code	numeric,
stu_num	numeric,
enroll_date 	date,
enroll_grade	char(1),
constraint pk_enroll primary key(class_code,stu_num),
constraint fk_enroll_class foreign key(class_code) references class (class_code),
constraint fk_enroll_student foreign key(stu_num) references student(stu_num)
);

what's the best way to drop all those tables? I tried this but of course the bolded tables won't drop because of the foreign keys. Is there a shortcut?
drop table enroll;
drop table class;
drop table student;
drop table course;
drop table department;
drop table professor;
drop table school;
drop table room;
drop table building;

 

ok i dont know what i am doing anymore. lol.

here's a case study i have to do and what i mocked up so far. any help would be appreciated.

 

does this ERD have any obvious mistakes?