database management system

1

UNIT-II

UNIT-I

WELCOME TO THIS COURSE – DATABASE MANAGEMENT SYSTEM

UNIT-III

DATABASE MANAGEMENT SYSTEM

DATA MODELING

RELATIONAL DATABASE MANAGEMENT SYSTEMSRELATIONAL DATABASE MANAGEMENT SYSTEMS AND NORMALISATION

DATABASE DESIGN AND ENTITY-RELATIONSHIP MODELLING

SQL: QUERIES AND SUBQUERIESDATABASE SECURITY AND INTEGRITYDATABASE SECURITY AND INTEGRITY

2


UNIT I

3

CHAPTER I


4

Database Management System

• A collection of programs that enables to store, modify, and extract information from a database.

• There are many different types of DBMSs, ranging from small systems that run on personal computers to huge systems that run on mainframes.

Database Management

SystemStoring of data

Modification of data

Extraction of data

Updating data

UNIT I DATABASE MANAGEMENT SYSTEM

5

Process of Database


6

Database OrganisationDatabase Organisation defines how and where the data are organised in physical storage.

Phases of Database Organisation• External schema: Defines views of the database for individual user.• Conceptual schema : Describes the stored data structure, data types,

relationships, operations and constraints.• Internal schema : Defines how and where the data are organised in

physical storage.

Development

Design

Analyze


7

Major Components of Database

Basic Components Description Commands used

DDL (Data Definition Language )

It is used to create and destroy databases and database objects.

• CREATE DATABASE (To create a database): CREATE DATABASE <database-name> ;

• DROP DATABASE (To removes the database) : DROP DATABASE <database-name> ;

• CREATE TABLE (To create tables) : CREATE TABLE <table-name> ( ...);

• DROP TABLE (removes the table) : DROP TABLE <table-name> ;

• ALTER TABLE (To ‘alter’ tables after the creation of table) :

ALTER TABLE <table-name> ADD <field-name> <data-type>

ALTER TABLE <table-name> DROP <field-name>

ALTER TABLE <table-name> MODIFY <field-name> <new-field-

declaration>


8

Basic Components

Description Commands used

DML (Data Modified Language )

Modification of data like insert, select and update data.

• INSERT Statement (To get data into a database):

INSERT INTO <table-name> (<column1>,<column2>,<column3>,...)

VALUES (<column-value1>,<column-value2>,<column-value3>);

• SELECT Statement (To select the statement in the database): SELECT <column-list>

FROM <table-list> WHERE <search-condition>• UPDATE Statement (To update statement):

UPDATE <table-name> SET <column1> = <value1>, <column2> = <value2>, ...

WHERE <criteria>• DELETE Statement (To delete the statement):

DELETE FROM <table-name> WHERE <criteria>

Major Components of Database


9

Advantages and Disadvantages of DBMS

Advantages• Redundancies and inconsistencies can be reduced • Better service to the Users • Flexibility of the system is improved• Cost of developing and maintaining systems is lower• Security can be improved• Enterprise requirements can be identified • Data model must be developed

Disadvantages • Increased Complexity and size• Specialised manpower and increased installation and

management cost• Need for explicit backup and recovery• Confidentiality, Privacy and Security


10

CHAPTER II

DATA MODELING

11

Types of Data Model

High level or

conceptual

Ex: E-R Model

Implementation Data Model

Hierarchical Data Model

Network Data Model

Relational Data Model

Object – Based Data Model

Semi structured Data Model

Physical Data Model

Explain how data stored on disk and what access methods are

available

Introduction of Data Modeling• Data modeling is used for representing entities of interest and their

relationships in the database.• Provide the way of structuring the data• Allow a set of manipulative operations like updating and retrieving the

data from the database

UNIT I DATA MODELING

12

Conceptual Data Model• It describes the information used by an organisation.• The main advantage of Conceptual data model is that it is independent of

implementation details and understood by non technical background end-user.

Implementation Data Model• Implementation Data Model hide some data storage details from the user.

Some of the data models: Hierarchial data model:

i. It is a tree like structure having both child and parent node.ii. Each child node can have only one parent node.iii. It comprises of a set of records connected to one another through links.

Network data model:i. Data is represented by a collections of records, relationship among data are represented by links.ii. All nodes are link to each other without any hierarchy.iii. It is organised in the form of graphs.iv. It is quite complicated to handle.


13

Relational data model:i. It does not contain any physical link.ii. All data is maintained in the form of tables.

Object based data model:i. It is the extension of relational data model.ii. It combines the feature of both relational model and object oriented data model

Physical Data Model:• Physical Data Model describes the data in terms of collection of files, indices

and other storage structure.• It describes how the data is stored in disk and what access methods are

available


14


UNIT II

15

CHAPTER III

INTRODUCTION TO RELATIONAL DATABASE

MANAGEMENT SYSTEMS

16

Relational Database Management System• The data in RDBMS is stored in database objects called tables.• The database tables are the primary data storage for every RDBMS.• MS SQL Server, DB2, Oracle and MySQL are all Relational Database

Management Systems.

The Relational Data StructureData structures are composed of two components:• ENTITY TYPES- i.e. data group types:

Represented by relations and base table. A base table is loosely defined as an un-ordered collection of zero, one or

more tuples (rows). Each row of a table is uniquely identified by a PRIMARY KEY composed of

one or more columns. Superkey, primary key and candidate key are also applicable to the

relational model.

UNIT IIINTRODUCTION TO RELATIONAL DATABASE

MANAGEMENT SYSTEMS

17

Key

• A set of attributes constituting a key is a property of the relational

schema.

• A key is determined from the meaning of the attributes and the

property.

• A column, or group of columns, that uniquely identifies a row in a table

is called a CANDIDATE KEY.

• The Candidate Key cannot contain NULL value and should always

contain a unique value.

• Primary key is the candidate key in the relation whose values are used

to identify tuples.


MANAGEMENT SYSTEMS

18

Constraints• Constraints are logic rules that are used to ensure data consistency.• Major integrity constraints: Domain constraints: Value of each attribute must be atomic from the

domain.The major components of domain constrains:a. Domain nameb. Meaningc. Data typed. Size or length

Entity integrity constraints:a. It states that no part of a primary key field can contain NULL value.b. The primary key value is used to identify individual tuples in a relation.c. Entity integrity constraints are specified on the individual relations.

Referential integrity constraints:a. Concept of relationships between tables, based on the definition of a primary key and a foreign key.b. Referential integrity is a mechanism which prevents accidental database corruptions when doing inserts, updates, and deletes.c. Referential constraint provide security to the database.

Operational constraints: These constraints are used in an organisation for business rules and policies.


MANAGEMENT SYSTEMS

19

Codd’s 12 Rules

Rule 1 The information rule

Rule 2 The guaranteed access rule

Rule 3 Systematic treatment of null values

Rule 4 Active online catalogue based on the relational model

Rule 5 The comprehensive data sub-language rule

Rule 6 The view-updating rule

Rule7 High-level insert, update, and delete

Rule 8 Physical data independence


MANAGEMENT SYSTEMS

20

Rule 8 The information rule

Rule 9 Logical data independence

Rule 10 Integrity independence

Rule 11 Distribution independence

Rule 12 The no subversion rule

Codd’s 12 Rules


MANAGEMENT SYSTEMS

21

CHAPTER IV

RELATIONAL DATABASE MANAGEMENT SYSTEMS

AND NORMALISATION

22

Relational Database Design

• The main objective of logical data model is to create an accurate representation of data.

• The Normalization technique is used to achieve the objective.• Four commonly used normal forms:

First Normalization (1NF) Second Normalization (2NF) Third Normalization (3NF)

• The main aim of relational database design is to group attributes into relation to minimize the redundancy.

UNIT IIRELATIONAL DATABASE MANAGEMENT SYSTEMS AND

NORMALISATION

Process of Normalization

Un-Normalized Normal Form (UNF)

First Normal Form (1NF)

Second Normal Form (2NF)

Third Normal Form (3NF)

23

Unnormalized Normal Form (UNF)

• A table that contains one or more repeating groups To create an unnormalized table Transform the data from the information source (e.g. form) into table

format with columns and rows.

Example: Un-normalized Student Table


NORMALISATION

24

The requirements to satisfy the 1st NF:

• Each table has a primary key: minimal set of attributes which can uniquely identify a record

• The values in each column of a table are atomic (No multi-value attributes allowed).

• There are no repeating groups: two columns do not store similar information in the same table.

Example: Normalized Student Table


NORMALISATION

25

The requirements to satisfy the 2nd NF:

• All requirements for 1st NF must be met.• Redundant data across multiple rows of a table must be moved to a

separate table.• The resulting tables must be related to each other by use of foreign key.

Student Table

Registration Table


NORMALISATION

26

The requirements to satisfy the 3rd NF:

• All requirements for 2nd NF must be met.• Eliminate fields that do not depend on the primary key;

That is, any field that is dependent not only on the primary key but also on another field must be moved to another table.

Registration Table

Student Table

Advisor Table


NORMALISATION

27

• BCNF is based on the concept of a determinant.• A determinant is any attribute (simple or composite) on which some other

attribute is fully functionally dependent.• A relation is in BCNF is, and only if, every determinant is a candidate key.

BCNF (Boyce-Codd Normal Form)

Here, the first determinant suggests that the primary key of R could be changed from a,b to a,c. If this change was done all of the non-key attributes present in R could still be determined, and therefore this change is legal. However, the second determinant indicates that a,d determines b, but a,d could not be the key of R as a,d does not determine all of the non key attributes of R (it does not determine c).

Consider the following relation and determinants.

R(a,b,c,d) a,c -> b,d a,d -> b


NORMALISATION

28

CHAPTER V

DATABASE DESIGN AND ENTITY-RELATIONSHIP

MODELLING

29

Design Process

Data to bestored

Determine relationship

between different Data

Superimpose a logical structure upon the data

Determining the group of

information

Determine the relationship between the

groups

UNIT IIDATABASE DESIGN AND ENTITY-RELATIONSHIP

MODELLING

30

The Entity-Relationship Model• Entity-relationship model is a data modeling method used to model a system• An entity-relationship model (ERM) is a representation of structured data and

entity-relationship• The end-product of the modeling process is an entity-relationship diagram

(ERD)• The E-R (entity-relationship) data model views the real world as a set of basic

objects• The first stage of information system design uses these models during the

requirements analysis.• An information system that is based on a database, the conceptual data

model is, mapped to a logical data model, such as the relational modelNotations of E-R Diagram

Symbol Meaning

Entity

Weak Entity

Relationship


MODELLING

31

Components of an E-R ModelMajor components of E-r Model:

People

Lives in

Names


MODELLING

32

Entities and Entity Sets• An entity may be concrete (a person or a book, for example) or abstract (like a

holiday or a concept). • Entity sets need not be disjoint. For example, the entity set employee (all

employees of a bank).• An entity has a name and is represented by a set of attributes. E.g. name,

Cust_Id, street, city for “customer” entity.• The domain of the attribute is the set of permitted values

AttributesEvery entity is described by a set of (attribute, data value) pairs. Types of attributes:• Simple and Composite Attributes: Attribute which is composed of a single

component with an independent existence is called the simple attribute. • Single valued and multi-valued attribute: The attributes that hold a single

value for a single entity are single valued.Ex: an enrolment number attribute for the particular student may refer to only one enrolment number

• Null Attributes : A null value is used when an entity does not have a value for an attribute.

• Derived attributes: The value for this type of attribute can be derived from the values of other related attributes or entities.


MODELLING

33

Sample E-R Diagram

Entity-Relationship Diagram

Definition: An entity-relationship (ER) diagram is a specialized graphic that illustrates the interrelationships between entities in database.


MODELLING

34

Terms Associated with Entities: Degree• The degree of a relationship is the number of entities that participate in the

relationship.• The degree is of three types:

Unary Relationships Binary Relationships Ternary relationship Quaternary relationship

Cardinalities • Cardinality expresses the number of entities to which another entity can be

associated via a relationship set.• A cardinality is expressed with the help an crowsfoot symbol ( ) in ERD. • The cardinality relationship must be one of the following points:

One-to-one One-to-many Many-to-one Many-to-many


MODELLING

35


UNIT III

36

CHAPTER VI

SQL: QUERIES AND SUBQUERIES

37

Integer It represents a signed integer, decimal or binary.

Smallint It represents a signed integer, decimal or binary.SMALLINT must not exceed that of INT.

Double Precision Double-precision floating point number.

DATE Stores year, month and day values.

Time Store the hour, minute and second values.

Timestamp Stores year, month, day, hour, minute, and second values

SQL Data Types

UNIT III SQL: QUERIES AND SUBQUERIES

38

Clauses in SQL

SQL Data Query Language (DQL): DQL has only one data query statement whose syntax is SELECT.

SyntaxSELECT [ DISTINCT | ALL ]column_expression1, column_expression2, ....[ FROM from_clause ][ WHERE where_expression ][ GROUP BY expression1, expression2, .... ][ HAVING having_expression ][ ORDER BY order_column_expr1, order_column_expr2, .... ]

Example SELECT name FROM s WHERE city=’Rome’ Where "s" is the name of the table Supplier Location

Pierre Paris

John London

Mario Rome

List of suppliers


39

The FROM ClauseThe FROM clause always follows the SELECT clause.

Example SELECT * FROM sHere the entire table s is selected.SELECT supplier.name FROM s supplierHere only the supplier name is selected.

ORDER BY ClauseIt must be the last clause in the SELECT statement. The ORDER BY clause defines the ordering of rows based on columns from the SELECT clause.

The ORDER BY clause has the following general format:• ORDER BY column-1 [ASC|DESC] [column-2 [ASC|DESC]]...• ORDER BY sorts rows using the ordering columns in left-to-right, major-to-

minor order.


40

Types of FunctionsBasics types of functions:Aggregate FunctionsScalar Functions

Function Description

AVG (column) Returns the average value of a column

COUNTS (Column)Returns the number of row (without a NULL value) of a column.

COUNT(*) Returns the number of selected rows

MAX(column) Returns the highest value of a column

MIN(column) Returns the lowest value of a column

SUM(column) Returns the total sum of a column

Aggregate Functions


41

Scalar Functions

Scalar functions operate against a single value, and return a single value based on the input value. The built in functions:

TRIM([LEADING|TRAILING|BOTH] [FROM] exp-1)TRIM([LEADING|TRAILING|BOTH] exp-2 FROM exp-1)Trims leading, trailing or both characters from a string - exp-1.

SUBSTRING (exp-1 FROM exp-2 [FOR exp-3])Extracts a substring from a string - exp-1, beginning at the integer value exp-2, for the length of the integer value - exp-3

Data Definition Language (DDL)1. The DDL provides commands for defining relation schemas, deleting relation

schemas and modifying relation schemas2. The syntax of the commands are CREATE, ALTER and DROP

Syntax for CREATE Table CREATE TABLE "table_name"("column 1" "data_type_for_column_1","column 2" "data_type_for_column_2",... )


42

CHAPTER VII

DATABASE SECURITY AND INTEGRITY

43

Introduction to Database Security• Database security is the system, processes, and procedures that protect a

database from unintended activity. • Database Security is a specialty within the much broader area of computer

security.

Database Security ThreatsDatabases need to have level of security in order to protect the database against both malicious and accidental threats.Factors that drive the need for security• Theft and fraud• Confidentiality• Integrity• Privacy• Database availability

UNIT III DATABASE SECURITY AND INTEGRITY

44

Data Tampering• Data tampering is the deliberate destruction or manipulation of data. • The tampering may or may not be detected until some time in the future. • The tampering may or may not be detected until some time in the future. • Beyond controlling access, sensitive data should be encrypted using hashes

and digital signatures.

Data Theft and Eavesdropping• Some popular methods are Ecommerce, Password cracking and

eavesdropping. • In eavesdropping the data sent on insecure lines can be wiretapped and

recorded. Password-Related Threats• They may also choose to standardize passwords so that they are the same on

all machines or web sites.• Users with complex passwords may write them down where an attacker can

easily find them .• They may also choose to standardize passwords so that they are the same on

all machines or web sites.


45

Data Security Requirements

Vulnerability Assessments• A vulnerability assessment attempts to find vulnerability holes that could

be used to break into the database.• Information security administrators run vulnerability scans on databases

to discover mis-configuration of controls.• Database objects may include table or other objects listed in

the table link. • Vulnerability assessment is a preliminary procedure to

determine risk where a compliance program is the process of on-going risk assessment.

Monitoring of Database Protocol Traffic (SQL)• Security layer includes the real-time monitoring of database

protocol traffic (SQL) over the network.• When a network level audit system is not feasible a native

database audit program should be instituted. • A Database Security program should include the regular

review of permissions granted to individually.


46

Authentication• Authentication is the act of verifying a claim of identity.• Strong authentication requires providing information from two of the three

different types of authentication information.

Integrity• Integrity means that data should be protected from deletion and corruption.• Referential Integrity is the ability to maintain valid relationships between

values in the database.

AvailabilityThere are number of aspects in system availability:• Resistance: User profiles must be in place to define and limit the

resources any given user may consume• Scalability: System performance must remain adequate regardless

of the number of users or processes • Flexibility: Administrators must have adequate means of

managing the user population• Ease-of-use: Availability of valid user to get the work done


47

CHAPTER VIII

DATABASE SECURITY AND INTEGRITY

48

Introduction• The function of DBMS are supported by Operating System to provide

basic services.• The physical data and system catalog are stored on a physical disk.

Execution Steps of Operating System

User issue a query

Passing the query to the query optimizer and the DBMS accepts the user

DBMS produces the query evolution plan

DBMS executes the plan

UNIT III

STRUCTURE, COMPONENTS, FUNCTIONS AND IMPLEMENTATION OF DATABASE MANAGEMENT SYSTEM

49

Components of DBMSMajor software modules or components of DBMS are as follows:• Query processor• Run time database manager• DML processor• DDL processor

Query Processor• The query processor transforms user queries into series of low level

instructions.• It is used to interpret the online user query and convert it into an efficient

query. • Query processor use the data dictionary to find the structure of relevant

portion. Runtime Database Manager• It handles database during runtime.• The run time data manager then places a call to the physical database to

perform the request.• Major components of runtime database managers are authorisation control,

command processor and query optimizer.

UNIT III


50

DML Processor• DML processor converts the DML statements into standard function calls in

the host language.• The DML compiler converts the DML statements written in host

programming language into object code for database access.

DDL Processor• The DDL processor converts the DDL statements into a set of tables

containing metadata.• These tables are then stored in the system catalog while control

information is stored in data file headers.• The DDL compiler processes schema definitions, specified in the ddl and

stores description of the schema.• The system catalog includes information such as the names of data files,

data items, storage details.

UNIT III


51

Functions of DBMS• Data Storage Management• Data Manipulation Management• Data Definition Services• Data Dictionary/System Catalog Management• Database Communication Interfaces• Backup and Recovery Management• Data Independence Services• Transaction Management

Types of DBMS

Types of DBMS

Centralised DBMS Distributed DBMS Client/Server DBMS

UNIT III


52

Centralized Database SystemThe centralised database system consists of a single processor together with its associated data storage and other peripherals.

Distributed Database Systems• A distributed database is a database that is under the control of a central

database management system (DBMS) in which storage devices are not all attached to a common CPU.

• It may be stored in multiple computers located in the same physical location, or may be dispersed over a network of interconnected computers.

UNIT III


53

Client/Server Database System• In client/server architecture the client is generally a desktop PC whereas the

server can be a large workstation.• The applications and tools of the DBMS run on one or more client platforms.• The server computer is called a backend and the client’s computer is called

the frontend.

UNIT III


database management system

Documents