## 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.

#### Explain the exec statement in sql, Explain the Exec statement in SQL An...

Explain the Exec statement in SQL Ans: Exec statement has the structure    EXEC SQL END-EXEC   Embeded SQl statements are of a form identical to non embedded SQL statem

#### RDBMS, DISADVANTAGES OF RDBMS

DISADVANTAGES OF RDBMS

#### Database Models, Benefits and Limitations od Data Models?

Benefits and Limitations od Data Models?

#### Thid, describe objectivde of file organisation

describe objectivde of file organisation

#### Database, A database has been designed for this beauty parlour in order for...

A database has been designed for this beauty parlour in order for the staff to manage specific things such as bookings made by clients, payments, staff availability, level of exper

#### Give detail explanation about cross-section of twisted pair, The cross-sect...

The cross-section of twisted pair The cross-section of each and every strand of the twisted pair is π/4 square mm. A 10-km length of this type of material, with two strands per

#### Why a report generator is used, Why a report generator is used ? Report...

Why a report generator is used ? Report generator is used for print files on paper.

#### Explain the check clause, Explain the Check Clause The CHECK clause is ...

Explain the Check Clause The CHECK clause is employed to at the end of a CREATE TABLE statement specify table constraints. This is known as table-based constraint because it ap

#### Sql, sql is relationally complete-justify

sql is relationally complete-justify

#### What is a view, What is a view? A view is a logical view on one or more...

What is a view? A view is a logical view on one or more tables.  A view on one or more tables i.e., the data from a view is not actually physically kept instead being derived f

### Write Your Message!

#### 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