Rationale Databases and Data Warehouse - Essay Example

Table of Contents Data Warehouse 2 Rationale Databases and Data Warehouse 2 2.Database Schema 3 2.1Entities Involved In Database Schema: 4 3.Rationale for ER Diagram 7 4. Data Warehouse – Data Flow Diagram (DFD) 1 4.1 Data Flow Diagram Description 0 References 1 Data Warehouse 1. Rationale Databases and Data Warehouse A data warehouse, in the field of Information Technology, is employed to record and evaluate the data to generate significant results. The data warehouse is recognized to assist in incorporating data acquired from a number of data means and yields a primary main repository of data. There is a storage space of data warehouses for maintaining the historical in addition to the most recent data. This historical data is designed to develop and enhance diverse fads and records available to the senior management in a way that the management makes use of the data (records and fads) for making definite decisions and scheme/policy generating. The functional systems, for example marketing, Enterprise Resource Planning (ERP) and so on, supply data the warehouse and making use of Extract, Transform and Load (ETL) approach the data is retrieved from the data warehouse (Rizzi, Abello, Lectenborger and Trujilo, 2006). Since the firm under discussion is concerned with a large volume of data to be considered for evaluation, for that reason, the data warehouse is suggested for execution by the company. The data warehouse would bring about the company not just in its business, but in addition delivers many different added benefits to the company. A few of the factors that motivate the execution of the data warehouse in the firm involve: the data warehouse in a position to cope with massive data, it generates reports instantly, accordingly. Saves time, it offers premium quality data and presents increased business intelligence. Nevertheless, the benefits are invariably linked to down sides, the risks of the data warehouse embody: investing time in extracting, clearing and uploading data, developing high upkeep system, and resource optimization. Keeping in mind the drawbacks of the data warehouse, there are particular rules and best procedures which the business has to stick to these while employing the data warehouse. Some of the most effective tactics that the organization ought to comply with incorporate: investing proper time in accumulating needs and style, building prototypes, correct usage of the centralized and in depth data, building data credibility checks and supply of correct training to the clients. Since the database of the firm is substantial, for that reason, the company must not deem building the relational data warehouse. On the other hand, the company is suggested to make use of the index partitioning and tables. It is advisable that the organization must comply with these best procedures to put into practice the data warehouse effectively. 2. Database Schema The database schema explains the framework of the database system in an authorized language in order that the database developers to fully grasp and put into practice the physical database. In an effort to enforce the credibility of the data, specifications/formulas are included in the database schema. On the whole, the database schema provides the data of the company in the relational form, i.e. the database tables and their associations. Furthermore, the database schema is characterized by the real-world companies found. Keeping in mind the above given specifics with regards to the database schema, the business procedures of the company under explanation are needed to be shown in the shape of tables and interactions to develop the database schema. Moreover, the database schema of the organization must also involve the features (fields) of the companies (Donald, n.d). 2.1 Entities Involved In Database Schema: Keeping in view the recognized business needs of the company, it is anticipated that the company must have a minimum of nine (9) entities to be shown in the database schema. These entities consist of, however, are not confined to the Client of the company, Employees of the company, Departments of the company, the Project Stakeholders, External Companies (employed for data assortment), Web Analytics, Tasks of the clients, Outcomes/Evaluation and Result Element. It is relevant to find and indicate the features of these entities, that is why, the aspects (characteristics) of the Client Entity comprise of: the client ID (Main Key / index), client name, client address, client contact phone number, client fax number, client e-mail address, and client information. The features of the Employee entity consist of: the employee ID (Main Key / index), employee first name, employee last name, employee contact number, employee e-mail address, employee qualification, employee designation, employee division, and employee income. The features of the Department entity comprise of: the department ID (Main Key / index), department name and also department information. The features of the external organization, entity are much like the client entity, as a result, these characteristics are not needed to mention once again. The options that come with the Web Analytics entity consist of, however, are not confined to the web ID (Main Key / index), the web page bounce rate, amount of visits, exclusive visits, the duration of the visitor stays at the web page and page rate. In the same way, the outstanding entities, i.e. the outcomes and outcome component’s characteristics are related to the evaluation of the client businesses (Bucchmann, 1997) As determined by these entities, perceptions can be developed as a saved Structured Query Language (SQL) query depending on the observed tables/entities (Date, 2003). 3. Rationale for ER Diagram Consistent with the entities and features of the database that have previously been determined before in the paper, an Entity Relationship Diagram (ERD) for the organization has been designed. Subsequently, the client entity keeps an association with two entities consist of: the employee and project. In reality, the workers of the company retain more than one client(s), whereas, the business starts one or more project(s) contracting for multiple employee and one or more clients. The employees are part of one and only one division at any given time. As demonstrated in the ER diagram, the projects may possibly entail external companies for the assortment of data. In addition to that, the projects include web analytics, results and outcome Components. Depending on these dealings, the above ER diagram has been created (Date, 2003). 4. Data Warehouse – Data Flow Diagram (DFD) 4.1 Data Flow Diagram Description The above diagram demonstrates that the company would get back the data from the functional databases consist of: the human resource (Management Information System), the data Acquisition system, Information Technology Marketing system and Research & Development System. The data would be retrieved, making use of the data warehouse approach referred to as an ETL (Extract, Transform and Load). As soon as the data is retrieved from the functional databases, the unprocessed data are saved in the staging section of the integration layer. After which the data are converted into the operational data store (ODS) in the integration layer. The data is moved from the operational data store in the data warehouse database and right here the data are kept in the hierarchical manner and this modification is generally referred to as dimensions, specifics/total specifics and their blend is known as star schema(Kimball, 1996). The data in the data warehouse are split up into the data marts to keep particular data to be able to obtain superb results and functionality. Subsequently, the fundamental business intelligence devices are implemented to evaluate the data and obtain varied reports. More than anything, it is relevant to specify here that if in any respect, the company under presentation have integrated data (in place of different standalone operational database systems), the procedures before the integration layer would not be needed to execute. Nevertheless, it is presumed that the company has standalone operational databases as demonstrated in the above given diagram. References Date, C., J. (2003). Introduction to Databases System – 8th Ed. Pearson Corperation, Wishiington DC, USA Donald S. Le., Vie, Jr. (n.d). Understanding Data Flow Diagrams. Retrieved from: http://im2.wessa.net/dfd.pdf Kimball, R., (1996). The Data Warehouse Toolkit: Practical Techniques for Building Dimensional Data Warehouses, John Wiley & Sons, Inc. M-C. Wu, A. P. Buchmann (1997). “Research issues in data warehousing,” Proc. 7th BTW, 61–82. Rizzi, S., Abello, A., Lectenborger, J., and Trujilo, J. (2006). Research in Data Warehouse Modeling and Design: Dead or Alive? ACM, Arlington, Virginia, USA Read More