sql: convert ER diagram to Relational Mapping (multi valued attributes? composite attributes?)

Question

I am new to SQL. And I am learning PostgreSQL. This is from my homework..Just to convert a ER diagram to RM diagram. e.g a picture So my Relational mapping should be Member with name_title,name_Family name and name_Lastname and or I need to make a new "name"? I google that and I think it should be only one Member. If it's true, when I create the table, should I create table name as a foreign key of the table Member, or I dont need to that. The second question is that I dont know how to deal with the multi valued attributes. the "phone".

Answer 1

During conversion from ER Model to Relational Model: This is important:

1. How to handle Composite Attributes

2. How to handle Multi valued attributes

            **1. How to handle Composite Attributes**
   

In case of composite attributes , use atomic/simple attributes in Your table

Example: In your diagram, "name" & "license" are composite attributes and their simple/atomic attributes are (title,familyName,givenName) & (nr,expires) respectively. So use atomic/simple attributes in your table.

Note* Do not use composite attributes directly in your relation/table.Use their simple attributes.

                **2. How to handle Multi valued attributes**

In case of multi valued attributes, create a separate table for it where primary key of initial table works as a foreign key .

Example: like in your case, "phone" is a multi valued attribute, so create a separate table for it with name 'Phone' where PK of "Member" table (PK: "email") works as a foreign key in 'Phone ' table.

So finally, you have two table like below:

                           Table 1 (Member)

Attributes:

1.email (as a primary key)

2.nickname

3.since

4.birthdate

5.address

6.password

7.title

8.familyName

9.givenName

10.br

11.expires

Primary key for this table will be: (email)

                         Table 2 (Phone)

Attributes:

1.email (As a Foreign key)

2.phone

Primary key for this table will be: (email,phone)

Note* Actually this whole conversion supports 1st Normal form of database. For more detail ,you can refer database in detail