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’),
(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.
Essay Writing Service Features
Our Experience
No matter how complex your assignment is, we can find the right professional for your specific task. Contact Essay is an essay writing company that hires only the smartest minds to help you with your projects. Our expertise allows us to provide students with high-quality academic writing, editing & proofreading services.
Free Features
Free revision policy
$10Free bibliography & reference
$8Free title page
$8Free formatting
$8How Our Essay Writing Service Works
First, you will need to complete an order form. It's not difficult but, in case there is anything you find not to be clear, you may always call us so that we can guide you through it. On the order form, you will need to include some basic information concerning your order: subject, topic, number of pages, etc. We also encourage our clients to upload any relevant information or sources that will help.
Complete the order form
Once we have all the information and instructions that we need, we select the most suitable writer for your assignment. While everything seems to be clear, the writer, who has complete knowledge of the subject, may need clarification from you. It is at that point that you would receive a call or email from us.
Writer’s assignment
As soon as the writer has finished, it will be delivered both to the website and to your email address so that you will not miss it. If your deadline is close at hand, we will place a call to you to make sure that you receive the paper on time.
Completing the order and download