Lecture 8 term2
1 / 56

Lecture 8 Term2 - PowerPoint PPT Presentation

  • Uploaded on

Lecture 8 Term2. 24/2/14. Creating Constraints. Constraints are basically rules to stop a user doing something with a table that you don’t want to happen. • Constraints are used by Oracle to enforce rules whenever a row is added, changed, or removed.

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
Download Presentation

PowerPoint Slideshow about 'Lecture 8 Term2' - burt

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

Creating constraints
Creating Constraints

Constraints are basically rules to stop a user doing something with a table that you don’t want to happen.

• Constraints are used by Oracle to enforce rules whenever a row is added, changed, or removed.

• There are two types of constraints : Table constraints; and Column Constraints.

Constraints continued
Constraints continued..

  • The following are the different constraints.

    • NOT NULL


    • UNIQUE



    • CHECK

Not null
Not Null

Columns, by default, allow NULL values. By specify NOT NULL we are saying that NULLs are not allowing in a particular column.

Create table addresses ( name varchar2(30) not null, address varchar2(50) not null, phone number(10) );

• Note that the constraint can also be given a name.

Create table phones (name varchar2(30) constraint namenull not null, phone number(9));


If a value isn’t supplied for a column, then a default can be supplied. If we don’t use the default constraint, a NULL value would be entered.

Create table addresses (name varchar2(30) not null, address varchar2(50) default 'None Given', phone number(10) );

• In this example, if no value is specified for address, “None Given” is inserted in the column.

• The default constraint cannot be given a name


• Unique constraints ensure that the contents of a column (or columns) are unique. There can be no duplicate entries in a column. An index is automatically created to handle the rule. We will discuss indexes in detail later.

• Unique constraints can be applied at the column level or at the table level. At the column level we simply add the constraint after the column definition. For table level, we are putting a constraint on two or more columns, so we add the constraint after all the column definitions.

Unique cont
Unique (cont)

• The following is an example of a column level unique constraint.

create table phone

(name char(20) constraint onename unique, phonenumnumber(10));

• Nulls are allowed in a column with a unique constraint.

• The following is an example of a table level constraint

create table phone

(firstname char(10),


course char(15),


constraint onename unique(firstname,secondname));

Primary key
Primary Key

There can only be one primary key per table and it is used to ensure that each row is distinctive. Each row is identified uniquely by its primary key.

• A primary key, like a unique constraint, can be made up of one or more columns. The difference is that NULLs are allowed in single column unique constraints, there can be no nulls in a primary key.

Primary key cont
Primary Key (cont)

To create a column level constraint, we run (for example)

create table phone

(name char(20) constraint keyone primary key,

address char(50),

phone number(10));

• An example of a table level constraint is

create table phone

(name char(20),

address char(50), phone number(10),

constraint keyone primary key(name));

Foreign key
Foreign Key

• A foreign key is used to link the information in two tables

Foreign key cont
Foreign Key (cont)

The command to create the phone table is

create table phone

(name char(8) primary key,


phone number(8),

constraint fkeyarea foreign key (areaname) references area(areaname));

• This is a table level constraint, the equivalent command for a column level constraint is

create table phone

(name char(8) primary key,

areaname char(8) constraint forkey references area(areaname),phone number(8));

Composite foreign keys
Composite Foreign Keys

• The idea of composite foreign keys is simply referencing more than one column.

• To create composite foreign key constraints (which can only be table level) for the example above, we run

Create table( …

, constraint fkeyarea foreign key (division,section) references departments(division,section)


We can combine up to 16 columns in a composite foreign key constraint.

Referential integrity
Referential Integrity

  • This means that the foreign key ensure that references between tables (links) can not “get lost”

Referential integrity cont
Referential Integrity (cont.)

The original constraint we used in creating the phone table was

Create table( …

, constraint fkeyarea foreign key (areaname) references area(areaname)


To allow “cascading deletions” (deleting in one table cascades into tables referencing it) we change this command to

Create table( …

, constraint fkeyarea foreign key (areaname) references area(areaname)

on delete cascade


Check constraints
Check Constraints

• Check constraints offer the most flexibility. Rather than Oracle defining how a constraint works, we can impose our own rules.

For example

create table money

(name char(10),

sal number(7,2) constraint mycheckcon check (sal > 4.8)


And finally
And finally


• When creating a condition, we can disable it.

You can also use the SQL CREATE TABLE AS statement

  • to create a table from an existing table by copying the existing table's columns.

  • It is important to note that when creating a table in this way, the new table will be populated with the records from the existing table

  • The syntax for the SQL CREATE TABLE AS statement copying all of the columns is:

  • CREATE TABLE new_table AS (SELECT * FROM old_table);

    Create table testing as

    select * from emp where sal > 2000;

    • This will create a table with all the columns from emp with the rows where salary is greater than2000.

Joins syntax
Joins Syntax

Inner Join

SELECT column_name(s)FROM table1JOIN table2ON table1.column_name=


The LEFT JOIN keyword returns all rows from the left table (table1), with the matching rows in the right table (table2). The result is NULL in the right side when there is no match.

SELECT column_name(s)FROM table1LEFT OUTER JOIN table2ON table1.column_name=table2.column_name;

Lecture 8 term2

The RIGHT JOIN keyword returns all rows from the right table (table2), with the matching rows in the left table (table1). The result is NULL in the left side when there is no match.

SELECT column_name(s)FROM table1RIGHT OUTER JOIN table2ON table1.column_name=table2.column_name;

The FULL OUTER JOIN keyword combines the result of both LEFT and RIGHT joins

SELECT column_name(s)FROM table1FULL OUTER JOIN table2ON table1.column_name=table2.column_name;

More join examples
More Join Examples

  • http://www.techonthenet.com/sql/joins.php

Some all and any
Some, All, and Any

• For sub-queries that return more than one row, there are further options to the where or having clauses.

• These are the SOME, ANY, and ALL operators. (

SOME and ANY are actually the same)

• These can be best seen with the following example

select emp_name,sal, dept_no

from employee

where sal > SOME

(select sal

from employee

where dept_no=1);

Lecture 8 term2

The ALL operator compares against all

values in the sub-query.

• Changing the code above would give us

select emp_name,sal, dept_no

from employee

where sal > ALL

(select sal

from employee

where dept_no=1);


• We have seen sub-queries using the where

clause, we can also use the having clause in

exactly the same way.

select job, avg(sal)

from employee

having avg(sal) >

(select avg(sal)

from employee

where job='CLERK')

group by job;

Reminder databases
Reminder - Databases

  • A database is a central data store where data is recorded and maintained in a standard way. The data is organised and cross-referenced, enabling any individual data items to be accessed.

  • The concept of a database was developed without reference to computers.

Lecture 8 term2

  • Examples of a Database:

  • Student Records at UCC

  • Credit Card details

  • Directory Enquiries

  • Insurance Broker

  • Library System

Advantages of a database
Advantages of a Database:

  • Data Integrity is easier to maintain as all data is held in on central location

  • A database allows for ad-hoc queries and caters to complex questions involving the interaction and relationships between the various data items in the database to be investigated

  • Security

  • Minimisation of data duplication

  • Control of data redundancy

  • Data Consistency

  • Increased Concurrency

  • Improved Maintenance

Database management system dbms
Database Management System (DBMS)

  • A group of programs that helps to create, process, store, retrieve, control, maintain, and manage data.

Lecture 8 term2

  • A program that makes it possible to :

    • Create

    • Use

    • Maintain

      • A Database

  • Provides logical access to the data stored in the DB

  • Users/programmers do not have to worry about the physical aspect of the DB

  • Examples of dbms
    Examples of DBMS:

    • Desktop DB:

      • MS Access, FoxPro, FileMaker Pro, Paradox and Lotus Approach

    • Enterprise DB:

      • Oracle, SQL Server, MySQL, DB2

    • http://databases.about.com/od/administration/a/choosing_a_db.htm

    The database market
    The Database Market

    • Companies such as Informix, Microsoft, Oracle, IBM and Sybase

      • IBM dominates relational database mainframe market

      • Oracle leads Unix relational databases

      • Microsoft for PC database suppliers

    Data views
    Data Views

    • Logical view of data

      • A view that shows the logical relationship(s) between different pieces of data in a database

    • Physical view of data

      • A view that shows how and where data are physically stored in a storage medium

    The four main dbms components
    The Four Main DBMS Components





    Data Definition


    Data Dictionary

    Reports and


    Language to

    process and

    update data

    Language to

    create and

    modify data

    An electronic


    that provides



    about each

    and every

    piece of data

    in the


    Software that

    generates reports

    and makes the

    database user-


    Data languages
    Data Languages

    • Data definition language

      • A DBMS language used to create and modify the data.

    • Data manipulation language (DML)

      • A language that processes and updates data.

    • Structured query language (SQL)

      • A language that deals exclusively with data, namely, data integrity, data manipulation, data access, data retrieval, data query, and data security

    Functions of a database management system
    Functions of a Database Management System

    • Data storage retrieval and update facilities

    • A user-accessible catalogue or data dictionary

    • Support for shared update

    • Backup and recovery services

    • Security services

    • Integrity

    • Services to promote data independence

    • Utilities

    Retrieval and update facilities
    Retrieval and Update Facilities

    • Logical transaction = many separate physical transactions (reading, updating, writing records)

    • If transactions are interrupted before entire completion “up to date” data is sacrificed for consistent data.

    • If not, transaction is committed – written to disk.

    • DBMS provides mechanisms that either Commit or Rollback transactions

    Database transaction management
    Database Transaction Management

    • A transaction is one or more SQL statements that make up a unit of work performed against the database, and either all the statements in a transaction are committed as a unit or all the statements are rolled back as a unit.

    • This unit of work typically satisfies a user request and ensures data integrity.

      • For example, when you use a computer to transfer money from one bank account to another, the request involves a transaction: updating values stored in the database for both accounts.

    • For a transaction to be completed and database changes to be made permanent, a transaction must be completed in its entirety

    Database transaction management1
    Database Transaction Management

    • Managing Commits in Transactions

      • Committing (and rolling back) transactions is slow because of the disk I/O and potentially the number of network round trips required.

    Database transaction management2
    Database Transaction Management

    • Isolation Levels

    • Local Transactions

    • Distributed Transactions


    • For example a payroll payment system may need to update these tables when an employee is paid:

      • Employee record Table - holds a record for each employee.

      • Accounts Table - holds payment details by department

      • Auditing Table - holds all payment details for auditing purposes

    • The idea is when the employee is paid that his record is updated with the paid amounts, a record is added to the accounts table and one to the auditing table.

    • That way the company can account for the salary payment. It all adds up and is consistent.

    • The problem arises if something goes wrong while writing to these three tables. Maybe a hardware failure or a program bug, say a duplicate data row added or a crash due to hardware. If you work with databases, you will experience a failure at some time or other. You are most likely to get problems during development if a SQL query goes wrong. Say it takes too long to run and you have to kill the process.

    Acid properties
    ACID Properties

    • The ACID model is one of the oldest and most important concepts of database theory.

    • It sets forward four goals that every database management system must strive to achieve: atomicity, consistency, isolation and durability.

    Acid properties1
    ACID Properties

    • Atomicity states that database modifications must follow an “all or nothing” rule. Each transaction is said to be “atomic.”

    • Consistency states that only valid data will be written to the database.

    • Isolation requires that multiple transactions occurring at the same time not impact each other’s execution.

    • Durability ensures that any transaction committed to the database will not be lost.

    Shared update
    Shared Update

    • Two users making updates at the same time.

      • Single vs. Multiuser environment.

    • Solutions

    • Avoidance:

      • Prohibit shared update

      • Allow access retrieval only

      • Record updates in transaction file and update database periodically using a batch program

    Shared update1
    Shared Update

    2. Locking

    • Lock table/record/field from accces by other users.

  • Types of Lock

    • Exclusive

    • Read only

    • Lock Time-out

  • Other variables

    • Lock granularity

    • Deadlock

  • Recovery

    • Back-ups or saves(normal backup of db files)

    • Journaling/Audit trail/Audit file

      • keep a log or journal of the activity which updates the database

      • Recovery involves: copying the backup over database and running a special program to update the backup version of the database with the transaction log.


    • Restriction of access to authorised users only.

    • Password

    • Encryption

    • Views

    • Authorisation Levels

      • Read only

      • Edit

      • Delete

      • create

    Data integrity
    Data Integrity

    • DBMS provides a mechanism to enforce specific rules.


      • Customer numbers must be numeric.

    • Programmers must also develop their own

    • Example

      • Credit limits must be 300, 500 or 1000 only.

      • The sales rep for a given customer must exist.

      • No customer may be deleted if he/she currently has an order on file.

    Data independence
    Data Independence

    • Organisations are rarely single site /single entity.

    • Flows of data transcend the boundaries of organisations – so do information systems.

    • Data communciation must be implemented.

    • Databases can be used to support the distribution of information resources.

    Integration of applications
    Integration of Applications

    • Organisational data sources are varied

    • All applications must be integrated to save time (i.e data exchange)

    • Databases may be used to enable theis integration.

    • Portability/compatibility is paramount.


    • Compact data files

    • Index/re-index data files

    • Repair database (crash)

    • Import/export data from and to other sources.

    • Enforce standards (e.g. integrity relationships)

    • Associated data dictionary

    • Access to remote computers (login, emulation)

    Some drawbacks
    Some Drawbacks…

    • Complexity

      • A DBMS is a complex piece of software all users must fully understand it to make use of its functionalities

    • Cost of DBMS

      • The cost varies significantly depending on the environment and the functionality provided. Must take into consideration recurrent annual maintenance costs


    • Cost of Conversion

      • Cost of converting existing applications to run on the new DBMS and hardware. (additional training costs)

    • Performance

      • DBMS is written for applications in general which means that some applications may run slower than before

    • Higher Impact of Failure

      • Centralization of resources increases vulnerability of the system

    Database administrator
    Database Administrator

    • Oversee a staff of database specialists

    • Final recommendations for DB design

    • Load and maintain DB

    • Establish security controls

    • Perform backup and recovery

    Data administration
    Data Administration

    • The role of managing the organisation’s data resources, concerned with information policy, data planning, maintenanceof data dictionaries, and data quality standards.

    • http://www.youtube.com/watch?v=Jos6dRLzi80

    Data administration1
    Data Administration










    Data planning

    and modelling




    • NoSQL encompasses a wide variety of different database technologies and were developed in response to a rise in the volume of data stored about users, objects and products, the frequency in which this data is accessed, and performance and processing needs.

    • Relational databases, on the other hand, were not designed to cope with the scale and agility challenges that face modern applications, nor were they built to take advantage of the cheap storage and processing power available

    • Document databases

    • Key-value stores

    • Graph stores

    • Wide-column stores

    • http://www.mongodb.com/learn/nosql


    • Open source object-relational database system

    • It has more than 15 years of active development and a proven architecture that has earned it a strong reputation for reliability, data integrity, and correctness

    • Runs on all major operating systems

    • http://db-engines.com/en/system/Oracle%3BPostgreSQL