1 DATABASE MANAGEMENT AND SYSTEMS FILE PROCESSING Data

1 DATABASE MANAGEMENT AND SYSTEMS

FILE PROCESSING § Data are stored in files with interface between programs and files. § Various access methods exist (e. g. , sequential, indexed, random). 2 § One file corresponds to one or several programs.

DISADVANTAGE OF FILE SYSTEM § Data Redundancy and Inconsistency § Difficult in accessing data § Data isolation § Integrity problem 3 § Atomicity problem

4 DATABASE APPROACH

DBMS § Database Management System consists of • Database and • Management System § Data • Facts, figures, statistics etc. having no particular meaning • (e. g. 1, ABC, 19 etc). § Record Name 1 ABC Age Fields or Attributes 19 § Table or Relation Roll 1 Name ABC Age 19 2 DEF 22 3 XYZ 28 Tuples or Record 5 Roll

EXAMPLE OF A SIMPLE DATABASE

7 DBMS

APPLICATIONS OF DATABASES § Original • Inventory control • Payroll • Banking and financial systems • Reservation systems • More Recent 8 • Geographic Information system • Multimedia • Telecommunication systems • e-commerce • Dynamic/personalized web content

TOP 10 COMMONLY USED DBMS § Oracle § My. SQL § SQL Server § Postgre. SQL § IBM DB 2 § Microsoft Access § SQLite § Maria. DB § Informix 9 § Azure SQL

DBMS – DATABASE MANAGEMENT SYSTEM § DBMS is a collection of programs that enables users to create and maintain a database 10 § The DBMS is a general purpose software system that facilitates the process of defining, constructing , manipulating and sharing databases among various users and applications

TYPICAL DBMS FUNCTIONALITY § Define a particular database in terms of its data types, structures, and constraints § Construct or Load the initial database contents on a secondary storage medium § Manipulating the database: • Retrieval: Querying, generating reports • Modification: Insertions, deletions and updates to its content • Accessing the database through Web applications § Processing and Sharing by a set of concurrent users and application programs – yet, keeping all data valid and consistent

PROPERTIES OF DATABASE § A database represents some aspect of the real world (miniworld) § A database is a logically coherent collection of data with some inherent meaning 12 § A database is designed, built, and populated with data for a specific purpose

EXAMPLE OF A DATABASE (WITH A CONCEPTUAL DATA MODEL) § Mini-world for the example: • Part of a UNIVERSITY environment. § Some mini-world entities: • • • STUDENTs COURSEs SECTIONs (academic) DEPARTMENTs INSTRUCTORs Slide 1 - 13

EXAMPLE OF A DATABASE (WITH A CONCEPTUAL DATA MODEL) § Some mini-world relationships: • SECTIONs are of specific COURSEs • STUDENTs take SECTIONs • COURSEs have prerequisite COURSEs • INSTRUCTORs teach SECTIONs • COURSEs are offered by DEPARTMENTs • STUDENTs major in DEPARTMENTs

EXAMPLE OF A SIMPLE DATABASE Slide 1 - 15

16 A SIMPLIFIED DATABASE SYSTEM ENVIRONMENT

CHARACTERISTICS OF THE DATABASE APPROACH § Self-describing nature of a database system § Insulation between programs and data § Data Abstraction § Support of multiple views of the data and multi-user transaction 17 § Sharing of processing

CHARACTERISTICS OF THE DATABASE APPROACH § Self-describing nature of a database system: • A DBMS catalog stores the description of a particular database (e. g. data structures, types, and constraints) • The description is called meta-data.

EXAMPLE OF A SIMPLIFIED DATABASE CATALOG Slide 1 - 19

CHARACTERISTICS OF THE DATABASE APPROACH § Insulation between programs and data: • Called program-data independence. • Allows changing data structures and storage organization without having to change the DBMS access programs. Data Item Name Starting Position Length in in Record Characters (bytes) Name 1 30 Student_number 31 4 Class 35 1 Major 36 4 20 Birth_date? ? ?

CHARACTERISTICS OF THE DATABASE APPROACH § Data Abstraction: • A data model is used to hide storage details and present the users with a conceptual view of the database. • Programs refer to the data model constructs rather than data storage details

CHARACTERISTICS OF THE DATABASE APPROACH 22 Support of multiple views of the data: Each user may see a different view of the database, which describes only the data of interest to that user.

CHARACTERISTICS OF THE DATABASE APPROACH § Sharing of data and multi-user transaction processing: • Allowing a set of concurrent users to retrieve from and to update the database. • Concurrency control within the DBMS guarantees that each transaction is correctly executed or aborted • Recovery subsystem ensures each completed transaction has its effect permanently recorded in the database • OLTP (Online Transaction Processing) is a major part of database applications. This allows hundreds of concurrent transactions to execute per second.

ADVANTAGES OF USING THE DATABASE APPROACH § § Controlling redundancy. § § § Providing backup and recovery services. Restricting unauthorized access to data. Providing persistent storage for program Objects Providing Storage Structures (e. g. indexes) for efficient Query Processing Providing multiple interfaces to different classes of users. Representing complex relationships among data. Enforcing integrity constraints on the database. Drawing inferences and actions from the stored data using deductive and active rules

WHEN TO USE DATABASE § Common Circumstances: • There is lots of data (mass storage) • Data is formatted • Requirements: 25 • persistence and reliability • efficient and concurrent access

WHEN NOT TO USE A DBMS 26 § More desirable to use regular files for: • Simple, well-defined applications with no expected changes at all • Small variety of data and/or small amount of data • Stringent, real-time requirements that cannot afford DBMS overhead • Only single (personal) access to data