edited by
2,445 views
11 votes
11 votes

IMG

for all the above questions answer the following :

a ) how many minimum relation tables are required which satisfy 1NF

b) how many minimum relation tables are required which satisfy 3NF

c) how many minimum relation tables are required which satisfy BCNF

d) minimum tables required

Note: please provide detailed answer

edited by

2 Answers

Best answer
21 votes
21 votes

Here since nothing is mentioned about the attributes and keys of entities , so I will talk about INF form only as without attribute and key set we cannot formulate FDs and hence we cannot conclude about how many tables required for 2NF , 3NF etc which requires knowledge of keys and hence prime and non prime attributes.

So we talk about 1NF form only.

Now to decide minimum tables we should keep into account the cardinality of the entity sets that are participating into the relation and mode of participation i.e. total or partial.Both factors have an important role in deciding no of tables required.

For 1) :

Since it is many to one relation and participation is partial both sides of the relation , so only "many" side of the relation can be merged.Hence the tables will be : {E2R} , {E1} .Hence two tables required minimum for INF.

For 2) :

Now it is similar like previous one but the only difference is that E1 is participating totally , hence although it is in the "one " side of the relation , it can be merged.Hence in this case only 1 table required minimum.

For 3) :

Here total participation both sides hence both the entities can be merged with the relationship so only 1 table required here as well

For 4) :

Here we have total participation in "many" side of the relationship but "one" side of the relationship is participating partially .Hence the "one" side entity cannot be merged.Hence here we require 2 tables minimum. {E2R} , {E1}

For 5) :

Now here although it is a many to many relationship but total participation is here both sides of the relationship.Hence here also we require only 1 table minimum if no redundancy is considered (i.e. for 1NF only).

Note that in each of the above instances , we are merging the relationship set with either of the entity set or both of the entity sets , so no separate table is required for relationship set.

selected by
1 votes
1 votes

Any relational database is implicitly 1NF, so in most of these sums even if nothing is given, we assume that atleast it doesn’t voilate 1NF. Here, there’s no mention about functional dependencies, or even attributes. So, assuming 1NF form, which is essentially 2 properties:

  • Table has a unique Primary Key (non-null)
  • No multi-valued attribute, unique column names

Assume $K_1$ is Primary Key(PK) of $E_1$, & $K_2$ is PK of $E_2$

  1. Since both sides have partial participation, if we try to combine into single table, we might have cases where $K_1=NULL$ or $K_2=NULL$. So we can’t combine them as there’s no possible PK. Hence, we split it into two tables, and shift the relation on the many side, with PK as $K_2$. Each tuple of $E_2$ is related to exactly one $E_1$, hence no multi-valued attributes.
  2. Observe that we have full participation on the one’s side. Trying to combine into single table, we see that each value of $K_2$ can account for each row in the combined table, and doesn’t become $NULL$. Hence, we can combine in a single table with PK as $K_2$
  3. When there’s full participation on both sides, all the values of $K_1$ & $K_2$ are involved, and it’s always possible for PK to be $\{K_1,K_2\}$ for the combined table, no matter 1-1, 1-many or many-many. Hence, we can combine in a single table with PK as $\{K_1,K_2\}$.
  4. Opposite case of 2, we have full participation on $E_2$’s side. If we combine into single table, then it’s possible for $K_2=NULL$, for some $K_1$ not participating. We can’t use $K_1$ alone as PK, since it’s 1-many mapping. So, we split into two tables with the relation on the many side, with PK as $K_2$.
  5. This is also a case of full participation on both sides. Hence, we can combine in a single table with PK as $\{K_1,K_2\}$.
     

In general 

Note that in all the cases with 2 Tables, we shift the relation to the many-side.

edited by

Related questions

0 votes
0 votes
1 answer
1
Deepak Joshi asked Oct 8, 2023
553 views
A database relation R has 10 attributes (A, B, C, D,..., J)."CDEF" is known to be a candidate key of relation R. There may be other candidate keys, which are unknown. Wha...
1 votes
1 votes
0 answers
2
JAINchiNMay asked Dec 1, 2022
397 views
Any attribute(s) determining a prime attribute, automatically becomes a prime(s) attributeTrue or false
0 votes
0 votes
1 answer
4
abhinowKatore asked Sep 8, 2022
255 views
If a schedule is not conflict-serializable. Is it serializable?