Database Management Systems (DBMS)

Database Management Systems (DBMS) DeSiamore www.infoposter.co.tz www.desiamore.com/ifm

File Processing………. • Advantages of Sequential file processing • It uses magnetic tape, the least expensive method of secondary storage. • It is the most efficient form of organisation when the entire of file or most of it is processed at once • Transaction file and old master file together act as a back-up, it can be used to create the new master file when existing one damaged or destroyed www.desiamore.com/ifm

File Processing………. • Disadvantages .. • Time factor –the time it takes to access a particular record may be too long for many applications • The entire file most be accessed and a new master file created , even if only one record requires maintenance or updating. www.desiamore.com/ifm

File Processing………. • Direct File Organisation • The data can be organised in such a way that they are scattered throughout the disk • This form of organisation that supports direct access also referred to as random access • The records can be accessed nearly instantaneously and in any order • When a record accessed, a record can be read or updated and when this process competed , then the system is free to respond to another request www.desiamore.com/ifm

File Processing………. • Direct processing requires either magnetic disk or optical disk and cannot use magnetic tape • Direct access systems do not search the entire file rather, they move direct or nearly directly to the required record, to do this the system must have some way to determine where a particular record is stored • Example, in figure 4, data are entered directly into the system through a terminal that is in contact with the CPU of the central computer, the system locates the specific record in the master file and then updates it. www.desiamore.com/ifm

File Processing………. www.desiamore.com/ifm

File Processing………. • Direct file processing…. • There are several strategies which are used to find record.. • Relative addressing • Hashing (randomising) • Indexing • Relative addressing • Simplest method of finding a record • A record’s primary key is associated with a specific physical storage location • On retrieval process, the user enters the Key and the disk operating system associates this key with the appropriate location on the disk www.desiamore.com/ifm

File Processing………. • Relative addressing…. • Relative addressing loses its appeal when the record key cannot be made to match the physical location • Hashing also known as Randomising • Method for determining the physical location of a record. • The record key is processed mathematically and another number is computed that represents the location where the record will be stored • Record keys are transformed into storage addresses and by using an arithmetic procedures called randomising or hashing algorithm www.desiamore.com/ifm

File Processing………. • The task of this process is to take a set of records keys and find a formula to map them into set of disk storage location Identifiers • On retrieval process, user needs to retrieve the record once its key is entered and the hashing routine is used to determine where the record can be found on storage disks. • Major difficulty with the hashing procedure is due to that some addresses will never get generated whilst two or more record keys produce identical disk address or synonyms or Collisions www.desiamore.com/ifm

File Processing………. • Indexing • It uses a primary index which associates a primary key with the physical location in which a record is stored. • Advantages of direct file organisation • Data can be accessed directly and quickly • Primary and secondary indexes can be used to search data in many ways • Files can still be processed sequentially using secondary index • Centrally maintained data can be kept up-date www.desiamore.com/ifm

File Processing………. • Disadvantages of direct file processing • The use of an index lowers the computer system’s efficiency • The hardware must be expensive for these systems because all data must be stored on disks • There will be no backup data if a file destroyed, the files are updated directly and no transaction files are maintained on system www.desiamore.com/ifm

File Processing………. • Summary on direct file organisation • The choice of file organisation and the methods used for direct access depend on the five characteristics • File volatility • File activity • File query needs • File size • Data currency www.desiamore.com/ifm

Data Processing • Data processing comprises the following; • Capturing of data • Storing of data • Updating and retrieving of data and information • Data Management • Data management is the arrangement of all data and information with an organization • It also refers to the methods of physical storage and retrieval of data on a disk or other storage devices www.desiamore.com/ifm

Data Management.. • Data management involves the following, • Data administration • The standards of defining data • The way in which people perceive and use data in their day-to-day activities www.desiamore.com/ifm

Data Independence • Data Independence • Data independence allows a database to be structurally changed , it means data can be; • Added and deleted or data attributes altered with minimum disruption to the existing system • This implies that application programs are not required to have detailed knowledge of the records layout, it means when a record layout is changed like fields added, deleted, changed in size then fewer application programs or none would be changed www.desiamore.com/ifm

Data Independence…. • There are two distinct levels of data independence; • Logical data independence • Physical data independence • Logical data independence • Insulates application programs from logical operations such as combining two records into one or splitting an existing record into two or more records www.desiamore.com/ifm

Data independence…. • Physical data independence • Indicates that the physical storage structures or devices used for storing data could be changed, this happens without needing a change in the record structure or application programs www.desiamore.com/ifm

Database Administration Roles • Database administration –Centrally controlling the database • Implemented by a person or group of persons under the supervision of a knowledgeable person called Administrator, this person known as Database Administrator (DBA) • DBA is responsible for supervising the creation, modification and maintenance of the database The DBA controls the database structure and sets up the definition for physical as well as logical implementation of the database. www.desiamore.com/ifm

Database Administration Roles.. • Implementing Security Features, • DBA maintains the integrity of a database • DBA maintains that the database is not accessible by unauthorised users, • DBA is responsible for granting permission to use the database and stores the profile of each user • The user profile can be used by the DBA to verify that a particular user is allowed to access and perform a given operation on database within the limited time frame www.desiamore.com/ifm

Database Administration Roles… • Measures Against Data Loss • DBA is responsible for defining procedures to recover data from failures –human natural, or hardware malfunctioning with minimum loss • DBMS Users • The users of a database can be classified depending on their degree of expertise or their mode of interactions with the DBMS. www.desiamore.com/ifm

Database Administration Roles… • DBMS Users….. • The user can be classified as.. • Naïve Users • Online Users • Application Programmers • BDAs • Naïve Users • Are those users who need not be aware of the presence of the database system or any other system supporting their usage • Example, the users of an Automatic Teller Machine fall in this category. www.desiamore.com/ifm

Database Administration Roles… • Naïve Users… • The user is instructed through each step of a transaction, user respond by pressing a coded key or numeric value • Operations that can be performed by Naïve user are very limited and affect on precise portion of the database www.desiamore.com/ifm

Database Administration Roles… • Online Users • Are those users who may communicate with the database directly via an online terminal or indirectly via a user interface and application program • These users are aware of the presence of the database system and may have acquired a certain amount of expertise with the limited interaction they are permitted with a database www.desiamore.com/ifm

Database Administration Roles… • Application programmers • Professional programmers, who are responsible for developing application programs or user interface • Database Administrator • DBA is a knowledgeable person who is responsible for the physical design and management of the database www.desiamore.com/ifm

Data Dictionary • A Data Dictionary is a database about databases, it holds the following information about each data element in the databases; • Name • Type • Range of values • Source • Access authorization • Indicates which application programs use the data. • A data dictionary may be a stand-alone information system used for management and documentation purposes, or it may be an integral part of the database management system. • Data dictionary is used to actually control the database operations, data integrity and accuracy www.desiamore.com/ifm

Data Dictionary….. • Importance of Data Dictionary • It provides the name of a data element, its description, and data structure in which may be found • Provides great assistance in producing a report of where a data element is used in all programs that mention it • It is possible to search for a data name, provided keywords that describe that name www.desiamore.com/ifm

Database Processing www.desiamore.com/ifm

Database processing….. • What is a Database? • A collection of data designed to be used by different people, • Organised in such a way that a computer program can quickly select desired piece of information • A database consists of four elements; • Data • Relationships • Constraints • Schema • Data • Binary computer representations of stored logical entities • They are distinct piece of information usually formatted in a special way. • The term data is often used to distinguish binary (machine-readable) information from textual (human-readable) information. www.desiamore.com/ifm

Database Processing…. • Example, some applications make a distinction between data files (files that contain binary data) and text files (files that contain ASCII data) • In database management systems, data files are the files that store the database information whereas other –index files and data dictionaries, stores administrative information known as metadata www.desiamore.com/ifm

Database processing….. • Relationships • Relationships represent a correspondence between the various data elements • Constraints • Are predicates that define correct database states. • Schema • Describes the organisation of data and relationships within the database. • Defines various views of the database for the use of the system components of the database management system and for the application’s security as in figure 2 www.desiamore.com/ifm

Database processing….. • Schema… • It separates the physical aspects of data storage from the logical aspects of the representation • As in figure 2, • The internal schema defines how and where data are organised in physical data storage • The conceptual schema defines the stored data structures in terms of the database model used. • The external schema defines a view/s of the database for particular users. In this case the database management system provides services for accessing the database whilst maintaining the required correctness and consistency of the stored data www.desiamore.com/ifm

Database processing….. www.desiamore.com/ifm

Database processing….. • Why a Database • Why should an organisation have an integrated database to store its operational data? • Deficiencies of pre-database information processing include (but not limit to) the following… • Data inconsistency • Lack data integrity • Data repetition or redundancy • Interdependence between programs and data files • Lack of foolproof data security mechanisms • Lack of coordination across applications using common data • Non-uniform back-up and recovery methods • Encoded data www.desiamore.com/ifm

Database processing….. • The advantage of having the data in a database are; • Redundancy can be reduced –having a centralised database redundancy or multiple copies of the same data can be reduced • Inconsistency can be avoided –this depends on data redundancy, which means when the same data is duplicated and changes are made at one site, which is not propagated to the other site, then it gives rise to inconsistency. So if the redundancy is removed chances of having inconsistent data is also removed • Data can be shared –the existing application can share data in a database • Standards can be enforced –with the central control of the database, the DBA can enforce standards • Security restrictions can be applied –the DBA can define authorisation checks to be carried out wherever access to sensitive data is attempted. www.desiamore.com/ifm

Database processing….. • Integrity can be maintained –integrity means that the data in the database is accurate. Centralised control of the data helps in permitting the administrator to define integrity constraints to the data in the database • Conflicting requirement can be balanced –database designers can be able to create database that is the best for the organisation by knowing the overall requirements. www.desiamore.com/ifm

Database processing….. • Characteristics of Data in a Database • Shared –a data in a database are shared among different users and applications • Persistence –data in a database exist permanently in the sense the data can live beyond the scope of the process that created • Validity/Integrity/ Correctness –data should be correct with respect to the real world entity that they represent • Security –data should be protected from unauthorised access • Consistency –whenever more that one data element in a database represents related real-world values, the values should be consistent with respect to the relationship • Non-Redundancy –no two data items in a database should represent the same real-world entity • Independence –the three levels in the schema (internal, conceptual and external) should be independent of each other so that changes in the schema at one level should not affect the other levels www.desiamore.com/ifm

Types of Database Language • There are three types of database languages • DDL ( Data Definition Language) • DML (Data Manipulation Language) • DCL (Data Control Language) www.desiamore.com/ifm

Types of Database Language… • DDL • Used to define data and their relationships to other types of data • Used to formulate schema-level concepts • Mainly used to create files, databases, data dictionaries, and tables within databases. • Defines the format or schema of the database www.desiamore.com/ifm

Data Definition Language… • It allows specification of following information about each tables • The schema of each table • The integrity constraints • The set of values associated with each attribute • The security and authorization information for each table • The physical storage structure of each table on disk www.desiamore.com/ifm

Types of Database Language…… • DML • DML is a language which deals with the processing or manipulation of various database objects • It provides for the program interface to open and close database, find records in files, navigate through the records, add new records and change or delete existing records • To formulate changes to be effected in a database instance www.desiamore.com/ifm

Types of Database Language…… • DCL • DCL is a language which used to improve security features and thus prevents unauthorised access to data in the database • Security is provided by granting or revoking privileges on a user • Privileges determines whether or not a user can execute a given command or a command can be executed on specific groups of data www.desiamore.com/ifm

Any Questions www.desiamore.com/ifm

Database Management Systems (DBMS) • Outline • Introduction to DBMS • Database Architecture • Database Management System • Why DBMS • Types of DBMS www.desiamore.com/ifm

Database Management Systems (DBMS)