01 CHAPTER ONE PART I - OVERVIEW OF THE DATABASE SYSTEM.pptx

database beginners slide

1. Microlink information technology college DEPARTMENT OF COMPUTER SCIENCE Fundamental Database Systems 1 Compiled by: G/ slassie E.

2. CHAPTER ONE part i :-OVERVIEW Of the DATABASE SYSTEM 2 Compiled by: G/slassie E.

3. 3 CHAPTER ONE: OVERVIEW OF THE DATABASE SYSTEM What the course is about? Information System, Data and Information Data Handling Approaches DBMS (Database Management System) Services of DBMS Objectives of Database System Components of DBMS Advantages of DBMS Benefits of Database Approach Database System Applications Components of DBMS Environment Database Languages Compiled by: G/slassie E.

4. Introduction What the course is about? These days, organizations are considering data as one important resource like finance, human resource and time. The management of these resources needs special attention and development strategy. Due to this reason it is considered that databases are essential to every business . They are used to maintain internal records, to present data to customers and clients on the World-Wide-Web, and to support many other commercial processes. Databases are likewise found at the core of many modern organization’s information systems . Database is a structured collection of related data ; which is Persistent Data (permanent storage of data). 4 Compiled by: G/slassie E.

5. Cont. Database System is about Organization of Data , Efficient Retrieval of Data , Reliable Storage of Data , Maintaining Consistent Data , and Making Useful Information for Decision Making . The ultimate purpose of a database system is to transform and manage 5 Compiled by: G/slassie E.

6. Cont. Information System Information System is set of peoples , procedures and resources that collects and transforms data into information and then distributes this information . Component of an organization that manages (gets, processes, stores, communicates) the information of interest. Each organization has an information system, possibly not made explicit in its structure. Usually, the information system operates in support to other components of the organization. The very notion of information system is partly independent of its computerization; however, we are mainly interested in information systems that are, to a large extent, computerized . 6 Compiled by: G/slassie E.

7. Cont. Differences between Data and Information Data - facts made up of text , numbers , images & sounds . These pieces of information are called data. E.g.:- Solomon 35000 10 Information – is the meaning given to data in the way it is interpreted. E.g .:- Mr. Solomon is a sales person whose basic annual salary is $35,000 and whose commission rate is 10%. Generally , we perform operations on data or data items to supply some information about an entity . 7 Data Information It is a collection of facts and figures. It is a collection of final results. It is in an unorganized (raw) form. It is in an organized form. It is not in directly useful form. It is in directly useful form. It needs processing. It does not need any processing. It is also termed as input. It is also termed as output. It requires observations and recordings. It requires analysis. Compiled by: G/slassie E.

8. Cont. But what is da ta? And where is it now? Data is factual ( accurate ) information about objects and concepts, such as: measurements , statistics You can find it in : filing cabinets, spread sheets , folders, ledgers, lists, colleagues’ memories, piles of papers on your desk. What does “managing data/information” mean ? Making information work for us Making information useful Avoiding "accidental disorganisation” Making information easily accessible and integrated with the rest of our work 8 Compiled by: G/slassie E.

9. Cont. Basic Concepts File - A set of related records. Record - A collection of Data about an individual item. Field - A single item of data common to all records. 9 Compiled by: G/slassie E.

10. Cont. What is a database? A structured collection of related data . A filing cabinet, an address book, a telephone directory, a timetable, etc. Google and your email is a database School Student Information System 10 Compiled by: G/slassie E.

11. Cont. Data Handling Approaches Data management passes through the different levels of development along with the development in technology and services . These levels could best be described by categorizing the levels into three levels of development. Even though there is an advantage and a problem overcome at each new level, all methods of data handling are in use to some extent. The major three levels are; Manual Approach, File-Based Approach and Database Approach 11 Compiled by: G/slassie E.

12. Cont. 1. Manual (Traditional) Approach Before computerization, data was stored using manual filing system. This system has been in practice for years. Data storage and retrieval follows the primitive and traditional way of information handling cards and paper are used for the purpose. The data storage and retrieval will be performed using human labour . In manual file system, data was usually stored in an organized Alphabetically Sorted or Numerically Ordered , way in a Paper , Flat Files , Box Files , File Cabinets , Shelves ,.. 12 Compiled by: G/slassie E.

13. Cont. Limitations of the Manual Approach Files are exposed (prone) to errors Time consuming to access and maintain the files Files are easily damaged , lost or misplaced Difficult to share the file at the same time Difficult to update , retrieve , integrate and Cross referencing Redundancy (multiple copies of the same files) in different same organization Inconsistency (if one copy is changed the other copies may not be updated ) Cross referencing is difficult The frequent need of report generation and the need for greater efficiency drove people to manage their data using computers. 13 Compiled by: G/slassie E.

14. Cont. 2. Computerized System In this method data is stored, processed and Presented using a computer . Currently , two different systems are on practice: 2.1 File-Based Approach and 2.2 Database Approach 2.1 File-Based Approach There are several computer applications with file based processing used for the purpose of data handling. This approach is the decentralized computerized data handling method. Since every application defines and manages its own data , the system is subjected to serious data duplication problem . It is a collection of applications that perform services for end users such as the production of reports . 14 Compiled by: G/slassie E.

15. Cont. Figure : File-Based System 15 Compiled by: G/slassie E.

16. Cont. Limitations of File-Based Approach Duplication of data, Data Dependency on the application, Incompatible File Formats, Separation and Isolation of Data, Limited Data Sharing, and Lengthy Development and Maintenance Time. Duplication of data (Data Redundancy) Duplication of data means same data being stored more than once . Data redundancy is a problem in file-based approach due to the decentralized approach . 16 Compiled by: G/slassie E.

17. Cont. The main drawbacks of duplication of data are: Duplication of data leads to wastage of storage space . If the storage space is wasted it will have a direct impact on cost. The cost will increase . Duplication of data can lead to loss of data integrity ; the data are no longer consistent . Data Dependence on Application Data dependence means the application program depends on the data . If some modifications have to be made in the data , then the application program has to be rewritten . If the application program is independent of the storage structure of the data, then it is termed as data independence . 17 Compiled by: G/slassie E.

18. Cont. Incompatible File Formats F ile-based system lacks program data independence , the structure of the file depends on the application programming language. Incompatible file formats between different applications and programs creating inconsistency . The incompatibility of such files makes them difficult to process jointly . Separation and Isolation of Data In file-based approach, data are isolated in separate files ; hence it is difficult to access data . The application programmer must synchronize ( coordinate) the processing of two files to ensure that the correct data are extracted. This difficulty is more if data has to be retrieved from more than two files . 18 Compiled by: G/slassie E.

19. Cont. 2.2 Database Approach It is fair to say that databases play a critical role in almost all areas where computers are used, including business , engineering , medicine , law , education , and library science , to name a few. Definitions: “A Database is a collection of related data .” “ A Database is a shared collection of interrelated data , designed to meet the varied information needs of an organization. A database has two important properties: it’s integrated and shared .” “ A Database is a well-organized collection of data that are related in a meaningful way , which can be accessed in different logical orders .” “ A Database is a collection of persistent data that is used by the application systems of some given enterprise .” 19 Compiled by: G/slassie E.

20. Cont. “ A Database is a structured object that means it is consists of Data and Metadata , with metadata being the structured part. Data in a database is the actual stored expressive information , such as all the names and addresses of your customers. Metadata describes the structure applied by the database to the customer data. In other words, the metadata is the customer table definition . The customer table definition contains the fields for the names and addresses , the lengths of each of those fields , and datatypes . Metadata applies structure and organization to Raw Data . 20 Compiled by: G/slassie E.

21. Cont. A database has the following Contained Properties : A database is designed, built, and occupied with data for a specific purpose . A database represents some aspect of the real world, sometimes called the Mini-world . Changes to the mini-world are reflected in the database . A database is a logically rational collection of data with some Inherent Meaning . A database can be of Any Size and of Varying Complexity . Limitations and Risk of Database Approach Introduction of new professional and specialized personnel. The cost and risk during conversion from the old to the new system; Complexity in designing and managing data High cost to be incurred to develop and maintain the system High impact on the system when failure occurs to the central system . 21 Compiled by: G/slassie E.

22. Figure : shows a general overview of a database. A database is often represented graphically by a cylindrical disk, as shown on the left of the diagram. The database contains both metadata and raw data. The database itself is stored and executed on a database server computer 22 Compiled by: G/slassie E.

23. Cont. DBMS (Database Management System) A DBMS is a collection of programs that enables users to create and maintain a database. Usually a database accompanies with management software called DBMS. E.g .: Microsoft Access is a popular example of a single or small-group user DBMS. Microsoft's SQL Server is an example of a DBMS that serves database requests from multiple (client) users . Other popular DBMSs are IBM's DB2 , Oracle Corporation's Oracle , and Sybase Corporation's Sybase . We will call the Database and DBMS software together a Database System , which requires the database to be well-organized and managed. 23 Compiled by: G/slassie E.

24. Cont. The DBMS is hence a general-purpose software system that facilitates the processes of Defining , Constructing , and Manipulating databases for various applications. Defining a database involves specifying the data types , structures , and constraints for the data to be stored in the database. Constructing the database is the process of storing the data itself on some storage medium that is controlled by the DBMS. Manipulating a database includes such functions as querying the database to retrieve specific data , updating the database to reflect changes in the mini world, and generating reports from the data. 24 Compiled by: G/slassie E.

25. Cont. Figure: Illustrates these ideas. Generally, DBMS is software that provides a set of primitives for defining, accessing, and manipulating data. In DBMS approach, the same data are being shared by different application programs; as a result data redundancy is minimized. 25 Compiled by: G/slassie E.

26. Cont. Services of DBMS A DBMS provides you a systematic method for creating , updating , storing and retrieving data in a database. DBMS also provides the service of controlling data access , enforcing data integrity , managing concurrency control , recovery and user accessible catalogue . A DBMS Support for Data Communication : should provide the facility to integrate with data transfer software or data communication managers. 26 Compiled by: G/slassie E.

27. Cont. Objectives of Database System The main objectives of database management system are Data Availability , Data Integrity , Data Consistency , Data Security , Centralized Data Management and Data Independence . Data Availability refers to the fact that the data are made available to wide variety of users in a meaningful format at reasonable cost so that the users can easily access the data. Data Integrity refers to the correctness of the data in the database. In other words, the data available in the database is a reliable data . Data Consistency - DBMS is designed to have data consistency (since data is kept as one file , different copies cannot exist ). 27 Compiled by: G/slassie E.

28. Cont. Data Security refers to the fact that only authorized users can access the data. Data security can be enforced by passwords . If two separate users are accessing a particular data at the same time , the DBMS must not allow them to make conflicting changes . Centralized Data Management - In DBMS all files are integrated into one system thus reducing redundancies and making data management more efficient . Data Independence DBMS allows the user to store , update , and retrieve data in an efficient manner. DBMS provides an “ abstract view of Data ” of how the data is stored and maintained in the database it means the system hides certain details. 28 Compiled by: G/slassie E.

29. Cont. Components of DBMS To understand the value of a DBMS, it helps to see the components that are commonly provide . Each DBMS has unique strengths and weaknesses . A DBMS is evaluated based on the Database Engine Data Dictionary Query Processor Report Writer Forms Generator Application Generator Communication (Integration) and Security 29 Compiled by: G/slassie E.

30. Cont. Database Engine - is the heart of DBMS . It is responsible for storing , retrieving , and updating the data. The performance (speed) and the ability to handle large problems (scalability ). Data Dictionary – It holds the definitions of all of the data tables. It holds name , type , range of values , sources and access authorization . Stand Alone Information and Documentation; It helps developer and users find the data that need. Query Processor - It enables developers and users to store and retrieve data. In some case the query processor is the only connection to database . Report Writer - Most business users want to see summaries of the data in some type of report . Many of the reports follow common formats. 30 Compiled by: G/slassie E.

31. Cont. Forms Generator - it called ‘ input screen ’ helps the developer create input forms. The goal is to create forms that making it easy for users to inter data. The forms can include graphs and images . Application Generator - An application is a collection of forms and reports designed for a specific user task. It is the final package that you are trying to create. Security - Because a primary goal of a database is to share data with multiple users , the DBMS must also be responsible for establishing and maintaining security access controls . The DBA is responsible for assigning and managing user accounts to uniquely identify users . The most common method of identifying users is by accounts and password . Each person has a unique account name and chooses a password. 31 Compiled by: G/slassie E.

32. Cont. Advantages of DBMS Data Storage , Retrieval , and Update Controlling Data Redundancy and Data Consistency Restricting Unauthorized Access Providing Persistent Storage for Program Objects Data Structure Compatibility Providing Backup and Recovery Providing Multiple User Interfaces and Sharing a Data Representing Complex Relationship among Data Enforcing Integrity Constraints Reduced Application Development Time Availability of Up-to-Date Information Improved Maintenance through Data Independence Concurrency Control Services Support for Data Communication 32 Compiled by: G/slassie E.

33. Cont. Benefits of Database Approach Data can be shared Improved accessibility of data Redundancy can be reduced Inconsistency can be avoided Data independence activated Quality data can be maintained Security majors can be enforced Improved Design support Standards can be enforced Compactness Centralized information Control Transaction support can be provided Integrity can be maintained (necessary for efficient transaction) Support for multiple views ( necessary for security reasons) 33 Compiled by: G/slassie E.

34. Cont. DBMS software Options 34 Consumer Microsoft Excel - Limit of 65,536 Rows Microsoft Access FileMaker Pro MySQL (Open Source) Postgres (Open Source) Enterprise RDMS Oracle IBM/DB2 MS SQL-server Sybase Informix Lotus Notes MySQL (Open Source) Postgres (Open Source) Compiled by: G/slassie E.

35. Cont. Where are DB Systems used? Backend for Computer Based Information System ( CBIS ) applications: Student record System Backend for large Websites: Google Backend for Web services: Amazon Databases everywhere ! Because of the versatility of databases, we find them powering all sorts of projects: A web site that is capturing registered users A client tracking application for social service organisations A medical record system for a health care facility A system that issues airline reservations 35 Compiled by: G/slassie E.

36. Cont. Database System Applications Who uses Databases? Almost everyone: Business , Doctors , Teachers and Students etc… Banking : All Transactions Airlines : Reservations, Schedules Universities : Registration, Grades Sales : Customers, Products, Purchases Manufacturing : Production, Inventory, Orders, Supply Chain Human Resources : Employee Records, Salaries, Tax Deductions Who Interacts with a DBMS? Many different individuals are involved with a DBMS over its life: Systems Analysts , Database Designers , Database Administrators , Application Developers and Users . 36 Compiled by: G/slassie E.

37. Cont. Components of DBMS Environment People - Includes database designers, DBAs, application programmers, and end-users. Data - Used by the organization and a description of this data called the schema. Procedures - Instructions and rules that should be applied to the design and use of the database and DBMS. Software - DBMS, operating system, network software (if necessary) and also the application programs. Hardware - Can range from a PC to a network of computers. 37 Compiled by: G/slassie E.

38. Cont. Database Languages Each DBMS should have facilities to define the database , manipulate the content of the database and control the database . These facilities will help the designer , the user as well as the database administrator to discharge their responsibility in designing , using and managing the database. The two major categories of languages are:- Data Definition Language ( DDL) Data Manipulation Language (DML) Still One Can Consider Additional DB Languages Called; Data Control Language (DCL) 38 Compiled by: G/slassie E.

39. Cont. 1. Data Definition Language (DDL ) Commands for setting up schema or the intension of database . These commands are used to setup a database , create , delete and alter table with the facility of handling constraints . Allows DBA or user to describe and name entities , attributes and relationships required for the application. Some Command Examples: CREATE - to create objects in the database ALTER - alters the structure of the database DROP - delete objects from the database COMMENT - add comments to the data dictionary RENAME - rename an object 39 Compiled by: G/slassie E.

40. Cont. 2. Data Manipulation Language (DML ) Is a core command used by end-users and programmers to store , retrieve , and access the data in the database. E.g. SQL ‘Query Language’ DML can be in two forms Procedural DML : user specifies what data is required and how to get the data. Non-Procedural DML : user specifies what data is required but not how it is to be retrieved. E.g. SQL Some Command Examples: SELECT - retrieve data from the a database INSERT - insert data into a table UPDATE - updates existing data within a table DELETE - deletes all records from a table LOCK TABLE - control concurrency 40 Compiled by: G/slassie E.

41. Cont. 3. Data Control Language (DCL) Database is a shared resource that demands control of data access and usage . Data Control Languages are commands that will help the Database Administrator to control the database . The commands include commands like, Granting Privileges to access the database or P articular object within the database and To Store or Remove database transactions . Some Command Examples GRANT - gives user's access privileges to database REVOKE - withdraw access privileges given with the GRANT command 41 Compiled by: G/slassie E.