The Entity Relationship Diagram ERD Computer Science Essay

Published:

This essay has been submitted by a student. This is not an example of the work written by our professional essay writers.

In task 1, I have to draw the Entity Relationship Diagram (ERD) for both the logical and physical to describe the content and the data held by the department. When I analyzed the college information, I find that the department has the three main subject areas to study which are databases, information systems and artificial intelligence.

The subject area leader leads each subject area. Anyone from the staff can be a subject area leader for just one subject area. There are five undergraduate programmes offered by each subject area and each programme takes 3 years to study. There are four modules per year for each undergraduate programme. The programme leader leads each undergraduate programme. The subject area leader can also be a programme leader at the same time. Anyone from the staff can be a programme leader for a maximum of three programmes. The module leader is in charge for each module. The students can attend in full time mode or part time mode. The difference between full time mode and part time mode is the number of the modules studied per year.

The programme administrator has all the information of all students who are registered to the programme including the personal details, the programme title, the year and the module enrollment. Student progress is recorded according to the exam and the coursework and the attendance in the lectures and the seminars and the laboratory sessions. The exam and the coursework are defined for each module. All modules are compulsory and there are no optional modules. The students may interrupt their programme for a defined period of time by their personal cases. A student is awarded a pass for the module if the overall module mark is greater than 40 %. A student who passes the module is awarded thirty credits for that module and a student who fails the module is awarded zero credits for that module.

Assumption

After I finished analyzing, I draw the Entity Relationship Diagram (ERD) for both the logical and physical. I take out sixteen entities from the analyzed document. They are Subject Area, Programme, Module, Module Detail, Staff, Student, Exam, Exam Detail, Coursework, Coursework Detail, Lecture, Lecture Detail, Seminar, Seminar Detail, Laboratory and Laboratory Detail. There are five undergraduate programmes for each subject area and there is only one subject area for each programmes. So I construct one to many relationship between Subject Area entity and Programme entity.

There is a lot of staff for each subject area and there is only one subject area for each staff. So I construct one to many relationship between Subject Area entity and Staff entity. There is a lot of staff for each programme and there is only one programme for each staff. So I construct one to many relationship between Programme entity and Staff entity. There is a lot of students for each programme and there is only one programme for each student. So I construct one to many relationship between Programme entity and Student entity. There are twelve modules for each programme and there is only one programme for each module. So I construct one to many relationship between Programme entity and Module entity. There are a lot of staff for each module and there is only one module for each staff. So I construct many to many relationship between Module entity and Staff entity. There are a lot of students for each module and there are also a lot of modules for each student. So I construct many to many relationship between Module entity and Student entity. Because of the relationship is many to many, I place the dummy table named Module Detail between Student entity and Module entity.

There is one to many relationship between Module entity and Module detail entity and there is also one to many relationship between Student entity and Module Detail entity. There are a lot of exams for each module and there is only one module for each exam and so I construct one to many relationship between Module entity and Exam entity. There are a lot of courseworks for each module and there is only one module for each coursework and so I construct one to many relationship between Module entity and Coursework entity. There are a lot of students for each exam and there is also a lot of exams for each student. So I construct many to many relationship between Student entity and Exam entity. Because of the relationship is many to many, I place the dummy table named Exam Detail between Student entity and Exam entity. There is one to many relationship between Student entity and Exam Detail entity and there is also one to many relationship between Exam Detail entity and Exam entity. There are a lot of students for each coursework and there is also a lot of courseworks for each student. So I construct many to many relationship between Student entity and Coursework entity. Because of the relationship is many to many, I place the dummy table named Coursework Detail between Student entity and Coursework entity.

There is one to many relationship between Student entity and Coursework Detail entity and there is also one to many relationship between Coursework entity and Coursework Detail entity. There are a lot of students for each lecture and there are a lot of lectures for each student. So, I construct many to many relationship between Lecture entity and Student entity. Because of the relationship is many to many, I place the dummy table named Lecture Detail between Lecture entity and Student entity. There is one to many relationship between Student entity and Lecture Detail entity and there is also one to many relationship between Lecture Detail and Lecture. There are a lot of seminars for each student and there are also a lot of students for each seminar and so I construct many to many relationship between Student entity and Seminar entity. Because of the relationship is many to many, I place the dummy table named Seminar Detail between Student entity and Seminar entity. There is one to many relationship between Student entity and Seminar Detail entity and there is also one to many relationship between Seminar Detail entity and Seminar entity. There are a lot of laboratory sessions for each student and there are also a lot of students for each laboratory sessions and so I construct many to many relationship between Student entity and Laboratory entity. Because of the relationship is many to many, I place the dummy table named Laboratory Detail between Student entity and Laboratory entity. There is one to many relationship between Student entity and Laboratory Detail entity and there is also one to many relationship between Laboratory Detail entity and Laboratory entity.

Task 2

Analysis

In task 2, I have to normalize the form and define the primary keys and the foreign keys and define the fields and tables to construct the database. I analyze the forms from the colledge and I normalize the data from them.

Assumption

When I finished analyzing the data, I normalize data from four forms. They are Student Allocation Form, Exam Result Form, Coursework Result Form and Recording of Attendance Form. I take out six tables from the Student Allocation Form. They are Student table, Subject Area table, Programme table, Module table, Module Detail table and Staff table. The fields for the Student table are Student ID, Name, NRC, Sex, Father's Name, Mother's Name, Address, Phone Number, Attend Mode, Year, Start Date, End Date, Subject Area ID and Programme ID. The primary key for the student table is Student ID and the foreign keys are Subject Area ID and Programme ID. The fields for the Subject Area table are Subject Area ID, Subject Area Name and Period. The primary key for the Subject Area table is Subject Area ID and there is no foreign key for Subject Area ID. The fields for the Programme table are Programme ID, Programme Name, Fee, Start Date, End Date, Period and Subject Area ID. The primary key for the Programme table is Programme ID and the foreign key is Subject Area ID. The fields for the Module Detail table are Student ID, Module ID and Result. The Module Detail table has composite primary key, Student ID and Module ID and the foreign key is Student ID and Module ID.

The Module Detail table is the dummy table between Module table and the Student table. The fields for the Module table are Module ID, Module Name, Start Date, End Date, Period and Programme ID. The primary key for the Module table is Module ID and the foreign key is Programme ID. The fields for the Staff table are Staff ID, Staff Name, Post, Address, Phone Number, Salary, Subject Area ID, Programme ID and Module ID. The primary key for the Staff table is Staff ID and the foreign keys are Subject Area ID, Programme ID and Module ID. I take out two more tables from the Exam Result form. They are the Exam table and the Exam Detail table. The fields for the Exam table are Exam ID, Exam Title, Exam Date, Answering Time, Number of questions and Module ID. The primary key for the Exam table is Exam ID and the foreign key is Module ID. The fields for the Exam Detail table are Exam ID, Student ID, Mark and Grade. The Exam Detail table has the composite primary key, Exam ID and Student ID and the foreign keys are Exam ID and Student ID. The Exam Detail table is the dummy table between the Student table and the Exam table. I take out two more tables from the Coursework Result Form. They are the Coursework table and the Coursework Detail table. The fields for the Coursework table are Coursework ID, Coursework Title, Coursework Date, Number of Tasks, Allow Period and Module ID. The primary key for the Coursework table is Coursework ID and the foreign key for the Coursework table is Module ID. The fields for the Coursework Detail table are Corsework ID, Student ID, Mark and Grade. The Coursework Detail table has the composite primary key, Coursework ID and Student ID. The foreign keys for the Coursework Detail table are Student ID and Coursework ID. The Coursework Detail table is the dummy table between the Student table and the Coursework table. I take out another six tables form the Recording Of Attendance Form.

They are the Lecture table, the Lecture Detail table, the Seminar table, the Seminar Detail table, the Laboratory table and the Laboratory Detail table. The fields for the Lecture table are Lecture ID, Lecture Title and Date. The primary key for the Lecture table is Lecture ID and there is no foreign key for the Lecture table. The fields for the Lecture Detail table are Student ID, Lecture ID and Lecture's Attendance. The Lecture Detail table has the composite primary key, Student ID and Lecture ID. The foreign keys for the Lecture Detail table are Student ID and Lecture ID. The Lecture Detail table is the dummy table between the Student table and the Lecture table. The fields for the Seminar are Seminar ID, Seminar Title and Date. The primary key for the Seminar table is Seminar ID and there is no foreign key for the Seminar table. The fields for the Seminar Detail table are Student ID, Seminar ID and Seminar's Attendance. The Seminar Detail table has the composite primary key, Student ID and Seminar ID. The foreign keys for the Seminar Detail table are Student ID and Seminar ID. The Seminar Detail table is the dummy table between the Seminar table and the Student table. The fields for the Laboratory table are Laboratory ID, Laboratory Title and Date. The primary key for the Laboratory table is Laboratory ID and there is no foreign key for the Laboratory table. The fields for the Laboratory Detail table are Student ID, Laboratory ID and Laboratory's Attendance. The Laboratory Detail table has the composite primary key, Student ID and Laboratory ID. The foreign keys for the Laboratory Detail table are Student ID and Laboratory ID. The Laboratory Detail table is the dummy table between the Laboratory table and the Student table.

Task 3

Analysis

In the task 3, I have to set up all the normalized tables that are from the normalization that I make in the task 2 by using the Database Management System (DBMS) and I have to input some test data to the tables.

Assumption

So, I use Microsoft Access 2003 DBMS to set up the tables. When I construct the Coursework table, I define the fields such as CourseworkID, CourseworkTitle, CourseworkDate, NoOfTasks, AllowPeriod and ModuleID. The format for the CourseworkID is text and it is used to store the coursework ID. The format of the CourseworkTitle is text and it is used to store the title of the coursework. The format of the CourseworkDate is date/time and it is used to store the date of the coursework. The format of the NoOfTasks is number and it is used to store the number of tasks for each coursework. The format of the AllowPeriod is text and it is used to store the allowed period to do the coursework.

The format of the Module ID is text and it is used to store the module ID. I give the primary key to the Coursework ID. When I construct the Coursework Detail table, I define the fields such as CourseworkID, StudentID, Mark and Grade. The format for the CourseworkID is text and it is used to store the coursework ID. The format for the StudentID is text and it is used to store the student ID. The format for the Mark is number and it is used to store the mark that the student gets for each coursework. The format for the grade is text and it is used to store the grade that the student gets for each coursework. I give the composite primary key, Student ID and Coursework ID to the Coursework Detail table. When I construct the Exam table, I define the fields such as ExamID, ExamTitle, ExamDate, AnsweringTime, NoOfQuestions and ModuleID. The format for the ExamID is text and it is used to store the exam ID. The format for the ExamTitle is text and it is used to store the title of the exam. The format for the ExamDate is date/time and it is used to store the date of the exam.

The format for the AnsweringTime is text and it is used to store the answering time of the exam. The format for the NoOfQuestions is number and it is used to store the number of questions for each exam. The format for the ModuleID is text and it is used to store the module ID. I give the primary key to the Exam ID. When I construct Exam Detail table, I define the fields such as ExamID, StudentID, Mark and Grade. The format for the ExamID is text and it is used to store the exam ID. The format for the StudentID is text and it is used to store the student ID. The format for the Mark is number and it is used to store the mark that the student gets for each exam. The format for the Grade is text and it is used to store the grade that the student gets for each exam. I give the composite primary key, ExamID and StudentID to the Exam Detail table.

When I construct Laboratory table, I define the fields such as LaboratoryID, LaboratoryTitle and Date. The format for the LaboratoryID is text and it is used to store the laboratory ID. The format for the LaboratoryTitle is text and it is used to store the title of the laboratory. The format for the Date is date/time and it is used to store the date of the laboratory session. I give the primary key to the LaboratoryID. When I construct Laboratory Detail table, I define the fields such as StudentID, LaboratoryID and Laboratory'sAttendance. The format for the Student ID is text and it is used to store the student ID. The format for the LaboratoryID is text and it is used to store the laboratory ID. The format for the Laboratory'sAttendance is text and it is used to store the attendance of the student for each laboratory session. I give the composite primary key, StudentID and LaboratoryID to the Laboratory Detail table. When I construct Lecture table, I define the fields such as LectureID, LectureTitle and Date. The format for the LectureID is text and it is used to store the lecture ID. The format for the LectureTitle is text and it is used to store the title of the lecture. The format for the Date is date/time and it is used to store the date of the lecture. I give the primary key to the LectureID. When I construct Lecture Detail table, I define the fields such as StudentID, LectureID and Lecuture'sAttendance. The format for the StudentID is text and it is used to store the student ID.

The format for the LectureID is text and it is used to store the lecture ID. The format for the Lecture'sAttendance is text and it is used to store the attendance of the student for each lecture. I give the composite primary key, StudentID and LectureID to the Lecture Detail table. When I construct Module table, I define the fields such as ModuleID, ModuleName, StartDate, EndDate, Period and ProgrammeID. The format for the ModuleID is text and it is used to store the module ID. The format for the ModuleName is text and it is used to store the name of the module. The format for the StartDate is date/time and it is used to store the start date of the module. The format for the EndDate is date/time and it is used to store the end date of the module. The format for the Period is text and it is used to store the period of the module. The format for the ProgrammeID is text and it is used to store the Programme ID. I give the primary key to the ModuleID. When I construct Module Detail table, I define the fields such as StudentID, ModuleID and Result. The format for the StudentID is text and it is used to store the student ID. The format for the ModuleID is text and it is used to store the module ID. The format for the Result is text and it is used to store the result of the student for each module. I give the composite primary key, ModuleID and StudentID. When I construct Programme table, I define the fields such as ProgrammeID, ProgrammeName, Fee, StartDate, EndDate, Period and SubjectAreaID. The format for the ProgrammeID is text and it is used to store the programme ID.

The format for the ProgrammeName is text and it is used to store the name of the programme. The format for the Fee is number and it is used to store the fee for the programme. The format for the StartDate is date/time and it is used to store the start date of the programme. The format for the EndDate is date/time and it is used to store the end date of the programme. The format for the Period is text and it is used to store the period of the programme. The format for the SubjectAreaID is text and it is used to store the subject area ID. I give the primary key to the ProgrammeID. When I construct the Seminar table, I define the fields such as SeminarID, SeminarTitle and Date. The format for the SeminarID is text and it is used to store the Seminar ID. The format for the SeminarTitle is text and it is used to store the title of the title of the seminar. The format for the Date is date/time and it is used to store the date of the seminar. I give the primary key to the Seminar ID. When I construct SeminarDetail table, I define the fields such as StudentID, SeminarID and Seminar'sAttendance.

The format for the StudentID is text and it is used to store the student ID. The format for the SeminarID is text and it is used to store the seminar ID. The format for the Seminar'sAttendance is text and it is used to store the attendance of the student for each seminar. I give the composite primary key, StudentID and SeminarID to the Seminar Detail table. When I construct the Staff table, I define the fields such as StaffID, StaffName, Post, Address, PhoneNo, Salary, SubjectAreaID, ProgrammeID and ModuleID. The format for the StaffID is text and it is used to store the staff ID. The format for the StaffName is text and it is used to store the name of the staff. The format of the Post is text and it is used to store the post of the staff. The format of the Address is text and it is used to store the address of the staff. The format of the PhoneNo is text and it is used to store the phone number of the staff. The format of the Salary is number and it is used to store the salary of the staff. The format of the SubjectAreaID is text and it is used to store the subject area ID.

The format for the ProgrammeID is text and it is used to store the programme ID. The format for the ModuleID is text and it is used to store the module DI. I give the primary key to the StaffID. When I construct the Student table, I define the fields such as StudentID, Name, NRC, Sex, Father'sName, Mother'sName, Address, PhoneNo, AttendMode, Year, StartDate, EndDate, SubjectAreaID and ProgrammeID. The format for the StudentID is text and it is used to store the student ID. The format for the Name is text and it is used to store the name of the student. The format for the NRC is text and it is used to store the NRC of the student. The format for the Sex is text and it is used to store the sex of the student. The format for the Father'sName is text and it is used to store the father's name of the student. The format for the Mother'sName is text and it is used to store the mother's name of the student. The format for the Address is text and it is used to store the address of the student. The format of the PhoneNo is text and it is used to store the phone number of the student. The format of the AttendMode is text and it is used to store the attend mode whether the student is part time student or full time student. The format of the Year is number and it is used to store the year of the student. The format of the StartDate is date/time and it is used to store the start date of the student. The format of the EndDate is date/time and it is used to store the end date of the student. The format of the SubjectAreaID is text and it is used to store the subject area ID. The format of the ProgrammeID is text and it is used to store the programme ID. I give the primary key to the StudentID. When I construct the Subject Area table, I define the fields such as SubjectAreaID, SubjectAreaName and Period. The format for the SubjectAreaID is text and it is used to store the subject area ID. The format for the SubjectAreaName is text and it is used to store the name of the subject area. The format for the Period is text and it is used to store the period of the subject area. I give the primary key to the SubjectAreaID.

Task 4

Analysis

In task 4, I have to I have to set up and test the queries by using the Structured Query Language (SQL). In question 1, I have to set up the query to display all the subject area leaders, programme leaders and module leaders along with the names of the subject areas, programmes or modules they lead. In question 2, I have to set up the queries to display the list of programmes under the Artificial Intelligence subject area and I have to display the list of modules under these programmes and also the list of students who win the Pass for one of the modules. In question 3, I have to set up the query to display the student's year of study, the module they study and their exam and coursework marks and exam for these modules.

Assumption

So I create SQL statements by using Microsoft Access Database 2003. In question 1, I take out staff name and post from staff table, subject area name from the subject area table and the module name from the module table. In question 2 (a), I take out programme name from programme table where the subject area is Artificial Intelligence. In question 2 (b), I take out the module name from the module table where the modules are under the programmes of the Artificial Intelligence subject area. In question 2 (c), I take out the student name, result from the student table and the module detail table only the result is pass. In question 3, I take out student name, year, module name, exam ID, exam mark, coursework ID and coursework mark from the student table, module table, exam table, exam detail table, coursework table and coursework detail table.

Books

Database Design and Development

Systems Design

Writing Services

Essay Writing
Service

Find out how the very best essay writing service can help you accomplish more and achieve higher marks today.

Assignment Writing Service

From complicated assignments to tricky tasks, our experts can tackle virtually any question thrown at them.

Dissertation Writing Service

A dissertation (also known as a thesis or research project) is probably the most important piece of work for any student! From full dissertations to individual chapters, we’re on hand to support you.

Coursework Writing Service

Our expert qualified writers can help you get your coursework right first time, every time.

Dissertation Proposal Service

The first step to completing a dissertation is to create a proposal that talks about what you wish to do. Our experts can design suitable methodologies - perfect to help you get started with a dissertation.

Report Writing
Service

Reports for any audience. Perfectly structured, professionally written, and tailored to suit your exact requirements.

Essay Skeleton Answer Service

If you’re just looking for some help to get started on an essay, our outline service provides you with a perfect essay plan.

Marking & Proofreading Service

Not sure if your work is hitting the mark? Struggling to get feedback from your lecturer? Our premium marking service was created just for you - get the feedback you deserve now.

Exam Revision
Service

Exams can be one of the most stressful experiences you’ll ever have! Revision is key, and we’re here to help. With custom created revision notes and exam answers, you’ll never feel underprepared again.