7/31/2019 01 Intro Dbms

1/63

INTRO TO DBMS

7/31/2019 01 Intro Dbms

2/63

DATA

IT IS A COLLECTION OF FACTS AND FIGURES

OVER A PERIOD OF TIME FOR RETRIEVAL AT

A LATER DATE.

INFORMATION

IT IS PROCESSED DATA TO SUPPORTCERTAIN DECISION MAKING FUNCTIONS.

7/31/2019 01 Intro Dbms

3/63

DATABASE

A database is any collection of related data

A database is a persistent , logically coherent

collection of inherently meaningful data , relevant

to some aspect of the real world.

7/31/2019 01 Intro Dbms

4/63

DATABASE MANAGEMENT SYSTEM (DBMS)

Collection of interrelated data

Set of programs to access the data DBMS contains information about a particular

enterprise

DBMS provides an environment that is bothconvenientand efficientto use.

Database Applications: Banking: all transactions

Airlines: reservations, schedules

Universities: registration, grades

Sales: customers, products, purchases Manufacturing: production, inventory, orders, supply chain

Human resources: employee records, salaries, taxdeductions

Databases touch all aspects of our lives

7/31/2019 01 Intro Dbms

5/63

PURPOSEOF DATABASE SYSTEM

In the early days, database applications were

built on top of file systems

Drawbacks of using file systems to store data:

Data redundancy and inconsistency

Multiple file formats, duplication of information in different files

Difficulty in accessing data

Need to write a new program to carry out each new task

Data isolation multiple files and formats

Integrity problems Integrity constraints (e.g. account balance > 0) become part

of program code

Hard to add new constraints or change existing ones

7/31/2019 01 Intro Dbms

6/63

PURPOSEOF DATABASE SYSTEMS (CONT.)

Drawbacks of using file systems (cont.)

Atomicity of updatesFailures may leave database in an inconsistent state with

partial updates carried out

E.g. transfer of funds from one account to another should

either complete or not happen at all

Concurrent access by multiple users

Concurrent accessed needed for performance

Uncontrolled concurrent accesses can lead to inconsistencies

E.g. two people reading a balance and updating it at the

same time Security problems

Database systems offer solutions to all the

above problems

7/31/2019 01 Intro Dbms

7/63

COMPONENTSOFADATABASESYSTEM

Data

Hardware

Software

Users

7/31/2019 01 Intro Dbms

8/63

Ranjit

COMPONENTSOFADATABASESYSTEM

7/31/2019 01 Intro Dbms

9/63

Ranjit

DBMS : A MULTILAYERED SYSTEM

7/31/2019 01 Intro Dbms

10/63

SIMPLIFIED PICTURE OF A DATABASESYSTEM

DATABASE

ENDUSERSAPPLICATION

PROGRAMS

DBMS

COMPONENTS

DATAHARDWARE

SOFTWARE

USERS

7/31/2019 01 Intro Dbms

11/63

WHATDOESADBMSDO

Allow concurrency

Control security

Maintain data integrity

Provide for backup & recovery

Control redundancy

Allow data independence

Provide Non procedural query language Perform automatic query optimisation

7/31/2019 01 Intro Dbms

12/63

WHOINTERACTSWITHADBMS

System analysts

Database designers

Database administrators

Application developers

Users

7/31/2019 01 Intro Dbms

13/63

7/31/2019 01 Intro Dbms

14/63

TYPESOFDATABASES

Operational databases

Used primarily for OLTP Where need is to collect, modify & maintain data on a

daily basis.

Dynamic ie changes continuously .

Retails stores , manufacturing companies etc

Analytical databases Primarily used for OLAP

To store and track historical & time dependent data

Trends , statistics over a long period of time.

Rarely modified.

Reflects point in time snapshot of data

Marketing companies , geological companies etc

Ranjit

Analytical databases often use data from operational databases

7/31/2019 01 Intro Dbms

15/63

15

WHATISA DATA MODEL?

A notation for describing data or information

Description consists of 3 parts: Structure of the data

Operations on the dataQueries

Modifications

Constraints on the data

7/31/2019 01 Intro Dbms

16/63

Slide 2-16

DATA MODELS

Data Model: A set of concepts to describe thestructureof a database,and certain constraints

that the database should obey.

Data Model Operations: Operations forspecifying database retrievals and updates by

referring to the concepts of the data model.

Operations on the data model may include

basic operationsand user-defined operations.

7/31/2019 01 Intro Dbms

17/63

Slide 2-17

HISTORYOF DATA MODELS

Hierarchical Data Model:

Implemented in a joint effort by IBM and North American Rockwellaround 1965. Resulted in the IMS family of systems. The mostpopular model. Other system based on this model: System 2k(SAS inc.)

Network Model:

the first one to be implemented by Honeywell in 1964-65 (IDSSystem). Adopted heavily due to the support by CODASYL(CODASYL - DBTG report of 1971). Later implemented in a largevariety of systems - IDMS (Cullinet - now CA), DMS 1100 (Unisys),IMAGE (H.P.), VAX -DBMS (Digital Equipment Corp.).

Relational Model: proposed in 1970 by E.F. Codd (IBM), first commercial system in

1981-82. Now in several commercial products (DB2, ORACLE,SQL Server, SYBASE, INFORMIX).

7/31/2019 01 Intro Dbms

18/63

Slide 2-18

HISTORYOF DATA MODELS

Object-oriented Data Model(s):

Several models have been proposed for implementing in adatabase system. One set comprises models of persistent

O-O Programming Languages such as C++

Object-Relational Models: Most Recent Trend. Started with Informix Universal Server.

Exemplified in the latest versions of Oracle-10i, DB2, and

SQL Server etc.

7/31/2019 01 Intro Dbms

19/63

THE HIERARCHICAL DATABASE MODEL

Data structured hierarchically as an inverted tree

A single table acts as the root of the inverted tree. Other tables act as branches.

Relationship is represented by parent/child.

Tables are explicitly linked via a pointer or physical arrangement ofrecords in the table.

Data is accessed starting from root and continuing down the tree.

7/31/2019 01 Intro Dbms

20/63

ADVANTAGES & DISADVANTAGES HIERARCHICAL

MODEL

Data retrieval is quick due to explicit links between

the table structures.

Referential integrity is inbuilt and automatically

enforced.

Data can not be entered in child table which is not

related to parent.

Can not support complex relations.

Representing many to many relation requires

redundant data. Which may lead to inconsistency.

To query this type of data , person must know the

complete structure.

7/31/2019 01 Intro Dbms

21/63

THENETWORK DATABASE MODEL

Addresses some of the problems of hierarchical model.

Structure is represented in terms of nodes and set structures.

Node represents a collection of records.

Set structure represents a relationship relating a pair of nodes

together by using one node as an owner and the other node asmember.

Supports one to many relationship.

Record in owner node can exist without having any record inthe member node.

User can access data from within the network while inHierarchical data access has to start from the root.

Supports complex queries.

7/31/2019 01 Intro Dbms

22/63

THENETWORK DATABASE MODEL

7/31/2019 01 Intro Dbms

23/63

ONETO MANY RELATIONSHIPIN NETWORK

MODEL

7/31/2019 01 Intro Dbms

24/63

THE RELATIONAL DATABASE MODEL

First conceived in 1969 by Dr Edgar F Codd

Based on set theory and first order predicate logic.

Stores data in relations known as tables.

Each relation is composed of tuples (records) andattributes (Fields)

Physical order of records is immaterial.

Each record in the table is identified by a field thatcontains unique value.

User need not know the physical structure of the databaseto retrieve values.

Categorises relationships as one to one , one to many andmany to many.

Data can be retrieved by SQL (structured querylanguage).

7/31/2019 01 Intro Dbms

25/63

THE RELATIONALDATABASE MODEL

7/31/2019 01 Intro Dbms

26/63

SOME TERMINOLOGY USEDIN RELATIONAL

MODEL

Relational DB is a collection of tables

Table is a collection of columns (attributes) that

describe an entity

Objects are stored as rows (tuples) within a table

Attribute or property is a characteristic or descriptor of

an entity

7/31/2019 01 Intro Dbms

27/63

NVSK

7/31/2019 01 Intro Dbms

28/63

FORMAL & INFORMALMAPPINGOF TERMINOLOGY

USEDIN RELATIONAL MODEL

Formal Relational Term Informal Equivalents

Relation,Entity Table

Tuple Row, Record

Cardinality No of rows

Attribute Column, Field

Degree No of Columns

Primary Key Unique Identifier

Domain Set of Legal Values

7/31/2019 01 Intro Dbms

29/63

ADVANTAGESOF RELATIONAL DATABASE

Built in Multilevel Integrity

Field Level

Record Level

Table Level

Primary key

Logical & Physical data independence

Data consistency & accuracy

Easy data retrieval

7/31/2019 01 Intro Dbms

30/63

RDBMS

A software program which is used to create ,maintain , modify and manipulate a relationaldatabase.

7/31/2019 01 Intro Dbms

31/63

TERMINOLOGYUSEDIN RDBMS

7/31/2019 01 Intro Dbms

32/63

TERMINOLOGYINRDBMS

Used to express & define the special ideas and

concepts of RDBMS

To express & define the database design process

itself. Four Categories

Value related

Structure related

Relationship related Integrity related

7/31/2019 01 Intro Dbms

33/63

DATA

Values you store in the database.

Static till modified

George Edleman 92883 05/16/96 95.00

7/31/2019 01 Intro Dbms

34/63

INFORMATION

Processed data to make it more meaningful

Data is what you store and information is what you retrieve

7/31/2019 01 Intro Dbms

35/63

NULL

Null represents a missing or unknown value.

Zero & Blank (Are they Null)

7/31/2019 01 Intro Dbms

36/63

NULL

Any mathematical computation with NULL will

always be NULL

(25 x 3) + 4 = 79

(Null x 3) + 4 = Null

(25 x Null) + 4 = Null

(25 x 3) + Null = Null

7/31/2019 01 Intro Dbms

37/63

EFFECTOF NULL ON OUTPUT

7/31/2019 01 Intro Dbms

38/63

STRUCTURE RELATED TERMS

7/31/2019 01 Intro Dbms

39/63

TABLE

Collection of similar records .

7/31/2019 01 Intro Dbms

40/63

TABLE

Table can represent Object or event

Object representation means thins which are tangible

like person , place or thing.

Object characteristics can be stored in tables.

In case table represents an event , it represents

something that occurs at a given point in time having

characteristics which you wish to store.

7/31/2019 01 Intro Dbms

41/63

VALIDATION TABLE

Also known as look up tables.

Generally represents subjects such as city names ,

skill categories , product codes etc.

7/31/2019 01 Intro Dbms

42/63

FIELD

Fields are the structures that actually store data.

It is the smallest structure in the table which represents

characteristics of the subject.

Contains one and only one value.

Types

Multipart (composite field) containing two or more distinct

values.

Multivalued contains multiple instances of the same type.

Calculated field

7/31/2019 01 Intro Dbms

43/63

EXAMPLE : TYPESOF FIELDS

7/31/2019 01 Intro Dbms

44/63

RECORD

Also known as tuple.

Represents a unique instance of the subject of a

table.

Each record is identified by the unique value in the

primary key.

VIEW

7/31/2019 01 Intro Dbms

45/63

VIEW

A view is a virtual table composed of fields from one

or more tables in the database.

Access refer them as saved queries.

7/31/2019 01 Intro Dbms

46/63

IMPORTANCEOF VIEWS

Allow you to work with data from multiple tables

simultaneously ( relationship is must for this

purpose)

Enable you to prevent certain users from viewing or

manipulating data in specific fields within a table.

Can be used for data integrity. Such views aare

known as data validation views.

7/31/2019 01 Intro Dbms

47/63

KEYS

Keys are special fields that play very specific role within a

table.

Type of key determines the purpose.

Most common : Primary Key & Foreign Key

Primary key is a field or group of field that uniquely

identifies each record within a table.

Primary key which is composed of two or more fields is

known as composite primary key.

Helpful in enforcing table level data integrity and helps

establishing relationships with other tables.

Every table must have a primary key.

7/31/2019 01 Intro Dbms

48/63

PRIMARY & FOREIGN KEY

7/31/2019 01 Intro Dbms

49/63

INDEX

An index is a structure RDBMS provides to improve

data processing.

Can be based on any type of field.

Basically reorganization of records based on certainfield or set of fields.

7/31/2019 01 Intro Dbms

50/63

RELATIONSHIP RELATED TERMS

7/31/2019 01 Intro Dbms

51/63

RELATIONSHIP

A relationship exists between two tables if records

of one table cane associated with records of the

other table.

Enables to create multiple views.

Helps reduce redundant data and eliminate

duplicate data.

Relationship can be established via a set of primary

and foreign keys.

Relationship can also be established through a

third table which is known as linking table or

associate table.

7/31/2019 01 Intro Dbms

52/63

RELATIONSHIPVIAPRIMARY & FOREIGN KEYS

7/31/2019 01 Intro Dbms

53/63

RELATIONSHIPVIAAN ASSOCIATETABLE

7/31/2019 01 Intro Dbms

54/63

TYPESOF RELATIONSHIPS

One to One Relationship

One to Many Relationship

Many to many Relationship

7/31/2019 01 Intro Dbms

55/63

ONETO ONE RELATIONSHIP

One table serves as a parent and the other as a child

Here both tables share the same primary key

O

7/31/2019 01 Intro Dbms

56/63

ONETO MANY RELATIONSHIP

Relationship is established by primary & foreign keys

One record in parent relates to many records in the child

while single record in the child can be related to only one

record in the parent

M M R

7/31/2019 01 Intro Dbms

57/63

MANYTO MANY RELATIONSHIP

Unresolved many to many relationship

How to relate these tables : No field seems to be common

M M R

7/31/2019 01 Intro Dbms

58/63

MANYTO MANY RELATIONSHIP

Linking table is used to establish such relationship

Linking table is formed by taking the primary keys of both

tables and forming a new table.

7/31/2019 01 Intro Dbms

59/63

PARTICIPATIONIN A RELATIONSHIP

Type of Participation

Compulsory or Optional

Whether a record in the table A can exist without having a

corresponding record in the table B

Degree of Participation Degree of participation is established between table A &

table B by indicating a minimum & maximum no of

records in table B that can be linked to a single record in

A.

Example if child table can have a maximum of 10 and atleast one record linked to parent then degree of

participation id written as 1,10.

7/31/2019 01 Intro Dbms

60/63

INTEGRITY RELATED TERMS

7/31/2019 01 Intro Dbms

61/63

FIELD SPECIFICATION

A field specification (domain) represents all the

elements of a field. Each field specification has

three types of elements

General, Physical , Logical

General field name , description , parent table

Physical :

Data type , Length , & Display format

Logical

Required value , range of Values , & Default Value

7/31/2019 01 Intro Dbms

62/63

DATA INTEGRITY

Refers to validity , consistency and accuracy of the

database . Four types. Table level integrity , Field level , Relationship level , Business

rule level

Table Level ( Entity Integrity)

No duplicate records and field that identifies each record

unique & never NULL.

Field Level ( Domain Integrity)

Valid , consistent & accurate values in each field.

Relationship Level ( Referential Integrity)

Ensures records in the table are synchronised whenever

insert , modify & delete operations are performed

Business Related

Depending upon business rules data base design may

change which also includes integrity constraints..

7/31/2019 01 Intro Dbms

63/63

NVSK