Adapted from Wiltod Litwins presentation

1
Multi database Manipulations

• Adapted from Wiltod Litwins presentation
• http//ceria.dauphine.fr/witold.html
• Presented by
• Meenakshi Nagarajan
• For CSCI 8370
• Advanced Databases

2
MSQL(Litwin, Abdellatif, Nicolas, Zeroual,
1989L. Suardi, M. Rusinkiewicz, 1992)

• An extension to SQL
• Contains by definition every SQL-x
• Allows for non-procedural multidatabase base
  manipulations
• ? MSQL queries impossible to formulate in SQL
• An MSQL query may replace several SQL queries
• Developed in 1986-89
• INRIA, projet B A BA,
• Dr. Thesis of MM. Abdellatif, Nicolas, Zeroual
• Compiler implemented at Houston University
• Team of Prof. M. Rusinkiewicz, 1990-1993

3
MSQL(Litwin, Abdellatif, Nicolas, Zeroual,
1989L. Suardi, M. Rusinkiewicz, 1992)

• Research vehicle for functions for the MBD
  environment
• to address relations in different databases
• to manipulate semantically heterogeneous data
• to create MDB views
• to transfer data (and schemas) between DBs
• to define MDB dependencies
• Present to limited extent in most of commercial
  DBMSs

4
MSQL(Basic new properties)

• SQL Query
• Uses 1st order predicate calculus
• Is compiled for optimization into the relational
  algebra
• Result is a table
• MSQL Query
• May use higher-order predicate calculus
• Is compiled for optimization into the
  multirelational algebra
• Result is a multitable
• A set of relations (tables)
• May be constituted from one or no tables

5
MSQL(More on functions specific to the MDB
environment)

• Addressing of tables in different DBs
• Implicitly or by qualification by (multi)database
  names
• Introduced around 1985 by relational multibase
  system prototype MRDSM
• Unknown at that time of any relational language
• See the overview of relational DBMSs existing
  in 1987 (M. Brodie)

6
MSQL(More on functions specific to MDB env.)

• Manipulation of semantically heterogeneous
• data
• Multiple Queries
• With multiples identifiers
• With semantic variables
• Ranging over data names
• Scale and Precision
• Units of measure
• Implicit joins
• Capabilities still unknown of SQL
• Capabilities known at present to some dialects
• Limited with respect to MSQL

7
MSQL example
View
View
SIL Internal Logical Schema
8
Conceptual Schemas (the multischema)

• DB bnp
• br (br, brname, street, street, city,
  zipcode, tel)account (acc, cl, balance,
  br)client (cl, clname, cltel, cltype, street,
  street, city, zipcode)spe-acc (acc, br, cl,
  balance, curr)
• DB sg
• branch (bra, braname, street, s, town, zip,
  t, class)acc (acc, bra, c, balance)client
  (c, cname, ct, ctype, street, s, town, zip)
• DB cic
• br (br, brname, street, street, city,
  zipcode, tel)account (ac, br, cl, balance,
  open_date)client(cl, clname, cltel, cltype,
  street, street, city, zipcode)

9
Semantic Heterogeneity In Banks

• Same names can designate different data
• Different names can designate same data
• same client, same town..
• The value of a primary key is valid only in one
  DB
• how to identify same client in diff. banks ?

10
MSQL Commands

• CREATE TABLE CREATE DATABASE
• CREATE MULTIDATABASE CREATE VIEW
• ALTER TABLE or ALTER VIEW
• ALTER MULTIDATABASE
• DROP TABLE DROP DATABASE
• DROP MULTIDATABASE DROP VIEW

11
MSQL CREATE DATABASE
gt MSQL CREATE DATABASE boulogne CREATE DB
.com.org.user.boulogne CREATE MULTIDATABASE
Banks (bnp cic sg ) USE Banks CREATE
DATABASE boulogne FROM bnp
Query scope
12
MSQL CREATE MULTIDATABASE

• MSQL
• CREATE MDB EC-Banks (f-banks-i-banks, s-banks,
  g-banks, e-banks )
• CREATE MULTIDATABASE can create
• flat MDBs (only contain DBs)
• nested MDBs (DBs or MDBs)
• can be potentially any network of DBs or MDBs
• like through the links on the WEB
• what about cycles ?

13
MSQLCREATE TABLE
Import

• use banks
• CREATE TABLE boulogne.loan FROM bnp.loan
• CREATE TABLE fake_checks (Chq INT,
  Montant_Euro CURRENCY EURO .... )
• One has created four (empty) tables bnp.
  fake_checks , cic. fake_checks ...
  boulogne.fake_checks
• CREATE TABLE boulogne.client (c, cn, ct) FROM
  bnp.client (cl, clname, cltel)
• PRIMARY KEY (c)
• (cn, ct) OUTER REFERENCES (clname, cltel)

Unit of measure
14
MSQLCREATE TABLE with References

• USE AuPrintemps / MDB AuPrintemps
• CREATE TABLE MusicDep.Inventory
• .
• FOREIGN KEY (Item) REFERENCES
  Central.Stock(I)
• No unauthorized Item in the inventory of the
  Music Department
• Other options
• PRIMARY KEY () REFERENCES T()
• T1(A) LEFTRIGHT REFERENCES T2(B)
• Generates implicit equijoin, or left or right
  implicit outerjoins when a query selects
  attributes A and B.

15
MSQLALTER MULTIDATABASE

• use banks
• alter banks include vernesremove cic
• Alter MDB can create
• flat MDBs (only contain DBs)
• nested MDBs

16
MSQL Elementary queries
Prefixing with DB names was unknown to SQL -
and is in DB2 SQL since last year only
17
MSQL Default DB
Tables of the default database are not prefixed
18
MSQL Elementary queries without prefixed names
Table names are unique within the query scope
19
Updates

• USE (bnp b) sg
• UPDATE account
• SET account.balance account.balance 500
• WHERE account.balance gt acc.balance
• AND b.client.clname sg.client.cname AND
  b.client.street sg.client.street
• What does it mean ?

20
Multiples Queries
21
Multiple Queries
22
Results (a multitable)
23
Multiple Updates

• Begin
• Use BanksUpdate clset street 'Charles de
  Gaulle"where street 'Etoile'
• If SQLCODE ltgt 0 then Rollback
• Commit
• Use Banks vital cicUpdate clset street
  'Charles de Gaulle"where street 'Etoile'
• MSQL transaction semantics is more general than
  ACID
• may include COMP (compensation) statement, list
  of accept. states....

24
Semantic Variables in MSQL

• use bnp sglet x be town
• select from bwhere x 'Paris' and street
  'r. de Rivoli'
• use bnp
• select
• from br
• where town 'Paris' and street 'r. de Rivoli'
• use sg
• select
• from branch
• where town 'Paris' and street 'r. de Rivoli'

25
Semantic Variables in MSQL

• use bnp sglet x be town cityselect from
  bwhere x 'Paris' and street 'r. de Rivoli'
• Alternatively

use bnp sglet x be to cityselect from
bwhere x 'Paris' and street 'r. de
Rivoli'
26
Semantic Variables in MSQL

• use banks
• let X be banks.
• select a, balance, cnamefrom X.a a, X.c c
• where a. a c. c

• The query illustrates the multitable pair-wise
  join
• Semantic variable a over relation name account is
  not necessary, but simplifies the typing of the
  query

27
Semantic Variables in MSQL

• use banks
• let x be town city
• let y be sg bnpselect Z. from y.b Z, cic.b
  Vwhere V.x 'Paris' and
• V.street Z.street and Z.x 'Paris'
• What does it means ?
• Is the  natural  decomposition into SQL
  queries optimal ?
• Otherwise is there any better ?

28
Multirelational Algebra

• No, the natural decomposition is not optimal
• the selection in cic is repeated three times
  uselessly
• It should be done once first, then one should
  proceed with the join
• One needs an algebra for multiple queries
• Grant, Litwin, Selis, Roussopoulos. An Algebra
  and Calculus for Relational Multidatabases. The
  VLDB Journal, Vol. 2, No. 2, April 1993, 153-171.

29
Multirelational Algebra

• Multirelational operators
• Select From M where (boolean condition)
• Project M (A, B)
• Pair-wise Theta join On (M1.A ? M2.B AND )
• Theta Join On (M1.A ? M2.B AND )
• These operators are typically commutative and
  associative as their relational counterparts
• Select can be moved through a join down the
  execution tree
• Project (C (Project M (A, B, C))) Project M (C)
• Etc

30
Semantic Variables in MSQL

• Semantic variables can be compound and with
  values selected by queries from some dictionaries
• use bankslet (x, y) be select X.attr Y.attr
  from FD X, FD Y where X.mean tel and Y.mean
  city
• select from clientwhere x '123' and y
  'Paris'

FD
mean attr tel t tel tel city city city town city
burgh
31
Semantic Variables in MSQL

• Can be applied to MSQL DD statements
• use banks
• create database cic2
• let x be a b c
• create table cic2.x from cic.x
• Copies cic schema except for one table

32
Name homogenizationThe labels

• USE Banks LET t BE tel tSELECT name
  branch_name, t tel, s streetFROM br
  brWHERE street Champs Elysées
• The result multitable
• ( bnp.br.branch_name, bnp.br.tel,
  bnp.br.street ), ( sg.br.branch_name,
  sg.br.tel, sg.br.street )( cic.br.
  branch_name, cic.br.tel, cic.br.street )

33
Multidatabase Views
bnp
my_bank
sg
A partial view of DBs bnp and sg in DB
my_bank
The views in my_bank can be considered Import
Schemes
34
Multidatabase Union Views

• Use Banks
• Create View bnp.all-banks as
• Use banks
• let x be town city
• let y be banks.
• Select y.br ( y, br, brname branch, street,
  street, x city, zip zip, t tel)
• Union

• Union unions all the tables of the selected
  multitable
• It scales to all the tables named br of Banks,
  if new banks enters the MDB Banks in the future
• Current DBMS, e.g., SQL Server, require to alter
  the union view definition in such a case

35
Key words and Aggregate Functions in MSQL

• Key words and Aggregate Functions of SQL
• par definition
• DISTINCT, GROUP BY, ORDER BY
• COUNT, AVG, SUM
• operate at each table of a multitable
• Their extensions to multitables
• MDISTINCT, MCOUNT, MGROUP BY, MORDER BYMAVG,
  MSUM...
• operate at whole multitable
• important for warehousing

36
Example

• USE BanksSELECT COUNT ()FROM br brWHERE
  street 'champs elysées'

37
Example

• USE BanksSELECT COUNT ()FROM br brWHERE
  street 'champs elysées'
• bnp.br2
• cic.br2
• sg.br2