Databases notions - - PowerPoint PPT Presentation

▶
databases notions slide 11
SMART_READER_LITE
LIVE PREVIEW

Databases notions - - PowerPoint PPT Presentation

Aug 12, 2023 105 likes •264 views

Databases notions slide 11 Technical context DB advantages DB vocabulary DB data models slide 31 A Reference

slide-1
SLIDE 1

1

Introduction-

  • 10
  • Databases notions

slide 11

Technical context DB advantages DB vocabulary

DB data models

slide 31

A Reference architecture

slide 44

What is a DBMS ?

slide 49

11

Data in main-memory (variables) Files Databases

  • 12
  • Principles

Data storage in a fast access but non persistent

storage

Problems

Temporary storage «low-capacity » Programming languages Single-process (single-user) access

slide-2
SLIDE 2

2

Introduction-

13

  • Persistent storage on disks

«high-capacity» of data Programming languages and file systems Single-process (single-user) access

14

  • library

Social service Cobol Appli Java appli C Appli Students and Modules files Students and Books files Students and Rooms files Study direction 15

  • Library application

FS

Students file

* 2

Description of Students file Edition of Students file in alphabetical

  • rder

Program 1

Description of Sudents files Updates on Students giving a name

Program 2 16

  • No global vision of data

Problem to understand links between data Data redundancy Inconsistency

No data sharing between users No independence between data and programs Problems with data security Multiplicity of languages, systems, hardware

slide-3
SLIDE 3

3

Introduction-

17

  • Persistent storage on disks

«very high-capacity» Query languages and database PLs Multi-users

18 Terminals PC Windows PC Linux PC NT C Appli Java Appli Cobol Appli DBMS Students Modules Books Rooms DB catalog DB 19

!"

PC Windows PC Linux PC NT C Appli Java Appli Cobol Appli Students Modules Books Rooms DB catalog DB DBMS 20

"#

Integration :

Unique and global description of data

No redundancy Consistency

Independence :

Independence between data and programs Independence between a logical and physical description of

data

slide-4
SLIDE 4

4

Introduction-

21

"#$%&

Security :

Semantic control Protection against unauthorized access Protection against crashes

Easiness for end-user :

Data sharing Personalized and High-level vision Easy management of data Efficient access to data Distribution of data and programs

22

  • Data

Data model Database DBMS Database schema Database instance Integrity constraint Data dictionary (catalog) 23

  • A structure

Simple: price, name, date Complex: person, document, image

A semantic :

Amount of money on an account A picture of Paris

An owner :

Who creates the data Who defines rules on the data

  • « the temperature has to be between -30°

C and +40° C »

  • « the salary of year n has to be greater than the salary of year n-1 »

Who gives rights on the data

24

$%&

Users

Querying

« temperature on Paris the 6th of October 2004 ? »

Updating

« Add 100 euros on M. Hill’s account »

slide-5
SLIDE 5

5

Introduction-

25

  • Set of concepts to describe :

Data of the real world Links between data Semantic of data

Set of operators to manage data

26

  • DB :

Collection of data described according to a model

DBMS :

Software managing data, according to a model A DBMS has to allow data definition, data updates

and data control

27

  • Schema (intension) :

Data description, according to a model Network model, relational model, hierarchical model Generally static

Instance (extension) :

Data Collection, described according to a model Instance of the schema Dynamic

28

  • Integrity constraint (IC) :

Rule defined on data, to specify a consistent state of

the database

The salary has to be greater than the “1000”

Data dictionary (DD):

data describing the data (meta-data) « values of the schema »

slide-6
SLIDE 6

6

Introduction-

29

'

As a central element :

databank (professional, general public) Transactional systems (bank, insurances,…) Decision support system (dashboard, statistical analysis,…)

As an essential element :

Communication and Information systems (enterprise, administration,…) Control and supervision Systems (telecommunications, production,…)

As an auxiliary element :

Information retrieval in digital libraries systems (texts, sounds, graphics,

…)

CAx systems (CAD, CALS, …) …

30

  • Tpm notion

Querying Response time in mn or h ≈ To Decision support system Datewarehouse Datamining > 100 tps Querying + Updates Response time < 2 s. ≈ Go Transactional application Service intensity Program complexity Data volume TPC (Transaction Processing Council) Benchmarks transactional systems

31

#

Reality Very high-level Conceptual schema High-level Conceptual schema Internal schema ER Model, SDM, Z Network or relational models DDL Tools: placing and access methods Taking into account DBMS features

32

  • Data design

Models

Entity relationship model [Chen 76] Hierarchical model Network model Relational model

slide-7
SLIDE 7

7

Introduction-

33

#

  • Data model :
  • Set of concepts to describe :
  • Data
  • Links between data
  • Semantic of data
  • Generally, an associated set of operators
  • Description formalism :
  • Textual
  • Graphical
  • Mathematics
  • Examples of models :
  • Entity-relationship
  • Hierarchical / Network model
  • Relational
  • Object, Relational-Object

34

( )*+,

moduleId nbH coord Module Register Student name age mark address studentId lendingDate Room Book roomId price bookId title Borrow Rent

1,n 3,n 0,n 0,1 0,1 1,1

35

(-%

1,1 0,1 0,1 0,n 3,n 1,n

Module moduleId nbH coord Student studentId name age address Book bookId title Room roomId price Register mark Borrow lendingDate Rent

36

'"#!(.

Advantages

Rich semantic Extension to object

concepts (inheritance, ...)

visual

model for database design drawbacks

Just for data describing No associated operators No ER DBMS

not an implementation

model

slide-8
SLIDE 8

8

Introduction-

37

/

IBM IMS system, designed at the end of 60ies for

Appolo program (NASA)

Example

Modules Students Rooms Books Students Modules 38

/$%&

DB Schema

Tree structure

DB

Set of records linked by pointers DML Navigational and procedural language (DL/1, IMS system)

Problems :

No independence between logical/ physical level Data Redundancy => inconsistency

39

Defined by the DBTG, CODASYL comity, 1971

(new version in 1978)

Example

Room Students Registration Book Modules 40

0$%&

DB schema

Acyclic oriented graph

DB

Set of records linked by pointers

DML

Navigational and procedural Pointer based Standards CODASYL 71, 78

Systems

IDS2 of Honeywell (1975), Total of Cincom (1974), Adabas of Soft.

Ag (1978)

Problems

No physical/logical independence

slide-9
SLIDE 9

9

Introduction-

41

.

[CODD 70] « A Relational Model for Large

Shared Data Banks »

Example

Students(StudentId, name, address, age) Module(ModuleId, nbH, coord) Inscription(StudentId, ModuleId, mark) Book(BookId, title, StudentId, LendingDate) Room(RoomId, price, StudentId)

42

.$%&

Module moduleId nbh coord Student studentId roomId name age address Book bookID studentId title lendingDate Room roomId studentId price Register moduleId studentId mark

43

.$1&

DB Schema

Set of relation schemas

DB

Set of tuples linked by values

DML

Set oriented Declarative Standard [ SQL 86, 89, 92 ou SQL2]

Research prototype

System/R - IBM (1976), Ingres - Berkeley (1976)

Commercial system

SQL/DS and DB2 d’IBM (1982), Oracle (1983), Ingres (1983), Informix

(1981), Sybase (1984), SqlServer (1998)

MySQL (1995) Access

44

.

Advantages

Logical/physical independence Simple DML Based on a solid theoretical foundation Standard

slide-10
SLIDE 10

10

Introduction-

45

'.

A 3 levels data description Example Consequences

46

'1"

ANSI/X3/SPARC (1975)

External schema 1 External schema n Conceptual schema Physical schema

….

47

(

External schema for library appli

  • students
  • books

External schema for study direction

  • students
  • modules

External schema for social service

  • students
  • rooms

Conceptual schema Physical schema

How data are seen by users Multiple logical representations Global Unique Logical desc of data Global Unique Physical representation (how data are stored (files, index)

48

2

Physical independence

Between storage structures – data structures Allows updating physical organization without changing

programs

Example: add an index

Logical independence

Allows updating the conceptual schema without modifying

programs

Example : add an attribute

slide-11
SLIDE 11

11

Introduction-

49

34

Goal Means Functionalities Architecture Data Definition Language (DDL) Data Manipulation Language (DML) Interface with a PL Java and DBMS Transaction DBMS users 50

5

Goal :

data access simplicity and efficiency High level of security

Means :

Giving a high level vision of data, using a logical model Doing the translation between “high-level” vision and

“low-level” vision of data (storage structure and accessing methods)

51

  • DDL + DML

Integrity management IC, Trigger Persistence management Efficient storage and access methods cluster, index Concurrency management (transaction, locks) Security management transaction, journals, crash recovery

mechanisms

Rights management identification, rights definition Optimization algorithms, statistics Interface with PL

52

$&

Versioning Distribution Complex objects (image, document, ...) Knowledge management and deductive DBMS CASE tools

slide-12
SLIDE 12

12

Introduction-

53

'

DB schema def DB manipulation Programmer Administrator User DDL translator DML Translator DBMS Kernel DB DD 54

6

Logical definition of data

Global conceptual schema views (external schemas) Data structure and links between data

Physical Definition of data

Physical schema of data Placing and access methods

control of data

integrity constraints rights

55

6

  • Goal
  • Querying and updating data (read, add, delete, update)
  • Procedural way
  • Define the sequence of operations to do to get the result
  • Hierarchical and network models
  • Navigational languages
  • CODASYL 71, 78 norms for network models
  • Physical structure dependence
  • Integration to a PL (Cobol for network model)
  • Declarative way
  • Define the properties of the desired information, and not the way to get it
  • Relational model
  • Set theoretical language
  • Physical independence
  • SQL 86, 89, 92 norms
  • SQL « alone » and embedded SQL in classical PL

56

76

Drawbacks of DML

Just querying and updating data No complex computing

Solution

Interfacing DML and PL

Examples

Network DML + Cobol Relational DML +

  • Cobol, Pascal, Fortran, PL/1 (1978)
  • Ada, C (1989)
  • Java (SQLJ)
slide-13
SLIDE 13

13

Introduction-

57

8"

JDBC : SQL/CLI level API, independence to

target DBMS (drivers)

SQLJ : embedded SQL for Java Persistence Management interface of java

  • bjects (automatic mapping of java objects to

relational structures – both ways)

58

9

Sequence of elementary operations (read,

write) on DB

Transaction withdraw (AccountIdtf, amount) Begin select balance into s from Accounts where AccountId = AccountIdtf; if s > amount then

Update Accounts set balance = balance – amount where AccountID= AccountIdtf ; giveMoney(amount); Commit();

else

print(‘forbiden’); Abort();

Endif End;

59

9

A : Atomicity C : Consistency I : Isolation D : Durability

Consistent DB Consistent DB time t' time t Transaction 60

  • Designer

Job context : conceptual level Conceptual schema definition using a data model

DBA (DB Administrator)

Job context : 3 levels Defines and updates logical schema Defines IC + Views + rights Defines physical schemas (storage structures, access methods) Using DDL + DML, DD

slide-14
SLIDE 14

14

Introduction-

61

$%&

Application programmer

Job context : external and conceptual level DML + PL Writes programs to query, update, process data Knows logical and physical schemas

Final user

Job context : external level « expert »: query the DB using a DML « Naïve »: interaction via an application