Database management systems

1
Database management systems
2
Codds Eight Services of a DBMS

• Data storage, retrieval and update
• A user-accessible catalog (meta data)
• Transaction support all or none
• Concurrency control all users allowed to update
  correctly and transparently
• Recovery services to a a consistent state
• Authorization services for security levels
• Support for data communication
• Data integrity correct and consistent through
  constraints

3
DBMS components to consider
4
Multi-user architectures

• Tele-processing 1 cpu, multiple terminals
• File-server applications and dbms on each
  workstations, files on server(s)
• Client-server
• 2-tier client provides a process, server
  provides a resource for the process
• 3-tier client provides user interface processes
  middle layer handles application logic, syntax
  verification, transaction logic and server layer
  handles dbms integrity, security logic, system
  catalogs, db recovery

5
The relational model

• History
• Set model proposed in 1968
• Relational model proposed by EF Codd in 1970
  (Comm. ACM, 13(6), 377-387, with the following
  accomplishments
• a high degree of data independence
• normalization
• a framework for a set-oriented DML
• DBMS System R - IBM
• INGRES University of California at Berkeley

6
The relational model

• Common applications using this model
• Oracle
• DB2
• SQL Server
• Sybase
• Informix
• Access
• Interbase
• Rbase
• MySQL
• PostgreSQL
• Interbase
• Delphi

7
The relational model

• Terminology
• Based on the mathematical concept of a relation
  (a table)
• Focused on set theory and predicate logic

8
The relational model

• The relational data structure consists of
• Relation a logical structure allowing
  interaction with the dataset arranged by columns
  and rows
• Attribute a column within a relation for
  grouping values used to describe that component
  of the dataset
• Domain the allowable values (data types) within
  an attribute
• Tuple one record (row) in a relation with
  values from each attribute that describe and
  identify one entity within the entity set
  (relation)

9
The relational model

• And
• Degree the number of attributes (columns)
  within a relation
• Unary, binary, ternary, , n-ary
• A property of the intension
• Cardinality number of tuples (rows) in a
  relation
• A property of the extension
• Relational database a collection of normalized
  relations (usually base relations) with distinct
  identities

10
The relational model

• Alternate naming conventions

11
The relational model

• Mathematical relations
• Cartesian product Set1 x Set2
• Set1 contains 1,2,3
• Set2 contains 6,7,8
• The Cartesian product contains
  (1,6),(1,7),(1,8),(2,6),(2,7),(2,8),(3,6),(3,7),(3
  ,8)
• A relation is a subset of the Cartesian product
  of the domains of the attributes
• We specify the domains of these sets to define a
  relation

12
The relational model

• Relation schema a set of attribute and domain
  name pairs
• A relation is a subset of the values contained in
  the domains of each of its attributes
• A1d1, A2d2,...,Andnd1?D1, d2?D2,dn?Dn
• When the values have been identified, it is a
  domain instance
• PlantID24, LotNumber48892, SerialNumber2443

13
The relational model

• Null a value for an attribute that is
• nonexistent
• exists, but
• is not known
• is unavailable
• Inconsistent with the relational model as a
  first-order (Boolean) logic
• Controversial, but commonly used

14
The relational model

• Relation properties
• A distinct name
• 1 or more attributes, each with
• a distinct name
• a domain constraint
• 0..N tuples, with each
• containing up to 1 value from each of the
  respective domains
• being distinct (unique) i.e., not duplicated
• no more than 1 atomic value per cell (row-column
  intersection) i.e., 1st Normal Form
• Columns and rows can be in any order

15
The relational model

• Relational keys
• Superkey
• one or more attributes that define (uniquely
  identify) a tuple within a relation
• there is always one superkey in a relation
• Candidate key
• A superkey which has no proper subset (a smaller
  set of attributes that are still a superkey)
• Group Key (Composite Key) a candidate key with
  more than one attribute
• Primary Key the selected candidate key for a
  relation
• Foreign Key one or more attributes in a
  relation that references (can be matched to) the
  candidate key of a relation

16
The relational model

• Two common representations
• TableName (GroupKeyField1, GroupKeyField2,
  Field3, , FieldN)

17
The relational model

• Data integrity
• Domain integrity values of attributes must be
  from within the domain of the attribute
• Entity Integrity the collection of values in a
  primary key must be unique and no attribute
  within the primary key can contain a null value
• Referential Integrity the collection of values
  in the foreign key of a relation that are either
  null or match the values of the primary key in
  the relation to which it refers
• Enterprise (Business) Integrity rules specified
  by the application needs to apply additional
  constraints

18
The relational model

• View
• A virtual or derived relation
• It contains a schema, but no data
• Data is derived from the base relations to which
  it refers
• They
• Hide tables, columns and data when necessary
• Allow easy creation of multiple interfaces to the
  same data
• Are grouped into
• Theoretically not updatable
• Theoretically updatable
• Partially updatable