Untitled

Fold the Video

Loading...

cardinality of relationships : - shubham Ingle

converting E-R of various cardinalities into tables :                             -1

  ONE TO ONE :

converting E-R of various cardinalities into tables :                             -6

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

set theorotic perspective :

converting E-R of various cardinalities into tables :                             -9

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:

converting E-R of various cardinalities into tables :                             -11


ONE TO MANY :

converting E-R of various cardinalities into tables :                             -15

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 :

converting E-R of various cardinalities into tables :                             -17

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



MANY - ONE :

converting E-R of various cardinalities into tables :                             -23

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

converting E-R of various cardinalities into tables :                             -25


MANY TO MANY :

converting E-R of various cardinalities into tables :                             -29


converting E-R of various cardinalities into tables :                             -32

RULES OF CONSTRUCTING TABLES FROM E-R DIAGRAMS :

converting E-R of various cardinalities into tables :                             -36

Some cases :

case 1 :

converting E-R of various cardinalities into tables :                             -39

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:


converting E-R of various cardinalities into tables :                             -43


lets see if three tables is possible or not :

converting E-R of various cardinalities into tables :                             -47

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 :

converting E-R of various cardinalities into tables :                             -51

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 :

converting E-R of various cardinalities into tables :                             -56

lets see if one table is possible or not :

converting E-R of various cardinalities into tables :                             -59

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

  • In row 1 of this table we can see that E1 is related to E2 as no place is empty.
  • In row 2 we can see that E2 is not related to E1 (we can also check in set relation diagram).

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 :

converting E-R of various cardinalities into tables :                             -71

Lets see if one table is possible or not :

converting E-R of various cardinalities into tables :                             -73

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 :

converting E-R of various cardinalities into tables :                             -79

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 ?

converting E-R of various cardinalities into tables :                             -84

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 :

converting E-R of various cardinalities into tables :                             -89

Lets see if one table is possible or not :

converting E-R of various cardinalities into tables :                             -91

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 :

converting E-R of various cardinalities into tables :                             -97
converting E-R of various cardinalities into tables :                             -98
converting E-R of various cardinalities into tables :                             -99


2nd person's: doubt:

converting E-R of various cardinalities into tables :                             -103

converting E-R of various cardinalities into tables :                             -105
converting E-R of various cardinalities into tables :                             -106



Case 5 :

converting E-R of various cardinalities into tables :                             -111

Lets see if one table is possible or not ?

converting E-R of various cardinalities into tables :                             -114

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)

converting E-R of various cardinalities into tables :                             -118

this is how 2 tables are possible ,

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



Case 6:

converting E-R of various cardinalities into tables :                             -125

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

converting E-R of various cardinalities into tables :                             -129

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

converting E-R of various cardinalities into tables :                             -133

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:

converting E-R of various cardinalities into tables :                             -149

2 tables possbile or not ?

1.

converting E-R of various cardinalities into tables :                             -152

2

converting E-R of various cardinalities into tables :                             -155

so again, here we will require minimum 3 tables.



QUESTION :

converting E-R of various cardinalities into tables :                             -161

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:

converting E-R of various cardinalities into tables :                             -168

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

Last Updated:

Summarize & share videos seamlessly

Loading...