MadeEasy Test Series: Databases - Database Normalization

Question

Consider a database with the following relation:
Shipping (ShipName, ShipType, TripId, Cargo, Port. Date)

With the following functional dependencies:

sorry for not typing ,i will type it later 

my doubt is-isn’t partial dependency $ShipName\rightarrow ShipType$ present in new database??

Comments

adarsh_1997 yes, thanks 

---

thanks to all ,i was thinking that in relation R3 has candidate key (shipname,date) and relation R1 has  ShipName→ShipType.it will partial dependency for R3 or not??

---

In R3 isn't Trip+Port+Date a C.K.
So isn't the dependency Trip -> Shipname of type p->p i.e. 3nf ?

Answer 1

$R_1,(\text{ShipName}\rightarrow\text{ShipType})$

$R_2, (\text{TripId}\rightarrow\text{ShipName, Cargo})$

$R_3,  (\text{ShipName, Date}\rightarrow\text{TripId, Port})$ and $(\text{TripId}\rightarrow \text{ShipName})$

$R_3$ is not BCNF because in $(\text{TripId}\rightarrow \text{ShipName})$, $\text{TripId}$ is not Superkey because candidate keys are $\{\text{ShipName Date,  TripId Date}\}$

but in $R_3$ there is no partial or transitive dependency hence it satisfies $\text{3NF}$

Comments

In the question it is given : the third FD as :

ShipName, Date ->  Tripid, Port

But in ur answer u mentioned in  R3 table the FD is ShipName, Date→TripId, Cargo which is not mentioned in the set of original FDs.

---

user2525 Corrected Thanks

---

Don't you think {ShipName Date} being the superKey is the sufficient condition to say that the relation R3 is in BCNF?

Answer 2

See simle approach would be :

consider all names given Shipping (ShipName, ShipType, TripId, Cargo, Port. Date) as

R(A,B,C,D,E, F) with dependencies given as :-

A->B

C->AD

AF->CE

with candidate keys{AF AND CF)

Now coming to examine new databases below are steps:-

NOTE: again assume all names as variables like A,B,C,D,E and F

1) R1(A,B)

find candidate keys for this relation using only that dependencies that R1 uses(dependency having A and B ie.A->B  ) it would be A.

using the dependencies given we will use only those dependencies that come under our R1 ie

A->B because our relation R1 Contains R1(A,B)

check all forms one by one for this dependency – it will satisfy BCNF as A is super key also . R1 is in BCNF

2) R2(C,A,D)

find candidate keys for this relation using only that dependencies that R2 uses(dependency having C,A and D ie. C->AD ). it would be C.

using the dependencies given we will use only those dependencies that come under our R2 ie

C->AD  as we are only 3 variables in our R2 that uses this dependency

check all forms one by one for this dependency – it will satisfy BCNF as C is super key also . R2 is in BCNF

3)R3(A,F,C,E)

find candidate keys for this relation using only that dependencies that R3 uses(dependency having F,A,C and E ie. AF->CE).. it would be AF

check all forms one by one for this dependency – it will satisfy BCNF as AF is super key also . R3 is in BCNF

 

 

 

 

Comments

simplified approach :)

Answer 3

Is it BCNF???

Comments

Ya given answer is BCNF but why??

In R3 there exist TripId->ShifName so why it is in BCNF???

---

Yeah.You are right.Then it must be in 3NF

---

R3(ShipName, Date, TripId, Port)

ShipName, Date $\rightarrow$ TripId, Port

$\text{TripId} \rightarrow \text{ShipName } $ 

TripId is not a superkey hence BCNF not satisfied