Lecture_Notes_Unit4_Chapter_8_9_10_RDBMS for the students affiliated by alagappa university

Title: Relational Database Management System Concepts(RDBMS)

Description:
Welcome to the comprehensive guide on Relational Database Management System (RDBMS) concepts, tailored for final year B.Sc. Computer Science students affiliated with Alagappa University. This document covers fundamental principles and advanced topics in RDBMS, offering a structured approach to understanding databases in the context of modern computing. PDF content is prepared from the text book Learn Oracle 8I by JOSE A RAMALHO.

Key Topics Covered:

Main Topic : DATA INTEGRITY, CREATING AND MAINTAINING A TABLE AND INDEX
Sub-Topic :
Data Integrity,Types of Integrity, Integrity Constraints, Primary Key, Foreign key, unique key, self referential integrity,
creating and maintain a table, Modifying a table, alter a table, Deleting a table
Create an Index, Alter Index, Drop Index, Function based index, obtaining information about index, Difference between ROWID and ROWNUM

Target Audience:

Final year B.Sc. Computer Science students at Alagappa University seeking a solid foundation in RDBMS principles for academic and practical applications.


About the Author:
Dr. S. Murugan is Associate Professor at Alagappa Government Arts College, Karaikudi. With 23 years of teaching experience in the field of Computer Science, Dr. S. Murugan has a passion for simplifying complex concepts in database management.

Disclaimer:
This document is intended for educational purposes only. The content presented here reflects the author’s understanding in the field of RDBMS as of 2024.

Feedback and Contact Information:
Your feedback is valuable! For any queries or suggestions, please contact [email protected]

1. RDBMS -Unit IV
Chapter 8
Data Integrity
Prepared By
Dr. S.Murugan, Associate Professor
Department of Computer Science,
AlagappaGovernment Arts College, Karaikudi.
(Affiliated by AlagappaUniversity)
Mailid: [email protected]
Reference Book:
LEARN ORACLE 8i
JOSE A RAMALHO

2. Data Integrity -Definition
➢Dataintegritycanbedefinedasrulescreatedbythe
developer,orDBA,inordertoassuretheconsistency
ofinformationmaintainedinthetables.
➢Forexample,lookattheDEPTandEMPtablebelow

3. Data Integrity -Example

4. Data Integrity -Example
When these tables were created, the following integrity
rules were stipulated:
➢TheDeptnocolumnoftheDEPTtablecannot
containduplicatevalues.Thevaluemustbeunique.
Forexample,therecan’tbetwodepartmentswiththe
code20.
➢TheEnamecolumnoftheEMPtablecannotbeleft
blank(Thiscolumnmustcontainvalue).Other
columnsmayhavenullvalues,allowingthemtobe
leftempty.

5. Data Integrity -Example
➢TheDeptnocolumnoftheEMPtablecancontain
onlythosedepartmentnumbersthatcorrespondto
theDeptnocolumnoftheDEPTtable.
➢TheSalcolumncontainsonlyvaluesbelow10,000.
➢TheEmpnocolumncannotbeempty,andmustbe
unique.

6. Types of Integrity
Thereareseveralwaystoforcedataintegrityina
table’scolumn.Manyofthemaredefinedasrules,and
areappliedtothestructuresofthetable.
Nulls:Thefieldcanbeleftblank.Ex:comminthe
emptable.
UniqueValues:Theuniquevaluerulemeansthateach
valueinaparticularcolumnisunique,suchasDeptno
intheDEPTtable.
PrimaryKeyValues:Theprimarykeyvaluerule
specifiesthateachrowofatablemustbeidentifiedbya
uniquevalue.(Ex:EmpNo)

7. Types of Integrity
Referential Integrity:
➢Thereferentialintegrityruledeterminesthatthe
valueorgroupoffieldscorrespondstothekeyfields
ofothertables.
➢Forexample,theDeptnocolumnoftheEMPtable
acceptsonlythevaluesthatareregisteredinthe
DeptnocolumnoftheDEPTtable.
Triggers
➢Triggersareroutinesorproceduresthatareused
whenanINSERT,UPDATE,orDELETEcommand
isexecutedinatableoraview.

8. Integrity Constraints
➢Aconstraintisadeclarativemethodfordefiningthe
ruleforacolumn.Oraclehasthefollowing
constraints.Theirnamesaresimilartothoseofthe
integrityrules:
➢NOTNULLforrulesassociatedwithnulls.
➢UNIQUEKEYforuniquevaluerules.
➢PRIMARYKEYfortherulesassociatedwith
primarykeys.
➢FOREIGNKEYfortherulesassociatedwith
referentialintegrity.
➢CHECKforcomplexrules.

9. Understanding Constraints
➢Constraintsareassociatedtoaspecifictableandare
storedinthedatadictionary.IftheSalfield,for
example,hasaconstraintthatlimitsthemaximum
valueto10,000,andsomeonetriestoinsert11,000,
anerrormessageisdisplayedandtheoperationis
cancelled.
NOT NULL Constraints
➢TheNOTNULLconstraintrequiresthatthecolumns
ofthetablealwayscontainavalue.Forexample,the
EnamecolumnoftheEMPtablehasthisconstraint,
whiletheCommcolumnallowsnullvaluestobe
accepted.

10. Understanding Constraints
UNIQUE KEY Constraints
➢ Eachofthevaluesintherowsinacolumnor
groupsofcolumnsthatformthekeyarerequiredby
theUNIQUEKEYconstrainttobeunique.The
DnamecolumnoftheDEPTtable,forexample,has
theUNIQUEconstraint,becausetherecannotbe
morethanonedepartmentwiththesamename.
➢ TheLoccolumn,ontheotherhand,cancontain
duplicatevalues,becausetherecanbedifferent
departmentsinthesamecity.

11. Understanding Constraints
PRIMARY KEY Constraints
➢Thecolumn(orgroupofcolumns)includedinthe
definitionoftheconstraintiscalledthePRIMARY
KEY.Eachtableshouldhaveatleastoneprimary
key.
➢Figure8.2showstheDEPTtable,wheretheDeptno
columnistheprimarykey.Therefore,nulland
duplicatevaluesarenotallowed.

12. Understanding Constraints
FOREIGN KEY Constraints
➢Foreachrowofthechildtable,theremustbea
correspondingrowintheparenttable.
➢Thefigure8.2showsthattheDeptnocolumnofthe
EMPtablewasdefinedwiththeFOREIGNKEY
constraint,andthatitisassociatedtotheDEPTtable
throughtheDeptnofield.
➢The names of the FOREIGN KEY and PARENT
KEY columns do not need to be the same; however,
having the same name helps to understand the
association.

13. Understanding Constraints
FOREIGN KEY Constraints

14. Understanding Constraints
CHECK Constraints
➢CHECK,thelasttypeofconstraint,allowsthe
creationofaroutinewritteninPL/SQLthatperforms
complextestsandreturnsavalue(trueorfalse).
➢WhenaDMLcommandresultsinfalse,the
commandisundone.

15. Understanding Constraints
SELF-REFERENTIAL Constraints
➢AnothertypeofreferentialintegrityistheSELF-
REFERENTIAL constraint,whichreferencesa
columninthetableitself.Here,thePARENTKEYis
inthetableitself,andnotinanexternaltable.
➢Apracticalapplicationofthistypeofconstraintis
theEMPtable.
➢TheMgrcolumncontainsanemployeenumberthat
correspondstotheemployee’smanager.Toprevent
incorrectemployeecodesfrombeinginserted,you
canapplySELF-REFERENTIAL integrity,
specifyingtheEmpnocolumn.

16. Understanding Constraints
SELF-REFERENTIAL Constraints

17. Understanding Constraints
DeferredConstraints
➢Aconstraintcanhaveitscheckingpostponeduntil
theendofthetransaction.Inthiscase,thesystem
causesarollbackoftheentiretransactionifitisnot
undone.

18. Chapter 9
Creating and Maintaining Tables

19. The CREATE TABLE Command
The CREATE TABLE command creates a new table
through the following operations:
1. Definition of the columns
2. Definition of the table organization
3. Integrity constraints
4. Tablespaceof the table
5. Storage characteristics
6. An optional cluster
7. Query data
8. Rate of parallelism used in the creation of the table, and
default rate of parallelism for the queries of the table
9. Definition of partitions
10. Arrangement by index or heap (the storage area that
allows random access)

20. Basic syntax
CREATE TABLE schema.table(column1 datatype
DEFAULT expr
column_constraint, ...columnX)
PCTFREE integer
PCTUSED integer
INITRANS integer
MAXTRANS integer
TABLESPACE tablespace
STORAGE storage_clause
ENABLE enable_clause
DISABLE disable_clauseAS subqueryPARALLEL
parallel_clause
CACHE
NOCACHE ( column datatype)
RECOVERABLE
UNRECOVERABLE table_constraint

21. Arguments
schema
An optional parameter that indicates the schema to which the table
belongs.
table
The name of the table to be created.
datatype
Specifies the column’s data type. Up to 254 columns are allowed.
DEFAULT
This is optional, and indicates a default value for the column.
CONSTRAINT
This is optional and indicates some integrity control that applies to
the column; for example, it must contain a value.
TABLE CONSTRAINT
This is optional and indicates some integrity control that applies to
the table; for example, the primary key.

22. Arguments
PCTFREE
This is optional and indicates the percentage of each data block that
will be reserved for future updating in the table. The default value is
10.
PCTUSED
This is optional and indicates the minimum percentage of used
space that Oracle must maintain before a data block is eligible for a
row insertion. The default value is 40.
INITRANS
The default value is 1. It can vary from 1 to 255 and indicates the
number of transactions assigned inside the data block of the table.
MAXTRANS
Indicates the maximum number of transactions that can update a
data block.

23. Arguments
TABLESPACE
This is optional; it indicates a tablespacedifferent from that used as
default.
CLUSTER
This is optional; it indicates that the table is part of a cluster.
ENABLE
This is optional; it links the integrity restrictions.
DISABLE
This is optional; it disables the integrity restrictions.

24. Example for primary Key, default and not
null constraints
Table Creation
create table newagain
(first number(4) constraint pk_newprimary key,
second date default sysdate,
third varchar(20) not null);
Method1forinsertion:
insertintonewagainvalues(1111,'18-JUN-19','karaikudi')
Method2forinsertion:
insertintonewagain(first,third)values(3333,'cccc');
select*fromnewagain

25. Example for Data Integrity
Parent Table Creation
CREATE TABLE dept1
(deptnoNUMBER (2) PRIMARY KEY,
dnameVARCHAR2(10),
locVARCHAR2(9) );

26. Example for Data Integrity
CREATE TABLE empagac2
(empnoNUMBER CONSTRAINT pk_empPRIMARY KEY,
enameVARCHAR2(10) CONSTRAINT nn_enameNOT NULL
CONSTRAINT upper_ename
CHECK (ename= UPPER(ename)),
job VARCHAR2(9),
mgrNUMBER CONSTRAINT fk_mgrREFERENCES
empagac2(empno),
hiredateDATE DEFAULT SYSDATE,
salNUMBER(10,2) CONSTRAINT ck_sal
CHECK (sal> 500),
commNUMBER(9,0) DEFAULT NULL,
deptnoNUMBER(2) CONSTRAINT nn_deptno
NOT NULL CONSTRAINT fk_deptno
REFERENCES dept1 (deptno) )

27. Example for Data Integrity
insert into dept1 values (10,'sales','Trichy')
1 row(s) inserted.
insert into empagac2 values
(1001,'RAJESH','Manager',1001,'10-apr-
19',15000,150,10)
1 row(s) inserted.
Test for Primary Key: (1001 already exists)
insert into empagac2 values
(1001,'RAJESH','Manager',1001,'10-apr-
19',15000,150,10)
ORA-00001: unique constraint
(SYSTEM.PK_EMP) violated

28. Example for Data Integrity
Test for Upper Case Letters: (Name typed in
lowercase letters)
insert into empagac2 values
(1002,'mani','Manager',1001,'10-apr-19',15000,150,10)
ORA-02290: check constraint
(SYSTEM.UPPER_ENAME) violated

29. Example for Data Integrity
Test for Self Referential Integrity constraints 1004 is
not an Employee number
insert into empagac2 values
(1002,'MANI','Manager',1004,'10-apr-
19',15000,150,10)
ORA-02291: integrity constraint
(SYSTEM.FK_MGR) violated -parent key not
found

30. Example for Data Integrity
Test for Salary field (The salary must >500)
insert into empagac2 values
(1002,'MANI','Clerk',1001,'10-apr-19',500,150,10)
ORA-02290: check constraint (SYSTEM.CK_SAL)
violated
Test for null (Accept null values for commission)
insert into empagac2 values
(1002,'MANI','Clerk',1001,'10-apr-19',1500,'',10)

31. Example for Data Integrity
Test for Foreign Key (DNO 20 is not present in the
DNO of DEPT Table)
insert into empagac2 values
(1004,'SELVAM','Clerk',1001,'10-apr-19',1500,150,20)
ORA-02291: integrity constraint
(SYSTEM.FK_DEPTNO) violated -parent key not
found

32. Creating a Table with the Contents of
Another Table
➢UsingtheASoption,wecancreateatablewiththe
SELECTcommandtorecoverthestructureanddata
ofanothertable.
➢Thefollowingexamplecreatesatablecalled
NEWEMPwithallthecolumnsoftheEMPtable
andonlytherowsofdepartment20:
➢createtablenewempasselect*fromempwhere
deptno=20;

33. Constraint Disabling
Constraints can be disabled by the keyword DISABLE.
SQL> CREATE TABLE dept1
(deptnoNUMBER (2) PRIMARY KEY DISABLE,
dnameVARCHAR2(10),
locVARCHAR2(9) );
This option will allow the creation of duplicate keys,
For Ex:
SQL> insert into dept1 values(10,’aaaa’,’aaaa’);
1 row created.
SQL> insert into dept1 values(10,’bbbb’,’bbbbb’);
1 row created.

34. Modifying Tables
➢Itincludeaddingorremovingcolumnsand
constraints;changingcharacteristicsofcolumns,
suchasdatatype,size,anddefaultvalue;
ChangingaColumn
➢Tochangethecharacteristicsofacolumn,the
MODIFYoptioncanbeused,andspecifythe
characteristicsforthecolumn.Thefollowing
exampleaddsaNOTNULLconstrainttotheSal
columnoftheNEWEMPtable:
➢ALTERTABLEnewempMODIFY(salNUMBER
CONSTRAINTnn_salNOTNULL);

35. Modifying Tables
AddingColumns
ByusingtheADDoption,wecanaddnewcolumnsto
thetable.Inthefollowingexample,weaddaState
columntotheDEPT1table:
SQL>altertabledept1add(statevarchar(2));
Tablechanged.

36. Modifying Tables
Eliminating a Column
Toeliminateacolumn,usetheDROPoption.For
example,toeliminatetheCommcolumnfromthe
NEWEMP2table:
SQL>altertablenewemp2drop(comm);
Tablechanged.

37. Modifying Tables
Output for Before Eliminating commcolumn
Output for After Eliminating commcolumn.
7521 WARD SALESMAN 7698 02/22/81 1250 30

38. Deleting a Table
➢Toremoveatablefromadatabase,usetheDROP
TABLEcommand:
Syntax:
DROPTABLEschema.name_of_table
Example:
SQL>droptabledemo1;
Tabledropped.
➢Ifthedeletedtablehasaprimaryoruniquekey
referencedbytheFOREIGNKEYsofother
tables,andyouwanttodeleteaconstraintofthe
othertable,usetheCASCADECONSTRAINTS
option:
DROPTABLEtableCASCADECONSTRAINTS

39. Chapter 10
Indexes

40. Indexes
➢ToAccesstherecordsinfastwayfromthe
table,oracleprovidestwomechanism.One
isrowidandanotheroneisindex.
➢Therowididentifiesarowinsideatable,
itisnotconvenientforapplicationsthat
mostlyusespecificcodestoaccess
records,suchastheemployeecode,
productcode,etc.
➢Tospeedrowaccessinatable,Oracle
usestheconceptofindexes.Theseare
similartoindexesfoundinabook.

41. Indexes

42. When to Create Indexes
➢Anindexcanbecreatedduringthedesign
processofthetablestructurebyusingthe
PRIMARYKEYconstraint.
➢Columnsoftypelobs,long,andlongraw
cannotbeindexed.

43. Creating Indexes with Constraints
➢Theindexiscreatedduringthetable
creationbyusingthekeywordthrough
usingindex.

44. Creating Indexes with the CREATE INDEX
Command

45. Creating Indexes with the CREATE INDEX
Command
➢TheCREATEINDEXcommandis
responsibleforthecreationofsimple
indexesusingauniquecolumn,or
compositeindexesusingmorethanone
column.
➢Tocreatecompositeindexusethe
followingquery:
➢Createindexempind3onempind(name,
idno)

46. Creating a Function-Based Index
➢Function-basedindexesimprovethequeriesthatuse
avaluereturnedbyafunctionorexpression.
➢Oneadvantageofusingthistypeofindexisthe
abilitytoincludesortsbasedonkeyscontaining
wordswithaccents(Eitherlowercaseorupper
case).
➢ForExample,thecreationofanindexwithallthe
namesconvertedtouppercase,usingtheUPPER
function:
CREATE INDEX x1 ON newemp2 UPPER(ename));

47. Creating a Function-Based Index
➢Even when there are names in lowercase or combining
lowercase and uppercase, the following SELECT
command returns data from all the entries that include
“Smith”:
➢SELECT * FROM newemp2 WHERE
UPPER(ename) = ‘SMITH’; (If the column ename
contains the value smith, it will be displayed)
➢The following command would not display the
lowercase letters.
SELECT * FROM newemp2 WHERE ename=‘SMITH’

48. Creating an Index Using Key Compression
➢Oracleallowsthecreationofanindexusingkey
compression,atechniquethathelpstoeliminatethe
duplicatedvaluesofakeycolumn.
➢Tocreateacompressedindex,justaddthe
COMPRESSclause,asshowninthefollowing
example:
CREATEINDEXemp_enameonemp_test(ename)
COMPRESS1
➢KeycompressioncanbedisabledwiththeALTER
INDEXcommandandtheNOCOMPRESSclause:
ALTER INDEX emp_ename REBUILD
NOCOMPRESS;

49. Changing an Index
➢Changinganindexisrestrictedtomodifyingthe
transactionandstorageparameters.Itisnotpossible
tochangethecolumnsintheindex.Inthissituation,
theindexmustbedeletedandre-createdwithanew
definition.
➢Thefollowingcommandchangestheindex:
SQL>alterindexpk_emp3
INITRANS5
MAXTRANS10
STORAGE(PCTINCREASE50);

50. Re-creating an Index
➢Anindexcanbere-createdbyusingtheALTER
INDEXcommand.Thisoperationistheequivalent
ofdeletingandreconstructingtheindex.
➢Tore-createanexistingindex,usethefollowing
format:
ALTERINDEXindex_nameREBUILD;

51. Obtaining Information about Indexes
➢The USER_INDEXES command is used to obtain
information about the index of each user.
➢For example,
SQL> select index_name, table_name, uniqueness,
ini_trans, max_transfrom user_indexes;

52. Eliminating an Index
The SQL command responsible for the elimination of
an index is DROP INDEX.
Syntax:
DROP INDEX name
Example:
DROP INDEX depemp

53. Difference between ROWID and ROWNUM
RowIdis Permanent. It locates the physical address.
Rownumis Temperary. It locates the record number.