Reduce the ER diagram to relation table solved exercise

How to reduce an ER diagram to tables? Reduction of ERD to relation schema, Convert Entity Relationship diagram to set of tables, ERD to relation schema examples, mapping ER diagram to relational tables

Reduce (convert) the following ER diagram to relational schema

ER components	Given component	Result
Strong Entity Set Rule: Strong entity set can be directly converted into table.	(a) STUDENT (b) SUBJECT (c) CLASS	(a) STUDENT (Student_ID, Student_Name, DOB, Address)  (b) SUBJECT (Subject_ID, Subject_Name, Teacher) (C) CLASS (Class_ID, Class_Name)
Derived attribute Rule: No need to create a column in the table for derived attribute.	Age in STUDENT table	No changes
Composite attribute Rule: Replace the composite attribute with its component attributes.	Address in STUDENT table	STUDENT (Student_ID, Student_Name, DOB, Door, Street, City, Pin)
1-1, 1-n, and n-1 Relationships Rule: Include the primary key of one side entity set as the foreign key of other side entity set.	Attends (1-1 from STUDENT to CLASS) Studies (1-n from STUDENT to SUBJECT)	CLASS (Class_ID, Class_Name, Student_ID) SUBJECT (Subject_ID, Subject_Name, Teacher, Student_ID)
Descriptive attribute Rule: An attribute that is part of a relationship is descriptive. Include the descriptive attributes to 1 side as shown above.	DateOfJoin, Hours# of Attends relationship.	CLASS (Class_ID, Class_Name, Student_ID, DateOfJoin, Hours#)
Weak entity set Rule: Weak entity set is totally participated (existence dependent) on the strong entity set. Include the primary key of strong entity set into the weak entity set as foreign key.	(d) SECTION	SECTION (Section_ID, Section_Name, Class_ID)
Weak relationship Rule: No need to create as a table. If created, then the table is redundant.	Has	No changes

Final set of relation schemas: (Primary keys are underlined)

STUDENT (Student_ID, Student_Name, DOB, Door, Street, City, Pin)

CLASS (Class_ID, Class_Name, Student_ID, DateOfJoin, Hours#)

Student_ID is the foreign key refers STUDENT table

SUBJECT (Subject_ID, Subject_Name, Teacher, Student_ID)

Student_ID is the foreign key refers STUDENT table

SECTION (Section_ID, Class_ID, Section_Name)

Class_ID is the foreign key refers CLASS table

*********************

Go to Solved ER exercises page

Explain many-to-many relationship with example in DBMS

What is many-to-many relationship in DBMS, many-to-many relationship example, cardinality ratio many-to-many

Many-to-many relationship

If an entity [a record] of one entity set is associated with zero or more entities of the other entity set and vice versa then the cardinality ratio is said to be many-to-many.

When we would say the relationship is one-to-many? Assume two entity sets A and B. The relationship between A and B is many-to-many if and only if “an entity in A is associated with zero or more entities (records) in B and an entity in B is associated with zero or more entities (records) in A”. If we put in simpler terms, entity set B is the many side for A and entity set A is the many side for B.

Example:

 

In a University database let us consider two entity sets Student and Course. Assume that a student can register many courses that are offered and a course may be registered by many students per semester. Then the relationship between Student and Course will be many-to-many.

Sample many-to-many relationship between entity sets

Observe carefully from the above figure the following;

• Each student registered for zero or more courses [for example, Ravi registered for DBMS and Operating System]

• Each course is registered by zero or more students [ for example, DBMS is registered by Ravi, Mark, and Mary]

The ER diagram for this case is shown below;

Many-to-many relationship Register between entity sets Student and Course

************

Go to Important Keywords/Terms in DBMS page

Go to Advanced DBMS concepts page

Go to Some facts about relational tables page

Go to Reducing (converting) an ER diagram to schemas page

 

Explain one-to-many relationship with example in DBMS

What is one-to-many relationship in DBMS, one-to-many relationship example, cardinality ratio one-to-many

One-to-many relationship

If an entity [a record] of one entity set is associated with zero or more entities of the other entity set, then the cardinality ratio is said to be one-to-many from one side entity set to the many side entity set.

When we would say the relationship is one-to-many? Assume two entity sets A and B. The relationship is one-to-many from A to B if and only if “an entity in A is associated with zero or more entities (records) in B and an entity in B is associated with only one entity (record) in A”. If we put in simpler terms, entity set B is the many side for A and entity set A is the one side for B.

Example:

Let us assume a database for Airline Reservation System. Further assume that there are two entity sets Flights and Flight_Attendants to model the flight details and the flight attendants details respectively. If one flight can have many attendants, then this relationship can be modeled as one-to-many from Flight to Flight_Attendants.

When we say that the relationship type is either one-to-many or many-to-one, it is always important to say which side is one and which side is many to avoid ambiguity.

Sample one-to-many relationship between entity sets

Observe carefully from the above figure the following;

• Each flight is having one or more attendants. [look from flight to flight_attendant]

• Each flight attendant is allotted with exactly one flight. [look from flight_attendant to flight]

The ER diagram for this case is shown below;

One-to-many relationship Has between entity sets Flight and Flight_Attendant

This can be read as, one flight has many flight attendants and one flight attendant is attending only one flight.

 

*************

Go to Important Keywords/Terms in DBMS page

Go to Advanced DBMS concepts page

Go to Some facts about relational tables page

Go to Reducing (converting) an ER diagram to schemas page