Semester Exam System Erd Essay Example

Semester Exam System Erd Essay Example Semester Exam System Erd Essay Semester Exam System Erd Essay Semester Examination System DBMS Lab Assignment Group III Srimanta Singh (004) Samik Saha (015) Prasenjit Gayen (016) 3rd yr. 1st sem. , Department of Computer Sc. Engg. Jadavpur University ASSUMPTIONS Data requirements: ? The major characteristics of the semester examination system are†¦Ã¢â‚¬ ¦.. The university has many departments which are identified basically by the department_id. Also the departments have a particular location (campus). ? Each department has teachers. Each teacher is assigned to teach a particular subject. He also has to make question paper and evaluate the answer script. WE have assumed that a teacher can take classes in different department but he will belong to a particular department. ? A particular department may offer few courses. One or more subjects may be same for different courses as well as departments. Same subject (name) in two courses or department is separately identified by the corresponding subject_id. ? Subject_id for a course of a department is unique. ? A student must register to appear in the exam. A registered student is considered as exam candidate. ? Exam can be of two types –regular and supplementary. In case of supplementary a candidate may not have to appear in all the subjects assigned to a particular semester. ? The exam controller section is the administrator of this whole examination system. ENTITY SETS USED IN E-R DIAGRAM For constructing a conceptual schema of this data base, the entity sets and the associated attributes are given below (as our specification of the E-R diagram)†¦Ã¢â‚¬ ¦Ã¢â‚¬ ¦Ã¢â‚¬ ¦Ã¢â‚¬ ¦Ã¢â‚¬ ¦Ã¢â‚¬ ¦Ã¢â‚¬ ¦Ã¢â‚¬ ¦Ã¢â‚¬ ¦.. ENTITY SETS 1. Student 2. Department 3. Teacher 4. Subject 5. Exam ASSOCIATED ATTRIBUTES Name, Roll No, Reg. No, Contact No Department_id, Name, Location Teacher_id, Name, Contact No, Status Name, Subject_id ,Course, Dept Exam_id, Roll No, Sub_id, Exam roll no. 6. Exam Candidate Semester, Year, Exam_id, Sub_id 7. Result 8. Routine Roll no, Exam_id, Sub_id, Grade, Date_of_publish Date, Time, Room No, Exam_id ? Student: Student enrolls in a course in a particular department, each student has a roll no by which they are identified uniquely, and each student also has a registration number. Each student has to enroll to appear in the examination as a candidate. A student also have also have contact number and address. ? Department: University has so many departments. Each department has a unique department_id, name. The departments have their respective location and contact number. ? Teacher Each department has many teachers. Each teacher has their corresponding teacher_id, contact no. , name and status. Each teacher is uniquely identified by the attribute teacher_id. Contact no. of the teachers may have multiple values. ? Subject: Each department offers many courses and each course consists of subjects. Here each subject have subject name, subject_id. The subjects are uniquely identified by their corresponding subject_id. Same subject can be taught in several departments but in that case the subject_id will be different. ? Exam : The whole semester examination system is organized in few exams. These exams are identified by the exam_id, semester and year in which the exam is taken. Exam may be of two types – regular and supplementary. Exam_id is the primary key of this entity. All attributes are atomic. ? Exam Candidate : Each student has to register his name for the examination. After registration for the exam he achieves the candidature for that particular exam. A candidate is uniquely identified by his exam roll no. It contains candidate name, roll no. , sub_id and exam_id. ? Result : Each exam must have their corresponding results. Result is also associated with the students. Thus the result of a student of a particular semester is identified using roll no, subject_id, exam_id. It also contains grade and date of publication of the result as attributes ? Routine : Each exam has a corresponding routine which consists of date, time, room no. and exam_id. RELATIONSHIP SETS FOR DATA BASE In our E-R diagram specified relationship sets and mapping cardinalities are described below: ? Has This is the 1-to-many binary relationship set between department and student entity sets. And the participation of the weak entity set. We also have a one-to-one binary has relationship between the exam and the exam results. ? Belongs to This is the 1-to-many binary relation ship set between DEPARTMENT and TEACHER sets. Because- for a particular department there are many teacher but for a teacher only 1 department is fixed. And the participation of the teacher entity sets in this relation ship sets is partial. ? Teaches This is the many-to-many binary relationship sets between teacher and subjects. Enrolls This is the one-to-one binary relationship sets between Student and Exam Candidate. Component attributes are: date, month, and year. ? Sits for This is a many-to-many binary relationship sets between the Exam Candidate and the Examination. ? Scheduled This is the relationship that holds between the examination and the exam routine. ? Holds This is a many-to-many binary relationship set between the department and the examination. ? ISA This is a relationship set that holds between the exam and the two category of exam-regular and supplementary. Of This relation holds between the exam and subjects as well as the subject and results. ? Gets This is the relationship sets between Exam Candidate and the Results. name dept. no. name belongs to department contact no. teacher teacher-id has of sub-id name roll no. student regd. no. contact no. dept. no. (fk) assigne d to teaches sem year exam-id exam sub-id (fk) ISA of subject name enrolls scheduled routine time room no date exam-id (fk) Sub-id regular sits for supplementary of has exam-id (fk) enrolled gets sub-id sub-id grade roll no. (fk) exam-id (fk) roll no. results Date published