1 / 14

CS 257 Database Systems Principles Assignment 1

CS 257 Database Systems Principles Assignment 1. Instructor: Student: Dr. T. Y. Lin Rajan Vyas (119). Modes of Information Integration. Information. Information Integration allows database or other distributed information to work together. Three most common approaches:

latona
Télécharger la présentation

CS 257 Database Systems Principles Assignment 1

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. CS 257Database Systems PrinciplesAssignment 1 Instructor:Student: Dr. T. Y. Lin RajanVyas (119)

  2. Modes of Information Integration

  3. Information • Information Integration allows database or other distributed information to work together. • Three most common approaches: • Federated Database • DataWareHousing • Mediators

  4. Federations • The simplest architecture for integrating several DBs • One to one connections between all pairs of DBs • n DBs talk to each other, n(n-1) wrappers are needed • Good when communications between DBs are limited

  5. Federated Database System • Sources are independent, but one source can call on others to supply information. • One-to-One connection between the all pairs of databases DB 1 DB 2 DB 3 DB 4

  6. Dealer 1 NeededCars(mode1, color, autotrans) • Dealer 2 Autos(seria1, model, color) Options(seria1, option) • Dealer 1 to Dealer 2 f or ( e a ch t u p l e (:m, : c , :a) in neededCars) if ( : a = TRUE) { /* automatic transmission wanted */ SELECT s e r i a l FROM Autos, Options WHERE Autos.seria1 = Options.seria1 AND Options.option = 'autoTrans' AND Autos.mode1 = :m AND Autos.color = :c; } e l s e { /* automatic transmission not wanted */ SELECT serial FROM Autos WHERE Autos.mode1 = :m AND Autos.color = :c AND NOT EXISTS ( SELECT * FROM Options WHERE s e r i a l = Autos.seria1 AND option = 'autoTrans' }; } } • Dealer 3 Cars(serialN0, model, color, autoTrans, ...)

  7. Data Warehouse • Sources are translated from their local schema to a global schema and copied to a central DB. • User transparent: user uses Data Warehouse just like an ordinary DB • User is not allowed to update Data Warehouse

  8. Information • Copies sources of data from several sources are stored in a single database. User Query Result Ware House Combiner Extractor 1 Extractor 2 Source 2 Source 1

  9. Example • Construct a data warehouse from sources DB of 2 car dealers: • Dealer-1’s schema: Cars(serialNo, model,color,autoTrans,cdPlayer,…) • Dealer-2’s schema: Auto(serial,model,color) Options(serial,option) • Warehouse’s schema: AutoWhse(serialNo,model,color,autoTrans,dealer) • Extractor --- Query to extract data from Dealer-1’s data: • INSERT INTO AutosWhse(serialNo, model, color, autoTans, dealer) • SELECT serialNo,model,color,autoTrans,’dealer1’ • From Cars;

  10. Extractor --- Query to extract data from Dealer-2’s data: • INSERT INTO AutosWhse(serialNo, model, color, autoTans, dealer) • SELECT serialNo,model,color,’yes’,’dealer2’ • FROM Autos,Options • WHERE Autos.serial=Options.serial AND option=‘autoTrans’; • INSERT INTO AutosWhse(serialNo, model, color, autoTans, dealer) • SELECT serialNo,model,color,’no’,’dealer2’ • FROM Autos • WHERE NOT EXISTS ( SELECT * FROM serial =Autos.serial AND option = ‘autoTrans’);

  11. Mediators

  12. It is a software component that supports a virtual database. • It stores no data of its own. User Query Result Mediator Wrapper Wrapper Source 2 Source 1

  13. Extractor for translating Dealer-2 data to the warehouse • INSERT INTO AutosWhse(serialNo, model, color,autoTrans, dealer) SELECT s e r i a l , model, color, ' y e s ' , 'dealer2' FROM Autos, Options WHERE Autos.seria1 = Options.seria1 AND option = 'autoTrans'; • INSERT INTO AutosWhse(serialNo, model, color,autoTrans, dealer) SELECT s e r i a l , model, color, 'no', 'dealer2‘ FROM Autos WHERE NOT EXISTS ( SELECT * FROM Options WHERE s e r i a l = Autos.seria1 AND option = 'autoTrans' );

  14. Thank You

More Related