Samenvatting Databases (X_401008)

Introduction
a database CDB) is a collection of data with a certain logical
structure , specific semantics ,
and a specific group of users .




a database management system CDBMS) allows to :




-
create ,
modify and manipulate a database :


(retrieve) data
-

query using a
query language :
-

support persistent storage of large amounts of data :


enable durability and
recovery from failure ;
-




'
control access to the data by many users in parallel :




without unexpected interactions among users ( isolation)
actions on the data should never be partial Catomi city)


ANSI SPARC architecture

most DBMS 's use the ANSI SPARC architecture because it achieves
data independence and avoidance of duplication
The architecture uses three levels :

i. the view ( external) level hides details and information
for
privacy and security
2 .
the logical ( conceptual ) level describes the data stored
in the database and relations among the data

how is stored
3 . the physical ( internal ) level the data .




Different applications might use different views ,
but the data

is stored in the
physical layer This is for the
once .

good
consistency and this
way we avoid that data at the physical
level needs to be duplicated .




Logical data independence is the ability to modify the logical
schema without
breaking existent applications ( because

applications
the access the views .
not the logical database )

Physical data independence is the ability to modify the
physical
schema without
changing the logical schema .

, relational model

relational databases are based on the relational model .




In the relational model ,
view and logical level are represented
as relations 1 tables .




In the pure relational model ,
a table is a set of tuples and

there are no duplicates allowed and the set is unordered .




A database schema is the structure of the database ,




that is relations
,
t constraints .




A database instance is the actual content C " state
"
) 01 -




the database at some moment .




structured query language
DBMS 's use high level declarative query languages to describe

what information is sought and not to prescribe how to

retrieve the desired information .




The structured query language CSQL) is a declarative data

manipulation language . The user describes conditions the

requested data is required to fulfil .
(select from where)


Data models & Integrity constraints
DBMS 's have well-defined data models ( relational model ) and

data integrity constraints ( meta language for
describing
data , relationships and constraints )



SQL can be used as a query language and a data definition

language to create tables ( create table ) and to define

constraints :




customers Cid City)
primary key constraint name
-
, .




-

foreign key constraint accounts ( depositor customers Cid) ,




constrained data accountnr )
types
-




-
columns constraints c. e.
g. unique)
check constraints ( e. g. 10 )
age
-

, concurrent access & transactions

A transaction is a sequence of operations that performs
a single logical function in a database application .




DBMS 's ensure transactions with ACID properties :


-
Atomicity : a transaction either executes fully commit) or


not at all ( abort) .




-

consistency :
database remains in a consistent state

where all
integrity constraints hold .




Isolation multiple modify at the same time
:
.
users can

but will not see each others partial actions .




'

Durability : once a transaction is omitted successfully ,




modified data is of disk crashes
the persistent regardless
, .




designing database schemes

the entity relationship CER) model can be used where the
entities are objects (e.g customers ,
accounts ) and the

relationship between entities is depicted using the

relationship set descriptor leg . links customer with account)

name
account nr




id customers depositor accounts




city balance




Database schemes also be represented 0mL class
can
by
diagrams .
The entities are depicted using classes and the

relationships using associations .




The relational model
database schemas

Database schemas describe the structure of the database .




Table entries are values that conform to some data type .




The available data
types depend on the database

the supported version of the
management system ,
and

SQL standard .

, Every data type D comes with a domain DOMCD ) .




The domain is the set of possible values .




SQL allows to define application -


specific domains as subsets
of standard data types .




Domains are useful to document that two columns represent

& that comparisons
the same kind of objects are meaningful .




A relation schemas C0F a
single relation ) defines the structure

of a single relation :



-
a Lfinitel
sequence At .
. . .
.
An of distinct attribute names .




for each attribute Ai ( or domain ) D.
a
datatype
-




A relation schema can be written as s= ( Ai :
Di .
. ..
.
An :
Dh )

(e.g. Exercises ( category ,
number , topic ,
Max Points )




If the column datatypes not crucial then nice
are ,
a
way
to communicate schemas is by simply mentioning the

name of the relation 1 table 1 each column or
using a

sketch of the table headers .




A relational database schema S defines :
-
a finite set of relation names ER1 . . . .
. RM3
-
a relation schema schema CRI ) for every relation Ri

a set of
integrity constraints C
-




In
summary ,
S =
CERI .
. . .
,
RM3 . schema , C) .




database states

Tuples are used to formalise table rows . A tuple t with

respect to the relation schema 5 =
LAI : Di . . . .
.
An : Dh )

is t= Cd . dn ) of values such that die dom Cbi )
a
sequence . . . .
,
.




Given a tuple t , we write t Ai .
for the value in column Ai .




Let S =
CERI .
. . .
,
RM3 . schema , C) be a database schema .




A database state 1 for database schemas defines for
Ri , finite CRI ) with
every relation name a set of tuples
respect to schema Cris .