Veri tabanı tasarım yöntemlerinin karşılaştırılması ve bir uygulama

Abstract

Bu çalışmada veri tabanı yaklaşımı ve veri tabanı tasarım yöntemleri incelenmiştir. öncelikle veri ve veri tabanı tanımı ortaya çıkarılmış daha sonra genel veri tabanı tasarım yöntemleri ve adımları incelenmiştir. Veri tabanı tasarımı konusunda matematiksel yöntemlere dayalı bir tasarım yöntemi ele alınmıştır. Bu yöntemin gerektirdikleri ve adımları incelenmiştir. Veri tabanı tasarımında matematiksel yöntemlerin halen kolay olarak kullanılamayacağı yargısına ulaşılmıştır. Tasarım konusunda bir başka yöntem daha incelenmiştir. Gerçek hayatta uygulanagelen yöntemlerle daha yakın olan bu yöntemde veri tabanı, yasam süreci olarak kabul edilmekte ve veri ortak bir kaynak olarak görülmektedir. Yöntem gereksinim formülasyonu, mantıksal tasarım ve gerçekleştirme gibi adımlardan sonra veri tabanının bilgi işlem gereksinimleri altında verimliliğini incelemeyi hedeflemektedir. Mantıksal kayıt erişim sayıları ve tasıma hacmi değerleri yaklaşımı ile yapılan tasarımın performansı incelenmekte, gerekirse bu değerleri en aza indirecek, öncelikli işlemlerin performansını arttıracak ancak temelde göreceli olarak tüm veri tabanı kullanıcılarına kabul edilebilir bir performans sağlayacak bir düzenleme öngörülmektedir. Bu çalışma kapsamında örnek bir sistem seçilmiş ve bu yaklaşımla veri tabanı tasarımı yapılmıştır. Elde edilen tasarım, sistemin bilgi işlem gereksinimlerine göre incelenmiş, ayrıca yeri tabanı yapısı ve işlemler bilgisayar ortamında denenmiştir. Yapı hesaplanan değerlere ve amaca göre yeniden düzenlenmiş ve ulaşılan yeni yapı yine bilgisayar ortamında denenmiştir. Her iki yapının sonuçları karşılaştırılmış ve ekteki grafiklerden de görülebileceği gibi belirgin bir iyileşme görülmüştür.

Database management systems have evolved to the point of general acceptance and wide application; however a major problem still facing the user is the effective utilization of these systems. Important to achieving effective database usability and responsiveness is the design of database. The main objective for the data base usage is the present data as a common, shared and centrally controlled resource. The target is to decrease data maintenance and application development costs. Security» data integrity and reduced data redundancy because of increased concurrency is the goals of data base phylosophy. In order to get these advanteges of data bases, one have to design data base carefully and corretly. The 'design process is the most important, because all of the other processes depend on it. This thesis presents a practical stepwise data base design methodology that derives a data base management system processable data base structure from a set of user information and processing requirements. There are two different data base design methods were examined. One of them requires mathematical approach to the database design process. The other requires no specific knowledge but requires more attention to design process. Because the method is heuristic. There isn't any strictly define data base design process. The sequence and the steps that applied are commonly used by many peoples. The methods giving the sequence but the best thing to do is work on the steps and develope your own design process plan. Because some of the design steps are effected by organizations rules. In general database design process contains following steps. Firstly, requirements formulation and analysis. Then logical design process takes place. At last physical design and data base implementation occurs. vi 1 1 - Firstly, user requirements are to be collected and documented. In two of the methods, this step is the same. The only thing to do that working in a structured manner. Requirements der i va ted from objectives of data base and management personnel. The technique normally used is personal interviews with various levels of management and key employees involved in the processing of goods, services, and data in the organization. Data analysis also to be done in this step. Data required to satisfy user requirements must be defined and identified in this step. Entities, entity-classes and entity relationships must be defined. Then, attributes and attribute types for entities must be chosed and defined. The very useful 1 way to decide the relations between entities is the form all of the relations into the third normal form. Because third normal form generally eliminates anomalies on relations caused by data operations such as insert, delete and update operations. Logical design step consist of geting input from fisrt step and producing a data base management processable design specifications. All the user requirements (data and processing) are implemented logically by relations, record types and processess. Data contents are examined for processing. Rules for application programming are documented. Data volumes are examined and projected for data base performance and space requirements. The consolidation of all these works the data base management processable design that is called as logical schema, specifications must be produced. Logical design must be independent of data models and specific data base management system. Relations or record types, key fields and key dependencies must be fully identified and documented for application development process. Next step is the implementation of logical schema in a selected data base management system. Phsical design includes data placement, data compression, access path selection, index creation and selection, record clustering and sizing processes. Options of the current data base management system must be carefully examined and useful options must be implemented in phsical design. Security, integrity and concurrency concepts are also examined befure phsical implementation. Recovery and restart procedures must be ready for disasters. Backup procedures must be prepared for daily operations. - ix - Phsical design totally depends on software and hardware environment that currently in used. In change of equipments the design must be re-analyzed for performance. The characteristics of phsical devices are very important for the performance of the phsical design and they must be carefully formulated for a given hardware/software environment. The methods that are studied in this thesis are giving its own steps. The steps are similar but the methods are different. The requirements formulation and analysis step is the same for all of the methods. All data and user requirements must be analyzed, formulated and identified in terms of attributes, relations and transactions. The first method requires mathematical approach for data base design process. Given the set of intended transactions, this method models the problem of choosing the most efficient decomposition of the universal set of attributes. Universal set of attributes consist of all the attributes in the given system and the decomposition of this set is the data base design. The task is to select most efficient decomposition, design. Unlike other models which neglect the logical structure developed in data base theory, the model developed in this method incorporates it as part of a mathematical program. This provides a comprehensive and an integrated formulation for the combined logical and physical data base design problem. This method formulates the data base design problem as a 0-1 integer program. Unlike other design procedures, the method tends to exploit the information about the way the data base is used for both logical and phsical design of the data base and the formulation provieds an integrated approach for data base design. The integer program model that was developed in this method is extremely difficult to solve. An efficient and correct algorithym must be developed and implemented to solve this problem. The solution of this problem is not studied in this thesis. - x - The other model requires very few mathematical operations for performance measurement of the developed design. Although the methodology emphasizes the logical design step» the activities of requirements analysis and physical design are also addressed. The method accepts data base life cycle concept. The design steps are similar. First requirement formulation and analysis take place. Then in the logical design step addresses the design and refinement of an information structure through the consolidation of the user information requirements specifications. At last phsyical design and implementation steps are take place. In order to measure or project the performance of the design logical record access approach is used. Logical record access counts are made to estimate the expected number of occurences of each record type retrieved explicitly in the course of executing an application. When weighted by processing frequencies, they show which application will probably require the most I/O accesses to and from the data base. / If logical record access counts can be specified for each application by record type, it is easy to use their results to calculate transport volume, or the total amount of data flow between the application programs and the data base management system. The transport volume due to one record type in one application is just the product of that record type's size and its number of logical record accesses in that application. This may be summed over all record types to calculate the transport volume in bytes for the application. Finally, the various application's transport volumes may be weighted by the application frequencies and summed to obtain the global transport volume. By noting which applications are dominant in terms of frequency of execution, logical record accesses per unit time and transport volume, one can determine where design improvements will be most effective. - xi - Using this method, a sample data base design study included. The system consists of a company that sales the imported products through the authorized dealers. The general statement of the company's requirements is to handle all of the operations on products, such as sales, ordering, billing, stock control, etc. First a data base design had done and impelemented, then examined in computer environment, for data base performance. The pure relational IBM DBS and IBM MVS/ESA environment is used for this study. Then logical record access counts and transport volumes calculated. Improvements had done on the design and new design re- implemented and examined. One can find the comparison charts by CPU time and Service Units at appendixes. The main objective of this study is to show and compare data base design methods and the advanteges of accepting and applying the data base concept.