Codeplus SS
Codeplus SS
Codeplus SS
Code:
Create database:
CREATE DATABASE RCIStudentInfoDBMS2;
USE RCIStudentInfoDBMS2;
Create table:
Student’s Table:
CREATE TABLE Students (
studentID INT auto_increment primary key,
fname varchar(255) not null,
lname varchar(255) not null,
gender enum('Male', 'Female', 'Other') not null,
bday DATE not null,
address varchar(255) not null,
phoneNum varchar(15),
email varchar(255) unique,
yearLvl INT,
foreign key (yearLvl) references Yearlvl(yearLvl)
);
Teacher’s Table
create table Teachers (
TeacherID INT auto_increment primary key,
fname varchar(255) not null,
lname varchar(255) not null,
email varchar(255) unique
);
Enrollment’s Table:
CREATE TABLE Enrollments (
enrollmentID INT auto_increment primary key,
studentID INT,
subjectID INT,
enrollmentDate DATE,
foreign key(studentID) references Students(studentID),
foreign key(subjectID) references Subjects(subjectID)
);
Grade’s Table:
create table Grade (
GradeID INT auto_increment primary key,
grade decimal(10,3),
semester varchar(20),
studentID INT,
subjectID INT,
foreign key(studentID) references Students(studentID),
foreign key(subjectID) references Subjects(subjectID)
);
Schedule’s Table:
Create table Schedule (
ScheduleID INT AUTO_INCREMENT PRIMARY KEY,
TeacherID INT,
subjectID INT,
DayOfWeek VARCHAR(20),
StartTime TIME,
EndTime TIME,
FOREIGN KEY (TeacherID) REFERENCES Teachers(TeacherID),
FOREIGN KEY (subjectID) REFERENCES Subjects(subjectID)
);
Insert Data:
Yearlvl’s Table:
INSERT INTO Yearlvl(yearName)
VALUES
('Freshman'),
('Sophomore'),
('Junior'),
('Senior');
Students’ Table:
INSERT INTO Students(fname, lname, gender, bday, address, phoneNum, email, yearLvl)
VALUES
('Rommel', 'Dela Torre', 'Male', '2000-01-10', 'Plaridel, Bulacan', '09082996818',
'[email protected]','2'),
('Janico', 'Castillo', 'Male', '2002-11-03', 'Pulilan, Bulacan', '09559856351',
'janicakes24:[email protected]','2'),
('Angelica', 'Villarisco', 'Other','2002-05-03', 'Plaridel, Bulacan', '09996589471',
'[email protected]','2');
Subjects’ Table:
INSERT INTO Subjects (subjectName, subjectDescription)
VALUES
('Information Management', 'The course covers Information Management implementation of
database design, implementation, data modeling, and SQL using relational Database Systems.'),
('Human-Computer Interaction', 'The study and design of how people interact with computers
and other digital devices, aiming to improve usability and user experience.'),
('Enterprise Architecture', 'This includes understanding how to manage data, applications, and
technology infrastructure to support organizational objectives.');
Teachers’ Table
INSERT INTO Teachers (fname, lname, email)
VALUES
('Francis Ian', 'Africa', '[email protected]'),
('Mia Grace', 'Daylo', '[email protected]'),
('Juan', 'Luna', '[email protected]');
Enrollments’ Table
INSERT INTO Enrollments (studentID, subjectID, enrollmentDate)
VALUES
(1, 1, '2024-04-25'),
(1, 2, '2024-04-25'),
(2, 1, '2024-04-25'),
(2, 2, '2024-04-25'),
(2, 3, '2024-04-25'),
(3, 1, '2024-04-25'),
(3, 2, '2024-04-25'),
(3, 3, '2024-04-25');
Grades’ Table:
INSERT INTO Grade (grade, semester, studentID, subjectID)
VALUES
('2.00', '2nd sem', 1,1),
('1.50', '2nd sem', 1,2),
('2.00', '2nd sem', 2,1),
('1.50', '2nd sem', 2,2),
('1.75', '2nd sem', 2,3),
('1.75', '2nd sem', 3,1),
('1.50', '2nd sem', 3,2),
('2.00', '2nd sem', 3,3);
Schedules’ Table:
INSERT INTO Schedule (TeacherID, subjectID, DayOfWeek, StartTime, EndTime)
VALUES
(1, 1, 'Monday', '07:00:00', '12:30:00'),
(1, 3, 'Wednesday', '10:30:00', '13:30:00'),
(2, 2, 'Saturday', '07:00:00', '10:00:00');
e. Teacher Schedule's
SELECT t.TeacherID, t.fname, t.lname, c.subjectName, sc.DayOfWeek, sc.StartTime,
sc.EndTime
FROM teachers t
JOIN schedule sc ON t.TeacherID = sc.TeacherID
JOIN subjects c ON sc.subjectID = c.subjectID
WHERE t.TeacherID = 1;