3/4/2024

Database systems

Dr. Sufyan Samara

Course
• Book: you can choose one of these books
– Database System Concepts Seventh Edition, A. Silberschatz, H. F.
Korth, and S. Sudarshan
– Database Systems: Introduction to Databases and Data
Warehouses 2ed edition by Nenad Jukic , Susan Vrbsky, et al.
– Database Systems: Design, Implementation, & Management by
Carlos Coronel and Steven Morris – latest edition
– Fundamentals of database systems, Elmasri and Navathe, latest
Edition
• Three Exams:
– Midterm Exam: 30% (16-3-2024)
– Final Exam : 40%
– (Homework, Oral discussion, Project, Report): 30%

1
 3/4/2024

Course Outline
• Basic Concepts:
– Objectives of DBMSs.
– Database environment.
– Database and DBMSs.
– Database Architecture.
• Data Modeling:
– Entity Relationship Model (ERM).
– Extended Entity Relationship Model (EERM).
– Universal modeling language (UML).
– Relational model.
• Database Design
– Logical database design.
– Schema mapping and transformation.
– Data redundancy and duplication. 3

Course Outline – cont.

• Relational algebra and Structured Query Language (SQL):
– Database languages.
– Relational Algebra.
– Data definition language (DDL).
– Data manipulation language (DML).
• JDBC, Object Relational Modeling, and Reporting
– Introduction to JDBC
– Object Relational Modeling and JPA
– Report design and Jasper Library
• Normalization Process
– First normal form.
– Second normal form.
– Third normal form.
– Transaction anomalies.
• Contemporary Topics

2
 3/4/2024

What are the tools?

• Many Database systems
– ORACLE
– MSSQL
– MySQL
– PostgreSQL
– Access Database
– …
• Open Source vs Commercial
• You can use either PostgreSQL or Oracle
• Also, Apache NetBeans 20, pgAdmin4 or SQL
developer, and Jasperreports.

Introduction
• Databases can be found in many applications
– Web site - Google, Yahoo!, Amazon.com, or
thousands of smaller sites
– Corporations maintain all their important records in
databases.
– Many scientific investigations; data gathered by
astronomers, by investigators of human genome,
and by biochemists exploring properties of proteins.

3
 3/4/2024

Introduction - DBMS
• A body of knowledge and technology that has
developed over several decades which is the
reason behind the power of DB.
• This is a specialized software called a database
management system, or DBMS, or simply a
"database system."
• It is a powerful tool for creating and managing
large amounts of data efficiently and allowing it
to persist over long periods of time, safely.
• These systems are among the most complex
types of software available.

Introduction – cont.
• A database-management system (DBMS) is a
collection of interrelated data and a set of
programs to access those data.
• The collection of data, usually referred to as the
database, contains information relevant to an
enterprise.
• The primary goal of a DBMS is to provide a way to
store and retrieve database information that is
both convenient and efficient.
8

4
 3/4/2024

Functionalities of DBMS
• Specialized data-definition language: to allow users to create new
databases and specify their schemas (logical structure of the data).
• Query Language: Provides the ability to query and to modify the
data.
• Large amounts of data storage: many terabytes or more - over a
long period of time, allowing efficient access to the data for queries
and database modifications.
• Durability: the recovery of the database in the face of failures,
errors of many kinds, or intentional misuse.
• Control access to data from many users at once, without allowing
unexpected interactions among users (called isolation) and without
actions on the data to be performed partially but not completely
(called atomicity).

Data, Information, and metadata

111 Ahmad 45 ClientID ClientName DaysOverdue
123 Doaa 17 111 Ahmad 45
101 Ruba 55 123 Doaa 17
341 Jameel 74 101 Ruba 55
117 Sami 101 341 Jameel 74
117 Sami 101
The metadata explains that each row represents a client

10

10

5
 3/4/2024

Early Database Management Systems

• First appear in 1960
• Evolved from file systems.
• Although file systems allows large data storage
for long time is has many disadvantages.
– Data can be lost without recovery guarantee
– Hard to access data within file
– No query language support
– Their schema is limited to file and directory structure
– Limited or no concurrent access (only one can modify
a file at one time).

11

11

HDD internals

12

12

6
 3/4/2024

13

13

Some file system types

14

14

7
 3/4/2024

Applications of DBMS
• Banking systems: maintaining accounts and
making sure that system failures do not cause
money to disappear.
• Airline reservation systems: require assurance
that data will not be lost, and they must accept
very large volumes of small actions by customers.
• Corporate record keeping: employment and tax
records, inventories, sales records, and a great
variety of other types of information, much of it
critical.
15

15

Visualizing data in DBMS

• Early DBMS used "hierarchical" or tree-based
model and the graph-based "network" model.
– They did not support high-level query languages.
– Users has to specify low level data storage.
– For example the CODASYL query language had
statements that allowed the user to jump from
data element to data element, through a graph of
pointers among these elements.
– There was considerable effort needed to write
such programs, even for very simple queries.

16

16

8
 3/4/2024

Visualizing data in DBMS – cont.

• In 1970 Ted Codd proposed that database systems
should view data as tables called relations.
• Behind the scenes, there might be a complex data
structure that allowed rapid response to a variety of
queries.
• The programmer of a relational system would not be
concerned with the storage structure.
• Queries could be expressed in a very high-level
language.
• By 1990, relational database systems were the norm
• Database systems are still evolving.
– Object-oriented model.
– NoSQL databases 17

17

Nowadays
• Personal computers nowadays have large storage area >1000 GB.
• DBMS can be easily installed on a PC
• However, a gigabyte is not that much anymore.
• Corporate databases normally store petabytes ( 1015 bytes) or even
exabytes (1018 bytes) of data and serve it all to users.
• Examples:
– Google holds exabytes of data gleaned from its crawl of the Web in
specialized structures optimized for search-engine queries.
– Satellites send down petabytes of information for storage in
specialized systems.
– Repositories used in social media networks application (Facebook,
Instagram, etc.) store trillions of pictures and videos and support
search of those media.
– Amazon's has trillions of pictures of products to serve.
– An hour of video requires at least a gigabyte. Sites such as You Tube
hold millions of movies and make them available easily.
– Peer-to-peer file-sharing systems use large networks of conventional
computers, together the database they embody is enormous.

18

18

9
 3/4/2024

Distribution, Diversity, and Information Integration

• There exist many cases where related information resides in many

databases.
• The DBMS used in each database may have different structures for
information.
• As a result, it has become necessary to build structures on top of
existing databases, with the goal of integrating the information
distributed among them.
• One popular approach is the creation of data warehouses, where
information from many legacy databases is copied periodically, with
the appropriate translation, to a central database.
• Another approach is the implementation of a mediator, or
"middleware," whose function is to support an integrated model of
the data of the various databases, while translating between this
model and the actual models used by each database.

19

19

20

20

10
 3/4/2024

DBMS

The solid lines indicate control and Single boxes represent system
data flow, while dashed lines indicate components, while double boxes
21
data flow only. represent in-memory data structures.

21

DBMS
• There are two distinct sources of commands
to the DBMS:
– Conventional users and application programs that
ask for data or modify data.
– A database administrator: a person or persons
responsible for the structure or schema of the
database.

22

22

11
 3/4/2024

Data-Definition Language Commands

• The database administrator, or DBA, might decide to create a new
table or relation.
• For example, a table for a student with columns for a student
name, a course, and a grade of that course. Moreover, the
allowable grades are A, B, C, D, and F.
• This structure and constraint information is all part of the schema
of the database.
• DBA requires special commands and privileges to alter the database
schema.
• These schema-altering data-definition language (DDL) commands
are parsed by a DDL processor and passed to the execution engine,
which then goes through the index/file/record manager to alter the
metadata, that is, the schema information for the database.

23

23

Query Processing
• The majority of interactions with the DBMS
are through users or application programs
initiate some action, using the data-
manipulation language (DML).
• Does not affect the schema but may affect the
content of the database.
• Handled by two subsystems:
– One responsible of answering the Query
– One responsible of transaction Processing

24

24

12
 3/4/2024

Answering a query
• Query compiler: The query is parsed and optimized by a
query compiler.
– Results in a query plan or sequence of actions that the DBMS
will perform.
• The query is then passed to the Execution Engine which
issues a sequence of requests for small pieces of data,
typically records or tuples of a relation, to a resource
manager.
• The resource manager is responsible of data files (holding
relations), the format and size of records in those files, and
index files, which help find elements of data files quickly.
• The requests for data are passed to the buffer manager.
• The buffer manager’s task is to bring appropriate portions
of the data from secondary storage (disk) where it is kept
permanently, to the main-memory buffers.
• The buffer manager communicates with a storage manager
to get data from disk.
25

25

Transaction Processing
• Queries and other DML actions are grouped into transactions,
which are units that must be executed atomically and in isolation
from one another.
• Any query or modification action can be a transaction by itself and
must be preserved even if the system fails.
• Transaction processor can be divided into two major parts:
– A concurrency-control manager, or scheduler, responsible for assuring
atomicity and isolation of transactions.
• Uses a typical scheduler that does its work by maintaining locks on certain
pieces of the database.
• Locks are generally stored in a main-memory lock table.
• Requires Deadlock resolution
– A logging and recovery manager, responsible for the durability of
transactions.
• Every change in the database is logged separately on disk.
• when a system failure or "crash" occurs, a recovery manager will be able to
examine the log of changes and restore the database to some consistent state.
• The log manager initially writes the log in buffers and negotiates with the
buffer manager to make sure that buffers are written to disk.

26

26

13
 3/4/2024

The ACID Properties of Transactions

• "A" stands for "atomicity," the all-or-nothing execution of
transactions.
• "I'' stands for "isolation," the fact that each transaction
must appear to be executed as if no other transaction is
executing at the same time.
• "D" stands for "durability," the condition that the effect on
the database of a transaction must never be lost, once the
transaction has completed.
• "C" stands for "consistency." That is, all databases have
consistency constraints, or expectations about relationships
among data elements (e.g., account balances may not be
negative after a transaction finishes).

27

27

Storage and Buffer Management

• The storage manager controls the placement of data on disk and its
movement between disk and main memory.
• The buffer manager is responsible for partitioning the available main
memory into buffers, which are page-sized regions into which disk blocks
can be transferred.
• The kinds of information that various components may need include:
– Data: the contents of the database itself.
– Metadata: the database schema that describes the structure of, and
constraints on, the database.
– Log Records: information about recent changes to the database; these
support durability of the database.
– Statistics: information gathered and stored by the DBMS about data
properties such as the sizes of, and values in, various relations or other
components of the database.
– Indexes: data structures that support efficient access to the data.

28

28

14
 3/4/2024

The Query Processor

• Represented by two components: query compiler and execution
engine
• The query compiler
– Which translates the query into query plan which is a sequence of
operations to be performed on the data. Often implementations of
"relational algebra" operations
– The query compiler consists of three major units:
• (a) A query parser, which builds a tree structure from the textual form of the
query.
• (b) A query preprocessor, which performs semantic checks on the query
• (c) A query optimizer, which transforms the initial query plan into the best
available sequence of operations on the actual data.
– The query compiler uses metadata and statistics about the data to
decide
• The execution engine
– Has the responsibility for executing each of the steps in the chosen
query plan.
– Works with the buffers, the scheduler, and log manager. 29

29

Data Models
• Underlying the structure of a database is the data model: a
collection of conceptual tools for describing data, data
relationships, data semantics, and consistency constraints.
• There are a number of different data models that we shall
cover in the text.
• The data models can be classified into four different
categories:
– Relational Model
– Entity-Relationship Model.
– Semi-structured Data Model
– Object-Based Data Model

30

30

15
 3/4/2024

Relational Model
• The relational model uses a collection of tables to represent both
data and the relationships among those data.
• Each table has multiple columns, and each column has a unique
name. Tables are also known as relations.
• The relational model is an example of a record-based model.
• Record-based models are so named because the database is
structured in fixed-format records of several types.
• Each table contains records of a particular type.
• Each record type defines a fixed number of fields, or attributes.
• The columns of the table correspond to the attributes of the record
type.
• The relational data model is the most widely used data model, and
a vast majority of current database systems are based on the
relational model.

31

31

Entity-Relationship Model
• The entity-relationship (E-R) data model uses
a collection of basic objects, called entities,
and relationships among these objects.
• An entity is a “thing” or “object” in the real
world that is distinguishable from other
objects.
• The entity-relationship model is widely used in
database design.

32

32

16
 3/4/2024

Semi-structured Data Model

• The semi-structured data model permits the
specification of data where individual data items
of the same type may have different sets of
attributes.
• This is in contrast to the data models mentioned
earlier, where every data item of a particular type
must have the same set of attributes.
• JSON and Extensible Markup Language (XML) are
widely used semi-structured data
representations.
33

33

Object-Based Data Model

• Object-oriented programming (especially in Java, C++,
or C#) has become the dominant software-
development methodology.
• This led initially to the development of a distinct
object-oriented data model, but today the concept of
objects is well integrated into relational databases.
• Standards exist to store objects in relational tables.
Database systems allow procedures to be stored in the
database system and executed by the database system.
• This can be seen as extending the relational model
with notions of encapsulation

34

34

17
 3/4/2024

Database design and

Data Modeling Using the
Entity-Relationship (ER) Model
• Conceptual modeling is a very important
phase in designing a successful database
application.
• We need to understand the system before we
can actually program it or manage it.
• Example: Bank
– Requires the design, implementation, and testing
of applications for tellers, managers, and
customers.

35

35

Database Design

36

36

18
 3/4/2024

Database Requirements

37

37

Rrequirements collection and analysis

• The database designers interview prospective
database users to understand and document
their data and functional requirements.
• Data requirements: what kind of data is to be
stored.
• Functional requirements: user defined
operations (or transactions) that will be
applied to the database, including both
retrievals and updates.

38

38

19
 3/4/2024

Iterative nature of the database requirements

collection, definition, and visualization process

39

39

Conceptual design
• Next step after requirements.
• To create conceptual schema which is a detailed
descriptions of the entity types, relationships, and
constraints.
– An entity represents a real-world object or concept.
– A relationship represents an association among the
entities
– Entity-relationship data modeling
• Advantages
– Easy to understand and to communicate with nontechnical users
– As a reference that all requirements are met and no conflicts
exists
– Concentrate on data with no concern about storage or
implementation details.

40

40

20
 3/4/2024

With conceptual design

• Specify basic data model operations used by
high-level user queries.
• This is identified by functional requirements
• Modifications to the conceptual schema can
be introduced if some functional
requirements cannot be specified using the
initial schema.

41

41

implementation of the database

logical design
• Using relational or the object-relational
database model
• result is a database schema
• Often automated or semiautomated by DBMS
tools.

42

42

21
 3/4/2024

Physical design
• Internal storage structures, file organizations,
indexes, access paths, and physical design
parameters for the database files are
specified.

43

43

Application
• Along side the data base design and
implementation, application with a good
interactive GUI should be developed to work
with the database
• This is normally done using any normal
programming language such as java, C#, or
web a web interface.

44

44

22
 3/4/2024

ER model
• Used for conceptual schema design
• An enhanced version of it is EER model
• We will study this model on a sample
database application called COMPANY.

45

45

Company Data Requirments

• After requirements collection and analysis phase the following
description is provided.
– The company is organized into departments. Each department has a
unique name, a unique number, and a particular employee who
manages the department. We keep track of the start date when that
employee began managing the department. A department may have
several locations.
– A department controls a number of projects, each of which has a
unique name, a unique number, and a single location.
– We store each employee’s name, Social Security number,2 address,
salary, sex (gender), and birth date. An employee is assigned to one
department, but may work on several projects, which are not
necessarily controlled by the same department. We keep track of the
current number of hours per week that an employee works on each
project. We also keep track of the direct supervisor of each employee
(who is another employee).
– We want to keep track of the dependents of each employee for
insurance purposes. We keep each dependent’s first name, sex, birth
date, and relationship to the employee.
46

46

23
 3/4/2024

47

47

The company ER model

• Next slide shows the complete ER model of
the company database.
• This model will be used to gradually study the
ER diagrams step by step.

48

48

24
 3/4/2024

49

49

A Sample Database Application

 COMPANY
 Employees, departments, and projects
 Company is organized into departments
 Department controls a number of projects
 Employee: store each employee’s name,
Social Security number, address, salary, sex
(gender), and birth date
 Keep track of the dependents of each
employee
50

50

25
 3/4/2024

51

51

ER model
• The ER model describes data as entities,
relationships, and attributes
• The basic object is an entity, which is a thing in
the real world with an independent existence.
– Examples: person, car, house, job, course
• Each entity has attributes which are properties
that describe the entity
– Example:EMPLOYEE entity may be described by the
employee’s name, age, address, salary, and job.
– Each employee has a value for each attribute

52

52

26
 3/4/2024

Example: Enitities and attribute

• Figure 7.3: Two entities Employee e1, and
Company c1, with their attributes

53

53

Attribuites types
• ER model has several types of attributes.
– simple or composite,
– Singlevalued or multivalued, and
– stored or derived

54

54

27
 3/4/2024

Composite versus Simple (Atomic)

Attributes
• Composite attributes
– can be divided into smaller subparts, which represent
more basic attributes with independent meanings.
– For example, the Address attribute of the employee
entity can be subdivided into StreetAddress, City,
State, and postal or ZIP cod, with the values "2311
Kirby,” "Houston," "Texas," and "77001."
• Attributes that are not divisible are called simple
or atomic attributes.

55

55

Single-Valued versus Multivalued

Attributes
• Single-valued.
– For example, Age is a single-valued attribute of a person
• In some cases an attribute can have a set of values for
the same entity.
– for example, a Colors attribute for a car, or a
CollegeDegrees attribute for a person.
– Cars with one color have a single value, whereas two-tone
cars have two values for Colors.
– Such attributes are called multivalued.
– may have lower and upper bounds to constrain the
number of values allowed for each individual entity.

56

56

28
 3/4/2024

Stored versus Derived Attributes

• In some cases, two (or more) attribute values
are related.
– for example, the Age and BirthDate attributes of a
person.
– For a particular person entity, the value of Age can
be determined from the current (today's) date and
the value of that person's BirthDate.
– The Age called a derived attribute
– The BirthDate called a stored attribute.

57

57

NULL Values
• In some cases, a particular entity may not have an applicable value for an
attribute.
– For example, the Apartment_number attribute of an address applies only to
addresses that are in apartment buildings and not to other types of
residences, such as single-family homes. Similarly, a College_degrees attribute
applies only to people with college degrees.
• For such situations, a special value called NULL is created.
• NULL can also be used if we do not know the value of an attribute for a
particular entity—for example, if we do not know the home phone
number of ‘John Smith’.
• The meaning of the former type of NULL is not applicable, whereas the
meaning of the latter is unknown.
• The unknown category of NULL can be further classified into two cases.
The first case arises when it is known that the attribute value exists but is
missing—for instance, if the Height attribute of a person is listed as NULL.
• The second case arises when it is not known whether the attribute value
exists—for example, if the Home_phone attribute of a person is NULL.
• The multi meaning of the NULL value is a problem.

58

58

29
 3/4/2024

Null Values – cont.

59

59

Complex attributes
• For example, if a person can have more than
one residence and each residence can have
multiple phones, an attribute AddressPhone
for a person
• {AddressPhone( {Phone(AreaCode,PhoneNumber)},
Address(StreetAddress(Number,Street,ApartmentNu
mber), City,State,Zip) ) }

60

60

30
 3/4/2024

Entity Types, Entity Sets, Keys, and

Value Sets
 Entity type
 Collection (or set) of entities that have the
same attributes

61

61

ER diagram
• An entity type is represented in ER diagrams as a
rectangular box enclosing the entity type name.
– An entity type describes the schema
• Attribute names are enclosed in ovals and are
attached to their entity type by straight lines.
• Composite attributes are attached to their
component attributes by straight lines.
• Multivalued attributes are displayed in double
ovals.
62

62

31
 3/4/2024

Entity Types, Entity Sets, and Keys

 Key or uniqueness constraint
 Attributes whose values are distinct for each
individual entity in entity set
 Key attribute
• Uniqueness property must hold for every entity set
of the entity type
• In ER diagrammatic notation, each key attribute
has its name underlined inside the oval
• An entity type may also have no key, in which
case it is called a weak entity type
63

63

Value sets
• Each simple attribute of an entity type is associated with a value set
 Value sets (or domain of values)
 Specifies set of values that may be assigned to that attribute for each
individual entity
 Example: Age of an employee can be assigned a domain set of integer
numbers between 16 and 70.
 Mathematically, an attribute A of entity set E whose value set is V
can be defined as a function from E to the power set P(V) of V:
 A : E → P(V)
 This definition applies to single-valued and multivalued attributes, as
well as NULLs.
 For a composite attribute A, the value set V is the power set of the
Cartesian product of P(V1), P(V2), ..., P(Vn), where V1, V2, ..., Vn are
the value sets of the simple component attributes that form A:
 V = P (P(V1) × P(V2) × ... × P(Vn))

64

64

32
 3/4/2024

65

65

Initial Conceptual Design of the

COMPANY Database

66

66

33
 3/4/2024

What left
• We have not represented the fact that an
employee can work on several projects
• We have not represented the number of hours
per week an employee works on each project.
• May be represented as multivalued composite
attribuite but not a good idea.
• A better way are relationships

67

67

Relationship Types, Relationship

Sets, Roles, and Structural
Constraints
 Relationship
 A relationship type R among n entity types E1,
E2, ..., En defines a set of associations— or a
relationship set—among entities from these
entity types.
 There is a relationship, when an attribute of
one entity type refers to another entity type
 Represent references as relationships not
attributes

68

68

34
 3/4/2024

Relationship Types, Sets, and

Instances
 Relationship instances ri
 Each ri associates n individual entities (e1, e2,
..., en)
 Each entity ej in ri is a member of entity set Ej

69

69

Relationship example and ER diagram

• For example, consider a relationship type WORKS_FOR
– Between the two entity types EMPLOYEE and
DEPARTMENT
– Associates each employee with the department for which
the employee works.
– Each relationship instance in the relationship set
WORKS_FOR associates one employee entity and one
department entity.
– Where each relationship instance ri is connected to the
employee and department entities that participate in ri.
– (See next slide) employees e1, e3, and e6 work for
department d1 ; e2 and e4 work for d2; and e5 and e7 work
for d3·
• In ER diagrams, relationship types are displayed as
diamond-shaped boxes, which are connected by
straight lines to the rectangular boxes representing the
participating entity types.
70

70

35
 3/4/2024

71

71

Relationship Degree
 Degree of a relationship type
 Number of participating entity types
 Binary, ternary
 The WORKS_FOR relationship is of degree two.
 An example of a ternary relationship is SUPPLY, where each
relationship instance rj associates three entities-a supplier s, a
part p, and a project j
 Relationships as attributes
 Think of a binary relationship type in terms of attributes
 One can think of an attribute called Department of the
EMPLOYEE entity type whose value for each employee entity is
(a reference to) the department entity that the employee works
for
 What if this attribute is added to the Department entity
 multivalued attribute Employees of the entity type DEPARTMENT

72

72

36
 3/4/2024

73

73

74

74

37
 3/4/2024

Role Names, how to choose a good

name for a relationship
• Each entity type that participates in a
relationship has role in the relationship.
• The role name signifies of each entity and
helps to explain what the relationship means.
• For example, in the WORKS_FOR
– EMPLOYEE plays the role of worker
– DEPARTMENT plays the role of employer.

75

75

Recursive Relationships
• In some cases the same entity type participates more
than once in a relationship type in different roles.
• In such cases the role name becomes essential for
distinguishing the meaning of each participation.
• Such relationship types are called recursive
relationships.
• Ex:
– The SUPERVISION relationship type relates an employee
to a supervisor.
– Both the employee and the supervisor are in the
EMPLOYEE entity, see next slide.

76

76

38
 3/4/2024

77

77

Constraints on Binary Relationship types

• Relationship types usually have certain constraints that
limit the possible combinations of entities that may
participate in the corresponding relationship set
• Constraints are determined from the miniworld
• Ex: company has a rule that each employee must work
for exactly one department
• Two types
– Cardinality ratio
– Participation
• The cardinality ratio and participation constraints,
taken together, are called the structural constraints

78

78

39
 3/4/2024

79

79

Constraints on Binary Relationship

Types
 Cardinality ratio for a binary relationship
 Specifies maximum number of relationship instances that entity
can participate in
 The WORKS_FOR binary relationship type, DEPARTMENT:
EMPLOYEE is of cardinality ratio 1:N, meaning that each
department can be related to any number of employees
 The possible cardinality ratios for binary relationship types are
1:1, 1:N, N:1, and M:N
 An example of a 1:1 binary relationship is MANAGES
 department entity to the employee who manages that department
 The relationship type WORKS_ON is M:N, because
 an employee can work on several projects and a project can have
several employees.

 Participation constraint
 Specifies whether existence of entity depends on its being
related to another entity
 Types: total and partial
80

80

40
 3/4/2024

Total and partial participation

• If a company policy states that every employee must
work for a department, then an employee entity can
exist only if it participates in at least one WORKS_FOR
relationship instance the participation of EMPLOYEE in
WORKS_FOR is called total participation
• We do not expect every employee to manage a
department, so the participation of EMPLOYEE in the
MANAGES relationship type is partial, meaning that
some or "part of the set of" employee entities are
related to some department entity via MANAGES, but
not necessarily all.
• In ER diagrams
– Total participation (or existence dependency) is displayed
as a double line connecting the participating entity type to
the relationship,
– The partial participation is represented by a single line
81

81

Attributes of Relationship Types

• Relationship types can also have attributes, similar to those of entity
types.
• For example,
– To record the number of hours per week that an employee works on a
particular project, we can include an attribute Hours for the WORKS_ON
relationship type
– To include the date on which a manager started managing a department via
an attribute StartDate for the MANAGES relationship type
• Attributes of 1:1 relationship types can be migrated to one of the
participating entity types. For example, the StartDate attribute for the
MANAGES relationship can be an attribute of either EMPLOYEE or
DEPARTMENT, although conceptually it belongs to MANAGES.
• For a 1:N relationship type, a relationship attribute can be migrated only
to the entity type on the N-side of the relationship. For example, if the
WORKS_FOR relationship also has an attribute StartDate that indicates
when an employee started working for a department, this attribute can be
included as an attribute of EMPLOYEE.
• For M:N relationship types, some attributes may be determined by the
combination of participating entities in a relationship instance, not by any
single entity. Such attributes must be specified as relationship attributes.
An example is the Hours attribute of the M:N relationship WORKS_ON

82

82

41
 3/4/2024

Weak Entity Types

 Do not have key attributes of their own
– Identified by being related to specific entities from another entity type
which has a key (the identifying or the owner entity type)
– A weak entity type always has a total participation constraint (existence
dependency) with respect to its identifying relationship
 Identifying relationship
 Relates a weak entity type to its owner
 Ex: The entity type DEPENDENT, related to EMPLOYEE, which is used
to keep track of the dependents of each employee via a 1:N
relationship.
 A weak entity type normally has a partial key, which is the set of
attributes that can uniquely identify weak entities that are related to
the same owner entity
 Not every existence dependency results in a weak entity type.
 For example, a DRIVER_LICENSE entity cannot exist unless it is
related to a PERSON entity, even though it has its own key
(LicenseNumber) and hence is not a weak entity.
 In ER diagrams, both a weak entity type and its identifying
relationship are distinguished by surrounding their boxes and
diamonds with double lines
 The partial key attribute is underlined with a dashed or dotted line.

83

83

84

84

42
 3/4/2024

Refining the ER Design for the

COMPANY Database
 Change attributes that represent
relationships into relationship types
 Determine cardinality ratio and
participation constraint of each
relationship type

85

85

COMPANY Database relationship types

• MANAGES, a 1:1 relationship type between EMPLOYEE and
DEPARTMENT. EMPLOYEE participation is partial.
DEPARTMENT participation is not clear from the
requirements.
– We question the users, who say that a department must have a
manager at all times, which implies total participation.
– The attribute StartDate is assigned to this relationship type.
• WORKS_FOR, a 1:N relationship type between
DEPARTMENT and EMPLOYEE. Both participations are total.
• CONTROLS, a 1:N relationship type between DEPARTMENT
and PROJECT. The participation of PROJECT is total,
whereas that of DEPARTMENT is determined to be partial,
after consultation with the users indicates that some
departments may control no projects.

86

86

43
 3/4/2024

COMPANY Database relationship types

• SUPERVISION, a 1:N relationship type between EMPLOYEE
(in the supervisor role) and EMPLOYEE (in the supervisee
role). Both participations are determined to be partial, after
the users indicate that not every employee is a supervisor
and not every employee has a supervisor.
• WORKS_ON, determined to be an M:N relationship type
with attribute Hours, after the users indicate that a project
can have several employees working on it. Both
participations are determined to be total.
• DEPENDENTS_OF, a 1:N relationship type between
EMPLOYEE and DEPENDENT, which is also the identifying
relationship for the weak entity type DEPENDENT. The
participation of EMPLOYEE is partial, whereas that of
DEPENDENT is total.
87

87

ER Diagrams, Naming
Conventions, and Design Issues

88

88

44
 3/4/2024

Proper Naming of Schema Constructs

 In ER diagrams the emphasis is on
representing the schemas rather than the
instances.
 Choose names that convy meanings
attached to different constructs in schema
 Nouns give rise to entity type names
 Verbs indicate names of relationship types
 Choose binary relationship names to make
ER diagram readable from left to right and
from top to bottom
89

89

Design Choices for ER Conceptual

Design
 Model concept first as an attribute
 Refined into a relationship if attribute is a
reference to another entity type
 Attribute that exists in several entity types
may be elevated to an independent entity
type
 Can also be applied in the inverse

90

90

45
 3/4/2024

Alternative Notations for ER

Diagrams
 Specify structural constraints on relationships
 Replaces cardinality ratio (1:1, 1:N, M:N) and
single/double line notation for participation constraints
 Associate a pair of integer numbers (min, max) with
each participation of an entity type E in a relationship
type R, where 0 ≤ min ≤ max and max ≥ 1
 The numbers mean that for each entity e in E, e must
participate in at least min and at most max
relationship instances in R at any point in time.
 In this method, min = 0 implies partial participation,
whereas min > 0 implies total participation.

91

91

92

92

46
 3/4/2024

93

93

94

94

47
 3/4/2024

95

95

Relationship Types of Degree

Higher than Two
 Degree of a relationship type
 Number of participating entity types
 Binary
 Relationship type of degree two
 Ternary
 Relationship type of degree three
 Some database design tools are based on
variations of the ER model that permit only
binary relationships.
 Convert ternary relationship to two binary
relationships.
96

96

48
 3/4/2024

97

97

98

98

49
 3/4/2024

Terrnary and binary relationship types

meaning
• In general, a ternary relationship type represents different information
than do three binary relationship types.
• Example of a ternary relationship is SUPPLY, pervious slide (a).
• Suppose that CAN_SUPPLY, between SUPPLIER and PART, includes an
instance (s, p) whenever supplier s can supply part p (to any project);
• Suppose that USES, between PROJECT and PART, includes an instance
( j, p) whenever project j uses part p;
• Suppose the SUPPLIES, between SUPPLIER and PROJECT, includes an
instance (s, j) whenever supplier s supplies some part to project j.
• The existence of three relationship instances (s, p), ( j, p), and (s, j)
in CAN_SUPPLY, USES, and SUPPLIES, respectively, does not
necessarily imply that an instance (s, j, p) exists in the ternary
relationship SUPPLY, because the meaning is different.

99

99

Ternary relationship as entity type

• Another way to convert a relationship to binary to
represent the relationship as a weak entity type, with no
partial key and with three identifying relationships.
– Example: converting SUPLY into week entity, the owner entity
type are the three participating entity types SUPPLIER, PART,
and PROJECT.
– Identified by the combination of its three owner entities from
SUPPLIER, PART, and PROJECT.
• It is also possible to represent the ternary relationship as a
regular entity type
– by introducing an artificial or surrogate key; Supply_id.
• In any case, we would have three binary N:1 relationships
relate SUPPLY to the three participating entity types.

100

100

50
 3/4/2024

Another example of ternary

relationship type

101

101

The Enhanced Entity-Relationship

(EER) Model
• Design accurate database
– especially for engineering porpuses such asdesign
and manufacturing (CAD/CAM) and
telecomunication.
– These have more complex requirements
– Needs additional semantic data modeling.
– Added to ER modeling as enhancements which led
to the Enhanced ER model.

102

102

51
 3/4/2024

Subclasses, Superclasses, and

Inheritance
• Subtype or Subclass of an entity type
– In many cases an entity type has numerous subgroupings or subtypes
of its entities that are meaningful and need to be represented
explicitly because of their significance to the database application.
• Example:
– EMPLOYEE entities can be subdivided into SECRETARY, ENGINEER,
MANAGER, TECHNICIAN, SALARIED_EMPLOYEE, HOURLY_EMPLOYEE,
and so on
– In this example EMPLOYEE is called Superclass and any of its subtypes
are called subclasses.
• This Relationship is called superclass/subclass or
supertype/subtype or simply class/subclass relationship.
• An important concept associated with subclasses
(subtypes) is that of type inheritance.

103

103

Specialization
• The process of defining a set of subclasses of an entity
type.
• The specialization is defined on the basis of some
distinguishing characteristic.
• Example: For the EMPLOYEE type
– Distinguish type (job type): the set of subclasses {SECRETARY,
ENGINEER, TECHNICIAN}
– Distinguish type (method of pay):set of subclasses
{SALARIED_EMPLOYEE, HOURLY_EMPLOYEE}
• In EER The subclasses that define a specialization are
attached by lines to a circle that represents the
specialization, which is connected in turn to the superclass.

104

104

52
 3/4/2024

105

105

Reasons for including class/subclass

relationships
• Certain attributes may apply to some but not
all entities of the superclass.
– SECRETARY subclass has the specific attribute
Typing_speed, whereas the ENGINEER subclass
has the specific attribute Eng_type
– SECRETARY and ENGINEER share their other
inherited attributes from the EMPLOYEE entity
type
• Some relationship types may be valid for only
entities that are members of the subclass.
106

106

53
 3/4/2024

Generalization
• A reverse process of abstraction in which we suppress the
differences among several entity types, identify their common
features, and generalize them into a single superclass of which the
original entity types are special subclasses
• Example: CAR and TRUCK
– They have common attribute, so they can be generalized into VEHICLE
• To distinguish between generalization and specialization:
– An arrow pointing to the generalized superclass represents a
generalization, whereas
– arrows pointing to the specialized subclasses represent a
specialization.
• We will not use this notation because the decision as to which
process is followed in a particular situation is often subjective.

107

107

108

108

54
 3/4/2024

109

109

Constraints and Characteristics of Specialization

and Generalization Hierarchies

• We will concentrat on specialization, the same

goes for generalization.

110

110

55
 3/4/2024

Constraints on Specialization and Generalization

• An entity type is sometimes usefull to be divided into subtypes.

• To denote this division we use a circle
– Inside the circle, a letter may be placed depending on the disjoint type
(d, o, or U).
• Specialization may be determined either by user (user-defined) or
by using a pre-determined attribuit (predicate or condition-defined)
such as Job_type in Employee
– In ER we write the predicate-condition next to the line that connects
the subclass to specialization circle.
– In case of predicate-defined, the specialization is done automatically
upon data insertion.
• If a specialization consists of a single subclass only, such as the
{MANAGER} specialization, we do not use the circle notation

111

111

Attribute-defined specialization

112

112

56
 3/4/2024

113

113

Disjointness and oerlapping

• If specialization which is attribute-defined
implies the disjointness in entities sets, we put
„d” inside the specialization circile
– Example: Employee type (secretary, engineer) or
employee has to be either salaried or hourly
employee.
• If their sets of entities may overlap we put „o”
inside the specialization circle.
– Example: a company may produce a part and also
in some circumistances purchase it.
114

114

57
 3/4/2024

Overlapping

115

115

Total vs. Partial specialization

• A total specialization constraint specifies that every
entity in the superclass must be a member of at least
one subclass in the specialization.
– Every employee has to be either a salaried or an
hourly_employee.
– Represented by a double line notation
• Partial specialization allows an entity not to belong to
any of the subclasses. For example, if some EMPLOYEE
entities do not belong to any of the subclasses
{SECRETARY, ENGINEER, TECHNICIAN}
– Denoted by single line connection

116

116

58
 3/4/2024

Insertion and deletion rules that apply

to specialization
• Deleting an entity from a superclass implies that
it is automatically deleted from all the subclasses
to which it belongs.
• Inserting an entity in a superclass implies that the
entity is mandatorily inserted in all predicate-
defined (or attribute-defined) subclasses for
which the entity satisfies the defining predicate.
• Inserting an entity in a superclass of a total
specialization implies that the entity is
mandatorily inserted in at least one of the
subclasses of the specialization.

117

117

Specialization and Generalization

Hierarchies and Lattices
• A subclass itself may have further subclasses
specified on it, forming a hierarchy or a lattice of
specializations.
• A specialization hierarchy has the constraint that
every subclass participates as a subclass in only
one class/subclass relationship that is, each
subclass has only one parent, which results in a
tree structure or strict hierarchy.
• Specialization lattice, a subclass can be a subclass
in more than one class/subclass relationship.
• Example: University DB
118

118

59
 3/4/2024

The University DB requirements

• The database keeps track of three types of persons:
employees, alumni, and students. A person can belong
to one, two, or all three of these types. Each person
has a name, SSN, sex, address, and birth date.
• Every employee has a salary, and there are three types
of employees: faculty, staff, and student assistants.
Each employee belongs to exactly one of these types.
For each alumnus, a record of the degree or degrees
that he or she earned at the university is kept,
including the name of the degree, the year granted,
and the major department. Each student has a major
department.

119

119

The University DB requirements –

cont.
• Each faculty has a rank, whereas each staff member
has a staff position. Student assistants are classified
further as either research assistants or teaching
assistants, and the percent of time that they work is
recorded in the database. Research assistants have
their research project stored, whereas teaching
assistants have the current course they work on.
• Students are further classified as either graduate or
undergraduate, with the specific attributes degree
program (M.S., Ph.D., M.B.A., and so on) for graduate
students and class (freshman, sophomore, and so on)
for undergraduates.

120

120

60
 3/4/2024

121

121

Notes on the Uni DB

• The specilaization {EMPLOYEE, ALUMNUS,
STUDENT} is overlapping; for example, an
alumnus may also be an employee and may also
be a student pursuing an advanced degree.
• There is a specialization lattice between student,
student_assistant, and Employee.
• A subclass inherits the attributes not only of its
direct superclass, but also of all its predecessor
superclasses all the way to the root of the
hierarchy or lattice if necessary
122

122

61
 3/4/2024

Modeling of UNION Types Using

Categories
• it is sometimes necessary to represent a single
superclass/subclass relationship with more than
one superclass, where the superclasses represent
different entity types.
• May represented as subclass that will represent a
collection of objects that is a subset of the UNION
of distinct entity types; we call such a subclass a
union type or a category
• A union is denoted by a circle with the „U „ letter
• A union can be total or partial which are denoted
by double or single line respectively.
123

123

Example
• Three entity types: PERSON, BANK, and COMPANY.
• In a database for motor vehicle registration, an owner
of a vehicle can be a person, a bank (holding a lien on
a vehicle), or a company.
• We need to create a class (collection of entities) that
includes entities of all three types to play the role of
vehicle owner.
• A category (union type) OWNER that is a subclass of
the UNION of the three entity sets of COMPANY, BANK,
and PERSON can be created for this purpose.

124

124

62
 3/4/2024

125

125

A Sample UNIVERSITY EER Schema,

Design Choices, and Formal Definitions
• UNIVERSITY database that keeps track of
students and their majors, transcripts, and
registration as well as of the university’s
course offerings. The database also keeps
track of the sponsored research projects of
faculty and graduate students

126

126

63
 3/4/2024

Discussion of the requirements

• For each person, the database maintains
information on the person’s Name [Name], Social
Security number [Ssn], address [Address], sex
[Sex], and birth date [Bdate].
• Two subclasses of the PERSON entity type are
identified: FACULTY and STUDENT.
• Specific attributes of FACULTY are rank [Rank]
(assistant, associate, adjunct, research, visiting,
and so on), office [Foffice], office phone
[Fphone], and salary [Salary].
127

127

Discussion of the requirements – cont.

• All faculty members are related to the academic
department(s) with which they are affiliated
[BELONGS] (a faculty member can be associated with
several departments, so the relationship is M:N). A
specific attribute of STUDENT is [Class] (freshman=1,
sophomore=2, ..., graduate student=5).
• Each STUDENT is also related to his or her major and
minor departments (if known) [MAJOR] and [MINOR],
to the course sections he or she is currently attending
[REGISTERED], and to the courses completed
[TRANSCRIPT]. Each TRANSCRIPT instance includes the
grade the student received [Grade] in a section of a
course.
128

128

64
 3/4/2024

Discussion of the requirements – cont.

• GRAD_STUDENT is a subclass of STUDENT, with the defining predicate
Class = 5.
– For each graduate student, we keep a list of previous degrees in a composite,
multivalued attribute [Degrees].We also relate the graduate student to a
faculty advisor [ADVISOR] and to a thesis committee [COMMITTEE], if one
exists.
• An academic department has the attributes name [Dname], telephone
[Dphone], and office number [Office] and is related to the faculty member
who is its chairperson [CHAIRS] and to the college to which it belongs
[CD].
• Each college has attributes college name [Cname], office number
[Coffice], and the name of its dean [Dean].
• A course has attributes course number [C#], course name [Cname], and
course description [Cdesc].
• Several sections of each course are offered, with each section having the
attributes section number [Sec#] and the year and quarter in which the
section was offered ([Year] and [Qtr]). Section numbers uniquely identify
each section.

129

129

Discussion of the requirements – cont.

• The sections being offered during the current quarter are in a subclass
CURRENT_SECTION of SECTION, with the defining predicate Qtr =
Current_qtr and Year = Current_year. Each section is related to the
instructor who taught or is teaching it ([TEACH]), if that instructor is in the
database.
• The category INSTRUCTOR_RESEARCHER is a subset of the union of
FACULTY and GRAD_STUDENT and includes all faculty, as well as graduate
students who are supported by teaching or research.
• Finally, the entity type GRANT keeps track of research grants and
contracts awarded to the university. Each grant has attributes grant title
[Title], grant number [No], the awarding agency [Agency], and the
starting date [St_date].
• A grant is related to one principal investigator [PI] and to all researchers it
supports [SUPPORT].
• Each instance of support has as attributes the starting date of support
[Start], the ending date of the support (if known) [End], and the
percentage of time being spent on the project [Time] by the researcher
being supported.
130

130

65
 3/4/2024

131

131

Example of Other Notation:

UML Class Diagrams
 UML methodology
 Used extensively in software design
 Many types of diagrams for various software
design purposes
 We are only concerned with UML class
diagrams
 Entity in ER corresponds to an object in UML

132

132

66
 3/4/2024

Example of Other Notation:

UML Class Diagrams (cont’d.)
 Class includes three sections:
 Top section gives the class name
 Middle section includes the attributes;
 Last section includes operations that can be applied to individual
objects
 Example:
 Consider the EMPLOYEE class (See next slide).
 Its attributes are Name, Ssn, Bdate, Sex, Address, and Salary.
 The designer can optionally specify the domain of an attribute if
desired, by placing a colon (:) followed by the domain name or
description
 A composite attribute is modeled as a structured domain
 Example: the Name attribute of EMPLOYEE.
 A multivalued attribute will generally be modeled as a
separate class
 Example: the LOCATION class.

133

133

134

134

67
 3/4/2024

135

135

Relationships in UML class diagram

• Relationship types are called associations in UML terminology
• Relationship instances are called links.
• A binary association (binary relationship type) is represented as a line connecting
the participating classes (entity types), and may optionally have a name.
• A relationship attribute, called a link attribute, is placed in a box that is connected
to the association’s line by a dashed line.
• The (min, max) is used to specify relationship constraints, which are called
multiplicities in UML terminology.
• Multiplicities are specified in the form min..max, and an asterisk (*) indicates no
maximum limit on participation.
• However, the multiplicities are placed on the opposite ends of the relationship
when compared with the notation discussed
• In UML, a single asterisk indicates a multiplicity of 0..*, and a single 1 indicates a
multiplicity of 1..1.
• A recursive relationship is called a reflexive association in UML, and the role
names—like the multiplicities—are placed at the opposite ends of an association
when compared with the placing of role names.

136

136

68
 3/4/2024

UML relationships – cont.

• In UML, there are two types of relationships:
association and aggregation.
• Aggregation is meant to represent a relationship
between a whole object and its component parts, and
it has a distinct diagrammatic notation.
– The locations of a department and the single location of a
project as aggregations.
• aggregation and association do not have different
structural properties, and the choice as to which type
of relationship to use is somewhat subjective.
• In the ER model, both are represented as relationships.

137

137

UML relationships – cont.

• UML also distinguishes between unidirectional and bidirectional
associations (or aggregations).
• In the unidirectional case, the line connecting the classes is
displayed with an arrow to indicate that only one direction for
accessing related objects is needed. If no arrow is displayed, the
bidirectional case is assumed, which is the default.
– For example, if we always expect to access the manager of a
department starting from a DEPARTMENT object, we would draw the
association line representing the MANAGES association with an arrow
from DEPARTMENT to EMPLOYEE.
• In addition, relationship instances may be specified to be ordered.
For example, we could specify that the employee objects related to
each department through the WORKS_FOR association
(relationship) should be ordered by their Salary attribute values.

138

138

69
 3/4/2024

UML relationships – cont.

• Association (relationship) names are optional in UML, and relationship
attributes are displayed in a box attached with a dashed line to the line
representing the association/aggregation (e.g: Start_date and Hours).
• The operations in class are derived from the functional requirements of
the application
• Specifying the operation name is sufficient, but can be refined to include
argument types(parameters) and description.
– One can specify more with UML such as sequence diagrams.
• Weak entities modeled using the construct called qualified association (or
qualified aggregation) in UML
– represent both the identifying relationship and the partial key
– placed in a box attached to the owner class (e.g.: DEPENDENT)
– The partial key Dependent_name is called the discriminator in UML
terminology, since its value distinguishes the objects associated with (related
to) the same EMPLOYEE.
• UML is much more than that, here we only illustrate one popular type of
alternative diagrammatic notation that can be used for representing ER
modeling concepts.

139

139

Representing Specialization and

Generalization in UML Class Diagrams
• The basic notation for
specialization/generalization is to connect the
subclasses by vertical lines to a base of a triangle
which points to the superclass.
• A blank triangle indicates a
specialization/generalization with the disjoint
constraint, and a filled triangle indicates an
overlapping constraint.
• The root superclass is called the base class, and
the subclasses (leaf nodes) are called leaf classes.
140

140

70
 3/4/2024

141

141

142

142

71
 3/4/2024

What to do?
• Use Microsoft Visio or Draw.io to build your
project databse, using Crow’s foot notation
and UML notation.
– What is the difference between Chen and Crow
notations?
• Use Microsoft Word to write your project
requirments.

143

143

Relational Database Design by ER- and

EER-to-Relational Mapping
• For each regular (strong) entity type E in the ER
schema, create a relation R that includes all the
simple attributes of E.
• Choose one of the key attributes of E as the
primary key for R.
• The foreign key and relationship attributes, if any,
are not included yet; they will be added later.
– These include the attributes Super_ssn and Dno of
EMPLOYEE, Mgr_ssn and Mgr_start_date of
DEPARTMENT, and Dnum of PROJECT.

144

144

72
 3/4/2024

Weak entity mapping

• For each weak entity type W in the ER schema with owner entity
type E, create a relation R and include all simple attributes of W as
attributes of R.
• In addition, include as foreign key attributes of R, the primary key
attribute(s) of the relation(s) that correspond to the owner entity
type(s)
• The primary key of R is the combination of the primary key(s) of the
owner(s) and the partial key of the weak entity type W, if any.
• It is common to choose the propagate (CASCADE) option for the
referential triggered action on the foreign key in the relation
corresponding to the weak entity type, since a weak entity has an
existence dependency on its owner entity. This can be used for both
ON UPDATE and ON DELETE.

145

145

Binary 1:1 relationship

• For each binary 1:1 relationship type R in the
ER schema, identify the relations S and T that
correspond to the entity types participating in
R.
• There are three possible approaches:
– (1) the foreign key approach,
– (2) the merged relationship approach, and
– (3) the crossreference or relationship relation
approach

146

146

73
 3/4/2024

Binary relationship mapping

• Foreign key approach: Choose one of the
relations—S, say—and include as a foreign
key in S the primary key of T.
• It is better to choose an entity type with total
participation in R in the role of S. Include all
the simple attributes (or simple components
of composite attributes) of the 1:1
relationship type R as attributes of S.

147

147

Binary relationship mapping – cont.

• Merged relation approach: An alternative
mapping of a 1:1 relationship type is to merge
the two entity types and the relationship into
a single relation.
• This is possible when both participations are
total, as this would indicate that the two
tables will have the exact same number of
tuples at all times.

148

148

74
 3/4/2024

Binary relationship mapping – cont.

• Cross-reference or relationship relation
approach: The third option is to set up a third
relation R for the purpose of cross-referencing
the primary keys of the two relations S and T
representing the entity types.
• This approach is required for binary M:N
relationships. The relation R is called a
relationship relation (or sometimes a lookup
table), because each tuple in R represents a
relationship instance that relates one tuple from
S with one tuple from T

149

149

Binary 1:N relationship

• For each regular binary 1:N relationship type R, identify
the relation S that represents the participating entity
type at the N-side of the relationship type.
• Include as foreign key in S the primary key of the
relation T that represents the other entity type
participating in R;
– we do this because each entity instance on the N-side is
related to at most one entity instance on the 1-side of the
relationship type.
• Include any simple attributes (or simple components of
composite attributes) of the 1:N relationship type as
attributes of S.

150

150

75
 3/4/2024

151

151

Binary M:N relationships

• For each binary M:N relationship type R, create a new
relation S to represent R. Include as foreign key attributes
in S the primary keys of the relations that represent the
participating entity types; their combination will form the
primary key of S.
• Also include any simple attributes of the M:N relationship
type (or simple components of composite attributes) as
attributes of S.
• Notice that we cannot represent an M:N relationship type
by a single foreign key attribute in one of the participating
relations (as we did for 1:1 or 1:N relationship types)
because of the M:N cardinality ratio; we must create a
separate relationship relation S.

152

152

76
 3/4/2024

Multivalued attribute
• For each multivalued attribute A, create a new
relation R.
• This relation R will include
– an attribute corresponding to A, plus
– the primary key attribute K—as a foreign key in R—of
the relation that represents the entity type or
relationship type that has A as a multivalued attribute.
• The primary key of R is the combination of A and
K. If the multivalued attribute is composite, we
include its simple components.

153

153

Summary

154

154

77
 3/4/2024

Options for Mapping Specialization or

Generalization
• Convert each specialization with m subclasses {S1, S2, ..., Sm} and
(generalized) superclass C, where the attributes of C are {k, a1, ...an}
and k is the (primary) key, into relation schemas using one of the
following options:
– 8A: Multiple relations—superclass and subclasses. Create a relation L
for C with attributes Attrs(L) = {k, a1, ...an} and PK(L) = k. Create a
relation Li for each subclass Si, 1 ≤ i ≤ m, with the attributes Attrs(Li) =
{k} ∪ {attributes of Si} and PK(Li) = k. This option works for any
specialization (total or partial, disjoint or overlapping).
– 8B: Multiple relations—subclass relations only. Create a relation Li for
each subclass Si, 1 ≤ i ≤ m, with the attributes Attrs(Li) = {attributes of
Si} ∪ {k, a1, ...an} and PK(Li) = k. This option only works for a
specialization whose subclasses are total (every entity in the
superclass must belong to (at least) one of the subclasses).
Additionally, it is only recommended if the specialization has the
disjointedness constraint. If the specialization is overlapping, the same
entity may be duplicated in several relations.

155

155

Options for Mapping Specialization or

Generalization – cont.
• 8C: Single relation with one type attribute. Create a single relation
L with attributes Attrs(L) = {k, a1, ...an} ∪ {attributes of S1} ∪ ... ∪
{attributes of Sm} ∪ {t} and PK(L) = k. The attribute t is called a type
(or discriminating) attribute whose value indicates the subclass to
which each tuple belongs, if any. This option works only for a
specialization whose subclasses are disjoint, and has the potential
for generating many NULL values if many specific attributes exist in
the subclasses.
• 8D: Single relation with multiple type attributes. Create a single
relation schema L with attributes Attrs(L) = {k, a1, ...an} ∪ {attributes
of S1} ∪ ... ∪ {attributes of Sm} ∪ {t1, t2, ..., tm} and PK(L) = k. Each ti,
1 ≤ i ≤ m, is a Boolean type attribute indicating whether a tuple
belongs to subclass Si. This option is used for a specialization whose
subclasses are overlapping (but will also work for a disjoint
specialization).

156

156

78
 3/4/2024

See next slide for 8.4, 8.3b, 8.5 figures

157

157

158

158

79
 3/4/2024

Mapping of Shared Subclasses

(Multiple Inheritance)
• A shared subclass, such as ENGINEERING_MANAGER, is a subclass
of several superclasses, indicating multiple inheritance.
• These classes must all have the same key attribute; otherwise, the
shared subclass would be modeled as a category (union type).
• We can apply any of the options discussed in step 8 to a shared
subclass, subject to the restrictions discussed in step 8 of the
mapping algorithm.
• In Figure 9.6, see next slide, options 8C and 8D are used for the
shared subclass STUDENT_ASSISTANT.
• Option 8C is used in the EMPLOYEE relation (Employee_type
attribute) and option 8D is used in the STUDENT relation
(Student_assist_flag attribute).

159

159

160

160

80
 3/4/2024

161

161

Download tools
• Download PostgreSQL database from
https://www.postgresql.org/download/
• Download SQL developer from
https://www.oracle.com/database/sqldeveloper/techn
ologies/download/
– You may be required to sign up before download, its free.
• Download and install JDK if you are not already have it
• Download and install Netbeans if you are not already
have it from https://netbeans.apache.org/

162

162

81
 3/4/2024

Install PostgreSQL database

• Install PostgreSQL database.
• The default administrator username is
postgres
• During installation you will be asked to enter a
password for the postgres username.
– Use a password that is easy to remember.
• At the end of installation start the PostgreSQL
Application Stack Builder

163

163

Use Application Stack Builder to install

pgJDBC

164

164

82
 3/4/2024

Extract and configure SQL Developer

• Extract and start SQL Developer
• To connect to the PostgreSQL database, from the SQL developer menu go to
Tools>Preferences…>Database> Third Party JDBC Drivers.
• Add Entry to the pgJDBC drivers you added using the Application stack Builder, see
previous slide.

165

165

Create connection to PostgreSQL

166

166

83
 3/4/2024

Create New user to use with your

project
• Execute the following commands using the postgres user to create a new user
– CREATE USER noora WITH PASSWORD '654321';
– GRANT CONNECT ON DATABASE postgres TO noora;
– You can either use the public schema
• GRANT ALL ON SCHEMA public TO noora;
– Or create a new schema and set it to the default schema for the created user
• CREATE SCHEMA COMPANY AUTHORIZATION noora;
• GRANT USAGE ON SCHEMA COMPANY TO noora;
• ALTER USER noora SET search_path TO COMPANY;

– In SQL developer, create a new connection, put first the Postgres as username, choose the
new database, then change the username and password to the ones you have created.

• Connect to the database using the newly created user.

• Optionally you can create a new database
– create database my_project_db;
– grant all privileges on database my_project_db to ahmad;
– Then you can create a schema and authorize the schema to a user.

167

167

Basic SQL
 SQL language
 Considered one of the major reasons for the
commercial success of relational databases
 SQL
 Structured Query Language
 Statements for data definitions, queries, and
updates (both DDL and DML)
 Core specification
 Plus specialized extensions

168

168

84
 3/4/2024

Schema and Catalog Concepts in

SQL
 SQL schema
 Identified by a schema name
 Includes an authorization identifier and
descriptors for each element
 Schema elements include
 Tables, constraints, views, domains, and other
constructs
 Each statement in SQL ends with a
semicolon

169

169

SQL Data Definition and Data

Types
 Terminology:
 Table, row, and column used for relational
model terms relation, tuple, and attribute
 CREATE statement
 Main SQL command for data definition

170

170

85
 3/4/2024

The CREATE TABLE Command in

SQL (cont’d.)
 Base tables (base relations)
 Relation and its tuples are actually created
and stored as a file by the DBMS
 Virtual relations
 Created through the CREATE VIEW
statement

171

171

Objectives
• Explain how tables are created and
modified.
• Explain what features are available to
control what data is stored in the tables.
• Discuss how tables can be organized into
schemas, and how privileges can be
assigned to tables.
• Finally, we will briefly look at other features
that affect the data storage, such as
inheritance, table partitioning, views,
functions, and triggers.
172

172

86
 3/4/2024

Table Basics
• Like a table on paper: It consists of rows and columns.
• SQL does not make any guarantees about the order of
the rows in a table.
• When a table is read, the rows will appear in an
unspecified order, unless sorting is explicitly requested.
• Each column has a data type.
• The data type constrains the set of possible values that
can be assigned to a column and assigns semantics to
the data stored in the column so that it can be used for
computations.

173

173

The CREATE TABLE Command in

SQL
• Specify a new relation
– Provide name
– the table name,
– the name of each column,
– the data type of each column,
– and the size of each column.
– Specify constraints
• Can optionally specify schema:
– CREATE TABLE COMPANY.EMPLOYEE ...
or
– CREATE TABLE EMPLOYEE ...
– In oracle schema is created by default when
creating a user.
– In PostgreSQL you have to create the schema

174

174

87
 3/4/2024

Table Basics – cont.

• Some of the frequently used data types are integer for
whole numbers, numeric for possibly fractional numbers,
text for character strings, date for dates, time for time-of-
day values, and timestamp for values containing both date
and time.
CREATE TABLE products (
• Example: product_no integer,
name text,
price numeric
);

• In Postgresql, there is a limit on how many columns a table

can contain. Depending on the column types, it is between
250 and 1600. However, defining a table with anywhere
near this many columns is highly unusual and often a
questionable design.

175

175

Delete table
• If you no longer need a table, you can remove it using
the DROP TABLE command. For example: DROP TABLE
products;
• Attempting to drop a table that does not exist is an
error.
• Nevertheless, it is common in SQL script files to
unconditionally try to drop each table before creating
it, ignoring any error messages, so that the script works
whether or not the table exists.
• In Postgresql, If you like, you can use the DROP TABLE
IF EXISTS variant to avoid the error messages, but this
is not standard SQL.

176

176

88
 3/4/2024

Data types
• PostgreSQL has many built-in data types; it also
allows users to add new types using CREATE TYPE
command.
• The main types can be grouped into
– Boolean Type
– Character Type
– Integer Type and Numeric Type
– Date Type, Time Type, Interval Type, and TimeStamp
Type
– Array Type
– Json Type

177

177

PostgreSQL Numeric Types

Name Storage Size Description Range
smallint 2 bytes small-range integer -32768 to +32767
integer 4 bytes typical choice for integer -2147483648 to +2147483647
bigint 8 bytes large-range integer -9223372036854775808 to
+9223372036854775807

decimal variable user-specified precision, exact up to 131072 digits before the decimal point;
up to 16383 digits after the decimal point

numeric variable user-specified precision, exact up to 131072 digits before the decimal point;
up to 16383 digits after the decimal point

real 4 bytes variable-precision, inexact 6 decimal digits precision

double precision 8 bytes variable-precision, inexact 15 decimal digits precision
smallserial 2 bytes small autoincrementing integer 1 to 32767

serial 4 bytes autoincrementing integer 1 to 2147483647

bigserial 8 bytes large autoincrementing integer 1 to 9223372036854775807

178

178

89
 3/4/2024

PostgreSQL Numeric Types – cont.

• The type numeric can store numbers with a very large number of digits. It is
especially recommended for storing monetary amounts and other quantities
where exactness is required.
• Calculations on numeric values are very slow compared to the integer types, or to
the floating-point types.
• NUMERIC(precision, scale)
– The precision of a numeric is the total count of significant digits in the whole number, that is,
the number of digits to both sides of the decimal point.
– The scale of a numeric is the count of decimal digits in the fractional part, to the right of the
decimal point.
– Example: The number 23.5141 has a precision of 6 and a scale of 4. Integers can be considered
to have a scale of zero.
– without any precision or scale, one creates an “unconstrained numeric” column in which
numeric values of any length can be stored, up to the implementation limits.
– The maximum precision that can be explicitly specified in a numeric type declaration is 1000.
An unconstrained numeric column is subject to the limits described in the previous slide.
– Normally scale should be smaller than precision.
– Example: NUMERIC(3, 1); for values between -99.9 and 99.9, inclusive.
• Numeric type can also accept values: Infinity, -Infinity, NaN.
– Infinity values can only be stored in an unconstrained numeric column

179

179

Character Types
Name Description
character varying(n), varchar(n) variable-length with limit
character(n), char(n), bpchar(n) fixed-length, blank padded
text variable unlimited length

• The length n must be greater than zero and cannot exceed 10485760. character
without length specifier is equivalent to character(1). If character varying is used
without length specifier, the type accepts strings of any size.
• An attempt to store a longer string into a column of these types will result in an
error, unless the excess characters are all spaces, in which case the string will be
truncated to the maximum length.
• Trailing spaces are treated as semantically insignificant and disregarded when
comparing two values of type character. Trailing spaces are removed when
converting a character value to one of the other string types.
• Note that trailing spaces are semantically significant in character varying and text
values, and when using pattern matching, that is LIKE and regular expressions.
180

180

90
 3/4/2024

Date/Time Types
Storage
Name Size Description Low Value High Value Resolution
timestamp [ (p) ] [ without time zone ] 8 bytes both date and time (no time 4713 BC 294276 AD 1 microsecond
zone)
timestamp [ (p) ] with time zone 8 bytes both date and time, with time 4713 BC 294276 AD 1 microsecond
zone
date 4 bytes date (no time of day) 4713 BC 5874897 AD 1 day
time [ (p) ] [ without time zone ] 8 bytes time of day (no date) 00:00:00 24:00:00 1 microsecond
time [ (p) ] with time zone 12 bytes time of day (no date), with 00:00:00+155 24:00:00- 1 microsecond
time zone 9 1559
interval [ fields ] [ (p) ] 16 bytes time interval -178000000 178000000 1 microsecond
years years

• time, timestamp, and interval accept an optional precision value p which specifies the
number of fractional digits retained in the seconds field. The allowed range of p is
from 0 to 6.
• time alone is equivalent to time without time zone.
• The interval type has an additional option, which is to restrict the set of stored fields
by writing one of these phrases: YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, YEAR
TO MONTH, DAY TO HOUR, DAY TO MINUTE, DAY TO SECOND, HOUR TO MINUTE,
HOUR TO SECOND, and MINUTE TO SECOND.
• Note that if both fields and p are specified, the fields must include SECOND, since
the precision applies only to the seconds.
181

181

Date/Time Types – cont.

• Date and time input is accepted in almost any reasonable format
– yyyy-mm-dd is the recommended format for date field, its the ISO
8601 format.
– any date or time literal input needs to be enclosed in single quotes
• Set the DateStyle parameter to MDY to select month-day-year
interpretation, DMY to select day-month-year interpretation, or
YMD to select year-month-day interpretation.
– set datestyle to [your new datestyle];
• For more information about valid values and time zone refer to
https://www.postgresql.org/docs/current/datatype-datetime.html

182

182

91
 3/4/2024

Other data types

• Monetary Types
Name Storage Size Description Range
money 8 bytes currency amount -92233720368547758.08 to
+92233720368547758.07
• Binary and bit Data Types
Name Storage Size Description
bytea 1 or 4 bytes plus the actual binary string variable-length binary string
bit [ (n) ] fixed-length bit string
bit varying [ (n) ] varbit [ (n) ] variable-length bit string

• For more data types and information refer to

https://www.postgresql.org/docs/current/dat
atype.html
183

183

Compatibility with standard SQL

• The following types (or spellings thereof) are
specified by SQL: bigint, bit, bit varying,
boolean, char, character varying, character,
varchar, date, double precision, integer,
interval, numeric, decimal, real, smallint, time
(with or without time zone), timestamp (with
or without time zone), xml.

184

184

92
 3/4/2024

Default Values
• A column can be assigned a default value.
• When a new row is created and no values are
specified for some of the columns, those columns
will be filled with their respective default values.
• A data manipulation command can also request
explicitly that a column be set to its default value,
without having to know what that value is.
• If no default value is declared explicitly, the
default value is the null value.
• Example: CREATE TABLE products (
product_no integer,
name text,
price numeric DEFAULT 9.99
);

185

185

Default Values – cont.

• The default value can be an expression, which will be evaluated whenever
the default value is inserted (not when the table is created).
• A common example is for a timestamp column to have a default of
CURRENT_TIMESTAMP, so that it gets set to the time of row insertion.

CREATE TABLE note(

note_id serial PRIMARY KEY,
message varchar(255) NOT NULL,
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

• Another common example is generating a “serial number” for each row. In

PostgreSQL this is typically done by something like:
CREATE TABLE products (
product_no integer DEFAULT nextval('products_product_no_seq'),
...
); --products_product_no_seq is a sequence name. We will study sequences later.
186

186

93
 3/4/2024

Generated Columns
• A generated column is a special column that is always
computed from other columns.
• There are two kinds of generated columns: stored and virtual.
– A stored generated column is computed when it is written (inserted or
updated) and occupies storage as if it were a normal column.
– A virtual generated column occupies no storage and is computed
when it is read.
– PostgreSQL currently implements only stored generated columns.
• Example CREATE TABLE people (
...,
height_cm numeric,
height_in numeric GENERATED ALWAYS AS (height_cm / 2.54) STORED
);

**The keyword STORED must be specified to choose the stored kind of generated column.

187

187

CREATE TYPE — define a new

data type
• In PostgreSQL you can define a new datatype using the
command CREATE TYPE
• The new data type can be a
– A composite type
– An ENUM
– A Range
– A base type
– An Array type
• For more information see
– https://www.postgresql.org/docs/current/datatype.html
– https://www.postgresql.org/docs/16/sql-createtype.html

188

188

94
 3/4/2024

Attribute Data Types and Domains

in SQL (cont’d.)
 Domain
 Name used with the attribute specification
 Makes it easier to change the data type for a domain that
is used by numerous attributes
 Improves schema readability
 Example:
• CREATE DOMAIN SSN_TYPE AS CHAR(9);
• Not supported by oracle, instead one can define
a table and use its primary key as foreign key
datatype
– CREATE TABLE gender_domain (
gender VARCHAR2(1) PRIMARY KEY,
CONSTRAINT ch_gen CHECK (gender IN ('M', 'F'))
);

189

189

PostgreSQL constraints
• PostgreSQL provides you with different
constraints
– Check Constraints
– Not-Null Constraints
– Unique Constraints
– Primary Keys
– Foreign Keys
– Exclusion Constraints

190

190

95
 3/4/2024

SQL create table DDL statement for the

company database schema
CREATE SCHEMA company
AUTHORIZATION postgres;

CREATE TYPE company."genderType" AS ENUM

('F', 'M');

CREATE TABLE company."EMPLOYEE"

(
"Fname" character varying(15) NOT NULL,
"Minit" character,
CREATE TABLE company."DEPARTMENT"
"Lname" character varying(15) NOT NULL,
(
"Ssn" character(9) NOT NULL,
"Dname" character varying(15) NOT NULL,
"Bdate" date,
"Dnumber" integer NOT NULL,
"Address" character varying(30),
"Mgr_ssn" character(9) NOT NULL,
"Gender" company."genderType",
"Mgr_start_date" date,
"Salary" numeric(10, 2),
PRIMARY KEY ("Dnumber"),
"Super_ssn" character(9),
UNIQUE ("Dname")
"Dno" integer,
);
PRIMARY KEY ("Ssn")
); 191

191

DDL for company database schema

CREATE TABLE company."DEPT_LOCATIONS"
(
"Dnumber" integer NOT NULL, CREATE TABLE company."WORKS_ON"
"Dlocation" character varying(15) NOT NULL, (
PRIMARY KEY ("Dnumber", "Dlocation") "Essn" character(9) NOT NULL,
); "Pno" integer NOT NULL,
"Hours" numeric(3, 1) NOT NULL,
CREATE TABLE company."Project" PRIMARY KEY ("Essn", "Pno")
( );
"Pname" character varying(15) NOT NULL,
"Pnumber" integer NOT NULL,
"Plocation" character varying(15), CREATE TABLE company."DEPENDENT"
"Dnum" integer NOT NULL, (
PRIMARY KEY ("Pnumber"), "Essn" character(9) NOT NULL,
UNIQUE ("Pname") "Dependent_name" character varying(15) NOT NULL,
); "Gender" company."genderType",
"Bdate" date,
"Relationship" character varying(8),
PRIMARY KEY ("Essn", "Dependent_name")
);

192

192

96
 3/4/2024

The CREATE TABLE Command in

SQL (cont’d.)
 Some foreign keys may cause errors
 Specified either via:
• Circular references
• Or because they refer to a table that has not yet
been created

193

193

Foreign keys may cause errors

194

194

97
 3/4/2024

Foreign keys for the company schema

ALTER TABLE IF EXISTS company."DEPARTMENT"
ADD CONSTRAINT "DEPARTMENT_MGR_SSN_FK" FOREIGN KEY ("Mgr_ssn")
REFERENCES company."EMPLOYEE" ("Ssn")
ALTER TABLE IF EXISTS company."EMPLOYEE"
ADD FOREIGN KEY ("Super_ssn")
REFERENCES company."EMPLOYEE" ("Ssn")
ALTER TABLE IF EXISTS company."EMPLOYEE"
ADD FOREIGN KEY ("Dno")
REFERENCES company."DEPARTMENT" ("Dnumber")
ALTER TABLE IF EXISTS company."DEPT_LOCATIONS"
ADD FOREIGN KEY ("Dnumber")
REFERENCES company."DEPARTMENT" ("Dnumber")
ALTER TABLE IF EXISTS company."Project"
ADD FOREIGN KEY ("Dnum")
REFERENCES company."DEPARTMENT" ("Dnumber")

195

195

Foreign keys for the company schema

– cont.
ALTER TABLE IF EXISTS company."WORKS_ON"
ADD FOREIGN KEY ("Pno")
REFERENCES company."Project" ("Pnumber")

ALTER TABLE IF EXISTS company."WORKS_ON"

ADD FOREIGN KEY ("Essn")
REFERENCES company."EMPLOYEE" ("Ssn")

ALTER TABLE IF EXISTS company."DEPENDENT"

ADD FOREIGN KEY ("Essn")
REFERENCES company."EMPLOYEE" ("Ssn")

196

196

98
 3/4/2024

Specifying Constraints in SQL

 Basic constraints:
 Key and referential integrity constraints
 Restrictions on attribute domains and NULLs
 Constraints on individual tuples within a
relation

197

197

Table level
 A table-level constraint references one or more columns and
is defined separately from the definitions of the columns.

 Normally, it is written after all columns are defined.

 All constraints can be defined at the table level except for the
NOT NULL constraint.

[CONSTRAINT constraint_name] constraint_typ (Column, . . .),

Example:
CONSTRAIN location_roomid_pk PRIMARY KEY(Roomid)

198

198

99
 3/4/2024

The Primary Key Constrain

 The PRIMARY KEY constraint is also known as
the entity integrity constraint

 It creates a primary key for the table. A table can

have only one primary key constraint.

 If a table uses more than one column as its

primary key (i.e., a composite key), the key can
only be declared at the table level.

199

199

The Primary Key Constrain

 At the column level, the constraints is
defined by
DeptId NUMBER (2) CONSTRAINT dept_deptid_pk PRIMARY KEY,

 At the table level, the constraint is

defined by
CONSTRAINT dept_deptid_pk PRIMARY KEY(DeptId),

200

200

100
 3/4/2024

The NOT NULL Constraint

 The NOT NULL constraint ensures that the
column has a value and the value is not a null
value

 A space or a numeric zero is not a null value

 At the column level ONLY, the constraint is

defined by:

Name VARCHAR2(15) CONSTRAINT faculty_name_nn NOT NULL,

201

201

The UNIQUE Constraint

 The UNIQUE constraint requires that every value in a
column or set of columns be unique.

 At the table level, the constraint is defined by

CONSTRAINT dept_deptname_uk UNIQUE(DeptName),

 At the column level, the constraint is defined by:

DeptName VARCHAR2(12) CONSTRAINT dept_deptname_uk
UNIQUE,

202

202

101
 3/4/2024

The CHECK Constraint

 The CHECK constraint defines a condition that every row must
satisfy

 At the column level, the constraint is defined by

DeptId NUMBER(2) CONSTRAINT dept_deptid_cc
CHECK((DeptId >= 10) and (DeptId <= 99)),

 At the table level, the constraint is defined by:

CONSTRAINT dept_deptid_cc
CHECK((DeptId >= 10) and (DeptId <= 99)),

203

203

The FOREIGN KEY Constraint

 The FOREIGN KEY constraint is also
known as the referential integrity
constraint.

 It uses a column or columns as a

foreign key, and it establishes a
relationship with the primary key of the
same or another table.

204

204

102
 3/4/2024

The FOREIGN KEY Constraint

 To establish a foreign key in a table, the
other referenced table and its primary key
must already exist.

 Foreign key and referenced primary key

columns need not have the same name, but a
foreign key value must match the value in
the parent table’s primary key value or be
NULL
205

205

The FOREIGN KEY Constraint

 At the table level ONLY
CONSTRAINT student_facultyid_fk FOREIGN KEY(FacultyId)
REFERENCES faculty (FacultyId),

206

206

103
 3/4/2024

Specifying Key and Referential

Integrity Constraints (cont’d.)
 FOREIGN KEY clause
 Default operation: reject update on
violation
 Attach referential triggered action clause
• Options include SET NULL, CASCADE, and SET
DEFAULT
• Action taken by the DBMS for SET NULL or
SET DEFAULT is the same for both ON
DELETE and ON UPDATE
• CASCADE option suitable for “relationship”
relations 207

207

208

208

104
 3/4/2024

CREATING AN ORACLE TABLE

A table is created as soon as the CREATE statement is
successfully executed by the Oracle server. The general
syntax of CREATE TABLE statement is

CREATE TABLE [schema.] tablename

(column1 datatype [CONSTRAINT constraint_name]

constraint_type . . .,

(column2 datatype [CONSTRAINT constraint_name]

constraint_type,

[CONSTRAINT constraint_name] constraint_type (column, . . .

), . . . );

209

209

Viewing a Table’s Structure

 The SQL*Plus command to view a
table’s structure is DESCRIBE, which
does not need a semicolon at the end
because it is not a SQL statement.

SQL> DESCRIBE employee

210

210

105
 3/4/2024

Adding a New Column to an

Existing Table
 The general syntax to add a column to
an existing table is

ALTER TABLE tablename

ADD columnname datatype;

SQL> ALTER TABLE student

2 ADD SocialSecurity CHAR(9);
Table altered.
SQL>

211

211

Modifying an Existing Column

 The general syntax to modify an existing column is
SQL> ALTER TABLE student
2 MODIFY SocialSecurity VARCHAR2(11);
ALTER TABLE tablename Table altered.
MODIFY columnname newdatatype; SQL>

where newdatatype is the new data type or the new size for the column.

In PostgreSQL, to modify a type one can use ALTER COLUMN

ALTER TABLE employee
ALTER COLUMN first_name TYPE varchar(80),
ALTER COLUMN last_name TYPE varchar(80);
To rename a column
ALTER TABLE employee RENAME COLUMN birthdate TO BoD;
To add Not null constraint
ALTER TABLE employee ALTER COLUMN gender SET NOT NULL;

212

212

106
 3/4/2024

Adding a Constraint
 To add a constraint using ALTER TABLE, the syntax for
table level constraint is used. The general syntax of
ALTER TABLE is
ALTER TABLE tablename
ADD [CONSTRAINT constraint_name]
constraint_type (column, …),

SQL> ALTER TABLE COURSE

2 ADD CONSTRAINT COURSE_PREREQ_FK FOREIGN KEY (PREREQ)
3 REFERENCES COURSE(COURSEID);
Table altered.
SQL>

213

213

Displaying Table Information

 When a user creates a table or many tables in the database, Oracle
tracks them using its own data dictionary

 Viewing a User’s Table Names

SELECT TABLE_NAME FROM USER_TABLES;

 To display all information:

SELECT * FROM USER_TABLES;

In PostgreSQL,
pg_tables and information_schema.tables both contain information
about tables. information_schema is the standard ANSI SQL way to
query the data dictionary

214

214

107
 3/4/2024

Dropping a Column
 The general syntax is
ALTER TABLE tablename DROP COLUMN columnname;

215

215

Dropping a Table
 The general syntax is
DROP TABLE tablename [CASCADE
CONSTRAINTS];

 For example,
DROP TABLE sample;

 Oracle displays a “Table dropped” message when a

table is successfully dropped.
 If you add optional CASCADE CONSTRAINTS clause,
it removes foreign key references to the table also.

216

216

108
 3/4/2024

Relational Algebra: 5 Basic Operations

• Selection (s ) Selects a subset of rows from relation
(horizontal).
• Projection ( p ) Retains only wanted columns from relation
(vertical).
• Cross-product (x) Allows us to combine two relations.
• Set-difference (–) Tuples in r1, but not in r2.
• Union ( ) Tuples in r1 and/or in r2.

Since each operation returns a relation, operations can be

composed! (Algebra is “closed”.)

217

217

Example Instances
R1 sid bid day
22 101 10/10/96
58 103 11/12/96

bid bname color S1 sid sname rating age

101 Interlake blue 22 dustin 7 45.0
102 Interlake red 31 lubber 8 55.5
103 Clipper green 58 rusty 10 35.0
104 Marine red
Boats S2 sid sname rating age
28 yuppy 9 35.0
31 lubber 8 55.5
44 guppy 5 35.0
58 rusty 10 35.0 218

218

109
 3/4/2024

Projection
• Examples: p age(;S2) p (S2)
sname,rating
• Retains only attributes that are in the “projection list”.
• Schema of result:
– exactly the fields in the projection list, with the same
names that they had in the input relation.
• Projection operator has to eliminate duplicates (How do
they arise? Why remove them?)
– Note: real systems typically don’t do duplicate
elimination unless the user explicitly asks for it. (Why
not?)

219

219

Projection sname rating

yuppy 9
lubber 8
guppy 5
rusty 10
sid sname rating age p sname,rating (S 2)
28 yuppy 9 35.0
31 lubber 8 55.5
44 guppy 5 35.0
58 rusty 10 35.0 age
S2 35.0
55.5
p age(S2)
220

220

110
 3/4/2024

Selection ()
• Selects rows that satisfy selection condition.
• Result is a relation.
Schema of result is same as that of the input relation.
• Do we need to do duplicate elimination?

sid sname rating age

28 yuppy 9 35.0 sname rating
31 lubber 8 55.5
yuppy 9
44 guppy 5 35.0
58 rusty 10 35.0 rusty 10
s rating >8(S2) p sname,rating(s rating > 8(S2212))
221

Union and Set-Difference

• All of these operations take two input relations, which

must be union-compatible:
– Same number of fields.
– `Corresponding’ fields have the same type.

• For which, if any, is duplicate elimination required?

222

222

111
 3/4/2024

Union
sid sname rating age sid sname rating age
22 dustin 7 45.0 22 dustin 7 45.0
31 lubber 8 55.5
31 lubber 8 55.5
58 rusty 10 35.0
58 rusty 10 35.0
44 guppy 5 35.0
S1
28 yuppy 9 35.0
sid sname rating age S1 S2
28 yuppy 9 35.0
31 lubber 8 55.5
44 guppy 5 35.0
58 rusty 10 35.0
S2
223

223

Set Difference
sid sname rating age
sid sname rating age
22 dustin 7 45.0
22 dustin 7 45.0
31 lubber 8 55.5 S1- S2
58 rusty 10 35.0
S1

sid sname rating age sid sname rating age

28 yuppy 9 35.0 28 yuppy 9 35.0
31 lubber 8 55.5 44 guppy 5 35.0
44 guppy 5 35.0
S2 – S1
58 rusty 10 35.0
S2
224

224

112
 3/4/2024

Cross-Product
• S1 x R1: Each row of S1 paired with each row of R1.
• Q: How many rows in the result?
• Result schema has one field per field of S1 and R1, with
field names `inherited’ if possible.
– May have a naming conflict: Both S1 and R1 have a field
with the same name.
– In this case, can use the renaming operator:
r (C(1® sid1, 5 ® sid2), S1´ R1)

225

225

Cross Product Example

sid sname rating age
sid bid day
22 dustin 7 45.0
22 101 10/10/96 31 lubber 8 55.5
58 103 11/12/96 58 rusty 10 35.0
R1
S1

(sid) sname rating age (sid) bid day

22 dustin 7 45.0 22 101 10/10/96
R1 X S1 = 22 dustin 7 45.0 58 103 11/12/96
31 lubber 8 55.5 22 101 10/10/96
31 lubber 8 55.5 58 103 11/12/96
58 rusty 10 35.0 22 101 10/10/96
58 rusty 10 35.0 58 103 11/12/96
226

226

113
 3/4/2024

Compound Operator: Intersection

• In addition to the 5 basic operators, there are several
additional “Compound Operators”
– These add no computational power to the
language, but are useful shorthands.
– Can be expressed solely with the basic ops.

• Intersection takes two input relations, which must be

union-compatible.
• Q: How to express it using basic operators?
R  S = R - (R - S)
227

227

Intersection
sid sname rating age
22 dustin 7 45.0
31 lubber 8 55.5 sid sname rating age
58 rusty 10 35.0 31 lubber 8 55.5
S1 58 rusty 10 35.0
sid sname rating age
28
31
yuppy
lubber
9
8
35.0
55.5
S1 S 2
44 guppy 5 35.0
58 rusty 10 35.0
S2
228

228

114
 3/4/2024

Compound Operator: Join

• Joins are compound operators involving cross
product, selection, and (sometimes) projection.
• Most common type of join is a “natural join” (often
just called “join”). R S conceptually is:
– Compute R X S
– Select rows where attributes that appear in both relations have equal values
– Project all unique atttributes and one copy of each of the common ones.
• Note: Usually done much more efficiently than this.
• Useful for putting “normalized” relations back
together.

229

229

Natural Join Example

sid sname rating age
sid bid day 22 dustin 7 45.0
22 101 10/10/96 31 lubber 8 55.5
58 103 11/12/96 58 rusty 10 35.0
R1
S1

R1 S1 =

sid sname rating age bid day

22 dustin 7 45.0 101 10/10/96
58 rusty 10 35.0 103 11/12/96

230

230

115
 3/4/2024

Other Types of Joins

• Condition Join (or “theta-join”):
R  c S = s c (R ´ S)

• Result schema same as that of cross-product.

• May have fewer tuples than cross-product.

• Equi-Join: Special case: condition c contains

only conjunction of equalities.

231

231

“Theta” Join Example

sid bid day sid sname rating age
22 101 10/10/96 22 dustin 7 45.0
58 103 11/12/96 31 lubber 8 55.5
58 rusty 10 35.0
R1
S1

S1 R1 =
S1.sid< R1.sid

(sid) sname rating age (sid) bid day

22 dustin 7 45.0 58 103 11/12/96
31 lubber 8 55.5 58 103 11/12/96
232

232

116
 3/4/2024

Compound Operator: Division

• Useful for expressing “for all” queries like:
Find sids of sailors who have reserved all boats.
• For A/B attributes of B are subset of attrs of A.
– May need to “project” to make this happen.
• E.g., let A have 2 fields, x and y; B have only field y:

A B = { x " y Î B($ x, y Î A)}

A/B contains all x tuples such that for every y tuple
in B, there is an xy tuple in A.

233

233

Examples of Division A/B

s n o pno pno pno pno
s1 p1 p2 p2 p1
s1 p2
B1 p4 p2
s1 p3
s1 p4 B2 p4
s2 p1
sno
B3
s2 p2
s1
s3 p2 s2 sno
s4 p2 s3 s1 sno
s4 p4 s4 s4 s1
A A/B1 A/B2 A/B3
234

234

117
 3/4/2024

235

235

Expressing A/B Using Basic Operators

• Division is not essential op; just a useful shorthand.
– (Also true of joins, but joins are so common that systems
implement joins specially.)
• Idea: For A/B, compute all x values that are not
`disqualified’ by some y value in B.
– x value is disqualified if by attaching y value from B, we obtain
an xy tuple that is not in A.

Disqualified x values: p x ((p x ( A) ´ B) - A)

A/B: p x ( A) - Disqualified x values

236

236

118
 3/4/2024

sid bid day

Examples Reserves
22 101 10/10/96
58 103 11/12/96
sid sname rating age
Sailors 22 dustin 7 45.0
31 lubber 8 55.5
58 rusty 10 35.0

Boats
bid bname color
101 Interlake Blue
102 Interlake Red
103 Clipper Green
104 Marine Red
237

237

Ex: Find names of sailors who’ve reserved boat #103

• Solution 1: p sname ((s bid =103 Re serves)  Sailors)

• Solution 2: p sname (s (Re serves  Sailors))

bid =103

238

238

119
 3/4/2024

Ex: Find names of sailors who’ve reserved a red boat

• Information about boat color only available in
Boats; so need an extra join:
p sname ((s Boats)  Re serves  Sailors)
color =' red '

* A more efficient (???) solution:

p sname(p ((p (s Boats)) Res) Sailors)
sid bid color='red '

* A query optimizer can find this given the first solution!

239

239

Find sailors who’ve reserved a red or a green boat

• Can identify all red or green boats, then find

sailors who’ve reserved one of these boats:
r (Tempboats, (s Boats))
color =' red ' Ú color =' green '
p sname(Tempboats  Re serves  Sailors)

240

240

120
 3/4/2024

Find sailors who’ve reserved a red and a green boat

• Previous approach won’t work! Must identify

sailors who’ve reserved red boats, sailors who’ve
reserved green boats, then find the intersection
(note that sid is a key for Sailors):
r (Tempred, p ((s Boats)  Re serves))
sid color =' red '
r (Tempgreen, p ((s Boats)  Re serves))
sid color =' green'
p sname((Tempred  Tempgreen)  Sailors)
241

241

Find the names of sailors who’ve reserved all boats

• Uses division; schemas of the input

relations to / must be carefully chosen:
r (Tempsids ,(p Re serves)/(p Boats ))
sid ,bid bid
p sname (Tempsids  Sailors )

v To find sailors who’ve reserved all ‘Interlake’ boats:

..... /p (s Boats)
bid bname =' Interlake'
242

242

121
 3/4/2024

SQL SELECT STATMENT

252

252

Tables
• We will use the following tables
• create table account (account_number varchar(15) not null,
branch_name varchar(15) not null, balance number not null, primary
key(account_number));

• create table branch (branch_name varchar(15) not null , branch_city

varchar(15) not null, assets number not null, primary
key(branch_name));

• account(account_number, branch_name, balance);

• branch(branch_name, branch_city, assets);

253

253

122
 3/4/2024

Tables
• create table customer (customer_name varchar(15) not null ,
customer_street varchar(12) not null, customer_city varchar(15)
not null, primary key(customer_name));

• create table loan (loan_number varchar(15) not null , branch_name

varchar(15) not null, amount number not null, primary
key(loan_number));

• customer(customer_name, customer_street, customer_city );

• Loan(loan_number, branch_name, amount);

254

254

Tables
• create table depositor (customer_name varchar(15) not null,
account_number varchar(15) not null,
primary key(customer_name, account_number),
foreign key(account_number) references account(account_number),
foreign key(customer_name) references customer(customer_name));

• create table borrower (customer_name varchar(15) not null,

loan_number varchar(15) not null,
primary key(customer_name, loan_number),
foreign key(customer_name) references customer(customer_name),
foreign key(loan_number) references loan(loan_number));

• depositor(customer_name, account_number);
• borrower(customer_name, loan_number);

255

255

123
 3/4/2024

Basic Structure
• A typical SQL query has the form:
select A1, A2, …, An
from R1, R2, …, Rm
where condition
- Ai represent attributes
- Ri represent relations

This query is equivalent to the relational algebra

expression:
A1, A2, …, An(P(R1 ´ R2 ´ … ´ Rm))

• The result of an SQL query is a relation.

257

257

Projection
• The select corresponds to the projection operation of the
relational algebra. It is used to list the attributes desired in the
result of a query.
• Find the names of all branches in the loan relation

• select branch-name
from loan

Equivalent to: branch-name(loan)

• An asterisk in the select clause denotes “all attributes”

• select *
from loan

258

258

124
 3/4/2024

Duplicate Removal
• SQL allows duplicates in relations as well as in query results.
• Use select distinct to force the elimination of duplicates.
Find the names of all branches in the loan relation, and
remove duplicates
select distinct branch-name
force the DBMS to
from loan remove duplicates
• The keyword all specifies that duplicates not be removed.
select all branch-name
from loan
force the DBMS not
to remove duplicates

259

259

Arithmetic Operations on Retrieved

Results
• The select clause can contain arithmetic expressions involving
the operators,,-, and ´, and operating on constants or
attributes of tuples.
• The query:

• select branch-name, loan-number, amount * 100

from loan

would return a relation which is the same as the loan relations,

except that the attribute amount is multiplied by 100

260

260

125
 3/4/2024

The where Clause

• The where clause specifies conditions that tuples in the
relations in the from clause must satisfy.
• Find all loan numbers for loans made at the Nablus branch
with loan amounts greater than $1200.

• select loan-number
from loan
where branch-name=“Nablus” and amount >1200

• SQL allows logical connectives and, or, and not. Arithmetic

expressions can be used in the comparison operators.

261

261

The where Clause (Cont.)

• SQL includes the between operator
• Find the loan number of those loans with loan
amounts between $90,000 and $100,000 (that is, 
$90,000 and  $100,000)

select loan-number
from loan
where amount between 90000and
100000
262

262

126
 3/4/2024

The from Clause

• The from clause corresponds to the Cartesian product
operation of the relational algebra.
• Find the Cartesian product borrower ´ loan

select *
from borrower, loan

It is rarely used without a where clause.

• Find the name and loan number of all customers having a loan
at the Nablus branch.

• select distinct customer-name, borrower.loan-number

from borrower, loan
where borrower.loan-number = loan.loan-number and
branch-name = “Nablus”
263

263

Table
loan borrower
branch-name loan-number amount cust-name loan-number
Nablus L-170 3000 Jones L-170
Perryridge L-260 1700 Smith L-230
Nablus L-230 4000 Hayes L-155

Loan Borrower branch-name loan-number amount cust-name

Nablus L-170 3000 Jones
Nablus L-230 4000 Smith

264

264

127
 3/4/2024

The Rename Operation

• Renaming relations and attributes using the as clause:
old-name as new-name
• Find the name and loan number of all customers having a loan
at the Nablus branch; replace the column name loan-number
with the name loan-id.

select distinct customer-name, borrower.loan-number as loan-id

from borrower, loan
where borrower.loan-number = loan.loan-number and
branch-name = “Nablus”

265

265

Tuple Variables/Alias
• Tuple variables are defined in the from clause via the use of
the “as” clause.
• Find the customer names and their loan numbers for all
customers having a loan at some branch.

• select distinct customer-name, T.loan-number

from borrower as T, loan as S
where T.loan-number = S.loan-number

266

266

128
 3/4/2024

String Operations
• Character attributes can be compared to a pattern:
% matches any substring.

• Find the name of all customers whose street includes the

substring ‘Main’. (Eg Mainroad, Smallmain Road,
AMainroad,…)

select customer-name
from customer
where customer-street like “%Main%”
• How to match the name “Main%”: (Eg abcMain%,
MainMain%,…)

267

267

Ordering the Display of Tuples

• List in alphabetic order the names of all customers having a loan at
Nablus branch

select distinct customer-name

from borrower, loan
where borrower.loan-number = loan.loan-number and
branch-name = “Nablus”
order by customer-name

• order by customer-name desc, amount asc

desc for descending order; asc for ascending order (default)

268

268

129
 3/4/2024

Set operations
• Find all customers who have a loan, an account, or both:

(select customer-name from depositor)

union
(select customer-name from borrower)
• Find all customers who have both a loan and an account.

(select customer-name from depositor)

intersect
(select customer-name from borrower)
• Find all customers who have an account but no loan.

(select customer-name from depositor)

except
(select customer-name from borrower)

269

269

SQL
Aggregate Functions

270

270

130
 3/4/2024

Aggregate Functions
• Operates on a column of a relation, and return
a value

avg: average value

min: minimum value
max: maximum value
sum: sum of values
count: number of values

271

271

Aggregate Functions(cont.)
• Find the average account balance at the Nablus branch.

select avg(balance)
from account
where branch-name=“Nablus”

balance
account select balance
from account
where branch-name Avg()
=“Nablus” 120,000

272

272

131
 3/4/2024

Aggregate Functions(cont.)
• Find the numbers of tuples in the customer relation.

select count(*)
from customer
– remember * stands for all attributes
– compare to:
select count(customer-city)
from customer
• Find the number of depositors in the bank
select count (distinct customer-name)
from depositor

273

273

Aggregate functions - Group by

• Find the number of accounts for each branch.

select branch-name, count( distinct account-number)
from account
group by branch-name
• For each group of tuples with the same branch-name, apply
aggregate function count and distinct to account-number
b ra n c h -n a m e a c c o u n t-n u m b e r b a la n c e branch-name account-number balance

P e rry rid g e a -1 0 2 400 Perryridge a-102 400

B rig h to n a- 217 750 Perryridge a-201 900
P e rry rid g e a -2 0 1 900 Brighton a-217 750
B rig h to n a -2 1 5 750 Brighton a-215 750
R edw ood a -2 2 2 700 Redwood a-222 700

branch-name count-account-no
account table Perryridge 2
Brighton 2
Redwood 1

274

274

132
 3/4/2024

Null values
• It is possible for tuples to have a null value, denoted by null, for some of
their attributes; null signifies an unknown value or that a value does not
exist.
• The result of any arithmetic expression involving null is null.
• More precisely,
– Any comparison with null returns unknown (caution: Oracle treats it
as false!)
– (true or unknown) = true, (false or unknown) = unknown
(unknown or unknown) = unknown,
(true and unknown) = unknown, (false and unknown) = false
(unknown and unknown) = unknown

– Result of where clause predicate is treated as false if it evaluates to

unknown

275

275

Null Values (cont.)

• Find all loan numbers which appear in the loan relation with null
values for amount.

select loan-number
from loan
where amount is null
• Total of all loan amounts

• select sum(amount)
from loan

Above statement ignores null amounts;

276

276

133
 3/4/2024

Example Nested Query

• Find all customers who have both an account and a loan in the bank.

• select distinct customer-name

from borrower
where customer-name in (select customer-name
from‫ھﻲ‬depositor)

Check for each borrower

if he is also a depositor

277

277

Example Query
• Find all customers who have a loan at the bank
but do not have an account at the bank.

• select distinct customer-name

from borrower
where customer-name not in (select customer-
name from depositor)

278

278

134
 3/4/2024

Views
• Provide a mechanism to hide certain data from the
view of certain users. To create a view we use the
command:

create view view-name as <query expression>

where:
– <query expression> is any legal SQL query
– the name of the view is represented by view-name

279

279

Views
• Base Relation
– Named relation corresponding to an entity in
conceptual schema, whose tuples are
physically stored in database.

• View
– Dynamic result of one or more relational
operations operating on base relations to
produce another relation.

Slide 280

280

135
 3/4/2024

Views
• A virtual relation that does not necessarily
actually exist in the database but is produced
upon request, at time of request.

• Contents of a view are defined as a query on

one or more base relations.

• Views are dynamic, meaning that changes

made to base relations that affect view
attributes are immediately reflected in the
view.

Slide 281

281

Purpose of Views
• Provides powerful and flexible security
mechanism by hiding parts of database from
certain users.

• Permits users to access data in a customized

way, so that same data can be seen by
different users in different ways, at same
time.

• Can simplify complex operations on base

relations.

Slide 282

282

136
 3/4/2024

Updating Views
• All updates to a base relation should be
immediately reflected in all views that
reference that base relation.

• If view is updated, underlying base

relation should reflect change.

Slide 283

283

Updating Views
• There are restrictions on types of
modifications that can be made through
views:
– Updates are allowed if query involves a single base
relation and contains a candidate key of base relation.
– Updates are not allowed involving multiple base
relations.
– Updates are not allowed involving aggregation or
grouping operations.

Slide 284

284

137
 3/4/2024

Example Queries
• A view consisting of branches and their customers
create view all-customer as
(select branch-name, customer-name
from depositor, account
where depositor.account-number = account.name-number)
union
(select branch-name, customer-name
from borrower, loan
where borrower.loan-number = loan.loan-number)

• Find all customers of the Nablus branch

select customer-name
from all-customer
where branch-name = “Nablus”

285

285

Data Manipulation Language

(DML)

286

286

138
 3/4/2024

Modification of the Database - Deletion

• Delete all account records at the Nablus branch

delete from account

where branch-name = “Nablus”

• Conceptually, delete is done in two steps:

– find the tuples you want to delete:

select * from account

where branch-name = “Nablus”

– delete the tuples you found.

287

287

Modification of the Database - Deletion

• Delete all accounts at every branch located in Nablus.

• delete from account

where branch-name in (select branch-name from branch
where branch-city = “Nablus”)

account(account_number, branch_name, balance);

branch(branch_name, branch_city, assets);
customer(customer_name, customer_street, customer_city );
Loan(loan_number, branch_name, amount);
depositor(customer_name, account_number);
borrower(customer_name, loan_number);

288

288

139
 3/4/2024

Example Query
• Delete the records of all accounts with balances below the average at the
bank

• delete from account

where balance < (select avg (balance)
from account)

account(account_number, branch_name, balance);

branch(branch_name, branch_city, assets);
customer(customer_name, customer_street, customer_city );
Loan(loan_number, branch_name, amount);
depositor(customer_name, account_number);
borrower(customer_name, loan_number);

289

289

Modification of the database - Insertion

• Add a new tuple to account

insert into account values (‘A-9732’, ‘Perryridge’, 1200)

To reorder attributes, specify attribute names explicitly:

insert into account (branch-name, balance, account-number) values

(‘Perryridge’, 1200, ‘A-9732’)

• Add a new tuple to account with balance set to null

insert into account values (‘A-777’, ‘Perryridge’, null)

290

290

140
 3/4/2024

Modification of the database - Updates

• Increase all accounts with balance over $10,000 by 6%, all
other accounts receive 5%.
– Write two update statements:
update account
set balance = balance *1.06
where balance >10000

update account
set balance = balance *1.05
where balance  10000
– the order is important
– Solution use case

291

291

Case Statement for Conditional Updates

• Same query as before: Increase all accounts with balances

over $10,000 by 6%, all other accounts receive 5%.
update account
set balance = case
when balance <= 10000
then balance *1.05
else balance * 1.06
end

292

292

141
 3/4/2024

Correct Update Procedure

Consider the following database

works-on(pid,eid,hours)
project(pid,name,location,depID)
employee(eid, name, bd, address)

Write the update statement that will switch the employee whose name is ahmad
from working on the „database” project to work on the „AI” project.

Replace old pid with Find the tuple relating “Ahmad”

the pid of “AI” to the “database” project

update works-on
set pid = ( select id from project where name=`AI’ )
where eid = ( select id from employee where name = `Ahmad’ )
and pid = ( select id from project where name = ‘database’ )

293

293

RA - SQL
• Given the following tables:
employee (person_name, street, city)
works (person_name, company_name, salary)
company (company_name, city)
manages (person_name, manager_name)

• Find the name of employees who earn more

than $10,000 and live in Hong Kong.
pperson_name (ssalary > 10000 (works))∩ p person_name (s
city=“Hong Kong” (employee))
294

294

142
 3/4/2024

• Find the name of employees who earn more

than $10,000 and live in Hong Kong.

select w.person_name
from works as w
where w.salary > 10000 and w.person_name in
(select e.person_name
from employee as e
where e.city = “Hong Kong”)

employee (person_name, street, city)

• Alternative solutions works (person_name, company_name, salary)
company (company_name, city)
select w.person_name manages (person_naame, manager_name)
from works as w, employee as e
where w.salary > 10000 and e.city = “Hong Kong”
and w.person_name = e.person_name )
295

295

RA - SQL
• Given the following tables:
employee (person_name, street, city)
works (person_name, company_name, salary)
company (company_name, city)
manages (person_name, manager_name)

• Find the name of the employees who are not a

manager.

pperson_name(employee) – pmanager_name(manages)

296

296

143
 3/4/2024

• Find the name of the employees who are not a

manager.

(select person_name
from employee)
except
(select manager_name
from manages) employee (person_name, street, city)
works (person_name, company_name, salary)
company (company_name, city)
manages (person_naame, manager_name)
297

297

• Alternative solutions

select person_name
from employee
where person_name not in
(select manager_name
from manages)

employee (person_name, street, city)

works (person_name, company_name, salary)
company (company_name, city)
manages (person_naame, manager_name)
298

298

144
 3/4/2024

• Find the names of all persons who work for “First Bank
Corporation” and live in the city where the company is
located.
employee (person_name, street, city)
works (person_name, company_name, salary)
company (company_name, city)
select w.person_name manages (person_naame, manager_name)
from works as w
where w.company_name = “First Bank Corporation”
and exists
(select *
from company as c, employee as e
where c.company_name = w.company_name
and e.person_name = w.person_name
and c.city = e.city )

299

299

• Find all cities where employees live or where

companies are located

(select distinct city

from employee)
union
(select distinct city
from company) employee (person_name, street, city)
works (person_name, company_name, salary)
company (company_name, city)
manages (person_naame, manager_name)
300

300

145
 3/4/2024

• Find the names, city of employees who work (in at least a company)

select distinct (e.person_name, e.city)

from employee as e, works as w
where w.person_name = e.person_name

employee (person_name, street, city)

works (person_name, company_name, salary)
company (company_name, city)
manages (person_naame, manager_name)
301

301

• Display the names of all employees who work (in at least

a company) and the city of the company in ascending
order of names

select distinct (w.person_name, c.city)

from works as w, company as c
where c.company_name = w.company_name
order by w.person_name asc

employee (person_name, street, city)

works (person_name, company_name, salary)
company (company_name, city)
manages (person_naame, manager_name)
302

302

146
 3/4/2024

• Find the names, cities of employees who only

work for exactly ONE company

select e.person_name, e.city

from employee as e employee (person_name, street, city)
works (person_name, company_name, salary)
where unique company (company_name, city)
manages (person_naame, manager_name)
(select *
from works as w,
where w.person_name = e.person_name)
303

303

• Find the names of all employees who earn more than SOME
employee of Small Bank Corporation.

select w1.person_name
from works as w1
where w1.salary > some
(select w2.salary
from works as w2
where w2.company_name = “Small Bank Corporation”)

employee (person_name, street, city)

works (person_name, company_name, salary)
company (company_name, city)
manages (person_naame, manager_name)

304

304

147
 3/4/2024

JDBC
• JDBC (Java Database Connectivity) is a standard
API for accessing relational databases from a
Java program.
• This interface makes it easy to access a
database because it provides an abstract layer
that hides the low-level details, such as managing
sockets.
• It also provides for interoperability and
portability since it allows a single application to
access multiple database management systems
simultaneously.
– For example, a single application can query and
manipulate a database in Oracle and a database in
DB2. Communication with a database management
system (DBMS) is through method calls. These calls
are passed to the driver, which in turn, translates
them into DBMS-specific calls.
305

305

JDBC – cont.
• The driver basically is a set of library routines.
• For every database you need a compatible
JDBC driver.
• The basic steps to get your program up and
running are:
1. Include the compatible JDBC driver library
2. Load the driver and register it with the driver
manager
3. Connect to a database
4. Create a statement
5. Execute a query and retrieve the results, or make
changes to the database
6. Disconnect from the database
306

306

148
 3/4/2024

JDBC – cont.
• Steps 1, 2, and 3 are the only DBMS-specific
steps. The rest is DBMS independent with one
exception:
– The mappings between the DBMS and Java datatypes is
somewhat DBMS-specific.
– However, this is not a major issue because the driver
usually handles the datatype conversions.
• Therefore, to make your program work with DB2
instead Oracle, you usually only have to change the
code associated with steps 1 and 2, and 3.
• We will cover only JDBC 1.0 now, next we will see
other versions.
• The latest version is JDBC 4.1

307

307

Setting classpath
• The first thing you need to do is set the
CLASSPATH environment variable so that
Java can find the classes for the driver. Add
the following line to your classpath :
– C:\app\UserName\product\11.2.0\dbhome_1\jd
bc\lib\ojdbc6.jar
• Alternatevily, you can set the classpath in
your IDE program to point to the oracle jdbc
driver.
308

308

149
 3/4/2024

Loading and Registering Drivers

• We will now load the oracle JDBC thin driver.
• Import the required libraries, see next slide.
• Thin drivers are written completely in oracle
database. They do not require any oracle client
installation.
• Three ways:
– DriverManager.registerDriver(new
oracle.jdbc.driver.OracleDriver()); or
– Class.forName("oracle.jdbc.driver.OracleDriver");
– Using OracleDataSource class
• The first method throws an SQLException and the
second throws a ClassNotFoundException.
309

309

Import Statements

310

310

150
 3/4/2024

Connecting to a Database
• The DriverManager class provides the static getConnection()
method for opening a database connection.
– public static Connection getConnection(String url, String userid,
String password) throws SQLException
• The url is the DBMS-specific part.
– For the Oracle thin driver, it is of the form:
"jdbc:oracle:thin:@host_name:port_number:sid", where
• host_name is the host name of the database server,
• port_number is the port number on which a "listener" is listening for
connection requests, and
• sid is the system identifier that identifies the database server.
• The url that we are using is "jdbc:oracle:thin:@localhost:1521:orcl",
• DriverManager is not needed when OracleDataSource
class is used

311

311

Connecting to a Database – cont.

• Connection con = DriverManager.getConnection(
"jdbc:oracle:thin:@localhost:1521:orcl", "username",
"password");
• The Connection object returned by getConnection()
represents one connection to a particular
database.
• To connect to another database, you will need to
create another Connection object using
getConnection() with the appropriate url argument
(consult the driver's documentation for the format of
the url).
• To disconnect from a database, use the Connection
object's close() method.

312

312

151
 3/4/2024

Using OracleDataSource class

– OracleDataSource ods = new OracleDataSource();
– String url = "jdbc:oracle:thin:@//myhost:1521/orcl",
– ods.setURL(url);
– ods.setUser("scott");
– ods.setPassword("tiger");
– Connection conn = ods.getConnection();
• Username and password can be specified in string
url
– String URL =
"jdbc:oracle:thin:scott/tiger@//myhost:1521/orcl");
– ods.setURL(URL);
– Connection conn = ods.getConnection();

313

313

Creating and Executing Statements

• A Statement object represents an SQL
statement.
• It is created using the Connection object's
createStatement() method.
// con is a Connection object
Statement stmt = con.createStatement();
• The SQL statement string is not specified
until the statement is executed.

314

314

152
 3/4/2024

Executing Inserts, Updates, and Deletes

• To execute a data definition language statement (e.g., create, alter,
drop) or a data manipulation language statement (e.g., insert,
update, delete), use the executeUpdate() method of the Statement
object.
• For insert, update, and delete statements, this method returns the
number of rows processed.
• Here is an example:
// stmt is a statement object int rowCount =
stmt.executeUpdate("INSERT INTO branch VALUES (20, 'Faisal
Street', 'main road', 'Ahmad', 5252738)");

• Notes:
• Do not terminate SQL statements with a semicolon.
• You can reuse Statement objects to execute another
statement.
• To indicate string nesting, alternate between the use of double
and single quotation marks.

315

315

Executing Queries
• To execute a query, use the executeQuery()
method. Here is an example:
– stmt.executeQuery("SELECT branch_id,
branch_name FROM branch WHERE branch_city
= 'Vancouver' ");
• The executeQuery() method returns a
ResultSet object, which maintains a cursor.
• excuteQuery() never returns null.
• SQL queries handle sets of rows at a time,
while Java can handle only one row at a time.
316

316

153
 3/4/2024

317

317

Executing Queries – cont.

• The ResultSet class makes it easy to move from row to row
and to retrieve the data in the current row (the current row is
the row at which the cursor is currently pointing).
• Initially, the cursor points before the first row.
• The next() method is used to move the cursor to the next
row and make it the current row.
– The first call to next() moves the cursor to the first row.
– next() returns false when there are no more rows.
• The getXXX() methods are used to fetch column values of
Java type XXX from the current row.
• For specifying the column, these methods accept a column
name or a column number.
– Column names are not case sensitive, and
– column numbers start at 1 (column numbers refer to the columns
in the result set).
318

318

154
 3/4/2024

Example
• int branchID;
• String branchName;
• String branchAddr;
• String branchCity;
• int branchPhone;
• ...
• // con is a Connection object
• Statement stmt = con.createStatement();
• ResultSet rs = stmt.executeQuery("SELECT * FROM branch");
• while(rs.next()) {
– branchID = rs.getInt(1);
– branchName = rs.getString("branch_name");
– branchAddr = rs.getString(3);
– branchCity = rs.getString("branch_city");
– branchPhone = rs.getInt(5);
– ...
• }
319

319

Checking for Null Return Values

• You should always check for nulls for all nullable columns.
– If you don't, you may encounter exceptions at runtime.
• The ResultSet class provides the method wasNull() for detecting
fetched null values.
– It returns true if the last value fetched by getXXX() is null.
• The SQL NULL value is mapped to Java's null. However,
– only object types can represent null;
– primitive types, such as int and float, cannot. These types represent
null as 0.
– Thus when NULL is fetched, getByte(), getShort(), getInt(), getLong(),
getFloat(), and getDouble() return 0 instead of null. Moreover, the
getBoolean() method returns false if NULL is fetched.
– What if the value stored in the database is actually 0 or false? To
avoid this problem, use the wasNull() method to check for null values.
• You cannot insert null using Statement objects. You have to use
PreparedStatement objects instead.

320

320

155
 3/4/2024

Converting between Java and

Oracle Datatypes
• Oracle datatypes and host language datatypes are not the same. When
values are passed from Oracle to Java and vice versa, they need to be
cast from one datatype to the other.
• The JDBC driver can automatically convert values of Oracle datatypes to
values of some of the Java datatypes.
• The table below shows the getXXX() methods that can be used for some
common Oracle datatypes.
• An * denotes that the getXXX() method is the preferred one for retrieving
values of the given Oracle datatype.
• An x denotes that the getXXX() method can be used for retrieving values
of the given Oracle datatype.
• For example, if only integers are stored in a NUMBER column, use
getInt(). For the DATE datatype, if a DATE column only stores times, use
getTime(). If a DATE column only stores dates, use getDate(). If the column
stores both dates and times, use getTimestamp(). Note that getString() can
be used for all the Oracle datatypes; however, use getString() only when
you really want to receive a string.

321

321

getXXX() methods
CHAR VARCHAR2 LONG NUMBER INTEGER FLOAT DATE RAW LONGRAW

getByte() x x x x x x
getShort() x x x x x x
getInt() x x x x * x
getLong() x x x x x x
getFloat() x x x x x x
getDouble() x x x x x *
getBigDecimal() x x x x x x
getBoolean() x x x x x x
getString() * * x x x x x x x
getBytes() * x
getDate() x x x x
getTime() x x x x
getTimestamp() x x x x
getAsciiStream() x x * x x
getUnicodeStream() x x * x x
getBinaryStream() x *
getObject() x x x x x x x x x

Note: You must import java.math.*; if you want to use the BigDecimal class.
322

322

156
 3/4/2024

Using Prepared Statements

• Represents a precompiled SQL statement that contains
placeholders to be substituted later with actual values.
– compiled at creation time.
• The statement can then be executed and re-executed using
different values for each placeholder without needing to be
recompiled.
• Unlike a prepared statement, An SQL statement represented
by a Statement object is compiled every time it is executed.
• PreparedStatement inherits methods from Statement, you
can use executeQuery() and executeUpdate() to execute a
prepared statement;
– However, these methods are redefined to have no parameters
as you will soon see.
– You can also use the close() method to close a prepared
statement, and the wasNull() method to check for fetched null
values.
323

323

PreparedStatement – cont.
• PreparedStatement is created using the Connection object returned
by getConnection().
• However, unlike a Statement object, the SQL statement is specified
when the prepared statement is created and not when it is executed.
Here's an example of creating a prepared statement:
// con is a Connection object created by getConnection()
// note that there is no 'd' in "prepare" in prepareStatement()
PreparedStatement ps = con.prepareStatement("UPDATE branch SET " +
"branch_addr = ?, branch_phone = ? WHERE branch_city =
'Vancouver'");

• Each placeholder is denoted by a ?. A ? can only be used to

represent a column value. It cannot be used to represent a
database object, such as a table or column name.
• The setXXX() methods are used to substitute values for the
placeholders.
• setXXX() accepts a placeholder index and a value of type XXX.
– The first placeholder has an index of 1.

324

324

157
 3/4/2024

setXXX() methods
Oracle Datatype setXXX()
CHAR setString()
VARCHAR2 setString()
LONG setString()
NUMBER setBigDecimal()
setBoolean()
setByte()
setShort()
setInt()
setLong()
setFloat()
setDouble()
INTEGER setInt()
FLOAT setDouble()
RAW setBytes()
LONGRAW setBytes()
DATE setDate()
setTime()
setTimestamp()
325

325

Datatype mapping
• Unlike getXXX(), the setXXX() methods do not perform any
datatype conversions.
• You must use a Java value whose type is mapped to the
target Oracle datatype.
• Therefore, to input a Java value that is not compatible with the
target Oracle datatype, you must convert it to a compatible
Java type.
• The setObject() method can be used to convert a Java value
to the format of a JDBC SQL type.
• JDBC SQL types are constants that are used to represent
generic SQL types; they are not actual Java types.
• The table below shows the mappings among Oracle, JDBC,
and Java types

326

326

158
 3/4/2024

Oracle Datatype JDBC Generic SQL Type Standard Java Type

CHAR java.sql.Types.CHAR java.lang.String
VARCHAR2 java.sql.Types.VARCHAR java.lang.String
LONG java.sql.Types.LONGVARCHAR java.lang.String

NUMBER java.sql.Types.NUMERIC java.math.BigDecimal

NUMBER java.sql.Types.DECIMAL java.math.BigDecimal
NUMBER java.sql.Types.BIT boolean
NUMBER java.sql.Types.TINYINT byte
NUMBER java.sql.Types.SMALLINT short

NUMBER java.sql.Types.INTEGER int

NUMBER java.sql.Types.BIGINT long
NUMBER java.sql.Types.REAL float
NUMBER java.sql.Types.FLOAT double
NUMBER java.sql.Types.DOUBLE double
RAW java.sql.Types.BINARY byte[]
RAW java.sql.Types.VARBINARY byte[]

LONGRAW java.sql.Types.LONGVARBINARY byte[]

DATE java.sql.Types.DATE java.sql.Date

DATE java.sql.Types.TIME java.sql.Time
DATE java.sql.Types.TIMESTAMP javal.sql.Timestamp
327

327

Example
• Here is an example of using a prepared statement:
•

• // con is a Connection object created by getConnection()

• PreparedStatement ps = con.prepareStatement("INSERT INTO branch " +
• "(branch_id, branch_name, branch_city) VALUES (?, ?, 'Vancouver')");
• int bid[5] = {1, 2, 3, 4, 5};
• String bname[5] = {"Main", "Westside", "MacDonald", "Mountain Ridge", "Valley Drive"};

• for (int i = 0; i < 5; i++)

• {
• ps.setInt(1, bid[i]);
• ps.setString(2, bname[i]);

• ps.executeUpdate();
• }

• Note: Once the value of a placeholder has been defined using setXXX(), the value will remain in the
prepared statement until it is replaced by another value, or when the clearParameters() method gets
called.

328

328

159
 3/4/2024

Inserting Null Values

• The setNull() method is used to substitute a
placeholder with a null value.
• setNull() accepts two parameters: the placeholder
index and the JDBC SQL type code.
• Refer to the Table before to select a JDBC SQL
type that is compatible with the target Oracle
datatype.
• Alternatively, for setXXX() methods that accept an
object as an argument, such as setString(), you
can use null directly in setXXX().

329

329

Transaction Processing
• Any changes made to a database are not necessarily made permanent, right away.
• For example, when you transfer money from one bank account to another, you do not want
the bank to debit one account and not credit the other because of an error (unless the error
benefits you). You want the debit and credit SQL calls to be treated as one atomic unit of
work (all or none principle), so either both the debit and credit are canceled if an error
occurs, or both the debit and credit are made permanent if the transfer is successful. Thus
you should group your SQL statements into transactions in order to ensure data integrity.
• To make changes to the database permanent, use the Connection object's commit()
method like this:
– // con is a Connection object
– con.commit();
• By default, data manipulation language statements, such as insert, delete, and update,
issue an automatic commit.
• You should disable auto commit mode so that you can group statements into transactions.
– con.setAutoCommit(false);
• When you disable auto commit, you must manually issue commit() after each transaction.
• However, if you do not issue a commit or rollback for the last transaction and auto commit
is disabled, then a commit is issued automatically for you when the connection is closed.
As a general rule, commit often.
• Note: Data definition statements, such as create, drop, and alter, issue an automatic
commit regardless of whether or not auto commit is off or on.
• To undo changes made to the database by the most recently executed transaction, use the
Connection object's rollback() method like this:
– con.rollback();

330

330

160
 3/4/2024

Using Java Persistence API

• The Java Persistence API(JPA) provides an
object/relational mapping facility for
managing relational data in Java applications.
• JPA is a lightweight, POJO-based framework
for object-relational mapping.
• The mapping between Java objects and a
relational database is done using annotations
and/or XML deployment descriptors.

331

331

Using JPA – cont.

• Netbeans will be used
• Connect to the database by creating a
new connection from the services tab.

332

332

161
 3/4/2024

Setting connection in netbeans

• Choose the oracle thin drive
• For the driver file, click Add... , browse to
the ojdbc6.jar file

333

333

Setting connection in netbeans

– cont.

334

334

162
 3/4/2024

Select the schema

335

335

Name your connection and click

finish

336

336

163
 3/4/2024

Execute SQL command

337

337

The result will be shown

338

338

164
 3/4/2024

Lets begin with JPA

• Create A Java Application project
• I will name it TestingJPA
• Make sure to uncheck the Create Main
Class
• Click Finish to create the project
• Right Click your project „TestingJPA”
• Choose New ...>Entity Classes from
Database ...
339

339

New Entity classes from

database...

340

340

165
 3/4/2024

New Entity Classes from

Database – cont.

341

341

Coding JPA
• You will have two packages shown
– The one you name it, let us name it the working
package, in this example „TestJPA”
– Another package which name is „META-INF”
• Contains the persistence.xml configuration file.
• In the working package („TestJPA”) create a
main class, a JFrame class(es). We will use
the created classes with the JPA API to
handle and create a CRUD (Create, Read,
Update, and Delete) application interface
342

342

166
 3/4/2024

Coding JPA – cont.

• We will use the name provided in the
persistence.xml file

343

343

Coding JPA – cont.

• Import the following classes
– import javax.persistence.EntityManager;
– import javax.persistence.EntityManagerFactory;
– import javax.persistence.Persistence;
– If you did not import them, netbeans will do that automatically once
you used any of them.
• Create an EntityManager for your classes
– EntityManagerFactory emf =
Persistence.createEntityManagerFactory("TestingJPAPU");
– EntityManager em = emf.createEntityManager();

344

344

167
 3/4/2024

Coding JPA – cont.

• To insert an Employee
– em.getTransaction().begin();
– Create one object from the entity classes, lets say
its name is e1
– Set its values
– Use the function em.persist(e1) to insert it
– Use the function em.getTransaction().commit() to
save changes
– Then close the connection by em.close(); and
emf.close();

345

345

Coding JPA – cont.

• Query example
• EntityManager entityManager =
Persistence.createEntityManagerFactory("TestingJPAPU").createEntityManager();
• Query query = entityManager.createNamedQuery("Regions.findAll");
• List<Regions> resultList = query.getResultList();
• for (Regions c : resultList)
• {
• jTextArea1.append(c.getRegionID() + " (" + c.getRegionName() + ")" + "\n");
• }

• For more information see

https://platform.netbeans.org/tutorials/nbm-crud.html

346

346

168
 3/4/2024

Sequences
• In most cases, tuples in relations are uniquely identified by numbers
(primary keys, ex: StudentID).
• However, Oracle provides a function which can automatically generate
unique numbers. This is done with the following command:
• CREATE SEQUENCE <sequence_name>
– Therefore, to create a sequence called branch_counter, we specify: CREATE
SEQUENCE branch_counter
• To start generating sequence numbers, we do the following in our INSERT
statements:
• INSERT
• INTO BRANCH (branch_id, branch_name, branch_addr, branch_city,
branch_phone)
• VALUES (branch_counter.nextval, 'West', '7291 W. 16th', 'Coquitlam',
5559238)
• Every time the NEXTVAL variable is accessed, the sequence number
corresponding to branch_counter increases by 1. Therefore, the sequence
numbers generated by branch_counter for branch_id are 1, 2, ... (Note:
multiple accesses to NEXTVAL within the same SQL statement result in the
same value).

347

347

Sequences – cont.
• NEXTVAL can be used only in the following
cases:
– in an INSERT statement
– in an UPDATE statement
– in a SELECT statement which must NOT:
• be part of a view
• contain DISTINCT
• contain ORDER BY
• contain GROUP BY
• contain set operators such as UNION

348

348

169
 3/4/2024

Sequences – cont.
• A sequence does not necessarily have to increment by 1 and
start at 1. We have the following options:
START WITH <integer>
INCREMENT BY <integer>
MAXVALUE <integer>
MINVALUE <integer>
CYCLE | NOCYCLE
ORDER | NOORDER
• The semantics of these options should be self-explanatory. To
illustrate:
CREATE SEQUENCE branch_counter
START WITH 10
INCREMENT BY 2
MAXVALUE 20
CYCLE
• results in the sequence: 10, 12, 14, 16, 18, 20, 10, 12 ...
349

349

Sequences – cont.
• Of course, if we use sequences for primary key fields, Oracle will not allow
the CYCLE option to be part of the definition of the sequence.
• Another useful variable is the CURRVAL variable, which returns the most
recently generated value by NEXTVAL.
– Example:
SELECT *
FROM BRANCH
WHERE branch_id = branch_counter.currval

• To alter or delete sequences, we use the commands:

– ALTER SEQUENCE <sequence-name> [<sequence options>] and
– DROP SEQUENCE <sequence-name>
• For example, to alter the branch counter sequence to increment by 100, to
a maximum value of 1000:
– ALTER SEQUENCE branch_counter
– INCREMENT BY 100
– MAXVALUE 1000
• The only option that we cannot alter after a sequence has been created is
START WITH.
– To change the START WITH value, we would have to delete the sequence and create
it again with the new value.
350

350

170
 3/4/2024

Sequences – cont.
• You can query the settings of your sequences by referencing
the SEQ table, which contains fields such as
SEQUENCE_NAME, MIN_VALUE, MAX_VALUE,
LAST_NUMBER, INCREMENT_BY, and C (for cycle).
• Below is an example of how to use a sequence in Java. A
branch tuple is inserted and then returned in the query that
follows.
// stmt is a Statement object
// branch_counter is a sequence
stmt.executeUpdate("INSERT INTO branch VALUES +
(branch_counter.nextval, 'West', '7291 W.16th', 'Coquitlam', 5559238)");
ResultSet rs = stmt.executeQuery("SELECT * FROM branch WHERE " +
"branch_id = branch_counter.currval");
• Note: Not all DBMSs support sequences.

351

351

352

352

171
 3/4/2024

Getting Information about a

Result Set
• The ResultSetMetaData class provides methods to get information
about the columns in a result set.
• For example, this class provides methods to return
– the number of columns,
– the name of a given column,
– the maximum character width of a given column,
– the datatype of a given column, and
– whether or not null values are permitted in a given column.
• Refer to the ResultSetMetaData class in the Java API
documentation for the class description.
• The following example gets the number of columns in a ResultSet
object:
// rs is a ResultSet object
ResultSetMetaData rsmd = rs.getMetaData();
int count = rsmd.getColumnCount();

353

353

Floating Point Numbers

• The default type for a floating point number is
double
• It is better to use type casting when dealing with
float numbers, if not, it may not compile.
• Constants
• Example:
– float f = (float)3.14;
– ps.setFloat(1, (float)1234.99);
• To avoid the truncation of large floating point
numbers, use getDouble(), setDouble(), and the
double datatype.

354

354

172
 3/4/2024

Date/time formate
• JDBC supports dates, times, and timestamps that are only in
ISO standard format, which is different from the default date
format used by Oracle (the default date format is dd-MMM-yy,
e.g. 23-JUN-01). Consequently, date literals must be in the
form {d 'yyyy-MM-dd'}. For example, the following inserts 23-
JUN-01 into table abc.
– stmt.excuteUpdate("INSERT INTO abc VALUES ({d '2001-06-
23'})");
• In addition, time literals must be of the form {t 'H:mm:ss'}. 'H'
is for hour in day (0-23) and 'h' is for hour in am/pm (1-12).
– stmt.executeQuery("SELECT xyz FROM abc WHERE
inspectionTime = {t '05:12:45'}");
• More simpols can be found on JDBC API documentations
(date/time formating.)

355

355

For more information see the

Oracle® Database
JDBC Developer’s Guide
You can get it from
Oracle
356

356

173
 3/4/2024

Jaspersoft Studio and

JasperReport Library
• Download and install Jaspersoft Studio
– https://sourceforge.net/projects/jasperstudio/
• Create the report design
• Copy the .jrxml and all the related files to your java
Netbeans project library
• Use maven repository to include all the related jasperreport
library.
– Choose a version related to the jasperstudio
– https://mvnrepository.com/artifact/net.sf.jasperreports/jasperreports

357

357

358

358

174
 3/4/2024

Viewing reports

365

365

Working with reports

Connection con;
InputStream input;
JasperDesign jasperDesign;
JasperReport jasperReport;
JasperPrint jasperPrint;
OutputStream output;
OracleDataSource ods;
ods=new OracleDataSource();
ods.setURL("jdbc:oracle:thin:@localhost:1521:orcl");
ods.setUser("hr");
ods.setPassword("hr");
con=ods.getConnection();
input=new FileInputStream(new File("report1.jrxml"));
jasperDesign=JRXmlLoader.load(input);
jasperReport=JasperCompileManager.compileReport(jasperDesign);
jasperPrint=JasperFillManager.fillReport(jasperReport, null, con);
output= new FileOutputStream(new File("EmployeeReport.pdf"));
JasperExportManager.exportReportToPdfStream(jasperPrint,output);
//you can also use the viewer, so replace the last two lines with
//the following code
JFrame frame = new JFrame("Report");
frame.getContentPane().add(new JRViewer(jasperPrint));
frame.pack(); 366
frame.setVisible(true);

366

175
 3/4/2024

Design Theory for Relational

Databases
• Many way to design a relationsl database
schema.
• Whatever approach we use, it is common for
an initial relational schema to have room for
improvement, especially by eliminating
redundancy.
• Often, the problems with a schema involve
trying to combine too much into one relation.

367

367

Dependencies
• Fortunately, there is a well developed theory for relational databases:
"dependencies,"

• Mostly used to make a good relational database schema, and how

improve a schema if it has flaws.

• The problems of schemas are referred to as anomalies.

• We will discuss Functional dependencies, a generalizeation form of the

idea of a key for a relation.

• The notion of functional dependencies is then used to define normal

forms for relation schemas.

• Normalization is what we use to decompose relations into two or more

relations to remove anomalies.
368

368

176
 3/4/2024

Functional dependencies
• A functional dependency (FD) definition:
"If two tuples of R agree on all of the attributes
A1 , A2 , ... , An
– (i.e., the tuples have the same values in their respective
components for each of these attributes)
Then they must also agree on all of another list of
attributes B1, B2 , ... , Bm.
• Formally we write it as :
A1 A2 · · · An  B1 B2 · · · Bm and say that
"A1, A2, ... , An functionally determine B1 , B2, ... , Bm"
369

369

Functional Dependencies
Definition: A1, ..., Am  B1, ..., Bn holds in R if:

"t, t’ Î R, (t.A1=t’.A1  ...  t.Am=t’.Am  t.B1=t’.B1  ...  t.Bm=t’.Bm )

R
A1 ... Am B1 ... Bm

t’

if t, t’ agree here then t, t’ agree here 370

370

177
 3/4/2024

Functional Dependencies
• A form of constraint
– hence, part of the schema
• Finding them is part of the database design
• Also used in normalizing the relations

371

371

Functional Dependence
• Existence dependence: The existence of B depends on A
• Functional dependence: B’s value depends on A’s value
– EmpName is functionally dependent on EmpNo
– Given the EmpNo, I can determine one and only one value
of EmpName

• Require that the value for a certain set of attributes

determines uniquely the value for another set of attributes.
• Functional dependence is a generalization of the notion of a
key.

372

372

178
 3/4/2024

Keys of Relations
• We say a set of one or more attributes
{ A1 , A2 , ... , An} is a key for a relation R if:
– Those attributes functionally determine all other
attributes of the relation.
• That is, it is impossible for two distinct tuples of R to
agree on all of A1, A2, ..., An.
– No proper subset of { A1, A2, ... , An} can
functionally determines all other attributes of R;
i.e., a key must be minimal.
• When a key consists of a single attribute A,
we often say that A (rather than {A}) is a key.

373

373

Functional Dependencies
• Loan-info = (branch-name, loan-number, customer-name,
amount)

We expect the following set of functional dependencies

to hold:
loan-number ® amount
loan-number ® branch-name

but would not expect the following to hold:

loan-number ® customer-name

374

374

179
 3/4/2024

Examples
loan-number ® amount loan-info
loan-number ® branch-name branch-nm loan-no cust-nm amount
Perryridge L-001 Peter Yeung 100000
´ customer-name
loan-number ® Perryridge L-001 David Chan 100000
Perryridge L-001 May Chan 100000
Wanchai L-002 Leon Lai 100000

loan-info
Another example:
branch-nm loan-no cust-nm amount
Try to write the FDs of the Perryridge L-001 Peter Yeung 100000
following table Perryridge L-001 Peter Yeung 100000
Central L-001 Peter Yeung 250000
Wanchai L-002 Leon Lai 100000

375

375

In General
• To check A  B, erase all other columns

… A … B
X1 Y1
X2 Y2
… …
• check if the remaining relation is many-one or
one-one (called functional in mathematics)

376

376

180
 3/4/2024

Closure of a Set of Functional

Dependencies
• Given a set of functional dependencies F, there are certain other
functional dependencies that are logically implied by F.

• The set of all functional dependencies logically implied by F is the

closure of F.
• We denote the closure of F by F+.
• We can find all of F+ by applying :

– if   , then  ®  (reflexivity)
– if  ® , then  ®  (augmentation) ‫زﯾﺎده‬
– if  ®  and ® , then  ®  (transitivity) ‫ﺗﻌﺪي‬

377

377

Closure
• We can further simplify computation of F+ by
using the following additional rules.

– If  ®  holds and  ®  holds, then  ® 

holds (union)
– If  ®  holds, then  ®  holds and  ® 
holds (decomposition)
– If  ®  holds and  ®  holds, then  ® 
holds (pseudotransitivity)

378

378

181
 3/4/2024

Examples of Armstrong’s
Axioms
• We can find all of F+ by applying :
– if   , then  ®  (reflexivity)
loan-no ® loan-no
loan-no, amount ® loan-no
loan-no, amount ® amount

– if  ® , then  ®  (augmentation)
loan-no ® amount (given)
loan-no, branch-name ® amount, branch-name

– if  ®  and ® , then  ®  (transitivity)

loan-no ® branch-name (given)
branch-name ® branch-city (given)
loan-no ® branch-city
379

379

Example

• R = (A, B, C, G, H, I)

• F = {A ® B
A ® C
CG ® H
CG ® I
B ® H}

• some members of F+
A ®H A ® B; B ® H
AG ® I
A ® C; AG ® CG; CG ® I
CG ® HI

380

380

182
 3/4/2024

Example

• R = (A, B, C, G, H, I)
F = ( A B
A C
CG H
CG I
B H}
• (AG+)
– Result= AG
– Result= ABCG (A  C; A  B and A  AG)
– Result= ABCGH (CG  H and CG  AGBC)
– Result=ABCGHI (CG  I and CG  AGBCH)
• Is AG a candidate key? result contains all
– AG  R so yes it is a candidate key of the attributes
of R, so stop381

381

Example
• R = (A, B, C, G, H, I)
F={ A®B
A®C
CG ® H
CG ® I
B ® H}
• some members of F +
– A®H
• by transitivity from A ® B and B ® H
– AG ® I
• by augmenting A ® C with G, to get AG ® CG
and then transitivity with CG ® I
– CG ® HI
• from CG ® H and CG ® I : “union rule”

382

382

183
 3/4/2024

Example
Given this FD for this R(A,B,C,D,E,F)
AB C
AD E
BD
AFB
Check if AB+ is a key for this relation?
AB+ is key if AB+ can find all the attribute of R

ABAB
BD so B AB  AB+ABD
ADE so AD ABDAB+ABDE
ABC so AB ABDE AB+ABCDE
AFB so AF Not ABDE  AB+ABCDE
AB not a key because it does not contain all attributes such as F
383

383

Example
Given this FD for this R(A,B,C,D,E,F)
AB C
AD E
BD
AFB
Check if AF+ is a key for this relation?
AF is a key

384

384

184
 3/4/2024

Example
Given this FD for this R(A,B,C,D,E,F,G)
A D
D CG
BE
EF
ABF
What are all the keys for this relation?
Solution: we look to the right side of FD and take all the attribute
which does not found in the FD
Here is AB
Then find AB+ = ABCDEFG  AB is a key

385

385

Example
Given this FD for this R(A,B,C,D)
AB C
CD
DA
What are all the keys for this relation?
Look to the right  B
Take all minimum combination with B
AB,BC,BD
Find
AB+=ABCD
BC+=ABCD
BD+=ABCD

ALL AB,BC,BD are keys

List all the super keys for R that are not key (not minimal number of attributes)
ABC
BCD
ABCD

386

386

185
 3/4/2024

Trivial and non trivial dependency

• A Functional dependency A1,A2,….An Bn is said to be
trivial dependency if B is one of A’s such as:
• title, year  title

• Not trivial if one of B’s not on A’s such as:

• title, year length, year
• Complete not trivial dependency if all B’s not found on
A’s

387

387

Example
Given this FD for this R(A,B,C,D)
AC D  completely non trivial
BC A  completely non trivial
DB  completely non trivial

What are the keys?

Look to right  C  take all combinations  AC, BC, CD

What are the super key?

ABC
ADC
BCD
ABCD
ABD not a super key because C must be found on left side

388

388

186
 3/4/2024

Normalization

389

389

Objectives of Normalization
• How tables that contain redundant data can suffer
from update anomalies, which can introduce
inconsistencies into a database.

• The rules associated with the most commonly used

normal forms, namely first (1NF), second (2NF), and
third (3NF).

• The identification of various types of update

anomalies such as insertion, deletion, and
modification anomalies.

390

390

187
 3/4/2024

Normalization
• Is a process of deleting different anomalies by
splitting the relation into two or more classes

• 1NF
• 2NF
• 3NF
• BCNF( Boyce coded normal form)
• 4NF
• 5NF

391

391

Data redundancy and update

anomalies
• Problems associated with data
redundancy are illustrated by
comparing the Staff and Branch tables
with the StaffBranch table.

392

392

188
 3/4/2024

Data redundancy and update

anomalies
• StaffBranch table has redundant data;
the branch information are repeated for
every member of staff.
• In contrast, the branch information
appears only once for each branch in
the Branch table and only the branch
number (branchNo) is repeated in the
Staff table, to represent where each
member of staff is located.

393

393

Data redundancy and update

anomalies

394

394

189
 3/4/2024

Data redundancy and update

anomalies

395

395

Data redundancy and update

anomalies
• Tables that contain redundant
information may potentially suffer from
update anomalies.

• Types of update anomalies include

– insertion
– deletion
– modification

396

396

190
 3/4/2024

Relationship of Normal Forms

397

397

Stages of Normalisation

Remove repeating groups

First normal form
(1NF)
Remove partial dependencies
Second normal form
(2NF)
Remove transitive dependencies
Third normal form
(3NF)
Remove remaining functional
dependency anomalies
Boyce-Codd normal
form (BCNF)
Remove multivalued dependencies
Fourth normal form
(4NF)
Remove remaining anomalies
Fifth normal form
(5NF) 398

398

191
 3/4/2024

First normal form (1NF)

• A table in which the intersection of
every column and record contains only
one value.

399

399

Branch table is not in 1NF

400

400

192
 3/4/2024

Converting Branch table to 1NF

401

401

Second normal form (2NF)

• 2NF only applies to tables with
composite primary keys.

• A table that is in 1NF and in which the

values of each non-primary-key column
can be worked out from the values in
all the columns that make up the
primary key.

402

402

193
 3/4/2024

TempStaffAllocation table is not

in 2NF

403

403

Second normal form (2NF)

• Formal definition of 2NF is a table that is
in 1NF and every non-primary-key
column is fully functional dependent on
the primary key.
• Full functional dependency indicates that
if A and B are columns of a table, B is
fully dependent on A if B is functionally
dependent on A but not on any proper
subset of A.
404

404

194
 3/4/2024

Converting TempStaffAllocation
table to 2NF

405

405

Third normal form (3NF)

• The formal definition of 3NF is a table
that is in 1NF and 2NF and in which no
non-primary-key column is transitively
dependent on the primary key.

406

406

195
 3/4/2024

StaffBranch table is not in 3NF

407

407

Third normal form (3NF)

• For example, consider a table with A, B,
and C. If B is functional dependent on A
(A  B) and C is functional dependent
on B (B  C), then C is transitively
dependent on A via B (provided that A is
not functionally dependent on B or C).
• If a transitive dependency exists on the
primary key, the table is not in 3NF.

408

408

196
 3/4/2024

Converting the StaffBranch table

to 3NF

409

409

Boyce-Codd Normal Form (BCNF)

• A relation is in BCNF, if and only if every

determinant is a candidate key.
• A relation is in Boyce-Codd normal form
(BCNF) if for every FD A  B either
– B is contained in A (the FD is trivial), or
– A contains a candidate key of the relation,

410

410

197
 3/4/2024

Example

Hourly_Emps

• SNLRWH has FDs S ® SNLRWH and R ® W

• Q: Is this relation in BCNF?

No, The second FD causes a violation;

W values repeatedly associated with R values.

411

Decomposing a Relation
• Easiest fix is to create a relation RW to store
these associations, and to remove W from
the main schema:

•Q: Are both of these relations are now in BCNF?

•Decompositions should be used only when needed.
–Q: potential problems of decomposition?
412

198
 3/4/2024

Problems with Decompositions

• There are three potential problems to consider:
1) May be impossible to reconstruct the original relation! (Lossy Decomposition)
• Fortunately, not in the SNLRWH example.
2) Dependency checking may require joins (not Dependency Preserving)
• Fortunately, not in the SNLRWH example.
3) Some queries become more expensive.
• e.g., How much does Guldu earn?

Tradeoff: Must consider these issues vs. redundancy.

(Well, not usually #1)

413

Lossless Decomposition (example)



414

199
 3/4/2024

Lossy Decomposition (example)

415

416

416

200
 3/4/2024

Lossless Join Decompositions

• Decomposition of R into X and Y is lossless w.r.t. a set

of FDs F if, for every instance r that satisfies F:
p X (r)  p Y (r) = r
• It is always true that r  p X (r)  p Y (r)
– In general, the other direction does not hold! If it does,
the decomposition is lossless-join.
• Definition extended to decomposition into 3 or more
relations in a straightforward way.
• It is essential that all decompositions used to deal with
redundancy be lossless! (Avoids Problem #1)

417

More on Lossless Decomposition

• The decomposition of R into X and Y is
lossless with respect to F if and only if the
closure of F contains: i.e. the common attributes
form a superkey for one
X  Y ® X, or side or the other

XY®Y
in example: decomposing ABC into AB and BC is
lossy, because intersection (i.e., “B”) is not a key
of either resulting relation.
• Useful result: If W ® Z holds over R and W  Z is
empty, then decomposition of R into R-Z and WZ is
loss-less.

418

201
 3/4/2024

Lossless Decomposition (example)

But, now we can’t check A  B without doing a join!

419

Dependency Preserving Decomposition

• Dependency preserving decomposition (Intuitive):
– If R is decomposed into X, Y and Z, and we
enforce the FDs that hold individually on X, on Y
and on Z, then all FDs that were given to hold
on R must also hold. (Avoids Problem #2 on
our list.)
• Why do we care??
• Projection of set of FDs F : If R is decomposed into
X and Y the projection of F on X (denoted FX ) is the
set of FDs U  V in F+ (closure of F , not just F ) such
that all of the attributes U, V are in X. (same holds
for Y of course)

420

202
 3/4/2024

Dependency Preserving Decompositions (Contd.)

• Decomposition of R into X and Y is dependency

preserving if (FX  FY ) + = F +
– i.e., if we consider only dependencies in the closure F + that
can be checked in X without considering Y, and in Y without
considering X, these imply all dependencies in F +.
• Important to consider F + in this definition:
– ABC, A ® B, B ® C, C ® A, decomposed into AB and BC.
– Is this dependency preserving? Is C ® A preserved?????
• note: F + contains F  {A ® C, B ® A, C ® B}, so…

• FAB contains A ®B and B ® A; FBC contains B ® C and C ® B

• So, (FAB  FBC)+ contains C ® A

421

Decomposition into BCNF

• Consider relation R with FDs F. If X  Y violates
BCNF, decompose R into R - Y and XY (guaranteed
to be loss-less).
– Repeated application of this idea will give us a collection
of relations that are in BCNF; lossless join decomposition,
and guaranteed to terminate.
– e.g., CSJDPQV, key C, JP ® C, SD ® P, J ® S
– {contractid, supplierid, projectid,deptid,partid, qty, value}
– To deal with SD ® P, decompose into SDP, CSJDQV.
– To deal with J ® S, decompose CSJDQV into JS and
CJDQV
– So we end up with: SDP, JS, and CJDQV
• Note: several dependencies may cause violation of
BCNF. The order in which we ``deal with’’ them
could lead to very different sets of relations!

422

203
 3/4/2024

BCNF and Dependency Preservation

• In general, there may not be a dependency preserving
decomposition into BCNF.
– e.g., CSZ, CS ® Z, Z ® C
– Can’t decompose while preserving 1st FD; not in BCNF.
• Similarly, decomposition of CSJDPQV into SDP, JS and
CJDQV is not dependency preserving (w.r.t. the FDs
JP  C, SD  P and J  S).
• {contractid, supplierid, projectid,deptid,partid, qty, value}
– However, it is a lossless join decomposition.
– In this case, adding JPC to the collection of relations gives
us a dependency preserving decomposition.
• but JPC tuples are stored only for checking the f.d. (Redundancy!)

423

PL\SQL
• A programming language
– Allows you to write a high level language
using SQL
– Extension to SQL
• Used to write a series of computer
instructions to accomplish a given task
• An Oracle proprietary language found in
many Oracle products
• Has a predefined structure
438

438

204
 3/4/2024

Example
• DECLARE
• N1 NUMBER; Declaration Section: Optional
• N2 NUMBER;
• RESULT NUMBER;
• BEGIN
• N1:= 4; Body of the program:
• N2:=5; Mandatory
• RESULT:= N1+N2;
• DBMS_OUTPUT.PUT_LINE(RESULT);
• END;
• /

439

439

Structure of a Typical PL/SQL program

• DECLARE --optional
– Variable Declaration
• BEGIN --mandatory
– Statements of the program
• EXCEPTION --optional
– Error Handling
• END;
• /
440

440

205
 3/4/2024

Example programs

441

441

Variable Declaration - Datatypes

• Datatypes:
• NUMBER - store numeric data

• CHAR - store fixed length character data

• VARCHAR2(n) – Store variable length character data

• DATE – Store date values (DD-MON-YY format)

• BOOLEAN- Store TRUE, FALSE, or NULL that can be used in

conditional statements

• Examples
– BDAY DATE;
– VSALARY NUMBER(10,2);
– VENAME VARCHAR2(10);
– ACTIVE BOOLEAN;

442

442

206
 3/4/2024

Variable Declaration
• Initializing a variable during declaration:
– Using the Assignment operator ‘:=‘
• Examples
– BDAY DATE:=’10-FEB-99’;
– VSALARY NUMBER(10):=10;
– VENAMEVARCHAR2(10):=‘SID’;
– ACTIVE BOOLEAN:=FALSE;
• One can define a constant in the declaration section and
it has to be initialized
• Example
– BDAY CONSTANT DATE :=’10-FEB-99’;
• One can enforce a NOT NULL constraint on a variable
– NOT NULL variables has to be initialized
• Example:
– BDAY DATE NOT NULL:=’10-FEB-99’; 443

443

The Body of the PL/SQL Block

• Contained between BEGIN and END;
• Each executable statement is terminated
with a semi colon
• The termination of the block is indicatied
by a forward slash as the first character on
a new line.

444

444

207
 3/4/2024

DBMS_OUTPUT.PUT_LINE
• Used to display a string on the screen
• The string should be enclosed in a single quotes- the value contained in
the variable will be displayed on the screen.
• If the variable is not a character variable, use the to_char function
around the variable name.
• Combine a string with a variable with the use of concatenation character
(double pipe ||)
• Example:
DECLARE
VSALARY NUMBER(10,2):=4500;
BEGIN
DBMS_OUTPUT.PUT_LINE(‘THE SALARY IS ’||TO_CHAR(VSALARY));
END;
/

445

445

SQL PLUS commands

• ed filename - open the notepad so you
can type in your statements in a file
• set serverout on – enable the output so the
DBMS_OUTPUT function would work and
you see the results
• show user – shows the current user of the
SQL plus
• save filename – saves the statements written
on the SQL plus prompt to a filename
446

446

208
 3/4/2024

SELECT statements and PL/SQL

• Syntax of a SELECT statement in a PL/SQL block
SELECT column1, column2, … INTO memory_variable1, memory_variable2,…
FROM table_name
[WHERE condition and other Clauses]
• Example:
DECLARE
VSALARY NUMBER(10,2);
BEGIN
SELECT SALARY INTO VSALARY
FROM EMPLOYEE
WHERE FIRST_NAME=‘Ahmad’;

DBMS_OUTPUT.PUT_LINE(‘The salary of Ahmad is:’ TO_CHAR(VSALARY));

END;
/

447

447

DML statements in PL/SQL

• In PL/SQL, you write your DML statements in the body block as the
select statement.
• After the DML statement you use either COMMIT or ROLLBACK to
make the change permanent or undo it.
• Example:
BEGIN
INSERT INTO STUDENT VALUES (‘Ahmad’, 20,
‘NABLUS’);
UPDATE STUDENT SET AGE=21 where
name=‘Sami’;
COMMIT;
END;
/

448

448

209
 3/4/2024

DML and PL/SQL example

449

449

•
Conditional Statements (IF)
Syntax:
IF condition THEN
statement;
[statements;]
[ELSIF condition THEN
statement;
[statements;]]

[ELSE
statement;
[statements]]
END IF;

• Example:
DECLARE
VSALARY NUMBER(10,2);
BEGIN
SELECT SALARY INTO VSALARY
FROM EMPLOYEE WHERE FIRST_NAME=‘Ahmad’;
IF VSALARY > 6000 THEN
DBMS_OUTPUT.PUT_LINE(‘Earns more than normal’);
END IF;
450
END;
/
450

210
 3/4/2024

CASE Syntax
CASE memory_variable
WHEN value1 THEN statement(s);
WHEN value2 THEN statement(s);
[WHEN value3 THEN statement(s);
ELSE statement(s);]
END CASE;
• OR
CASE
WHEN condition1 THEN statement(s);
[WHEN condition2 THEN statement(s);
WHEN condition3 THEN statement(s);
ELSE statement(s);]
END CASE;

451

451

CASE Example
CASE VSALARY
WHEN 6000 THEN
…
WHEN 4000 THEN
…
WHEN 2000 THEN
…
ELSE …;
END CASE;
• OR
CASE
WHEN VSALARY> 6000 THEN
…
WHEN VCITY=‘NABLUS’ THEN
…
WHEN VJOB=‘STUDENT’ THEN
…
ELSE …;
END CASE;
452

452

211
 3/4/2024

LOOP Statements
• Allows to perform a series of actions
repeatedly.
• Three types of LOOP statements
– LOOP … END LOOP
– WHILE (condition) LOOP … END LOOP;
– FOR … LOOP … END LOOP;

453

453

LOOP … END LOOP;

LOOP
DBMS_OUTPUT.PUT_LINE(‘SALAM’);
C1:=C1+1;
EXIT WHEN C1=10;
END LOOP;

454

454

212
 3/4/2024

WHILE LOOP
DECLARE
C1 NUMBER:=1;
BEGIN
WHILE (C1<=5) LOOP
DBMS_OUTPUT.PUT_LINE(‘SALAM’);
C:=C+1;
END LOOP;
END;
/ 455

455

Creating Triggers

456

456

213
 3/4/2024

Objectives

• After completing this lesson, you

should be able to do the following:
– Describe the different types of triggers
– Describe database triggers and their
uses
– Create database triggers
– Describe database trigger-firing rules
– Remove database triggers

457

457

Types of Triggers
• A trigger:
– Is a PL/SQL block or a PL/SQL procedure
associated with a table, view, schema, or
database
– Executes implicitly whenever a particular event
takes place
– Can be either of the following:
• Application trigger: Fires whenever an event occurs
with a particular application
• Database trigger: Fires whenever a data event (such
as DML) or system event (such as logon or
shutdown) occurs on a schema or database

458

458

214
 3/4/2024

Guidelines for Designing

Triggers
– You can design triggers to:
• Perform related actions
• Centralize global operations
– You must not design triggers:
• Where functionality is already built into the
Oracle server
• That duplicate other triggers
– You can create stored procedures and
invoke them in a trigger, if the PL/SQL code
is very lengthy.
– The excessive use of triggers can result in
complex interdependencies, which may be
difficult to maintain in large applications.
459

459

Creating DML Triggers

• Create DML statement or row type triggers by
using:[OR REPLACE] TRIGGER trigger_name
CREATE
timing
event1 [OR event2 OR event3]
ON object_name
[[REFERENCING OLD AS old | NEW AS new]
FOR EACH ROW
[WHEN (condition)]]
trigger_body

– A statement trigger fires once for a DML statement.

– A row trigger fires once for each row affected.
• Note: Trigger names must be unique with
respect to other triggers in the same schema.

460

460

215
 3/4/2024

Types of DML Triggers

• The trigger type determines if the body
executes for each row or only once for the
triggering statement.
– A statement trigger:
• Executes once for the triggering event
• Is the default type of trigger
• Fires once even if no rows are affected at all
– A row trigger:
• Executes once for each row affected by the triggering
event
• Is not executed if the triggering event does not affect
any rows
• Is indicated by specifying the FOR EACH ROW clause

461

461

Trigger Timing
• When should the trigger fire?
– BEFORE: Execute the trigger body before the
triggering DML event on a table.
– AFTER: Execute the trigger body after the triggering
DML event on a table.
– INSTEAD OF: Execute the trigger body instead of
the triggering statement. This is used for views that
are not otherwise modifiable.
• Note: If multiple triggers are defined for the
same object, then the order of firing triggers is
arbitrary.

462

462

216
 3/4/2024

Trigger-Firing Sequence
• Use the following firing sequence for a trigger on a
table when a single row is manipulated:
DML statement
INSERT INTO departments
(department_id,department_name, location_id)
VALUES (400, 'CONSULTING', 2400);

Triggering action BEFORE

statement trigger

… BEFORE row trigger

AFTER row trigger
AFTER statement trigger
463

463

Trigger-Firing Sequence
Use the following firing sequence for a trigger on a table
when many rows are manipulated:
UPDATE employees
SET salary = salary * 1.1
WHERE department_id = 30;

BEFORE statement trigger

BEFORE row trigger
AFTER row trigger
...
BEFORE row trigger
AFTER
... row trigger
AFTER statement trigger
464

464

217
 3/4/2024

Trigger Event Types and Body

• A trigger event:
– Determines which DML statement causes the
trigger to execute
– Types are:
• INSERT
• UPDATE [OF column]
• DELETE
• A trigger body:
– Determines what action is performed
– Is a PL/SQL block or a CALL to a procedure

465

465

Creating a DML Statement

Trigger
Application
EMPLOYEES table
INSERT INTO EMPLOYEES...;

SECURE_EMP trigger

CREATE OR REPLACE TRIGGER secure_emp

BEFORE INSERT ON employees BEGIN
IF (TO_CHAR(SYSDATE,'DY') IN ('SAT','SUN')) OR
(TO_CHAR(SYSDATE,'HH24:MI')
NOT BETWEEN '08:00' AND '18:00') THEN
RAISE_APPLICATION_ERROR(-20500, 'You may insert'
||' into EMPLOYEES table only during '
||' business hours.');
END IF;
END;
466

466

218
 3/4/2024

Testing SECURE_EMP

INSERT INTO employees (employee_id, last_name,

first_name, email, hire_date, job_id,
salary, department_id)
VALUES (300, 'Smith', 'Rob', 'RSMITH', SYSDATE,
'IT_PROG', 4500, 60);

467

467

Using Conditional Predicates

CREATE OR REPLACE TRIGGER secure_emp BEFORE
INSERT OR UPDATE OR DELETE ON employees BEGIN
IF (TO_CHAR(SYSDATE,'DY') IN ('SAT','SUN')) OR
(TO_CHAR(SYSDATE,'HH24')
NOT BETWEEN '08' AND '18') THEN
IF DELETING THEN RAISE_APPLICATION_ERROR(
-20502,'You may delete from EMPLOYEES table'||
'only during business hours.');
ELSIF INSERTING THEN RAISE_APPLICATION_ERROR(
-20500,'You may insert into EMPLOYEES table'||
'only during business hours.');
ELSIF UPDATING('SALARY') THEN
RAISE_APPLICATION_ERROR(-20503, 'You may '||
'update SALARY only during business hours.');
ELSE RAISE_APPLICATION_ERROR(-20504,'You may'||
' update EMPLOYEES table only during'||
' normal hours.');
END IF;
END IF;
END;
468

468

219
 3/4/2024

Creating a DML Row Trigger

CREATE OR REPLACE TRIGGER restrict_salary

BEFORE INSERT OR UPDATE OF salary ON employees
FOR EACH ROW
BEGIN
IF NOT (:NEW.job_id IN ('AD_PRES', 'AD_VP'))
AND :NEW.salary > 15000 THEN
RAISE_APPLICATION_ERROR (-20202,
'Employee cannot earn more than $15,000.');
END IF;
END;
/

469

469

Using OLD and NEW Qualifiers

CREATE OR REPLACE TRIGGER audit_emp_values

AFTER DELETE OR INSERT OR UPDATE ON employees
FOR EACH ROW
BEGIN
INSERT INTO audit_emp(user_name, time_stamp, id,
old_last_name, new_last_name, old_title,
new_title, old_salary, new_salary)
VALUES (USER, SYSDATE, :OLD.employee_id,
:OLD.last_name, :NEW.last_name, :OLD.job_id,
:NEW.job_id, :OLD.salary, :NEW.salary);
END;
/

470

470

220
 3/4/2024

Using OLD and NEW Qualifiers:

Example Using audit_emp
INSERT INTO employees
(employee_id, last_name, job_id, salary, ...)
VALUES (999, 'Temp emp', 'SA_REP', 6000,...);

UPDATE employees
SET salary = 7000, last_name = 'Smith'
WHERE employee_id = 999;

SELECT user_name, timestamp, ...

FROM audit_emp;

471

471

Restricting a Row Trigger:

Example
CREATE OR REPLACE TRIGGER derive_commission_pct
BEFORE INSERT OR UPDATE OF salary ON employees
FOR EACH ROW
WHEN (NEW.job_id = 'SA_REP')
BEGIN
IF INSERTING THEN
:NEW.commission_pct := 0;
ELSIF :OLD.commission_pct IS NULL THEN
:NEW.commission_pct := 0;
ELSE
:NEW.commission_pct := :OLD.commission_pct+0.05;
END IF;
END;
/

472

472

221
 3/4/2024

Summary of Trigger Execution

Model
1. Execute all BEFORE STATEMENT triggers.
2. Loop for each row affected:
a.Execute all BEFORE ROW triggers.
b.Execute the DML statement and perform integrity
constraint checking.
c. Execute all AFTER ROW triggers.
3. Execute all AFTER STATEMENT triggers.
• Note: Integrity checking can be deferred until
the COMMIT operation is performed.

473

473

Implementing an Integrity
Constraint
with a Trigger
UPDATE employees SET department_id = 999
WHERE employee_id = 170;
-- Integrity constraint violation error
CREATE OR REPLACE TRIGGER employee_dept_fk_trg
AFTER UPDATE OF department_id
ON employees FOR EACH ROW
BEGIN
INSERT INTO departments VALUES(:new.department_id,
'Dept '||:new.department_id, NULL, NULL);
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
NULL; -- mask exception if department exists
END;
/
UPDATE employees SET department_id = 999
WHERE employee_id = 170;
-- Successful after trigger is fired
474

474

222
 3/4/2024

INSTEAD OF Triggers
Application

INSERT INTO my_view

. . .;

INSERT
TABLE1
INSTEAD OF trigger

UPDAT
MY_VIE
E
W
TABLE2

475

475

Creating an INSTEAD OF
Trigger
• Perform the INSERT into EMP_DETAILS that is based
on EMPLOYEES and DEPARTMENTS tables:
INSERT INTO emp_details
VALUES (9001,'ABBOTT',3000, 10, 'Administration');

1 INSTEAD OF INSERT
into EMP_DETAILS

2 INSERT into NEW_EMPS 3 UPDATE NEW_DEPTS

…
…
476

476

223
 3/4/2024

Creating an INSTEAD OF
Trigger
• Use INSTEAD OF to perform DML on complex views:
CREATE TABLE new_emps AS
SELECT employee_id,last_name,salary,department_id
FROM employees;

CREATE TABLE new_depts AS

SELECT d.department_id,d.department_name,
sum(e.salary) dept_sal
FROM employees e, departments d
WHERE e.department_id = d.department_id;

CREATE VIEW emp_details AS

SELECT e.employee_id, e.last_name, e.salary,
e.department_id, d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id
GROUP BY d.department_id,d.department_name;
477

477

Comparison of Database Triggers

and
Stored Procedures

Triggers Procedures

Defined with CREATE TRIGGER Defined with CREATE PROCEDURE

Data dictionary contains source code Data dictionary contains source
in USER_TRIGGERS. code in USER_SOURCE.
Implicitly invoked by DML Explicitly invoked
COMMIT, SAVEPOINT, and COMMIT, SAVEPOINT, and
ROLLBACK are not allowed. ROLLBACK are allowed.

479

479

224
 3/4/2024

Comparison of Database Triggers

and Oracle Forms Triggers

INSERT INTO EMPLOYEES

. . .;

EMPLOYEES CHECK_SAL trigger

table

BEFORE
INSERT
… row

480

480

Managing Triggers
– Disable or reenable a database trigger:
ALTER TRIGGER trigger_name DISABLE | ENABLE

– Disable or reenable all triggers for a table:

ALTER TABLE table_name DISABLE | ENABLE
ALL TRIGGERS

– Recompile a trigger for a table:

ALTER TRIGGER trigger_name COMPILE

481

481

225
 3/4/2024

Removing Triggers
• To remove a trigger from the database, use the
DROP TRIGGER statement:
DROP TRIGGER trigger_name;

• Example:
DROP TRIGGER secure_emp;

• Note: All triggers on a table are removed when

the table is removed.

482

482

Testing Triggers
– Test each triggering data operation, as well as
nontriggering data operations.
– Test each case of the WHEN clause.
– Cause the trigger to fire directly from a basic data
operation, as well as indirectly from a procedure.
– Test the effect of the trigger on other triggers.
– Test the effect of other triggers on the trigger.

483

483

226
 3/4/2024

Summary
• In this lesson, you should have learned how to:
– Create database triggers that are invoked by DML
operations
– Create statement and row trigger types
– Use database trigger-firing rules
– Enable, disable, and manage database triggers
– Develop a strategy for testing triggers
– Remove database triggers

484

484

Practice 10: Overview

• This practice covers the following topics:
– Creating row triggers
– Creating a statement trigger
– Calling procedures from a trigger

485

485

227
 3/4/2024

Creating Stored Procedures

486

486

Objectives
– Describe and create a procedure
– Create procedures with parameters
– Differentiate between formal and actual
parameters
– Use different parameter-passing modes
– Invoke a procedure
– Handle exceptions in procedures
– Remove a procedure

487

487

228
 3/4/2024

What Is a Procedure?

• A procedure:
– Is a type of subprogram that performs an
action
– Can be stored in the database as a
schema object
– Promotes reusability and maintainability

488

488

Syntax for Creating Procedures

– Use CREATE PROCEDURE followed by the name,
optional parameters, and keyword IS or AS.
– Add the OR REPLACE option to overwrite an
existing procedure.
– Write a PL/SQL block containing local variables,
a BEGIN, and an END (or END procedure_name).

CREATE [OR REPLACE] PROCEDURE procedure_name

[(parameter1 [mode] datatype1,
parameter2 [mode] datatype2, ...)]
IS|AS
[local_variable_declarations; …]
BEGIN
PL/SQL Block
-- actions;
END [procedure_name];
489

489

229
 3/4/2024

Developing Procedures

1 Edit 2 Load

file.sql
3 Create (compile and store)

4 Execute
Use SHOW ERRORS
for compilation errors
490

490

What Are Parameters?

• Parameters:
– Are declared after the subprogram name in the
PL/SQL header
– Pass or communicate data between the caller
and the subprogram
– Are used like local variables but are dependent
on their parameter-passing mode:
• An IN parameter (the default) provides values for a
subprogram to process.
• An OUT parameter returns a value to the caller.
• An IN OUT parameter supplies an input value, which
may be returned (output) as a modified value.

491

491

230
 3/4/2024

Formal and Actual Parameters

– Formal parameters: Local variables declared in
the parameter list of a subprogram specification
Example:
CREATE PROCEDURE raise_sal(id NUMBER,sal NUMBER) IS
BEGIN ...
END raise_sal;
– Actual parameters: Literal values, variables, or
expressions used in the parameter list of the
called subprogram
Example:
emp_id := 100;
raise_sal(emp_id, 2000)
492

492

Procedural Parameter Modes

– Parameter modes are specified in the formal
parameter declaration, after the parameter name
and before its data type.
– The IN mode is the default if no mode is specified.

CREATE PROCEDURE procedure(param [mode] datatype)

...

Modes

IN (default)
Calling
OUT
environment
IN OUT

Procedure
493

493

231
 3/4/2024

Using IN Parameters: Example

CREATE OR REPLACE PROCEDURE raise_salary
(id IN employees.employee_id%TYPE,
percent IN NUMBER)
IS
BEGIN
UPDATE employees
SET salary = salary * (1 + percent/100)
WHERE employee_id = id;
END raise_salary;
/

EXECUTE raise_salary(176,10)

494

494

Using OUT Parameters:

Example
CREATE OR REPLACE PROCEDURE query_emp
(id IN employees.employee_id%TYPE,
name OUT employees.last_name%TYPE,
salary OUT employees.salary%TYPE) IS
BEGIN
SELECT last_name, salary INTO name, salary
FROM employees
WHERE employee_id = id;
END query_emp;

DECLARE
emp_name employees.last_name%TYPE;
emp_sal employees.salary%TYPE;
BEGIN
query_emp(171, emp_name, emp_sal); ...
END;
495

495

232
 3/4/2024

Viewing OUT Parameters with

iSQL*Plus
– Use PL/SQL variables that are printed with calls to
the DBMS_OUTPUT.PUT_LINE procedure.
SET SERVEROUTPUT ON
DECLARE
emp_name employees.last_name%TYPE;
emp_sal employees.salary%TYPE;
BEGIN
query_emp(171, emp_name, emp_sal);
DBMS_OUTPUT.PUT_LINE('Name: ' || emp_name);
DBMS_OUTPUT.PUT_LINE('Salary: ' || emp_sal);
END;
– Use iSQL*Plus host variables, execute QUERY_EMP
using host variables, and print the host variables.
VARIABLE name VARCHAR2(25)
VARIABLE sal NUMBER
EXECUTE query_emp(171, :name, :sal)
PRINT name sal
496

496

Calling PL/SQL Using Host

Variables
• A host variable (also known as a bind or a
global variable):
– Is declared and exists externally to the PL/SQL
subprogram. A host variable can be created in:
• iSQL*Plus by using the VARIABLE command
• Oracle Forms internal and UI variables
• Java variables
– Is preceded by a colon (:) when referenced in
PL/SQL code
– Can be referenced in an anonymous block but
not in a stored subprogram
– Provides a value to a PL/SQL block and
receives a value from a PL/SQL block

497

497

233
 3/4/2024

Using IN OUT Parameters:

Example
Calling environment
phone_no (before the call) phone_no (after the call)
'8006330575' '(800)633-0575'

CREATE OR REPLACE PROCEDURE format_phone

(phone_no IN OUT VARCHAR2) IS
BEGIN
phone_no := '(' || SUBSTR(phone_no,1,3) ||
')' || SUBSTR(phone_no,4,3) ||
'-' || SUBSTR(phone_no,7);
END format_phone;
/

498

498

Syntax for Passing Parameters

– Positional:
• Lists the actual parameters in the same order as the
formal parameters
– Named:
• Lists the actual parameters in arbitrary order and
uses the association operator (=>) to associate a
named formal parameter with its actual parameter
– Combination:
• Lists some of the actual parameters as positional and
some as named

499

499

234
 3/4/2024

Parameter Passing: Examples

CREATE OR REPLACE PROCEDURE add_dept(
name IN departments.department_name%TYPE,
loc IN departments.location_id%TYPE) IS
BEGIN
INSERT INTO departments(department_id,
department_name, location_id)
VALUES (departments_seq.NEXTVAL, name, loc);
END add_dept;
/

– Passing by positional notation

EXECUTE add_dept ('TRAINING', 2500)

– Passing by named notation

EXECUTE add_dept (loc=>2400, name=>'EDUCATION')
500

500

Using the DEFAULT Option for

Parameters
– Defines default values for parameters:
CREATE OR REPLACE PROCEDURE add_dept(
name departments.department_name%TYPE:='Unknown',
loc departments.location_id%TYPE DEFAULT 1700)
IS
BEGIN
INSERT INTO departments (...)
VALUES (departments_seq.NEXTVAL, name, loc);
END add_dept;
– Provides flexibility by combining the positional and
named parameter-passing syntax:
EXECUTE add_dept
EXECUTE add_dept ('ADVERTISING', loc => 1200)
EXECUTE add_dept (loc => 1200)
501

501

235
 3/4/2024

Summary of Parameter Modes

IN OUT IN OUT
Default mode Must be Must be specified
specified
Value is passed into Returned to Passed into
subprogram calling subprogram; returned to
environment calling environment
Formal parameter acts Uninitialized Initialized variable
as a constant variable
Actual parameter can be Must be a Must be a variable
a literal, expression, variable
constant, or initialized
variable
Can be assigned a Cannot be Cannot be assigned
default value assigned a default value
a default value
503

503

Invoking Procedures
– You can invoke parameters by:
• Using anonymous blocks
• Using another procedure, as in the following:
CREATE OR REPLACE PROCEDURE process_employees
IS
CURSOR emp_cursor IS
SELECT employee_id
FROM employees;
BEGIN
FOR emp_rec IN emp_cursor
LOOP
raise_salary(emp_rec.employee_id, 10);
END LOOP;
COMMIT;
END process_employees;
/
504

504

236
 3/4/2024

Handled Exceptions
Calling procedure Called procedure
PROCEDURE PROCEDURE
PROC1 ... PROC2 ...
IS IS
... ...
BEGIN BEGIN
... ...
PROC2(arg1); Exception raised
EXCEPTION
... ... Exception handled
EXCEPTION
... END PROC2;
END PROC1;
Control returns
to calling
procedure

505

505

Handled Exceptions: Example

CREATE PROCEDURE add_department(
name VARCHAR2, mgr NUMBER, loc NUMBER) IS
BEGIN
INSERT INTO DEPARTMENTS (department_id,
department_name, manager_id, location_id)
VALUES (DEPARTMENTS_SEQ.NEXTVAL, name, mgr, loc);
DBMS_OUTPUT.PUT_LINE('Added Dept: '||name);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Err: adding dept: '||name);
END;

CREATE PROCEDURE create_departments IS

BEGIN
add_department('Media', 100, 1800);
add_department('Editing', 99, 1800);
add_department('Advertising', 101, 1800);
END;
506

506

237
 3/4/2024

Exceptions Not Handled

Calling procedure Called procedure
PROCEDURE PROCEDURE
PROC1 ... PROC2 ...
IS IS
... ...
BEGIN BEGIN
... ...
PROC2(arg1); Exception raised
EXCEPTION
... ... Exception not
EXCEPTION
... END PROC2; handled
END PROC1;
Control returned
to exception
section of calling
procedure

507

507

Exceptions Not Handled:

Example
CREATE PROCEDURE add_department_noex(
name VARCHAR2, mgr NUMBER, loc NUMBER) IS
BEGIN
INSERT INTO DEPARTMENTS (department_id,
department_name, manager_id, location_id)
VALUES (DEPARTMENTS_SEQ.NEXTVAL, name, mgr, loc);
DBMS_OUTPUT.PUT_LINE('Added Dept: '||name);
END;

CREATE PROCEDURE create_departments_noex IS

BEGIN
add_department_noex('Media', 100, 1800);
add_department_noex('Editing', 99, 1800);
add_department_noex('Advertising', 101, 1800);
END;
508

508

238
 3/4/2024

Removing Procedures
• You can remove a procedure that is stored in
the database.
– Syntax:
DROP PROCEDURE procedure_name

– Example:
DROP PROCEDURE raise_salary;

509

509

Viewing Procedures in the Data

Dictionary
• Information for PL/SQL procedures is saved in
the following data dictionary views:
– View source code in the USER_SOURCE table to
view the subprograms that you own, or the
ALL_SOURCE table for procedures that are owned
by others who have granted you the EXECUTE
privilege.
SELECT text
FROM user_source
WHERE name='ADD_DEPARTMENT' and type='PROCEDURE'
ORDER BY line;
– View the names of procedures in USER_OBJECTS.

SELECT object_name
FROM user_objects
WHERE object_type = 'PROCEDURE';
510

510

239
 3/4/2024

Benefits of Subprograms
– Easy maintenance
– Improved data security and integrity
– Improved performance
– Improved code clarity

511

511

Summary
• In this lesson, you should have learned how to:
– Write a procedure to perform a task or an action
– Create, compile, and save procedures in the
database by using the CREATE PROCEDURE SQL
command
– Use parameters to pass data from the calling
environment to the procedure using three different
parameter modes: IN (the default), OUT, and IN
OUT
– Recognize the effect of handling and not handling
exceptions on transactions and calling procedures

512

512

240
 3/4/2024

Summary

– Remove procedures from the database

by using the DROP PROCEDURE SQL
command
– Modularize your application code by
using procedures as building blocks

513

513

Practice 1: Overview
• This practice covers the following topics:
– Creating stored procedures to:
• Insert new rows into a table using the supplied
parameter values
• Update data in a table for rows that match the
supplied parameter values
• Delete rows from a table that match the supplied
parameter values
• Query a table and retrieve data based on
supplied parameter values
– Handling exceptions in procedures
– Compiling and invoking procedures

514

514

241
 3/4/2024

Creating Stored Functions

518

518

Objectives
• After completing this lesson, you should be able
to do the following:
– Describe the uses of functions
– Create stored functions
– Invoke a function
– Remove a function
– Differentiate between a procedure and a function

519

519

242
 3/4/2024

Overview of Stored Functions

• A function:
– Is a named PL/SQL block that returns a
value
– Can be stored in the database as a
schema object for repeated execution
– Is called as part of an expression or is
used to provide a parameter value

520

520

Syntax for Creating Functions

• The PL/SQL block must have at least one RETURN
statement.
CREATE [OR REPLACE] FUNCTION function_name
[(parameter1 [mode1] datatype1, ...)]
RETURN datatype IS|AS
[local_variable_declarations; …]
BEGIN
-- actions; PL/SQL Block
RETURN expression;
END [function_name];

521

521

243
 3/4/2024

Developing Functions

1 Edit 2 Load

func.sql
3 Create (compile and store)

4 Execute
Use SHOW ERRORS
for compilation errors
522

522

Stored Function: Example

– Create the function:
CREATE OR REPLACE FUNCTION get_sal
(id employees.employee_id%TYPE) RETURN NUMBER IS
sal employees.salary%TYPE := 0;
BEGIN
SELECT salary
INTO sal
FROM employees
WHERE employee_id = id;
RETURN sal;
END get_sal;
/

– Invoke the function as an expression or as

a parameter value:
EXECUTE dbms_output.put_line(get_sal(100))
523

523

244
 3/4/2024

Ways to Execute Functions

– Invoke as part of a PL/SQL expression
• Using a host variable to obtain the result
VARIABLE salary NUMBER
EXECUTE :salary := get_sal(100)
• Using a local variable to obtain the result
DECLARE sal employees.salary%type;
BEGIN
sal := get_sal(100); ...
END;
– Use as a parameter to another subprogram
EXECUTE dbms_output.put_line(get_sal(100))
– Use in a SQL statement (subject to restrictions)
SELECT job_id, get_sal(employee_id) FROM employees;
524

524

Advantages of User-Defined
Functions in SQL Statements
– Can extend SQL where activities are too
complex, too awkward, or unavailable
with SQL
– Can increase efficiency when used in
the WHERE clause to filter data, as
opposed to filtering the data in the
application
– Can manipulate data values

525

525

245
 3/4/2024

Function in SQL Expressions:

Example
CREATE OR REPLACE FUNCTION tax(value IN NUMBER)
RETURN NUMBER IS
BEGIN
RETURN (value * 0.08);
END tax;
/
SELECT employee_id, last_name, salary, tax(salary)
FROM employees
WHERE department_id = 100;
Function created.

6 rows selected.
526

526

Locations to Call User-Defined

Functions
• User-defined functions act like built-in single-
row functions and can be used in:
– The SELECT list or clause of a query
– Conditional expressions of the WHERE and
HAVING clauses
– The CONNECT BY, START WITH, ORDER BY,
and GROUP BY clauses of a query
– The VALUES clause of the INSERT statement
– The SET clause of the UPDATE statement

527

527

246
 3/4/2024

Restrictions on Calling Functions

from SQL Expressions
– User-defined functions that are callable
from SQL expressions must:
• Be stored in the database
• Accept only IN parameters with valid SQL data
types, not PL/SQL-specific types
• Return valid SQL data types, not PL/SQL-
specific types
– When calling functions in SQL statements:
• Parameters must be specified with positional
notation
• You must own the function or have the EXECUTE
privilege

528

528

Controlling Side Effects When

Calling Functions from SQL
Expressions
• Functions called from:
– A SELECT statement cannot contain DML
statements
– An UPDATE or DELETE statement on a table T
cannot query or contain DML on the same table T
– SQL statements cannot end transactions (that is,
cannot execute COMMIT or ROLLBACK operations)

• Note: Calls to subprograms that break these

restrictions are also not allowed in the function.

529

529

247
 3/4/2024

Restrictions on Calling
Functions from SQL: Example
CREATE OR REPLACE FUNCTION dml_call_sql(sal NUMBER)
RETURN NUMBER IS
BEGIN
INSERT INTO employees(employee_id, last_name,
email, hire_date, job_id, salary)
VALUES(1, 'Frost', '[email protected]',
SYSDATE, 'SA_MAN', sal);
RETURN (sal + 100);
END;
UPDATE employees
SET salary = dml_call_sql(2000)
WHERE employee_id = 170;
UPDATE employees SET salary = dml_call_sql(2000)
*
ERROR at line 1:
ORA-04091: table PLSQL.EMPLOYEES is mutating,
trigger/function may not see it
ORA-06512: at "PLSQL.DML_CALL_SQL", line 4
530

530

Removing Functions

• Removing a stored function:

– You can drop a stored function by using the
following syntax:
DROP FUNCTION function_name
• Example:
DROP FUNCTION get_sal;
– All the privileges that are granted on a function
are revoked when the function is dropped.
– The CREATE OR REPLACE syntax is
equivalent to dropping a function and re-
creating it. Privileges granted on the function
remain the same when this syntax is used.
531

531

248
 3/4/2024

Viewing Functions in the Data

Dictionary
• Information for PL/SQL functions is stored in
the following Oracle data dictionary views:
– You can view source code in the
USER_SOURCE table for subprograms that you
own, or the ALL_SOURCE table for functions
owned by others who have granted you the
EXECUTE privilege.
SELECT text
FROM user_source
WHERE type = 'FUNCTION'
ORDER BY line;
– You can view the names of functions by using
USER_OBJECTS.
SELECT object_name
FROM user_objects
WHERE object_type = 'FUNCTION';
532

532

Procedures Versus Functions

Procedures Functions
Execute as a PL/SQL Invoke as part of an
statement expression
Do not contain RETURN Must contain a RETURN
clause in the header clause in the header
Can return values (if any) in Must return a single value
output parameters
Can contain a RETURN Must contain at least one
statement without a value RETURN statement

533

533

249
 3/4/2024

Summary
• In this lesson, you should have learned how to:
– Write a PL/SQL function to compute and return a
value by using the CREATE FUNCTION SQL
statement
– Invoke a function as part of a PL/SQL expression
– Use stored PL/SQL functions in SQL statements
– Remove a function from the database by using the
DROP FUNCTION SQL statement

534

534

250