From one N to N relationship between Subject and Lecturer another table is needed.
Note that where there is a 1 to n relation ship, the primary key of one table becomes a foreign key from another table however the same is not applied to M to M relationship. Such relationship needs a new table where the primary key from this table will be composed by the primary key from the other two tables. (Refer to assessment table.)
Relational Data Model (RDM model)
-
Student (StudentID, FirstName, LastName, StartDate, Nationality, Password)
-
Administration (AdiminID, AdminName, Password, Email, [CourseID])
-
Course (CourseID, CourseName, [StudentID])
-
Subject (SubjectID, SubjectName, [CourseID])
-
Lecturer (LecturerID, LecturerFirstName, LecturerLastName, Email)
-
Assessment ( [LecturerID],[ SubjectID], AssessmentID, KnowlegeOfSubject, CommunicationSkills, punctuality, PromptnessGivenAssignment, QualityOfGuidance, QualityofHandout, OverallAssessment)
Entities and attributes:
Student
Since students must authenticate themselves to use the facility, much information is needed in order to keep the environment secure. The student ID and the Password is needed for authentication at the login page, the first name, last name and nationality is on order to user double check the person who is log in is him/her and the start date attribute is there in order to predict in which semester the student is in, enabling them to assess the right lecturers. Note that student id has student id as a primary key, it was chosen because this number is going always to be unique.
Administration
The Administration entities could have is blended into the student entity in order to have fewer tables. Once the number of tables isn’t a problem, a administration entity was created. The following attributes was given, based on the system functionalities. The Admin ID is obviously the primary key for this entity due its uniqueness Course Id is a foreign keys in this entities. The reason is because to make a link between both entities a key is needed. Either the admin id is foreign key in the course entities or course id key is foreign key in the administration entity. Since course already have a foreign key from the student entity, Administration entity had to receive the foreign key. The password attribute is for security matter, email is for reports to be sent to the admin regularly and the admin name is for matter of check whether he is that person logged in.
Course
The course entities hold only a few attributes due not much going on here. The course Id is a primary key and the student ID represents a foreign key at this stage the course name is for checking what course are to going to be assessed by the student.
Note that course name could be the primary key but for matter of keep the data consistent a course ID was introduced, the same happen to the subject entity.
Subject
Subject entity holds the subjects that have in a course. This entity has three attribute, the course ID, this attribute will uniquely identify the course, the course name is another attribute that allows the student know what subject it is and the course ID a foreign key in order to connect the tables.
Lecturer
The lecturer entity has as attributes lecturer ID, first Name, Last name and email address. The lecturer ID is the primary key of the entity and because of the relationship between lecturer and subject is many to many, the lecturer ID will go to this special table that is created due M:M relationship as foreign key. The same happen to the subject id that comes to the special table as a foreign key. Those foreign keys plus another key from the new table will form the new table primary key. The email attribute is there because the system will be able to sent reports to the lecturer when their score go below the average.
Assessment
- Knowledge Of Subject 4.Communication Skills, punctuality,
- Promptness Given Assignment, 5.Quality Of Guidance
- Quality of Handout 6.Overall Assessment
This table is result of an M: M relationship between Lecturer and Subject. Once understanding that 1-6 attributes are from lecturer and subject and there is no how to keep this data in one of those entities due data consistency, a new table had to be created.
Primary key (attribute)
Normal attribute
Foreign key (primary key from another table)
Entity relationship Diagram
Relationship between entities
The relationship between STUDENT and COURSE was created because a student must be register in a course and every course must have some student. Since every student can just only be enrolled in one course and that one course can have many students the relationship between both entities is N: 1 relationship, where N means many and 1 one.
N 1
The ADMINISTRATION entity is linked to the COURSE entity because the Administrative staff will regularly check on the lecturers and their scores regarding the subject there are teaching. Although a bit restrict for this application, there will be only one administrative member allowed to check on the courses. Therefore a 1 : N relationship where the selected person will be allow to check on the courses assesses and many courses will be check by only one person.
1 N
In this stage, COURSE entity is associated to SUBJECT entity. Since one course has many different subjects, and many subject are related to only one course, the relationship those entities is 1:N. note that it is in this phase that the student will be choosing the subjects that he/she will be assessing.
1 N
The association between the LECTURER and the SUBJECT was made because lecturers can teaches subject. Although, not everywhere but in our school, a lecturer is able to teach more than one subject and one subject can be teach by many lectures. Since the relationship between those two entities is N: N, when this happen another table is created in order to keep the data consistent and away from anomalies.
N N
Implementation
Testing
Recommendations
Conclusion
Lessons learned
References
-
Bromberg.P.A,. Database Normalization Basics for Developers. [online]. Available at http://www.eggheadcafe.com/articles/20050826.asp[accessed 18 May 2008].
-
Gray.C.F & Larson.E.W., 2006. Project Management. 3rd Ed. New York: Irwin/McGraw-Hill.
-
Schwalbe.K, 2006. Information Technology Project Management. 4th Ed. Boston: Course Technology.
-
Date, C. J. An Introduction to Database Systems, Eighth Edition, Addison Wesley, 2003.
- http://images.google.co.uk/imgres?imgurl=http://www.csuci.edu/images/redesign_images/future-students-pic.jpg&imgrefurl=http://www.csuci.edu/students/prospective.htm&h=301&w=768&sz=41&hl=en&start=20&um=1&tbnid=nEKtzPFn_NI9OM:&tbnh=56&tbnw=142&prev=/images%3Fq%3Dstudents%26start%3D18%26ndsp%3D18%26um%3D1%26hl%3Den%26sa%3DN
Appendix I
Project proposal
Final Year Project
Course: BSc. Computing Year: 2008
Name: Antonio Savio Machado Da Silva Student ID: 0257HWTR0806
Title:
Development of an Online Lecturer Assessment System
PROJECT AIM:
Develop a system that will enable student to assess their lecturers online. (To experience in real life all I have studied so far in this programme.)
PROJECT OBJECTIVES:
- Reach all the students.
- Point where lecturers need further developments.
- Be more accurate with the results.
- Keep student’s id confidential. (Deeply understand how a connection between a GUI (graphical user interface) and the database is made and how their share data between them.
- To produce reports based on the results.
SYSTEM FUNCTIONALITIES:
- Authentication of the student.
- Graphical User Interface to guide the student throughout the assessment.
- Database to store the feedbacks.
SYSTEM EXTRA FUNCTIONALITIES:
The system extra functionalities are a further step taken by me in order to push myself to learn more and more. It is a challenge because knowledge on PL/SQL is needed. Although stated on here the implementation of those functionalities will just be possible after the system is running properly.
- Sent Automatic reports to the registry.
- Sent an alert email to the lecturer if his rank goes below the average.
- Sent an alert email to the registry if the rank of a lecturer goes below the average.
REQUIRMENTS PRIOR THE PROJECT:
It is of extremely importance prior the project to start to know the entire requirement and make clear what they are. Most of the projects nowadays suffer from scope creep. This happens when the scope of the project keeps changing over the time. Although having a good and a bad side project creep usually result in cost overrun and delay (Gray and Larson, 2006).
The main requirement to know in prior the project start was:
- Identify what method is adopted by the schools to assess their lecturers.
- Identify what types of report are important to the registry.
- Identify what the formula could be used to calculate the average of the lecturers.
- Create a WBS to know whether the project is feasible and also to keep track of the project development. (Having time as constrain).
- Identify a suitable work model to use for this project. Waterfall model, spiral model, v model as example
- Identify the most convenient way to develop this system
PRACTSING WHAT I HAVE LEARNT:
- Acquire experience in Software Project Management.
- Learn about Programming languages.
- Deeply learn on databases. (Different types of database, different ways to design it.)
- How to integrate the GUI and the database together.
- Methods of authentication and how to implement them.
TECNOLOGIES TO BE USED
- ORACLE 10g (Database).
-
JDeveloper (Oracle HTTP Server, OC4J (Oracle container for java), Oracle Business Rules, Top Link, and Application Server Control).
- Microsoft World
- Microsoft Project
Project Tutor:
Nigel Kermode
Appendix II
System Interfaces
Log in
Assessment page
Do it later
Finish
Appendix II
Screen Shoots removed from Oracle 10g followed by the SQL codes.
STUDENT TABLE
STUDENT SQL
CREATE TABLE "STUDENT" // creates the table
( "STUDENTID" VARCHAR2(50) NOT NULL ENABLE, // attribute followed by the data type used.
"FIRSTNAME" VARCHAR2(50) NOT NULL ENABLE,
"LASTNAME" VARCHAR2(50) NOT NULL ENABLE,
"DATEOFBIRTHDAY" DATE NOT NULL ENABLE,
"NATIONALITY" VARCHAR2(50),
CONSTRAINT "STUDENT_PK" PRIMARY KEY ("STUDENTID") ENABLE // this line shows what primary key is to be used for this table.
)
/
CREATE OR REPLACE TRIGGER "BI_STUDENT" // creates a trigger , trigger is an event that occurs after an update, insert or delete.
before insert on "STUDENT"
for each row
begin
select "STUDENT_SEQ".nextval into :NEW.STUDENTID from dual;
end;
/
ALTER TRIGGER "BI_STUDENT" ENABLE
/
COURSE TABLE
COURSE SQL
CREATE TABLE "COURSE"
( "COURSEID" VARCHAR2(50) NOT NULL ENABLE,
"COURSENAME" VARCHAR2(50) NOT NULL ENABLE,
"STUDENTID" VARCHAR2(50) NOT NULL ENABLE,
CONSTRAINT "COURSE_PK" PRIMARY KEY ("COURSEID") ENABLE,
CONSTRAINT "COURSE_FK" FOREIGN KEY ("STUDENTID")
REFERENCES "STUDENT" ("STUDENTID") ENABLE
)
/
CREATE OR REPLACE TRIGGER "BI_COURSE"
before insert on "COURSE"
for each row
begin
select "COURSE_SEQ".nextval into :NEW.COURSEID from dual;
end;
/
ALTER TRIGGER "BI_COURSE" ENABLE
/
SUBJECT TABLE
SUBJECT SQL
CREATE TABLE "SUBJECT"
( "SUBJECTID" VARCHAR2(50) NOT NULL ENABLE,
"SUBJECTNAME" VARCHAR2(50) NOT NULL ENABLE,
"COURSEID" VARCHAR2(50) NOT NULL ENABLE,
CONSTRAINT "SUBJECT_PK" PRIMARY KEY ("SUBJECTID") ENABLE,
CONSTRAINT "SUBJECT_FK" FOREIGN KEY ("COURSEID")
REFERENCES "COURSE" ("COURSEID") ENABLE
)
/
CREATE OR REPLACE TRIGGER "BI_SUBJECT"
before insert on "SUBJECT"
for each row
begin
select "SUBJECT_SEQ".nextval into :NEW.SUBJECTID from dual;
end;
/
ALTER TRIGGER "BI_SUBJECT" ENABLE
/
LECTURER TABLE
LECTURER SQL
CREATE TABLE "LECTURER"
( "LECTURERID" VARCHAR2(50) NOT NULL ENABLE,
"LECTURERFIRSTNAME" VARCHAR2(50) NOT NULL ENABLE,
"LECTURELASTNAME" VARCHAR2(50),
CONSTRAINT "LECTURER_PK" PRIMARY KEY ("LECTURERID") ENABLE
)
/
CREATE OR REPLACE TRIGGER "BI_LECTURER"
before insert on "LECTURER"
for each row
begin
select "LECTURER_SEQ".nextval into :NEW.LECTURERID from dual;
end;
/
ALTER TRIGGER "BI_LECTURER" ENABLE
/
ASSESSMENT TABLE
ASSESSMENT SQL
CREATE TABLE "ASSESSMENT"
( "LECTURERID" VARCHAR2(50) NOT NULL ENABLE,
"SUBJECTID" VARCHAR2(50),
"ASSESSMENTID" VARCHAR2(50) NOT NULL ENABLE,
"KNOWLEGEOFTHESUBJECT" NUMBER,
"COMMUNICATIONSKILLS" NUMBER,
"PUNCTUALITY" NUMBER,
"PROPTNESSINGIVINGASSIGNMENT" NUMBER,
"QUALITYOFGUIDANCE" NUMBER,
"QUALITYOFHANDOUT" NUMBER,
"OVERALLASSESSMENT" NUMBER,
CONSTRAINT "ASSESSMENT_PK" PRIMARY KEY ("ASSESSMENTID") ENABLE,
CONSTRAINT "ASSESSMENT_FK2" FOREIGN KEY ("LECTURERID")
REFERENCES "LECTURER" ("LECTURERID") ENABLE,
CONSTRAINT "ASSESSMENT_FK3" FOREIGN KEY ("SUBJECTID")
REFERENCES "SUBJECT" ("SUBJECTID") ENABLE
)
/
CREATE OR REPLACE TRIGGER "BI_ASSESSMENT"
before insert on "ASSESSMENT"
for each row
begin
select "ASSESSMENT_SEQ".nextval into :NEW.ASSESSMENTID from dual;
end;
/
ALTER TRIGGER "BI_ASSESSMENT" ENABLE/
ADMINISTRATION TABLE
ADIMINISTRATION SQL
CREATE TABLE "ADMINISTRATION"
( "ADMINID" VARCHAR2(50) NOT NULL ENABLE,
"ADMINNAME" VARCHAR2(50) NOT NULL ENABLE,
"PASSWORD" VARCHAR2(50) NOT NULL ENABLE,
"EMAIL" VARCHAR2(50) NOT NULL ENABLE,
"COURSEID" VARCHAR2(50) NOT NULL ENABLE,
CONSTRAINT "ADMINISTRATION_PK" PRIMARY KEY ("ADMINID") ENABLE,
CONSTRAINT "ADMINISTRATION_FK" FOREIGN KEY ("COURSEID")
REFERENCES "COURSE" ("COURSEID") ENABLE
)
/
VIEWS
According to the system specifications, the administration will allow only to access the final data which are the overall rating for that lecturer in his specific subject and the overall rating of that lecturer for the whole semester. Note that sometimes one lecture teaches more then on subject in one semester so therefore that second view was created.
RATING FOR A SUBJECT
RATING FOR A SUBJECT SQL
CREATE OR REPLACE FORCE VIEW "LECTURERRATINGFORSUBJECT" ("COURSENAME", "LECTURERFIRSTNAME", "LECTURELASTNAME", "SUBJECTNAME", "KNOWLEGEOFTHESUBJECT", "COMMUNICATIONSKILLS", "PUNCTUALITY", "PROPTNESSINGIVINGASSIGNMENT", "QUALITYOFGUIDANCE", "QUALITYOFHANDOUT", "OVERALLASSESSMENT") AS // creating view
select "COURSE"."COURSENAME" as "COURSENAME",// selecting attributes from other tables
"LECTURER"."LECTURERFIRSTNAME" as "LECTURERFIRSTNAME",//such as course, lecturer
"LECTURER"."LECTURELASTNAME" as "LECTURELASTNAME",
"SUBJECT"."SUBJECTNAME" as "SUBJECTNAME",
"ASSESSMENT"."KNOWLEGEOFTHESUBJECT" as "KNOWLEGEOFTHESUBJECT",
"ASSESSMENT"."COMMUNICATIONSKILLS" as "COMMUNICATIONSKILLS",
"ASSESSMENT"."PUNCTUALITY" as "PUNCTUALITY",
"ASSESSMENT"."PROPTNESSINGIVINGASSIGNMENT" as "PROPTNESSINGIVINGASSIGNMENT",
"ASSESSMENT"."QUALITYOFGUIDANCE" as "QUALITYOFGUIDANCE",
"ASSESSMENT"."QUALITYOFHANDOUT" as "QUALITYOFHANDOUT",
"ASSESSMENT"."OVERALLASSESSMENT" as "OVERALLASSESSMENT"
from "COURSE" "COURSE",// showing where the attributes used are.
"ASSESSMENT" "ASSESSMENT",
"SUBJECT" "SUBJECT",
"LECTURER" "LECTURER"
where "SUBJECT"."SUBJECTID"="ASSESSMENT"."SUBJECTID"//the primary keys of the tables used
and "LECTURER"."LECTURERID"="ASSESSMENT"."LECTURERID"
and "SUBJECT"."COURSEID"="COURSE"."COURSEID"
/
OVERALL RATING
OVERALL RATING SQL
CREATE OR REPLACE FORCE VIEW "LECTUREROVERALLRATING" ("LECTURERFIRSTNAME", "KNOWLEGEOFTHESUBJECT", "COMMUNICATIONSKILLS", "PUNCTUALITY", "PROPTNESSINGIVINGASSIGNMENT", "QUALITYOFGUIDANCE", "QUALITYOFHANDOUT", "OVERALLASSESSMENT") AS
select "LECTURER"."LECTURERFIRSTNAME" as "LECTURERFIRSTNAME",
"ASSESSMENT"."KNOWLEGEOFTHESUBJECT" as "KNOWLEGEOFTHESUBJECT",
"ASSESSMENT"."COMMUNICATIONSKILLS" as "COMMUNICATIONSKILLS",
"ASSESSMENT"."PUNCTUALITY" as "PUNCTUALITY",
"ASSESSMENT"."PROPTNESSINGIVINGASSIGNMENT" as "PROPTNESSINGIVINGASSIGNMENT",
"ASSESSMENT"."QUALITYOFGUIDANCE" as "QUALITYOFGUIDANCE",
"ASSESSMENT"."QUALITYOFHANDOUT" as "QUALITYOFHANDOUT",
"ASSESSMENT"."OVERALLASSESSMENT" as "OVERALLASSESSMENT"
from "ASSESSMENT" "ASSESSMENT",
"LECTURER" "LECTURER"
where "LECTURER"."LECTURERID"="ASSESSMENT"."LECTURERID"
and "ASSESSMENT"."LECTURERID"="LECTURER"."LECTURERID"
/
Appendix III
Lecturer Assessment Source Code
