Creating And Testing A MySQL Database For Southern Cross University Student Enrolments
Client Business Rule: –
- A Student enrol in many Courses
- A Student Enroll in Zero or many Course Enrollment.
- Each course enrollment is related to one and only one Student
- A Course has many Student
- A course has zero or many Course enrollment.
- Each course enrollment is related to one and only one Course.
- A unit teach by many Teachers
- A unit has zero or many units offered.
- Each unit offered is related to one and only one Unit.
- A teacher teaches many units
- A teacher has one or many units offered.
- Each unit offered is related to one and only one Teacher.
- A student enrolled in many units offered
- A student enrols in zero or many unit enrollments.
- Each unit enrollment is related to one and only one student.
- A unit offered has many students
- A unit offered has zero or many unit enrollments.
- Each unit enrollment is related to one and only one unit offered.
- A unit offered has one or many workshops
- Each workshop related to one and only one unit offered
- A teacher teach in one or many workshops
- Each workshop is related to one and only one teacher.
- A student enrolled in many workshops
- A student enrols in zero or many workshop enrollment.
- Each workshop enrollment is related to one and only one student.
- A workshop has many students
- A workshop has zero or many workshop enrollment.
- Each workshop enrollment is related to one and only one workshop
- A unit offered has one or many assessments
- Each assessment is related to one and only one unit offered
- A student has many assessments
- A student has zero or many student assessments.
- Each student assessment is related to one and only one student
We have not made any assumption and have just followed all the case study that was sent to us.
We have taken the same name as sent in ERD for all entities and attributes.
I selected all the data types according to the given requirement files (ERD and Excel sheet): –
|
Entity |
Attribute |
Data type |
PK, FK |
|
TEACHER |
StaffID |
INT (10) |
PK |
|
FirstName |
VARCHAR (20) |
– |
|
|
LastName |
VARCHAR (20) |
– |
|
|
Campus |
VARCHAR (30) |
– |
|
|
OfficeLocation |
VARCHAR (20) |
– |
|
|
|
VARCHAR (100) |
– |
|
|
Phone |
VARCHAR (12) |
– |
|
|
COURSE |
CourseCode |
INT (10) |
PK |
|
CourseName |
VARCHAR (200) |
– |
|
|
CreditPoint |
INT (5) |
– |
|
|
Notes |
VARCHAR (200) |
– |
|
|
UNIT |
UnitCode |
VARCHAR (10) |
PK |
|
UnitName |
VARCHAR (200) |
– |
|
|
Prerequisites |
VARCHAR (30) |
– |
|
|
Description |
VARCHAR (255) |
– |
|
|
UNIT_OFFERING |
UnitOfferingID |
INT (10) |
PK |
|
UnitCode |
VARCHAR (10) |
FK |
|
|
Year |
INT (4) |
– |
|
|
Session |
VARCHAR (10) |
– |
|
|
UnitAssessorID |
INT (10) |
FK |
|
|
WORKSHOP |
WorkShopID |
INT (10) |
PK |
|
UnitOfferingID |
INT (10) |
FK |
|
|
Campus |
VARCHAR (15) |
– |
|
|
Location |
VARCHAR (20) |
– |
|
|
TeacherID |
INT (10) |
FK |
|
|
WeekDay |
VARCHAR (15) |
– |
|
|
ClassTime |
VARCHAR (20) |
– |
|
|
STUDENT |
StudentID |
INT (10) |
PK |
|
FirstName |
VARCHAR (20) |
– |
|
|
LastName |
VARCHAR (20) |
– |
|
|
Address |
VARCHAR (200) |
– |
|
|
City |
VARCHAR (50) |
– |
|
|
State |
VARCHAR (3) |
– |
|
|
PostCode |
INT (4) |
– |
|
|
|
VARCHAR (100) |
– |
|
|
Phone |
VARCHAR (20) |
– |
|
|
COURSE_ENROLMENT |
StudentID |
INT (10) |
PK, FK |
|
CourseCode |
INT (10) |
PK, FK |
|
|
EnrolmentDate |
DATE |
– |
|
|
Status |
VARCHAR (20) |
– |
|
|
UNIT_ENROLMENT |
UnitEnrolmentID |
INT (10) |
PK |
|
StudentID |
INT (10) |
FK |
|
|
UnitOfferingID |
INT (10) |
FK |
|
|
Year |
INT (4) |
– |
|
|
Session |
INT (1) |
– |
|
|
EnrolmentType |
VARCHAR (20) |
– |
|
|
FinalGrade |
VARCHAR (30) |
– |
|
|
WORKSHOP_ENROLMENT |
WorkShopID |
INT (10) |
PK, FK |
|
StudentID |
INT (10) |
PK, FK |
|
|
ASSESSMENT |
AssessmentID |
INT (10) |
PK |
|
UnitOfferingID |
INT (10) |
FK |
|
|
AssessmentName |
VARCHAR (30) |
– |
|
|
Description |
VARCHAR (255) |
– |
|
|
DueDate |
DATE |
– |
|
|
PosssibleMarks |
INT (5) |
– |
|
|
STUDENT_ASSESSMENT |
AssessmentID |
INT (10) |
PK, FK |
|
StudentID |
INT (10) |
PK, FK |
|
|
DateSubmitted |
DATE |
– |
|
|
DaysExtension |
INT (3) |
– |
|
|
MarkAwarded |
DECIMAL (6,2) |
– |
CREATE TABLE TEACHER
StaffID INT(10) AUTO_INCREMENT PRIMARY KEY,
FirstName VARCHAR(20) NOT NULL,
LastName VARCHAR(20) NOT NULL,
Campus VARCHAR(30) NOT NULL,
OfficeLocation VARCHAR(20),
Email VARCHAR(100) NOT NULL,
Phone VARCHAR(12)
CREATE TABLE COURSE
CourseCode INT(10) PRIMARY KEY,
CourseName VARCHAR(200) NOT NULL,
CreditPoint INT(5) NOT NULL,
Notes VARCHAR(200)
CREATE TABLE UNIT
UnitCode VARCHAR(10) PRIMARY KEY,
UnitName VARCHAR(200) NOT NULL,
Prerequisites VARCHAR(30),
Description VARCHAR(200)
CREATE TABLE UNIT_OFFERING
UnitOfferingID INT(10) AUTO_INCREMENT PRIMARY KEY,
UnitCode VARCHAR(10),
Year INT(4) NOT NULL,
Session VARCHAR(10) NOT NULL,
UnitAssessorID INT(10) NOT NULL,
FOREIGN KEY (UnitAssessorID) REFERENCES TEACHER(StaffID),
FOREIGN KEY (UnitCode) REFERENCES UNIT(UnitCode)
CREATE TABLE WORKSHOP
WorkshopID INT(10) AUTO_INCREMENT PRIMARY KEY,
UnitOfferingID INT(10) NOT NULL,
Campus VARCHAR(15) NOT NULL,
Location VARCHAR(20) NOT NULL,
TeacherID INT(10) NOT NULL,
WeekDay VARCHAR(15) NOT NULL,
ClassTimes VARCHAR(20) NOT NULL,
FOREIGN KEY (TeacherID) REFERENCES TEACHER(StaffID),
FOREIGN KEY (UnitOfferingID) REFERENCES UNIT_OFFERING(UnitOfferingID)
StudentID INT(10) PRIMARY KEY,
FirstName VARCHAR(20) NOT NULL,
LastName VARCHAR(20) NOT NULL,
Address VARCHAR(200) NOT NULL,
City VARCHAR(50) NOT NULL,
State VARCHAR(3) NOT NULL,
PostCode INT(4) NOT NULL,
Email VARCHAR(100) NOT NULL,
Phone VARCHAR(20) NOT NULLUDENT(StudentID)
INSERT INTO TEACHER(FirstName, LastName, Campus, OfficeLocation, Email, Phone) VALUES
(‘Bruce’,’Armstrong’,’Coffs Harbour’,’M.G.33′,’[email protected]‘,’66593174’),
(‘Graham’,’Cooper’,’Coffs Harbour’,’M.G.32′,’[email protected]‘,’66593327’),
(‘Michelle’,’Kennedy’,’Gold Coast’,NULL,’[email protected]‘,NULL),
(‘Alex’,’Hendry’,’Gold Coast’,NULL,’[email protected]‘,NULL),
(‘Nicola’,’Jayne’,’Lismore’,NULL,’[email protected]‘,’66203118’),
(‘Raina’,’Mason’,’Gold Coast’,’A3.03′,’[email protected]‘,’755893194’),
(‘Carolyn’,’Seton’,’Gold Coast’,NULL,’[email protected]‘,NULL),
(‘Bill’,’Smart’,’Gold Coast’,’A3.06′,’[email protected]‘,’07 55893121’),
(‘Golam’,’Sorwar’,’Gold Coast’,’A3.02′,’[email protected]‘,NULL),
(‘Barry’,’Wilks’,’Coffs Harbour’,’M.G.31′,’[email protected]‘,’66593002’),
(‘Gao’,’Xiangzhu’,’Lismore’,NULL,’[email protected]‘,’66203860’),
(‘Elizabeth’,’Yuwono’,’Gold Coast’,NULL,’[email protected]‘,NULL);
(2202321,’Associate Degree of Information Technology’,192,’First 2 years of B InfTech’),
(3002111,’Bachelor of Information Technology’,288, NULL),
(3002116,’Bachelor of Applied Computing’,288,’Entry pathways from TAFE’),
(3007016,’Bachelor of Technology Education’,384,NULL),
(3707000,’Bachelor of Education’,192,’Graduate entry’);
(‘COM00207′,’Communication in Organisations’, NULL, NULL),
(‘CSC71001′,’Programming I’,NULL,NULL),
(‘CSC72001′,’Database Systems’,’ISY00243′, NULL),
(‘CSC72002′,’Programming II’,NULL, NULL),
(‘CSC00240′,’Data Communications and Networks’,NULL, NULL),
(‘CSC10210′,’Object Oriented Program Development’,’ISY00245′, NULL),
(‘CSC10214′,’Designing the User Experience’,’ISY10209′, NULL),
(‘CSC10215′,’Developing the User Experience’,’CSC10214′,NULL),
(‘CSC10216′,’Object Oriented GUI Development’,’ISY00246′, NULL),
(‘CSC10217′,’Web Development II’,’ISY10209′,NULL),
(‘ISY00243′,’Systems Analysis and Design’,NULL, NULL),
(‘ISY00246′,’Client/Server Systems’,’CSC10210′, NULL),
(‘ISY00324′,’Digital Media I: Images, Text and Interface Design’, NULL, NULL),
(‘ISY00325′,’Digital Media II: Audio-Video Resources and Linear Scriptwriting’, NULL, NULL),
(‘ISY10058’, ‘Electronic Commerce Systems’,’ISY10209′, NULL),
(‘ISY10209’, ‘Web Development I’,NULL, NULL),
(‘ISY10212′,’Contemporary Issues in Multimedia and Information Technology’, NULL, NULL),
(‘ISY10221′,’Computing Project I: Analysis and Design’,NULL, NULL),
(‘CSC00228′,’Database Systems I’, NULL, NULL),
(‘CSC00235′,’Applications Development’, NULL, NULL),
(‘ISY00245′,’Principles of Programming’, NULL, NULL),
(‘ISY10222′,’Computing Project II: Development and Implementation’,’ISY10221′, NULL),
(‘MAT10251′,’Statistical Analysis’, NULL, NULL),
(‘MNG10247′,’Managing Organisations’, NULL, NULL);
INSERT INTO UNIT_OFFERING(UnitCode, Year, Session, UnitAssessorID) VALUES
(‘COM00207′,2010,’2010-2012’,1),
(‘CSC71001′,2010,’2010-2012’,2),
(‘CSC72001′,2010,’2010-2012’,3),
(‘CSC72002′,2010,’2010-2012’,4),
(‘CSC00240′,2010,’2010-2012’,5),
(‘CSC10210′,2010,’2010-2012’,6),
(‘CSC10214′,2010,’2010-2012’,7),
(‘CSC10215′,2010,’2010-2012’,8),
(‘CSC10216′,2010,’2010-2012’,9),
(‘CSC10217′,2010,’2010-2012’,10),
(‘ISY00243′,2010,’2010-2012’,11),
(‘ISY00246′,2010,’2010-2012’,12),
(‘ISY00324′,2010,’2010-2012’,1),
(‘ISY00325′,2010,’2010-2012’,2),
(‘ISY10058′,2010,’2010-2012’,3),
(‘ISY10209′,2010,’2010-2012’,4),
(‘ISY10212′,2010,’2010-2012’,5),
(‘ISY10221′,2010,’2010-2012’,6),
(‘ISY00243′,2010,’2010-2012’,7),
(‘CSC00228′,2010,’2010-2012’,8),
(‘CSC00235′,2010,’2010-2012’,9),
(‘ISY00245′,2010,’2010-2012’,10),
(‘ISY10222′,2010,’2010-2012’,11),
(‘MAT10251′,2010,’2010-2012’,12),
(‘MNG10247′,2010,’2010-2012’,1);
INSERT INTO WORKSHOP(UnitOfferingID, Campus, Location, TeacherID, WeekDay, ClassTimes) VALUES
(3,’Gold Coast’,’Room A.123′,2, ‘Tuesday’,’9:00 – 11:00′),
(3,’Gold Coast’,’Room A.123′,2,’Tuesday’,’14:00 – 16:00′),
(3,’Gold Coast’,’Room L.8′,6,’Wednesday’,’9:00 – 11:00′),
(3,’Gold Coast’,’Room L.8′,6,’Wednesday’,’13:00 – 15:00′),
(20,’Gold Coast’,’Room A.123′,2,’Tuesday’,’14:00 – 16:00′),
(21,’Gold Coast’,’Room L.8′,6,’Wednesday’,’9:00 – 11:00′);
INSERT INTO COURSE_ENROLMENT VALUES
(32104706,2202321,’2016-02-02′,’Completed’),
(32158855,2202321,’2017-02-04′,’Enrolled’),
(32091316,2202321,’2018-02-18′,’Enrolled’),
(32091895,3002111,’2011-01-15′,’Enrolled’),
(32158787,3002111,’2017-02-12′,’Enrolled’),
(32091303,3002111,’2017-06-16′,’Enrolled’),
(32105589,3002111,’2018-03-04′,’Enrolled’),
(32150961,3002111,’2017-09-19′,’Enrolled’),
(32149163,3002111,’2018-06-17′,’Enrolled’),
(32100500,3002111,’2017-06-12′,’Deferred’),
(32095839,3002116,’2017-02-07′,’Enrolled’),
(32067540,3002116,’2015-03-01′,’Completed’),
(32120210,3002116,’2017-06-06′,’Enrolled’),
(32119001,3002116,’2017-06-02′,’Enrolled’),
(31995679,3007016,’2018-02-18′,’Enrolled’),
(31971145,3007016,’2018-02-07′,’Enrolled’),
Assumption made: –
(31908266,3707000,’2018-02-08′,’Enrolled’);
INSERT INTO UNIT_ENROLMENT (StudentID, UnitOfferingID, Year, Session, EnrolmentType, FinalGrade) VALUES
(32104706,16,2017,1,’External’,’Distinction’),
(32104706,21,2017,1,’External’,’Credit’),
(32104706,19,2017,2,’External’,’High Distinction’),
(32104706,17,2017,2,’External’,’Distinction’),
(32104706,20,2017,3,’External’,’Distinction’),
(32104706,22,2017,2,’External’,’Credit’),
(32104706,13,2018,1,’External’,NULL),
(32158855,NULL,2015,1,’on-campus’,’Credit’),
(32158855,13,2015,1,’on-campus’,’Pass’),
(32158855,19,2017,2,’on-campus’,’Fail’),
(32158855,19,2017,3,’External’,’Pass’),
(32158855,14,2018,1,’on-campus’,NULL),
(32158855,20,2018,1,’on-campus’, NULL),
(32091316,16,2015,1,’On-campus’,’Credit’),
(32091316,19,2017,2,’On-campus’,’Pass’),
(32091316,14,2017,1,’On-campus’,’Pass’),
(32091316,20,2018,1,’External’,NULL),
(32091895,16,2015,3,’External’,’Credit’),
(32091895,19,2017,2,’External’,’Distinction’),
(32091895,20,2018,1,’External’, NULL);
INSERT INTO WORKSHOP_ENROLMENT VALUES
(6,31908266),
(5,31908266),
(5,32105589),
(6,32105589);
INSERT INTO ASSESSMENT(UnitOfferingID, AssessmentName, Description, DueDate, PossibleMarks) VALUES
(20,’Online Test 1′,’Multiple choice quiz’,’2018-03-01′,5),
(20,’Online Test 2′,’Multiple choice quiz’,’2018-03-08′,5),
(20,’Online Test 3′,’Multiple choice quiz’,’2018-04-15′,5),
(20,’Assignment 1′,’Build/test Student Enrolment database’,’2018-03-29′,15),
(20,’Assignment 2A’,’EERD for 2B’,’2018-04-26′,10),
(20,’Assignment 2B’,’Build/test database’,’2018-05-10′,20),
(20,’Exam’,’Examination’,NULL,40),
(19,’Major Report’,’Systems Requirements Report’,’2018-05-01′,50),
(19,’Presentation’,’Requirements Presentation’,’2018-05-10′,10),
(19,’Exam’,’Examination’,NULL,40);
(1,32104706,’2018-03-01′,NULL,4.5),
(2,32104706,’2018-03-08′,NULL,5),
(3,32104706,’2018-04-15′,NULL,4),
(4,32104706,’2018-03-29′,NULL,12.25),
(5,32104706,’2018-04-26′,NULL,9),
(6,32104706,’2018-05-10′,NULL,18),
(8,32104706,’2018-05-01′,NULL,46),
(9,32104706,’2018-05-10′,NULL,9),
(1,32158855,’2018-03-01′,NULL,4),
(2,32158855,’2018-03-08′,NULL,4),
(3,32158855,’2018-04-15′,NULL,3.5),
(4,32158855,’2018-03-29′,NULL,11.5),
(8,32158855,’2018-05-02′,2,32.5),
(9,32158855,’2018-05-10′,NULL,7.6),
(1,32091316,’2018-03-01′,NULL,3),
(2,32091316,’2018-03-08′,NULL,2.5),
(8,32091316,’2018-05-05′,3,27.5),
(9,32091316,’2018-05-10′,NULL,6.5),
(8,32091895,’2018-05-05′,4,36.2),
(9,32091895,’2018-05-10′,NULL,7.2);
Create a query to display the workshop enrolment lists for all students enrolled in Session 1, 2018. The result should include the session, unit code, unit name, campus location, workshop day & time, tutor name, student number and student name. The result should be sorted by the unit code, campus location, workshop day and time then student last name and first name.
select unit_enrolment.Session, unit.UnitCode,unit.UnitName, workshop.Campus,workshop.Location, workshop.WeekDay,workshop.ClassTimes,
concat(teacher.FirstName,’ ‘,teacher.LastName) as ‘Teacher name’,student.StudentID,concat(student.FirstName,’ ‘,student.LastName) as ‘Student name’
from student,workshop,workshop_enrolment,unit_offering,unit_enrolment,unit,teacher
where workshop.WorkshopID=workshop_enrolment.WorkshopID AND
student.StudentID=workshop_enrolment.StudentID and
unit_offering.UnitOfferingID=unit_enrolment.UnitOfferingID AND
workshop.TeacherID=teacher.StaffID and
unit.UnitCode=unit_offering.UnitCode and
unit_enrolment.Year=2017 and unit_enrolment.Session=1
order by unit.UnitCode, workshop.Campus,workshop.Location,workshop.WeekDay,workshop.ClassTimes,
student.LastName,student.FirstName;
Create a query to display the number of students who were enrolled in workshops in all sessions for 2017. The result should include the session, unit code, unit name, campus location, tutor name and the total count of students enrolled. The result should be sorted by the Session, unit code, campus location and workshop day & time.
select unit_enrolment.Session, unit.UnitCode,unit.UnitName, workshop.Campus,workshop.Location,
concat(teacher.FirstName,’ ‘,teacher.LastName) as ‘Tutor name’,count(student.StudentID) AS ‘total count of students enrolled’
from student,workshop,workshop_enrolment,unit_offering,unit_enrolment,unit,teacher
where workshop.WorkshopID=workshop_enrolment.WorkshopID AND
student.StudentID=workshop_enrolment.StudentID and
unit_offering.UnitOfferingID=unit_enrolment.UnitOfferingID AND
workshop.TeacherID=teacher.StaffID and
unit.UnitCode=unit_offering.UnitCode and
unit_enrolment.Year=2017
group by unit_enrolment.Session, unit.UnitCode,unit.UnitName, workshop.Campus,workshop.Location,
concat(teacher.FirstName,’ ‘,teacher.LastName);
Create a query to display the information required for a Student Transcript. The result should include the student number, student name, year, session, unit
select student.StudentID, concat(student.FirstName,’ ‘,student.LastName) as ‘Student name’, unit_enrolment.Year, unit.UnitCode,unit.UnitName
from student,workshop,workshop_enrolment,unit_offering,unit_enrolment,unit,teacher
where workshop.WorkshopID=workshop_enrolment.WorkshopID AND
student.StudentID=workshop_enrolment.StudentID and
unit_offering.UnitOfferingID=unit_enrolment.UnitOfferingID AND
workshop.TeacherID=teacher.StaffID and
unit.UnitCode=unit_offering.UnitCode;
Create a query to display the total marks for each student’s assessments in all sessions for 2017. The result should include the student names, unit code, the sum of marks given for their assessments and the final grade given. It should be sorted by the student’s last name and first name, session, and unit code. Note: the final grade is entered by the unit assessor into the database, not calculated automatically from the total.
select concat(student.FirstName,’ ‘,student.LastName) as ‘Student name’,unit.UnitCode,
sum(STUDENT_ASSESSMENT.MarkAwarded) as ‘sum of marks’ , unit_enrolment.FinalGrade
from student, unit_offering,unit_enrolment,unit, assessment,student_assessment
where assessment.AssessmentID=student_assessment.AssessmentID AND
student_assessment.StudentID=student.StudentID and
STUDENT.StudentID=UNIT_ENROLMENT.StudentID and
unit_offering.UnitOfferingID=unit_enrolment.UnitOfferingID AND
unit.UnitCode=unit_offering.UnitCode
Group by concat(student.FirstName,’ ‘,student.LastName),unit.UnitCode, unit_enrolment.FinalGrade
ORDER BY STUDENT.LastName, Student.FirstName, Unit_Enrolment.Session, Unit.UnitCode ;
References: –
Awad, E., and Gotterer, M., (1992). Database management. Danvers, Mass.: Boyd & Fraser Pub. Co.
Butler, T., and Yank, K., (2017). PHP and MySQL. Victoria: SitePoint Pty, Limited.
Date, C., and White, C., (1990). A guide to DB2. Reading, Mass.: Addison-Wesley.
Dubois, P., (2005). MySQL. Madrid: Anaya Multimedia.
Dubois, P., (2013). MySQL. Upper Saddle, NJ: Addison-Wesley.
Gilmore, W., (2010). Beginning PHP and MySQL. New York: Apress.
Kofler, M., and Kramer, D., (2001). MySQL. Berkeley: Apress.
Kofler, M., and Kramer, D., (2008). MySQL. Berkeley, CA: Apress L. P.
Larson, J., (1982). Database management system anatomy. Lexington, Mass.: Lexington Books.
Li, D., (1987). A PROLOG database system. Letchworth: Research Studies Press.
MacWhinney, B., (2000). The database. Mahwah, NJ [u.a.]: Lawrence Erlbaum.
Ramarkrishnan, R., (1997). Database management system. London: McGraw-Hill Pub. Co. (ISE Editions).
Schwartz, B., Zaitsev, P., and Tkachenko, V., (2012). High-Performance MySQL. Beijing: O’Reilly.
Silberschatz, A., Korth, H., and Sudarshan, S., (2011). Database system concepts. New York: McGraw.
Ullman, L., (2006). MySQL. Berkeley, Calif.: Peachpit.