locked
Junction table data insertion RRS feed

  • Question

  • Hi, here is the script of my database. The problem is I am inserting data into the tables, and I have to insert data even in the junction tables. Junction tables don't get the data (Foreign keys) auto inserted when their adjacent tables are filled.

    DataBase Creation:

             

    CREATE DATABASE SCHOOL

    USE SCHOOL


    CREATE TABLE Class 
    (
    C_ID numeric(10) NOT NULL,
    C_Name  varchar(20) NOT NULL,
    C_Level numeric(10) NOT NULL,
    C_Strength numeric(20) NOT NULL,
    CONSTRAINT CLS_PK PRIMARY KEY(C_ID)
    )

    CREATE TABLE Student
    (
     S_ID numeric(10) NOT NULL,
     C_ID numeric(10) NOT NULL,
     S_FirstName varchar(10)NOT NULL,
     S_LastName varchar(10) NOT NULL,
     S_DOB date NOT NULL,
     S_FatherName varchar(20),
     S_Guardian varchar(20) NOT NULL,
     S_City varchar(10) NOT NULL,
     S_Street varchar(10) NOT NULL,
     S_HouseNo varchar(10) NOT NULL,
     S_Contact varchar(12) NOT NULL

     CONSTRAINT STD_PK PRIMARY KEY(S_ID),
     CONSTRAINT FK_CLS_STD FOREIGN KEY(C_ID) REFERENCES Class(C_ID)
    )

    CREATE TABLE Course
    (
      CRS_ID numeric(10) NOT NULL,
      C_ID numeric(10) NOT NULL,
      C_Title varchar(20) NOT NULL,
      C_Info varchar (100),

      CONSTRAINT PK_COURSE PRIMARY KEY(CRS_ID),
      CONSTRAINT FK_CLS_CRS FOREIGN KEY(C_ID) REFERENCES Class(C_ID)
    )

    CREATE TABLE Teacher 
    (
    T_ID numeric(10) NOT NULL,
    T_FirstName varchar(20) NOT NULL,
    T_LastName varchar(20) NOT NULL,
    T_Contact numeric(20) NOT NULL,

    CONSTRAINT PK_TEACHER PRIMARY KEY(T_ID)
    )

    CREATE TABLE TEA_COURSE
    (
     T_ID NUMERIC (10) NOT NULL,
     C_ID NUMERIC (10) NOT NULL,

     CONSTRAINT PK_TEA_COURSE PRIMARY KEY(T_ID,C_ID),
     CONSTRAINT FK_TEA_TEA_COURSE FOREIGN KEY(T_ID) REFERENCES Teacher(T_ID),
     CONSTRAINT FK_CLS_TEA_COURSE FOREIGN KEY(C_ID) REFERENCES Course(CRS_ID)
    )

    CREATE TABLE CLS_TEACHER
    (
      C_ID NUMERIC (10) NOT NULL,
      T_ID NUMERIC (10) NOT NULL,

      CONSTRAINT PK_CLS_TEACHER PRIMARY KEY(C_ID,T_ID),
      CONSTRAINT FK_Class_CLS_TEACHER FOREIGN KEY (C_ID) REFERENCES Class(C_ID),
      CONSTRAINT FK_TEACHER_CLS_TEACHER FOREIGN KEY (T_ID) REFERENCES Teacher(T_ID)
    )

    CREATE TABLE Education
    (
    E_ID NUMERIC(10) NOT NULL,
    E_Name VARCHAR(20) NOT NULL,
    E_Details VARCHAR(100)

    CONSTRAINT PK_EDUCATION PRIMARY KEY(E_ID)
    )

    CREATE TABLE TEA_EDU
    (
    T_ID NUMERIC(10) NOT NULL,
    E_ID NUMERIC(10) NOT NULL,

    CONSTRAINT PK_TEA_EDU PRIMARY KEY(T_ID,E_ID),
    CONSTRAINT FK_TEACHER_TEA_EDU FOREIGN KEY(T_ID) REFERENCES Teacher(T_ID),
    CONSTRAINT FK_EDUCATION_TEA_EDU FOREIGN KEY(E_ID) REFERENCES Education(E_ID)
    )

    Insertion: Do I have to insert data into junction tables manually in order to use join on the Teacher and Course

    -- Furqan Data insertion
    INSERT INTO Course (CRS_ID,C_ID,C_Title,C_Info) VALUES(1,1,'English',' abc xyz')
    INSERT INTO Course (CRS_ID,C_ID,C_Title,C_Info) VALUES(2,1,'Urdu',' abc xyz')
    INSERT INTO Course (CRS_ID,C_ID,C_Title,C_Info) VALUES(3,1,'Math',' abc xyz')
    INSERT INTO Course (CRS_ID,C_ID,C_Title,C_Info) VALUES(4,1,'',' abc xyz')
    --Education
    INSERT INTO Education (E_ID,E_Name,E_Details) VALUES(1,'MBA','abc xyz')
    INSERT INTO Education (E_ID,E_Name,E_Details) VALUES(2,'BSCS','abc xyz')
    INSERT INTO Education (E_ID,E_Name,E_Details) VALUES(3,'Bs Maths','abc xyz')
    INSERT INTO Education (E_ID,E_Name,E_Details) VALUES(4,'Bs Physics','abc xyz')

    --Teacher
    INSERT INTO Teacher (T_ID,T_FirstName,T_LastName,T_Contact) VALUES(1,'Ahmad','Shaikh',2)
    INSERT INTO Teacher (T_ID,T_FirstName,T_LastName,T_Contact) VALUES(2,'Naeem','Butt',3)
    INSERT INTO Teacher (T_ID,T_FirstName,T_LastName,T_Contact) VALUES(3,'Shagufta','Ejaz',1)

    select E.E_ID, E.E_Name,E.E_Details, T.T_ID,T.T_FirstName from Education E inner join TEA_EDU as te on e.E_ID = te.E_ID 
    inner join Teacher T on te.T_ID = T.T_ID   

    This query give null results.

    Thursday, June 27, 2013 1:05 PM

Answers

  • Inayat,
    yes you have to manually insert the data to the junction table, because only busines knows about below things One Student belong to multiple cources and One cource belong to  multiple Students.

    for example in junction table in can put

    Inayat ,Math
    Inayat,Physica
    Inayat,English

    Etc, its not possibel to autopopulate, because in your scenario only the SCHOOL authorities knows that Inayat  has three subjects, the conclusion is, its should not auto populated. Please mark as answer if this answer looks helpful to you.


    • Edited by Parixit Thursday, June 27, 2013 1:55 PM repair
    • Proposed as answer by Christoph Muthmann Thursday, June 27, 2013 2:06 PM
    • Marked as answer by Inayat Ullah Friday, June 28, 2013 11:54 AM
    Thursday, June 27, 2013 1:54 PM