| 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/20243/CST1600/41/16241522/Lab07/ |
Upload File : |
University Course Management
Business Logic:
Students can enroll in multiple courses.
Each student can only enroll in a course once.
Students can have multiple majors.
Courses can have multiple instructors.
Instructors may relocate to teach in different classrooms or remotely via online sessions.
Field Meanings:
StudentID: Unique identifier for each student.
StudentFullName: Full name of the student.
Majors: Codes representing the student's major(s).
CourseID: Unique identifier for each course.
CourseCode: Course identification (department code + course number).
CourseTitle: Title of the course.
InstructorID: Unique identifier for each instructor.
InstructorName: Full name of the instructor.
Classroom: Room(s) where the course is conducted.
Grade: Student's grade for the course.
Sample Data:
StudentID StudentFullName Majors CourseID CourseCode CourseTitle InstructorID InstructorName Classroom Grade
1001 Alice Thompson CS, EE 5001 CS101 Introduction to CS 2001 Mark Johnson A101 A
1002 Bob Smith ME 5002 ME201 Mechanical Design 2002 Sarah Williams B102 B+
1003 Emily Davis CE, EE 5003 CE301 Structural Engineering 2003 Daniel Brown A105 A-
1001 Alice Thompson CS, EE 5004 EE401 Electrical Circuits 2004 Emily Adams Online B
1004 James Johnson CS 5001 CS101 Introduction to CS 2001 Mark Johnson A101 A+
Normalization Steps:
UNF:
student_course_details (StudentID, StudentFullName, Majors, CourseID, CourseCode, CourseTitle, InstructorID, InstructorName, Classroom, Grade)
1NF:
Identified composite primary key (StudentID, CourseID).
Created tables: student, course, instructor, classroom, student_course.
Removed redundant fields from the main table (StudentFullName, Majors, InstructorName, Classroom).
2NF:
Evaluated composite primary keys: student_course does not have non-key fields, hence passes 2NF.
Ensured dependencies exist between primary key and non-key fields.
3NF:
Identified CourseTitle dependency on CourseCode, moved to a separate 'course_details' table.
Ensured all tables exhibit proper dependencies and eliminated transitive dependencies.
Three Data Rows:
Tables (formatted as per example provided)
These changes will be reflected across the normalized tables, ensuring each table holds specific, non-redundant information and maintains proper relationships between entitie
Tables:
student
StudentID* (Primary Key)
StudentFullName
Majors
course
CourseID* (Primary Key)
CourseCode
CourseTitle
instructor
InstructorID* (Primary Key)
InstructorName
classroom
Classroom* (Primary Key)
student_course
StudentID* (Foreign Key to student)
CourseID* (Foreign Key to course)
Grade
course_details
CourseCode* (Foreign Key to course)
CourseTitle
Three Data Rows:
student table:
yaml
| StudentID | StudentFullName | Majors |
|-----------|-----------------|-----------|
| 1001 | Alice Thompson | CS, EE |
| 1002 | Bob Smith | ME |
| 1003 | Emily Davis | CE, EE |
course table:
yaml
| CourseID | CourseCode | CourseTitle |
|----------|------------|-----------------------|
| 5001 | CS101 | Introduction to CS |
| 5002 | ME201 | Mechanical Design |
| 5003 | CE301 | Structural Engineering|
instructor table:
yaml
| InstructorID | InstructorName |
|--------------|------------------|
| 2001 | Mark Johnson |
| 2002 | Sarah Williams |
| 2003 | Daniel Brown |
classroom table:
lua
| Classroom |
|-----------|
| A101 |
| B102 |
| A105 |
| Online |
student_course table:
yaml
| StudentID | CourseID | Grade |
|-----------|----------|-------|
| 1001 | 5001 | A |
| 1002 | 5002 | B+ |
| 1003 | 5003 | A- |
course_details table:
css
| CourseCode | CourseTitle |
|------------|-----------------------|
| CS101 | Introduction to CS |
| ME201 | Mechanical Design |
| CE301 | Structural Engineering|