Download Study notes for final year student and more Study notes Computer Science in PDF only on Docsity!
CONTENTS
- DatabaseConcepts
- 1.1 Introduction
- 1.2 Relationship
- 1.3 DBMS
- 1.4 RelationalDatabaseModel
- 1.5 IntegrityRules
- 1.6 TheoreticalRelationalLanguage
- Database Design
- 2.1 DataModeling
- 2.2 Dependency
- 2.3 Normalization
- 2.4 De-normalization
- Oracle9i
- 3.1 DatabaseSystem Architectures
- 3.2 Oracle9i introduction
- 3.3 SQL*Plus Environment
- 3.4 SQL
- 3.5 InteractionbetweenSQLandSQL*plusEnvironment
- 3.6 SQL* Plus Commands
- 3.7 Oracle ErrorcodesandHelp
- Oracle Tables
- 4.1 DDL
- 4.2 Datatypes
- 4.3 Constraints
- 4.4 Creatingan OracleTable
- 4.5 DisplayingTableinformation
- 4.6 Alteringan ExistingTable
- 4.7 Oracle Tabletypes
- 4.8 Spooling
- 4.9 ErrorCodes
- Workingwith Tables
- 5.1 DataManipulationLanguage- (DML)
- 5.2 AddingANewRow/Record
- 5.3 UpdatingExistingRows/Records
- 5.4 DeletingExistingRows/Records
- 5.5 RetrievingDataFrom ATable
- 5.6 ArithmeticOperations
- 5.7 Sorting
- 5.8 RevisitingSubstitution Variables
- 5.9 DefineCommand
- 5.10 CaseStructure
- Functionsand Grouping
- 6.1 Built-In-Functions
- 6.2 Single-RowFunctions
- 6.3 CharacterFunctions
- 6.4 Numeric Functions
- 6.5 DateFunctions
- 6.6 OtherFunctions
- 6.7 ConversionFunctions
- 6.8 NestedFunctions
- 6.9 GroupFunctions
- 6.10 GroupingData
- 6.11 HavingClause
- 6.12 NestingGroup Functions
- JoinsAndSet Operators
- 7.1 Equi Join
- 7.2 NonEqui Join
- 7.3 OuterJoin
- 7.4 SelfJoin
- 7.5 SetOperators
- PL/SQL
- 8.1 HistoryandFundamentals
- 8.2 BindVariables
- 8.3 SubstitutionVariables
- 8.4 Printing
- 8.5 ArithmeticOperators
- 8.6 ControlStructures
- DataManipulation in PL/SQL
- 9.1 DMLStatements
- 9.2 TransactionControlStatements
- 9.3 Cursors
- 9.4 ImplicitCursor
- 9.5 ExplicitCursor
- 9.6 CursorAttributes
- 9.7 CursorForLoops
- 9.8 Exceptions
- PL/SQLCompositeDataType
- 10.1 PL/SQLRecord
- 10.2 PL/SQLTable
- 10.3 PL/SQRVarrays
- NamedBlocks
- 11.1 Procedures
- 11.2 Functions
- 11.3 Packages
- 11.4 Triggers
- APPENDIX
1.DATABASECONCEPTS
1.1 Introduction
A database is an organized collection of data. The data are typically organized to modelrelevantaspectsofrealityinawaythatsupportsprocessesrequiringthisinformation.Itisanelectronicsto rageof data. It storesinformation andrelationshipamongdifferentthings. Basictermstoexaminethestructureof aDatabase Thetermsareentity,data,attributes,entitysetandrelationship. Entity: Aperson,place,eventoritemiscalledanentity.Example:Customer,Transaction,Employee,Library, etc., Data: The facts describing an entity are known as data. Example: For a Customer, custno, custname,address…,etc.,aremaintainedinaregister whichdescribesthecharacteristicsoftheentityCustomer. Attributes: Eachentitycanbedescribedbyitscharacteristics,whichareknownasattributes.Example: Fora Student, name, regno, class,address etc…. Entity Set: All the related entities are collected together to form an entity set. An entity set is given asingular noun. Example: The student entity set contains data about student only. All related entity inthe STUDENT entity set is student. Similarly a company keeps track all of its employees is an entitysetcalledEMPLOYEES.TheEMPLOYEEentitysetdoesnotcontainaboutthecompany’scustomers,b ecauseitwould notmake anysense. Database: A database is a collection of entity sets. Example: A college database may include entitiessuchasstudent,faculty,course,term,office,building,exametc.,theentitiesinadatabasearelikelytoint eractwith otherentities. Relationship: The interactions between the entity sets are called relationship. Example: A facultymember teachesina building,sotherelationshipbetween FACULTYandBUILDINGisteaches.
1.2 Relationship
Theinteractions betweenthe entitysetsarecalledrelationship. Example: A faculty member teaches in a building, so the relationship between FACULTY andBUILDINGisteaches.
USER
APPLICATIONS DBMS DATABASE
OPERATINGSYSTEMSOFTWARE
HARDWARE
▪ Software- OperatingSystemandaNetworkoperatingsystem. ▪ Userdevelopedandimplementeddtaabase(s)– Tables,DataDictionary,Recordsandotherdatabaseobjcts. ▪ CustomApplication suchasdataentryform,reports,queries,blocksandprograms. ▪ Personnel – ADatabase Administrator(DBA),DatabaseDesigner, Analyst,ProgrammerandEnd User. The database system is utilized as a decision-making system and is also refered to as aninformation system. A DBMS based on a relational model is also known as a Relational DataBaseManagement System(RDBMS).
1.3.2 FunctionsofRDBMS
Dataaretherawmaterials.Informationisprocessedmanipulated,collectedororganizeddata.AnRDBMS notonlymanagesdatabutisalsoresponsibleforother importantfunctions. ▪ Itmanagesthedataandrelationsstoredinthedatabase. ▪ It creates a data dictionary which is a system structure that stores metadata. A metadata is dataaboutdata.It icludestablenames,attributes,datatypes,physical space,relatinshipandsoon. ▪ Itmanagesallday-to-daytransactions. ▪ It allowsuserstospecifyvalidationruleswhich avoidincorrect datavalues. ▪ It performsbookkeepingduties. ▪ Ittransformslogical datarequesttomatchphysicaldatastructure. ▪ ItSecuresaccessthroughPassword,Encryptionandrestircteduserrights. ▪ Itprovidesbackupandrecoveryprocedurefor physicalsecurityof data. ▪ It allowsusertosave datawith datalockingcapabilities ▪ Itprovidesimportandexportutilitiestousedatacreatedindatabasetootherdatabaseorspreadsheetsof tware ortouse datain othersoftwares. ▪ Itreducesdataredundancy
1.4 TheRelationalDatabaseModel
Because of its simplicity in design and ease in retrieval of data, the relational database modelhas been very popular, especially in the personal computer environment. E.F code developed therelationaldatabasemodel in1970. Themodelisbasedonmathematicalsettheory and itusesarelationas the buildingblockofthe database.
Table: Atableisamatrixofrowsandcolumnsinwhicheachrowrepresentanentityandeachcolumnrepresenta n attribute. Tuple: Inrelational terminologyarowisreferedasatuple. Degree: Thenumberofcolumnsinatableiscalledthedegree.Example:Atablehasfourcolumnsthenthe degree ofthe tableis 4. Domain: Thesetofallpossiblevaluesthatacolumnmayhaveiscalledthedomainofthatcolumn.Twodomainsar e same onlyiftheyhave the same meaninganduse.
TerminologyComparison
Relationalterminology Filesystem terminology Entity set (or) Table (or)Relation File Entity(or)Row(or)Tuple Record Attribute(or) Column Field A key isaminimalsetofcolumnsusedtouniquelydefineanyrowinatable.A uniquekey or primarykey is a keythatuniquelydefinesthecharacteristics ofeach row. PrimaryKey Whenasinglecolumnis usedasauniqueidentifier,itisknown asprimarykey.
- Aprimarykey cannotallow nullvalues.
- Eachtablecanhave at mostone primarykey.
- OnsomeRDBMSaprimarykeyautomaticallygeneratesa clustered table indexbydefault. UniqueKey
- Aunique key can allow nullvalues.
- Eachtablecanhave multiple uniquekeys.
- OnsomeRDBMSa uniquekeyautomaticallygeneratesa non-clustered tableindexbydefault. CompositeKey When a combination of columns is used as a unique identifier it is known as a compositeprimarykey orcompositekey. SecondaryKey Sometimes, a more human approach is used to identify or retrieve a row from a table becauseit is not possible to remember primary key values such as the employee number, Book number,department number etc., that time name of the employee, author of the book may be noted with theprimarykey. This keyis known as SecondaryKey.
WIPRO
REGNO NAME COURSE
72 GOPI CS
82 JEEVA CS
62 PRIYA CS
TCS
REGNO NAME COURSE
73 KAVIN CS
62 PRIYA CS
72 GOPI CS
- Union
- Intersection
- Difference
- Projection
- Selection
- Product
- Assignment
- Join
- Division.
1. Union
The union of two tables result in retrieval of all the rows that are inboth tables. The duplicaterowsare eliminatedfromthe resultingtable. Basicrequirementtoperformunionoperations ontwotables: (i) Bothtablesmust havethesamedegree. (ii) The domains of the corresponding columns in two tables must be same.Suchtablesaresaid to beunioncompatible. ConsideranexampleoftwotablesnamedWIPROandTCSwhichcontainsthenamesofthestudentspl acedinthose companies. TABLE_A=WIPRO TCS TABLE_A REGNO NAME COURSE 72 GOPI CS 82 JEEVA CS 62 PRIYA CS 73 KAVIN CS 62 PRIYA CS 72 GOPI CS
2. Intersection The insertionoftwotables producesatablewith rows thatare inbothtables. Thetwotablesmusthavebe union compatible to performanintersection on them. TABLE_B =WIPRO ∩ TCS TABLE_B REGNO NAME COURSE 82 JEEVA CS 62 PRIYA CS 3. Difference Thedifferenceoftwotablesproceduresatablewithrowsthatarepresentinthefirsttablebutnotinthe secondtable.Thedifferencecan beperformed onunion-compatible tablesonly. TABLE_C=WIPRO- TCS TABLE_C REGNO NAME COURSE 82 JEEVA CS 4. Projection Theprojectionoperationallowscreatingtablebasedondescribablecolumnsfrom allexistingcolumnsina table.The projection operationreturnsthe “verticalslices”ofa table. ▪ Theprojectionisindicatedbyincludingthetablename andalist of described columns PARTS PARTNO PARTDESC VENDOR COST 11 NUT RICHARDS 20. 22 BOLT BLACK 5. 33 WASHER ALBERT 55.
DEPT
DNO DNAME
10 PRODUCTION
20 SUPPLIES
30 MARKETING
EMP
ENO ENAME DNO
100 CARTER 10
200 ALBERT 20
300 BEENS 30
TABLE_G=EMPLOYEE*DEPARTMENT
TABLE_G
ENAME DEPTNAME
CARTER PRODUCTION
CARTER SUPPLIES
CARTER MARKETING
ALBERT PRODUCTION
ALBERT SUPPLIES
ALBERT MARKETING
7. Assignment
▪ Thisoperationcreatesanewtablefromexistingtables. ▪ Assignment(=)givesanabilitytonamenewtablesthatarebasedonothertablesassignmentisnotanora cle term. TABLE_D=TCS- WIPRO TABLE_D REGNO NAME COURSE 73 KAVIN CS
8. Join ▪ The join is one of the most important operations because of it’s ability to get related data froma numberoftables. ▪ Thejoin isbased oncommonsetof values, whichdoesnot havethesamenameinboth tablesbut does haveto have the same domain in bothtables. ▪ Whenajoin basedon qualityofvaluesitis known as anaturaljoin.
PNO
PNO PARTNO
TABLE_H =join(EMP,DEPT: DNO=DNO) TABLE_H ENO ENAME DNO DNAME 100 CARTER 10 PRODUCTION 200 ALBERT 20 SUPPLIES 300 BEENS 30 MARKETING
9. Division ▪ Thedivisionoperationsarethemost difficult operationtocomprehend. ▪ Itisnot assimpleasdivisioninmathematics. ▪ Inrelationalalgebra,itidentifiersrowsinonetablethathaveacertain relationshiptoallrowsinanothertable. PROJ PROJPARTS TABLE_I = PROJPARTS / PROJTABLE_I PNO 1 3 2
1.6.2 RelationalCalculus
Relationalcalculusisnonprocedural.Ithasthesameexpressivepowerasrelationalalgebra, i.e. it isrelationally complete.Itisa formallanguagebasedupona branchof mathematical logiccalled"predicatecalculus".Therearetwoapproaches:TuplerelationalcalculusandDomainrelationalca lculus.Theyarepartoftherelationalmodelfordatabasesandprovideadeclarativewaytospecifydatabase queries.
In this example, we're only looking for the name, and that's B. F = C is a requirement, becauseweneedtofindEnterprisecrewmembersANDtheyare intheStellarCartographyDepartment. Someabbreviationsforlogic
- (p=>q )equivalentto((not p)orq )
- ∀x(f(x))equiv.tonot(∃x(notf(x)))
- ∃x(f(x))equiv.tonot(∀x(notf(x)))
- ∀xεS(f)equiv. to∀x ((x εS)=>f )
- ∃x εS(f)equiv. to∃x((x εS)andf)
2.DATABASEDESIGN
2.1 DataModeling
The Relational model uses relation (table) to represent both entities and relationships amongentities. A relation may be visualized as a table. However table is just one of the way, among many, torepresent arelation.
Typesof DataModels
Flat Model: This may not strictly qualify as a data model. The flat (or table) model consists of asingle, two-dimensional array of data elements, where all members of a given column are assumed tobesimilarvalues,and allmembersofaroware assumed toberelatedtoone another. Fig:Flat Model Hierarchical Model: In this model data is organized into a tree-like structure, implying a singleupward link in each record to describe the nesting, and a sort field to keep the records in a particularorder ineach same-levellist.
Fig:Object-RelationalModel Relational Model: This is a database model based on first-order predicate logic. Its core idea is todescribe a database as a collection of predicates over a finite set of predicate variables, describingconstraintsonthepossiblevalues and combinations ofvalues. Fig:RelationalModel
Entity–RelationshipModel A model is a simplified version of real-life, complex objects. An Entity is a distinct in theorganization that is to be represents in the database. The entity relationship (E-R) model is a verypopular modelingtoolamong manysuch tools availabletoday. TheE-Rmodelprovides1.Anexcellentcommunicationtool2.Asimplegraphicalrepresentation of data. The E-R model uses E-R diagrams (ERD) for graphial representation of thedatabasecomponents. Entity: ▪ Anentityrepresentedbyarectangle.Thenameoftheentitywrittenwithintherectangleandblockletter s.
