SEQUNCES Lecture_Notes_Unit4_chapter11_sequence

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.

1. RDBMS -Unit IV
Chapter 11
SEQUENCES
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. Sequences
➢Asequenceissimplyanautomaticcounter,whichis
enabledwheneveritisaccessed.
➢
➢Thisguaranteesthattherearenotworowswiththe
samecode.
➢Adefaultsequencehasthefollowingcharacteristics:
•Always starts from the number 1.
•In ascending order.
•Increases by 1.

3. The CREATE SEQUENCE Command
➢TheSQLcommandusedtocreateasequenceisthe
CREATESEQUENCEcommand.

4. The CREATE SEQUENCE Command
➢STARTWITH:Indicatestheinitialvalueofthe
sequence.
➢INCREMENT BY:Indicatesthevaluebywhichthe
sequencewillbeincrementedeachtimeitisaccessed.
➢MAXVALUE:Indicatesthemaximumvalueofthe
sequence.Whenomitted,themaximumvalueofthe
sequencecanbe1.00e+27.
➢NOMAXVALUE :Indicatesthatthesequencedoesn’t
haveapredefinedmaximumvalue.
➢CYCLE/NOCYCLE :CYCLEindicatesthatthe
sequenceshouldreturntotheinitialvaluewhenthe
maximumvalueisreached.

5. Pseudo-Columns
➢Oraclehasseveralpseudo-columnsthatbehaveasan
extracolumnwhenanewtableiscreated.
➢Thecurrvalandnextvalpseudo-columnsaredirectly
relatedtosequences.Therearetwoadditionalpseudo-
columns:levelandrownum
Currval:Returnsthecurrentvalueofthesequence.
Nextval:Increasesthevalueofthesequenceandreturns
thenextvalue.
Rowid:Itisusedtoindividualizeeachrowofatable.
rowidoffersafasterandmorepracticalwaytoaccessa
rowintheentiredatabase.

6. Example for ROWID

7. Example for ROWNUM
➢Therownumpseudo-columnreceivesanumber
thatcorrespondstothepositionofarowintheset
ofrowsreturnedbyaquery.

8. Creating a Sequence –Example 1
➢Thefollowingexampleillustratesthecreationofthe
Newseqsequenceusingthedefaultvalues:
➢SQL>createsequencenewseq;

9. Checking a User’s List of Sequences
Tocheckthesequencesthatwerecreatedforauserand
thevaluesoftheirparameters,youuseatablecalled
USER_SEQUENCES.

10. Sequence –Example2
➢createasequencewithaninitialvalue,anincrement
value,andthesizeofthecachespecifiedbythe
currentuser:

11. Sequence –Example3
➢Inthisnextexample,thesequenceiscreatedwith
minimumandmaximumvalues:
SQL> create sequence newseq3
minvalue1000
maxvalue4000;
Sequence created:

12. Deleting a Sequence
➢TheDROPSEQUENCE commandremovesthe
sequencefromtheschemainwhichitwascreated.In
thisexample,Newseq2isremoved:
SQL> drop sequence newseq2;

13. Changing a Sequence
➢WiththeALTERSEQUENCEcommandtheusercan
changesomeofthesequence’sparameters.However,
thereareseveralrestrictions.
➢Theinitialvaluecannotbealtered
➢Theminimumvalueforthetablecannotbegreater
thanthecurrentvalue.
➢Theincrementvaluecanbechangedwithoutany
problems.Inthenextexample,twosuccessfulchanges
weremadetotheNewseq2sequence:
SQL>altersequencenewseq2
minvalue30
incrementby20;
Sequencealtered.

14. Using a Sequence
➢SQL>createnewseq3minval1000maxval1005.
➢Thesequenceisenabledthefirsttimeitisused,and
displaysitsinitialvalue:
SQL>selectnewseq3.nextvalfromdual;
NEXTVAL
-------
1000
➢Thesecondtimeitisused,itdisplaysthevalue
incrementedby1.
SQL>run
NEXTVAL
-------
1001

15. Using a Sequence
➢Whenweaccesstocurrval,itdoesnotchangethe
valueofthesequence:
SQL> select newseq3.currval from dual;
CURRVAL
-------
1001
SQL> run
CURRVAL
-------
1001

16. Using a Sequence
➢InsertingSequencenumbertothedatabase.
1.Createatable
2.createasequence
3.Insertsequenceintoatable.
3.Viewtherecordsfromtable.
1.Createtableproduct(snonumber(3),item_name
char(20),bill_datedate);
2.Createsequenceserialminvalue1maxvalue100;
3.Insertintoproductvalues(serial.nextval,’RICE’,’14-
JUL-19’);
4.Repeatthestep3asyouwanttoinsert.
5.Select*fromproduct;