CS 257 Database Systems Principles Assignment 1

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