Chapter 1

Introduction and Data Modeling

using ER Diagram

7/19/2018 10:01 AM BY Ms. B.K.Ugale, CSE Dept, RIT. 1

 Contents
• Introduction to Database Systems • Database System Architecture

• Advantages and Applications • Database Users and Administrators

• View of Database • ER Model

• Data Models • Keys

• Database Languages • Extended Features of ER Model

7/19/2018 10:01 AM BY Ms. B.K.Ugale, CSE Dept, RIT. 2

 Introduction to Database System
• What is Data?

• What is Information?

• What is Knowledge?

• What is Database?

• What is Database Management System?

7/19/2018 10:01 AM BY Ms. B.K.Ugale, CSE Dept, RIT. 3

 Disadvantages of File System??
• Data Redundancy
• Data Inconsistency
• Difficulty in Accessing Data
• Data Isolation
• Limited Data Sharing
• Integrity Problems
• Atomicity Problems
• Concurrent Access Anomalies
• Security Problems
7/19/2018 10:01 AM BY Ms. B.K.Ugale, CSE Dept, RIT. 4
 Advantages of Database System??

7/19/2018 10:01 AM BY Ms. B.K.Ugale, CSE Dept, RIT. 5

 Applications
• Banking: all transactions

• Airlines: reservations, schedules

• Universities: registration, grades

• Sales: customers, products, purchases

• Online retailers: order tracking, customized recommendations

• Manufacturing: production, inventory, orders, supply chain

• Human resources: employee records, salaries, tax deductions

7/19/2018 10:01 AM BY Ms. B.K.Ugale, CSE Dept, RIT. 6
 View of Data

• Data abstraction

• Instances and Schemas

7/19/2018 10:01 AM BY Ms. B.K.Ugale, CSE Dept, RIT. 7

 Data Abstraction
■ Physical level: describes how a record (e.g., customer) is stored.
■ Logical level: describes data stored in database, and the relationships among the
data.
• type customer = record
• customer_id : string; customer_name : string;
customer_street : string; customer_city :
integer;
• end
■ View level: A way to hide: (a) details of data types and (b) information (such as an
employee’s salary) for security purposes.

7/19/2018 10:01 AM BY Ms. B.K.Ugale, CSE Dept, RIT. 8

 View of Data

View Level

Logical Level

Physical Level

7/19/2018 10:01 AM BY Ms. B.K.Ugale, CSE Dept, RIT. 9

 Instances and Schemas
■ Similar to types and variables in programming languages
■ Schema – the logical structure of the database
• Example: The database consists of information about a set of customers and accounts and the relationship
between them)
• Analogous to type information of a variable in a program
• Physical schema: database design at the physical level
• Logical schema: database design at the logical level
■ Instance – the actual content of the database at a particular point in time
• Analogous to the value of a variable
■ Physical Data Independence – the ability to modify the physical schema without changing the logical schema
• Applications depend on the logical schema
• In general, the interfaces between the various levels and components should be well defined so that changes
in some parts do not seriously influence others
7/19/2018 10:01 AM BY Ms. B.K.Ugale, CSE Dept, RIT. 10
 Data Models
■ A collection of tools for describing
• Data
• Data relationships
• Data semantics
• Data constraints
■ Relational model
■ Entity-Relationship data model (mainly for database design)
■ Object-based data models (Object-oriented and Object-relational)
■ Semi structured data model (XML)
■ Other older models:
• Network model
• Hierarchical model
7/19/2018 10:01 AM BY Ms. B.K.Ugale, CSE Dept, RIT. 11
 Relational Model-Example

7/19/2018 10:01 AM BY Ms. B.K.Ugale, CSE Dept, RIT. 12

 ER Diagram- Example

7/19/2018 10:01 AM BY Ms. B.K.Ugale, CSE Dept, RIT. 13

 Database Languages

• Data Definition Language

• Data Manipulation Language

• Database Sublanguages:
– Data Control Language

– Transaction Control Language

7/19/2018 10:01 AM BY Ms. B.K.Ugale, CSE Dept, RIT. 14

 Data Definition Language
■ Specification notation for defining the database schema
Example: create table account ( account-number char(10),
balance integer )
■ DDL compiler generates a set of tables stored in a data dictionary
■ Data dictionary contains metadata (i.e., data about data)
•
Database schema
• Integrity constraints
Domain constraints
Referential integrity (references constraint in SQL)
Assertions
■ Data storage and definition language
• Specifies the storage structure and access methods used
7/19/2018 10:01 AM BY Ms. B.K.Ugale, CSE Dept, RIT. 15
 Data Manipulation Language
■ Language for accessing and manipulating the data organized by the
appropriate data model
• DML also known as query language
■ Two classes of languages
• Procedural – user specifies what data is required and how to get
those data
• Declarative (nonprocedural) – user specifies what data is
required without specifying how to get those data
■ SQL is the most widely used query language
7/19/2018 10:01 AM BY Ms. B.K.Ugale, CSE Dept, RIT. 16
 DML- Example
• SELECT * FROM student_details;

• INSERT INTO employee (id, name, dept, age, salary) VALUES (105,
'Srinath', 'Aeronautics', 27, 33000);

• UPDATE employee SET location ='Mysore‘ WHERE id = 101;

• DELETE FROM employee WHERE id = 100;

7/19/2018 10:01 AM BY Ms. B.K.Ugale, CSE Dept, RIT. 17

 Data Control Language

• DCL is used for granting and revoking user access on a

database –

• GRANT: To grant access to user

• REVOKE: To revoke access from user

7/19/2018 10:01 AM BY Ms. B.K.Ugale, CSE Dept, RIT. 18

 Transaction Control Language
• Transaction Control statements are used to run the changes made by DML
statements. It allows statements to be grouped together into logical transactions.

▪ COMMIT - It saves the work done

▪ SAVEPOINT - It identifies a point in a transaction to which you can later roll back

▪ ROLLBACK - It restores database to original since the last COMMIT

▪ SET TRANSACTION - It changes the transaction options and what rollback

segment to use

7/19/2018 10:01 AM BY Ms. B.K.Ugale, CSE Dept, RIT. 19

 Entity- Relationship Model

7/19/2018 10:01 AM BY Ms. B.K.Ugale, CSE Dept, RIT. 20

 What is ER Model
▪ ER model is used to show the Conceptual schema of an organisation.

▪ Independent of specific data model or DBMS

▪ The model is later transformed into a Logical model (e.g. relational) on which the physical database is
built

▪ ER Model perceives the real world as consisting of basic objects called as entities and relationship
among these entities.

7/19/2018 10:01 AM BY Ms. B.K.Ugale, CSE Dept, RIT. 21

 Entity
• An entity is an object that exists and is distinguishable from other
objects.
– Example: specific person, company, event, plant

• Entities have attributes- Properties of entity

– Example: people have names and addresses
Example:
Customer = (customer_id,
customer_name,
customer_street,
customer_city
)
Loan = (loan_number, amount )

7/19/2018 10:01 AM BY Ms. B.K.Ugale, CSE Dept, RIT. 22

 Entity Set and Domain
• An entity set is a set of entities of the same type that share the same
properties.
– Example: set of all persons, companies, trees, holidays
• Attributes have domain or value set- the set of permitted values for each attribute
– Example: domain of attribute loan_number might be the set of strings of the form
“L-n”

Entity Set

7/19/2018 10:01 AM BY Ms. B.K.Ugale, CSE Dept, RIT. 23

 (Domain)

Entity Set

7/19/2018 10:01 AM BY Ms. B.K.Ugale, CSE Dept, RIT. 24

 Types of Attributes

▪ Simple attribute

▪ Composite attribute

▪ Derived attribute

▪ Single-value attribute

▪ Multi-value attribute

7/19/2018 10:01 AM BY Ms. B.K.Ugale, CSE Dept, RIT. 25

 Types of Keys
▪ Super Key
▪ Candidate Key
▪ Primary Key
▪ Alternate Key
▪ Composite Key
▪ Foreign Key
▪ Unique Key

7/19/2018 10:01 AM BY Ms. B.K.Ugale, CSE Dept, RIT. 26

 Super Key
▪ An attribute or set of attributes that are required to identifies a
tuple in a relation.
▪ A Super Key may contain additional attributes that are not
require for unique identification.
▪ Unique Key-Student_ID
▪ Super Key:
▪ {Student_ ID, Student_Name}
▪ {Studenr_ID, Student_Address}
7/19/2018 10:01 AM BY Ms. B.K.Ugale, CSE Dept, RIT. 27
 Candidate Key
▪ Candidate Keys are minimal set of Super Key

7/19/2018 10:01 AM BY Ms. B.K.Ugale, CSE Dept, RIT. 28

 Primary Key
▪ Primary Key is a candidate key chosen by database administrator for
unique identification.
▪ No Two rows can have the primary key same values.
▪ Every row must have primary key value
▪ The primary key value can not be null
▪ Value in primary key can not be modified.
▪ Primary Key: PAN No.

7/19/2018 10:01 AM BY Ms. B.K.Ugale, CSE Dept, RIT. 29

 Alternate Key
• Candidate keys other than primary key are called as a alternate
key.
• PK: PAN No.
• Alternate Key: Voter Id No, Passport No

7/19/2018 10:01 AM BY Ms. B.K.Ugale, CSE Dept, RIT. 30

 Composite Key
• Composite key is the one having combination of more than one attribute
to uniquely determine the records/tuples in a table.
• The only condition that is necessary before choosing composite key is,
the attributes which are responsible for forming the composite key must
not be a key individually.

7/19/2018 10:01 AM BY Ms. B.K.Ugale, CSE Dept, RIT. 31

 Foreign Key
• A foreign key is nothing but an attribute that is commonly linked between two
relation using that same attribute and that attribute must be primary key from one
relation.
• Both the relations/tables must contain the same attribute.
• It can be referred as referential integrity

7/19/2018 10:01 AM BY Ms. B.K.Ugale, CSE Dept, RIT. 32

 Unique Key
▪ Unique key is same as primary key with the difference being
the existence of Null value.
▪ Unique Key: MemberID

7/19/2018 10:01 AM BY Ms. B.K.Ugale, CSE Dept, RIT. 33

7/19/2018 10:01 AM BY Ms. B.K.Ugale, CSE Dept, RIT. 34
 Relationship and Relationship Sets
• A relationship is “An association among entities (the participants)”
• Example:
– Harish depositor A-102
– customer entity relationship set account entity
• Relationship Set-
• A relationship set is a set of relationships of the same type.
• Formally it is a mathematical relation on n>=2 sets.
• If E1,E2,..En are entity sets, then a relationship set R is a subset of
{(e1,e2,..en) | e1ϵE1, e2ϵE2,..en ϵ En}
• where (e1,e2,..en) is a relationship.
• Example: (Harish, A-102) ∈ depositor

7/19/2018 10:01 AM BY Ms. B.K.Ugale, CSE Dept, RIT. 35

 Entity- Relationship Diagram

7/19/2018 10:01 AM BY Ms. B.K.Ugale, CSE Dept, RIT. 36

 Entity- Relationship Diagram
• ER Diagram express the overall logical structure of database graphically.
• ER diagram Components:

1. Rectangle : Entities are represented by means of rectangles. Rectangles

are named with the entity set they represent.

7/19/2018 10:01 AM BY Ms. B.K.Ugale, CSE Dept, RIT. 37

 ER Components
2. Ellipses: Attributes are represented by means of ellipses. Every ellipse
represents one attribute and is directly connected to its entity (rectangle).
3. Diamonds: Which represents relationship sets
4. Line: Which links attributes to entity sets and entity set to relationship set
5. Underline : Indicates Primary key of an entity

7/19/2018 10:01 AM BY Ms. B.K.Ugale, CSE Dept, RIT. 38

 ER Components
6. Double Ellipses: Multivalued attributes are depicted by double ellipse.
7. Dotted Ellipses : Derived attributes are depicted by dashed ellipse. If the
attributes are composite, they are further divided in a tree like structure.

7/19/2018 10:01 AM BY Ms. B.K.Ugale, CSE Dept, RIT. 39

 ER Components
8. Double Line: Total Participation of an entity in a relationship set

9. Double Rectangle : Represents weak entity set

10. Double Diamond: Identifying relationship set for weak entity set
11. Dotted Underline: Discriminating attribute of weak entity set

Attribute

9. Discriminating
Attribute
7/19/2018 10:01 AM BY Ms. B.K.Ugale, CSE Dept, RIT. 40
 Constraints

• An ER model may define certain constraints to which the

contents of a database must conform.

– Mapping Cardinalities

– Participation Constraints

7/19/2018 10:01 AM BY Ms. B.K.Ugale, CSE Dept, RIT. 41

 Mapping Cardinalities
• Mapping cardinalities or cardinality rations, express the number
of entities to which another entity can be associated via a
relationship set.
– One to One
– One to Many
– Many to One
– Many to Many

7/19/2018 10:01 AM BY Ms. B.K.Ugale, CSE Dept, RIT. 42

 One to One
• One entity from entity set A can be associated with at most one
entity of entity set B and vice versa.

7/19/2018 10:01 AM BY Ms. B.K.Ugale, CSE Dept, RIT. 43

 One to Many
• One entity from entity set A can be associated with more than
one entities of entity set B however an entity from entity set B,
can be associated with at most one entity.

7/19/2018 10:01 AM BY Ms. B.K.Ugale, CSE Dept, RIT. 44

 Many to One
• More than one entities from entity set A can be associated with at most
one entity of entity set B, however an entity from entity set B can be
associated with more than one entity from entity set A.

7/19/2018 10:01 AM BY Ms. B.K.Ugale, CSE Dept, RIT. 45

 Many to Many
• One entity from A can be associated with more than one entity from B
and vice versa.

7/19/2018 10:01 AM BY Ms. B.K.Ugale, CSE Dept, RIT. 46

 Participation Constraints
• Total Participation − Each entity is involved in the
relationship. Total participation is represented by double lines.
• Partial participation − Not all entities are involved in the
relationship. Partial participation is represented by single lines.

7/19/2018 10:01 AM BY Ms. B.K.Ugale, CSE Dept, RIT. 47

 Participation Constraints

7/19/2018 10:01 AM BY Ms. B.K.Ugale, CSE Dept, RIT. 48

 Roles
▪ Entity sets of a relationship need not be distinct
▪ The labels “manager” and “worker” are called roles; they specify how employee
entities interact via the works-for relationship set.
▪ Roles are indicated in E-R diagrams by labeling the lines that connect diamonds to
rectangles.
▪ Role labels are optional, and are used to clarify semantics of the relationship

7/19/2018 10:01 AM BY Ms. B.K.Ugale, CSE Dept, RIT. 49

 Descriptive Attribute
• Attributes of the relationship is called descriptive attribute

7/19/2018 10:01 AM BY Ms. B.K.Ugale, CSE Dept, RIT. 50

 Types of Relationship Set
• Unary Relation set:
• Degree of relationship set is 1

• Binary Relation Set:

• Degree of relationship set is 2

7/19/2018 10:01 AM BY Ms. B.K.Ugale, CSE Dept, RIT. 51

 Types of Relationship Set
• Ternary Relation set:
• Degree of relationship set is 3

• N ary Relation set:

• Degree of relationship set
is N

7/19/2018 10:01 AM BY Ms. B.K.Ugale, CSE Dept, RIT. 52

 Weak Entity Set
• An entity set that does not have a primary key is referred to as a weak entity set.
• The existence of a weak entity set depends on the existence of a identifying entity set or
Owner entity set.
• The relationship associating the weak entity set with the strong entity set is called as
identifying relationship.
• The discriminator (or partial key) of a weak entity set is the set of attributes that
distinguishes among all the entities of a weak entity set.
• The primary key of a weak entity set is formed by the primary key of the strong entity
set on which the weak entity set is existence dependent, plus the weak entity set’s
discriminator.

7/19/2018 10:01 AM BY Ms. B.K.Ugale, CSE Dept, RIT. 53

 Weak Entity Set

7/19/2018 10:01 AM BY Ms. B.K.Ugale, CSE Dept, RIT. 54

 ER Model Example

7/19/2018 10:01 AM BY Ms. B.K.Ugale, CSE Dept, RIT. 55

7/19/2018 10:01 AM BY Ms. B.K.Ugale, CSE Dept, RIT. 56
 Construction of ER diagram
• Step 1 : Identify the Entities

• Step 2 : Identify the relationships

• Step 3: Identify the key attributes

• Step 4: Identify other relevant attributes

• Step 5: Draw complete ER diagram

7/19/2018 10:01 AM BY Ms. B.K.Ugale, CSE Dept, RIT. 57

ER diagram for Car Insurance Company
• Construct an ER diagram for car insurance company that has a set of
customers each of whom owns one or more cars. Each car has associated
with it zero to any number of recorded accidents

• Entities
– Person
– Car
– Accident

7/19/2018 10:01 AM BY Ms. B.K.Ugale, CSE Dept, RIT. 58

 ER diagram for Car Insurance Company

7/19/2018 10:01 AM BY Ms. B.K.Ugale, CSE Dept, RIT. 59

 Extended ER Features

7/19/2018 10:01 AM BY Ms. B.K.Ugale, CSE Dept, RIT. 60

 Extended ER Features

▪ Generalization

▪ Specialization

▪ Aggregation

7/19/2018 10:01 AM BY Ms. B.K.Ugale, CSE Dept, RIT. 61

 Generalization

▪ Generalization is the process of extracting common properties

from a set of entities and create a generalized entity from it.

▪ It is a bottom-up approach in which two or more entities can be

generalized to a higher level entity if they have some attributes
in common.

7/19/2018 10:01 AM BY Ms. B.K.Ugale, CSE Dept, RIT. 62

 Generalization

Bottom-Up
Approach

7/19/2018 10:01 AM BY Ms. B.K.Ugale, CSE Dept, RIT. 63

 Specialization

▪ Specialization is opposite to Generalization.

▪ It is a top-down approach in which one higher level entity can

be broken down into two lower level entity.

▪ In specialization, a group of entities is divided into sub-groups

based on their characteristics.

7/19/2018 10:01 AM BY Ms. B.K.Ugale, CSE Dept, RIT. 64

 Specialization

Top Down
Approach

7/19/2018 10:01 AM BY Ms. B.K.Ugale, CSE Dept, RIT. 65

 Attribute Inheritance

7/19/2018 10:01 AM BY Ms. B.K.Ugale, CSE Dept, RIT. 66

 Aggregation
▪ One limitation of the E-R model is that it cannot express
relationships among relationships

7/19/2018 10:01 AM BY Ms. B.K.Ugale, CSE Dept, RIT. 67

 Aggregation

7/19/2018 10:01 AM BY Ms. B.K.Ugale, CSE Dept, RIT. 68

 Database System Architecture

7/19/2018 10:01 AM BY Ms. B.K.Ugale, CSE Dept, RIT. 69

 Database Users
• Users are differentiated by the way they expect to interact with the system

■ Naïve users – invoke one of the permanent application programs that have been

written previously

• Examples, people accessing database over the web, bank tellers, clerical staff

■ Application programmers – interact with system through DML calls

■ Sophisticated users – form requests in a database query language

■ Specialized users – write specialized database applications that do not fit into the

traditional data processing framework

7/19/2018 10:01 AM BY Ms. B.K.Ugale, CSE Dept, RIT. 70
 Database Administrator
■ Coordinates all the activities of the database system; the database administrator has a good understanding of the
enterprise’s information resources and needs.

■ Database administrator's duties include:

• Schema definition

• Storage structure and access method definition

• Schema and physical organization modification

• Granting user authority to access the database

• Specifying integrity constraints

• Monitoring performance and responding to changes in requirements

• Routine maintenance: Backup of Database, Ensuring free space is available or not.

7/19/2018 10:01 AM BY Ms. B.K.Ugale, CSE Dept, RIT. 71

 Storage Manager
• Storage manager is a program module that provides the interface between the low-
level data stored in the database and the application programs and queries submitted
to the system.
• The Storage Manager Component include:
– Authorization and integrity Manager
– Transaction Manager
– File Manager
– Buffer Manager
➢ Data Structures Managed by Storage Manager:
➢ Data File: Database
➢ Data Dictionary
➢ Indices

7/19/2018 10:01 AM BY Ms. B.K.Ugale, CSE Dept, RIT. 72

 Query Processor
▪ The Query Processor Components include:
▪ DDL interpreter
▪ Data Dictionary

▪ DML Compiler
▪ Translate DML in query language to evaluation plan

▪ Query Optimization

▪ Query Evaluation engine

▪ Executes low level instructions generated by DML Compiler
7/19/2018 10:01 AM BY Ms. B.K.Ugale, CSE Dept, RIT. 73
 Query Processor
1. Parsing and translation
2. Optimization
3. Evaluation

7/19/2018 10:01 AM BY Ms. B.K.Ugale, CSE Dept, RIT. 74

 Application Architecture

7/19/2018 10:01 AM BY Ms. B.K.Ugale, CSE Dept, RIT. 75

 ER-Example: College Database System
▪ A college contains many departments
▪ Each department can offer any number of courses
▪ Many instructors can work in a department
▪ An instructor can work only in one department
▪ For each department there is a Head
▪ An instructor can be head of only one department
▪ Each instructor can take any number of courses
▪ A course can be taken by only one instructor
▪ A student can enroll for any number of courses
▪ Each course can have any number of students
7/19/2018 10:01 AM BY Ms. B.K.Ugale, CSE Dept, RIT. 76
7/19/2018 10:01 AM BY Ms. B.K.Ugale, CSE Dept, RIT. 77
 ER-Example: Identify Possible relations with Attributes

7/19/2018 10:01 AM BY Ms. B.K.Ugale, CSE Dept, RIT. 78

 ER-Example: Hospital Database System

7/19/2018 10:01 AM BY Ms. B.K.Ugale, CSE Dept, RIT. 79

 ER-Example: Bus Timetable System

7/19/2018 10:01 AM BY Ms. B.K.Ugale, CSE Dept, RIT. 80