2.6. Binary Vs Ternary Relationships¶

This section is devoted for:

• proving the fact that *The Ternary relationship is not equivalent to multiple binary relationships.

• Improving readers’ understanding for ternary relationships.

• When ternary relationship is required and when it isn’t?

This is achieved by: presenting two problem statements for two different stores’ policies of the same application domain.

The design solution of each problem should relate the same three entities. As the two stores have different working policies, so they have to use different ways to relate those entities so as to comply with the requirements of each store.

After presenting the problems, the reader will find that; the solution of the first problem (Store X) requires the use of a ternary relationship between the three entities, whereas the solution of the second problem (Store Y) requires the use of two binary relationships between the same three entities.

For each problem statement: $(Store$ $X$ $&$ $Store$ $Y$ $problems)$, all possible solutions that the designer may think of will be analysed starting with the wrong ones and finally presents the right solution. This aims to better understand the drawbacks of the wrong designs. And helps the reader to identify the circumstances in which a ternary relationship between three entities is required.

2.6.1. General Problem Statement¶

• Both solutions require definning three entities (distributor, product, country).

• The distributor entity is identified by distributor number and has name attribute.

• Country is identified by country number and and has name attribute

• Product is identified by product number and also has name attribute.

Tip: Before start watching the visualizations you should obey all step by step instructions given in the slides for better understanding.

General problem statement and Store X first solution illustration:

1 / 57 Settings

<<<>>>

Here are the problems' statments

Store X: (global competition)

Store X has a lot of distributors to sell their several products around the world. The store's policy states that each distributor is allowed to sell the store’s products in each country. And also multiple distributors are allowed to sell products in the same country. In this case each distributor can set different price for each product type.

Store Y: (exclusive distributor)

Store Y has a lot of distributors to sell their several products around the world. The store's policy states that each country has only one distributor who sells all the store’s products exclusively. A distributor is permitted to distribute in many countries. The price of each product is set differently for each country.

Distributer

1. Dist-id
2. Dist-name

1. 1
2. Ahmed

1. 2
2. ali

1. 3
2. adel

1. 4
2. hany

1. 5
2. wael

Country

1. Country-id
2. Coun-name

1. A
2. Egypt

1. B
2. USA

1. C
2. KSA

1. D
2. UK

1. E
2. China

Product

1. Pro-id
2. Pro-name

1. F512
2. blinder

1. G920
2. drier

1. K344
2. Fridge

1. Z212
2. mixer

1. Z302
2. watch

1

M

M

1

M

M

1

1

N

N

M

M

M

1

N

1

M

1

M

M

1

1

M

1- Defining main entities and their attributes

2- Determine any additional (i.e. relational attributes) to be added to solution

3- Identify example dataset containing all cases defined in the problem specification

Note: Some detailed attributes are removed from example dataset for illustration

4- Discuss pros and cons of all possible solutions to determine the correct one

Note: All suggested solutions will be tested by using example dataset defined in step 3

Distributor

d_no.

d_name

Country

C_no.

C_name

Product

P_no.

P_name

As mentioned in both problems of store X and store Y specifications:

Store X problem states that In this case each distributor can set different price for each product type.

Store Y problem states that The price of each product is set differently for each country.

Then: price should be a relational attribute as its value depends on more that one entity or the relation between entities as stated in both problems (e.g. price value for a product is determined by specific distributor for specific country)

Store X test dataset Example:

1. Country
2. Product
3. Distributer
4. Price

1. Egypt
2. TV
3. ali
4. 3000

1. Egypt
2. watch
3. ali
4. 5000

1. Egypt
2. blender
3. adam
4. 2500

1. Egypt
2. blender
3. tarek
4. 2300

1. USA
2. TV
3. adam
4. 4500

1. USA
2. watch
3. ali
4. 4700

1. USA
2. blender
3. tarek
4. 2000

1. UK
2. TV
3. adam
4. 4000

1. UK
2. blender
3. adam
4. 3000

Store Y test dataset Example:

1. Country
2. Product
3. Distributer
4. Price

1. Egypt
2. TV
3. ali
4. 3000

1. Egypt
2. watch
3. ali
4. 5000

1. UK
2. blender
3. ali
4. 2500

1. UK
2. watch
3. ali
4. 2300

1. USA
2. TV
3. adam
4. 4500

1. USA
2. watch
3. adam
4. 4700

1. USA
2. blender
3. adam
4. 2000

1. lebanon
2. TV
3. morad
4. 4000

1. lebanon
2. blender
3. morad
4. 3000

Note1:

Note1: Certain technical attributes (such as the product number, distributor number) have been omitted from these datasets to keep it clear and simple

Note2:

Note2: This data will be used for testing suggested solutions (i.e. The correct solution for each store is the one that can represent & retrive all information in the store's dataset given here)

Note3:

Note3: There is a difference between the datasets' of stores X and Y, which represents the two different policies of the stores according to their specifications given in the problem

Note4:

Note4: The dataset of store X reflects its policy where each country has many distributors and each distributor can sell different products in many countries with different prices determined by him. (as shown in stores X dataset above)

For Example IN store X: USA country has three different distributors (adam, ali, tarek) . (as shown in stores X dataset above)

Note4Continue: while store Y policy is assigning only one distributor for each country, but each distributor can sell products in many countries, prices of same products differ according to each country. (as shown in stores Y dataset above)

But in Store Y Example: Each country (e.g. USA) must have only one distributor for all the product sold there (e.g. adam). (as shown in stores Y dataset above)

And In store X: distributor adam works for three different countries (Egypt, USA, UK) . (as shown in stores X dataset above)

And also In store Y: distributor can work in many countries (e.g.) distributor ali works for two countries (Egypt, UK). (as shown in stores Y dataset above)

Finally In store X : Prices for same product (as TV)are set differently by different distributors in different countries. (as shown in stores X dataset above)

Dataset shows that: ali sold TV in Egypt with 3000

And: adam sold the same TV in USA with 4500

And also: The same distributor adam sold the same TV in other different country UK with different price 4000

While In store Y policy : Price of product (as TV) is determined according to the country where the product is sold regardless of who will sell the product as each country has only one distributor. (as shown in stores X dataset above)

Note: TV product has three different prices one for Egypt , one for USA , and third price for lebanon

In store Y policy : each product has only one price per country as it is only sold by one distributer. ( look at TV in Egypt in store Y dataset above)

The case in store X policy is different : each product can has more than one price per country if it is sold by many distributers in the same country. ( look at blender in Egypt in store X dataset above)

Conclusion & Problem Summary:

STORE X-----> each country ----->many distributors THEN product price-----> based on -----> (distributor+country) SO many prices/distributor/country

STORE Y-----> each country ----->one distributor THEN product price-----> based on -----> (country) SO one price/country

WARNNING: -----> Think, Think, Think alone

Try to think of answers for both problems & write them down in your paper before jumping to next slide.

Is your solution one of the suggested solutions in the next slide??

If so wait to know weather your solution is right or wrong.

STORE X Suggested Solutions

First Sol.: Two binary relations (N:M country/product relation including price attribute) & (N:M country/distributor)

Distributor

d_no.

d_name

Country

C_no.

C_name

Bridge

C_no.

P_no.

Price

Bridge

d_no.

P_no.

Price

Bridge

d_no.

p_no.

c_no.

c_no.

price

Product

p_no.

p_name

Second Sol.: Two binary relations (N:M distributor/product relation including price attribute) & (N:M country/distributor)

Third Sol.: Three binary relations (N:M country/product including price attribute) , (N:M product/distributor) & (N:M distributor/country)

Distributor

d_no.

d_name

Forth Sol.: One ternary relation (N:M:P country/product/distributor relation including price attribute)

STORE Y Suggested Solutions

First Sol.: One ternary relation (N:M:P country/product/distributor relation including price attribute) (same as store X forth solution)

Second Sol.: One ternary relation (1:N:M country/distributor/product relation including price attribute) (same as pervious solution except in 1 cardinality)

Third Sol.: Three binary relations (N:M country/product including price attribute) , (N:M product/distributor) & (1:M distributor/country)

Forth Sol.: Two binary relations (N:M country/product relation including price attribute) & (1:M distributor/country)

RIGHT SOLUTIONS ARE:

Forth solution for Store X

Forth solution for Store Y

WHY??

NOTE: any N:M relationship in the suggested solutions will be substituted with its corresponding bridge in the next slides for clear demonstration

Store X First Solution Analysis

1. Country
2. Product
3. Distributer
4. Price

1. Egypt
2. TV
3. ali
4. 3000

1. Egypt
2. watch
3. ali
4. 5000

1. Egypt
2. blender
3. adam
4. 2500

1. Egypt
2. blender
3. tarek
4. 2300

1. USA
2. TV
3. adam
4. 4500

1. USA
2. watch
3. ali
4. 4700

1. USA
2. blender
3. tarek
4. 2000

1. UK
2. TV
3. adam
4. 4000

1. UK
2. blender
3. adam
4. 3000

These are tables representing first solution relations, try to fill tables with records in
dataset to see weather these relations can represent all records in dataset or not.

Distributor

1. D-no
2. D-name

1. d1
2. ali

1. d2
2. adam

1. d3
2. tarek

1. D-no
2. c-no

Bridge

1. c-no
2. p-no
3. price

Bridge

1. d-no
2. p-no
3. price

Bridge

1. c-no
2. p-no
3. d-no
4. price

Country

1. C-no
2. c-name

1. c1
2. Egypt

1. c2
2. USA

1. c3
2. UK

Product

1. p-no
2. p-name

1. p1
2. TV

1. p2
2. Watch

1. p3
2. blender

To represent information given in the above highlighted record which says that:
ali with no.d1 distributes at egypt with id c1

this relation between ali & egypt represented by:
inserting their numbers in Distributor/Country bridge

ali sells in egypt product TV with id (p1) with price 3000
according to Store X first sol.
we have country/product relation including price

Then Fill country/product with this data

Next record in dataset, ali (d1) sells watches in egypt (c1)
ali/egypt relation already exist in distributor/country bridge.

But this time he sells, watch (p2) in egypt (c1) with 5000 L.E.

So ADD watch/egypt relation in product/country bridge,
watch (p2) in egypt (c1) with 5000 L.E.

Next inserting 3rd record in dataset in relational tables,
adam (d2) sells blender in egypt (c1).

adam/egypt = d2/c1 inserted in distributor/country bridge.

As adam sells blender in egypt with price=2500
Insert this information in product/country bridge. as shown in bridge here

At the 4th record in dataset, a different distributor tarek (d3) also sells in egypt (c1)
the same product blender (p3) but with different price=2300 than distributor adam
in the previous record

Then tarek (d3)/egypt (c1) relation added to distributor/country bridge

The current step: is to represent blender/egypt relation
with distributor tarek's price=2300 in the product/country bridge
WHICH IS IMPOSSIBLE

Adding this information to product/country leads to major
ERROR (Primary key repitation)

EEROR 1
primary key repition

Error 1: this solution cann't represent Store X requirement that:
*different distributors can set different prices for same product*

adam's price

tarek's price

Error 2: Assume that the dataset is hidden
Can you answer the question which distributor sells a specific product?
(e.g.) Who sells watch?

Error 2: -By Looking at all tables no direct relation between product & distributor
-Product has relation only with country

Error 2: -From product/country relation bridge:
-We can conclude that watch(p2) sold in country (c1)

Error 2: -From country table we can find that c1 is egypt country
-this means that watch(p2) sold in egypt
So can we use the distributor/country relation to know the watch distributor?

Error 2: unfortunately
-egypt country (c1) has three records in distributor country brigde
- (i.e.) egypt has relations with all distributors (d1,d2,d3) because all of them sell different products in egypt.

Error 2: HERE IS THE PROBLEM
-In this solution we can know the products sold in each country,
and also who are the distributors of each country,
but we cann't link the distributors with the products they sell

Store X solution 1 Cons
ERROR 1: For each pair (country/product) only one price is set
ERROR 2: the distributor of a product in a country is untraceable

Saving...
Server Error
Resubmit

2.6.2. Ternary Relationship Example (Store X)¶

Store X second solution illustration:

1 / 23 Settings

<<<>>>

Here are the problems' statments

1

M

1

M

M

1

1

M

Store X Second Solution Analysis

Two Binary relaionships, (distributor/country) & (distributor/product) including price relationships

NOTE: only Store X dataset records that reveal solution bugs will be examined for time saving

1. Country
2. Product
3. Distributer
4. Price

1. Egypt
2. watch
3. ali
4. 3000

1. Egypt
2. TV
3. ali
4. 5000

1. Egypt
2. Watch
3. adam
4. 2500

1. Egypt
2. blender
3. tarek
4. 2300

1. USA
2. TV
3. adam
4. 4500

1. USA
2. watch
3. ali
4. 4700

1. USA
2. blender
3. tarek
4. 2000

1. UK
2. TV
3. adam
4. 4000

1. UK
2. blender
3. adam
4. 3000

These are tables representing second solution relations, try to fill tables with records in
dataset to see weather these relations can represent all records in dataset or not.

Distributor

1. D-no
2. D-name

1. d1
2. ali

1. d2
2. adam

1. d3
2. tarek

1. D-no
2. c-no

Bridge

Bridge

1. d-no
2. p-no
3. price

Country

1. C-no
2. c-name

1. c1
2. Egypt

1. c2
2. USA

1. c3
2. UK

Product

1. p-no
2. p-name

1. p1
2. Watch

1. p2
2. TV

1. p3
2. blender

To represent information given in the above highlighted record which says that:
ali with no.d1 distributes at egypt with id c1

this relation between ali & egypt represented by:
inserting their numbers in Distributor/Country bridge

ali sells in egypt product watch with id (p1) with price 3000
according to Store X second sol.
we have distributor/product relation including price

Then Fill Distributor/product with this data

Record 2 in dataset will be skipped for time saving as it doesn't reveal solution bugs

Next record in dataset, different distributor adam (d2) also sells the same product
watch in egypt (c1) as distributor ali
adam/egypt added in distributor/country bridge.

pay attention: that adam sell watches in egypt with different price than ali
adam (d2)/watch (p1) with price 2500 added to
distributor/product bridge.

Skip records 4,5 jump to record 6 where
the same distributor ali (d1) sells the same product watch (p1) in different country
USA (c2)

add ali/USA relation in distributor/country bridge.

Now it's time to add ali/watch relation in distributor/product bridge
with the new price=4700 of USA.

Is this possible? .

No, it's not possible as it results in primary key redundancy as shown above.

EEROR 1
primary key repition
not allowed

ali's watch price for Egypt

ali's watch price for USA

Error 1: only one price per product is set for each distributor
contradicting Store x requirement that allows distributors to set different prices for
a given product in different countries .

Next, put highlighted record in store X dataset in relational tables here .

(adam/UK) = (d2/c3) inserted in (distributor/Contry) bridge. .

(adam/TV) = (d2/p2) inserted in (distributor/product) bridge with price=4000.

If we hide store X dataset,
Error 2: second solution ERD design is insufficient
to convey all right information existing in store x dataset.

From (distributor/product) table we can know for example that,
distributor d2 (adam) sells two products p1 & p2 (watch/TV)
(i.e. from this bridge we can know which products each distributor sells).

From (distributor/contry) we can know for example that, the same
distributor d2 (adam) sells products in two contries c1 & c3 (Egypt/UK)
(i.e. from this bridge we can know who sells products in each country).

But we cann't know which product adam sells in which country
Does adam sell TVs in egypt?
Does adam sell watches in egypt? the same confusion is also for UK.

The conclusion is that the (country/product) relation is missed in this solution.

Saving...
Server Error
Resubmit

Store X third solution illustration:

1 / 22 Settings

<<<>>>

Here are the problems' statments

1

M

1

M

1

M

1

M

M

1

1

M

Store X Third Solution Analysis

Three Binary relationships, (distributor/country) , (distributor/product) & (country/product) including price relationships

NOTE: only Store X dataset records that reveal solution bugs will be examined for time saving

1. Country
2. Product
3. Distributer
4. Price

1. Egypt
2. TV
3. ali
4. 3000

1. Egypt
2. watch
3. ali
4. 5000

1. Egypt
2. blender
3. adam
4. 2500

1. Egypt
2. blender
3. tarek
4. 2300

1. USA
2. TV
3. adam
4. 4500

1. USA
2. watch
3. ali
4. 4700

1. USA
2. blender
3. tarek
4. 2000

1. UK
2. TV
3. adam
4. 4000

1. UK
2. blender
3. adam
4. 3000

These are tables representing third solution relations, try to fill tables with records in
dataset to see weather these relations can represent all records in dataset or not.

Distributor

1. D-no
2. D-name

1. d1
2. ali

1. d2
2. adam

1. d3
2. tarek

1. D-no
2. c-no

1. d-no
2. p-no

Bridge

1. c-no
2. p-no
3. price

Country

1. C-no
2. c-name

1. c1
2. Egypt

1. c2
2. USA

1. c3
2. UK

Product

1. p-no
2. p-name

1. p1
2. TV

1. p2
2. Watch

1. p3
2. blender

Insert first record in store X dataset in solution three relational tables.

Hightligth first record basic Information (ali/ egypt/ TV)
in the main tables (distributor/ country/ product).

Use (distributor/country) bridge to express
(ali (d1)/ egypt (c1)) relation.

Then use (distributor/product) bridge to express
(ali (d1)/ TV (p1)) relation.

Finally for first record representation: Insert in (country/product) bridge
Values (egypt (c1)/ TV (p1) & price=3000.

Insert second record in store X dataset in relational tables where ali (d1)
sells watches (p2) in egypt (c1).

(ali (d1)/ egypt (c1)) relation already exists in
(distributor/country) bridge from previous record.

Then use (distributor/product) bridge to express
(ali (d1)/ watch (p2)) relation.

Finally for second record representation: Insert in (country/product) bridge
Values (egypt (c1)/ TV (p1) & price=5000.

Insert third record in store X dataset in relational tables where adam (d2)
sells blender (p3) in egypt (c1).

Use (distributor/country) bridge to express
(adam (d2)/ egypt (c1)) relation.

Then use (distributor/product) bridge to express
(adam (d2)/ blender (p3)) relation.

Finally for third record representation: Insert in (country/product) bridge
Values (egypt (c1)/ blender (p3) & price=2500.

Insert forth record in store X dataset in relational tables where tarek (d3)
sells blender (p3) in egypt (c1).

Use (distributor/country) bridge to express
(tarek (d3)/ egypt (c1)) relation.

Then use (distributor/product) bridge to express
(tarek (d3)/ blender (p3)) relation.

Finally for forth record representation: Insert in (country/product) bridge
Values (egypt (c1)/ blender (p3), But this time with different price=2300
for distributor tarek.

Error: impossible insertion results in primary key repetition.

EEROR
pK repition
not allowed

Error: prices different distributor set for the same product sold in the same country
cann't be represented using this design.

Because only one price per (product/country) exists in
solution 3 desgin contradicting store X requirement.

Saving...
Server Error
Resubmit

Store X forth solution illustration:

1 / 17 Settings

<<<>>>

Here are the problems' statments

M

1

1

M

M

1

Store X Forth Solution Analysis

One Ternary relationship, (distributor/country/product) including price as a relationship attribute

NOTE: only Store X dataset records that may reveal solution bugs will be examined to cover all possible test cases.

1. Country
2. Product
3. Distributer
4. Price

1. Egypt
2. TV
3. ali
4. 3000

1. Egypt
2. watch
3. ali
4. 5000

1. Egypt
2. blender
3. adam
4. 2500

1. Egypt
2. blender
3. tarek
4. 2300

1. USA
2. TV
3. adam
4. 4500

1. USA
2. watch
3. ali
4. 4700

1. USA
2. blender
3. tarek
4. 2000

1. UK
2. TV
3. adam
4. 4000

1. UK
2. blender
3. adam
4. 3000

These are tables representing forth solution relations, try to fill tables with records
in dataset to see weather these relations can represent all records in dataset or not.

Distributor

1. D-no
2. D-name

1. d1
2. ali

1. d2
2. adam

1. d3
2. tarek

Bridge

1. c-no
2. p-no
3. d-no
4. price

Country

1. C-no
2. c-name

1. c1
2. Egypt

1. c2
2. USA

1. c3
2. UK

Product

1. p-no
2. p-name

1. p1
2. TV

1. p2
2. Watch

1. p3
2. blender

Records 2,3,4 & 6 in Store X dataset will be inserted in relational tables here,
As they cover all possible test cases that made problems in the pervious
suggested three solutions.

Two records having different sellers for the same product in the same country
each seller sets his own price (e.g. records 3 & 4).

And the other two records for the same seller selling the same product in
two different countries & the price is set differently according to the country
(e.g. records 2 & 6).

Inserting record 2 data in relational tables where:
ali (d1) distributes watches (p2) in country egypt (c1).

Inserting Ids of distributor, country & product in the bridge of ternary relationship
along with the price=5000.

Inserting record 3 data in relational tables where:
adam (d2) distributes blender (p3) in country egypt (c1).

Inserting Ids of distributor, country & product in the bridge of ternary relationship
along with the price=2500.

Inserting record 4 data in relational tables where:
tarek (d3) distributes blender (p3) in country egypt (c1).

Inserting Ids of distributor, country & product in the bridge of ternary relationship
along with the price=2300.

Inserting record 6 data in relational tables where:
ali (d1) distributes watches (p2) in country USA (c2).

Inserting Ids of distributor, country & product in the bridge of ternary relationship
along with the price=4700.

As proved here This is the best (correct) solution
It overcomes all difficulties of the pervious suggested solutions
It can represent all different test cases as required by store X.

Without any primary key redundancy OR untraceable data .

The basic idea of this solution lies in store X requiement which is:
The price is determined according to all three factors (country, distributor & product).

When any one of these factors changes, the price may vary.

Saving...
Server Error
Resubmit

2.6.3. Binary Relationship Example (Store Y)¶

Store Y first & second solutions’ analysis:

1 / 17 Settings

<<<>>>

M

1

1

M

M

1

1

Store Y First & Second Solution Analysis

First sol.:One (N:M:P) Ternary relationship, between (country/distributor/product) including price as a relationship attribute

Second sol.:One (1:N:M) Ternary relationship, between (country/distributor/product) including price as a relationship attribute

Note: The difference between these two solutions is the '1' cardinality connecting country to the ternary relationship.

NOTE: The problem with Store Y proposed solutions is not that they hinder dataset Y records representation but they don't prevent faulty data as shown here in the visualization

1. Country
2. Product
3. Distributer
4. Price

1. Egypt
2. TV
3. ali
4. 3000

1. Egypt
2. watch
3. ali
4. 5000

1. UK
2. blender
3. ali
4. 2500

1. UK
2. watch
3. ali
4. 2300

1. USA
2. TV
3. adam
4. 4500

1. USA
2. watch
3. adam
4. 4700

1. USA
2. blender
3. adam
4. 2000

1. lebanon
2. TV
3. morad
4. 4000

1. lebanon
2. blender
3. morad
4. 3000

These are tables representing first solution relations, try to fill tables with records
in dataset.

Distributor

1. D-no
2. D-name

1. d1
2. ali

1. d2
2. adam

1. d3
2. tarek

Bridge

1. c-no
2. p-no
3. d-no
4. price

Country

1. C-no
2. c-name

1. c1
2. Egypt

1. c2
2. USA

1. c3
2. UK

Product

1. p-no
2. p-name

1. p1
2. TV

1. p2
2. Watch

1. p3
2. blender

Try Adding Records 1 in Store Y dataset to solution tables here.

In which Distributor: ali sells Product: TV in country: Egypt with price:3000.

Then all these data will be added to the Bridge.

IF you try adding all dataset records in the bridge, it will be added SUCCESSFULLY
BUT this solution remains a FAULTY solution
AS It doesn't prevent adding erroneous data to the brigde.

For Example: try to add this record (Egypt, TV, adam, 2500) in the bridge

Here is the fault It is also added successfully which is logically wrong
As It allows multiple distributors (ali & adam) to sell the same product (tv) in the
same country (Egypt)
Which is against store Y policy that dedicates only one distributor for each country to sell all products of that country.

EEROR
Logically wrong record
should not be added

The differences between first and this second solutions in store Y is:
- '1' cardinality of country entity to the ternary relationship
- (c-no) isn't part of the bridge composit primary key because of the one cardinality.

1. p-no
2. d-no
3. c-no
4. price

(C-no) is not part of PK

If we try adding some selected records from dataset in the solution design here,
For example dataset's first record added as shown in the bridge here
Where distributor ali who is dedicated for egypt country according to store Y policy sells TV.

Then: Adding second record as shown
Where Egypt's distributor (ali) sells another product watch
also, done successfully without physical or logical errors.

Adding third record
Notice: the same distributor ali who distributes in egypt sells in other country (UK)
Which is allowed in store Y policy that the same distributor sells in different countries.

Try adding forth record
where UK's distributor (ali) sells another product watch in UK
Which is allowed in store Y policy that the same distributor sells in different countries.

PHYSICAL ERROR
In this solution design both (d-id & p-id) are composite PK that can not be repeated
But ali previously sold watch in egypt (record 2 in bridge)-----> (PK repetition)

PHYSICAL ERROR
So that design is faulty as it doesn't fulfill store y requirements
that allows same distributor to sell in different countries & by the way he may sell the same product in those countries.

Physical EEROR: PK repetition

This solution also results in a Logical Error
Assume adding a faulty record as (adam ,Egypt, TV, 1500) , this is wrong data as
Egypt already has an execlusive distributor (ali) in the dataset.

Unfortunately the record is added SUCCESSFULLY which is WRONG
Now (ali & adam both distribute in Egypt), contradicting store's Y policy of one
execlusive distributor for each country.

Logically Wrong record
should not be added

d2=adam
c1=Egypt

d1=ali
c1=Egypt

Saving...
Server Error
Resubmit

Store Y third & forth solutions’ analysis:

1 / 24 Settings

<<<>>>

Here are the problems' statments

M

1

M

1

1

M

M

1

1

M

Store Y Third & Forth Solutions Analysis

- Three Binary relationships, 1:M (distributor/country) , N:M (distributor/product) & N:M (country/product) including price relationships

- Two Binary relationships, 1:M (distributor/country) & N:M (country/product) including price relationships

NOTE: only Store Y dataset records that reveal solution bugs will be examined for time saving

1. Country
2. Product
3. Distributer
4. Price

1. Egypt
2. TV
3. ali
4. 3000

1. Egypt
2. watch
3. ali
4. 5000

1. UK
2. blender
3. ali
4. 2500

1. UK
2. watch
3. ali
4. 2300

1. USA
2. TV
3. adam
4. 4500

1. USA
2. watch
3. adam
4. 4700

1. USA
2. blender
3. adam
4. 2000

1. lebanon
2. TV
3. morad
4. 4000

1. lebanon
2. blender
3. morad
4. 3000

These are tables representing third solution relations, try to fill tables with records in
dataset to see weather these relations are correct or not.

Distributor

1. D-no
2. D-name

1. d1
2. ali

1. d2
2. adam

1. d3
2. tarek

1. d-no
2. p-no

Bridge

1. c-no
2. p-no
3. price

Country

1. C-no
2. c-name
3. d-no

1. c1
2. Egypt

1. c2
2. USA

1. c3
2. UK

Product

1. p-no
2. p-name

1. p1
2. TV

1. p2
2. Watch

1. p3
2. blender

In this solution inserting for example first dataset record requires inserting
in three different tables.

As (ali) is the distributor of (egypt)
So ali's id (d1) should be inserted in country table as a foreign key, to represent
distributor/country (1:M) relation.

FK of distributor

To specify that TV is the product that ali sells in Egypt with price=3000
Fill in (country/product) bridge as shown.

who sells TV in Egypt? (ali)
So last step is to fill in (distributor/product) bridge.

All other dataset records should be inserted in the same way as done with first record.
But the problem here is that no constraints exist to ensure data integrity
Assume that user entered a wrong data while filling in (distributor/product) bridge

Assume for any reason that (id=d2) is inserted for ali instead of (d1) in the
(distributor/product) for last step of representing first dataset recod.

Unfortunately, the design will not recognize this data conflict.
d-no in (distributor/product) bridge should be equal to d-no (FK) in country table
This design allows data corruption.

value conflict

value conflict

Redundant relationships is the drawback of this design
The redundancy is in distributor/product direct relation, this relation is indirectly exists
in the design through the country entity
To illustrate this concept dataset was hidened.

The selected part in the design (1:M) relation between (country & distributor) means ,
Each country is associated with only one distributor.

While this part of the design relates each country to all its products.

For Exapmle: By looking at first record in country table we know
that Ali is the sole distributor in Egypt (c1) through FK (d-no=d1).

And from first & second record in (country/product) bridge, we know that
TVs (p1) & watches (p2) are sold in Egypt (c1).

Then logically we can conclude that ali is the only distributor of tvs and watches
without need to use the direct distributor/product relation.

So we should git rid of the useless redundant distributor/product relation.

The new effecient solution after removing the redundant relation is here.

Continue inserting dataset records here to ensure solution correctness
In dataset third record as distributor ali also sells in uk
ali's id inserted in FK feild at UK record in country table.

As ali sells blenders with price=2500 in UK
Then insert UK-id (c3) & blender-id (p3) in (product/country) bridge.

While inserting dataset forth record skip first step (adding distributor id as
a FK in country table) because UK has only one exclusive distributor ali whose
id is already added at previous record insertion process (record 3 in dataset).

But the relation between UK & watches sold there with price 2300
should be expressed by inserting (c3, p2) Keys in (country/product) bridge.

Record 5 insertion results shown at diagram where USA country linked with distributor
adam through adding d2 as a FK in the country table.
Then add record details of USA product & its price in (product/country) bridge.

The remaining dataset records shoulld be added in the same way
IF new country or distributor founded (e.g. lebanon country, distributor morad)
in record 8 & 9 in dataset, their data should be added as new records in main tables (country & distributor).

As proved forth solution is the correct design for store Y
- Effeciently fulfill all store Y requirments
- No redundant relations, no data conflict
- No violation for primary key constraint or data integrity.

Saving...
Server Error
Resubmit