Lossless-join decomposition, Database Management System

Assignment Help:

Lossless-Join Decomposition

 

Let us show an intuitive decomposition of a relation. We require a better basis for deciding decompositions since intuition may not always be right. We show how a careless decomposition may lead to problems containing loss of information.

Consider the following relation

ENROL (stno, cno, date-enrolled, room-no, instructor)

Suppose we decompose the above relation into two relations enrol and enrol2 as follows:

ENROL1 (stno, cno, date-enrolled)

ENROL2 (date-enrolled, room-no, instructor)

There are troubles with this decomposition but we do not wish to focus on this aspect at the moment. Let a case of the relation ENROL be:

St no

cno

Date-enrolled

Room-no

Instructor

1123

MCS-011

20-06-2004

1

Navyug

1123

MCS-012

26-09-2004

2

Anurag Sharma

1259

MCS-011

26-09-2003

1

Preeti Anand

1134

MCS-015

30-10-2005

5

Preeti Anand

2223

MCS-016

05-02-2004

6

Shashi Bhushan

                                                              Figure:  A sample relation for decomposition

Then on decomposition the relations ENROL1 and ENROL2 would be:

St no

Cno

Date-enrolled

1123

MCS-011

20-06-2004

1123

MCS-012

26-09-2004

1259

MCS-011

26-09-2003

1134

MCS-015

30-10-2005

2223

MCS-016

05-02-2004

 

 

ENROL1

ENROL2

Date-enrolled

Room-no

Instructor

20-06-2004

1

Navyug

26-09-2004

2

Anurag Sharma

26-09-2003

1

Preeti Anand

30-10-2005

5

Preeti Anand

05-02-2004

6

Shashi Bhushan

All the information that was in the relation ENROL appears to be still existing in ENROL1 and ENROL2 but this is not so. Assume, we wanted to retrieve the student numbers of all students taking a course from Preeti Anand, we would require to join ENROL1 and ENROL2. For joining the only general attribute is Date-enrolled. Therefore, the resulting relation obtained will not be the similar as that of Figure. (Please do the verify and join the resulting relation).

The join will have a number of spurious tuples that were not in the unique relation. Because of these additional tuples, we have lost the correct information about which students take courses from Preeti Anand. (Yes, we have many tuples but less information because we are not capable to say with certainty who is taking courses from Preeti Anand). Such decompositions are known as lossy decompositions. Lossless or nonloss decomposition is that which surety that the join will result in exactly the similar relation as was decomposed. One might think that there may be other ways of recovering the unique relation from the decomposed relations but, sadly, no other operators can recover the unique relation if the join does not (why?).

We require to analyse why the decomposition is lossy. The general attribute in the above decompositions was Date-enrolled. The general attribute is the glue that provides us the ability to find the relationships among different relations by joining the relations simultaneously. If the general attribute have been the primary key of at least one of the two decomposed relations, the trouble of losing information would not have existed. The trouble arises because various enrolments may take place on the similar date.


Related Discussions:- Lossless-join decomposition

Define an abstraction, Define an abstraction by which relationships are tre...

Define an abstraction by which relationships are treated as higher level entities Ans: Aggregation.  It is an abstraction by which relationships are treated as higher level

State the steps of object design, State the Steps of Object Design ...

State the Steps of Object Design Object design is a very iterative process in which various classes, relationships among objects are added when you move from one level to

Explain what is aggregation, Explain what is Aggregation? Aggregation:...

Explain what is Aggregation? Aggregation: Aggregation is an significant concept in database design whereas composite objects could be modelled during the design of database a

Explain the benefit of oom, Explain the benefit of OOM. Main benefits o...

Explain the benefit of OOM. Main benefits of object oriented modeling are development of system become fast, quality of the system get improve. It gives freedom of use of alrea

Special workarea for interface, While using extract datasets it is required...

While using extract datasets it is required to have a special workarea for interface False.  It is not required to have a special workarea for interface

Create a table student with attributes student name, Question 1 Create a t...

Question 1 Create a table student with attributes student name, roll number, total marks and percentage. Do the following operations Insert details in to the table Update

design the logic using looping structure, 1. Design the logic, using a loo...

1. Design the logic, using a looping structure, for a program that outputs every even number from 1 through 25 along with its square and cube. 2. Mr. Sanchez owns 12 apartme

What is the state machine engine, What is the State Machine Engine The...

What is the State Machine Engine The straightest approach to implement control is to have any way of explicitly representing and executing state machines. With the help of thi

Create the table, Question: Using the data model express the following ...

Question: Using the data model express the following queries in SQL. (a) Create the Table named "Cinema" and make the cinema_code to be the primary key of the relation and

MS Access 2010 - Form Updates Table, Hi there, Have an issue with my Databa...

Hi there, Have an issue with my Database. I have: TblOrderDetails and FmOrderDetails TblCircuit and FmCircuit Whenever I add data into the Form, it will update the table as expect

Write Your Message!

Captcha
Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd