Transcript Review

Oracle Programming
Week 1
Review on the Relational Data
Model
Basic
Definitions
Relational Database Concepts
Relational Database Management Systems
Chapter 5 Elmasri & Navathe book
1
Basic Definitions

Data: Known facts that can be recorded and have an implicit
meaning.


Database : A collection of related data
Database management system (DBMS):
a collection of computer programs, which enables users to create
and maintain databases
User2
User1
Database
User3
User4
2
Basic Definitions (Cont.)
Schema: description of data at some level
(e.g., tables, attributes, constraints, domains)
 Model: tools and language for describing:


Conceptual schema
 Data


Integrity constraints, domains (DDL)
Operations on data
 Data

definition language (DDL)
manipulation language (DML)
Directives that influence the physical schema
(affects performance, not semantics)
 Storage
definition language (SDL)
3
Relation

Relation is a set of tuples/records
 Tuple
ordering irrelevant
 Cardinality of relation = number of tuples

All tuples in a relation have the same
structure; constructed from the same
set of attributes
 Attributes
named (=> ordering irrelevant)
 Value of an attribute drawn from the
attribute’s domain
4
General format of a relation when
represented as a table
Attribute or
Column Name
A1 A2 A3 … An
Attribute or
Column Name
a11 a12 a13 … a1n
Row or
tuple values
an1 an2 an3 … ann
5
Example
6
Relation as A Table (cont.)

Mathematical entity corresponding to a
table
~ tuple
 column ~ attribute
 row

Values in a tuple are related to each other
 John
lives at 123 Main
7
Relational Database Management
System (RDBMS)
Finite set of relations
 Database schema = set of relations (and
other things)

8
Database Schema (Example)





Student (Id: INT, Name: STRING, Address:
STRING, Status: STRING)
Professor (Id: INT, Name: STRING, DeptId:
DEPTS)
Course (DeptId: DEPTS, CrsName: STRING,
CrsCode: COURSES)
Transcript (CrsCode: COURSES, StudId: INT,
Grade: GRADES, Semester: SEMESTERS)
Department(DeptId: DEPTS, Name: STRING)
9
Three-schema Architecture

Internal Level  Internal Schema


Conceptual Level  Conceptual Schema



Describes the physical storage structure of the
database
Describes the structure of the whole database for all
users. It hides the storage details.
Concerned of entities (tables), data types,
relationships, user operations and constraints.
External or View level  External Schema

Describes part of the database for a group of users.
10
Three-schema Architecture – Cont.
External View
External View
DCL
Conceptual Schema
Internal Schema
DDL,
DML
DSL
11
Stored Database
Data Definition Language (DDL)
Used by DBAs or designers to define schema
 A compiler compiles this language to construct
the database and store its constraints in DBMS
catalog
 Examples of DDL Commands:

CREATE
 ALTER
 DROP
 TRUNCATE (Deletes all records in a table)

12
Data Manipulation Language (DML)

Some of its commands:
 SELECT
(Retrieves data)
 INSERT
 UPDATE
 DELETE
13
Data Control Language (DCL)
Used to create roles, permissions, and
referential integrity and to control access
on a database.
 Example of its commands:

 GRANT
(Grants user privileges)
 REVOKE (Withdraws privileges)
14
Other languages

Data Storage Language (DSL)


Specifies Internal Schema
Transactional Control Language (TCL)

Used to manage different transactions occurring
within a database.



COMMIT (Saves your work)
ROLLBACK (Restore database to original state since the last
COMMIT)
SAVE TRANSACTION (Sets a savepoint within a transaction)
15
Integrity Constraints
Part of schema
 Restriction on state (or sequence of states) of
database
 Enforced by DBMS
 Intra-relational - involve only one relation




Part of relation schema
e.g., all Ids are unique
Inter-relational - involve several relations

Part of relation schema or database schema
16
Database Integrity
 Implies
that the data held in the tables of the
database is consistent in terms of the
Relational Data Model
 Two Types
 Entity
integrity (PK)
 Referential Integrity (FK)
17
Relation Keys Constraints

Key Constraint: Values in a column (or
columns) of a relation are unique: at most one
row in a relation instance can contain a
particular value(s)

What is a Key?
A
minimal set of attributes satisfying key
constraint
18
Key Constraint (con’t)


Every relation has a key
Candidate Key


No two tuples of the relation will have identical entries in all
attributes of the key.
The number of attributes that comprises the key must be
minimal.
Primary Key- Since a table may have more than one candidate
key, one should be designated as the primary key (PK) of the
relation.
Examples:


primary key (Id in Student) –
candidate key ((Name, Address) in Student)
19
Candidate Keys &
Primary Keys

A RDBMS allows only one primary key per table.


Once a PK has been selected, any remaining candidate keys are
called alternate keys.
A primary key may be composed of

a single attribute (single primary key)


More than one attribute (composite primary key)



E.g. ID
E.g. Code + Serial
An attribute that is a primary key can not have a null
value
An attribute that is part of any key is called a prime
attribute.
20
Example 1
Employee
Table
EmpId
Name
Salary
NIN
Dno
100
Hasan H.
1500
1250011
100
101
Johns S.
2000
2360031
101
102
Kelly A.
900
1299997
101
103
Ibrams S.
1110
2359994
101
104
Kelly A.
2000
5502300
100
Department
Table
Dno
Dname
Loc
Manager
100
Finance.
1500
1250011
101
IT
2000
2360031
21
Example 2
Employee -Project
Table
EmpId
ProjId
Number
Of Hours
100
p22
15
101
p20
9
102
p22
11
100
p23
24
104
p23
15
104
p22
11
22
Candidate Keys &
Primary Keys (Cont.)

Primary keys are




defined using Data Definition Language (DDL)
Automatically enforced by the RDBMS
Generally are defined at the time the tables are
created.
When selecting primary keys, we need to choose
attributes that satisfy the uniqueness and
minimalist conditions for all permissible data.
23
NULL

What is NULL?
a
NULL value is used to represent missing
information, unknown, or inapplicable data.
 A NULL value is not a zero value
 A NULL value doesn’t represent a particular
value within the computer.
24
Foreign Key Constraint

Referential integrity => Item named for primary key
attribute/s in one relation must correspond to tuple(s) in
another that describes the item



Employee (ProjId) references Project(ProjId)
Professor(DeptId) references Department(DeptId)
a1 is a foreign key of R1 referring to a2 in R2 => if v is
a value of a1, there is a unique tuple of R2 in which a2
has value v




This is a special case of referential integrity: a2 must be a
candidate key of R2 (DeptId is a key of Department)
If no row exists in R2 => violation of referential integrity
Not all rows of R2 need to be referenced: relationship is not
symmetric
Value of a foreign key might not be specified (DeptId column of
25
some professor might be null)
Foreign Key Constraint (Example)
a1
v1
v2
v3
v4
-v3
R1
Foreign key
a2
v3
v5
v1
v6
v2
v7
v4
R2
Candidate/Primary key
26
5.7
27
Foreign Key (con’t)

Names of a1 and a2 need not be the same.
 With SQL tables:
Foreign key <name> professor (DeptId) references
Department (Dno)
DeptId attribute of Professor Table references to Dno attribute in
Department Table


R1 and R2 need not be distinct.
The attributes of where the referential integrity
exists must have the same data type and length.
28
Foreign Key (con’t)

Foreign key might consist of several columns


(CrsCode, Semester) of Transcript references (CrsCode,
Sem) of Teaching
R1(a1, …an) references R2(b1, …bn)




There exists a 1 - 1 relationship between a1,…an and
b1,…bn
ai and bi have same domains (although not necessarily
the same names)
For every tuple T in R1 over ai’s there exists a unique
tuple S in R2 over bi’s, with T = S
b1,…bn is a candidate key of R2
29
Semantic Constraints
Domain, primary key, and foreign key are
examples of structural (syntactic)
constraints
 Semantic constraints express rules of
application:

number of registered students 
maximum enrollment
 SQL calls them Check constraint
 e.g.,
30
SQL
Language for describing database schema
and operations on tables
 Data Definition Language (DDL):
sublanguage of SQL for describing schema
and constraints

31
Tables
SQL entity that corresponds to a relation
 An element of the database schema
 SQL is current standard
 Database vendors generally deviate from
standard

32
Table Creation
Steps in table creation:
1.
Identify data types for
attributes
2.
Identify columns that can
and cannot be null
3.
Identify columns that must
be unique (candidate keys)
4.
Identify primary keyforeign key mates
5.
Determine default values
6.
Identify constraints on
columns (domain
specifications)
7.
Create the table and
associated indexes
33
Common SQL Data Types (from Oracle)

String types




Numeric types





CHAR(n) – fixed-length character data, n characters long
Maximum length = 2000 bytes
VARCHAR2(n) – variable length character data, maximum
4000 bytes
LONG – variable-length character data, up to 4GB. Maximum
1 per table
NUMBER(p,q) – general purpose numeric data type
Numeric (p, q)- general purpose numeric data type
INTEGER(p) – signed integer, p digits wide
FLOAT(p) – floating point in scientific notation with p binary
digits precision
Date/time type

DATE – fixed-length date/time in dd-mm-yy form
34
Table Declaration in SQL
CREATE TABLE Student (
Id, numaric(5),
Name CHAR(20),
AddressVARCHAR(50),
Status: Boolean,
Constraint Pk_Id primary key(Id));
Id
101222333
234567890
Name
Address
Status
John 10 Cedar St Freshman
Mary 22 Main St Sophomore
Student
35
Primary, Unique, Check, Foreign, Null Keys
Create table department (
Dno
numeric(2),
Dname
varchar2(20),
Loc
varchar2(30) not null,
Constraint Pk_Dno primary key(Dno)
Constraint uk_name unique key(Dname));
Create table employee(
Eno
numeric(4),
Ename
varchar2(20),
Salary
numeric(7, 2) not null,
Hire_Date
Date not null,
Extention
char(4),
DeptNo
numeric(2),
Constraint Pk_Eno primary key(Eno),
Constraint Fk_Dno foreign key(DeptNo) reference
department (Dno),
36
Constraint ch_salary check (salary between 900 and 5000));
System Catalog
CREATE TABLE inserts information into the
catalog
 Catalog is another table that describes Objects
created such as:

Table names
 Constraint names
 Role Names
 Triggers, Sequences, Views, etc
 Attribute names of different tables
 Corresponding attribute types, etc.

Catalog schema is generally fixed by vendor
 In Oracle SQL this catalog is called DICTIONARY

37
Circularity in Foreign Key Constraint
a1
A
y
a2
a3
b1
x
x
candidate key : a1
foreign key : a3 references B(b1)
b2
b3
y
B
candidate key : b1
foreign key : b3 references A(a1)
Problem 1: Creation of A requires existence of B and vice versa
Solution 1:
CREATE TABLE A ( ……), (* no foreign key *)
CREATE TABLE B ( ……), (* foreign key included *)
ALTER TABLE A
ADD CONSTRAINT cons
FOREIGN KEY (a3) REFERENCES B (b1)
38
Circularity in Foreign Key Constraint


Problem 2 : Insertion of row in A requires prior
existence of row in B and vice versa
Solution 2 : DEFERRED constraint check - insert both
rows within a single transaction
CREATE TABLE Dept (…..
DeptId: CHAR (4),
MngrId : INTEGER,
PRIMARY KEY DeptId,
FOREIGN KEY (MngrId) REFERENCES Employee (Id) )
CREATE TABLE Employee ( ….
DeptId : CHAR (4),
Id : INTEGER,
PRIMARY KEY Id,
FOREIGN KEY (DeptId) REFERENCES Dept ( DeptId) )
39
Handling Foreign Key Violations/
Anomalies
Insert Anomaly:
 Insertion into A: Reject if no row exists in Table
B containing a primary key of the inserted row
 Insertion into B: No problem
A
x
x
B
40
Handling Foreign Key Violations
Deletion from B: Reject if the to be removed
is linked to other rows in A
 Deletion from A: No problem
Solution




A
SET NULL: Set value of foreign key in referencing
row(s) in A to null
SET DEFAULT: Set value of foreign key in referencing
row(s) in A to default value (y)
CASCADE: Delete referencing row(s) in A as well
B
null
A
y
y
41
B
Handling Foreign Key Violations




Update a candidate/primary key in B: Reject if row exists
in A containing for the old value
Update a foreign key in A (to z): Reject if no row exists in
B containing the new value
Update a foreign key in A (to z): NO problem if a row in
B exist, which contain the new updated value
Solution :
 SET NULL: Set value of foreign key to null
 SET DEFAULT: Set value of foreign key to default
 CASCADE: Propagate z to foreign key
42
Specifying Actions
CREATE TABLE Teaching (
ProfId INTEGER,
CrsCode CHAR (6),
Semester CHAR (6),
PRIMARY KEY (CrsCode, Semester),
FOREIGN KEY (ProfId) REFERENCES Professor (Id)
ON DELETE CASCADE,
FOREIGN KEY (CrsCode) REFERENCES Course
(CrsCode)
ON DELETE SET NULL
ON UPDATE CASCADE )
43