First step of any relational database design is to make ER Diagram for it and then convert it into relational Model.
What is relational model ?
Relational Model represents how data is stored in database in the form of table.

Lets learn step by step how to convert ER diagram into relational model

1.Entity Set:

Consider we have entity STUDENT in ER diagram with attributes Roll Number, Student Name and Class.

To convert this entity set into relational schema
1.Entity is mapped as relation in Relational schema
2.Attributes of Entity set are mapped as attributes for that Relation.
3.Key attribute of Entity becomes Primary key for that Relation.

2.Entity set with multi valued attribute:

Consider we have entity set Employee with attributes Employee ID, Name and Contact number.
Here contact number is multivalued attribute as it has multiple values. as an employee can have more than one contact number for that we have to repeat all attributes for every new contact number. This will lead to data redundancy in table.
Hence to convert entity with multivalued attribute into relational schema
separate relation is created for multivalued attribute in which
1.Key attribute and multivalued attribute of entity set becomes primary key of relation.
2.Separate relation employee is created with remaining attributes.
Due to this instead of repeating all attributes of entity now only one attribute is need to repeat.

3.Entity set with Composite attribute:

Consider entity set student with attributes Roll Number, Student Name and Class. here student name is composite attribute as it has further divided into First name, last name.
In this case to convert entity into relational schema,
composite attribute student name should not be include in relation but all parts of composite attribute are mapped as simple attributes for relation.

4. 1:M (one to many) Relationship:

Consider 1:M relationship set enrolled exist between entity sets student and course as follow,

Attributes of entity set student are Roll no which is primary key, student name and class
Attributes of entity set course are Course code which is primary key, Course name and duration
And date of enroll is attribute of relationship set enroll.
Here Enroll is 1:M relationship exist between entity set student and course which means that one student can enroll in multiple courses
In this case to convert this relationship into relational schema,
1.Separate relation is created for all participating entity sets (student and course)
2.Key attribute of Many’s side entity set (course) is mapped as foreign key in one’s side relation(Student)
3.All attributes of relationship set are mapped as attributes for relation of one’s side entity set (student)

5. M:1 (many to one) Relationship:

Consider same relationship set enroll exist between entity sets student and course . but here student is many side entity set while course is one side entity set. Which means many student can enroll in one course.

To convert this relationship set into relational schema,
1.Separate relation is created for all participating entity sets.
2.Key attribute of Many’s side entity set student is mapped as foreign key in one’s side relation
3.All attributes of relationship set are mapped as attributes for one’s side relation course.

6. M:N (many to many) Relationship:

Consider same relationship set enrolled exist between entity sets student and course ,which means multiple student can enroll in multiple courses.

To convert this Relationship set into relational schema,

1. Relationship set is mapped as separate relation
2. Key attributes of participating entity sets are mapped as primary key for that relation
3. Attribute of relationship set becomes simple attributes for that relation
4. And separate relation is created for other participating entities

7. 1:1 (one to one) Relationship:

Consider same relationship set enroll exist between entity sets student and course ,which means one student can enroll in only one courses

To convert this Relationship set into relational schema,

1. Separate relation is created for all participating entity sets.
2. Primary Key of Relation Student can be act as foreign key for relation Course OR Primary Key of Relation Course act as foreign key for relation Student.

EXAMPLE:

View My Tutorials