SQL/DS ve CSP/AD ile kütüphane uygulaması geliştirme

Abstract

ÖZET Bu çalışmada SQL/DS (Structured Quary Language / Data System) ilişkisel veri tabanı ve CSP/AD (Cross System Product / Application Development) dördüncü nesil uygulama geliştirme yazılımı incelenmiş ve bunların olanak ve per fermanlarını gerçek bir uygulama üzerinde görmek için i.T.Ü ve benzer üniversitelerin kütüphanesinde kullanılabilecek bir kütüphane otomasyon yazılımı gerçekleştirilmiştir. Bir veri tabanı bir organizasyon içinde üretilen, depolanan tüm bilgilerin oluşturduğu kümedir. Veri tabanını diğer ortamlardan ayıran en önemli özellik verinin paylaş ılabilir ve sürekli güncel olmasıdır. Merkezi bir veri tabanını bilgisayar ortamında oluşturmak için çeşitli veri tabanı sistemleri kullanılabilir. Bu veri tabanları hiyerarşik modelde, ağ modelinde, ilişkisel modelde yada son yıllarda tanınmaya başlayan nesne yönetimli modelde olabilir. Hiyerarşik ve ağ modeli belirli ve sınırlı sayıda anahtar ile erişimde hızlı olmasına karşın tecrübeli bilgi işlem personeli gereklidir. Ayrıca günlük ve dinamik değişen ihtiyaçlar kolayca karşılanamaz, ilişkisel modelde veri tablo şeklinde yapılarda tutulur ve kullanıcı bu formda görür. Aynı komut kümesi ile (SQL dili) veri tanımlama, veri girişi düzeltme, sorgulama, silme işlemleri hem çevrimiçi olarak (I SQL ortamından) hem de yazdığımız yüksek seviyeli programlar (COBOL, CSP/AD vs.) içinden yapılabilir. CSP/AD dördüncü nesil uygulama geliştirme dili ise IBM' in değişik tipteki mimarilerinde (S/370, S/390, AS/400, PS/2) ve değişik işletim sistemleri (VM,VSE,MVS,OS/400, OS/2, MS/DOS vs.) altında çalışabilen uyumlu ve taşınabilir kod üretebilen bir ortamdır. CSP/AD kolayca öğrenilebilen az sayıda fakat etkin işlem ve komutlara sahiptir. Çok az bilgisayar bilgisiyle kısa sürede etkin programlar yazılabilir. CSP/AD yukarıda adı geçen sistemlerde kullanılan değişik tipte ve modelde veri tabanlarına ( SQL/DS, DB2, SQL/400 ilişkisel, DL/I,IMS/DB hiyerarşik, CMS ve MS DOS sıralı, doğrudan erişimli dosyalar vs.) aynı komutlarla erişir ve hata kontrolü yapar. Geliştirilen kütüphane uygulaması tüm kütüphane fonksiyonlarım içermemesine rağmen tasarımı tüm fonksiyonları olacak şekilde açık yapılmıştır. Gerçekleştirilen ve test edilen fonksiyonlar:. Ana kataloga belge girişi ve düzeltmesi. Kütüphane stoklarına katalogdan kopya girişi ve düzeltmesi. Okutucu tanımı. Dolaşım işlemleri (Belge ödünç verme, Belgeyi geri alma ). Belge arama ve görüntüleme fonksiyonları. Erişim numarasına göre, Konu kodu ve konu adına göre, Yazarlara göre, Sınıflandırma türü ve sınıflandırma numarasına göre, ISBN numarasına göre. Görüntülenen belgenin kopyalarım ve ilgili kopyanın dolaşım durumu izleme Sonuç olarak SQL/DS ve CSP/AD ile taşınabilir ve uyumlu uygulama geliştirme hızlı ve etkin olmakta. Test ve kodlama aşaması beraber olduğu için az hata çıkmaktadır. Gerçekleştirilen ygulamanın bakımı da kolay ve hızlı olmaktadır. Veri tabanı tasarımı ilişkisel model her nekadar fazla bilgi istemese de ilişkisel modeli ve normal isasyon işlemlerini kavramış tecrübeli personel tarafından uygulama kodlama aşamasına gelmeden yapılmış olmalıdır. indeks kullanımı mecburi olmamasına rağmen çok kullanılan sütunlar için gereklidir. iv

SUMMARY DESIGN AND IMPLEMENTATION OF A LIBRARY APPLICATION WITH SQL/DS AND CSP/AD In this study, SQL/DS ( a Relational Data Base System in ANSI standard) and CSP/AD (a Fourth Level Language) facilities and performance analysis has been done and to improve the performance, some facilities of SQL/DS and CSP/AD was used. For this purpose a sample application in which the facilities of SQL/DS and CSP/AD can be used properly, designed and implemented. As a sample application a Library Automation System was selected. A person needs information and doesn't know where the data is, comes library and tries to access data as quick as possible. A catalog contains information about all objects (books, reports, periodicals, articles etc' ) and SQL/DS is the most suitable data base system for dynamic search requests. A catalog in a relational data base system has many fields on multiple tables. For example object type (book, report etc.), access number, titles, authors, subjects, publishing place, date etc. Some fields in catalog has more than one entry (authors, subjects etc.), is not suitable for relational database. After necessary normalization steps master catalog that have to be seen as a single view to users, is broken into four separate tables, (master catalog table, authors table, titles table and subjects table). Join queries on multiple tables and view definition on multiple tables can provide to user single catalog view. A university library catalog has more than 100.000 entry. Performance analysis of SQL/DS can be done with this data accurately and properly. Data base systems: A Data Base is the set of information produced, used an stored by all of organisation of a company. All of the data in system can be accessible and shareble. In Data processing Systems, A data base (central or distributed) is the cumulative of data produced, used and especially stored data among the organisation. According to needs of organization, one or more of data base models can be used. The most known data base models are:. Hierarchic model. Network model. Relational model. Object oriented model. In hierarchic model data is stored tree like segments. Parent-Son type relationships suits this model. To access a segment, you must start root segment and walk down through the tree. Although access speed is very high on defined paths, this model has limited cap-abilities on nonplanned queries. Experienced Data Base Personal can only define access paths. An enduser does nothing except using programs having planned queries.Network model is much more complicated than the hierarchic model. More complex paths can be defined in both direction between segments. This models also require skilled personal. Although they can provide more paths to data, update and insert performances are very poor. Relational model is the closest model to enduser. Endusers can define, manipulate the data and access paths. To reach data, access path defining is not a must. In this model data is stored and seen in two dimensional simple table format. Relational Data Base System provides user an interactive facility. This facility include data definition and data manipulation command language. This English like language can be used directly from terminal or from high levels programming languages. A Nonprofessional user can also use this interface to define and manipulate him or her own data. SQL (structured query language) is a data definition and manipulation language for relational data base system in ANSI standard. Object Oriented Model is the newest model and still a lot of software developer works on. In this model data classified and separated into objects according to type of it ( data file, message file, table, source file, data area etc.). All commands and procedures are predefined. Only this commands and process effects the objects. This model includes the relational model for object in data file type, ncft SQL/PS: SQL./DS is a RDBMS (Relational Data Base Management System) developed by IBM. SQL (structured query language) is the data definition and data manipulation language of this relational system. SQL is also an MSI standard language for relational data base systems. Data in relational system is in simple table format. Columns of table are fields of relation and defined with their names and attributes ( character, integer, real etc.) Each row on table is an entry for this relation. A field on a row is the smallest unit that accessible. Some fields on a row contains null values. Null means that the data in this cell unknown ( not space or zero). ` SQL coEsaand can effect multiple rows. Queries, update or delete requests specify some conditions to select or limits rows. SQL commands can be used both interactively on terminal and in a high level program. With same SQL commands online queries can be handled on both user defined tables and system tables that contain table definitions and some statistics about data in tables. Whenever needed, two or more relation can be joined. For example:Student relation : STUDENT STUtf NAME ! SURNAME ! ADDRESS -+ +.ULUS !DERE SOK 13. i A?IR j MANOLYA ÇIK. i PHONE* -+ ! 1323434 ! 1333232 I 5611561 486621 ! AHMET 487634 İSALIM 487654 İADEM 545123 ! ALİ Grades relation: ! COŞKUN 'BALLI İŞARI SOK 11. I GÜVEN SOK 21. 1234455 GRADES STU# 486621 486621 487634 487634 487634 487634 487654 487654 LESSON FİZİK KİMYA FİZİK MATEMATİK KİMYA FİZİK FİZİK MATEMATİK GRADE 76 - 90 56 87 45 56 55 78 Query: Students and lessons from which students takes a grade over 75 SELECT STU# TABLE1, NAME, SURNAME, LESSON, GRADE FROM STUDENT TABLE1, GRADES TABLE2 WHERE TABLEl.STUtf = TABLE2.STW* Query command columns wanted to be displayed from which tables ? columns that join the tablesSome SQL commands are as fallows:. Data definition commands: Create table Create view Create index Create synonym Drop table Drop view Drop index Comment on Label on defines a new table view index synonym deletes the table from system catalogs view index puts same comments on a table puts a new column heading on a column Data Processing commands:. Select. Insert. Update. Delete. Authority commands:. Grant. Revoke. Control commands:. Connect. Acquire dbspace. Alter. Drop dbspace. Lock. Unlock. Commit work. Rollback work. Update statistics. Drop program retrieves one or more rows of data from one or more table inserts one or more row to a table updates one or more or rows of data deletes one or more rows from a table Gives some or all authority on a table to a user or public Revokes some or all authorities from a user Links the user a SQL/DS database Creates space for tables Changes some properties of tables, dbspaces Drops the dbspace and all tables defined in it- Puts locks on tables or on dbspaces Unlocks locks on a table or a dbspace Reflects changes to database with in last logical unit of work All changes with in last logical unit of work are ignored Statistics about data in tables are updated to improve right access plan creation Deletes an access module created before for a program. Performance analysis commands:. Explain Gives extra information for a given SQL command. This information includes access plans, response time,, number of columns affected, cost etc. Cf53CSP/AD : CSP/AD is a fourth generation application development tool. CSP/AD applications can run all IBM based Computers in different architectures (S/370, ESA/9000, AS/400, PS/2, etc.) and different operating systems ( MVS, VM, VSE, OS/400, OS/2 etc.) CSP/AE is the execution environment for CSP/AD applications. Data base systems that CSP/AD can access currently :. DB2 and SQL/DS, SQL/400 relational data base systems,. DL/I and IMS hierarchical data base systems,. VSAM, ISAM access methods.. MS/DOS, CMS sequential and direct file systems. A CSP/AD application is a set of CSP objects. These objects are: application, processes, records, maps, statement groups etc. An application is a sequence of processes and called processes. Application definition contains application specific attributes. ( for example working storage name, map group name, help map group name, user message file name etc. ) APPLICATION STRUCTURE NAME LVL OPTION OBJECT ERROR DESCRIPTION NOREC MAIN CONTROL LOOP CONTROL GET RECORD NUMBER READ RECORD FROM DATABASE DISPLAY RECORD A process has four section: before section (csp statements) process option ( none for execute ) ( I/O for others) after section flow section Each process has a I/O function. Main function of process is given at the process option. This option can be : execute statements only object no converse, terminal - printer I/O. display object = map. inquiry,. add,. update,. delete, Disk I/O. replace, object = record. setinq,. scan,. close If an error occurs during process option execution a statement group can be invoked. Before and after section are a set of CSP statements before and after I/O operation. Processes has EXECUTE type process option are only for application logic coding and has no I/O. EXECUTE processes has only ono section. In flow section, the next* process is given. Normally in a good CSP/AD application this section should not be used. Structural programming techniques ( while, if..then..else, perform) can be used instead. Each process has an object effected by process option. These object can be map or records. A Record is a data structures that used during I/O operations. Each record is a collection of fields and/or occurrences of fields. Some type of records are as fallows :. serial,. direct,. indexed,. working storage,. redefined,. SOX row,. DLI segment. For last two types of records, record definition can be extracted from its database definition. A map is a screen or printer form. All fields of map and some edit and control routines about each field can be defined interactively. Converse process option send a map to terminal and receive it after user fill it- Display process option only send map to terminal or printer. Statement, groups are a collection of CSP statements. If a group of statements repeats in two or more process, they can be grouped to a statement group. C133 Designed and implemented application (LIBRARY AUTOMATION SYSTEM) : General design factors:. More than one library can be handled (one of them is central ),. One catalog for all libraries,. Different types of documents (books, periodicals, reports, pictures etc) can be cataloged,. Each library can has branch offices,. Each library has its owns stock,. Main catalog has only one catalog record for all copies of one document.For each copy of a document one stock record in library that owns that copy, Documents that not received yet, can be cataloged. Each document can has more than one title. Each document can has more than one author, Each document can has more than one subject code, Both UDC and LC classification support, Each document can has more than one classification number, Only one reader card for all libraries that handled by system, Each reader can search catalog with some keys (access number, subject, author, classification number, ISBN etc) and can take some data about the documents, (all copies, availabilities of each copy, reader name took it and turnback date etc.) Security levels. (Readers at the lowest) Reader classes for circulation. Statistics about documents and readers. Implemented and tested library functions:. Catalog data entry,. Catalog update,. Stock entry,. Stock update,. Reader definition and update,. Circulation functions,. Search functions with:. Access number and language,. Subject code and subject name,. Classification number and type,. Author name,. ISBN number,. All copies of a document and status of each copy. To run application,:. Signon with an authorised user. Enter `cspedlbl` for CSP/AE file labels.. Enter `xspe ktOOa, alf=c` to start application. Conclusion : SQL/DS is an easy use database system. It provides some facilities to develop database applications from design step to final application. Even during coding phase database design can be changed easily. For test. Test data can be produced. With CSP/AD, coding, testing and recoding are quite easy and requires little effort comparing to other- languages. To design a good database (tables, views etc.) some design guidelines are needed eventhough not a must. Relational theory, normalisation methods and experience and utilities provided by SQL/DS can help for this.Designed application does not cover all functions of a library. Design allows to implement other functions easily. Although application does not cover all functions most of the main functions was implemented. ( catalog entry, reader definition, stock entry, all search functions on catalog and stock etc. ). Some of reports about documents was not in application boundaries. This reports can be produced with ISQL. Other reports can also be created with ISQL immediately. With some addition this application can be used in an university environment. This additions needs only one month/person. Other functions of library ( like financial appl., order, purchasing etc.) can be integrated with this application. In university environment a central database for library and other integrated applications data ( students, grades etc.) will explode the computer usage &nd its benefits. SQL/DS and CSP/AD can be selected for all applications in university. This leads us to easily' understood and maintained applications, fewer and less educated data processing personal.