Database Management and Information Systems

Data and Computers in Database Management

Information Systems Overview

Information Systems · Systems of organization and management of information that are necessary for the functioning of a company, an institution, etc. . The existence of an information system is totally independent of its eventual automation. In fact, information systems have existed since long before the invention of computers. · Information systems contain data and models of data organization. · E.g. medical record: provide a method to record the data of certain patients in a standardized way and allow it to be used during treatment.

Data and Computers and Information Systems

IT Tools for Information Management

Information Systems · To facilitate and enhance the management of an organization's information, IT tools are generally used, which allow (usually partial) automation of the information system. . The Computer System is the set of IT tools (computers, basic software, application programs, ... ) used for the automatic processing of information in order to improve its efficiency. · In a computer system, information is represented by means of data, which becomes information after a process of interpretation.

Data and Computers and Databases

Definition and Organization of Databases

Databases . It is a collection of data that represents the information that affects an information system. · It consists of a large amount of data, organized into homogeneous sets in correlation with each other. . The data is stored and managed in permanent memory (disk). · Data is shared across multiple users and applications to reduce redundancy and the possibility of inconsistencies.

Database Components: Schema and Instance

Databases In a database, there is: · A substantially time-invariant part, called the database schema, which describes the characteristics of the data; . A time-varying part, called the instance or state of the database, consisting of the actual values of the data.

Database Example: Courses and Professors

Databases Example: To represent the activated courses and their teachers, you can use a diagram such as (Course, Professor Name) This is a time-invariant schema, but (Medical Informatics, Morettini) (Bioengineering, Burattini) are instances of the above schema and can vary over time.

Data and Computers and Database Management System (DBMS)

DBMS Definition and Diagram

Database Management System (DBMS) It is a software system capable of managing (building, modifying, using) a database. L Data DBMS Computer system Information system

DBMS Functionality and File System Interaction

Database Management System (DBMS) · A DBMS is a software that deals with storing, finding, and managing large amounts of data. . All data is stored in files managed by the file system with which the DBMS interfaces. . In order to optimize its functionality, the DBMS must know in detail the computer's operating system and the organization of the file system.

User Interaction with DBMS

. The user doesn't have to worry about the details of the operating system and the organization of the data because the DBMS takes care of that. · The DBMS provides an interface to the user independent of the operating system that makes it much easier for the user to manage data. · The DBMS allows you to organize generic data into 'templates'.

DBMS Architecture Diagram

user program 1 DBMS file system disk storage user program 2

Features of a Database Management System

Database Management System (DBMS) Features: They offer languages for · Definition of the database schema · use of the database (access to data, insertion, modification, deletion) Ensure efficient database management · Operations must be performed using a set of resources (time and space) that are acceptable to users.

DBMS Persistence and Data Sharing

Database Management System (DBMS) Allows you to manage persistence · Algorithms that allow persistence even in the event of HW/SW malfunctions. Enable data sharing . Many different users with purposes and methods of access, concurrency control for the simultaneous modification of data. Check for redundancy · Multiple users access the same data: inconsistencies are avoided.

DBMS Reliability and Privacy

Database Management System (DBMS) Ensure the reliability of the database · Data is protected against errors due to malfunctions and interference (save and restore operations). Guarantee the privacy of the database . The data is protected from access and use by unauthorized users.

Conventional Approach vs. Database Management System

Conventional approach vs database File B C D E Programma 1 Programma 2 Programma 3 DBMS Programma 1 Programma Programma A B C D BASE DI DATI File modificati

Data and Computers Terminology

Key Database Concepts

Terminology · Database: is a structured set of data (files) logically connected to each other. . File: is a storage entity that has a name. Files are created and deleted from the computer's file system. . Record: It is the smallest logical unit for storing a file in a database. . Mass Storage Device: Device on which the database files are stored.

Data and Computers and Three-tier Architecture of a DBMS

Overview of DBMS Abstraction Levels

Three-tier architecture of a DBMS A DBMS provides its users with an abstract view of the database. In particular, three levels of abstraction can be distinguished: Outer Level utente utente utente utente utente Schema esterno Schema esterno Schema esterno Logic Level Schema logico Internal level Schema interno BD Physical database

Internal Level of DBMS Architecture

Three-tier architecture of a DBMS A. Internal level It considers the database as a set of records in mass memory, in particular it describes the distribution of data on the various media and the ways in which the data is stored. The management of this level is the responsibility of those who administer the DBMS and not of the users who can focus on higher-level aspects for the development of their applications.

Logical (Conceptual) Level of DBMS Architecture

Three-tier architecture of a DBMS B. Logical (or conceptual) level It presents the data in a way that highlights its logical structure, i.e. the information each piece of data possesses and how the data relates to each other. It is also called global level because it describes all the data in the database.

View Level (External) of DBMS Architecture

Three-tier architecture of a DBMS C. View Level (or Outside) It is the highest level of abstraction, presenting data as it is (or can) be seen by a particular class of users. There are many different representations, each associated with a particular user. Therefore, it is possible to associate several external schemas to a logical schema.

Usefulness of Views in DBMS Architecture

Three-tier architecture of a DBMS Views Usefulness: • A restructuring of the integrated schematic can, in some cases, be appropriately disguised by making use of views • Views can be used to better regulate database access control (e.g. by masking confidential data) • Views can be used to dynamically compute new data from data stored in the database, without introducing redundancy. Employee Year of hiring Bianchi 2005 Rossi 1999 Employee Years of service Bianchi 18 Rossi 24

Physical and Logical Independence of Data

Three-tier architecture of a DBMS The three levels of representation allow to introduce the concepts of physical and logical independence of data: • Physical Independence: It consists of the possibility of redefining the physical layer without modifying the logical layer, and therefore without altering the users' programs. • Logical independence: It consists of the ability to define new external schemas or extend the logical schema without altering pre-existing external schemas.

Data and Computers and DBMS Languages

Types of DBMS Languages

The languages of a DBMS A DBMS provides different languages to interact with databases. The level of abstraction of these languages is highly dependent on the data model to which they refer. A common distinction classifies languages on the basis of their functions: · DDL (Data Definition Language): It is used to define patterns (logical, external, internal). · DML (Data Manipulation Language): It is used to query and modify database instances. · DCL (Data Control Language): Includes various types of controls, e.g. for access control.

Data and Computers and DBMS Users

Categories of DBMS Users

Users of a DBMS With respect to the ways of interacting with the DBMS, we can group users into three main categories: 1. End-users: They can access the database through the commands of a query language, or, for even less experienced end users, through software interfaces, which present the menu or icons on the screen. 2. The Designer/Programmer: Design applications that interact with the database. 3. The System Administrator/Administrator: is responsible for managing the database (implements the logical model of the database in the processing system on the physical media of mass storage).

Data and Computers and Data Modeling

Definition of Data Model

Data Modeling · Data can be organized, and then analyzed, using different 'models'. · A data model is defined as a collection of concepts that represent reality.

External Data Model

1. External model . The user of the information system is primarily interested in data associated with his or her specific current interest (e.g., administrative data of a patient, clinical data, etc.). . The user uses the computer for data entry, data presentation, and data processing. . The various views of data that users are interested in define an external data model, which is a model that provides an external view of the data.

Conceptual Data Model

2. Conceptual model · Data models with a large number of constructs, which highlight the concepts present in a database, rather than the structure with which those concepts can be represented in the computer's memory. · The most famous conceptual model of data is the entity-relationship model.

Entity-Relationship Model

Entity-Relationship Model · Data in computers can have different properties (e.g. identification data, laboratory data, diagnostic system data, etc.). · Groups of data that have similar properties are called entities. · Specific examples of entities are called instances. Entity: A set of objects of the reality of interest that have common characteristics and have an autonomous existence Employee