Convert ER diagram to Relational model and Normalization

creating a student marks database of a university

1. Database System



Assignment
On


Submitted To :
Sir MD. Shohel Mojumder
Lecturer, Bangladesh University
Department of CSE


Submitted By :
MD. SADIQUR RAHMAN
ID : 201531043092
Batch No : 43
Department of CSE




BANGLADESH UNIVERSITY
15/1, Iqbal Road, Mohammadpur, Dhaka-1207

2. 1

Required Data

Required data for creating a student marks database
of a university:

1. For student information each student has a unique ID.
2. Each student has a name.
3. Each student takes a Program.
4. Versity offers courses for students.
5. Offering course have Course time limit, Section number
& Room number.
6. Each Course should have a unique ID.
7. Offering Course have Semester & Year.
8. Versity takes Exam & Every exam have a specific Exam
ID.
9. Each exam has subject, exam place & exam time.
10. Finally have marks of each student for each course.

3. 2

ER Diagram

4. 3
Snapshot :
Student (Sid, Sname, Program, Sec_no)
Course_offreing (Sec_no, Time, Room, Course_no, Semester, Year)
Exam (Eid, Ename, Eplace, Etime)
Takes (Sid, Eid, makes, Sec_no)

Student
Sid Sname Program Sec_no


Course Offering
Sec_no Time Room Course_no Semester Year


Exam
Eid Ename Eplace Etime


Takes
Sid Eid Sec_no Marks

5. 4
Tables

Table made from ER diagram.

Students

Student ID. Name Program

10131 Akash CSE

10132 Karim BBA

10133 Jamail LAW

10134 Habib EEE



Exam

Exam ID. Name Place Time

43138 DBMS Mirpur 10.00 am

43139 Accounting Bobani 2.00 pm

43140 English Gulshan 12.00 pm

43141 DLD Mohakhali 10.00 am

6. 5
Course Offerings




Time Section No Room Course No Semester Year

4 months 3A 001 CSE-312 Fall 2017

4 months 3E 122 BUS-315 Fall 2016

4 months 4C 243 ENG-112 Spring 2015

4 months 5A 359 EEE-111 Summer 2016





Course taken by Students and their marks in
exams:




Student ID Course No Exam ID Marks

10131 CSE-312 43138 81

10132 BUS-315 43139 68

10133 ENG-112 43140 85

10134 EEE-111 43141 81

10134 CSE-312 43138 75

10131 BUS-315 43138 68

10132 ENG-112 43140 75

7. 6
Normalization

We will use three types of normalization:

1. First normal form(1NF)
2. Second normal form(2NF)
3. Third normal form(3NF)


Let’s begin. We can normalize the marks in exams table as it’s already in
first normal form.


Student ID Course No Exam ID Marks

10131 CSE-312 43138 81

10132 BUS-315 43139 68

10133 ENG-112 43140 85

10134 EEE-111 43141 81

10134 CSE-312 43138 75

10131 BUS-315 43138 68

10132 ENG-112 43140 75




Now it’s time for second normal form. The roles for second
normal form is:

 Table is in 1NF (First normal form)

 No non-prime attribute is dependent on the proper subset
of any candidate key of table.

8. 7
As we can see there are two candidate keys: (Students ID, Course
NO).
And a non-prime attribute: (Marks).

Because to find out a student exam mark in a specific subject we
need that subject’s course ID/ course no. So marks a non-primitive
attribute is functionally dependent on students ID and course no.
both.

We have to remove this dependency.


We will create a table with students ID and Course No.





Student ID Course No

10131 CSE-312

10132 BUS-315

10133 ENG-112

10134 EEE-111

10134 CSE-312

10131 BUS-315

10132 ENG-112

9. 8

And a table with Course No, Exam ID, Marks.





Course No Exam ID Marks

CSE-312 43138 81

BUS-315 43139 68

ENG-112 43140 85

EEE-111 43141 81

CSE-312 43138 75

BUS-315 43138 68

ENG-112 43140 75








THE END