Answered by:
Junction table data insertion

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_IDThis 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,EnglishEtc, 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