Concept explainers
Explanation of Solution
a. Recommendations of required
Recommendations make regarding the type and characteristics of the required data base system are as follows:
- • The magazine publishing organization wants a distributed system with distributed database capabilities.
- • The distributed system will be distributed among the organization locations in Tennessee, South Carolina, Florida, and Georgia.
- • The distributed transparency features such as transaction transparency, fragmentation transparency, performance transparency, and replica transparency are supported by the Distributed Database Management System (DDBMS).
- • Heterogeneous competency is not a compulsory feature since the customers assume there is no existing Database Management System (DBMS) in place and that the organizations needs to normalize on a single DBMS.
Explanation of Solution
b. Data fragmentation needed for tables:
The data fragmentation needed for “CUSTOMER”, and “INVOICE” table is as follows:
The database is horizontally partitioned using the “REGION” attribute for the “INVOICE” table and the “STATE” attribute for the “CUSTOMER” table.
Explanation of Solution
c. Criteria used to partition the database:
The criteria “horizontal fragmentation” is used to partition the database.
Horizontal fragmentation of the “INVOICE” table by region is as follows:
Fragment name | Location | Condition | Node name |
I1 | Tennessee | REGION_CODE = “TN” | NAS |
I2 | Georgia | REGION_CODE = “GA” | ATL |
I3 | Florida | REGION_CODE = “FL” | TAM |
I4 | South Carolina | REGION_CODE = “SC” | CHA |
Horizontal fragmentation of the “CUSTOMER” table by state is as follows:
Fragment name | Location | Condition | Node name |
C1 | Tennessee | REGION_CODE = “TN” | NAS |
C2 | Georgia | REGION_CODE = “GA” | ATL |
C3 | Florida | REGION_CODE = “FL” | TAM |
C4 | South Carolina | REGION_CODE = “SC” | CHA |
Explanation of Solution
d. Database fragments:
Following are the database fragments with node names, location, fragment names, attribute names, and demonstration data.
Fragmentation of “INVOICE” table:
Fragment “I1” of “INVOICE” table with location “Tennessee”, and node “NAS” is as follows:
INV_NUM | REGION_CODE | CUS_NUM | INC_DATE | INV_TOT |
213342 | TN | 10884 | 1-NOV-15 | 45.95 |
209987 | TN | 10993 | 15-FEB-16 | 45.95 |
Fragment “I2” of “INVOICE” table with location “Georgia”, and node “ATL” is as follows:
INV_NUM | REGION_CODE | CUS_NUM | INC_DATE | INV_TOT |
198893 | GA | 11887 | 15-AUG-15 | 70.45 |
224345 | GA | 13558 | 1-JUN-16 | 45.95 |
Fragment “I3” of “INVOICE” table with location “Florida”, and node “TAM” is as follows:
INV_NUM | REGION_CODE | CUS_NUM | INC_DATE | INV_TOT |
200915 | FL | 10014 | 1-NOV-15 | 45.95 |
231148 | FL | 15998 | 1-MAR-16 | 24.95 |
Fragment “I4” of “INVOICE” table with location “South Carolina”, and node “CHA” is as follows:
INV_NUM | REGION_CODE | CUS_NUM | INC_DATE | INV_TOT |
243312 | SC | 21562 | 15-NOV-15 | 45.95 |
231156 | SC | 18776 | 1-OCT-16 | 45.95 |
Fragmentation of “CUSTOMER” table:
Fragment “C1” of “CUSTOMER” table with location “Tennessee”, and node “NAS” is as follows:
CUS_NUM | CUS_NAME | CUS_ADDRESS | CUS_CITY | CUS_STATE | CUS_SUB_DATE |
10884 | James D.Burger | 123 Court Avenue | Menphis | NV | 8-DEC-16 |
10993 | Lisa B.Barnette | 910 Eagle Street | Nashville | NV | 12-MAR-17 |
Fragment “C2” of “CUSTOMER” table with location “Georgia”, and node “ATL” is as follows:
CUS_NUM | CUS_NAME | CUS_ADDRESS | CUS_CITY | CUS_STATE | CUS_SUB_DATE |
11887 | Ginny E.Statton | 335 Main street | Atlanta | GA | 11-AUG-16 |
10993 | Anna H.Ariona | 657 Mason Ave. | Dalton | GA | 23-JUN-17 |
Fragment “C3” of “CUSTOMER” table with location “Florida”, and node “TAM” is as follows:
CUS_NUM | CUS_NAME | CUS_ADDRESS | CUS_CITY | CUS_STATE | CUS_SUB_DATE |
10014 | John T.Chi | 456 Brent Avenue | Miami | FL | 18-NOV-16 |
15998 | Lisa B.Barnette | 234 Ramala Street | Tampa | FL | 23-MAR-17 |
Fragment “C4” of “CUSTOMER” table with location “South Carolina”, and node “CHA” is as follows:
CUS_NUM | CUS_NAME | CUS_ADDRESS | CUS_CITY | CUS_STATE | CUS_SUB_DATE |
21562 | Thomas F.Matto | 45 N.Pratt Circle | Charleston | SC | 2-DEC-16 |
18776 | Mary B.Smith | 526 Boone Pike | Charleston | SC | 28-OCT-17 |
Explanation of Solution
e. Distributed database operations supported at each remote site:
The following table show the map of the location, the fragments at each location, and the type of transaction or request support to need to access the data in the distributed database.
Fragment | NAS | ATL | TAM | CHA |
INVOICE | I1 | I2 | I3 | I4 |
CUSTOMER | C1 | C2 | C3 | C4 |
Distributed operations required | None | None | None | None |
From the above table, there is no interstate access of “INVOICE” or “CUSTOMER” data is required. Therefore, there is no distributed database access is required in the four nodes such as “NAS”, “ATL”, “TAM”, and “CHA”.
Explanation of Solution
f. Distributed database operations supported at headquarters site
The following table show the map of the location, the fragments at each location, and the type of transaction or request support to need to access the data in the distributed database.
Fragment | NAS | ATL | TAM | CHA | Headquarters |
INVOICE | I1 | I2 | I3 | I4 | |
CUSTOMER | C1 | C2 | C3 | C4 | |
Distributed operations required | None | None | None | None | Distributed request |
For the headquarters, the manager needs to able to access the data in all four nodes such through a single SQL request. Therefore, the Distributed Database Management System (DDMS) must support distributed requests.
Want to see more full solutions like this?
Chapter 12 Solutions
Database Systems: Design, Implementation, & Management
- Database systems are often comprised of processes (also known as threads) that communicate with one another by interacting with shared memory resources.What, if any, access to the shared memory area is controlled.Is two-phase locking the most acceptable strategy for serializing access to data structures in shared memory? Your answer should be backed up by logic.arrow_forwardWhen comparing the performance of a distributed database over a wide-area network to that over a local-area network, what are the most important distinctions to be made?arrow_forwardDatabase systems are often composed of processes (or threads) that interact with one another through shared memory resources.How, if at all, access to the shared memory area is limited.Is two-phase locking the best appropriate technique for serializing access to data structures in shared memory? Give a reason for your answer.arrow_forward
- For the aviation industry, "snapshot isolation" serves as an example of a database design. In the case that airlines improve their overall performance, is it appropriate to choose a strategy that does not lend itself to serialization?arrow_forwardJustify why block allocation has a substantial impact on the performance of the database system.arrow_forwardHi Can you please answer the last three parts since the first 3 were answered in the previous request? Given the scenario and requirements in Problem 2, answer the following questions:a. What recommendations will you make regarding the type and characteristics of the required database system?b. What type of data fragmentation is needed for each table?c. What criteria must be used to partition each database?d. Design the database fragments. Show an example with node names, location, fragment names, attribute names, and demonstration data.e. What type of distributed database operations must be supported at each remote site?f. What type of distributed database operations must be supported at the headquarters site?arrow_forward
- In a distributed database environment, what are the key challenges related to data consistency, and how can these challenges be addressed using techniques such as two-phase commit (2PC) and three-phase commit (3PC)?arrow_forwardConsider the wide variety of data-request use cases that can arise in a distributed database architecture.arrow_forwardWhen should data replication be used in distributed database systems to address data fragmentation? There appears to be an incompatibility between the methods of replication and fragmentation. When comparing horizontal and vertical fragmentation, what are the key differences? Give me an illustration of your point of view, please.arrow_forward
- Consider the many data request scenarios that may arise in a distributed database system.arrow_forwardWhat are the primary distinctions between a local-area network and a wide-area network, and how do these distinctions play a role in the design of the architecture of a distributed database?arrow_forwardPossible implications for the design of a distributed database may be drawn from the differences between a local area network and a wide area network.arrow_forward
- Fundamentals of Information SystemsComputer ScienceISBN:9781337097536Author:Ralph Stair, George ReynoldsPublisher:Cengage LearningPrinciples of Information Systems (MindTap Course...Computer ScienceISBN:9781305971776Author:Ralph Stair, George ReynoldsPublisher:Cengage LearningSystems ArchitectureComputer ScienceISBN:9781305080195Author:Stephen D. BurdPublisher:Cengage Learning
- Principles of Information Systems (MindTap Course...Computer ScienceISBN:9781285867168Author:Ralph Stair, George ReynoldsPublisher:Cengage Learning