One-to-one: Use a foreign key to the referenced table:
student: student_id, first_name, last_name, address_id address: address_id, address, city, zipcode, student_id # you can have a
# "link back" if you need
You must also put a unique constraint on the foreign key column (addess.student_id) to prevent multiple rows in the child table (address) from relating to the same row in the referenced table (student).
-- Getting all students for a class:SELECT s.student_id, last_nameFROM student_classes scINNER JOIN students s ON s.student_id = sc.student_idWHERE sc.class_id = X-- Getting all classes for a student:SELECT c.class_id, nameFROM student_classes scINNER JOIN classes c ON c.class_id = sc.class_idWHERE sc.student_id = Y
Example
CREATETABLEClass( ClassID varchar2(10) PRIMARY KEY, Title varchar2(30), Instructor varchar2(30),Dayvarchar2(15),Timevarchar2(10));CREATETABLEStudent( StudentID varchar2(15) PRIMARY KEY,Namevarchar2(35), Major varchar2(35), ClassYear varchar2(10),Statusvarchar2(10));CREATETABLEClassStudentRelation( StudentID varchar2(15) NOT NULL, ClassID varchar2(14) NOT NULL,FOREIGN KEY (StudentID) REFERENCES Student(StudentID),FOREIGN KEY (ClassID) REFERENCES Class(ClassID),UNIQUE (StudentID, ClassID));