Question

QUESTION: The following tables describe the content of a relational database:

a) Identify and classify the tables as either entity or relationship

The first step in building an E-R model is to identify the entities. Having identified the entities, the next step is to identify all the relationships that exist between these entities. Using the content of the relational database above:

b) Using the relations in the relational database, explain how one can transform relationship in E-R model into a separate relation.

C) Suggest a corresponding E-R diagram that captures the table structure.

Answer 1

ANSWER:--

GIVEN THAT;--

There are five relational tables in the given database. These tables are as follows.

1) Student

2) Course

3) Teacher

4) Participate

5) Teaches

a)

As per the given relational database, first three relational tables (Student, Course, and Teacher) represent separate entities. Whereas Participate table represent relationship between Student and Course as "Student Participate in a Course". Also Teaches table represent relationship between Teacher and Course as "Teacher Teaches in Course".

This is depicted by diagrams as below.

b)

As per the given relational database; Student, Course, and Teacher have their special attribute including keys like

1) Student - (studentId, name, address)

2) Course - (courseId, name, address)

3) Teacher - (teacherId,name,address)

So It is clear that studentId, courseId, and teacherId are keys of table Student, Course, and Teacher respectively that uniquely identify an instance of particular entitiy. They are have their own attributes like name and address.

Now two relations Participate and Teaches of given database represent relationship tables in database. As these are relationship tables, It is clear that both the entities (Student,Course in Participate and Teacher,Course in Teaches) involved in both these relationships (Participate,Teaches) are connected with many to many relationship. Because relationship tables are only formed when entities are connected with many to many relatioship.

i.e.

As Participate relationship table is part of database tables, show that Student and Course are forming many to many relationship. In this case Participate relationship table will contain keys from both the entities. As given Participate database table contains key's (studentId and courseId) from both (Student and Course) Entities.

Similarly Teaches relationship table is part of database tables, shows that Teacher and Course are forming many to many relationship. In this case Teaches relationship table will contain keys from both the entities. As given Teaches database table contains key's (teacherId and courseId) from both (Teacher and Course) Entities.

C)

The final E-R diagram after the above explanation is as below.