CO1

Session 1 - Intoduction to DBMS

Session 2 - Characteristics of the Database

Session 3, 4, 5 - ER Model

Session 6, 7 - Enhanced ER model

Session 8, 9 - Relation Models

CO1 1
 Session 1 - Intoduction to
DBMS
What is Database?
Any Collection of Related information

Your 5 best friends

Shopping List

Phone Book

To-Do list

Databases can be stored in different ways

On paper

On a computer

In your mind

Comment section

What is a Database Management System (DBMS)?

Database Management System (DBMS) is a special software program that helps
users create and maintain a database

Makes it easy to manage large amounts of information

Handles Security

Backups

4 Main operations we can be doing with database:

C. R. U. D Create, Read, Update, Delete

There are two approaches for storing data in computers:

1. File system

2. DBMS

The main difference between the file system and the DBMS is that the file system
helps to store a collection of raw data files into the hard disk while the DBMS helps

Session 1 - Intoduction to DBMS 1

 to easily store, retrieve and manipulate data in a database.
DBMS vs File system

File Based Approach

A filesystem is a method for storing and organizing computer files and the data
they contain to make it easy to find and access them.

File systems may use a storage device such as a hard disk or CD ROM and
involve maintaining the physical location of the files.

Programmers used programming languages such as COBOL, C++ to write

applications that directly accessed files to perform data management services
and provide information for users.

Problems in traditional approach

Data Security

Data Redundancy

Data Dependence

Lack of Flexibility

Concurrent Access Anomalies

Session 1 - Intoduction to DBMS 2

 Database Approach
Characteristics of database approach:
Some of the most important characteristics of the database approach to the file
processing approach are the following as follows.
Self-Describing Nature of a Database System:

A DBMS catalog stores the description of a particular database (e.g. data

structures, types, and constraints). The description is called meta-data.

This allows the DBMS software to work with different database applications.

Isolation between Programs and Data, and Data Abstraction:

Allows changing data structures and storage organization without having to

change the DBMS access programs

Called program-data independence.

Data Abstraction:

Is used to hide storage details and present the users with a conceptual view of
the database.

Programs refer to the data model constructs rather than data storage details

Support of multiple views of the data:

Each user may see a different view of the database, which describes only the
data of interest to that user

Sharing of data and multi-user transaction processing:

A multi-user DBMS, as its name implies, must allow multiple users to access the
database at an equivalent time or concurrently.

OLTP(Online Transaction Processing) is a major part of database applications.

This allows hundreds of concurrent transactions to execute per second.

Advantages of DBMS:

Incorrect information cannot be stored in database known as Integrity can be

enforced

Controlling Redundancy: Duplication of data is almost avoided but not

completely.

Session 1 - Intoduction to DBMS 3

 Inconsistency can be avoided: When the same data is duplicated and
changes are made at one site, which is not propagated to the other site, it gives
rise to inconsistency and the two entries regarding the same data will not agree

Faster data Access: The Data base management system (DBMS) helps to
produce quick answers to database queries thus making data accessing faster
and more accurate.

Simple: Data base management system (DBMS) gives simple and clear logical
view of data. Many operations like insertion, deletion or creation of file or data
are easy to implement.

Increased end-user productivity: The data which is available with the help of
combination of tools which transform data into useful information, helps end user
to make quick, informative and better decisions that can make difference
between success and failure in the global economy.

Disadvantages of DBMS:

Increased Cost

Cost of Hardware and Software This is the first disadvantage of the

database management system. This is because, for DBMS, it is mandatory
to have a high-speed processor and also a large memory size.

Complexity: all companies are using the database management system as it

fulfills lots of requirements and also solves the problem. But a problem arises,
that is all this functionality has made the database management system an
extremely complex software. For the proper requirement of DBMS, it is very
important to have a good knowledge of it by the developers, DBA, designers,
and also the end-users.

Frequency Upgrade/Replacement Cycles: Frequent upgrade of the products

is done by the DBMS vendors to add new functionality to the systems.

Complexity Size Performance.

Session 1 - Intoduction to DBMS 4

 Session 2 - Characteristics of
the Database
DBMS System environment

DBMS Functionality

database is defined in terms of its data types, structures, and constraints

initial database contents are loaded or constructed on a secondary storage

medium

We can manipulate the datebase like

Retrieval: Querying, generating reports

Modification: Insertions, deletions and updates to its content

Accessing the database through Web applications

Processing and Sharing by a set of concurrent users and application programs –

yet, keeping all data valid and consistent

Protection or Security measures to prevent unauthorized access

“Active” processing to take internal actions on data

Session 2 - Characteristics of the Database 1

 Presentation and Visualization of data

Example of a Database:

Characteristics of the Database:

Self-describing nature of a database system:

A DBMS catalog stores the description of a particular database (e.g. data

structures, types, and constraints)

The description is called meta-data. This allows the DBMS software to work
with different database applications.

Insulation between programs and data:

Called program-data independence. Allows changing data structures and

storage organization without having to change the DBMS access programs.

Session 2 - Characteristics of the Database 2

 Potential for enforcing standards:

This is very crucial for the success of database applications in large

organizations. Standards refer to data item names, display formats, screens,
report structures, meta-data (description of data), Web page layouts, etc.

Reduced application development time:

Incremental time to add each new application is reduced.

Flexibility to change data structures:

Database structure may evolve as new requirements are defined.

Availability of current information:

Extremely important for on-line transaction systems such as airline, hotel,

car reservations.

Economies of scale:

Wasteful overlap of resources and personnel can be avoided by

consolidating data and applications across departments.

How Database is Designed

Session 2 - Characteristics of the Database 3

 Centralized/Shared-Memory

Centralized databases: One to a few cores, shared memory

Client-server: One server machine executes work on behalf of multiple client

machines.
Parallel databases: Many-core shared memory, Shared disk, Shared nothing

Distributed databases Geographical distribution Schema/data heterogeneity

Database Applications: These are divided into 2 or 3 parts

Session 2 - Characteristics of the Database 4

 Two-tier architecture: the application resides at the client machine, where it invokes
database system functionality at the server machine

Three-tier architecture: the client machine acts as a front end and does not contain
any direct database calls.

The client end communicates with an application server, usually through a

forms interface. The application server in turn communicates with a database
system to access data.

Database Users:

they are different types of uses in Database (as shown below)

Session 2 - Characteristics of the Database 5

 Database Administrator User:
This user has central control over the system is called a database
administrator(DBA). Functions of a DBA include:

Schema definition

Storage structure and access-method definition

Schema and physical-organization modification

Granting of authorization for data access

Routine maintenance Periodically backing up the database

Ensuring that enough free disk space is available for normal operations, and
upgrading disk space as required

Monitoring jobs running on the database

Types Of DBMS Languages

Session 2 - Characteristics of the Database 6

 Four types of database management systems
Relational Database system

hierarchical database systems

network database systems

object-oriented database systems

Relational Databases stores data in the form of tables. This concept purposed by
Dr. E.F. Codd, a researcher of IBM in the year 1960 s

hierarchical databases: model resembles a tree structure, similar to a folder

architecture in your computer system. The relationships between records are pre-

Session 2 - Characteristics of the Database 7

 defined in a one-to-one manner, between 'parent and child nodes. They require the
user to pass a hierarchy in order to access needed data. Due to limitations, such
databases may be confined to specific uses.

network databases: models also have a hierarchical structure. However, instead of

using a single-parent tree hierarchy, this model supports many too many
relationships, as child tables can have more than one parent.

Session 2 - Characteristics of the Database 8

 object-oriented databases: the information is represented as objects, with different
types of relationships possible between two or more objects. Such databases use an
object-oriented programming language for development.

Session 2 - Characteristics of the Database 9

 Session 3, 4, 5 - ER Model
ER diagrams are one popular example for displaying database structures.
Reference WebLink
Reference WebLink 2
Reference WebLink 3

Entity: A Person, Place, Object, or an Organization.

Weak Entity: A weak entity is an entity set that does not have sufficient attributes
for Unique Identification of its records. The weak entity is represented by a
double rectangle. The relation between one strong and one weak entity is
represented by a double diamond.
Example for weak entity:

In the ER diagram, we have two entities building and apartment

building is a strong entity because it has a primary key attribute called

building number which is capable of uniquely identifying all the flats present
in the apartment

Unlike a building, the apartment is a weak entity because it does not have
any primary key and the door number here acts only as a
discriminator because the door number cannot be used as a primary key,
there might be multiple flats in the building with the same door number or on
different floors.

Session 3, 4, 5 - ER Model 1
 Note: Weak entity always has total participation but a Strong entity may not have
total participation.

Attributes(represented by an oval): Properties of an Entity.

Key Attribute(represented by an oval with underlying lines): The attribute

which uniquely identifies each entity. Ex: Roll No.

Composite Attribute(represented by an oval comprising of ovals): An

attribute composed of many other attributes. Ex: Here "Address" is a
Composite Attribute

Multivalued Attribute(represented by a double oval): An attribute consisting of

more than one value.

Derived Attribute(represented by a dashed oval): An attribute that can be

derived from other attributes. Ex: Age (can be derived from DOB Attribute).

Session 3, 4, 5 - ER Model 2
 Note: Entities Will be present as Rows, and Attributes will be acts as columns in a
DataBase.

Relationship(represented by a diamond): A relationship relates two or more

distinct entities with a specific meaning.
Recursive Relationship: If the same entity participates more than once in a
relationship

Degree of a relationship:

Unary Relationship: only ONE entity set participating

Binary Relationship: TWO entities set participating

Note: The possible Cardinality ratios for binary relationship types are 1: 1,
1: N, N: 1, and M: N.

1. One to One Relationship(1: 1): For example, a person has only one
passport and a passport is given to one person.

Session 3, 4, 5 - ER Model 3
 2. One to Many Relationship(1: N): For example, a customer can place
many orders but a particular order cannot be placed by many customers.

Example 2:-

3. Many to One Relationship(N: 1): For example, many students can

study in a single college but a student cannot study in many colleges at
the same time.

4. Many to Many Relationship(M: N): For example, a student can be

assigned to many projects and a project can be assigned to many
students.

Example 2:-

Session 3, 4, 5 - ER Model 4
 Ternary Relationship: THREE entities set participating

n-ary Relationship: n entities set participating

Entity Sets, Relation sets

Min, Max notation for relationship constraints: (Participation Constraints)

Example:

Session 3, 4, 5 - ER Model 5
 Let's take a real-world example, Organisation stated conditions as follows:

Every Project need at least 3 employees and a max of 15 employees

Every employee may not participate in any of the projects but maximum
participation of 2 projects

Participation Constraints:

Total Participation: Each entity is involved in the relationship. Total participation

is represented by double lines.

i.e, Every element in the entity set is linked to at least one element of
another entity

Partial participation: Not all entities are involved in the relationship. Partial
participation is represented by single lines.

i.e, Every element in the entity set is not linked to at least one element of
another entity

In the below diagram, every student is mapped to some subject but not every subject
is mapped to any student like physics.

Session 3, 4, 5 - ER Model 6
Session 3, 4, 5 - ER Model 7
 Session 6, 7 - Enhanced ER
model
Reference Link
Reference PDF

Q. Why EER Models?

To reduce this complexity of modeling we have to make improvements or

enhancements were made to the existing ER model to make it able to handle the
complex application in a better way.

Enhanced entity-relationship diagrams are advanced database diagrams very

similar to regular ER diagrams

It is a diagrammatic technique for displaying the Sub Class and Super Class;
Specialization and Generalization; Union or Category; Aggregation etc.

Basic Outline of EER model:

Example of EER diagram:

Session 6, 7 - Enhanced ER model 1

 Here Vehicle is a Superclass or Supertype.

Car, Scotter, Truck are Sub-classes or Subtype or Subgroup.

Top to bottom approach is specialization

i.e, Specialization is the process of defining a set of subclasses of a

superclass

The above image can be taken as Specialization Example.

The set of subclasses is based upon some characteristics of the entities in

the superclass Example: {Car, Scooter, Truck} is a specialization of Vehicle,
based upon the type of vehicle.

Attributes of a subclass are called specific or local attributes.

The subclass can also participate in specific relationship types.

Bottom to top approach is Generalization

i.e, Generalization is the reverse of the specialization process (the process

of generalizing several entity types into a single superclass).

Example:

Session 6, 7 - Enhanced ER model 2

 Car, Truck become subclasses of the superclass VEHICLE.

We can view {Car, Truck} as a specialization of VEHICLE Alternatively,

we can view VEHICLE as a generalization of Car and Truck.

Arrow pointing to the generalized superclass represents a generalization.

Arrows pointing to the specialized subclasses represent a specialization.

We do not use this notation because it is often subjective as to which

process is more appropriate for a particular situation. We advocate not
drawing any arrows.

Inheritance: the concept that subtype entities inherit the values of all supertype
attributes.

Example 2:

Note: Where to use "o" and where to use "d"

In the first example, we have used "d" because no car can be a truck or a
scooter.

But in the second example, we have used "o" because an actor can be a
politician at the same time.

This is known as Disjoint Constrain

And also there is another type "u", which is used when generalizing things.

Session 6, 7 - Enhanced ER model 3

 Sets Representation:

Overlap "o":

Disjoint "d":

Union "u":

Session 6, 7 - Enhanced ER model 4

 Aggregation is Simply selecting more than one entity as a single entity and giving a
relation to another entity like shown above. As manager manages both job and
employee.

Example Question:

EER Diagram for the above question:

Session 6, 7 - Enhanced ER model 5

 Constrain/Conditions while doing Generalization and Specialization:

There are three constraints that may apply to a specialization/generalization:

1. Membership constraints.

a. Predicate Defined Subclasses(Condition Defined Subclasses):

We can determine to which subclass an entity belongs by

placing a condition on some attribute in the superclass.

We call this attribute “Defining Attribute”

Session 6, 7 - Enhanced ER model 6

 Job-type is an attribute of superclass “Employee”, based on the
value
of this attribute, we can determine if an employee belongs to
“Secretary”, “Technician”, or “Engineer”. So we can say JobType as
Defining Attribute.

b. User-Defined Subclasses: The main difference here is there is no Defining

Attribute, so the user Himself needs to define the subclass.

2. Disjoint constraints.

3. Completeness constraints.

a. Partial completeness is when a super-type does not need to use one of the
subtypes.

example 1:

Session 6, 7 - Enhanced ER model 7

 example 2: an employee can be a student or teacher, but doesn't have
to be.

b. Whereas total completeness super-type must use at least one of the

subtypes.

example

A student must be either a graduate or an undergrad.

And hence, we have four types of specialization/generalization:

1. Disjoint, total

2. Disjoint, partial

3. Overlapping, total

4. Overlapping, partial

Session 6, 7 - Enhanced ER model 8

 Examples:

Overlapping Total:

Session 6, 7 - Enhanced ER model 9

 Hierarchies and Lattices:
Hierarchies:

In the hierarchy,

There is one superclass for each subclass (One superclass/subclass

connection)

There is only one level of superclass/subclass connections.

Example

Lattices:

If we have a subclass that has multiple parents (superclasses), then the

hierarchy is called a “lattice”.

Multiple superclass/subclass connections for the same subclass.

Having more than one superclass for the same subclass is also called “Multiple
Inheritance”.

Session 6, 7 - Enhanced ER model 10

 Shared Subclass: A subclass with more than one superclass.

A shared subclass is a subclass in:

more than one distinct superclass/subclass relationships

each relationship has a single superclass

shared subclass leads to multiple inheritances

In the above example, Engineering_Manager is a Shared Subclass

Category or Union Type:

Category is a subset of the union of its superclasses.

Example:

Session 6, 7 - Enhanced ER model 11

 Represents a single superclass/subclass relationship with more than one
superclass

Subclass represents a collection of objects that is a subset of the UNION of

distinct entity types

Attribute inheritance works more selectively

Category can be total or partial

Leaf Node: A class that has no subclasses of its own.

Session 6, 7 - Enhanced ER model 12

 Session 8, 9 - Relation Models
What is the Relational Model?
Relational Model represents how data is stored in Relational Databases. A relational
database stores data in the form of relations (tables). Consider a relation STUDENT
with attributes ROLL_NO, NAME, ADDRESS, PHONE, and AGE shown in Table 1.

Informal Definitions:

Relation → Is a Table

A Relation is a set of tuples (rows)

Tuples → Nothing but Rows

Each row represent certain facts that correspond to a real-world entity or
relationship

Attribute name → 1st Column (Column Header)

Column header that gives an indication of the meaning of the data items in that
column

Key (Primary Key) → Like Key-Attribute in ER diagram

Each row has a value of a data item (or set of items) that uniquely identifies that
row in the table. In the below example, i.e, STUDENT table, SSN is the key.

Session 8, 9 - Relation Models 1

 Example:

Formal Definitions:

Artificial key or Surrogate key: An artificial key is an extra attribute added to

the table that is seen by the user. It does not exist in the external reality but can
be verified for syntax or check digits inside itself.

The Schema (or description) of a Relation:

Denoted by R(A1, A2, .....An)

R is the name of the relation

The attributes of the relation are A1, A2, ..., An

Each Attribute has a Domain(Set of values)

For example, the Domain of Student Phone number is 10 Digits

Example: CUSTOMER (Cust-id, Cust-name, Address, Phone#)

CUSTOMER is the relation name

Defined over the four attributes: Cust-id, Cust-name, Address, Phone#

Tuple:

Tuple can be represented as an ordered set of values (enclosed in angled

brackets ‘<..>’)

Each value is derived from an appropriate domain.

For example, row in the CUSTOMER relation is a 4-tuple and would

consist of four values

Session 8, 9 - Relation Models 2

 <632895, "John Smith", "101 Main St. Atlanta, GA 30332", "(404)
894-2000">

This is called a 4-tuple as it has 4 values

Domain:

The domain is all possible Column values

For example dom(Gender) = { Male, Females }

Domain has a Format Defined for it, For example, USA_phone_numbers

may have a format: (ddd)ddd-dddd where each d is a decimal digit.

The logical definition can be given as “USA_phone_numbers” are the

set of 10 digit phone numbers valid in the U.S.

Dates have various formats such as year, month, date formatted as yyyy-
mm-dd, or as dd mm,yyyy, etc.

The attribute name designates the role played by a domain in a relation

Example: The domain Date may be used to define two attributes named
“Invoice-date” and “Payment-date” with different meanings

Relation (or relation state)

In an Equation, Relation is something defined like this

Where,

Relation state is a subset of the Cartesian product of the domains of

its attributes each domain contains the set of all possible values the
attribute can take.

Example:

Session 8, 9 - Relation Models 3

 Let R(A1, A2) be a relation schema, and dom(A1) = {0,1}, Let
dom(A2) = {a,b,c}

So, the cartesian product of dom(A1), dom(A2) is {<0,a> , <0,b> ,

<0,c>, <1,a>, <1,b>, <1,c>}

So we can say like r(R) ⊂ {<0,a> , <0,b> , <0,c>, <1,a>, <1,b>,

<1,c>}

r(R) could be {<0,a> , <0,b> , <1,c> }. this is one possible state (or
“population” or “extension”) r of the relation R, defined over A1 and
A2.

A relation (or relation state) r of the relation schema R(A1, A2, .. . An), also
denoted
by r(R), is a set of n-tuples r={t1, t2, .., tm}.

Each n-tuple is an ordered list of n-values t=< v1, v2, .., vn>.

Characteristics Of Relations:

Ordering of tuples(Rows) in a relation r(R):

The tuples are not considered to be ordered, even though they appear to
be in the tabular form.

Ordering of attributes(columns) in a relation r(R):

We will consider the attributes in R(A1, A2, ..., An) and the values in t=<v1,
v2, ..., vn> to be ordered. (However, a more general alternative definition of
relation does not require this ordering).

Values in a tuple:

All values are considered indivisible (Cannot be Extended furthermore).

Each value in a tuple must be from the domain of the attribute for that
column

If tuple t = <v1, v2, …, vn> is a tuple (row) in the relation state r of R(A1,
A2, …, An)

Then each vi must be a value from dom(Ai)

A special null value is used to represent values that are unknown or

inapplicable to certain tuples.

Notation:

Session 8, 9 - Relation Models 4

 component values of a tuple t by : t[Ai] = vi or t.Ai = vi ( vi is
respective value )

t[Au, Av, ..., Aw] refers to the sub-tuple of t containing the values of
attributes Au, Av, ..., Aw, respectively in t.

Ref Link for Keys

Candidate Key (Minimal Super key):

The minimal set of attributes that can uniquely identify a tuple is known as a
candidate key. For Example, STUD_NO in STUDENT relation.

The value of the Candidate Key is unique and non-null for every tuple.

The candidate key can be simple (having only one attribute) or composite as
well. For Example, {STUD_NO, COURSE_NO} is a composite candidate key
for relation STUDENT_COURSE.

Super Key:

The set of attributes that can uniquely identify a tuple is known as Super Key.
For Example, STUD_NO, (STUD_NO, STUD_NAME), etc.

Adding zero or more attributes to the candidate key generates the super key.

A candidate key is a super key but vice versa is not true.

Primary Key:

From the candidate key/s in relation out of which we can choose any one as
the primary key according to requirement.

Session 8, 9 - Relation Models 5

 For Example, STUD_NO, as well as STUD_PHONE both, are candidate keys
for relation STUDENT but STUD_NO can be chosen as the primary key (only
one out of many candidate keys).

Alternate Key:

The candidate key other than the primary key is called an alternate key.

For Example, STUD_NO, as well as STUD_PHONE both, are candidate keys

for relation STUDENT but STUD_PHONE will be an alternate key (only one
out of many candidate keys).

Foreign Key:

A foreign key is different from a super key, candidate key or primary key
because a foreign key is the one that is used to link two tables together or
create connectivity between the two.

A foreign key is one that is used to link two tables together via the primary
key. It means the columns of one table points to the primary key attribute of
the other table. It further means that if any attribute is set as a primary key
attribute will work in another table as a foreign key attribute.

Foreign Key can be NULL as well as may contain duplicate tuples i.e. it need
not follow uniqueness constraint.

In the Student table, the field Stud_Id is a primary key because it is uniquely
identifying all other fields of the Student table. On the other hand, Stud_Id is
a foreign key attribute for the Department table because it is acting as a
primary key attribute for the Student table. It means that both the Student and
Department table are linked with one another because of the Stud_Id
attribute.

Keys In a Single diagram:

Session 8, 9 - Relation Models 6

 Ref Link for Constrains

Relational Integrity Constraints:

4 types:

1. Key constraints

2. Entity integrity constraints

3. Referential integrity constraints

4. domain constraint

Key Constraints:

Keys are the entity set that is used to identify an entity within its entity set
uniquely.

An entity set can have multiple keys, but out of which one key will be the primary
key. A primary key can contain a unique and null value in the relational table.

Session 8, 9 - Relation Models 7

 Entity integrity constraints:

What is Entity Integrity: The primary key attributes PK of each relation schema R
in S cannot have null values in any tuple of r(R).

The entity integrity constraint states that the primary key value can't be null.

This is because the primary key value is used to identify individual rows in
relation and if the primary key has a null value, then we can't identify those rows.

A table can contain a null value other than the primary key field.

Referential integrity constraint or Foreign Key Constraint:

Before going to Referential integrity constraints, we need to learn about what is

Referential integrity:

Referential integrity refers to the relationship between tables. Because

each table in a database must have a primary key, this primary key can
appear in other tables because of its relationship to data within those tables.
When a primary key from one table appears in another table, it is called a
foreign key.

The table from which the values are derived is known as the Master or
Referenced Table and the Table in which values are inserted accordingly is
known as the Child or Referencing Table, In other words, we can say that
the table containing the foreign key is called the child table, and the table
containing the Primary key/candidate key is called the referenced or parent
table.

A referential integrity constraint is specified between two tables.

In the Referential integrity constraints, if a foreign key in Table 1 refers to the

Primary Key of Table 2, then every value of the Foreign Key in Table 1 must be

Session 8, 9 - Relation Models 8

 null or be available in Table 2.

Domain constraint:

Domain constraints can be defined as the definition of a valid set of values for an
attribute.

The data type of domain includes string, character, integer, time, date, currency,
etc. The value of the attribute must be available in the corresponding domain.

Session 8, 9 - Relation Models 9

 Basic operations for changing the database:

INSERT a new tuple in a relation

DELETE an existing tuple from a relation

MODIFY an attribute of an existing tuple

Constrains while doing these operations:

Insert:

INSERT may violate any of the constraints

Domain constraint: if one of the attribute values provided for the new tuple
is not of the specified attribute domain

Key constraint: if the value of a key attribute in the new tuple already exits
in another tuple in the relation

Referential integrity: if a foreign key value in the new tuple references a

primary key value that does not exist in the referenced relation

Entity integrity: if the primary key value is null in the new tuple

Delete:

DELETE may violate only

Referential integrity: If the primary key value of the tuple being deleted is
referenced from other tuples in the database

Can be remedied by several actions: RESTRICT, CASCADE, SET NULL

RESTRICT option: reject the deletion

CASCADE option: propagate the new primary key value into the foreign
keys of the referencing tuples

Session 8, 9 - Relation Models 10

 SET NULL option: set the foreign keys of the referencing tuples to NULL

One of the above options must be specified during database design for each
foreign key constraint

Update:

The update may violate only

domain constraint: Any of the other constraints may also be violated,

depending on the attribute being updated:

Updating the primary key (PK): Similar to a DELETE followed by an

INSERT. Also, Need to specify similar options to DELETE

Updating a foreign key (FK): May violate referential integrity

Updating an ordinary attribute (neither PK nor FK): This can only violate
domain constraints

The update does not violate NULL constraint on an attribute being modified

Session 8, 9 - Relation Models 11