Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2006
    Posts
    19

    Unanswered: Help with Relationship

    Hello, I need help with the relationship on my script.
    What I want to do is make it possible to assign many courses to a student.
    But I have read a few books on foreign keys and primary keys, and I still dont get how to go on doing this....

    This is how the table creation with the keys looks right now...

    Hope you guys can help me out .

    http://img521.imageshack.us/img521/8...lscreenju4.jpg

    PHP Code:
    CREATE TABLE CAMPUS (
       
    CAMPUSID            NUMBER(4NOT NULL,
       
    NAME                VARCHAR2(80NOT NULL,
       
    CONSTRAINT CAMPUS_PK PRIMARY KEY (CAMPUSID));
    CREATE TABLE MAJOR (
       
    MAJORID            NUMBER(4NOT NULL,
       
    NAME                VARCHAR2(80NOT NULL,
       
    CONSTRAINT MAJOR_PK PRIMARY KEY (MAJORID)); 
    CREATE TABLE PROFESSOR (
       
    PROFESSORID        NUMBER NOT NULL,
       
    FIRSTNAME        VARCHAR2(30NOT NULL,
       
    LASTNAME            VARCHAR2(30NOT NULL,
       
    ADDRESS            VARCHAR2(80),
       
    ZIPCODE            VARCHAR2(10),
       
    HOMEPHONE        VARCHAR2(12),
       
    MOBILEPHONE        VARCHAR2(12),
       
    EMAIL            VARCHAR2(50),
       
    CONSTRAINT PROFESSOR_PK PRIMARY KEY (PROFESSORID));      
    CREATE TABLE COURSE (
       
    COURSEID            NUMBER(4NOT NULL,
       
    SUBJECT            CHAR(5NOT NULL,
       
    CRSE                CHAR(5NOT NULL,
       
    TITLE            VARCHAR2(80NOT NULL,
       
    SECTION            CHAR(3NOT NULL,
       
    DAYS                CHAR(2NOT NULL,
       
    TIME                VARCHAR2(15NOT NULL,
       
    BUILDING            VARCHAR2(10NOT NULL,
       
    ROOM                VARCHAR2(5NOT NULL,
       
    CREDITS            NUMBER(3NOT NULL,
       
    PROFESSORID        NUMBER NOT NULL,
       
    CONSTRAINT COURSE_PK PRIMARY KEY (COURSEID),
       
    CONSTRAINT COURSE_PROFESSOR_FK FOREIGN KEY (PROFESSORIDREFERENCES PROFESSOR(PROFESSORID));
    CREATE TABLE STUDENT (
       
    STUDENTID        NUMBER NOT NULL,
       
    FIRSTNAME        VARCHAR2(30NOT NULL,
       
    LASTNAME            VARCHAR2(30NOT NULL,
       
    ADDRESS            VARCHAR2(80),
       
    ZIPCODE            VARCHAR2(10),
       
    HOMEPHONE        VARCHAR2(12),
       
    MOBILEPHONE        VARCHAR2(12),
       
    EMAIL            VARCHAR2(50),
       
    CAMPUSID            NUMBER(4NOT NULL,
       
    MAJORID            NUMBER(4NOT NULL,
       
    COURSEID            NUMBER NOT NULL,
       
    CONSTRAINT STUDENT_PK PRIMARY KEY (STUDENTID),
       
    CONSTRAINT STUDENT_CAMPUSID_FK FOREIGN KEY (CAMPUSIDREFERENCES CAMPUS(CAMPUSID),
       
    CONSTRAINT STUDENT_MAJORID_FK FOREIGN KEY (MAJORIDREFERENCES MAJOR(MAJORID),
       
    CONSTRAINT STUDENT_COURSE_FK FOREIGN KEY (COURSEIDREFERENCES COURSE(COURSEID)); 

  2. #2
    Join Date
    Sep 2006
    Posts
    19
    Somebody can give me some light on this please? Been working on it, and can't figure it out...

  3. #3
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Been working on it, and can't figure it out...
    Neither can I.
    EXACTLY what question do you desire to be answered?

    >Hello, I need help with the relationship on my script.
    Script? I see some DDL.
    It looks OK to me.
    NO errors are/were posted. So what is the problem?
    Last edited by anacedent; 02-25-07 at 01:18.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  4. #4
    Join Date
    Sep 2006
    Posts
    19
    Sorry if I didn't explain it well, the thing is, I want to be able to see all the students that are in a course. Maybe there is some kind of data type where I can put multiple numbers like in the student table (courseid) (4001, 4002) etc. All the tables are filled with data.

  5. #5
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Talking

    Quote Originally Posted by XDC
    Sorry if I didn't explain it well, the thing is, I want to be able to see all the students that are in a course. Maybe there is some kind of data type where I can put multiple numbers like in the student table (courseid) (4001, 4002) etc. All the tables are filled with data.
    You may need to do some research on JOINS.

    A student can be enroled in MANY courses -- therefore you may need to create a STUDENT->COURSE table and revise the student table as follows:
    Code:
    CREATE TABLE STUDENT (
       STUDENTID        NUMBER NOT NULL,
       FIRSTNAME        VARCHAR2(30) NOT NULL,
       LASTNAME            VARCHAR2(30) NOT NULL,
       ADDRESS            VARCHAR2(80),
       ZIPCODE            VARCHAR2(10),
       HOMEPHONE        VARCHAR2(12),
       MOBILEPHONE        VARCHAR2(12),
       EMAIL            VARCHAR2(50),
       CAMPUSID            NUMBER(4) NOT NULL,
       MAJORID            NUMBER(4) NOT NULL,
       CONSTRAINT STUDENT_PK PRIMARY KEY (STUDENTID),
       CONSTRAINT STUDENT_CAMPUSID_FK FOREIGN KEY (CAMPUSID) REFERENCES CAMPUS(CAMPUSID),
       CONSTRAINT STUDENT_MAJORID_FK FOREIGN KEY (MAJORID) REFERENCES MAJOR(MAJORID));
    
    CREATE TABLE STUDENT_COURSE (
       STUDENTID        NUMBER NOT NULL,
       COURSEID            NUMBER NOT NULL,
       CONSTRAINT STUDENT_COURSE_PK PRIMARY KEY (STUDENTID,COURSEID),
       CONSTRAINT STUDENT_STUDENT_FK FOREIGN KEY (STUDENTID) 
                         REFERENCES STUDENT(STUDENTID),
       CONSTRAINT STUDENT_COURSE_FK FOREIGN KEY (COURSEID)
                         REFERENCES COURSE(COURSEID));
    In order to "see all the students that are in a course", a way to do it would then be to join the STUDENT table with the COURSE table using the STUDENT_COURSE table on their foreign keys.

    Last edited by LKBrwn_DBA; 02-25-07 at 12:56.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  6. #6
    Join Date
    Sep 2006
    Posts
    19
    Thanks alot for the help

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •