Submitted by Marlene Santos (marlene.bc.ufg@gmail.com) on 2016-08-05T19:33:36Z No. of bitstreams: 2 Dissertação - Flávio de Assis Vilela - 2015.pdf: 4909033 bytes, checksum: 3266fed0915712ec88adad7eec5bfc55 (MD5) license_rdf: 0 bytes, checksum: d41d8cd98f00b204e9800998ecf8427e (MD5) / Approved for entry into archive by Luciana Ferreira (lucgeral@gmail.com) on 2016-08-08T14:30:29Z (GMT) No. of bitstreams: 2 Dissertação - Flávio de Assis Vilela - 2015.pdf: 4909033 bytes, checksum: 3266fed0915712ec88adad7eec5bfc55 (MD5) license_rdf: 0 bytes, checksum: d41d8cd98f00b204e9800998ecf8427e (MD5) / Made available in DSpace on 2016-08-08T14:30:29Z (GMT). No. of bitstreams: 2 Dissertação - Flávio de Assis Vilela - 2015.pdf: 4909033 bytes, checksum: 3266fed0915712ec88adad7eec5bfc55 (MD5) license_rdf: 0 bytes, checksum: d41d8cd98f00b204e9800998ecf8427e (MD5) Previous issue date: 2015-10-08 / The increase in quantity and variety of data available on the Web contributed to the emergence of NOSQL approach, aiming at new demands, such as availability, schema flexibility and scalability. At the same time, relational databases are widely used for storing and manipulating structured data, providing stability and integrity of data, which is accessed through a standard language such as SQL. This work presents a method for integrating data stored in heterogeneous sources, in which an input query in standard SQL produces a unified answer, based in the partial answers of relational and NOSQL databases. / O aumento da quantidade e variedade de dados disponíveis na Web contribuiu com o surgimento da abordagem NOSQL, visando atender novas demandas, como disponibilidade, flexibilidade de esquema e escalabilidade. Paralelamente, bancos de dados relacionais são largamente utilizados para armazenamento e manipulação de dados estruturados, oferecendo estabilidade e integridade de dados, que são acessados através de uma linguagem padrão, como SQL. Este trabalho apresenta um método de integração de dados armazenados em fontes heterogêneas, no qual uma consulta de entrada em SQL produz uma resposta unificada, baseada nas respostas parciais de bancos de dados relacionais e NOSQL. Palavras–chave

O projeto e manutenção de bancos de dados é um importante desafio, tendo em vista as frequentes mudanças de requisitos solicitados pelos usuários. Para acompanhar essas mudanças o esquema do banco de dados deve passar por alterações estruturais que muitas vezes prejudicam o desempenho e o projeto das consultas, tais como: relacionamentos desnecessários, chaves primárias ou estrangeiras criadas fortemente acopladas ao domínio, atributos obsoletos e tipos de atributos inadequados. A literatura sobre Métodos Ágeis para desenvolvimento de software propõe o uso de refatorações para evolução do esquema do banco de dados quando há mudanças de requisitos. Uma refatoração é uma alteração simples que melhora o design, mas não altera a semântica do modelo de dados, nem adiciona novas funcionalidades. Esta Tese apresenta um novo processo para aplicar refatorações ao esquema do banco de dados. Este processo é definido por um conjunto de tarefas com o objetivo de executar as refatorações de uma forma controlada e segura, permitindo saber o impacto no desempenho do banco de dados para cada refatoração executada. A notação BPMN foi utilizada para representar e executar as tarefas do processo. Como estudo de caso foi utilizado um banco de dados relacional, o qual é usado por um sistema de informação para agricultura de precisão. Esse sistema, baseado na Web, necessita fazer grandes consultas para plotagem de gráficos com informações georreferenciadas. / The development and maintenance of a database is an important challenge, due to frequent changes and requirements from users. To follow these changes, the database schema suffers structural modifications that, many times, negatively affect its performance and the result of the queries, such as: unnecessary relationships, primary and foreign keys, created strongly attached to the domain, with obsolete attributes or inadequate types of attributes. The literature about Agile Methods for software development suggests the use of refactoring for the evolution of database schemas when there are requirement changes. A refactoring is a simple change that improves the design, but it does not alter the semantics of the data model neither adds new functionalities. This thesis aims at proposing a new process to apply many refactoring to the database schema. This process is defined by a set of refactoring tasks, which is executed in a controlled, secure and automatized form, aiming at improving the design of the schema and allowing the DBA to know exactly the impact on the performance of the database for each refactoring performed. A notation BPMN has been used to represent and execute the tasks of the workflow. As a case study, a relational database, which is used by an information system for precision agriculture was used. This system is web based, and needs to perform large consultations to transfer graphics with geo-referential information.

The Company X has a database that continuously increasing which causes the response time when retrieving data from the database increases the more data that is retrieved. Therefore, the company want an analysis of the database structures and stored procedures to see if there is a more efficient way to store and retrieve large datasets. Performance can have different meanings. Within the subject of computer systems, it could be factors such as transaction throughput, response time and storage space. But within the scope of this work performance is limited to the response time. One of the tables in the database is normalized and a few techniques are implemented for stored procedure that the company has not implemented to see if there have been any improvements in performance when retrieving data. The respond time for the various techniques implemented was measured in order to make a comparison on performance. The purpose of this thesis is to analyse database tables and how stored procedure can be improved to find a sustainable solution for the database in the future. Question to the work are: How can the database structure be improved with the aim of increasing performance of data retrieval? Which techniques can improve stored procedure performance when it comes to retrieving large amount of data? The result of this work was that normalization has reduced the respond time for large data retrieval. One of the stored procedure techniques which is called sp_executesql was one of the best techniques which improved the performance of execution time the most when it came to retrieve large amount of data. / Företaget X har en databas som kontinuerligt ökar i storlek vilket detta leder till att svarstiden vid hämtning av data från databasen öka ju mer data som hämtas. Därför vill företaget X att databasstrukturen och stored procedure skulle analyseras för att se om det fanns ett effektivare sätt att lagra samt hämta stora mängder data på. Prestanda kan ha olika betydelser; inom databassystem handlar det om olika faktorer som transaktionsgenomströmning, svarstid och lagringsutrymme. I detta arbete begränsas prestanda till svarstiden. En av tabellerna i databasen normaliseras och sedan implementeras ett antal tekniker för stored procedure som företaget inte har implementerat. Detta för att kunna se om det har skett förbättringar i prestanda vid hämtning av data. Därefter mätes svarstiden för de olika teknikerna som implementerades för att kunna göra en jämförelse på prestanda. Syftet med arbetet är att analysera databastabeller samt analysera hur stored procedure kan förbättras för att hitta en hållbar lösning för databasen i framtiden. Frågeställningar till arbetet är: Hur kan databasstrukturen förbättras i syfte att öka prestanda vid hämtning av data? Samt vilka tekniker kan förbättra prestanda för stored procedure vid hämtning av stora datamängder? Resultatet av arbetet blev att med hjälp av normalisering har svarstiden minskat för hämtning av stora datamängder samt att sp_executesql är den teknik inom stored procedure som är överlägset bäst av de tekniker som har implementerats när det gäller prestanda vid hämtning av stora datamängder.

<p>När en kassaflödesvärdering genomförs prognostiseras företagets värde genom att summera framtida kassaflöden och diskontera dem med företagets kapitalkostnad. På grund av att prognostiserade kassaflöden är uppskattningar inför framtiden karaktäriseras en värdering alltid av hög osäkerhet. Till underlag för prognoserna används en omfattande mängd information. För att ett tillförlitligt värde ska kunna beräknas är det viktigt att relevant och pålitlig information samlas in och hanteras på ett effektivt sätt. I dagsläget är det problematiskt att hantera omfattande mängder information och ett tillräckligt datorstöd saknas. Syftet med den här uppsatsen är att konstruera ett förslag till en databas till stöd för informationshantering vid kassaflödesvärdering.</p><p>Uppsatsen är skriven utifrån en kvalitativ metod. En omfattande referensram presenteras till syfte att ge läsaren möjlighat att bekanta sig med både kassaflödesvärderingar och databaser. Vidare används ett fallföretag för att exemplifiera hur en kassaflödesvärdering går till, vad den kännetecknar och vilka informationshanteringsbehov som föreligger.</p><p>Uppsatsens resultat består av en konceptuell samt en logisk och fysisk databasmodell utvecklad i Microsoft Access. Den logiska och fysiska databasmodellen består av 20 tabeller där relationerna sammankopplar olika faktorer som påverkar företagets kassaflöde. Möjligheter som databasen ger upphov till är bland annat en förenklad härledning av företagets prognostiserade värde. Vidare kan en större mängd information lagras och hanteras i samband med värderingen. En databas kan också skapa en högre grad av flexibilitet och möjlighat att anpassa värderingen till företagets specifika dynamik. Det finns också möjlighet att lättare identifiera företagets risker och hur riskerna påverkar kassaflödena. Tänkbara problem med databasen är risk för lagring av irrelevant information och att det kan vara problematiskt att påvisa en tillräcklig validitet för databasen.</p><p>I uppsatsen argumenteras också för att det finns möjlighet att även använda den föreslagna databasen vid kassaflödesvärdering av andra företag. Det är dock viktigt att påpeka att flera attribut i den föreslagna databasen är specifika för fallföretaget och kan bli irrelevanta för andra företag.</p> / <p>When a discounted cash flow valuation is performed, the value of the asset is determined by summarising the predicted cash flows and discounting them with the cost of capital. Due to the fact that the future cash flows are predictions, a valuation is always affected by uncertainty. In order to improve the accuracy of the predictions, an extensive amount of information is used. To be successful, it is important to collect reliable and relevant information and manage the information in an efficient way. At present it is difficult to gather and manage information and sufficient computer aid is missing. The purpose of this master thesis is to construct a proposal for a database to support information management for discounted cash flow valuation of corporations. The thesis is conducted with a qualitative research method. An extensive frame of reference is presented in order to give the reader the possibility to get to know both databases and cash flow valuations.</p><p>The result of the study is a conceptual model as well as a logical and physical database model. The proposed database consists of 20 tables where the database relationships connect the value creating factors of the corporation. Possible positive effects of the database are simplified deriving of the corporate value and capacity to store and manage greater amounts of information in a logical manner. The database can also improve the flexibility and adjust the valuation to the dynamic nature of the corporation. Furthermore, the identification of risks and risk factors can be improved. A potential problem with the database is the risk of storing irrelevant information. It can also be difficult to prove a sufficient validity for the database since no user valuation has been carried out.</p><p>In the thesis, the author also argues that the database can be utilised for discounted cash flow valuation of any corporation. However, some tables, attributes and relationships need to be changed and adjusted to the specific nature of the corporation.</p>

Cost models are an essential part of database systems, as they are the basis of query performance optimization. Based on predictions made by cost models, the fastest query execution plan can be chosen and executed or algorithms can be tuned and optimised. In-memory databases shifts the focus from disk to main memory accesses and CPU costs, compared to disk based systems where input and output costs dominate the overall costs and other processing costs are often neglected. However, modelling memory accesses is fundamentally different and common models do not apply anymore. This work presents a detailed parameter evaluation for the plan operators scan with equality selection, scan with range selection, positional lookup and insert in in-memory column stores. Based on this evaluation, a cost model based on cache misses for estimating the runtime of the considered plan operators using different data structures is developed. Considered are uncompressed columns, bit compressed and dictionary encoded columns with sorted and unsorted dictionaries. Furthermore, tree indices on the columns and dictionaries are discussed. Finally, partitioned columns consisting of one partition with a sorted and one with an unsorted dictionary are investigated. New values are inserted in the unsorted dictionary partition and moved periodically by a merge process to the sorted partition. An efficient attribute merge algorithm is described, supporting the update performance required to run enterprise applications on read-optimised databases. Further, a memory traffic based cost model for the merge process is provided. / Kostenmodelle sind ein essentieller Teil von Datenbanksystemen und bilden die Basis für Optimierungen von Ausführungsplänen. Durch Abschätzungen der Kosten können die entsprechend schnellsten Operatoren und Algorithmen zur Abarbeitung einer Anfrage ausgewählt und ausgeführt werden. Hauptspeicherresidente Datenbanken verschieben den Fokus von I/O Operationen hin zu Zugriffen auf den Hauptspeicher und CPU Kosten, verglichen zu Datenbanken deren primäre Kopie der Daten auf Sekundärspeicher liegt und deren Kostenmodelle sich in der Regel auf die kostendominierenden Zugriffe auf das Sekundärmedium beschränken. Kostenmodelle für Zugriffe auf Hauptspeicher unterscheiden sich jedoch fundamental von Kostenmodellen für Systeme basierend auf Festplatten, so dass alte Modelle nicht mehr greifen. Diese Arbeit präsentiert eine detaillierte Parameterdiskussion, sowie ein Kostenmodell basierend auf Cache-Zugriffen zum Abschätzen der Laufzeit von Datenbankoperatoren in spaltenorientierten und hauptspeicherresidenten Datenbanken wie das Selektieren von Werten einer Spalte mittels einer Gleichheitsbedingung oder eines Wertebereichs, das Nachschlagen der Werte einzelner Positionen oder dem Hinzufügen neuer Werte. Dabei werden Kostenfunktionen für die Operatoren erstellt, welche auf unkomprimierten Spalten, mittels Substitutionskompression komprimierten Spalten sowie bit-komprimierten Spalten operieren. Des Weiteren werden Baumstrukturen als Index Strukturen auf Spalten und Wörterbüchern in die Betrachtung gezogen. Abschließend werden partitionierte Spalten eingeführt, welche aus einer lese- und einer schreib-optimierten Partition bestehen. Neu Werte werden in die schreiboptimierte Partition eingefügt und periodisch von einem Attribut-Merge-Prozess mit der leseoptimierten Partition zusammengeführt. Beschrieben wird eine Effiziente Implementierung für den Attribut-Merge-Prozess und ein Hauptspeicher-bandbreitenbasiertes Kostenmodell aufgestellt.

Manufacturing today has become a matter of the effective and efficient application of information technology and knowledge engineering. Manufacturing firms success depends to a great extent on information technology, which emphasizes the integration of the information systems used by a manufacturing enterprise. This integration is also called enterprise application integration (here the term application means information systems or software systems). The methodology for enterprise application integration, in particular enterprise application integration automation, has been studied for at least a decade; however, no satisfactory solution has been found. Enterprise application integration is becoming even more difficult due to the explosive growth of various information systems as a result of ever increasing competition in the software market. This thesis aims to provide a novel solution to enterprise application integration. The semantic data model concept that evolved in database technology is revisited and applied to enterprise application integration. This has led to two novel ideas developed in this thesis. First, an ontology of an enterprise with five levels (following the data abstraction: generalization/specialization) is proposed and represented using unified modeling language. Second, both the ontology for the enterprise functions and the ontology for the enterprise applications are modeled to allow automatic processing of information back and forth between these two domains. The approach with these novel ideas is called the enterprise semantic model approach. The thesis presents a detailed description of the enterprise semantic model approach, including the fundamental rationale behind the enterprise semantic model, the ontology of enterprises with levels, and a systematic way towards the construction of a particular enterprise semantic model for a company. A case study is provided to illustrate how the approach works and to show the high potential of solving the existing problems within enterprise application integration.

Automatic physical database design tools rely on ``what-if'' interfaces to the query optimizer to estimate the execution time of the training query workload under different candidate physical designs. The tools use these what-if interfaces to recommend physical designs that minimize the estimated execution time of the input training workload. Minimizing estimated execution time alone can lead to designs that are not robust to query optimizer errors and workload changes. In particular, if the optimizer makes errors in estimating the execution time of the workload queries, then the recommended physical design may actually degrade the performance of these queries. In this sense, the physical design is risky. Furthermore, if the production queries are slightly different from the training queries, the recommended physical design may not benefit them at all. In this sense, the physical design is not general. We define Risk and Generality as two new measures aimed at evaluating the robustness of a proposed physical database design, and we show how to extend the objective function being optimized by a generic physical design tool to take these measures into account. We have implemented a physical design advisor in PostqreSQL, and we use it to experimentally demonstrate the usefulness of our approach. We show that our two new metrics result in physical designs that are more robust, which means that the user can implement them with a higher degree of confidence. This is particularly important as we move towards truly zero-administration database systems in which there is not the possibility for a DBA to vet the recommendations of the physical design tool before applying them.

När en kassaflödesvärdering genomförs prognostiseras företagets värde genom att summera framtida kassaflöden och diskontera dem med företagets kapitalkostnad. På grund av att prognostiserade kassaflöden är uppskattningar inför framtiden karaktäriseras en värdering alltid av hög osäkerhet. Till underlag för prognoserna används en omfattande mängd information. För att ett tillförlitligt värde ska kunna beräknas är det viktigt att relevant och pålitlig information samlas in och hanteras på ett effektivt sätt. I dagsläget är det problematiskt att hantera omfattande mängder information och ett tillräckligt datorstöd saknas. Syftet med den här uppsatsen är att konstruera ett förslag till en databas till stöd för informationshantering vid kassaflödesvärdering. Uppsatsen är skriven utifrån en kvalitativ metod. En omfattande referensram presenteras till syfte att ge läsaren möjlighat att bekanta sig med både kassaflödesvärderingar och databaser. Vidare används ett fallföretag för att exemplifiera hur en kassaflödesvärdering går till, vad den kännetecknar och vilka informationshanteringsbehov som föreligger. Uppsatsens resultat består av en konceptuell samt en logisk och fysisk databasmodell utvecklad i Microsoft Access. Den logiska och fysiska databasmodellen består av 20 tabeller där relationerna sammankopplar olika faktorer som påverkar företagets kassaflöde. Möjligheter som databasen ger upphov till är bland annat en förenklad härledning av företagets prognostiserade värde. Vidare kan en större mängd information lagras och hanteras i samband med värderingen. En databas kan också skapa en högre grad av flexibilitet och möjlighat att anpassa värderingen till företagets specifika dynamik. Det finns också möjlighet att lättare identifiera företagets risker och hur riskerna påverkar kassaflödena. Tänkbara problem med databasen är risk för lagring av irrelevant information och att det kan vara problematiskt att påvisa en tillräcklig validitet för databasen. I uppsatsen argumenteras också för att det finns möjlighet att även använda den föreslagna databasen vid kassaflödesvärdering av andra företag. Det är dock viktigt att påpeka att flera attribut i den föreslagna databasen är specifika för fallföretaget och kan bli irrelevanta för andra företag. / When a discounted cash flow valuation is performed, the value of the asset is determined by summarising the predicted cash flows and discounting them with the cost of capital. Due to the fact that the future cash flows are predictions, a valuation is always affected by uncertainty. In order to improve the accuracy of the predictions, an extensive amount of information is used. To be successful, it is important to collect reliable and relevant information and manage the information in an efficient way. At present it is difficult to gather and manage information and sufficient computer aid is missing. The purpose of this master thesis is to construct a proposal for a database to support information management for discounted cash flow valuation of corporations. The thesis is conducted with a qualitative research method. An extensive frame of reference is presented in order to give the reader the possibility to get to know both databases and cash flow valuations. The result of the study is a conceptual model as well as a logical and physical database model. The proposed database consists of 20 tables where the database relationships connect the value creating factors of the corporation. Possible positive effects of the database are simplified deriving of the corporate value and capacity to store and manage greater amounts of information in a logical manner. The database can also improve the flexibility and adjust the valuation to the dynamic nature of the corporation. Furthermore, the identification of risks and risk factors can be improved. A potential problem with the database is the risk of storing irrelevant information. It can also be difficult to prove a sufficient validity for the database since no user valuation has been carried out. In the thesis, the author also argues that the database can be utilised for discounted cash flow valuation of any corporation. However, some tables, attributes and relationships need to be changed and adjusted to the specific nature of the corporation.

Automatic physical database design tools rely on ``what-if'' interfaces to the query optimizer to estimate the execution time of the training query workload under different candidate physical designs. The tools use these what-if interfaces to recommend physical designs that minimize the estimated execution time of the input training workload. Minimizing estimated execution time alone can lead to designs that are not robust to query optimizer errors and workload changes. In particular, if the optimizer makes errors in estimating the execution time of the workload queries, then the recommended physical design may actually degrade the performance of these queries. In this sense, the physical design is risky. Furthermore, if the production queries are slightly different from the training queries, the recommended physical design may not benefit them at all. In this sense, the physical design is not general. We define Risk and Generality as two new measures aimed at evaluating the robustness of a proposed physical database design, and we show how to extend the objective function being optimized by a generic physical design tool to take these measures into account. We have implemented a physical design advisor in PostqreSQL, and we use it to experimentally demonstrate the usefulness of our approach. We show that our two new metrics result in physical designs that are more robust, which means that the user can implement them with a higher degree of confidence. This is particularly important as we move towards truly zero-administration database systems in which there is not the possibility for a DBA to vet the recommendations of the physical design tool before applying them.

Manufacturing today has become a matter of the effective and efficient application of information technology and knowledge engineering. Manufacturing firms success depends to a great extent on information technology, which emphasizes the integration of the information systems used by a manufacturing enterprise. This integration is also called enterprise application integration (here the term application means information systems or software systems). The methodology for enterprise application integration, in particular enterprise application integration automation, has been studied for at least a decade; however, no satisfactory solution has been found. Enterprise application integration is becoming even more difficult due to the explosive growth of various information systems as a result of ever increasing competition in the software market. This thesis aims to provide a novel solution to enterprise application integration. The semantic data model concept that evolved in database technology is revisited and applied to enterprise application integration. This has led to two novel ideas developed in this thesis. First, an ontology of an enterprise with five levels (following the data abstraction: generalization/specialization) is proposed and represented using unified modeling language. Second, both the ontology for the enterprise functions and the ontology for the enterprise applications are modeled to allow automatic processing of information back and forth between these two domains. The approach with these novel ideas is called the enterprise semantic model approach. The thesis presents a detailed description of the enterprise semantic model approach, including the fundamental rationale behind the enterprise semantic model, the ontology of enterprises with levels, and a systematic way towards the construction of a particular enterprise semantic model for a company. A case study is provided to illustrate how the approach works and to show the high potential of solving the existing problems within enterprise application integration.