Ask Question, Ask an Expert

+61-413 786 465

info@mywordsolution.com

Ask DBMS Expert


Home >> DBMS

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.

DBMS, Programming

  • Category:- DBMS
  • Reference No.:- M9526602

Have any Question?


Related Questions in DBMS

Question a suppose you are a marathon runner that can run a

Question : a) Suppose you are a marathon runner that can run a maximum of n miles on a single bottle of water. You are given a map of your marathon route with all the water stations marked. Design an efficient algorithm ...

Relational database exerciseyou have been assigned to a new

Relational Database Exercise: You have been assigned to a new development team. A client is requesting a relational database system to manage their present store with the anticipation of adding more stores in the future. ...

Answer the following question explain the difference

Answer the following Question : Explain the difference between a database management system (DBMS) and a database. Are Microsoft Access, SQL Server, and Oracle examples of databases or database management systems (DBMS)?

In sql developercreate a table userpermissions provide

IN SQL DEVELOPER Create a table UserPermissions (provide create and insert statements code) Document UserName Policy SYSTEM Menu JDOW W2 USAM Permissions SYSTEM W2 JDOW Form 1040 USAM Policy JDOW W2 SYSTEM Write a PL/SQL ...

Sql assignmentin these exercises youll enter and run your

SQL Assignment In these exercises, you'll enter and run your own SELECT statements. You will use the MyGuitarShop database for these queries. If you do not already have the MyGuitarShop database, the SQL script and the i ...

A schools office of the registrar maintains data about the

A School's office of the registrar maintains data about the following entities: a) courses (including course number, title, credits, syllabus and prerequisites), b) course offerings (including course number, year, semest ...

Assignment question - write and run sql statements to

Assignment Question - Write and run SQL statements to complete the following tasks Part A - DML 1. Locate the record in the vendor table that does not have a value for the attribute V_STATE 2. Find the customers whose ba ...

Database and information retrieval assignment - data

Database and Information Retrieval Assignment - Data Privacy Essay Task - Write an essay (aim for 750 words) that addresses issues associated with data proivacy. Use the Australian Privacy Principles - discussed in class ...

Select from e d pwhere edeptddept and dcitypcity and

Select * From E, D, P WHERE E.dept=D.dept AND D.city=P.city AND D.budget>1M AND P.priority=A; Assuming relations have the same size and uniform distributions, what is the best plan with Nested Loop joins only? Write it a ...

A taking an unnormalised list describe how you would

(a) Taking an unnormalised list, describe how you would normalise it using the normal forms technique and show how the result of this method is used. (b) You are currently in the process of developing a RDBMS for a natio ...

  • 4,153,160 Questions Asked
  • 13,132 Experts
  • 2,558,936 Questions Answered

Ask Experts for help!!

Looking for Assignment Help?

Start excelling in your Courses, Get help with Assignment

Write us your full requirement for evaluation and you will receive response within 20 minutes turnaround time.

Ask Now Help with Problems, Get a Best Answer

Why might a bank avoid the use of interest rate swaps even

Why might a bank avoid the use of interest rate swaps, even when the institution is exposed to significant interest rate

Describe the difference between zero coupon bonds and

Describe the difference between zero coupon bonds and coupon bonds. Under what conditions will a coupon bond sell at a p

Compute the present value of an annuity of 880 per year

Compute the present value of an annuity of $ 880 per year for 16 years, given a discount rate of 6 percent per annum. As

Compute the present value of an 1150 payment made in ten

Compute the present value of an $1,150 payment made in ten years when the discount rate is 12 percent. (Do not round int

Compute the present value of an annuity of 699 per year

Compute the present value of an annuity of $ 699 per year for 19 years, given a discount rate of 6 percent per annum. As