Introduction to Database Concepts

A Database is a collection of data for describing the activities of an enterprise.

A Database Management System (DBMS) is software that is designed to create, access, maintain a database in an effective (functional) – efficient (cost) manner.

1^st database system is IMS (an IBM system) (hierarchical model) developed in 1960’s.

Relational model, 1970, E. F. Codd.

Transactional management.

File Systems vs. DBMS

- File systems coordinate only the physical access.

- DBMS coordinate both physical & logical access.

DBMS

DBMS:

1) Provides data independence (physical; changing file structures

won’t affect user application)(logical)

2) Reduces data redundancy; we have one central storage

3) Provides security.

4) Provides data integrity (correctness).

5) Provides concurrent access & crash recovery.

6) Reduces application development time.

File System

DBMS Architecture

DBMS

Query

Results

View 1

View 2

View n

External Level

Conceptual Level

. . . . . . . . . . . . . . . . . . (External Schema)

Physical Level

Conceptual Schema

Physical Schema

External Schema -> is what a user wants to see

Conceptual Schema -> describes data in terms of the data model of DBMS

Physical Schema -> specifies storage details

Steps in setting a DBMS

1) Define a model containing all appropriate types of data and data relationships. E - R

2) Define integrity constraints on the data (e.g. a dependent can exist with an employee) Entity – Relationship Model

3) Define the conceptual schema for the model.

4) Define the physical level.

5) Define views.

6) Initiate (create) database.

7) Give access rights to users.

DBA: Database Administrator

Functions of DBA:

- Creates schema definitions

- Defines storage structures and access methods

- Modifies schemas when necessary

- Specifies integrity constraits.

DBMS Languages

DDL: Data Definition Language

DML: Data Manipulation Language

Host Language; Usually C or Cobol

Data Sublanguages

Formal Query Language Types

Calculus (Declerative) Algebra (Procedural)

Optional Languages:

o SQL: Structured Query Language (influenced by calculus)

o QBE: Query by Example

Query Optimization

Query

Query Parser

Optimizer

Operator Evaluator

File & Access Methods

Entity – Relationship Model

Introduced by Peter Chan, 1976, ACM Transactions on Database Systems, Vol I, no, I

Developed to facilitate the logical design of a database for an enterprise

Entities

Real World

Relationships among entities

Entity: An object that exists and that is distinguishable from other objects

e.g. Student Account at Bilkent

Customer Patient

Ali Patient 12345 ……

…… ……

……

Entity Set -> is a set of entities of the same type

students employees

Attributes:

All members of an entity set have the same common properties

Student: stuNo, stuName, stuMajor, gpa

Account: accNo, balance, accountOwner, openDate

Domain -> set of values from which an attribute receives values

gpa dateOfBirth

Mapping -> an attribute is a function, which maps on an entity set into a domain

An entity is described by a set of pairs, one pair for each attribute. <attribute, value>

{<stuNo, 1> <stuName, Ali> <stuMajor, CS> <gpa, 3.5>}

an instance of

entity type student

Entity Entity set

int instances

real int I, J

char

Relationships & Relationship Sets

We can relationship between two or more entities

Doctor --- Patient

Student --- Professor

Relationship set: A set of relations of the same type

Formally, let E₁, E₂, ... , E_n be entity sets, not necessarily distinct, the the relationship

Set R is a subset of ;

R Í { (e₁, e₂, ... , e_{n) |} e₁ Î E₁, ... , e_n Î E_n }

Customer Account

Customer – Account Relationship

M N

Instance

1/2001

E1 D1 E1 = Ali D1 = Gıda

E2 D2

10/2000

7/1997

5/1999

E3

E2 = Ahmet D2 = Giyecek

E3 = Ayşe

Example: Ternary Relationship set

Patient

Test

Doctor

(n_ary, n = 3)

M N

Interpretation:

P A doctor can perscribe several tests

for several patients; a test can be

prescribed by several doctors fo several

patients; and a patient can have several

Branch

Customer

Account

tests prescribed by several doctors.

N M

P

Roles: The function that an entity plays in a relationship is called its role. Generally, roles are implicit.

Sometimes roles are specified explicitly to clarify the meaning of a relationship.

{ (S1, S2) | S1 Î Staff Ù S2 Î Staff }

-> a binary and recursive relationship

Multiple Relationship Sets on the same Entity Sets

Two relationship sets between the same entity sets

I W

M

N

Constraints

An E – R enterprise Schema allows explicit specification of certain type of constraints; which are the

constraints that the database contents must always confirm.

The semantics (meaning) of data is partly captured by constraints.

1.Mapping Cardinalities

1.1 One – to one

1.2 One – to – many

1.3 Many – to – one

1.4 Many – to – many

2. Existance Dependencies

If the existance of entity X depends on the existance of entity Y, then X is existance dependent on Y.

Subordinate entity dominant entity