RDBMS:
- RDBMS is the abbreviation for ‘Relational DataBase Management System’.
a) Database Management system
b) Database Application
- The Database management system is required to organize and maintain the data.
- The Database Application is a program to retrieve, insert and update the data in the system.
SERVICES OF DBMS
A DBMS provides the following services to do the activities mentioned previously. Let us see some of the services.
1) Data Definition
This is a method of defining data and its storage.
2) Data Maintenance
This method verifies whether each record has fields containing all information as defined using data definition.
3) Data Manipulation
Data Manipulation allows to insert, update, delete and sorting of data in the database.
4) Data Display
Data Display allows viewing the data in the database.
5) Data Integrity
This method verifies the integrity or accuracy of data stored in the database.
DATA TYPES
- Data are stored in a table which is a collection of fields. Oracle supports the following data types.
A. Character data types
1. CHAR
Char data type is used when a fixed-length character string is required. Up to 2000 bytes can be stored in this data type. The default value is one byte.
Syntax
CHAR (size)
Example: CHAR (20)
2. VARCHAR2
This data type is used to store variable-length character strings. It can also store alphanumerical values. The size of these data types varies from 1 to 4000 bytes.
Syntax
VARCHAR2 (size)
3. LONG
Cells of this data type can store variable character strings up to 2GB. Binary data in ASCII format can also be used in such data types. Based on the memory space available on the computer, its length would be restricted.
NOTE:
a) Only one column in a table can have this data type.
b) This column should not contain a unique or primary key constraint.
c) This column can’t be indexed
d) This data type can’t be used as an argument in any procedure.
B. Number Data types
1. NUMBER
A number data type is used to declare a column to store only numbers. This type supports the storage of integers, real numbers, floating-point numbers, etc.
Example:
Salary Number
Age Number
C. Date Data Type
All types of time-related information, including date and associated time, can be stored in a column declared with the date data type.
Syntax
DATE
Example:
Date_of_birth DATE
DATA DEFINITION LANGUAGE
The Data Definition Language is used for the following purposes.
1. To create an object
2. To alter the structure of an object
3. To drop an object
Note: Some of the database objects are Table, View, Sequence, Cursor, etc.
DDL used for a table
The DDL used for table definition is classified as
1. Create table command
2. Alter table command
3. Truncate table command
4. Drop table command
1. CREATING A TABLE
Syntax
CREATE TABLE <table name> (column1 datatype1,…, column data type n);
Example
CREATE TABLE STUDENT_MASTER (Roll_No NUMBER (4), Name VARCHAR2 (15), Sex VARCHAR2 (6), DOB DATE, DOAD DATE, Class VARCHAR2 (6), Guardian VARCHAR2 (30), Address VARCHAR2 (100));
If the above command is executed successfully, the message “table created” is displayed.
Important points:
a) The first letter should be an alphabet
b) Oracle reserve words cannot be used as table name
c) The length of a table should not exceed 30 characters
d) Different tables should have different names.
e) Underscore, numerals and letters are allowed. Blank space and single quotes are not allowed.
f) Double quotes can be used for naming a table.
2. ALTERING A TABLE
The structure of a table can be changed using this command.
a) Add a new column
b) Change the width of a data type
c) Change the data type of a column
d) Include an integrity constraint
e) Drop an integrity constraint
Syntax
ALTER TABLE <table name>MODIFY (column datatype,…);
ALTER TABLE <table name>ADD (column datatype,….);
Restrictions in altering a table
The following are the major restrictions in altering a table
a) Cannot change the name of a column
b) Cannot drop a column from the table
c) Cannot change the name of a table
d) Cannot decrease the size of a column if the table is non-empty.
e) Cannot change the data type if the column contains data that conflicts with the data type.
f) Cannot add constraints if the data in the table violates the constraint.
Example:
ALTER TABLE student_master MODIFY (name VARCHAR2 (25));
ALTER TABLE student_master ADD (tel_no NUMBER(10));
3. TRUNCATING A TABLE
Syntax
TRUNCATE TABLE < table_name>;
This command is used to delete all records stored in a table. If we use this command, the structure of the table is retained.
Example:
TRUNCATE TABLE student_master;
4. DROPPING A TABLE
This command is used to remove a table from the database.
Syntax
DROP TABLE <table name>;
Note:
We can’t drop a table if it is referenced by an existing table through a foreign key.
Data Manipulation Language (DML)
Data manipulation languages are used in the query and to manipulate the existing objects in the database like tables. The DML commands are
1. Insert command
2. Select command
3. Update command
4. Delete command
1. Insert command
The insert command is used to add one or more rows to a table.
Syntax
INSERT INTO <table name> VALUES (list of values);
Note:
The values are separated by commas
Values of data type char, varchar2, raw, long and date must be enclosed in single quotes.
The value must be entered in the same order as they appear in the table.
a) Inserting a row by entering all values for the columns defined in a table
Syntax
INSERT INTO <table name> VALUES (value1,value2,…);
Example:
INSERT INTO student_master VALUES (1001,’ R.BALU’,’ MALE’,’01-JAN-00’,
’11-JAN-03’,’CIVIL’, ’M.RAGU’,’28, Main Road, Nagercoil’);
b) Inserting values only for some particular columns
Syntax
INSERT INTO <table name> (column1, column2…) VALUES (value1, value2,..);
Example:
INSERT INTO student_master (roll_no, name) VALUES (2001,’ J.ARUN’);
c) Entering a large number of rows
INSERT INTO staff_master
VALUES (&staff_id,’&subject’,’&name’,’&doap’,’&street’,’&city’,’&pin’);
2. Select command
The select command is used to retrieve the stored data from a table.
Syntax
SELECT * FROM <table_name>;
(or)
SELECT column1,column2,.. FROM <table_name>;
Selecting distinct rows
To avoid the selection of duplicate rows, the distinct clause is used in the select statement.
Syntax
SELECT DISTINCT field_name FROM <table_name>;
Selecting specific rows
To select specific rows from a table, where clause is used in the select statement after the from clause.
Syntax
SELECT column1,column2,.. FROM <table_name> WHERE <condition> ORDER BY column DESC
3. Update command
The update command is used to update (changing values in one or two columns of a row) rows in a table. Specific rows can also be updated based on some conditions.
Syntax
UPDATE <table_name> SET column1=expression,
Column2=expression…WHERE <search_condition>
4. Delete command
The delete command is used to delete rows from a table.
Syntax
DELETE FROM <table_name> WHERE <searchcondition>;
Deletion of the specific row
DELETE FROM staff_master WHERE name=’E. Suriya’;
Deletion of all rows
DELETE FROM staff_master;
Transaction Control Language (TCL)
All changes made to the database are defined as a transaction. It is a logical unit of work. The transaction can be made permanent in a database only if they are committed.
The two transaction control commands are
1. Commit
2. Rollback
1. Commit
The commit command is used to make all transaction changes permanent to the database.
This command erases all savepoints and releases the transaction lock.
Syntax
Commit;
2. Rollback
The rollback command is used to undo all the changes made in the current transaction. We can either roll back the entire transaction or roll back a transaction to a savepoint.
Syntax
Rollback;
