Consider the following relational database for the GBP case.

Green Book Publisher (GBP) wants to maintain data about its authors, the books, editorial staff, and their work for the books.

Each author has a unique author ID, name, address, telephone number, unique email address. A book has a unique ISBN, title, category, publication year, and publishing city. An author can write many books, and each book can be authored by many authors. An editorial staff member has a unique employee ID, employee name, title, and year hired. Some editorial staff members supervise others. Each book is usually edited by many editorial staff members. Each editorial staff member generally works for many books, but may not work for any book yet. Each time when an editorial staff member performs a specific editorial work for the book, the job type, start date, and end date are recorded. An editorial staff member can work for a book for multiple times with multiple start dates.

The database with five tables is described as follows.

BOOK

*ISBN

BookTitle

BookCategory

BookYear | BookCity

AUTHOR

*AuthorID

AuthorName

AuthorAdd

AuthorPhone

AuthorEmail

AUTHORSHIP

*AuthorID

*ISBN

AuthorOrder

EDITSTAFF

*EmployeeID

EmployeeName

EmployeeTitle

EmployeeYear

SupervisorID

EDITINGJOB

*EmployeeID

*ISBN

*StartDate

EndDate

JobType

GBP now has offices in four international locations: Boston (is also the headquarters), Shanghai, Paris, and Vancouver are three international offices. The database structure remains the same except that the Publishing City attribute of the BOOK relation now indicates in which of the four offices the book record is located.

GBP has decided to reconfigure its database as a distributed database among its four offices for their books. Distributed and replicated among these four offices, the tables have the following characteristics:

The AUTHOR table consists of 6,000 records. The entire table is heavily used in the Boston headquarters for centralize the review process as well as advertising. However the records of individual authors are also used frequently in the international offices where their authored books are published.

The BOOK table consists of 80,000 records and is used most heavily in the international offices where the books are published. The headquarters also uses the BOOK table, but do not take the responsibility of maintenance. EDITSTAFF table consists of 60,000 records with a situation similar to the BOOK table. The EDITINGJOB data have a huge volume, and are used and updated with the data of BOOK and EDITSTAFF. In other words, the three tables are treated in the similar way.

Design a distributed relational database for GBP. Specify (using a data deployment matrix) your design of data deployment placement, partitioning (H. P. or V.P.), and replication for AUTHOR, BOOK, and EDITSTAFF (3 tables only) . (5 point)