Relational Database Management Systems

TVSN Murthy Assistant Professor – DFT NIFT Hyderabad

Disclaimer: These slides are just for the purpose of easy reading and are not comprehensive in nature. Thus the slides have to be read together with the class lectures, reading material, and statutes dealing with the subject

RDBMS/Slide 1

Introduction
Flat files - 1960s - 1980s Hierarchical – 1970s - 1990s Network – 1970s - 1990s Relational – 1980s – present Object-oriented – 1990s – present Object-relational – 1990s – present Data warehousing – 1980s – present Web-enabled – 1990s - present

RDBMS/Slide 2

1

Introduction

Data Models Classification
Data models can be classified as: 1. 2. 3. File-based systems Traditional Data Models Semantic Data Models

File-based Systems: Entities or objects of interest are represented by records that are stored together in files. Relationships between objects are represented by using directories of various kinds.

RDBMS/Slide 3

Introduction

Data Models Classification
Traditional Data Models: Traditional data models are the Hierarchical, Network and Relational Models. Hierarchical Model:- Evolved from the file based system Network Model:- Is a superset of the Hierarchical model. Relational Model:- Is based on the mathematical concept of relation. Semantic Data Models: This class of data models was influenced by the semantic networks developed by artificial intelligence researchers. Semantic networks were developed to organize and represent general knowledge. These models are able to express greater interdependencies among entities.

RDBMS/Slide 4

2

Introduction
Common characteristics required for data models: A data model must show some degree of conceptual simplicity without compromising the semantic completeness. A data model must represent the real world as closely as possible. The representation of the real-world transformations (behavior) must be in compliance with the consistency and integrity characteristics of any data model.

RDBMS/Slide 5

Introduction
An organization must have accurate and reliable data for effective decision making. To this end organization maintains records on the various facets of its operations. These models capture the essential properties of the objects and record relationships among them. Such related data is called a database.

A database system is an integrated collection of related files, along with details of the interpretation of the data contained therein.

A database management system is a software system that allows access to data contained in a database.

RDBMS/Slide 6

3

Introduction
Three Level Architecture The architecture is divided into three levels: the external level, the conceptual level and the internal level The view at each of these levels is described by a scheme. A scheme is an outline or a plan that describes the records and relationships existing in the view. The word scheme, which means a systematic plan for attaining some goal is used interchangeably in the database literature with the word schema.

RDBMS/Slide 7

Three levels of architecture of a DBMS

External Level

VIEW A

VIEW B

VIEW C

Mapping supplied by DBMS

Mapping supplied by DBMS

Conceptual Level

CONCEPTUAL VIEW

Mapping supplied by DBMS/OS

Mapping supplied by DBMS/OS

Internal Level

INTERNAL VIEW

RDBMS/Slide 8

4

Three Level Architecture
External View

External view is at the highest level of abstraction where only those portions of the database of concern to a user or application program are included.

Each view is described by means of scheme called an external schema.

Schema consists of the definition of the logical records and the relationships in the external view.

RDBMS/Slide 9

Three Level Architecture
Conceptual View or Global View At this level of database abstraction all the database entries and the relationships among them are included. One conceptual view represents entire database.

It is defined by the conceptual schema.

Schema describes all the records and relationships included in the conceptual view

There is only one conceptual schema per database.

RDBMS/Slide 10

5

Three Level Architecture
Internal View Internal view is at the lowest level of abstraction, closet to the physical storage method used. It indicates how the data will be stored and describes the data structures and access methods View is expressed by the internal schema. Schema contains the definition of stored record, the method of representing the data fields, and access aids used.

RDBMS/Slide 11

Data Independence
Data Independence Three levels of abstraction, along with the mappings from internal to conceptual and from conceptual to external, provide two distinct levels of data independence: Logical Data Independence Physical Data Independence

RDBMS/Slide 12

6

Data Independence
Logical Data Independence: It indicates that the conceptual schema can be changed without affecting the existing external schemas. The change would be absorbed by the mapping between the external and conceptual levels. Logical data independence also insulates application programs from operations such as combining two records into one or splitting an existing record into two or more records. Is achieved by providing the external level or user view of the database.

RDBMS/Slide 13

Data Independence
Physical Data Independence It indicates that the physical storage structures or devices used for storing the data could be changed without necessitating a change in the conceptual view or any of the external views. The changed would be absorbed by the mapping between the conceptual and internal levels. It is achieved by the presence of the internal level of the database and the mapping or transformation from the conceptual level of the database to the internal level.

RDBMS/Slide 14

7

Database Users
DBMS Users The users of the database system can be classified in the following groups depending on their degree of expertise or mode of their interactions with the DBMS Naïve Users Online Users Application Programmers Database Administrator

RDBMS/Slide 15

Database Users
Naïve User User who need not be aware of the presence of the database system or any other system supporting their usage are considered naïve users. User of the ATM falls in this category

RDBMS/Slide 16

8

Database Users
Online Users These are users who may communicate with the database directly via online terminal or indirectly via a user interface and application program. These users are aware of the presence of the database system and may have acquired a certain amount of expertise in the limited interaction they are permitted with the database through the intermediary of the application program. Online users can also be naïve users requiring additional help, such as menus.

RDBMS/Slide 17

Database Users
Application Programmers Professional programmers who are responsible for developing application program or user interfaces utilized by the naïve and online users fall into this category. The application programs could be written in a general purpose programming language such as C or VB and include the commands required to manipulate the database.

RDBMS/Slide 18

9

Database Users
Database Administrator Centralized control of the database is exerted by a person under the supervision of a high level administrator. This person or group is referred to as the database administrator (DBA). The DBA administers the three levels of the database and in consultation with the overall community sets up the definition of the global view or conceptual level of the database. DBA specifies the external view of the various users and applications and is responsible for the definition and implementation of the internal level including the storage structure and access methods to be used for the optimum performance of the DBMS.

RDBMS/Slide 19

Database Users
Database Administrator (Contd.,) DBA is responsible for granting permission to the users of the database and stores the profile of each user in the database. DBA is also responsible for defining the procedures to recover the database from failures due to human, natural, or hardware causes with minimal loss of data.

RDBMS/Slide 20

10

Data Models
Data Models
Can be classified into two categories: Object-based logical model – focuses on describing the data, the relationship among the data, and any constraints defined Record-based logical model – focuses on describing the data structure and the access techniques in the Database Management System

RDBMS/Slide 21

Data Models
Object-based Logical Model
There are various object-based models. The most widely used is the entityrelationship model (E/R model)

RDBMS/Slide 22

11

Data Models
The Entity-relationship Model
Introduced by Peter Chen Chen introduced not only the E/R model, but also a corresponding diagramming technique

It is one of the most widely accepted graphical data modeling tools.

It graphically represents data as entities and their relationships in a database structure.

It complements the relational data model concepts.

E-R models are normally represented in an entity relationship diagram (ERD).

RDBMS/Slide 23

Entity Relationship Model
Entities
Chen defined an entity as “a thing, which can be easily identified” An entity is any object, place, person, or activity about which data is recorded In the diagramming technique, entities are named and represented inside a box An entity type is a set of things that share common properties STUDENT, COURSE, and GRADE are examples of entity type An entity type is usually in uppercase

RDBMS/Slide 24

12

Entity Relationship Model
Entities (Contd.)
Types of Entities Dependent entity Is an entity whose existence depends on the existence of another entity and are also called weak entities Independent entity Is an entity which does not depend on any other entity for existence and are also called regular entities

RDBMS/Slide 25

Entity Relationship Model
Relationships
Chen defines a relationship as “an association among entities”. For example, the relationship between students and instructors represents the fact that an instructor teaches several students and a student is taught by several instructors. This relationship could be named TEACH. Relationships are depicted as a diamond with the name of the relationship type.

RDBMS/Slide 26

13

Entity Relationship Model
Relationships (Contd.)
A relationship type is an association of entity types (STUDENT-INSTRUCTOR). A relationship can associate an entity with itself. Multiple relationships can also exist between the same entities.

RDBMS/Slide 27

Entity Relationship Model
Just a Minute…
1. The following statement has been extracted from a case presented by a manufacturer regarding the maintenance of their data: “A supplier ships certain parts.” Identify the entities mentioned in this statement, and their relationship. Draw a diagram depicting the relationship.

Supplier

Ships

Parts

Entities Relationship
RDBMS/Slide 28

14

Types of Relationships
Types of Relationships
There are three types of relationships: One-to-One One-to-Many (or Many-to-One) Many-to-Many

RDBMS/Slide 29

Types of Relationships
One-to-One Relationship
Consider the example of a university. For one DEPARTMENT (like the department of social sciences) there can be only one department head. This is a one-to-one relationship.

RDBMS/Slide 30

15

Types of Relationships
Many-to-One Relationship
A STUDENT can MAJOR in only one course, but many STUDENTs would have registered for a given MAJOR course. This is a many-to-one relationship.

RDBMS/Slide 31

Types of Relationships
Many-to-Many Relationship
An employee might learn many job skills, and each job skill might be learned by many employees.

m

m

EMPLOYEE

LEARNS

SKILL

RDBMS/Slide 32

16

Types of Relationships
Just a Minute…
1. What do the following E/R diagrams represent?

RDBMS/Slide 33

Types of Relationships

Relationship Depiction: The ERD

RDBMS/Slide 34

17

Types of Relationships

Relationship Depiction: The Crow’s Foot

RDBMS/Slide 35

Data Base Models
Entity-Relationship Data Model
Advantages Exceptional conceptual simplicity Visual representation Effective communication tool Integrated with the relational database model Disadvantages Limited constraint representation Limited relationship representation No data manipulation language Loss of information content

RDBMS/Slide 36

18

Data Base Models
Just a Minute… (Contd.)
2. Consider the following statement of a manufacturing company: “A supplier ships certain parts. A particular part is not necessarily shipped by only one supplier. No supplier ships only a single part.” What type of relationship is this? Draw a diagram to depict the relationship.

RDBMS/Slide 37

Data Base Models
Attributes
Attributes are a property of a given entity Attributes are depicted as ellipses, labeled with the name of the property

RDBMS/Slide 38

19

Data Base Models
Just a Minute…
1. A manufacturer needs to maintain the following details about the supplier: a. b. c. d. Name Address Credit Status Assigned code number

Draw a diagram to show this information.

RDBMS/Slide 39

Data Base Models
Just a Minute…
1. There are two types of suppliers. One type of supplier allows credit, while the other type insists on payment in cash before delivery. The manufacturer wishes to maintain separate information on these two types of suppliers. For the credit supplier, “credit period” and “credit limit” have to be recorded. For the cash supplier, “date of payment” has to be stored. Represent this diagrammatically.

RDBMS/Slide 40

20

Data Base Models
Record-based Logical Model
The three types of record-based models are: Hierarchical model In a hierarchical model, data is represented in the form of a tree Network model A network model is similar to a hierarchical model in the way that data and the relationships among them are represented in the form of records and links Relational model In the relational model, the database is structured in fixed-format records of several types

RDBMS/Slide 41

Data Base Models
Hierarchical Database Model
Basic Structure Collection of records logically organized to conform to the upsidedown tree (hierarchical) structure. The top layer is perceived as the parent of the segment directly beneath it. The segments below other segments are the children of the segment above them. A tree structure is represented as a hierarchical path on the computer’s storage media.

RDBMS/Slide 42

21

Data Base Models

A Hierarchical Structure

RDBMS/Slide 43

Data Base Models
Hierarchical Database Model
Advantages Conceptual simplicity Database security Data independence Database integrity Efficiency dealing with a large database Disadvantages Complex implementation Difficult to manage Lacks structural independence Applications programming and use complexity Implementation limitations Lack of standards
RDBMS/Slide 44

22

Data Base Models
Network Database Model The popularity of the network data model coincided with the popularity of the hierarchical data model. Some data were more naturally modeled with more than one parent per child. The network model permitted the modeling of many-to-many relationships in data.
Basic Structure Set -- A relationship is called a set. Each set is composed of at least two record types: an owner (parent) record and a member (child) record. A member record type can have that role in more than one set, hence the multiparent concept is supported. An owner record type can also be a member or owner in another set.

RDBMS/Slide 45

Data Base Models

RDBMS/Slide 46

23

Data Base Models
Network Database Model
Advantages Conceptual simplicity Handles more relationship types Data access flexibility Promotes database integrity Data independence Conformance to standards Disadvantages System complexity Lack of structural independence

RDBMS/Slide 47

Data Base Models
Relational Model
Dr.E. F. Codd first described the relational model in 1970 Relational model is an attempt to simplify the database structure It represents all data in the database as simple tables in the row-column format

RDBMS/Slide 48

24

Data Base Models
RDBMS
RDBMS can be defined as a database management system where all data visible to the user is organized strictly as tables of data values and where all database operations work on these tables

RDBMS/Slide 49

Data Base Models
Relational Data Structure
The organizing principle in a relational database is the table, a tabular arrangement of data values A table is called a relation The row (or record) in the table is called a tuple The column (or field) is called an attribute The number of tuples is called the cardinality, and the number of attributes is called the degree of the table Rows are unordered and each row must have some columns or a combination of columns that uniquely identifies each row, called the primary key of the table

RDBMS/Slide 50

25

Data Base Models
Relational Data Structure (Contd.)
A domain is a pool of values from which one or more attributes (columns) draw their actual values

RDBMS/Slide 51

Data Base Models
Representing Missing Information
Missing or unknown information is represented as NULL in a relational system NULL is not the same as space or zero

RDBMS/Slide 52

26

Data Base Models
Representing Relationships in an RDBMS
At any given time, no two rows of the table contain the same values in a column or column combination. This column (or columns) is called the primary key of the table. A column in one table whose value matches the primary key in some other table is called a foreign key Together, a primary key and a foreign key create a parent-child relationship between the tables that connects them

RDBMS/Slide 53

Data Base Models
Relational Database Model
Advantages Structural independence Improved conceptual simplicity Easier database design, implementation, management, and use Ad hoc query capability (SQL) Powerful database management system Disadvantages Substantial hardware and system software overhead Possibility of poor design and implementation Potential “islands of information” problems

RDBMS/Slide 54

27

Data Base Models
Just a Minute… Self Study Collect the information on Serial and Sequential files a. How are they created b. How records are maintained and manipulated c. Advantages and Disadvantages if any

RDBMS/Slide 55

Data Base Models
Subtypes and Supertypes
Super type: Is connected to the relationship with a line containing a crossbar. Is described by attributes that belong to all subtypes. The subtype is described by the attributes that are unique to it. Subtype: Is a subset of another entity. It is always dependent on the super type for its existence. Is connected to the super type by an unnamed relationship.

RDBMS/Slide 56

28

Data Base Models
Relational Operators
The relational model is based on the principle of relational algebra It is a collection of operators operating on relations. Each operator takes one or two relations as its input and produces a new relation as its output. Relational Operators are of the following types: RESTRICT: Extracts specified tuples or rows from a given relation, based on a condition. PROJECT: Extracts specified attributes or columns from a given relation.

RDBMS/Slide 57

Data Base Models
Relational Operators (Contd.,)
ID 101 103 104 107 110 112 NAME Jones Smith James Evan Drew Smith Projection NAME Jones Smith James Evan Drew Smith Projection of P over attribute “Name”

Relation P

RDBMS/Slide 58

29

Data Base Models
Relational Operators (Contd.)
PRODUCT: Builds a relation from two specified relations. It consists of all possible combinations of tuples, one from each of the two relations. UNION: Builds a relation from tuples appearing in either or both of the specified relations. To be union compatible, the two tables should have the same types of attributes. INTERSECT: Builds a relation consisting of tuples that appear in both relations. DIFFERENCE: Builds a relation of tuples appearing in the first but not the second of two specified relations.

Next

RDBMS/Slide 59

Data Base Models
Relational Operators (Contd.,)

ID ID 101 103 104 106 107 NAME Jones Smith James Byron Evan Software Packages J1 J2 Relation Q Cartesian Product 101 101 103 103 104 104 106 106 107 107

NAME Jones Jones Smith Smith James James Byron Byron Evan Evan

S J1 J2 J1 J2 J1 J2 J1 J2 J1 J2

Relation P

Relation P x Q

Back

RDBMS/Slide 60

30

Data Base Models
Relational Operators (Contd.,)
ID 101 Union 103 104 106 107 Relation Q 110 112 NAME Jones Smith James Byron Evan Drew Smith

ID 101 103 104 107 110 112

NAME Jones Smith James Evan Drew Smith

ID 103 104 106 110

NAME Smith James Byron Drew

Relation P

Relation P U Q

Back

RDBMS/Slide 61

Data Base Models
Relational Operators (Contd.,)

ID 101 103 104 107 110 112

NAME Jones Smith James Evan Drew Smith

ID 103 104 106 110

NAME Smith James Byron Drew Intersection ID 103 104 110 NAME Smith James Drew Q

Relation P Relation Q

Relation P

Back

RDBMS/Slide 62

31

Data Base Models
Relational Operators (Contd.,)

ID 101 103 104 107 110 112

NAME Jones Smith James Evan Drew Smith

ID 103 104 106 110

NAME Smith James Byron Drew Difference ID 101 107 112 NAME Jones Evan Smith

Relation P - Q Relation Q

Relation P

Back

RDBMS/Slide 63

Data Base Models
Relational Operators (Contd.)
JOIN: Join operator as the name suggests, allows the combining of two relations to forma single new relation. The tuples from the operand relations that participate in the operation and contribute to the result are related. DIVIDE: The Divide operator takes two relations and builds another relation consisting of values of an attribute of one relation that match all the values in the other relation.

RDBMS/Slide 64

32

Data Base Models
Relational Operators (Contd.)

ID 101 103 104 107

NAME Jones Smith James Evan

ID 101 103 104 107

SALARY 67 55 75 80 Join

ID 101 103 104 107

NAME Jones Smith James Evan

SALARY 67 55 75 80

Relation P

Relation Q

RDBMS/Slide 65

Data Base Models
Applications of an RDBMS
Some typical applications of an RDBMS are: Airline and railway reservations Banking applications Manufacturing industry Order processing Hospital management systems Library management systems Hotel industry

RDBMS/Slide 66

33

Data Base Models
Applications of an RDBMS (Contd.)
Several RDBMS products are available today. Some popular products are: Sybase Oracle Microsoft SQL Server Ingress DB2

RDBMS/Slide 67

Summary
In this lesson, you learned that: Data models can be classified as: Object-based models Record-based models In the entity-relationship diagramming technique: Entities are represented as rectangles Relationships are represented as diamonds Attributes are represented as ellipses Relationships, whether many-to-many, one-to-many, or one-to-one, are represented symbolically

RDBMS/Slide 68

34

Summary
(Contd.,) Weak entities are represented in double-lined boxes Subtypes are connected to the supertype by an unnamed relationship, marked with a crossbar on top In the relational model, data is represented in tables (relations) of rows (tuples) and columns (attributes) The number of tuples is called the cardinality of the relation, and the number of attributes is called the degree of the relation An attribute (or set of attributes) that is unique in every tuple is called the primary key

RDBMS/Slide 69

Summary
(Contd.,) Unknown or missing information is represented by a NULL in a table The foreign key is a column in one table that matches the primary key of another table The relational model is based on the principle of relational algebra The eight operators that operate on relations are restrict, project, product, union, intersect, difference, join, and divide

RDBMS/Slide 70

35

Relational Model

Keys are special fields that serve two main purposes: Primary keys are unique identifiers of the relation in question. Examples include employee numbers, social security numbers, etc. This is how we can guarantee that all rows are unique Foreign keys are identifiers that enable a dependent relation (on the many side of a relationship) to refer to its parent relation (on the one side of the relationship) Keys can be simple (a single field) or composite (more than one field)

RDBMS/Slide 71

Relational Model

Primary Key Foreign Key
(implements 1:N relationship between customer and order)

Combined, these are a composite primary key (uniquely identifies the order line)…individually they are foreign keys (implement M:N relationship between order and product)

RDBMS/Slide 72

36

Relational Model

Integrity Constraints (Rules) Integrity Rule 1:
ID 101 103 104 107 110 112 NAME Jones Smith James Evan Drew Smith ID 101 103 104 107 110 112 NAME Jones Smith James Evan Drew Smith Phone 123456 @ 635214 @ 568742 214796

A null value for an attribute is a value that is either not known at the time or doest not apply to a given instance of the object. It may also be possible that a particular tuple does not have a value for an attribute.

RDBMS/Slide 73

Relational Model

Integrity Rule 1 (Contd.,): If any attribute of primary key were permitted to have null value, the key cannot be used for identification of tuples. This contradicts the requirements for a primary key. If null values were permitted then the two tuples are indistinguishable even though they may represent two different instances of entity type employee. Integrity Rule 1 specifies that instances of the entities are distinguishable and thus no prime attribute value may be null. This rule is also referred to as the Entity Rule.

RDBMS/Slide 74

37

Relational Model

Integrity Rule 2: Integrity rule 2 is concerned with foreign keys ie., with attributes of a relation having domains that are those of the primary key of another relation. A relation (R) may contain references to another relation (S). Relation R and S need not be distinct. Suppose the reference in R is via a set of attributes that forms a primary key of the relation S. This set of attributes in R is a foreign key. The referencing attribute(s) in the relation R can have null value(s); in this case, it is not referencing any tuple in the relation S. If the value is not null, it must exist as the primary attribute of a tuple of the relation S.

Integrity Rule 2 specifies that, Given two relations R and S, suppose R refers to the relation S via a set of attributes that forms the primary key of S and this set of attributes forms a foreign key in R. Then the value of the foreign key in a tuple in R must either be equal to the primary key of a tuple of S or be entirely null. This rule is also referred to as the Referential Integrity Rule.

RDBMS/Slide 75

Relational Model

Relational Calculus: Tuple and Domain calculus are collectively referred to as relational calculus. Relational Calculus is a query system wherein queries are expressed as variables and formulas on these variables. Such formulas the properties of the required result without specifying the method of evaluating it. Relational calculus which means calculating with relations, is based on predicate calculus which is calculating with predicates. Predicate calculus is a formal language used to symbolize logical arguments in mathematics. Formal logic the main subject matter is propositions from which we can build other propositions. In predicate calculus, propositions may be built not only out of other propositions but also out of elements that are not themselves propositions. Propositions specifying a property consist of an expression that means an individual object and another expression called the predicate, that stands for the property that the individual object possesses.

RDBMS/Slide 76

38