Friday, April 18, 2008
Wednesday, October 10, 2007
DATA BASE MANAGEMENT SYSTEM
INTRODUCTION
Database
The related information when placed is an organized form makes a database. The organization of data/information is necessary because unorganized information has no meaning.
In dictionary, the words are arranged in alphabetic order along with their meanings.
Database and Computers
Computer has a large storage capacity. It can store thousands of records at a time.
It has high speed, within no time it searches any desired information, arrange the data in alphabetical order, do calculations on the data and make repetitions and so on.
Computer is more accurate.
Data in computers can be stored in the form of a file, records and fields.
There are two approaches for storing data in computers such as File based approach and Database approach.
File Based Approach
File Based system: File-based systems were an early attempt to computerize the manual filing system that we are all familiar with.
A file system is a method for storing and organizing computer files and the data they contain to make it easy to find and access them.
File systems may use a storage device such as a hard disk or CD-ROM and involve maintaining the physical location of the files.
Limitations of the File-Based Approach
There are following problems associated with the File Based Approach:
• Separated and Isolated Data
• Duplication of data
• Data Dependence
• Difficulty in representing data from the user’s view
• Data Inflexibility
• Incompatible file formats
Database Approach
In order to remove all the above limitations of the File Based Approach, a new approach was required that must be more effective known as Database approach.
A database is a computer based record keeping system whose over all purpose is to record and maintain information. The database is a single, large repository of data, which can be used simultaneously by many departments and users.
SQL * PLUS
In order to interactively use the SQL commands the Oracle Corporation has provided us the software called SQL *PLUS. This particular software allows us to access oracle databases through written procedure and commands. It allows us to edit, retrieve, perform calculations, print query results, run SQL commands etc. It helps us to copy data between SQL databases and send messages to accept responses from an end user.
Database
The related information when placed is an organized form makes a database. The organization of data/information is necessary because unorganized information has no meaning.
In dictionary, the words are arranged in alphabetic order along with their meanings.
Database and Computers
Computer has a large storage capacity. It can store thousands of records at a time.
It has high speed, within no time it searches any desired information, arrange the data in alphabetical order, do calculations on the data and make repetitions and so on.
Computer is more accurate.
Data in computers can be stored in the form of a file, records and fields.
There are two approaches for storing data in computers such as File based approach and Database approach.
File Based Approach
File Based system: File-based systems were an early attempt to computerize the manual filing system that we are all familiar with.
A file system is a method for storing and organizing computer files and the data they contain to make it easy to find and access them.
File systems may use a storage device such as a hard disk or CD-ROM and involve maintaining the physical location of the files.
Limitations of the File-Based Approach
There are following problems associated with the File Based Approach:
• Separated and Isolated Data
• Duplication of data
• Data Dependence
• Difficulty in representing data from the user’s view
• Data Inflexibility
• Incompatible file formats
Database Approach
In order to remove all the above limitations of the File Based Approach, a new approach was required that must be more effective known as Database approach.
A database is a computer based record keeping system whose over all purpose is to record and maintain information. The database is a single, large repository of data, which can be used simultaneously by many departments and users.
SQL * PLUS
In order to interactively use the SQL commands the Oracle Corporation has provided us the software called SQL *PLUS. This particular software allows us to access oracle databases through written procedure and commands. It allows us to edit, retrieve, perform calculations, print query results, run SQL commands etc. It helps us to copy data between SQL databases and send messages to accept responses from an end user.
DUAL TABLE AND ORACLE FUNCTIONS
Introduction to Oracle Functions
Functions make the result of the query easier and are used to manipulate the data values. Functions can accept any number of constant values or variables. These variables or constant are called as arguments. SQL functions can perform different types of operations such as modify individual data items, convert column data types, format dates and numbers etc.
Categories of Functions
Oracle functions are categorized into two categories:
· Single Row/Scalar Functions
· Group/Aggregate Functions
Functions, which operate on single rows and return one value per row, are called as Scalar functions or Single Row Functions.
On the other hand functions, which operate on set of values to give one result, are called as Group Functions or Aggregate Functions.
Single-Row Functions (Scalar functions)
These functions act on each row of the table and return a single value for each row selected. These functions may accept one or more arguments and can return a value of different data type than that it has accepted.
Classification of Single Row Functions
Single Row Functions can be classified into the following categories:
(i) Character
(ii) Number
(iii) Date
(iv) Conversion
(v) General
Character Functions
Numeric functions
It accept the numeric input and return numeric values.
· round(x[,y])
It rounds off x to the decimal precision of y. If y is negative, rounds to the precision of y places to the left of the decimal point.
Date Functions
Aggregate Functions (Group Functions)
These functions are used to produce summarized results. They are applied on set of rows to give you single value as a result. A group function allows you to perform a data operation on several values in a column of data as though the column was one collective group of data. These functions are called group functions also, because they are often used in a special clause of select statements called a group by clause.
COUNT (x)
This function returns the number of rows or non-null values for column x. When we use * in place of x, it returns the total number of rows in the table.
Syntax:
count([distinctall]column name)
Example:
1. Count the number of employees in the emp table.
SUM(x)
This function returns the sum of values for the column x. This function is applied on columns having numeric datatype and it returns the numeric value.
syntax : sum([distinctall]column name)
Example:
List the total salary paid to the employees in the emp table
AVG(x)
This function returns the average of values for the column x. This function is applied on columns having numeric datatype and it returns the numeric value. It ignores the null values in the column x.
syntax : avg([distinctall]column name)
Example:
List the average salary and the number of employees in the emp table .
MIN(x)
This function returns the minimum of values for the column x for all the rows .this function can be applied on any datatype .
syntax : min([distinctall]column name)
Example:
List the minimum salary in the emp table .
MAX(x)
This function returns the maximum of values for the column x for all the rows .this function can be applied on any datatype.
syntax : max([distinctall]column name)
Example:
List the maximum salary and commission in the emp table .
OPERATORS
Logical operators
There are three logical operators:
· AND
· OR
· NOT
AND Operator==List the details of the employees who do the job of salesman and belong to the department 10.
SQL> select * from emp where job = ‘MANAGER’ and deptno = 20;
OR Operator==List the names of the employees who do the job clerk or salesman.
SQL> select ename, job from emp where job = ‘CLERK’ or job = “SALESMAN’.
NOT Operator==List employee number of the employees whose don’t have the name of ‘FORD’, ‘JAMES ‘or ‘JONES’;
SQL> select empno from emp where ename not in (‘FORD’,’JAMES’,’JONES’);
Set Operators
Set operators are used to combine information of similar type from one of more than one table. While performing the set operations it should be remembered that the corresponding columns must be the same .The set operators allow us to combine two or more queries into one result. The different type of SET operators in Oracle is:
· UNION
· INTERSECT
· MINUS
Union clause
The union clause merges the output of two or more queries in a single set of rows or columns .It eliminates the duplicate values between two or more queries. The syntax of Union operator is:
Select
UNION
Select
[order by clause ]
Here the statements 1 and 2 are valid select statements and the order by clause is optional. Both the queries are executed independently, but the output of both the queries is merged.
Example:Display the different jobs in departments 20 and 30.
SQL>Select job from emp Where deptno =20
Union
Select job from emp Where deptno =30;
Intersect operator
Another operator, which can be used to get the combined output of multiple queries, is the Intersect operator. It gives only those rows as output from both the queries, which have common records among them.
The syntax of the intersect operator is:
SELECT
INTERSECT
SELECT
[ORDER by clause ];
Example:
· List the Jobs common to department 20 and 30.
SQL> select job from emp where deptno =20
INTERSECT
select job from emp where deptno =30;
Minus operator
The Minus operator returns the rows, which are unique to the first query. It must be remembered that in case of minus A-B is different from B-A. That is why the minus operator does not follow the property of commutative unlike UNION and INTERSECT operators.
The syntax is:
SELECT
MINUS
SELECT
[ORDER BY clause];
Example :List the jobs, which are common to department 20 only,
SQL> select job from emp where deptno =20
MINUS
select job from emp where deptno =30
MINUS
select job from emp where deptno =10;
Arithmetic operator
ADD
SQL>Select add(2,2) from dual;
SUBTRACT
SQL> Select sub(5,3) from dual;
MULTIPLY
SQL>Select mul(3,2) from dual;
DIVIDE
SQL>Select div(4,2) from dual;
MODULUS
SQL>Select mod(10,3) as first from dual;
OTHER TOPICS
Range searching
SQL> Select * from t1 where sal not between 300 and 500;
Distinct
Syntax:
SELECT DISTINCT column_name(s) FROM tablename;
If you need to find the unique departments and salaries from EMP table then, issue the following query:
SQL> SELECT DISTINCT deptno, sal FROM emp;
Pattern matching
In order to select rows that match a particular character pattern we use the LIKE operator. This character matching operation is called as wildcard search. The following symbols are used for matching the pattern
% (percentage) This symbol represents any sequence of zero or more characters . __ (underscore) this symbol is used for any single character search. The % and _ symbols can be used in any combination with literal characters.
Pattern matching symbol
%(percentage):- represents any sequence of zero or more characters.
_ (underscore):- it is used for any single character search.
List th
e employee names and empno whose names start with J.
SQL> select ename, empno from emp where ename like ‘J%’;
List the employee names whose names are of six characters length.
SQL>select ename from emp where ename like ‘_ _ _ _ _ _ ‘;
. List the employee names whose names end with ‘H’.
SQL>select ename from emp where ename like ‘%H’;
Truncating tables
It works same as delete function.
Syntax:
Truncate table tablename;
SQL> Select * from t1 where sal not between 300 and 500;
Distinct
Syntax:
SELECT DISTINCT column_name(s) FROM tablename;
If you need to find the unique departments and salaries from EMP table then, issue the following query:
SQL> SELECT DISTINCT deptno, sal FROM emp;
Pattern matching
In order to select rows that match a particular character pattern we use the LIKE operator. This character matching operation is called as wildcard search. The following symbols are used for matching the pattern
% (percentage) This symbol represents any sequence of zero or more characters . __ (underscore) this symbol is used for any single character search. The % and _ symbols can be used in any combination with literal characters.
Pattern matching symbol
%(percentage):- represents any sequence of zero or more characters.
_ (underscore):- it is used for any single character search.
List th
e employee names and empno whose names start with J.
SQL> select ename, empno from emp where ename like ‘J%’;
List the employee names whose names are of six characters length.
SQL>select ename from emp where ename like ‘_ _ _ _ _ _ ‘;
. List the employee names whose names end with ‘H’.
SQL>select ename from emp where ename like ‘%H’;
Truncating tables
It works same as delete function.
Syntax:
Truncate table tablename;
PARTS TO BE COVERED IN DBMS
Oracle Data types
The information in a database is maintained in the form of tables and each table consists of rows and columns, which store data, and therefore this data must have some data type i.e the type of data, which is stored in a table.
The different data types available in Oracle are:
The most commonly used data types are:
· char
· varchar or varchar2
· number
· date
· long
· long raw/ raw
Let us now briefly describe these data types:
Char(n)
This data type is used to store character strings of fixed size. The size of the character string is determined by the numeric value of n. This data type can hold maximum of 255 characters. When Oracle stores data in a CHAR data type, it will pad the value stored in the column up to the length of the column as declared by the table with blanks.
For example: If data type of address field is mentioned as CHAR(40) and address information of a particular record complete in 20 characters, then remaining 20 characters space is padded with blank characters.
Varchar(n) / Varchar2(n)
This data type is used to store variable length alphanumeric data. It can store maximum of 2000 characters. In case of varchar data type, Oracle will not store padded blank spaces if the value stored in a column defined is less than length of the column as declared by the table with data type VARCHAR2.
For example: If data type of address field is mentioned as VARCHAR(40) and address information of a particular record complete in 20 characters, then remaining 20 characters space is not padded with blank characters and memory space of 20 characters is used for some other purposes and not wasted as padded with blank characters.
Number (p,s)
This data type is used to store numbers fixed or floating point .The precision (p) determines the length of the data while (s), the scale, determines the number of places after the decimal. The NUMBER data type that is used to store number data can be specified either to store integers or decimals with the addition of a parenthetical precision indicator.
For example, if you had a column defined to be data type NUMBER(10,3), the number 546.3832 would be stored as 546.383, because after the decimal point we can store 3 digits. It can store a number of 10 digits including a decimal point for example a maximum number of 999999.999 can be stored with data type of NUMBER(10,3).
Date
This data type, stores date values in a special format internal to Oracle It offers a great deal of flexibility to users who want to perform date manipulation operations There are also numerous functions that handle date operations more complex than simple arithmetic. Another nice feature of Oracle’s method for date storage is that it is inherently millennium compliant. The default format in which date is stored is DD-MON-YY. If we want to store date in other format then we have to use the appropriate functions.
Long
The developer can declare columns to be of LONG data type, which can stores upto 2 gigabytes of alphanumeric text data. The values of long data type cannot be indexed and normal characters functions such as SUBSTR cannot be applied to long values.
Long Raw / Raw
It is useful to store graphics and sound files when used in conjunction with LONG to form the LONG RAW data type, which can accommodate up to 2 gigabytes of data.
Note: A table cannot contain more than one Long column. They cannot be indexed and no integrity constraints can be applied on them (except for NULL and NOT NULL constrain).
Components of the DBMS Environment
• Hardware
• Software
• Data
• Users
• Procedures
Hardware
The hardware is the actual computer system used for keeping and accessing the database. Conventional DBMS hardware consists of secondary storage devices, usually hard disks, on which the database physically resides, together with the associated input-output devices, device controllers and so forth.
Software
The software is the actual DBMS. Between the physical database itself (i.e. the data as actually stored) and the users of the system is a layer of software, usually called the Database Management System or DBMS. All requests from users for access to the database are handled by the DBMS.
One general function provided by the DBMS is thus the shielding of database users from complex hardware-level detail.
Data
The database should contain all the data needed by the organization. One of the major features of databases is that the actual data are separated from the programs that use the data. A database should always be designed, built and populated for a particular audience and for a specific purpose.
Users
There are a number of users who can access or retrieve data on demand using the applications and interfaces provided by the DBMS. Each type of user needs different software capabilities. The users of a database system can be classified in the following groups, depending on their degrees of expertise or the mode of their interactions with the DBMS.
The users can be:
v Naive Users
v Online Users
v Application Programmers
v Sophisticated Users
v DBA
Procedures
Procedures refer to the instructions and rules that govern the design and use of the database. The users of the system and the staff that manage the database require documented procedures on how to use or run the system.
CONSTRAINTS
Role of Constraints to achieve data integrity
To understand the role of constraints in database let us consider a case, when we appear for some interview, there are certain constraints for our qualification. Person who satisfies the given qualification constraints, only those person are eligible for interview, so these restrictions are called constraints which are to be enforced to select the correct candidate. Such limitations have to be enforced on the data to achieve the integrity (Correctness). The data, which does not, satisfies the conditions will not be stored, this ensures that the data stored in the database is valid and has gone through the integrity rules which prohibit the wrong data to be entered into the database.
Categories of Constraints
There are two types of constraints, these are:
· Column constraints
· Table constraints
Column Constraints
When we define constraints along the definition of the column while creating or altering the table structure, they are called as column constraints. Column constraints are applied only to the individual columns. These constraints are applied to the current column .A column level constraint cannot be applied if the data constraint spans across multiple columns in a table.
For example: Empno of emp table is a primary key, which is column level constraint.
Table Constraints
Table constraints are applied to more than one column of a table. These constraints are applied after defining all the table columns when creating or altering the structure of the table. A table level constraint can be applied if the data constraint spans across multiple columns in a table.
For example: In case of bank database the account number field of account holder table is not sufficient to uniquely identify the entities because two person can have joint account and hence repeating the value of account number once for each account holder, so in this case we have to apply primary key constraint on combination of account number field and name field to achieve the uniqueness. Now in this case the primary key constraint is applied on more than one column of the table so this is the table level constraint.
Types of Constraints
NULL/NOT NULL
Specifies if the column must contain value or might not contain any. By default all columns in a table allow nulls, i.e. absence of a value in a column. NOT NULL specifies that all rows in the table to have value for specified column. All NOT NULL columns are mandatory fields. Row of data will not be accepted for the table unless columns so defined have data in them. NULL columns might not contain any data and can be left empty. NULL should not be however specified for numeric fields as any arithmetic with NULL results in a NULL value.
Syntax:
Columnname datatype (size) [constraint constraintname] NOT NULL
Unique constraint
The unique key allows unique values to be entered into the column i.e. every value is a distinct value in that column. When we apply a unique constraint on a group of columns then each value of the group should be unique, they must not be repeated. A table can have more than one unique key. This constraint can be applied both at the table level and the column level. The syntax is:
Column level syntax:
Columnname datatype (size) [constraint constraintname] UNIQUE
Primary Key Constraint
A primary key is used to identify each row of the table uniquely. A primary key may be either consists of a single field or group of fields. It is a combination of both the unique key constraint as well as the not null constraint i.e. no column with the primary key constraint can be left blank and it must contain unique value in each row. We can declare the Primary key of a table with NOT NULL and UNIQUE constraint .SQL supports primary keys directly with the Primary Key constraint. When primary key is applied on a single field it is called as Simple Key and when applied on multiple columns it is called as Composite Primary Key. In a table there is only one primary key. The syntax of the Primary Key:
Column level syntax:
Columnname datatype(size) [ constraint_name] PRIMARY KEY
Check constraint
Check constraints allow Oracle to verify the validity of data being entered on a table against a set of constants. These constants act as valid values. The Check constraint consists of the keyword CHECK followed by parenthesized conditions. Check constraints must be specified as a logical expression that evaluates either to TRUE or FALSE. If an SQL statement causes the condition to be false an error message will be displayed.
Syntax of the check constraint is:
Column level syntax:
Columnname datatype (size) [constraint constraintname] CHECK (logical expression)
Default constraint
The default value constraint allows the user to insert the values in the columns where the user do not want to insert the value .The most common example of this constraint is NULL value, which is inserted in columns not defined with the NOT NULL constraint. This is not actually a constraint, it only specifies that a value should be inserted in a column if the user does not enter a value .The default values assignments are defined at the time of create table command. The datatype of the default value should match the datatype of the column.
The syntax is:
Columnname datatype (size) DEFAULT value;
Foreign Key constraint
A foreign key is a kind of constraint, which establishes a relationship among tables. A foreign key may be a single column or the combinations of columns, which derive their values, based on the primary key or unique key values from another table. A foreign key constraint is also known as the referential integrity constraint, as its values correspond to the actual values of the primary key of other table.
The syntax of the foreign key at the column level is:
Columnname datatype (size) references tablename [(columnname)] [ON DELETE CASCADE]
KEYS AND ITS TYPES
Key -A database consists of tables, which consist of records, which further consist of fields.
Types of Keys
Every key which has the property of uniqueness can be distinguished as following:
• Candidate Key
• Super Key
• Primary Key
• Alternate key
• Artificial key
• Foreign key
Candidate key
Candidate keys are those attributes of a relation, which have the properties of uniqueness and irreducibility, whose explanation is given below:
Let R be a relation. By definition, the set of all attributes of R has the uniqueness property, meaning that, at any given time, no two tuples in the value of R at that time are duplicates of one another.As in the case of the STUDENT relation, for example, the subset containing just attribute Roll number has that property. These facts constitute the intuition behind the definition of candidate key.
Let K be a set of attributes of relation R. Then K is a candidate key for R if and only if it possesses both of the following properties:
• Uniqueness: No legal value of R ever contains two distinct tuples with the same value for K.
• Irreducibility: No proper subset of K has the uniqueness property.
Super Key
A super key has the uniqueness property but not necessarily the irreducibility property. A candidate key is a special case of a super key.
For example if Roll_number is unique in relation STUDENT then, the set of attributes
(Roll_number, Name, Class) is a super key for a relation STUDENT, these set of attributes are also unique, but this combination of keys (composite key) is not having the property of irreducibility because Roll_number which is one subset of the composite key is also unique itself. Thus, this composite key is called as super key because it has the property of uniqueness but not the irreducibilty.
Consider a relation of Patient in which Patient_number is unique. Then, Patient_number is a candidate key and (Patient_number, Patient name) is a super key.Thus we can say that “A superset of a candidate key is a super key.”
Primary Key
The primary key is an attribute or a set of attributes that uniquely identify a specific instance of an entity. Every entity in the data model must have a primary key whose values uniquely identify instances of the entity. Sometimes a record may contain more than one key field.
For example the doctor’s patients file may contain both a patient number and a National Insurance number for each patient. Both of these are key fields. We therefore choose one of them and call it the primary key field. Primary key cannot contain any Null value because we cannot uniquely identify multiple Null values.
Alternate Key
The alternate keys of any table are simply those candidate keys, which are not currently selected as the primary key.
Exactly one of those candidate keys is chosen as the primary key and the remainder, if any, are then called alternate keys. An alternate key is a function of all candidate keys minus the primary key.
Artificial Key
Foreign Keys
Foreign keys are the attributes of a table, which refers to the primary key of some another table. Foreign Keys permit only those values, which appears in the primary key of the table to which it refers or may be null. Foreign keys are used to link together two or more different tables which have some form of relationship with each other. The foreign key is a reference to the tuple of a table from which it was taken, this tuple being called the Referenced or Target tuple. The table containing the referenced tuple will be called as Target table.
Oracle Data types
The information in a database is maintained in the form of tables and each table consists of rows and columns, which store data, and therefore this data must have some data type i.e the type of data, which is stored in a table.
The different data types available in Oracle are:
The most commonly used data types are:
· char
· varchar or varchar2
· number
· date
· long
· long raw/ raw
Let us now briefly describe these data types:
Char(n)
This data type is used to store character strings of fixed size. The size of the character string is determined by the numeric value of n. This data type can hold maximum of 255 characters. When Oracle stores data in a CHAR data type, it will pad the value stored in the column up to the length of the column as declared by the table with blanks.
For example: If data type of address field is mentioned as CHAR(40) and address information of a particular record complete in 20 characters, then remaining 20 characters space is padded with blank characters.
Varchar(n) / Varchar2(n)
This data type is used to store variable length alphanumeric data. It can store maximum of 2000 characters. In case of varchar data type, Oracle will not store padded blank spaces if the value stored in a column defined is less than length of the column as declared by the table with data type VARCHAR2.
For example: If data type of address field is mentioned as VARCHAR(40) and address information of a particular record complete in 20 characters, then remaining 20 characters space is not padded with blank characters and memory space of 20 characters is used for some other purposes and not wasted as padded with blank characters.
Number (p,s)
This data type is used to store numbers fixed or floating point .The precision (p) determines the length of the data while (s), the scale, determines the number of places after the decimal. The NUMBER data type that is used to store number data can be specified either to store integers or decimals with the addition of a parenthetical precision indicator.
For example, if you had a column defined to be data type NUMBER(10,3), the number 546.3832 would be stored as 546.383, because after the decimal point we can store 3 digits. It can store a number of 10 digits including a decimal point for example a maximum number of 999999.999 can be stored with data type of NUMBER(10,3).
Date
This data type, stores date values in a special format internal to Oracle It offers a great deal of flexibility to users who want to perform date manipulation operations There are also numerous functions that handle date operations more complex than simple arithmetic. Another nice feature of Oracle’s method for date storage is that it is inherently millennium compliant. The default format in which date is stored is DD-MON-YY. If we want to store date in other format then we have to use the appropriate functions.
Long
The developer can declare columns to be of LONG data type, which can stores upto 2 gigabytes of alphanumeric text data. The values of long data type cannot be indexed and normal characters functions such as SUBSTR cannot be applied to long values.
Long Raw / Raw
It is useful to store graphics and sound files when used in conjunction with LONG to form the LONG RAW data type, which can accommodate up to 2 gigabytes of data.
Note: A table cannot contain more than one Long column. They cannot be indexed and no integrity constraints can be applied on them (except for NULL and NOT NULL constrain).
Components of the DBMS Environment
• Hardware
• Software
• Data
• Users
• Procedures
Hardware
The hardware is the actual computer system used for keeping and accessing the database. Conventional DBMS hardware consists of secondary storage devices, usually hard disks, on which the database physically resides, together with the associated input-output devices, device controllers and so forth.
Software
The software is the actual DBMS. Between the physical database itself (i.e. the data as actually stored) and the users of the system is a layer of software, usually called the Database Management System or DBMS. All requests from users for access to the database are handled by the DBMS.
One general function provided by the DBMS is thus the shielding of database users from complex hardware-level detail.
Data
The database should contain all the data needed by the organization. One of the major features of databases is that the actual data are separated from the programs that use the data. A database should always be designed, built and populated for a particular audience and for a specific purpose.
Users
There are a number of users who can access or retrieve data on demand using the applications and interfaces provided by the DBMS. Each type of user needs different software capabilities. The users of a database system can be classified in the following groups, depending on their degrees of expertise or the mode of their interactions with the DBMS.
The users can be:
v Naive Users
v Online Users
v Application Programmers
v Sophisticated Users
v DBA
Procedures
Procedures refer to the instructions and rules that govern the design and use of the database. The users of the system and the staff that manage the database require documented procedures on how to use or run the system.
CONSTRAINTS
Role of Constraints to achieve data integrity
To understand the role of constraints in database let us consider a case, when we appear for some interview, there are certain constraints for our qualification. Person who satisfies the given qualification constraints, only those person are eligible for interview, so these restrictions are called constraints which are to be enforced to select the correct candidate. Such limitations have to be enforced on the data to achieve the integrity (Correctness). The data, which does not, satisfies the conditions will not be stored, this ensures that the data stored in the database is valid and has gone through the integrity rules which prohibit the wrong data to be entered into the database.
Categories of Constraints
There are two types of constraints, these are:
· Column constraints
· Table constraints
Column Constraints
When we define constraints along the definition of the column while creating or altering the table structure, they are called as column constraints. Column constraints are applied only to the individual columns. These constraints are applied to the current column .A column level constraint cannot be applied if the data constraint spans across multiple columns in a table.
For example: Empno of emp table is a primary key, which is column level constraint.
Table Constraints
Table constraints are applied to more than one column of a table. These constraints are applied after defining all the table columns when creating or altering the structure of the table. A table level constraint can be applied if the data constraint spans across multiple columns in a table.
For example: In case of bank database the account number field of account holder table is not sufficient to uniquely identify the entities because two person can have joint account and hence repeating the value of account number once for each account holder, so in this case we have to apply primary key constraint on combination of account number field and name field to achieve the uniqueness. Now in this case the primary key constraint is applied on more than one column of the table so this is the table level constraint.
Types of Constraints
NULL/NOT NULL
Specifies if the column must contain value or might not contain any. By default all columns in a table allow nulls, i.e. absence of a value in a column. NOT NULL specifies that all rows in the table to have value for specified column. All NOT NULL columns are mandatory fields. Row of data will not be accepted for the table unless columns so defined have data in them. NULL columns might not contain any data and can be left empty. NULL should not be however specified for numeric fields as any arithmetic with NULL results in a NULL value.
Syntax:
Columnname datatype (size) [constraint constraintname] NOT NULL
Unique constraint
The unique key allows unique values to be entered into the column i.e. every value is a distinct value in that column. When we apply a unique constraint on a group of columns then each value of the group should be unique, they must not be repeated. A table can have more than one unique key. This constraint can be applied both at the table level and the column level. The syntax is:
Column level syntax:
Columnname datatype (size) [constraint constraintname] UNIQUE
Primary Key Constraint
A primary key is used to identify each row of the table uniquely. A primary key may be either consists of a single field or group of fields. It is a combination of both the unique key constraint as well as the not null constraint i.e. no column with the primary key constraint can be left blank and it must contain unique value in each row. We can declare the Primary key of a table with NOT NULL and UNIQUE constraint .SQL supports primary keys directly with the Primary Key constraint. When primary key is applied on a single field it is called as Simple Key and when applied on multiple columns it is called as Composite Primary Key. In a table there is only one primary key. The syntax of the Primary Key:
Column level syntax:
Columnname datatype(size) [ constraint_name] PRIMARY KEY
Check constraint
Check constraints allow Oracle to verify the validity of data being entered on a table against a set of constants. These constants act as valid values. The Check constraint consists of the keyword CHECK followed by parenthesized conditions. Check constraints must be specified as a logical expression that evaluates either to TRUE or FALSE. If an SQL statement causes the condition to be false an error message will be displayed.
Syntax of the check constraint is:
Column level syntax:
Columnname datatype (size) [constraint constraintname] CHECK (logical expression)
Default constraint
The default value constraint allows the user to insert the values in the columns where the user do not want to insert the value .The most common example of this constraint is NULL value, which is inserted in columns not defined with the NOT NULL constraint. This is not actually a constraint, it only specifies that a value should be inserted in a column if the user does not enter a value .The default values assignments are defined at the time of create table command. The datatype of the default value should match the datatype of the column.
The syntax is:
Columnname datatype (size) DEFAULT value;
Foreign Key constraint
A foreign key is a kind of constraint, which establishes a relationship among tables. A foreign key may be a single column or the combinations of columns, which derive their values, based on the primary key or unique key values from another table. A foreign key constraint is also known as the referential integrity constraint, as its values correspond to the actual values of the primary key of other table.
The syntax of the foreign key at the column level is:
Columnname datatype (size) references tablename [(columnname)] [ON DELETE CASCADE]
KEYS AND ITS TYPES
Key -A database consists of tables, which consist of records, which further consist of fields.
Types of Keys
Every key which has the property of uniqueness can be distinguished as following:
• Candidate Key
• Super Key
• Primary Key
• Alternate key
• Artificial key
• Foreign key
Candidate key
Candidate keys are those attributes of a relation, which have the properties of uniqueness and irreducibility, whose explanation is given below:
Let R be a relation. By definition, the set of all attributes of R has the uniqueness property, meaning that, at any given time, no two tuples in the value of R at that time are duplicates of one another.As in the case of the STUDENT relation, for example, the subset containing just attribute Roll number has that property. These facts constitute the intuition behind the definition of candidate key.
Let K be a set of attributes of relation R. Then K is a candidate key for R if and only if it possesses both of the following properties:
• Uniqueness: No legal value of R ever contains two distinct tuples with the same value for K.
• Irreducibility: No proper subset of K has the uniqueness property.
Super Key
A super key has the uniqueness property but not necessarily the irreducibility property. A candidate key is a special case of a super key.
For example if Roll_number is unique in relation STUDENT then, the set of attributes
(Roll_number, Name, Class) is a super key for a relation STUDENT, these set of attributes are also unique, but this combination of keys (composite key) is not having the property of irreducibility because Roll_number which is one subset of the composite key is also unique itself. Thus, this composite key is called as super key because it has the property of uniqueness but not the irreducibilty.
Consider a relation of Patient in which Patient_number is unique. Then, Patient_number is a candidate key and (Patient_number, Patient name) is a super key.Thus we can say that “A superset of a candidate key is a super key.”
Primary Key
The primary key is an attribute or a set of attributes that uniquely identify a specific instance of an entity. Every entity in the data model must have a primary key whose values uniquely identify instances of the entity. Sometimes a record may contain more than one key field.
For example the doctor’s patients file may contain both a patient number and a National Insurance number for each patient. Both of these are key fields. We therefore choose one of them and call it the primary key field. Primary key cannot contain any Null value because we cannot uniquely identify multiple Null values.
Alternate Key
The alternate keys of any table are simply those candidate keys, which are not currently selected as the primary key.
Exactly one of those candidate keys is chosen as the primary key and the remainder, if any, are then called alternate keys. An alternate key is a function of all candidate keys minus the primary key.
Artificial Key
Foreign Keys
Foreign keys are the attributes of a table, which refers to the primary key of some another table. Foreign Keys permit only those values, which appears in the primary key of the table to which it refers or may be null. Foreign keys are used to link together two or more different tables which have some form of relationship with each other. The foreign key is a reference to the tuple of a table from which it was taken, this tuple being called the Referenced or Target tuple. The table containing the referenced tuple will be called as Target table.
Subscribe to:
Posts (Atom)