Posted at 12.31.2018
With the commercial development the surroundings is polluting significantly. So this issue becomes the burning up question of Bangladesh and it should be solved as Lasting Development aspect. In Bangladesh, the administrative centre city Dhaka is the nerve centre of all activities. Today's population of the location is just about 12 million and its growth rate is about 3%. The city is nearly encircled by a circular river system, which include Turag, Buriganga, Dhaleswari, Balu, Lakhya and Tongi Khal. The top normal water along these peripheral waterways is known to be highly polluted scheduled to municipal and commercial untreated waste waters that are discharged. At present moment, Dhaka Water Resource and Sewerage Authority (DWASA) is highly dependent on ground normal water for the supply in the town. As a result the average earth water depletion in most area in the town apparently around 1-3 m/calendar year. The present rate of depletion is alarming since it could cause environmental hazards such as land subsidence continuous water logging, alteration in vegetation etc. So, more research work should be achieved on surface normal water to make it functional. In this paper water quality data of peripheral streams of Dhaka city of different researchers of different schedules have been gathered, extracted and then stored in a data warehouse. Data warehouse is a subject-oriented, integrated, time-variant and non-volatile assortment of data designed to facilitate time-series examination, development forecasting and decision making. This process brings normal water quality data in the platform which gives research workers or related organizations the ability of digesting data quickly to learn days gone by, present and future tendency of water pollution in peripheral waterways of Dhaka city. In addition, it gives the chance of comparing the info from various experts. Water quality data include physicochemical variables such as DO (dissolved air), BOD (biochemical oxygen demand), COD (chemical air demand) and TDS (total dissolved solids) as well as concentrations of varied chemical pollutants (acid ions, metal ions, ammonia etc).
Keywords-Data warehouse, Peripheral River, Surface and Floor Water, Water Pollution.
Today, there a wide range of towns worldwide facing an serious shortage of drinking water. Dhaka City (the Capital of Bangladesh) is one such city labeled as a mega city (i. e. , metropolitan areas with populace exceeding 10 million) of the world , . The town is nearly bounded by a round river system, which include: Turag, Buriganga, Dhaleswari, Balu, Lakhya and Tongi Khal (Shape-2). The surface normal water along these peripheral waterways is known to be highly polluted scheduled to municipal and professional untreated wastewaters that are discharged. At present moment, Dhaka Drinking water Source and Sewerage Power (DWASA) is highly dependent on ground normal water for the supply in the city. As a result the average surface water depletion generally in most area in the town is reportedly around 1-3 m/time. The present rate of depletion is alarming since it could cause environmental hazards such as land subsidence, extended water logging, alteration in vegetation etc. , , . Thus, there can be an urgent need to ease the demand on the top aquifers and explore sustainable sources to augment the present water supply. A ecological solution is necessary. Therefore a conjunctive use of groundwater and surface water in order to take care of the balance between anthropogenic demand and water's natural availability , , .
To find the lasting solution, more research should be achieved on surface water that can be easier through water quality data warehouse. The primary concept of data warehousing is the fact the data stored for analysis can most effectively be utilized by separating the data from the operational systems. Furthermore to producing standard reports data warehousing systems support very advanced online analysis including multi dimensional analysis [INM96CHA97]. On this paper water quality data warehouse of peripheral streams of Dhaka city has been designed and developed.
The paper is outlined the following. Section 2 details the characteristics of data warehouse. Section 3 explains the subject domain name of data warehouse. Proposed data warehouse specially will be packed with this inflatable water quality guidelines of peripheral river of Dhaka city, Bangladesh. In this section we've shown the river system of Dhaka city. Section 4 represents the graph style of river systems that allows forming a private river subsystem using various data sources. Section 5 describes us the prerequisite data, knowledge and information, which are necessary for design issue of data warehouse. In section 6 we consider the look methodology of suggested data warehouse. We discuss here the steps of construction of data warehouse, simple fact and proportions of warehouse and draw a conceptual snowflake schema of data warehouse. Section 7 represents the graph obtained by executing the SQL directions on DW. Section 8 summarizes this article.
As a data warehouse we understand a assortment of data that is "subject-oriented, involved, nonvolatile, time-variant" and it helps the management's decisions , . Data warehouses (DW) are not database systems in traditional understanding of this term. They differ from databases among other activities with e. g. data models, getting into information methods, control models, query search engine optimization algorithms, visualization techniques , . Transformation from data to knowledge has been offered in Number 1.
Fig. 1 The example of the managerial information system based on data warehouse
The main streams around the greater Dhaka comprised the river routes for this paper (Body 1). Dhaka City is located between 2335ґ to 2354ґ North Latitude and 9020ґ to 9033ґ East Longitude . The routes included (Body 2):
i. Tongi Canal-Balu river
ii. Turag river-Buriganga river-Dhaleshwari river
iii. Shitalakhya River
The review encompassed the complete reach of the Tongi Canal, partial reach of the Turag (from the confluence of Turag and Tongi Canal to the confluence of Turag and Buriganga Rivers), incomplete reach of the Balu River (from the confluence of Tongi Canal and Balu River to the confluence of Balu River and Shitalakhya River at Demra), incomplete reach of the Dhaleshwari River (from the confluence of Buriganga and Dhaleshwari waterways to the confluence of Dhaleshwari and Shitalakhya streams at Kalagachia) and finally, partial reach of the Shitalakhya river (from Ghorashal railway bridge to the confluence of Shitalakhya and Dhaleshwari at Kalagachia). This river system is our subject domain of construction of data warehouse.
Fig. 2 Peripheral River System of Dhaka city, Bangladesh
To describe the structure of the river system, graph model has been developed (Fig. 3). Here the vertices of a directed graph is the point of confluence of the waterways falling into the river system (the vertices X4, X5, X7, X8, X10, X11), as well as the traditional boundaries of the river, determining the restrictions of the study to metropolitan (vertices X1, X2, X3, X6, X9, X12). Each edge of the graph corresponds to a fragment of the river system.
In terms of graph theory, indexing of vertices can be arbitrary. However, in this directed graph numbering of vertices is manufactured in a special way, taking into consideration the direction of water stream in the river system. The basic rule of numbering of the vertices: Range of the vertices boosts along the path to the stream of the river, that is the vertices with increased amounts located downstream. Thus, for just about any edge of the graph the index of last vertex will be higher than the index of the initial vertex.
The producing graph is incomplete and has some special properties that identify it from an arbitrary graph. Vertices of the graph, conditional restrictions of the whole river system, have a qualification. Graph has no vertices with amount of, this means that the branches of the river at one point concurrently can be three or less.
Joining of the fragment of river system (related to individual ends of the graph) in to the river subsystem (related to individual sub graphs) is also strictly defined by guidelines. In one subsystem s = (f1, f2) may be united only successive fragments f1 = (Xi, Xj) and f2 = (Xj, Xk), having a common vertex, which is the finish of one fragment and the beginning of other, with i
As an initial step for initiating the work, following jobs were first identified:
A formal explanation of the topic domain;
Analyzing all the foundation documents (main raw data), to design water quality data warehouse it is required to determine what kind of entities and what characteristics are essential to be taken in. The next entities have been considered during building of data warehouse:
Source: Drinking water quality data stored in the info warehouse have been gathered from different options, such as medical accounts of World Bank, the Division of Water and Sewerage Dhaka (DWASA), Office of Environment, Bangladesh (DOE), Institute of Water Modeling (IWM), the Asian Institute of Technology (AIT), Bangkok, Thailand etc. The information about data sources have been stored within an entity known as "Source". The entity "Source" consists of the areas such as "source_id", "source_name", "source_addr", and "author_information". The field "source_id" can be used as most important key. The areas "source_name", "source_addr", and "author_information" have been used for storing source name, source address and information of creator respectively.
Type: Drinking water quality data stored in the data warehouse can be acquired by collecting either results of individual measurements or averaged or simulated beliefs. Thus an entity known as "Type" with "tide_type" field was included in warehouse structure to indicate this simple fact, where "tide_id" is primary key.
Water Part: During the research of source documents, it was found that the researchers considered different normal water layers during drinking water quality measurements. Some analysts have measured beliefs of this particular quality parameters near the top of the surface drinking water while some have taken values at the bottom of the surface water. To demonstrate this characteristics an entity known as "Layer" has been considered, which includes the fields "layer_id" and "layer_name". "Layer_id" can be used for most important key and layer_name for indicating different layer name like surface layer and bottom coating.
Season: Drinking water quality variables of different months have been grouped as the next seasons:
i) Dry up season (December-January-February)
ii) Pre monsoon season (March-April-May)
iii) Monsoon season of rainy season (June-July-August)
iv) Post monsoon season (September-October-November)
To expose the above mentioned classification we consider an entity named "Season". The entity has 3 fields such as "season_id", "season_name" and "month_included". The field "season_id" is considered as most important key. The field "season_name" stores the name of season where "month_included" shows duration of each season.
Tide: The peripheral waterways of Dhaka city experience tidal oscillations induced by Bay of Bengal. Some experts notify the tide types as high tide and low tide while some do not talk about this reality. This truth has been considered by an entity called "Tide". The entity has two areas that are "tide_id" and "tide_type". The field "tide_id" indicates the principal key and "tide_type" classifies the tide.
Location: The researchers performed their experiments in a variety of locations of the peripheral river system. Water quality data warehouse should store the data gathered from various locations. To store information about the location of sampling things, we had to include an entity named "Location" which contains location_id, river_id, medical_location_name, open public_location_name, total_distance, specific_distance, river_fragment. Location_id uses as key key. River_id determines to which river the sampling point belongs to. Scientific_location_name and general public_location_name store the name of locations found in scientific reports and by local population respectively. For instance, a sampling point in Buriganga river is known to the experts as BURI_1, whereas it is known to the general public as "Gabtoli Bridge". Individual_distance indicates distance right from the start of the river fragment to the present sampling point. Total_distance signifies distance from the very start of the whole river to the current sampling point.
River: Six river reaches encompass the Dhaka city which includes Tongi Khal, Turag river, Buriganga river, Dhaleswari river, Lakhya river and Balu river. Different experts performed their experiments in various locations at different rivers. We have to track down the sampling point which belongs to particular River, so we've thought about an entity including the river name. The field river_id uses as primary key and river_name shows the name of the river.
Parameter: The awareness normal water quality parameter shows water pollution level of a river. The water is polluted if the concentrations are beyond to the typical level of normal water quality. The main parameters of water quality in the river systems are the attention of dissolved air (DO), chemical (COD) and biochemical (BOD) oxygen demand, total dissolve solids (TDS), as well as concentrations of chemical substance pollutants acid solution ions(NO3-, Cl-, SO4--, PO4--), metallic ions (Cr++, Pb++, Hg+, Zn++), ammonium(NH4+) etc. An entity named "Parameter" was used to store variables. This entity consists of 6 traits such as "parameter_id", "parameter_name", "drinking_ws", "household_ws", "fishing_ws" and "irrigation_ws" which correspond to main key, parameter's name and normal water quality requirements of Bangladesh for various uses such as normal water, household work, fishing and irrigation.
River fragment: It is discovered that different organizations have assessed the quality in different manner. Some have assessed this quality of a particular river while some have taken as subject matter area the particular fragment of the river. For this reason, the whole peripheral river system has been split into several fragments. An entity known as "River fragment" has been considered during creating of data warehouse. The entity includes six domains. The field "river_fragment_id" uses as principal key. The field "river_fragment_name" stores the name of the river fragment. "Shortriverfragment_name" exposes the abbreviation for the fragment. Riverfragment_span indicates the length of river fragment. The fields "river_id" is employed as international key.
Concentration: This is the vital entity which keeps immediate or indirect relationships with all above mentioned entities possesses extra qualities such as "time and time" of the tests and "awareness" levels of every normal water quality parameter.
Most data warehouses use a star schema to symbolize the multidimensional data model. The database consists of a single fact desk and a single table for every single dimensions. Snowflake schema offers a refinement of star schema where the dimensional hierarchy is explicitly displayed by normalizing the sizing tables. This contributes to advantages in maintaining the dimension dining tables. However, the renormalized structure of the dimensional furniture in star schemas may be more appropriate for browsing the dimensions. Truth collections are examples of more complex constructions where multiple fact furniture share dimensional dining tables. In our proposed DW we use the snowflake data model schema because:
The snowflake schema is a normalized celebrity schema. In a very snowflake schema, the dimensions furniture are normalized.
In some conditions it may improve performance because smaller tables are signed up with.
It is easier to maintain.
It increases flexibility.
Fig. 4 Snowflake data model schema of suggested data warehouse
As a good example, we consider a river subsystem consisting of four fragments of three different streams - Turag (T1 and T2), Buriganga (BR2) and Dhaleswari (DR2). The total length of river subsystem is L = 55 km. Dissolved oxygen (DO) refers to the quantity of air that is within normal water. Its limit is 4 in line with the standard of Office of Environment, Bangladesh. Below of the limit it is damaging as because Dissolved oxygen plays a huge role in the survival of aquatic life.
Fig. 5 Account of DO along Turag-Buriganga-Dhaleshwari River in 2008
The above physique shows the concentration of dissolved oxygen (DO) in the considered river subsystem in 2008. We present here 7 tips to that your industrial and home effluent of the city fall season through canals.
Here it is analyzed that the most advantageous conditions, ie highest concentrations of dissolved oxygen is observed through the monsoon period (June - August) and the cheapest concentrations is noticed during dried out period (December-Feb). Data for the graph is obtained from different literature sources by SQL-query checking out our data warehouse. Altogether more than 11 000 documents were collected and processed over the period of 1998 to 2009 years on 135 locations of river systems.
Measurement of normal water quality data is very expensive, requires skilled manpower, sophisticated instrument and well-organized laboratory facilities. One of the better benefits to use our data warehouse is the fact that users can access a sizable amount of information, which may be used to solve a large quantity of problems. As our normal water quality data are extracted from multiple resources and put in a centralized location, a business can analyze it in a manner that may allow them to come up with different solutions than they might if they looked at the data separately.
Overall, the task has laid the foundations for the Dhaka city planners and designers to produce a qualitative resource diagnosis of surface normal water. Such an evaluation can eventually evolve to a long-term monitoring system of normal water supply resources for Dhaka City. The task also facilitates normal water quality modelers to choose the proper drinking water quality model and help in formulating the technique for normal water abstraction and water resource for Dhaka city.