DBMS PPT - 1

Relational Database Concept
• Dr. E. F. Codd proposed the relational model for

database systems in 1970.
• It is the basis for the relational database
management system (RDBMS).
• The relational model consists of the
following:
– Collection of objects or relations
– Set of operators to act on the relations
– Data integrity for accuracy and
consistency
05/21/2023
Definition of a Relational Database
A relational database is a collection of relations or

two-dimensional tables.
Oracle
server
Table name: EMPLOYEES Table name: DEPARTMENTS
… …
05/21/2023
Data Models
Model of
system
Entity model of
in client’s
client’s model
mind
Table model
of entity model Oracle
server
Tables on disk
05/21/2023
Entity Relationship Model
• Create an entity relationship diagram from

business specifications or narratives:
EMPLOYEE DEPARTMENT
assigned to
#* number #* number
* name * name
job title composed of location
o o
• Scenario
– “. . . Assign one or more employees to a
department . . .”
– “. . . Some departments do not yet have assigned
employees . . .”
05/21/2023
Entity Relationship
Modeling Conventions
Entity Attribute
• Singular, unique name • Singular name
• Uppercase • Lowercase
• Soft box • Mandatory marked with *
• Synonym in parentheses • Optional marked with “o”
EMPLOYEE DEPARTMENT
#* number assigned to
#* number
* name * name
job title composed of location
o o
Unique identifier (UID)

Primary marked with “#”
Secondary marked with “(#)”
05/21/2023
Relating Multiple Tables
• Each row of data in a table is uniquely identified by a

primary key (PK).
• You can logically relate data from multiple tables using
foreign keys (FK).
Table name: DEPARTMENTS

Table name: EMPLOYEES
…
Primary key Foreign key Primary key
05/21/2023
Relational Database Properties
A relational database:
• Can be accessed and modified by executing
structured query language (SQL)
statements
• Contains a collection of tables with no physical
pointers
• Uses a set of operators
05/21/2023
Communicating with an RDBMS
Using SQL
SQL statement is entered. Statement is sent to

Oracle server.
SELECT department_name
FROM departments;
Oracle
server
05/21/2023
Oracle’s Relational Database Management
System
Oracle
server
User tables Data

dictionary
05/21/2023
SQL Statements
SELECT
INSERT Data manipulation language (DML)
UPDATE
DELETE
CREATE
MERGE
ALTER
DROP Data definition language (DDL)
RENAME
TRUNCATE
COMMENT
GRANT Data control language (DCL)

REVOKE
COMMIT
ROLLBACK Transaction control
SAVEPOINT
05/21/2023
Tables Used in the Course
EMPLOYEES
DEPARTMENTS JOB_GRADES
05/21/2023
Cont..
• Oracle Database 10g is the database for grid

computing.
• The database is based on the object relational
database management system.
• Relational databases are composed of relations,
managed by relational operations, and governed by
data integrity constraints.
• With the Oracle server, you can store and manage
information by using the SQL language and
PL/SQL engine.
05/21/2023
Retrieving Data Using
the SQL SELECT
Statement
05/21/2023
Capabilities of SQL SELECT
Statements
Projection Selection
Table 1 Table 1
Join
Table 1 Table 2
05/21/2023
Basic SELECT
Statement
SELECT *|{[DISTINCT] column|expression [alias],...}

FROM table;
• SELECT identifies the columns to be displayed.

• FROM identifies the table containing those
columns.
05/21/2023
Selecting All Columns
SELECT *
FROM departments;
05/21/2023
Selecting Specific Columns
SELECT department_id, location_id

FROM departments;
05/21/2023
Writing SQL Statements
• SQL statements are not case sensitive.

• SQL statements can be on one or more lines.
• Keywords cannot be abbreviated or split
across lines.
• Clauses are usually placed on separate
lines.
05/21/2023
Arithmetic Expressions
Create expressions with number and date data by using

arithmetic operators.
Operator Description
+ Add
- Subtract
* Multiply
/ Divide
05/21/2023
Using Arithmetic Operators
SELECT last_name, salary, salary + 300

salary*300
FROM employees;
05/21/2023
Operator Precedence
SELECT last_name, salary, 12*salary+100

FROM employees; 1
…
SELECT last_name, salary, 12*(salary+100)
FROM employees;
2
05/21/2023
Defining a Null Value
• A null is a value that is unavailable, unassigned,

unknown, or inapplicable.
• A null is not the same as a zero or a blank
space.
SELECT last_name, job_id, commission_pct
salary,commission_pct FROM
employees;
05/21/2023
Null Values
in Arithmetic Expressions
Arithmetic expressions containing a null value evaluate to

null.
SELECT last_name, 12*salary*commission_pct

FROM employees;
……
05/21/2023
Defining a Column Alias
A column alias:
• Renames a column heading
• Is useful with calculations
• Immediately follows the column name (There can
also be the optional AS keyword between the
column name and alias.)
• Requires double quotation marks if it contains
spaces or special characters or if it is case
sensitive
05/21/2023
Using Column Aliases
SELECT last_name AS name, commission_pct comm

FROM employees;
SELECT last_name "Name" , salary*12 "Annual Salary"

FROM employees;
05/21/2023
Concatenation Operator
A concatenation operator:
• Links columns or character strings to other
columns
• Is represented by two vertical bars (||)
• Creates a resultant column that is a character
expression
SELECT last_name||job_id AS "Employees"
FROM employees;
05/21/2023
Literal Character Strings
• A literal is a character, a number, or a date that is

included in the SELECT statement.
• Date and character literal values must be enclosed
by single quotation marks.
• Each character string is output once for each
row returned.
05/21/2023
Using Literal Character Strings
SELECT last_name ||' is a '||job_id

AS "Employee Details"
FROM employees;
05/21/2023
Duplicate Rows
The default display of queries is all rows, including

duplicate rows.
SELECT department_id
FROM employees; 1
…
SELECT DISTINCT department_id
FROM employees; 2
…
05/21/2023
Displaying Table Structure
DESCRIBE command to display the structure of a

table:
DESC[RIBE] tablename
05/21/2023
Displaying Table Structure
DESCRIBE employees
05/21/2023
Thank you
05/21/2023

DBMS PPT - 1

Uploaded by

Copyright:

Available Formats

DBMS PPT - 1

Uploaded by

Document Information

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

DBMS PPT - 1

Uploaded by

Copyright:

Available Formats

Relational Database Concept

• Dr. E. F. Codd proposed the relational model for

A relational database is a collection of relations or

Table name: EMPLOYEES Table name: DEPARTMENTS

• Create an entity relationship diagram from

Unique identifier (UID)

• Each row of data in a table is uniquely identified by a

Table name: DEPARTMENTS

SQL statement is entered. Statement is sent to

User tables Data

INSERT Data manipulation language (DML)

GRANT Data control language (DCL)

• Oracle Database 10g is the database for grid

SELECT *|{[DISTINCT] column|expression [alias],...}

• SELECT identifies the columns to be displayed.

SELECT department_id, location_id

• SQL statements are not case sensitive.

Create expressions with number and date data by using

SELECT last_name, salary, salary + 300

SELECT last_name, salary, 12*salary+100

• A null is a value that is unavailable, unassigned,

Arithmetic expressions containing a null value evaluate to

SELECT last_name, 12*salary*commission_pct

SELECT last_name AS name, commission_pct comm

SELECT last_name "Name" , salary*12 "Annual Salary"

• A literal is a character, a number, or a date that is

SELECT last_name ||' is a '||job_id

The default display of queries is all rows, including

DESCRIBE command to display the structure of a

You might also like

SELECT last_name, 12salarycommission_pct