Problem

Figure 2-26 shows a grade report that is mailed to students at the end of each semester....

Figure 2-26 shows a grade report that is mailed to students at the end of each semester. Prepare an ERD reflecting the data contained in the grade report. Assume that each course is taught by one instructor. Also, draw this data model using the tool you have been told to use in the course. Explain what you chose for the identifier of each entity type on your ERD.

FIGURE 2-26 Grade report

Step-by-Step Solution

Solution 1

Step #1 of 3

Entity Relationship diagram for Grade report

Entity-Relationship (E-R) diagram:

• Entity relationship diagram is basically a picture or snapshot about the business system that is the information stored, created represented in this diagram.

• Any analyst who knows about the ERD can easily determine the needs of the system and can easily read the every information stored in it.

Step #2 of 3

E-R diagram of grade report for student:

Prepare the E-R diagram based on the grade report of the student.

• The grade report contains the major parts of the student, course, instructor, and grade.

o The details of the student are student id, student name, campus address, and major.

o The details of the course are course id and title.

o The details of the instructor are instructor name and instructor location.

o The details of the grade are semester and year.

The Entity-Relationship diagram (ERD) of grade report for student is shown below:

$C:\Users\300889\AppData\Local\Microsoft\Windows\Temporary Internet Files\Content.Outlook\XNEWY8E1\10943-2-10PE_fig1.jpg$

Step #3 of 3

Explanation:

In the above diagram:

• The E-R diagram contains the four entity types such as “STUDENT”, “COURSE”, “INSTRUCTOR”, and “GRADE”.

• The relationship “Registers For” between the entity “STUDENT” and “COURSE” with “many-to-many” cardinality relationship.

• The cardinality relationship between the “COURSE” and “INSTRUCTOR” with “one-to-many” relationship.

• The Grade, Semester, and Year are the property of the relationship Registers For and this attributes are used to record the register details

Identifier for each entity types:

The above E-R diagram tells clearly that it contains the four entity types such as “STUDENT”, “COURSE”, “INSTRUCTOR”, and “GRADE”.

For “STUDENT” entity type,

• The entity “STUDENT” chooses the “Student ID” as its identifier attribute because it is unique.

• The attributes of the entity “STUDENT” is “Name”, “Address”, and “Major”.

For “COURSE” entity type,

• The entity “COURSE” chooses the “Course ID” as its identifier attribute because it is unique.

• The attributes of the entity “COURSE” is “Title”.

For “INSTRUCTOR” entity type,

• The entity “INSTRUCTOR” chooses the “Instructor Name” as its identifier attribute because it is unique.

• During the analysis work, it can be proven that the composite identifier is combined of Instructor Name and Location or create a new identifier as Instructor ID will play as unique identifier.

For “Registers For” relationship,

• The Grade, Semester, and Year are the property of the relationship Registers For.

• It is represented in rectangle box with dash line connected in relationship Registers For.

Add your Solution

Textbook Solutions and Answers Search

Solutions For Problems in Chapter 2