- Select ANAME
From AGENT, DELIV
Where AGENT.ANO=DELIV.ANO and CAP=30000 and LOC=‟Bac Lieu‟
- Select ANO, ANAME FROM AGENT, DELIV
Maybe you are interested!
-
Practice database programming with VB.net - 39 -
Evaluate the performance of some distributed hash table algorithms DHT and propose solutions to improve the performance of the CHORD algorithm - 1 -
SQL Database Administration - Hanoi University of Business and Technology - 14 -
Database Content Structure In GIS Environment -
Query Processing and Optimization in Distributed Object-Oriented Databases - 17
Where AGENT.ANO=DELIV.ANO and PRICE >100 and LOC='Bac Lieu'
- Select ANO, ANAME FROM AGENT, DELIV
Where AGENT.ANO=DELIV.ANO and QUAN=100
- Select ANO, ANAME FROM AGENT, DELIV
Where AGENT.ANO=DELIV.ANO and LOC=‟Da Nang‟
- Select ANO, ANAME FROM AGENT, DELIV
Where AGENT.ANO=DELIV.ANO and CAP=15000
d) Determine the minimum intersection selectivity for each minimum intersection predicate of the AGENT relation.
e) Draw the connection graph G of the distributed connection DELIV AGENT, determine the type of graph.
f) Prove the correct condition when fragmenting the DELIV, AGENT relation horizontally.
g) Write a matrix using attributes on the AGENT relation
h) Draw the fragment tree of the global relation DELIV, AGENT
k) Propose a positioning scheme and write an application program with 3 levels of distributed transparency for the following query:
- Shows the agent name whose agent code is entered from the terminal
- Shows the dealer code, dealer name of the dealers that supplied the product with the product code entered from the terminal.
4) Suppose the system has the following application:
- Application 1: Provide information including ANO, ANAME, CAP, ENO of agents with location 'Da Nang'.
- Application 2: Provide information including ANO, ANAME, LOC, ENO of agents with charter capital greater than 20,000.
- Application 3: Provide information including ANO, ANAME, LOC of agents with charter capital less than or equal to 20,000.
- Application 4: Provide information including ANAME, ENO of agents.
a) For applications 1, 2, 3; apply COM_MIN and PHORIZONTAL algorithms to:
- Horizontal fragmentation of the AGENT relation
- Horizontal fragmentation derives the relation DELIV
b) Simplify the query on the fragments
- Select ANO, ANAME FROM AGENT, DELIV
Where AGENT.ANO=DELIV.ANO and CAP>20000 and LOC=‟Bac Lieu‟
- Select ANAME
From AGENT, DELIV
Where AGENT.ANO=DELIV.ANO and CAP=50000 and LOC='Da Nang'
- Select ANO, ANAME FROM AGENT, DELIV
Where AGENT.ANO=DELIV.ANO and PRICE >100 and LOC='Da Nang'
- Select ANO, ANAME FROM AGENT, DELIV
Where AGENT.ANO=DELIV.ANO and QUAN=100
- Select ANO, PNO From AGENT, DELIV
Where AGENT.ANO=DELIV.ANO and LOC=‟Da Nang‟
- Select ANAME
From AGENT, DELIV
Where AGENT.ANO=DELIV.ANO and CAP<20000
c) Determine the minimum intersection selectivity for each minimum intersection predicate of the AGENT relation.
d) Draw the connection graph G of the distributed connection DELIV AGENT, determine the type of graph.
e) Prove the correct condition when horizontally fragmenting the relation DELIV, AGENT
f) Write the matrix using attributes on AGENT relation for all 4 applications
g) Draw the fragment tree of the global relation DELIV, AGENT
h) Propose a positioning scheme and write an application program with 3 levels of distributed transparency for the following query:
- Shows the agent name whose agent code is entered from the terminal
- Shows the dealer code, dealer name of the dealers that supplied the product with the product code entered from the terminal.
- Shows the agent names of agents whose locations are entered from the terminal
5) Suppose there is an application that retrieves a PROD with a product code less than or equal to „P4‟ and another application that retrieves a PROD with a product code greater than „P4‟.
a) Give a simple set of predicates
b) Apply the COM_MIN and PHORIZONTAL algorithms to:
- Horizontal fragmentation of the PROD relation
- Horizontal fragmentation derives the relation DELIV
c) Simplify queries on fragments
- Select PNO, PNAME From PROD, DELIV
Where PROD.PNO=DELIV.PNO and UNIT=”Cai” and PNO>‟P4‟
- Select PNO
From PROD, DELIV
Where PROD.PNO=DELIV.PNO and UNIT=”Tan” and PNO<=‟P4‟
- Select PNO, PNAME From PROD, DELIV
Where PROD.PNO=DELIV.PNO and QUAN=”Cai” and PNO>‟P4‟
- Select PNO
From PROD, DELIV
Where PROD.PNO=DELIV.PNO and PRICE>100 and PNO<=‟P4‟
d) Determine the minimum intersection selectivity for each minimum intersection predicate of the PROD relation.
e) Draw the connection graph G of the distributed connection DELIV PROD, determine the type of graph.
f) Prove the correctness condition when horizontally fragmenting the relation DELIV, PROD
g) Write a matrix using attributes on the PROD relation
h) Draw the fragment tree of the global relation DELIV, PROD
k) Propose a positioning scheme and write an application program with 3 levels of distributed transparency for the following query:
- Shows the product name with the product code entered from the terminal
- Shows the dealer code, dealer name of the dealers that supplied the product with the product code entered from the terminal.
- Shows the product names of products supplied by the dealer whose dealer code is entered from the terminal
6) Suppose there is an application accessing EMP with employee code equal to „E1‟ and another application accessing EMP with different employee code „E1‟.
a) Give a simple set of predicates
b) Apply the COM_MIN and PHORIZONTAL algorithms to:
- Horizontal fragmentation of the EMP relation
- Horizontal fragmentation derives the AGENT relation
c) Simplify queries on fragments
- Select ANAME From EMP, AGENT
Where EMP.ENO = AGENT.ENO and CAP > 10000 and ENO = „E2‟
- Select ENO, ANAME
From EMP, AGENT
Where EMP.ENO = AGENT.ENO and LOC='Da Nang' and ENO ='E1'
- Select ANAME From EMP, AGENT
Where EMP.ENO = AGENT.ENO and ENO = „E3‟
- Select ENO, ANAME From EMP, AGENT
Where EMP.ENO = AGENT.ENO and ENO ≠ „E1‟
d) Determine the minimum intersection selectivity for each minimum intersection predicate of the EMP relation.
e) Draw the connection graph G of the distributed connection AGENT EMP, determine the type of graph.
f) Prove the correct condition when fragmenting the AGENT and EMP relations horizontally.
g) Write a matrix using attributes on the EMP relation
h) Draw the fragment tree of the global relation AGENT, EMP
k) Propose a positioning scheme and write an application program with 3 levels of distributed transparency for the following query:
- Shows the employee name whose employee code is entered from the terminal
- Shows the agent code and agent name of the agents managed by the employee whose employee code is entered from the terminal.
7) Suppose the system has the following applications:
Application 1: Retrieve AGENT sets by location “Bac Lieu”
Application 2: Retrieve AGENT sets with employee codes greater than “E2”
Application 3: Retrieve AGENT sets with employee codes less than or equal to “E2”
a) Give a simple set of predicates
b) Apply the COM_MIN and PHORIZONTAL algorithms to
- Horizontal fragmentation of the AGENT relation
- Horizontal fragmentation derives the relation DELIV
c) Simplify queries on fragments
- Select ANO, ANAME FROM AGENT, DELIV
Where DELIV.ANO = AGENT.ANO and LOC= “Bac Lieu” and ENO = „E2‟
- Select ANO
From AGENT, DELIV
Where DELIV.ANO = AGENT.ANO and LOC= “Bac Lieu” and ENO ≠ „E2‟
- Select ANAME
From AGENT, DELIV
Where DELIV.ANO = AGENT.ANO and CAP ≠ 10000 and ENO = „E4‟
d) Determine the minimum intersection selectivity for each minimum intersection predicate of the AGENT relation.
e) Draw the connection graph G of the distributed connection DELIV AGENT, determine the type of graph.
f) Prove the correct condition when fragmenting the DELIV, AGENT relation horizontally.
g) Write a matrix using attributes on the AGENT relation
h) Draw the fragment tree of the global relation DELIV, AGENT
k) Propose a positioning scheme and write an application program with 3 levels of distributed transparency for the following query:
- Shows the agent name whose agent code is entered from the terminal
- Shows the dealer code, dealer name of the dealers that supplied the product with the product code entered from the terminal.
- Shows the agent code, agent name of the agents whose addresses are entered from the terminal.
Lesson 4:
VNU Library has two reader service rooms in Thanh Xuan and Cau Giay, each of which manages a number of books. For management, the library has a database with the following global schema:
READER(RNUM, RNAME, CLASS, DNUM): Stores information about readers. DEPART(DNUM, DNAME, DEAN): Stores information about departments.
BOOK(BNUM, BNAME, PRICE, LOC): Stores information about books. BORROW(RNUM, BNUM, MON): Stores information about the book borrowing process.
In which: RNUM-Reader code, RNAME-Reader name, CLASS-Class, DNUM-Department code, DNAME-Department name, DEAN-Department head name, BNUM-Book code, BNAME-Book title, PRICE-Price, LOC-Location, MON-Month of borrowing.
Let's:
1) Draw a connection graph to represent the relationships between relationships. Identify the master relationship and the part relationship of each connection line.
2) Identify quantitative information of the database.
3) Suppose the system has the following applications:
Application 1: Retrieve sets of BOOK by location "Thanh Xuan" Application 2: Retrieve sets of BOOK with price less than 30000
Application 3: Retrieve sets of BOOKs with price greater than or equal to 30000
a) Apply the COM_MIN and PHORIZONTAL algorithms to:
- Horizontal fragmentation of the BOOK relation
- Horizontal fragmentation derives the BORROW relation
b) Draw the connection graph G of the distributed connection BORROW BOOK, determine the type of graph.
4) Suppose there is an application that accesses READER tuples by class.
a) Apply the COM_MIN and PHORIZONTAL algorithms to:
- Horizontal fragmentation of the READER relation
- Horizontal fragmentation derives the BORROW relation
b) Prove the correctness condition when fragmenting the READER relation horizontally.
5) Suppose there is an application that retrieves DEPART with a key code less than „D2‟ and another application that retrieves DEPART with a key code greater than or equal to „D2‟.
a) Apply the COM_MIN and PHORIZONTAL algorithms to:
- Horizontal fragmentation of the DEPART relation
- Horizontal fragmentation derives the READER relation
b) Simplify the query on the Select DNUM, DNAME fragments
From DEPART, READER
Where DEPART.DNUM=READER.DNUM and DEAN=”Raw” and DNUM<„D2‟
6) Suppose the system has an application that retrieves sets of BOOKs by location.
a) Apply the COM_MIN and PHORIZONTAL algorithms to:
- Horizontal fragmentation of the BOOK relation
- Horizontal fragmentation derives the BORROW relation
b) Write an application program with fragmented transparency for the query: Give the book codes and book titles of books borrowed in a month with that month entered from the terminal.
7) Suppose there is an application accessing READER with reader ID „R2‟ and another application accessing READER with different reader ID „R2‟.
a) Apply the COM_MIN and PHORIZONTAL algorithms to:
- Horizontal fragmentation of the READER relation
- Horizontal fragmentation derives the BORROW relation
b) Simplify the query on the Select RNUM, RNAME, CLASS fragments
From BORROW, READER
Where BORROW.RNUM=READER.RNUM and CLASS=”K49 Chemistry” and MON=10
Lesson 5:
A company has 3 branches in the South, Central, and North; each has employees to conduct business. For management, the company has a database with the following global schema:
NV(MANV, HOTEN, LUONG, THUE, MAP): Save information about employees PH(MAP, TENP, MIEN, MAQL): Save information about rooms
NCC(MANCC, TENNCC, DC): Save information about the supplier MH(MAMH, TENMH, DVT, MAU): Save information about the item
KD(MANCC, MAMH, MAP, SL, DG): Store information about business processes. Including: MANV-Employee code, HOTEN-Full name, SALARY-Employee salary,
THUE-Tax rate, MAP-Room code, TENP-Room name, MIEN-Region, MAQL-Manager code, MANCC-Supplier code, TENNCC-Supplier name, DC-Supplier address, MAMH-Item code, TENMH-Item name, DVT-Unit, MAU-Color, QTY-Quantity, DG-Unit price.
And the corresponding relationship is:
Let's:
1) Draw a connection graph to represent the relationships between relationships. Identify the master relationship and the part relationship of each connection line.
2) Identify quantitative information of the database.
3) Suppose the system has an application that accesses supplier sets by address.
a) Apply the COM_MIN and PHORIZONTAL algorithms to:
- Horizontal fragmentation of the NCC relationship
- Horizontal fragmentation of KD relation
b) Prove the correct condition when fragmenting the KD relationship horizontally.
4) Suppose the system has an application that accesses PH sets by domain.
a) Apply the COM_MIN and PHORIZONTAL algorithms to:
- Horizontal fragmentation is the main PH relationship
- Horizontal fragmentation of KD relation
b) Draw the fragment tree of the global relationship PH, KD
5) Suppose the system has an application that accesses PH sets by domain.