Lecture 8 Term2

1. Lecture 8 Term2 24/2/14

2. 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.

3. Constraints continued.. • The following are the different constraints. • NOT NULL • DEFAULT • UNIQUE • PRIMARY KEY • FOREIGN KEY • CHECK

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

5. 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

6. 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.

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

8. 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.

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

10. Foreign Key • A foreign key is used to link the information in two tables

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

12. 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.

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

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

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

16. 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.

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

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

19. More Join Examples • http://www.techonthenet.com/sql/joins.php

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

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

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

23. 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.

24. Examples of a Database: • Student Records at UCC • Credit Card details • Directory Enquiries • Insurance Broker • Library System

26. 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

27. Database Management System (DBMS) • A group of programs that helps to create, process, store, retrieve, control, maintain, and manage data.

28. 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

29. 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

30. 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

31. 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

32. 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

33. 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

34. 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

35. 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

36. 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

37. 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.

38. Database Transaction Management • Isolation Levels • Local Transactions • Distributed Transactions

39. 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.

40. 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.

41. 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.

42. 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

43. 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

44. 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.

45. Security • Restriction of access to authorised users only. • Password • Encryption • Views • Authorisation Levels • Read only • Edit • Delete • create

46. 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.

47. 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.

48. 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.

49. 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)

50. 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