a) Given the following scenario for a company database system: ORDER (OrderNo, OrderQty, OrderDate, OrderStatus. Prodid{FK},...

 

Transcribed Image Text:

a) Given the following scenario for a company database system: ORDER (OrderNo, OrderQty, OrderDate, OrderStatus. Prodid{FK}, Suppld{FK}) PRODUCT (Prodld, ProdName. ProdQty) SUPPLIER (Suppld, SuppName. SuppAddress. SuppRegion. SuppAccountNo. SuppAccBal. SuppPhone) The new management of the company has decided to develop a distributed DBMS for the company database. The management has decided to fragment the Supplier table according to supplier region. The supplier region is divided into North, East and West regions. Then the management also wants to further fragment based on the supplier info and supplier account for the supplier fragment in the West region. Name a suitable fragmentation schema for the relations based on the above scenario. Show the fragmentation using SQL. (12 marks) b) Consider the following state of the log record found on disk immediately after a system crash. TIME LOG 00 T1 begin 01 T2 begin 02 Update: T1 writes P1 03 Update: T2 writes P2 04 CНECКРОINT 05 T3 begin 06 Update: T3 writes P8 07 T1 commit 08 Update: T2 writes P4 09 ТЗ commit 10 Update: T2 writes P5 11 XXX SYSTEM EAILURE XXX i. Translate the above table into a diagram that shows the concurrent executing transactions. (5 marks) i. Explain the restart work assuming the recovery manager uses the deferred update approach. (3 marks) a) Given the following scenario for a company database system: ORDER (OrderNo, OrderQty, OrderDate, OrderStatus. Prodid{FK}, Suppld{FK}) PRODUCT (Prodld, ProdName. ProdQty) SUPPLIER (Suppld, SuppName. SuppAddress. SuppRegion. SuppAccountNo. SuppAccBal. SuppPhone) The new management of the company has decided to develop a distributed DBMS for the company database. The management has decided to fragment the Supplier table according to supplier region. The supplier region is divided into North, East and West regions. Then the management also wants to further fragment based on the supplier info and supplier account for the supplier fragment in the West region. Name a suitable fragmentation schema for the relations based on the above scenario. Show the fragmentation using SQL. (12 marks) b) Consider the following state of the log record found on disk immediately after a system crash. TIME LOG 00 T1 begin 01 T2 begin 02 Update: T1 writes P1 03 Update: T2 writes P2 04 CНECКРОINT 05 T3 begin 06 Update: T3 writes P8 07 T1 commit 08 Update: T2 writes P4 09 ТЗ commit 10 Update: T2 writes P5 11 XXX SYSTEM EAILURE XXX i. Translate the above table into a diagram that shows the concurrent executing transactions. (5 marks) i. Explain the restart work assuming the recovery manager uses the deferred update approach. (3 marks)

Answer rating: 100% (QA)

a There are three different types of fragmentation 1 Vertical Fragmentation is the first typ... View the full answer