converting E-R of various cardinalities into tables :

cardinality of relationships : - shubham Ingle

  ONE TO ONE :

every customer is associated with exactly one driver's liscence and vice versa.

set theorotic perspective :

if we think this as a real life perspective ,there may be a customer who do not have driving liscence but ,for a liscence to be exist there must be a person associated with it and hence customer side of this relationship is partial one and liscence side is of total participation as shown in following fig:

ONE TO MANY :

conclusion : every team should be managed by exaclty 1 employee ( i.e. manager) . but there may exist some employees who do not manage any team (partial participation means 0 OR more ) . set theorotic perspective is shown below :

each line represents manages relationship. from is attribute of relation manager which represnets from which date employee is managing particular team.

MANY - ONE :

there may be a customer who dont have credit card . but for every credit card to be exist there must be a customer.

MANY TO MANY :

RULES OF CONSTRUCTING TABLES FROM E-R DIAGRAMS :

Some cases :

case 1 :

lets see whether one table is possible or not :

no attributes comes out to be a primary key if we put all attritube in one single table:

lets see if three tables is possible or not :

yes offcourse it is. as we are able to achieve primary key for every table.table of relation R have foregin keys came from tables of E1 and E2 .both of them combinely form primary key there.

now lets see whether two tables are possible or not :

yes it is possible as we can show all the relations using this two tables . as we can see in the 2nd row a1(which belongs to E1) dont have relation with b1 ( which belongs to E2) so values of B1 and C1 are kept null there. similary as we can see in second table we can store info related to b1 under attritube B1. so here, 2 tables combinely shows all relations which satisfy the E-R diagram. hence 2 tables are minimum no of tables can be formed outoff given ER .

Case 2 :

lets see if one table is possible or not :

yes as we can see its possible ,because it satisfies all 3 rules for constructing relational table from E-R .

So we can conclude that B1 will always be non empty as it is on the partial side of the relation ,also B1 is unique (already a primary key of E2) hence B1 will be the primary key of newly formed table.

There are some exceptions to this case we will see it later.

Note : If both sides of relations are having total participation then either of A1 and B1 can be primary key of newly formed table.

Case 3 :

Lets see if one table is possible or not :

As we can see none of them forms a primary key , if we put everything in one table.

Lets see if two tables are possible or not :

here we are making 2 tables such that ,first table contains A1,A2 & second table contains B1,B2,C1,A1 ..then 4 scenarios can be seen as shown below :

here both tables have attributes that uniquely defines every instance of them

first table have A1 and second table have B1 as a primary key.

what if we make other combinations of attributes to form two tables ?

as we can see in column B1 we will have 2 values corresponding to 1 value of A1 and if we try to write down 2nd value in new row then A1 will not able to keep its uniqueness, i.e. it will not no longer remain a primary key .

Case 4 :

Lets see if one table is possible or not :

As we can see one table is sufficient with B1 as primary key

now my doubt is : what we did in previous case that ,at first we took 3 scenarios where ,in 2nd scenario B1 &B2 are present and all are absent but we didnt consider this scenario here ,why?

--> i got some revalent queries from comment sections as below:

1st person's doubt :

2nd person's: doubt:

Case 5 :

Lets see if one table is possible or not ?

as we can see B1 can not be the primary key of newly formed table .so we have to see if A1 makes the primary key or not ? - as we can clearly see in set relation diagram first instance in E1 is related to 2 different instance in E2 ,So A1 can also not be considered as primary key .and ,hence showing this relationship with one table is not possible.

Lets see if two tables are possible or not ?

so like previous case we will make two tables such as primary keys of each table should be able to uniquelly identify every row. (according to navathe book , in 1-N Relationship , while making tables ,attritubes belong to 1 side of relationship are migrated to many side of the relation except in the case of 1-N relationship where total participation is on 1-side where 1 table is sufficient (refer case:4)

this is how 2 tables are possible ,

in first table A1 is primary key while in second table B1 is primary key .

Case 6:

as we can see, clearly one table will not be possible here,

lets take some combinations of 2 tables and see how it works:

1

here we can see , 1 instance of A1 is related to two 2 instances of B1 so this scenario fails to show uniqueness of instances.

2

even this case wont work because here B1 is failing to show its uniqueness.

so in this case we will require 3 tables:

1 table to show information of E1 ,1 table to show information of E2 and 1 table to show information of R .

attributes of table 1 : A1 ,A2 ; P.K. : A1

attributes of table 2 : A1,B1,C1 ;P.K. :A1,B1 (here we will have to not null constraint on both A1 and B1 because some values of A1 are not related to B1 and some values of B1 are not related to A1 so this can make primary key null which is not possible)

attributes of table 3 : B1, B2;P.K.: B1

MY DOUBT : why we can not make a single table here and make A1 B1 as combined primary key ?

my own explaination : becuase it will create too much redundancy and also it will be difficult to redraw E-R diagram from this

Case 7:

2 tables possbile or not ?

1.

2

so again, here we will require minimum 3 tables.

QUESTION :

ANS :

trivially we can make 4 tables :1 table for E1 , 1 for E2 ,1 for R1 and 1 for R2

but here we have to find minimum no of tables.

relation R1 is a 1 to many relation of partial participation at both sides ,

previously in this type of relationship we reuired minimum 2 tables,1 with E1 and 1 with primary key from E2 , so here same logic will be applied. now for R2 we will make a seperate table using attributes A1 ,B1 and D1 with primary key consist of A1 and B1 combinely .

so in total we will require minimum of 3 tables here as shown below:

i didnt understans why sir took D1 as part of primary key here