Distributed Database System Design - Essay Example

Distributed Database System Design: A Distributed Database System is a collection of multiple, logically connected databases distributed over a computer network. A Distributed Database System facilitates various applications to have an easy access to the data from local as well as remote databases. Client-server architecture is used by the Distributed Database Systems, whereby the database server is the Oracle software that manages the database, and the client may be an application that requests information from the database server. Each computer in this network consists of a node which can host multiple databases. The following diagram depicts a typical Oracle Distributed Database System Design: The host for the HQ database acts as a database server when a statement is issued against its local data but it acts as a client when a statement is issued against remote data. The client can access information by directly or indirectly connecting to the database server. For example, if a client connects to a server requests access to information from a database contained on that server a direct connection established. To elaborate further, if the client connects to the Head Quarter database and requests access to the data held in warehouse (inventory ) or retail stores (item, color ) tables, he can do so by a simple query – “ select * from inventory;”. This is a direct query as the object, i.e., the inventory table is not located on the remote database. On the other hand if the client connects to a server and accesses information from a database contained in another server, then an indirect connection is established. For example, if the client connects to one the retail server and accesses information from the database contained in the Brisbane Warehouse server or Melbourne Warehouse server, then an indirect connection is established as the object being accessed is not on the server to which the client is directly connected. A Distributed Database System is not just a mere collection of files that can be stored individually at each node of a computer network. In order to for a proper Distributed Database System, all the relevant files need to be stored in a logical manner, i.e., the files should be logically related, with a proper structure that facilitates easy and convenient access to the databases via a common interface. In a distributed database system, the business information of a company is distributed over a network, hence it is practical and preferable to localize the data in such a way that the information relating to the Brisbane Warehouse is stored in Brisbane and those related to the Melbourne Warehouse will be stored in Melbourne. For example, any query related to the order_id, inventory, item_id, inventory_quantity etc related to a specific location is stroed in each partition at a different site. This is known as fragmentation, where the information about one site is stored in its own server as well as some other server located at a remote site. Furthermore, in DDBS duplicating data is another feasible option, to enhance the performance and reliability. So when a user sends a query (as shown in the following diagram), that needs information from two tables, customer and orders, which could be located at two different sites, the user can still access the data without bothering about the fragmentation, location or replication of the data as the system handles these issues by itself. Data Independence in a Distributed Database System Data independence is a fundamental form of data transparency. It refers to the ability of user applications to refrain from changes in the definition and organization of data and vice versa. Data independence can further be classified into two major categories, logical data independence, which refers to the ability of the local user applications to restrict changes in the logical structure of the database and physical data independence, which refers to the hiding the details of the storage structure from user applications. It is mainly due to the fact that while writing the user applications it is not supposed to be concerned about the manner in which the physical data is organized. For example, the data could be organized on separate disks, or fragmented and stored in different disks or distributed over several storage hierarchies. Thus the user application need not be modified in accordance with any concurrent changes in the structure of organization of data. Transaction Processing in a Distributed System A transaction can be defined as a logical unit of work which comprises of or more SQL statements executed by a single user. It begins with the users first executable SQL statement and ends when it is committed or rolled back by that user. The transactions could be either remote or distributed. A remote transaction usually comprises of only statements that seeks to access a single remote node whereas a distributed transaction on the other hand comprises of statements that access more than one node. Remote SQL Statements A remote SQL Statement is a query which is used to select information from one or more remote tables located at the same remote node. For example, the query: Select * from MGT1.orders@inventory.au.clear_waters.com; could be used to access data from the orders table in the MGT1 schema of the remote inventory database. Similarly, a remote update statement or query could be used to modify data in one or more tables, where all the tables are located at the same remote node. For example, the query: Update MGT1.orders@customer.au.clear_waters.com Set o_methpmt=’check’ Where c_id=2; A subquery, that retrieves data from more than one remote nodes, could also be included in an update statement, but since the update query is executed at only a single remote node, it is classified as a remote update. Distributed SQL Statements As opposed to the Remote SQL Statement, the Distributed SQL Statement, retrieves information from two or more nodes. For example, the following query retrieves data from the local database as well as from the remotely located Head Quarter database. Select c_first,c_last, c_email_id From MGT1. Customer, MGT1.orders@customer.au.clear_waters.com Where customer.c_id=orders.c_id; A distributed update statement could also be used for modifying data on two or more nodes by using a PL/SQL program unit like, procedure or trigger which includes use of two or more remote updates requiring access to data located at different nodes. The following PL/SQL codes could be used for a distributed update statement: BEGIN UPDATE MGT1.CUSTOMER@ORDERS.AU.CLEAR_WATERS.COM SET C_EPHONE = 7155559000 WHERE C_ID = 1; UPDATE MGT1.CUSTOMER SET EMAIL_ID = ‘ME5@ABC.COM’ WHERE C_ID = 5; END; COMMIT; An efficient database guarantees a two phase commit mechanism, i.e., all transactions either distributed or non distributed, should perform the commit or roll back operations as a single unit. Also the effects of the current transactions should not be made visible to any other transactions at any node. This type of transparency should be made available for all transactions / operations irrespective of its type, such as queries, updates or remote procedure calls. Transparency in a Distributed Database System The basic intention or aim of a transparent distributed database design is to make the DDBS appear as a single database. Transparency could be related to Network transparency, Replication Transparency or Fragmentation Transparency. However there are other types of transparency features available at the disposal of the database administrator, which includes, location transparency. This is a unique feature that allows the application developers and administrators to hide the physical location of the database objects from applications and users. This type of transparency usually exists in situations where the user has universal access to any database object such as tables, irrespective of the nodes to which the application is connected. The benefits of such type of transparency is that the access to a remote data becomes relatively easier and convenient as the database users do not need to know the exact location of the database object being accessed. Secondly, the administrator’s can easily move any database object, without causing any significant impact on the end-users or existing database applications. Distributed Database Design for Clear Waters Trading Company Owing to the security requirements, the best possible design for the database would be one where the database is distributed still an integrated view of the database is provided to users. This requirement is well satisfied by client-server architecture. The basic idea is to distinguish the functionality which is needed to be provided and divide these functions into server functions and client functions. This eases management of complex modern DBMSs and the complexity of distribution. Also with client-server architecture we can enforce control over access to database. We can design the database as semi-autonomous thin client multi server-multi client architecture. A semi-autonomous system consists of DBMSs that can operate independently but have decided to participate in a federation to make their local data shareable. As shown in figure 1, there are three servers and each server has a set of clients. Each client can access its own database and intercommunication between servers allows clients of server1 to access database on server2. Each client can log into its own server known as home server while connections to foreign servers can be managed by home servers, thereby making access to foreign servers transparent. Figure 1: Semi-autonomous Multi Server- Multi Client DDBMs. Hence, the design shown in figure 1 is most appropriate considering the requirements of the case. Now to implement this architecture, we need to add one more table “Warehouse” to the existing database. This table will have the following fields: (insert figure 2 below) W_name will identify particular warehouse viz. Brisbane or Melbourne. Figure 3 below shows horizontal fragmentation of the table Warehouse. Warehouse is fragmented into two fragments: BW and MW. BW will fetch warehouse details for Brisbane Warehouse whereas Melbourne Warehouse details will be retrieved by the fragmentation MW. Figure 4 shows how the data collected at Brisbane Warehouse Server (containing BW fragment) and Melbourne Warehouse Server (containing MW fragment) will be merged at the Brisbane Headquarter Server (table BHQ). Transparent Management of Distributed and Replicated Data The term Transparency here, refers to separating the higher-level semantics of the system from lower-level implementation issues. A transparent system helps in hiding some of the implementation details from the end users. One of the most significant uses/ advantages of a transparent distributed management system is the high level of support, that it provides for developing of complex applications. In the given case study, the Clear Waters Trading company has its Head Quarters in Brisbane, two warehouses – one each in Brisbane and Melbourne, and 60 retail stores scattered across the country. Business Justification: We chose semi-autonomous thin client multi server-multi-client architecture as our design. There will be in all four servers. One server at Brisbane Headquarters, one server at Brisbane containing Brisbane Warehouse details one at Melbourne containing Melbourne Warehouse details and the fourth will be Retail Server which will hold information of all the 60 retail stores. Brisbane Warehouse Server and Melbourne Warehouse Server will contain fragments BW and MW respectively. All the queries will be processed at corresponding servers. Finally data from both the fragments will be sent to the Brisbane Headquarter Server where a join operation will be performed to merge data from both the fragments. This design is secured because of its client-server nature which enforces control over user actions. Also it is economical since the join operation, which is considerably costly will be performed only at one place i.e. Brisbane Headquarters. Select queries which are comparatively cheaper will be executed at Retail Server as well as two Warehouse Servers. Hence, this design is both economical as well as highly secured. A1) Retail server will allow all its 60 retailers to access the main server located at Brisbane Headquarters. Each retailer can place order at the appropriate warehouse server, check the status of its order, shipment details, edit order information, etc. For example retailer 1 can place order at Brisbane Warehouse Server. The information contained in the database of Brisbane Warehouse Server will be made available to retailer 1. Also by the end of day, all the sales information of retailer 1 will be updated at the Retail Server which in turn will be reflected on the Brisbane Headquarter Server. Moreover customer information will be also stored in the Retail Server database. Customers can also log into the online retail store where they can place new orders and also keep track of their existing orders. Retail Server will have two account types: one for retailer and the other for customer. An account of type customer will not be allowed any kind of direct access to either the Warehouse databases or the Headquarter database. On the other hand retailer account type will be allowed direct access to the other databases but at the same time any kind of insert queries will not be allowed on these databases. Only select queries will be allowed. The two Warehouse Servers, Brisbane Warehouse Server and Melbourne Warehouse Server will hold information like order id, quantity, shipment id, item id and inventory id. Retailers can access the warehouses through the Retail Server. Queries to Brisbane Warehouse will be directed to Brisbane Warehouse Server and Melbourne Server to Melbourne Warehouse Server. Finally the data on both these servers will be sent to the Brisbane Headquarter Server where it will be joined so that a single table will contain information regarding both the warehouses. Brisbane Headquarter Server will communicate with the remaining three servers viz. Brisbane Warehouse Server, Melbourne Headquarter Server and the Retail Server. Retail Server will collect updates from all its retailers and a single update from this server will be posted to the Headquarter Server. In much a similar way, both the warehouse servers, will individually collect their updates and send it to the Brisbane Headquarters Server. A2) At the Brisbane Headquarter mainly join and update operations will be performed. Warehouse information from both the warehouse servers will be merged here. Updates from Retail Server will simultaneously update the database at the Brisbane Headquarter Server. Retailers will communicate with the Brisbane Headquarter through Retail Server. In general clients of one server will be able to communicate with other servers and their clients through their own server. A3) Each warehouse will obtain information from retailers who place orders to appropriate warehouses. This information needs to be updated on both the servers. Hence the database operations on Warehouse servers will be select and update. A4) A retailer will place new orders, edit existing orders and check the status of existing orders. Thereby each retailer must be able to perform select, insert and update operations on the Warehouse Servers’ database which will be accessed through the Retail Server. A5) The successful operation of online retail store is fully dependent on the Retail Server. This is because retailers will be connected to Warehouse Servers and the Headquarter Server only through the Retail Server. Moreover due to security constraints imposed in the database design of the system under consideration, Warehouse databases and Headquarter databases can only be accessed through the Retail Server. Retail Server will basically require select and update operations which will enable successful operation of the online retail store. A6) Warehouse locations must be the criteria for the partition of tables in the database. This is because; Warehouse is the place where most of the data is constantly processed. Since there are only two warehouses, we can easily separate them out and retailers can query appropriate warehouse database. Updates from both the warehouses will then be posted to the Headquarter Server where it will be merged. A7) A8) A9) SQL queries showing union, intersection and minus of the horizontal fragments of the fragmented customer table: Intersection Select * from BW intersect select * from MW; Union Select * from BW Union Select * from MW; Minus Select * from BW Minus Select * from MW; REFERENCES M. Tamer Ozsu, Patrick Valduriez, S. Sridhar, Principles of Distributed Database Systems, second edition, Pearson Education Publication, 2007. Oracle8i Distributed Database Systems : Release 2 (8.1.6), Part Number A76960-01, June 1, 2008. Available from: < http://www.ecst.csuchico.edu/~oracle/server.817/a76960/ds_conce.htm> SQL CODES Creating table customer Create table customer (c_id number(5), c_last varchar2(30), c_first varchar2(30), c_mi char(1), c_birthdate date, c_address varchar2(30), c_state char(2), c_zip varchar2(10), c_dphone varchar2(10), c_ephone varchar2(10), c_userid varchar2(50), c_password varchar2(15), constraint customer_c_id_pk primary key (c_id)); Creating table: order_source Create table order_source (os_id number(3), os_desc varchar2(30), constraint order_source_os_id_pk primary key (os_id)); Creating table: orders Create table orders (o_id number(3), o_date date, o_methpmt varchar2(10), c_id number(5), os_id number(3), constraint orders_o_id_pk primary key (o_id)); Creating table: category Create table category (cat_id number(3), cat_desc varchar2(30), constraint category_c_id_pk primary key (cat_id)); Creating table: item Create table item (item_id number(8), item_desc varchar2(30), cat_id number(2), item_image blob, constraint item_item_id_pk primary key (item_id), constraint item_cat_id_fk foreign key (cat_id) references category (cat_id)); Creating table: color Create table color (color varchar2(20), constraint color_color_pk primary key (color)); Creating table: inventory Create table inventory create table inventory (inv_id number(10), item_id number(8), color varchar2(20), inv_size varchar2(10), inv_price number(6,2), inv_qoh number(4), constraint inventory_inv_id_pk primary key (inv_id), constraint inventory_item_id_fk foreign key (item_id) references item(item_id), constraint inventory_color_fk foreign key (color) references color(color)); Creating table: shipment create table shipment (ship_id number(10), ship_date_expected date, constraint shipment_ship_id_pk primary key (ship_id)); Creating table: shipment_line create table shipment_line (ship_id number(10), inv_id number(10), sl_quantity number(4), sl_date_received date, constraint shipment_line_ship_id_fk foreign key (ship_id) references shipment(ship_id), constraint shipment_line_inv_id_fk foreign key (inv_id) references inventory(inv_id), constraint shipment_line_shipid_invid_pk primary key(ship_id, inv_id)); Creating table: order_line create table order_line (o_id number(8), inv_id number(10), ol_quantity number(4) not null, constraint order_line_o_id_fk foreign key (o_id) references orders(o_id), constraint order_line_inv_id_fk foreign key (inv_id) references inventory(inv_id), constraint order_line_oid_invid_pk primary key (o_id, inv_id)); INSERTING RECORDS Customer table insert into customer values (1, harris, paula, e, to_date(04/09/1953, mm/dd/yyyy), 1156 water street, apt. #3, osseo, wi, 54705, 7155558943, 7155559035, harrispe, asdfjk); insert into customer values (2, garcia, maria, h, to_date(07/14/1958, mm/dd/yyyy), 2211 pine drive, radisson, wi, 54867, 7155558332, 7155558332, garciamm, 12345); insert into customer values (3, miller, lee, null, to_date(01/05/1936, mm/dd/yyyy), 699 pluto st. nw, silver lake, wi, 53821, 7155554978, 7155559002, millerl, zxcvb); insert into customer values (4, chang, alissa, r, to_date(10/01/1976, mm/dd/yyyy), 987 durham rd., apple valley, mn, 55712, 7155557651, 7155550087, changar, qwerui); insert into customer values (5, edwards, mitch, m, to_date(11/20/1986, mm/dd/yyyy), 4204 garner street, washburn, wi, 54891, 7155558243, 7155556975, edwardsmm, qwerty); insert into customer values (6, nelson, kyle, e, to_date(12/04/1984, mm/dd/yyyy), 232 echo rd., minnetonka, mn, 55438, 7151113333, 7155552222, nelsonke, clever); Inserting records in order_source insert into order_source values (1, winter 2005); insert into order_source values (2, spring 2006); insert into order_source values (3, summer 2006); insert into order_source values (4, outdoor 2006); insert into order_source values (5, childrens 2006); insert into order_source values (6, web site); Inserting records in orders insert into orders values (1, to_date(05/29/2006, mm/dd/yyyy),cc,1,2); insert into orders values (2, to_date(05/29/2006, mm/dd/yyyy),cc,5,6); insert into orders values (3, to_date(05/31/2006, mm/dd/yyyy),check,2,2); insert into orders values (4, to_date(05/31/2006, mm/dd/yyyy),cc,3,3); insert into orders values (5, to_date(06/01/2006, mm/dd/yyyy),cc,4,6); insert into orders values (6, to_date(06/01/2006, mm/dd/yyyy),cc,4,3); inserting records in category insert into category values (1, womens clothing); insert into category values (2, childrens clothing); insert into category values (3, mens clothing); insert into category values (4, outdoor gear); Inserting records in item insert into item values (1, mens expedition parka, 3, empty_blob()); insert into item values (2, 3-season tent, 4, empty_blob()); insert into item values (3, womens hiking shorts, 1, empty_blob()); insert into item values (4, womens fleece pullover, 1, empty_blob()); insert into item values (5, childrens beachcomber sandals, 2, empty_blob()); insert into item values (6, boys surf shorts, 2, empty_blob()); insert into item values (7, girls soccer tee, 2, empty_blob()); Inserting records in color insert into color values (sky blue); insert into color values (light grey); insert into color values (khaki); insert into color values (navy); insert into color values (royal); insert into color values (eggplant); insert into color values (blue); insert into color values (red); insert into color values (spruce); insert into color values (turquoise); insert into color values (bright pink); insert into color values (white); Inserting records in inventory insert into inventory values (1, 2, sky blue, null, 259.99, 16); insert into inventory values (2, 2, light grey, null, 259.99, 12); insert into inventory values (3, 3, khaki, s, 29.95, 150); insert into inventory values (4, 3, khaki, m, 29.95, 147); insert into inventory values (5, 3, khaki, l, 29.95, 0); insert into inventory values (6, 3, navy, s, 29.95, 139); insert into inventory values (7, 3, navy, m, 29.95, 137); insert into inventory values (8, 3, navy, l, 29.95, 115); insert into inventory values (9, 4, eggplant, s, 59.95, 135); insert into inventory values (10, 4, eggplant, m, 59.95, 168); insert into inventory values (11, 4, eggplant, l, 59.95, 187); insert into inventory values (12, 4, royal, s, 59.95, 0); insert into inventory values (13, 4, royal, m, 59.95, 124); insert into inventory values (14, 4, royal, l, 59.95, 112); insert into inventory values (15, 5, turquoise, 10, 15.99, 121); insert into inventory values (16, 5, turquoise, 11, 15.99, 111); insert into inventory values (17, 5, turquoise, 12, 15.99, 113); insert into inventory values (18, 5, turquoise, 1, 15.99, 121); insert into inventory values (19, 5, bright pink, 10, 15.99, 148); insert into inventory values (20, 5, bright pink, 11, 15.99, 137); insert into inventory values (21, 5, bright pink, 12, 15.99, 134); insert into inventory values (22, 5, bright pink, 1, 15.99, 123); insert into inventory values (23, 1, spruce, s, 199.95, 114); insert into inventory values (24, 1, spruce, m,199.95, 17); insert into inventory values (25, 1, spruce, l, 209.95, 0); insert into inventory values (26, 1, spruce, xl, 209.95, 12); insert into inventory values (27, 6, blue, s, 15.95, 50); insert into inventory values (28, 6, blue, m, 15.95, 100); insert into inventory values (29, 6, blue, l, 15.95, 100); insert into inventory values (30, 7, white, s, 19.99, 100); insert into inventory values (31, 7, white, m, 19.99, 100); insert into inventory values (32, 7, white, l, 19.99, 100); Inserting records in shipment insert into shipment values (1, to_date(09/15/2006, mm/dd/yyyy)); insert into shipment values (2, to_date(11/15/2006, mm/dd/yyyy)); insert into shipment values (3, to_date(06/25/2006, mm/dd/yyyy)); insert into shipment values (4, to_date(06/25/2006, mm/dd/yyyy)); insert into shipment values (5, to_date(08/15/2006, mm/dd/yyyy)); inserting records into shipment_line insert into shipment_line values (1, 1, 25, to_date(09/10/2006, mm/dd/yyyy)); insert into shipment_line values (1, 2, 25, to_date(09/10/2006, mm/dd/yyyy)); insert into shipment_line values (2, 2, 25, null); insert into shipment_line values (3, 5, 200, null); insert into shipment_line values (3, 6, 200, null); insert into shipment_line values (3, 7, 200, null); insert into shipment_line values (4, 12, 100, to_date(08/15/2006, mm/dd/yyyy)); insert into shipment_line values (4, 13, 100, to_date(08/25/2006, mm/dd/yyyy)); insert into shipment_line values (5, 23, 50, to_date(08/15/2006, mm/dd/yyyy)); insert into shipment_line values (5, 24, 100, to_date(08/15/2006, mm/dd/yyyy)); Inserting records in order_line Insert into order_line values (5,8,1); Insert into order_line values (6,8,2); Insert into order_line values (6,7,3) create table warehouse create table warehouse ( w_id number(3), o_id number(3), w_name varchar2(18), w_qty number(3), ship_id number(3), item_id number(3), inventory_id number(3)); create table bw create table BW (w_id number(3), w_name varchar2(10), o_id number(3), w_qty number(3),ship_id number(3), item_id number(3), inventory_id number(3)); create table mw create table MW (w_id number, w_name varchar2(10), o_id number(3), w_qty number(3), ship_id number(3), item_id number(3), inventory_id number(3)); adding primary key to warehouse table alter table warehouse add constraint w_id_pk primary key (w_id)); adding foreign key to bw table alter table BW add constraint w_id_fk foreign key (w_id) references warehouse (w_id); adding foreign key to mw table alter table MW add constraint w_id_mw_fk foreign key (w_id) references warehouse (w_id); joining bw and mw tables adding primary key to BW table alter table BW add constraint w_id_BW_pk primary key (w_id); Adding foreign key to MW table Alter table MW Add constraint w_id_2_fk Foreign key (w_id) References BW (w_id); PART 1. QUESTION 1. LINKING TABLES Linking tables: ORDERS AND CATEGORY STEPS: 1. Insert a new column - “cat_id” in Orders table Alter table orders Add (cat_id number(5)); 2. Add a primary key in Category table Alter table Category Add primary key (cat_id); 3. Add a foreign key in orders table (to link these two tables) Alter table orders Add constraint cat_id_fk foreign key(cat_id) References category (cat_id); To check if the two tables are joined or not, run this querry: Select orders.o_id, category.cat_desc, orders.o_date From category, orders; 4. CREATING INDEX Create index c_last On customer (c_last); To check / view the index Select index_name, index_type, table_name From user_indexes; 5. UPDATING RECORD IN CUSTOMER TABLE Update customer Set c_address = ’17 changed road’ Where c_id = 4; 5. ADDING A NEW COLUMN TO CUSTOMER TABLE Alter table customer Add (email_id varchar2(20)); INSERTING SAMPLE DATA Select c_id, c_last, c_first, email_id from customer; 6. A. QUERRY TO SELECT CUSTOMER ID, CUSTOMER LAST NAME AND CUSTOMER FIRST NAME FROM THE CUSTOMER TABLE. SELECT C_ID, C_LAST, C_FIRST FROM CUSTOMER; b. Write a query to select all attributes from the SHIPMENT_LINE table where quantity is between 10 and 150. Select * from shipment_line Where sl_quantity between 10 and 150; c. Write a query to order the list of all attributes from the smallest to largest quantity, in the SHIPMENT_LINE table. Select * from shipment_line Order by sl_quantity; d. Write a query using an aggregate function to list the records from the INVENTORY table, of the items that have the highest price. Select max(inv_price), item_id From inventory Group by item_id; e. Create a view named REORDER to display the items from the INVENTORY table where the quantity on hand is less than 60. Your view should display the item id, colour, size and price fields for the selected records. Create view reorder As select item_id, color, inv_size, inv_price, inv_qoh From inventory Where inv_qoh < 60; Select * from reorder; Read More