In this project, you’re asked to implement some of the services for the DreamHome case study problem in a distributed database environment. The following list additional assumptions for the case study problem:
There're a total of two branch offices. These offices are located in London and Glasgow, respectively. Each branch office has a database server locally.
The headquarter of the company is in London and has its database located in the same server as the branch office in London.
The branch offices are given the authority to hire new staffs or change the current status of existing staffs. However, to reduce the overhead of personnel management, all human resource related information processing are handled by the headquarter.
Each branch office are responsible to manage properties, renters, as well as property owners that are local to the branch office. However, the headquarters may request summary reports periodically.
Occasionally, a branch office may encounter a
potential customer who is interested in services provided by the other
branch office.
The following documents need to be submitted on Wednesday, April 24. No handwriting document is acceptable. It is highly recommended that you accumulate the documents throughout the project. Make sure all SQL statements as well as PL/SQL codes are well commented.
The ER diagram for the project according to the data and transaction requirements provided by the textbook.
Fragmentation and allocation schema.
Justify your fragmentation and allocation based on locality of reference, improved reliability and availability, acceptable performance, balanced storage capacities and costs, and minimal communication costs
A hard copy of the SQL statements that you used to create tables for the project.
Database links that your created for the project. Record the SQL statements that are used to create the database links. For each link, explain the benefit of using the link.
You're required to use the following approach to achieve location transparency for the project. Record the SQL statements that are used.
Synonyms - illustrate the use of synonym with one example.
Views - illustrate the use of view with one example. Explain the purpose of the example view and how location transparency is achieved.
Stored procedures - illustrate the use of stored procedure with one example. Explain the purpose of the example stored procedure and how location transparency is achieved.
Triggers in distributed database:
Create a trigger from the headquarter database to update the branch databases. Record the trigger and explain its purpose.
Create a trigger from Glasgow's database to update headquarter database. Record the trigger and explain its purpose.
Transaction management in distributed database:
Create a distributed transaction that transfers a renter from a property in Glasgow to a property in London. Record the transaction.
Create a distributed transaction that generates a summary report about status of all properties in both branch offices. Record the transaction.
Discuss transaction management of the above two transactions when they are executed concurrently.
Discuss transaction management for the distributed transaction in (a) if failure occurs before the transaction is permanently written.
Replication - due to the limit of time, we'll only experiment with the Read-Only Primary Key materialized view for this project. At least one of the following materialized views need to include subquery. Record the SQL statements that are used to perform the following tasks.
Create one read-only primary key materialized view in the London branch to be refreshed by tables located in the headquarter database. Allow manually refresh of the materialized view. Explain the purpose of creating such view.
This project must be demonstrated to me personally at a time TBD. These demonstrations will be live. They will occur in UH272. You should plan on 20 to 30 minutes for your demonstration.