database administration, integrity and performance
DESCRIPTION
Database Administration, Integrity and Performance. Objectives. Define the roles of database administrator Describe the term integrity Define the term security Define the basic performance measures. The Database Administrator. - PowerPoint PPT PresentationTRANSCRIPT
Database Administration, Database Administration, Integrity and PerformanceIntegrity and Performance
ObjectivesObjectives
Define the roles of database Define the roles of database administratoradministrator
Describe the term integrityDescribe the term integrity Define the term securityDefine the term security Define the basic performance Define the basic performance
measuresmeasures
The Database AdministratorThe Database Administrator
Data Administrator(DA) makes the Data Administrator(DA) makes the strategies and policies decisions strategies and policies decisions regarding the data of the enterprise.regarding the data of the enterprise.
Database Administrator(DBA) Database Administrator(DBA) implements those decisions implements those decisions technically.technically.
Functions of DBAFunctions of DBA
Creating the conceptual schema Creating the conceptual schema (logical or conceptual database (logical or conceptual database design)design)
Creating the internal schema Creating the internal schema (physical database design)(physical database design)
Functions of DBAFunctions of DBA
Defining security and integrity Defining security and integrity constraintsconstraints
Defining backup and recovery Defining backup and recovery policiespolicies
Monitoring performance and Monitoring performance and responding to changing responding to changing environments.environments.
SECURITYSECURITY
Protecting the data against Protecting the data against unauthorised disclosure, alteration unauthorised disclosure, alteration or destruction.or destruction.
Security is enforced bySecurity is enforced by
DBMS’s security subsystemDBMS’s security subsystem Checking access requests to Checking access requests to
security constraints stored in system security constraints stored in system catalogcatalog
Giving certain privileges in a relation Giving certain privileges in a relation to certain usersto certain users
What are views for?What are views for?
Automatic Security for hidden dataAutomatic Security for hidden data Short hand capabilityShort hand capability Same data to be seen by different users in Same data to be seen by different users in
a different way at the same timea different way at the same time May provide logical data independenceMay provide logical data independence
• immunity of users and user programs to immunity of users and user programs to changes in the logical structure of a databasechanges in the logical structure of a database
• GrowthGrowth– new attributes may be added new attributes may be added – new relations may be added new relations may be added
SQL FACILITIES FOR VIEWSSQL FACILITIES FOR VIEWS
CREATE VIEW < view name> AS <table expression>CREATE VIEW < view name> AS <table expression>
[ WITH [<qualifier>] CHECK OPTION ][ WITH [<qualifier>] CHECK OPTION ]
With check option means that INSERT and With check option means that INSERT and UPDATE on the view will be rejected if UPDATE on the view will be rejected if they violate any integrity constraint they violate any integrity constraint implied by the view defining expressionimplied by the view defining expression
Qualifier is CASCADED { Default } or Qualifier is CASCADED { Default } or LOCALLOCAL
Examples of viewsExamples of views
CREATE VIEW BestsupplierCREATE VIEW Bestsupplier
AS SELECT S.S#, S.STATUS, S.CITY AS SELECT S.S#, S.STATUS, S.CITY
FROM SUPPLIERFROM SUPPLIER
WHERE SUPPLIER.STATUS > 15 WHERE SUPPLIER.STATUS > 15
WITH CHECK OPTIONWITH CHECK OPTION ; ;
Examples of viewsExamples of views
CREATE VIEW GREENPART ASCREATE VIEW GREENPART AS
SELECT PART.P#, PART.NAME,SELECT PART.P#, PART.NAME, PART.WEIGHT AS PART.WEIGHT AS WT, WT,
PART.CITY PART.CITY
FROM PART FROM PART
WHERE PART.Color = ‘Green’WHERE PART.Color = ‘Green’
WITH CHECK OPTION;WITH CHECK OPTION;
Examples of viewsExamples of views
CREATE VIEW HEAVY_GREENPART ASCREATE VIEW HEAVY_GREENPART AS
SELECT GP.P#, GP.NAME, GP.WT, SELECT GP.P#, GP.NAME, GP.WT, GP.CITY GP.CITY
FROM GREENPART AS GPFROM GREENPART AS GP
WHERE GP.WT > 12.0WHERE GP.WT > 12.0
WITH CHECK OPTION;WITH CHECK OPTION;
Views and SecurityViews and Security
GRANT SELECT, UPDATE (NAME,WT),GRANT SELECT, UPDATE (NAME,WT),
DELETE ON DELETE ON GREENPARTGREENPART
TO user1, user2;TO user1, user2; Privileges can be USAGE, SELECT, Privileges can be USAGE, SELECT,
INSERT, UPDATE, DELETE and INSERT, UPDATE, DELETE and REFERENCESREFERENCES
Can be provided WITH GRANT OPTIONCan be provided WITH GRANT OPTION
REVOKE REVOKE
REVOKE DELETE ON REVOKE DELETE ON GREENPARTGREENPART FROM FROM user1 RESTRICTuser1 RESTRICT
Revoke can be done for GRANT OPTION Revoke can be done for GRANT OPTION FOR alsoFOR also
RESTRICT is needed if a privilege is RESTRICT is needed if a privilege is granted furthergranted further
What is Integrity?What is Integrity?
Accuracy or correctness of data in a Accuracy or correctness of data in a databasedatabase
Mostly RDBMS provide two kind of Mostly RDBMS provide two kind of Integrity support:Integrity support:• Declarative Integrity supportDeclarative Integrity support• Procedural Integrity supportProcedural Integrity support
Declarative SupportDeclarative Support
In the form of constraint definitionsIn the form of constraint definitions If a new constraint is declared then the If a new constraint is declared then the
system ensures that current state of system ensures that current state of database satisfies it, otherwise, the new database satisfies it, otherwise, the new constraint is rejectedconstraint is rejected
If a DBMS provides declarative support, If a DBMS provides declarative support, then about 90% of typical database then about 90% of typical database definition would consist of constraints.definition would consist of constraints.
Declarative Support Contd..Declarative Support Contd..
A system providing such support A system providing such support relieve application programmers of relieve application programmers of the burden, thus, increases the burden, thus, increases productivityproductivity
Golden RuleGolden Rule
No update operation must ever No update operation must ever be allowed to leave any relation be allowed to leave any relation in a state that violates its own in a state that violates its own predicate. No update transaction predicate. No update transaction must ever be allowed to leave must ever be allowed to leave database in a state that violates database in a state that violates its own predicate. its own predicate.
State Vs Transition State Vs Transition ConstraintsConstraints
Concerned with the correct state Concerned with the correct state of databaseof database
Constraints on legal transitionsConstraints on legal transitions
• Not married to marriedNot married to married
• No student class should ever No student class should ever deceasedecease
FOREIGN KEYS FOREIGN KEYS .
Let R2 be a relation, then a foreign key in Let R2 be a relation, then a foreign key in R2 is a set of attributes of R2, say FK, R2 is a set of attributes of R2, say FK, such that :such that :
• There exists a relation R1 ( R1 & R2 not There exists a relation R1 ( R1 & R2 not necessarily distinct) with a candidate necessarily distinct) with a candidate key CK; andkey CK; and
• For all times, each value of FK in the For all times, each value of FK in the current value of R2 is identical to the current value of R2 is identical to the value of CK in some tuple in the current value of CK in some tuple in the current value of R1.value of R1.
.
Foreign Key contd ...Foreign Key contd ...
Definition requires that every value of Definition requires that every value of given foreign key appears as a value of given foreign key appears as a value of matching candidate key - Converse is not matching candidate key - Converse is not a requirementa requirement
A foreign key is simple or compositeA foreign key is simple or composite
Each attribute of a given foreign key must Each attribute of a given foreign key must have a same name and type as the have a same name and type as the corresponding component of matching corresponding component of matching candidate keycandidate key
Referential IntegrityReferential Integrity
Foreign key is a ReferenceForeign key is a Reference
Matching candidate key tuple is Matching candidate key tuple is ReferencedReferenced
Referential constraint : A value of foreign Referential constraint : A value of foreign key must match the referenced candidate key must match the referenced candidate keykey
Referencing Relation : Have foreign keyReferencing Relation : Have foreign key
Referenced Relation : Have candidate keyReferenced Relation : Have candidate key
Referential DiagramsReferential Diagrams
.
A given relation can be both referenced A given relation can be both referenced and referencing at the same lineand referencing at the same line
R3 R2 R1R3 R2 R1
R1 & R2 are not necessarily distinct R1 & R2 are not necessarily distinct Example : (Employee, Mgr.) Example : (Employee, Mgr.) Self-referencing relation Self-referencing relation
Foreign to candidate key match is the glue Foreign to candidate key match is the glue that holds database togetherthat holds database together
SQL FacilitiesSQL Facilities
A candidate key definitionA candidate key definition
A foreign key definitionA foreign key definition
A check constraint definitionA check constraint definition
We may assign a name to We may assign a name to constraintconstraint
Candidate KeyCandidate Key
UNIQUE (<column name comma list>)UNIQUE (<column name comma list>) OROR
PRIMARY KEY (<column name comma PRIMARY KEY (<column name comma list>)list>)
At most one Primary key definitionAt most one Primary key definition
Any number of UNIQUE specificationsAny number of UNIQUE specifications
NOT NULL is by defaultNOT NULL is by default
Foreign Key Foreign Key
FOREIGN KEY (< col name comma list>)FOREIGN KEY (< col name comma list>) References <base table name> References <base table name>
[(< column name comma list>)][(< column name comma list>)] [ ON DELETE < referential action>[ ON DELETE < referential action> ON UPDATE<Referential action>]ON UPDATE<Referential action>]
No action{default} or CASCADE or SET No action{default} or CASCADE or SET DEFAULT or SET NULLDEFAULT or SET NULL
Second Comma list, if foreign key Second Comma list, if foreign key references a candidate key that is not references a candidate key that is not primary key.The foreign to candidate key primary key.The foreign to candidate key matching is done on column position ( left matching is done on column position ( left to right)to right)
Example of constraintsExample of constraints
CREATE TABLE CREATE TABLE SupPart ( S# S# NOT NULL,SupPart ( S# S# NOT NULL,
P# P# NOT NULL,P# P# NOT NULL, QTY QTY NOTNULL,QTY QTY NOTNULL,
PRIMARY KEY( S# , P# ) ,PRIMARY KEY( S# , P# ) ,FOREIGN KEY ( S#) REFERENCES SUPFOREIGN KEY ( S#) REFERENCES SUP
ON DELETE CASCADE,ON DELETE CASCADE, ON UPDATE CASCADE,ON UPDATE CASCADE,
FOREIGN KEY ( P#) REFERENCES PARTFOREIGN KEY ( P#) REFERENCES PART ON DELETE CASCADEON DELETE CASCADE ON UPDATE CASCADE,ON UPDATE CASCADE,
CHECK ( QTY > 0 and QTY < 5000) );CHECK ( QTY > 0 and QTY < 5000) );
TRIGGERED PROCEDURESTRIGGERED PROCEDURES
Procedures invoked automatically on Procedures invoked automatically on occurrence of a specified exception occurrence of a specified exception or a specified interval of timeor a specified interval of time
CASCADE referential action is a CASCADE referential action is a simple example of a triggered simple example of a triggered procedure-declaratively specifiedprocedure-declaratively specified
AssertionsAssertions
General constraintsGeneral constraintsCerate ASSERTION < constraints name >Cerate ASSERTION < constraints name > check ( < conditional expression >);check ( < conditional expression >);DROP ASSERTIONS <Constraint name>;DROP ASSERTIONS <Constraint name>; Assertions are always satisfied by database.Assertions are always satisfied by database. On creation validity of an assertion is On creation validity of an assertion is
checked checked Any change in database may result in Any change in database may result in
checking by an creation =>High overheadschecking by an creation =>High overheads
ExampleExample
The sum of all loans of a branch should be The sum of all loans of a branch should be less than total account balances less than total account balances
Create assertion sum-constraintCreate assertion sum-constraintcheck ( not exists (select * from branchcheck ( not exists (select * from branch where (select sum (amount) from loan where (select sum (amount) from loan where loan.branch-name =where loan.branch-name =
branch.branch-branch.branch-name )name )
>=(select sum (amount) from account>=(select sum (amount) from account where loan .branch-name = where loan .branch-name =
branch.branch-name)))branch.branch-name)))
Deferred CheckingDeferred Checking
SQL constraints can be:SQL constraints can be:• DEFERABLE (Initially Immediate OR initially DEFERABLE (Initially Immediate OR initially
deferrable )deferrable )• NOT DEFERABLE NOT DEFERABLE
SET CONSTRAINT <constraint name> SET CONSTRAINT <constraint name> <option>; statement switches on or off <option>; statement switches on or off the deferrable constraintthe deferrable constraint
Option is IMMEDIATE or DEFERRED Option is IMMEDIATE or DEFERRED
Performance Tuning involvesPerformance Tuning involves
Tuning of hardware resourcesTuning of hardware resources Reducing I/O operationsReducing I/O operations
Reducing Disk AccessesReducing Disk Accesses
Indexing is one major technique Indexing is one major technique used to reduce disk accessesused to reduce disk accesses
Ordered indicesOrdered indices
Based on based on sorted ordering Based on based on sorted ordering of of valuesvalues
Index involve Index involve • Access timeAccess time• Insertion timeInsertion time• Deletion timeDeletion time• Space overheadsSpace overheads
Ordered indicesOrdered indices
Index Structure is associated with a Index Structure is associated with a 'Search Key' 'Search Key' • Primary index- on a sequentially sorted Primary index- on a sequentially sorted
file. (index on primary key- not correct)file. (index on primary key- not correct)• Indices whose search key specifies an Indices whose search key specifies an
order different from the sequential order order different from the sequential order of the file are called secondary or non of the file are called secondary or non clustering indices.clustering indices.
Dense IndexDense Index
An index record/ entry appears for An index record/ entry appears for every search key 'value' in the file & every search key 'value' in the file & a pointer to first data record with that a pointer to first data record with that search key valuesearch key value
Sparse IndexSparse Index
Entry only for some of the values and Entry only for some of the values and a pointer to first value of that type.a pointer to first value of that type.• Take less spaceTake less space• A sparse index with one index entry per A sparse index with one index entry per
block may be a good choice.block may be a good choice.
Multilevel IndicesMultilevel Indices
Are useful when size of index is very Are useful when size of index is very largelarge
Secondary IndicesSecondary Indices
It contains pointers to all recordsIt contains pointers to all records It should not be sparseIt should not be sparse Improve performanceImprove performance Overheads on modification of Overheads on modification of
databasesdatabases Tradeoff : Frequency of Queries Tradeoff : Frequency of Queries
versus Modificationsversus Modifications
B TreeB Tree
Is one of the major method of Is one of the major method of implementing indeximplementing index
B tree adds performance overheads for B tree adds performance overheads for insertion and deletion and adds apace insertion and deletion and adds apace overheads.overheads.
This performance overhead is acceptable This performance overhead is acceptable even for files with high frequency of even for files with high frequency of modification- no cost of reorganization modification- no cost of reorganization