Prof. Dr.-Ing. habil. Andreas Mitschele-ThielIntegrated HW/SW Systems Group

Self-Organization17 November 2019 1

Dr.-Ing. Ali DiabComputer Engineering and Automation Research Group

Page 1

Database Systems-

Introduction

Dr.-Ing. Ali Diab

Outline

• Definitions of Database

• Concept of a Shared Organizational Database

• Databases Histody

• File-Based Processing

• Solution – The Database Approach

– Database Management System (DBMS)

2

Definitions of Database

3

Definitions

• Definition 1

– Database is an organized collection of logically related data

• Definition 2

– Database is a shared collection of logically related data that is storedto meet the requirements of different users of an organization

• Definition 3

– Database is a self-describing collection of integrated records

• Definition 4

– Database models a particular real world system in the computer in theform of data

4

Definitions

• Data

– Stored representations of meaningful objects and events

or

– Referred to facts concerning objects and events that could berecorded and stored on computer media

• Structured: numbers, text, dates

• Unstructured: images, video, documents

• Information

– Data processed to increase knowledge in the person using the data

• Metadata

– Data that describes the properties and context of user data

5

Data in Context

• Context helps users understand data

6

Data to Information

• Graphical displays turn data into useful information that managerscan use for decision making and interpretation

7

Metadata Example

• Descriptions of the properties or characteristics of the data,including data types, field sizes, allowable values, and datacontext

8

Concept of a Shared Organizational Database

9

An Example

10

Accounting

AccountsPayable

AccountsReceivable

Management

ControlPlanning

Manufacturing

ProductionScheduling

Marketing

ProductDevelopment

Sales

CorporateDatabase

Databases History

11

A Bit of History

• Computers initially used for computational/ engineering purposes

• Commercial applications introduced File Processing System

– A collection of application programs that perform services for the end-users such as production of reports

– Each program defines and manages its own data

12

RegistrationApplications

RegistrationDataFiles

Registration

ExaminationApplications

ExaminationDataFiles

Examination

LibraryApplications

LibraryDataFiles

Library

File-Based Processing

13

File-Based Processing

14

Disadvantages of File Processing

• Program-Data Dependency

– File structure is defined in the program code

– All programs maintain metadata for each file they use

• Duplication of Data (Data Redundancy)

– Different systems/programs have separate copies of the same data

– Same data is held by different programs

– Wasted space and potentially different values and/or different formatsfor the same item

• Limited Data Sharing

– No centralized control of data

– Programs are written in different languages, and so cannot easilyaccess each other’s files

15

Disadvantages of File Processing

• Lengthy Development Times

– Programmers must design their own file formats

• Excessive Program Maintenance

– 80% of information systems budget

• Vulnerable to Inconsistency

– Change in one table need changes in corresponding tables as wellotherwise data will be inconsistent

16

Problems with Data Dependency

• Each application programmers must maintain their own data

• Each application program needs to include a code for themetadata of each file

• Each application program must have its own processing routinesfor reading, inserting, updating and deleting data

• Lack of coordination and central control

• Non-standard file formats

17

Problems with Data Redundancy

• Waste of space to have duplicate data

• Causes more maintenance headaches

• The biggest problem:

– When data changes in one file, could cause inconsistencies(Vulnerable to Inconsistency)

– Compromises data integrity (data reliability)

18

Solution – The Database Approach

19

Database Approach

• Central repository of shared data

• Data is managed by a controlling agent

• Stored in a standardized and convenient form

• This requires a Database and Database Management System(DBMS), which is

– Database application program

– Software system that is used to create, maintain, and providecontrolled access to users of a database

– Computer program that interacts with database by issuing anappropriate request (SQL statement) to the DBMS

20

Database Approach

21

RegistrationExamination

LibraryApplications

Library

ExaminationApplications

RegistrationApplications

Database Management

System

University StudentsDatabase

Data SharingData independenceControlled redundancyBetter data integrity

Database Management System - Example

22

Advantages of Database Approach

• Program – data independence

• Minimal data redundancy

• Improved data consistency

• Improved data sharing

• Increased productivity of application developements

• Enforcement of standards

• Improved data quality

• Improved data accessibility and responsiveness

• Reduced prgogram maintenance

• Improved decision support

23

Costs & Risks of Database Approach

• New, specialized personals

• Installation and management cost and complexity

• Conversion costs

• Need for explicit backup and recovery

• Organizational conflict

24

Database Management System (DBMS)

25

Simplified DBMS Environment

26

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 secondarystorage 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 andapplication programs

– Note: all data should be kept valid and consistent

27

Typical DBMS Functionality

• Other functions

– Protection or Security measures to prevent unauthorized access

– Active processing to take internal actions on data

– Presentation and Visualization of data

– Maintaining the database and associated programs over the lifetime of the database application

• Called database, software, and system maintenance

28

Example Database

• Mini-world

– A database represents some aspect of the real world

• Mini-world for the example:

– Part of a UNIVERSITY environment

• Some mini-world entities

– STUDENTs

– COURSEs

– SECTIONs (of COURSEs)

– (academic) DEPARTMENTs

– INSTRUCTORs

– ...

29

Example 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

• Note: The above entities and relationships are typically expressedin a conceptual data model, such as the ENTITY-RELATIONSHIP data model

30

Example Database (with a Conceptual Data Model)

• The conceptual data model is a structured business view of thedata required to support business processes, record businessevents, and track related performance measures

• This model focuses on identifying the data used in the businessbut not its processing flow or physical characteristics.

31

Example Database (with a Conceptual Data Model)

32

Example of a Simple Database

33

Example of a Simple Database

34

Main 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

– This allows the DBMS software to work with different databaseapplications

35

Example of a Simplified Database Catalog

36

Database Users

• Users may be divided into

– Those who actually use and control the database content

– Those who design, develop and maintain database applications(called “Actors on the Scene”)

• Those who design and develop the DBMS software and related tools, andthe computer systems operators (called “Workers Behind the Scene”).

37

Database Users

• Actors on the scene

– Database administrators

• Responsible for authorizing access to the database, for coordinating andmonitoring its use, acquiring software and hardware resources, controllingits use and monitoring efficiency of operations

– Database Designers

• Responsible to define the content, the structure, the constraints, andfunctions or transactions against the database. They must communicatewith the end-users and understand their needs

38

Advantages of Using the Database Approach

• Controlling redundancy in data storage and in development andmaintenance efforts

• Restricting unauthorized access to data

• Providing Storage Structures (e.g. indexes) for efficient QueryProcessing

• Providing persistent storage for program Objects

39

Advantages of Using the Database Approach

• Providing backup and recovery services

• Providing multiple interfaces to different classes of users

• Representing complex relationships among data

• Enforcing integrity constraints on the database

• Permitting inferences and actions using deductive and active rules

40

Advantages of Using the Database Approach

• Deductive rule

41

Describing and Storing Data in a DBMS

• Data model

– Data can be represented in different ways

• Trees, graphs, tables, etc.

– A data model is a way to represent data

• Relational data model

– Using tables to represent data

42

Relational Data Model

• Relation Schema

– A table with rows and columns

• Schema

– Conceptual schema (logical schema)

• Describes the columns, or fields of relations

– Physical schema

• File organizations

• Indices

– External schema

• A collection of views from the conceptual schema

43

Levels of Abstraction

44

Levels of Abstraction

• Example: University Database

– Conceptual schema

• Courses(cid: string, cname:string, credits:integer) Faculty(fid:string,fname:string, sal:real) Teaches(cid:String, fid:string)

– Physical schema

• Relations stored as unordered files. Index on first column of Students.

– External Schema (View)

• Course_info(cid:string,fname:string)

45

Data Independence

• Applications insulated from how data is structured and stored

– Logical data independence

• Protection from changes in logical structure of data

– Physical data independence

• Protection from changes in physical structure of data.

• One of the most important benefits of using a DBMS!

46

Queries in a RDBMS

• Relational calculus

– A formal query language based on mathematical logic

• Relational algebra

– Another formal language based on a collection of operators formanipulating relations

• Structured Query Language (SQL)

– Data Manipulation language (DML)

– Data Definition Language (DDL)

47

Database Queries

• SELECT queries are used to extract information from a database

• Example

– SELECT first_name, last_name FROM president WHERE state =‘NY’;

– SELECT first_name, last_name FROM president WHEREbirth_date like ’19%’;

– SELECT count(*) FROM president WHERE state = ‘VT’;

– SELECT first_name, last_name FROM president WHERE state IN(ME,NH,VT,MA,CT,RI);

48

Database Queries

• Use CREATE TABLE query, specifying fields and column types

• Example

– CREATE TABLE friends (first_name varchar(25), last_namevarchar(25), gender enum(‘M’, ‘F’), grade smallint unsigned,hair_color varchar(20), email varchar(30), screen_name varchar(25),phone char(12));

• Use the INSERT query

• Example

– INSERT INTO friends VALUES (‘Daffy’, ‘Duck’,‘M’,11,’Brunette’,‘daffy@disney.com’, YellowBill’, ’315-555-1213’);

49

Database Queries

• Use the UPDATE query

• Example

– UPDATE friends

SET phone=‘315-555-1234’

WHERE first_name=‘Daffy’ and last_name = ‘Duck’;

• Use the DELETE query

• Example

– DELETE FROM friends WHERE first_name=‘Daffy’ andlast_name=‘Duck’;

50