| Server IP : 134.29.175.74 / Your IP : 216.73.216.160 Web Server : nginx/1.10.2 System : Windows NT CST-WEBSERVER 10.0 build 19045 (Windows 10) i586 User : Administrator ( 0) PHP Version : 7.1.0 Disable Function : NONE MySQL : OFF | cURL : ON | WGET : OFF | Perl : OFF | Python : OFF | Sudo : OFF | Pkexec : OFF Directory : C:/nginx/html/uploads/20253/CST1600/41/16917479/Lab07/ |
Upload File : |
-- Table for students with basic details
CREATE TABLE school.student (
StudentID INT PRIMARY KEY, -- Primary key for the student table (*StudentID)
Name VARCHAR(100) NOT NULL,
HomePhone VARCHAR(15),
MobilePhone VARCHAR(15) UNIQUE -- Unique constraint on MobilePhone (^MobilePhone)
);
-- Table for majors with each major’s unique code and name
CREATE TABLE school.major (
MajorCode VARCHAR(10) PRIMARY KEY, -- Primary key for the major table (*MajorCode)
MajorName VARCHAR(50)
);
-- Relationship table connecting students to their majors
CREATE TABLE school.student_major (
StudentID INT, -- Foreign key referencing student.StudentID (@StudentID[student.StudentID])
MajorCode VARCHAR(10), -- Foreign key referencing major.MajorCode (@MajorCode[major.MajorCode])
PRIMARY KEY (StudentID, MajorCode), -- Composite primary key to ensure no duplicate pairs
FOREIGN KEY (StudentID) REFERENCES student(StudentID),
FOREIGN KEY (MajorCode) REFERENCES major(MajorCode)
);
-- Check students and their associated majors
SELECT s.StudentID, s.Name, s.HomePhone, s.MobilePhone, sm.MajorCode
FROM school.student s
JOIN school.student_major sm ON s.StudentID = sm.StudentID;
-- Check majors and the students associated with them
SELECT m.MajorCode, m.MajorName, sm.StudentID
FROM school.major m
JOIN school.student_major sm ON m.MajorCode = sm.MajorCode;