Results 1 to 9 of 9
  1. #1
    Join Date
    Nov 2013
    Posts
    35

    Unanswered: Question Regarding SQL Developer

    Hello. I have been learning how to use Oracle and SQL Developer for the past month. I am making a database for a school and have multiple populated tables consisting of:

    STUDENT
    FACULTY
    THESIS_COMMITTEE
    SECTION
    COURSE
    COURSE_TERM
    SECTION_ENROLLMENT

    These tables are populated and can run reports and queries. I have two questions that I hope somebody can answer for me:

    1.) I have attached a picture regarding foreign keys. It says I already have a column named STUDENT_ID. I thought I am supposed to choose an existing column from another table so they connect?

    2.) Also my friend said I need to not prefix with the schema so lose all the "HR". and all the " in general. Namely taking out the schema name, quotes and storage clauses. Therefore he can run what the tool builds because he has different schema and table spaces. What exactly does this mean and how do I make these changes?

    Any help on these two questions would be greatly appreciated.
    Attached Thumbnails Attached Thumbnails Untitled.png  

  2. #2
    Join Date
    Dec 2007
    Posts
    253
    1. Change the name to student_id_fk
    2. Tell you friend to do the work himself.

  3. #3
    Join Date
    Nov 2013
    Posts
    35
    Regarding the second question. I think he wanted me to make those schema changes so he can look at what I have done. How would I take out the schema names?

  4. #4
    Join Date
    Dec 2007
    Posts
    253
    Quote Originally Posted by LaC0saNostra View Post
    Regarding the second question. I think he wanted me to make those schema changes so he can look at what I have done. How would I take out the schema names?
    How did you generate the code that included the schema names?
    ETA apart from that how the F*&*& does having the schema names there prevent him from seeing what you have done? Sorry, something doesn't scan here. I think that you are being economical with the truth when you are describing your scenario here.
    ETA Lying to someone from whom you are asking for help, is pretty rude.
    Last edited by pablolee; 11-30-13 at 18:58.

  5. #5
    Join Date
    Nov 2013
    Posts
    35
    Well that's why I am confused. He was a kid that took a C++ and OOP class with me two semesters ago. On my own I began learning Oracle and figured I could ask him questions when I had problems. I really don't know what he means either but I am not lying about my scenario.

  6. #6
    Join Date
    Jun 2004
    Posts
    796
    Provided Answers: 1
    I suspect your friend doesn't understand fully what he's doing, and is asking you to do a lot of unnecessary work so that he can get something to work for himself that he should already be able to get to work (if he knew what he was doing).
    90% of users' problems can be resolved by punching them - the other 10% by switching off their PCs.

  7. #7
    Join Date
    Nov 2013
    Posts
    35
    --------------------------------------------------------
    -- DDL for Table STUDENT
    --------------------------------------------------------

    CREATE TABLE "HR"."STUDENT"
    ( "STUDENT_ID" NUMBER(*,0),
    "LAST_NAME" CHAR(20 BYTE),
    "FIRST_NAME" CHAR(20 BYTE),
    "PHONE_NUMBER" NUMBER(*,0),
    "HOME_ADDRESS" VARCHAR2(30 BYTE),
    "YEARS_ENROLLED" CHAR(3 BYTE),
    "ADVISOR_LAST_NAME" CHAR(20 BYTE),
    "ACADEMIC_ADVISOR_ID" NUMBER(*,0),
    "THESIS_ADVISOR_ID" NUMBER(*,0),
    "ADVISOR_FIRST_NAME" VARCHAR2(35 BYTE),
    "ENROLLMENT_STATUS" VARCHAR2(20 BYTE),
    "STUDENT_FIRST_INITIAL" CHAR(10 BYTE)
    ) SEGMENT CREATION IMMEDIATE
    PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
    STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
    PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
    TABLESPACE "USERS" ;
    --------------------------------------------------------
    -- DDL for Index SYS_C007004
    --------------------------------------------------------

    CREATE UNIQUE INDEX "HR"."SYS_C007004" ON "HR"."STUDENT" ("STUDENT_ID")
    PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
    STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
    PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
    TABLESPACE "USERS" ;
    --------------------------------------------------------
    -- Constraints for Table STUDENT
    --------------------------------------------------------

    ALTER TABLE "HR"."STUDENT" ADD CONSTRAINT "STUDENT_ID" PRIMARY KEY ("STUDENT_ID")
    USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
    STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
    PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
    TABLESPACE "USERS" ENABLE;
    ALTER TABLE "HR"."STUDENT" MODIFY ("YEARS_ENROLLED" NOT NULL ENABLE);
    ALTER TABLE "HR"."STUDENT" MODIFY ("LAST_NAME" NOT NULL ENABLE);
    ALTER TABLE "HR"."STUDENT" MODIFY ("STUDENT_ID" NOT NULL ENABLE);

    This is my table for Student. Does he mean that I shouldn't have my script looking like a generator? I am curious because I actually wrote the code to this table myself

  8. #8
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    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.

  9. #9
    Join Date
    Nov 2013
    Posts
    35
    I think I may have figured it out. I think my buddy meant to export the database without the schema, storage options and create statements. It looks a lot more simple now:

    --------------------------------------------------------
    -- File created - Monday-December-02-2013
    --------------------------------------------------------
    --------------------------------------------------------
    -- DDL for Table COURSE
    --------------------------------------------------------

    CREATE TABLE "COURSE"
    ( "COURSE_ID" VARCHAR2(25),
    "STUDENT_COURSE" VARCHAR2(50),
    "TERM" CHAR(20),
    "PROFESSOR_LAST_NAME" VARCHAR2(35),
    "PROFESSOR_FIRST_INITIAL" VARCHAR2(20),
    "PREREQUISITE_ID" NUMBER(*,0),
    "PREREQUISITE_NAME" VARCHAR2(50)
    ) ;
    /
    --------------------------------------------------------
    -- DDL for Table COURSE_TERM
    --------------------------------------------------------

    CREATE TABLE "COURSE_TERM"
    ( "COURSE_TERM" NUMBER(*,0),
    "COURSE" VARCHAR2(35)
    ) ;
    /
    --------------------------------------------------------
    -- DDL for Table FACULTY
    --------------------------------------------------------

    CREATE TABLE "FACULTY"
    ( "FACULTY_ID" NUMBER(*,0),
    "FACULTY_LAST_NAME" CHAR(20),
    "FACULTY_FIRST_NAME" CHAR(20),
    "OFFICE_PHONE_NUMBER" NUMBER(*,0),
    "OFFICE_ROOM_NUMBER" NUMBER(*,0)
    ) ;
    /
    --------------------------------------------------------
    -- DDL for Table SECTION
    --------------------------------------------------------

    CREATE TABLE "SECTION"
    ( "SECTION" CHAR(35),
    "STUDENT_COURSE_FK" VARCHAR2(50),
    "INSTRUCTOR_ID" NUMBER(*,0),
    "COURSE_TERM" VARCHAR2(35)
    ) ;
    /
    --------------------------------------------------------
    -- DDL for Table SECTION_ENROLLMENT
    --------------------------------------------------------

    CREATE TABLE "SECTION_ENROLLMENT"
    ( "SECTION_ENROLLMENT_ID" NUMBER(*,0),
    "STUDENT_ID" NUMBER(*,0),
    "STATUS" VARCHAR2(10),
    "SECTION_ID" NUMBER(*,0)
    ) ;
    /
    --------------------------------------------------------
    -- DDL for Table STUDENT
    --------------------------------------------------------

    CREATE TABLE "STUDENT"
    ( "STUDENT_ID" NUMBER(*,0),
    "LAST_NAME" CHAR(20),
    "FIRST_NAME" CHAR(20),
    "PHONE_NUMBER" NUMBER(*,0),
    "HOME_ADDRESS" VARCHAR2(30),
    "YEARS_ENROLLED" CHAR(3),
    "ADVISOR_LAST_NAME" CHAR(20),
    "ACADEMIC_ADVISOR_ID" NUMBER(*,0),
    "THESIS_ADVISOR_ID" NUMBER(*,0),
    "ADVISOR_FIRST_NAME" VARCHAR2(35),
    "ENROLLMENT_STATUS" VARCHAR2(20),
    "STUDENT_FIRST_INITIAL" CHAR(10)
    ) ;
    /
    --------------------------------------------------------
    -- DDL for Table THESIS_COMMITTEE_MEMBER
    --------------------------------------------------------

    CREATE TABLE "THESIS_COMMITTEE_MEMBER"
    ( "COMMITTEE_MEMBER_LAST_NAME" CHAR(35),
    "COMMITTEE_MEMBER_FIRST_INITIAL" VARCHAR2(35),
    "CHAIR_INDICATOR" CHAR(10),
    "STUDENT_ID_FK" NUMBER(*,0),
    "FACULTY_ID_FK" NUMBER(*,0)
    ) ;
    --------------------------------------------------------
    -- Constraints for Table SECTION
    --------------------------------------------------------

    ALTER TABLE "SECTION" ADD CONSTRAINT "SECTION_ID" PRIMARY KEY ("SECTION") ENABLE;
    ALTER TABLE "SECTION" MODIFY ("STUDENT_COURSE_FK" NOT NULL ENABLE);
    ALTER TABLE "SECTION" MODIFY ("SECTION" NOT NULL ENABLE);
    /
    --------------------------------------------------------
    -- Constraints for Table COURSE
    --------------------------------------------------------

    ALTER TABLE "COURSE" ADD CONSTRAINT "COURSE_ID" PRIMARY KEY ("COURSE_ID") ENABLE;
    ALTER TABLE "COURSE" MODIFY ("STUDENT_COURSE" NOT NULL ENABLE);
    ALTER TABLE "COURSE" MODIFY ("COURSE_ID" NOT NULL ENABLE);
    /
    --------------------------------------------------------
    -- Constraints for Table THESIS_COMMITTEE_MEMBER
    --------------------------------------------------------

    ALTER TABLE "THESIS_COMMITTEE_MEMBER" MODIFY ("COMMITTEE_MEMBER_FIRST_INITIAL" NOT NULL ENABLE);
    ALTER TABLE "THESIS_COMMITTEE_MEMBER" MODIFY ("COMMITTEE_MEMBER_LAST_NAME" NOT NULL ENABLE);
    /
    --------------------------------------------------------
    -- Constraints for Table FACULTY
    --------------------------------------------------------

    ALTER TABLE "FACULTY" ADD CONSTRAINT "FACULTY_ID" PRIMARY KEY ("FACULTY_ID") ENABLE;
    ALTER TABLE "FACULTY" MODIFY ("FACULTY_LAST_NAME" NOT NULL ENABLE);
    ALTER TABLE "FACULTY" MODIFY ("FACULTY_ID" NOT NULL ENABLE);
    /
    --------------------------------------------------------
    -- Constraints for Table COURSE_TERM
    --------------------------------------------------------

    ALTER TABLE "COURSE_TERM" ADD CONSTRAINT "COURSE_TERM_ID" PRIMARY KEY ("COURSE_TERM") ENABLE;
    ALTER TABLE "COURSE_TERM" MODIFY ("COURSE_TERM" NOT NULL ENABLE);
    /
    --------------------------------------------------------
    -- Constraints for Table SECTION_ENROLLMENT
    --------------------------------------------------------

    ALTER TABLE "SECTION_ENROLLMENT" ADD CONSTRAINT "SECTION_ENROLLMENT_ID" PRIMARY KEY ("SECTION_ENROLLMENT_ID") ENABLE;
    ALTER TABLE "SECTION_ENROLLMENT" MODIFY ("STATUS" NOT NULL ENABLE);
    ALTER TABLE "SECTION_ENROLLMENT" MODIFY ("STUDENT_ID" NOT NULL ENABLE);
    ALTER TABLE "SECTION_ENROLLMENT" MODIFY ("SECTION_ENROLLMENT_ID" NOT NULL ENABLE);
    /
    --------------------------------------------------------
    -- Constraints for Table STUDENT
    --------------------------------------------------------

    ALTER TABLE "STUDENT" ADD CONSTRAINT "STUDENT_ID" PRIMARY KEY ("STUDENT_ID") ENABLE;
    ALTER TABLE "STUDENT" MODIFY ("YEARS_ENROLLED" NOT NULL ENABLE);
    ALTER TABLE "STUDENT" MODIFY ("LAST_NAME" NOT NULL ENABLE);
    ALTER TABLE "STUDENT" MODIFY ("STUDENT_ID" NOT NULL ENABLE);
    /
    --------------------------------------------------------
    -- Ref Constraints for Table COURSE_TERM
    --------------------------------------------------------

    ALTER TABLE "COURSE_TERM" ADD CONSTRAINT "COURSE_FK" FOREIGN KEY ("COURSE")
    REFERENCES "COURSE" ("COURSE_ID") ENABLE;
    /
    --------------------------------------------------------
    -- Ref Constraints for Table SECTION
    --------------------------------------------------------

    ALTER TABLE "SECTION" ADD CONSTRAINT "STUDENT_COURSE_FK" FOREIGN KEY ("STUDENT_COURSE_FK")
    REFERENCES "COURSE" ("COURSE_ID") ENABLE;
    /
    --------------------------------------------------------
    -- Ref Constraints for Table THESIS_COMMITTEE_MEMBER
    --------------------------------------------------------

    ALTER TABLE "THESIS_COMMITTEE_MEMBER" ADD CONSTRAINT "FACULTY_ID_FK" FOREIGN KEY ("STUDENT_ID_FK")
    REFERENCES "FACULTY" ("FACULTY_ID") ENABLE;
    ALTER TABLE "THESIS_COMMITTEE_MEMBER" ADD CONSTRAINT "STUDENT_ID_FK" FOREIGN KEY ("STUDENT_ID_FK")
    REFERENCES "STUDENT" ("STUDENT_ID") ENABLE;
    /

    Now I just need to continue working on my foreign keys I believe

Posting Permissions

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