lecture 8 term2
Download
Skip this Video
Download Presentation
Lecture 8 Term2

Loading in 2 Seconds...

play fullscreen
1 / 56

Lecture 8 Term2 - PowerPoint PPT Presentation


  • 92 Views
  • 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.

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
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
    • DEFAULT
    • UNIQUE
    • PRIMARY KEY
    • FOREIGN KEY
    • 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));

default
Default

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
Unique

• 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),

secondnamechar(20),

course char(15),

startdatedate,

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,

areanamechar(8),

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

DISABLE

• 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=

table2.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;

slide18
More

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);

slide21
ALL

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);

having
Having

• 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.
slide24

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.
slide28
DBMS
  • 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

DBMS

Data

Manipulation

Language

Data Definition

Language

Data Dictionary

Reports and

Utilities

Language to

process and

update data

Language to

create and

modify data

An electronic

document

that provides

detailed

information

about each

and every

piece of data

in the

database

Software that

generates reports

and makes the

database user-

friendly

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
continued
Continued…
  • 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
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.
security
Security
  • 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.

Example

    • 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.
utilities
Utilities
  • 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
continued1
Continued..
  • 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

Administrator

Database

technology

And

management

Database

Management

System

Data planning

and modelling

technology

Users

nosql
NOSQL
  • 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
postgresql
PostgreSQL
  • 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
ad