Distributed Database Systems
PROJECT DESCRIPTIONS
This project - “a Distributed Database Query Engine Service” - is an integral part of the
undergraduate course Distributed Database Systems. It weighs 30% of the credited mark for
the course, of which 20% attribute to system design and implementation, 5% attribute to
presentation, and 5% attribute to project documentation. Students of this course are required
to form a team of three members for the completion of the project by. The purpose of the
project is
（1） To help the students gain deep and insight understanding on the knowledge of
distributed database systems through a hands-on design and implementation
experiment.
（2） To enhance the problem solving capability of the students by giving them
system requirements while leaving the design and implementation issues be
solved by their own.
（3） To nurture the team-work spirit through cooperative work on a joint project.
System Requirements
1.1 SQL Support
Support the SQL query statement (select…from …where….). The where clause
allows the users to express simple predicates conditions connected by logical AND,
OR, or NOT. Note this select statement has a minimal feature. Other features of the
select statement are not required for the project.
1.2 System Test Environment
The distributed database query engine shall run on three computers (sites) connected
by a network. The architecture of the overall system must be P2P. For every site, the
operating system is Windows, and the local DBMS is MySQL. The communication
mechanism between sites can be socket, RPC, or others. The programming language
for the distributed database query engine is open for every team to choose.
1.3 Distributed Query Processing
The distributed database query engine to be delivered from the project shall have the
components for Query decomposition and localization, and Query optimization,
such as
- The optimization on initial general query tree
- Query tree reduction using fragmentation information
- Network traffic optimization
1
Note for the selected optimization method, its effect must be presented on the
screen. For example, by displaying the initial query tree and the optimized tree to
show the optimization result could be a good option.
1.4 Documentation and Report
For the project, every team shall hand in the following report
The content of the report shall include the details of the design of the distributed
database query engine, and implementation of the distributed query engine such
as the architecture, the query optimization method, the implementation of
communication protocols among sites, testing, etc. as well as a time line for the
project work plan, the instruction of installation, configuration, and operation of
the query engine. Each team is expected to deliver a report and make a
presentation at the class.
Project Team
The project shall be completed by a team of three students. One of the three students
shall take the leading role of the project. The whole project load should be allocated to
members properly. This load allocation must be specified in the final project report.
Time Schedule
The project will start in the 10 weeks and have a final presentation. The final system
evaluation will take place on the 12th week.
2
Example: Distributed Database Schema
1.1 Global Database Tables
We have four global tables. They are Publisher, Book, Customer and Order. Their
conceptual schemas are described below.
Table 1. the global tables
Global Tables
Publisher (id int key, name char(100), nation char(3))
Book (id int key, title char(100), authors char(200), publisher_id int, copies int)
Customer (id int key, name char (25), rank int)
Orders (customer_id int, book_id int, quantity int)
3
1.2 Table Description
Publisher (id int key, name char(100), nation char(3))
Table 2. Description of Table “Publisher”
Item Description Type Value
id (key) The ID of publisher Integer 100001-105000, no duplicate
name The name of publisher String -
nation The nation of publisher String ‘USA’: USA (Proportion:
50%)
‘PRC’: China (Proportion:
50%)
Number of records: 5000
Customer (id int key, name char (25), rank int)
Table 3. Description of Table “Customer”
Item Description Type Value
id (key) The ID of customer Integer 300001-315000, no duplicate
name The name of customer String -
rank The rank of customer Integer 1: Golden Customer (Proportion: 40%)
2: Silver Customer (Proportion: 30%)
3: Bronze Customer (Proportion: 30%)
Number of records: 15000
Book (id int key, title char(100), authors char(25), publisher_id int, copies int)
Table 4. Description of Table “Book”
Item Description Type Value
id (key) The ID of book Integer 200001-250000, no
duplicate
title The title of book String -
authors The authors of book String -
4
publisher_id The ID of publisher that Integer [Link]
publishes book
copies The number of published Integer
copies
Number of records: 50000
Order (customer_id int, book_id int, quantity int)
Table 5. Description of Table “Order”
Item Description Type Value
customer_id The ID of customer who order Integer [Link]
a book
book_id The ID of a ordered book Integer [Link]
quantity Amount of ordered books Integer -
Number of records: 100000
1.3 Fragmentation
Publisher (id int key, name char(100), nation char(3))
Table 6. Vertical Fragmentation of Table “Publisher”
Fragmentation Name Fragmentation Condition
Publisher.1 id < 104000 AND nation=’PRC’
Publisher.2 id < 104000 AND nation=’USA’
Publisher.3 id >= 104000 AND nation=’PRC’
Publisher.4 id >= 104000 AND nation=’USA’
Book (id int, name char(100), authors char(25), publisher_id int, copies int)
Table 7. Horizontal Fragmentation of Table “Book”
Fragmentation Name Fragmentation Condition
Book.1 id < 205000
Book.2 id >= 205000 AND id < 210000
Book.3 id >= 210000
Customer (id int key, name char (25), rank int)
5
Table 8. Vertical Fragmentation of Table “Customer”
Fragmentation Name Fragmentation Condition
Customer.1 (id, name)
Customer.2 (id, rank)
Order (customer_id int, book_id int, quantity int)
Table 9. Horizontal Fragmentation of Table “Order”
Fragmentation Name Fragmentation Condition
Order.1 customer_id < 307000 and book_id < 215000
Order.2 customer_id < 307000 and book_id >= 215000
Order.3 customer_id >= 307000 and book_id < 215000
Order.4 customer_id >= 307000 and book_id >= 215000
1.4 Allocation
Site Configuration: 4 sites deployed at 4 computers.
Scheme1: Basic Fragmentation
Table 1. Allocation Scheme of Basic Fragmentation
At Site Name Fragmentation Name
DB1 at Site 1 Publisher.1, Book.1, Customer.1, Order.1
DB2 at Site 2 Publisher.2, Book.2, Customer.2, Order.2
DB3 at Site 3 Publisher.3, Book.3, Order.3
DB4 at Site 3 Publisher.4, Order.4, Book.3(replica)