Database Administration

Lecture Notes

For Level III & IV COC Trainees

 What is a Database?
A database is a collection of organized data used by the applications/systems of an organization.
A database consists of:
 Data
 Relationships between the data
 Constraints on the data – or restrictions e.g. Quantity must be greater than 0
 A schema – describes all the objects in the database (e.g. a table has columns; a
column has a data-type and a size)
An example of Database includes:
 Police database
 Library Database
 School Registers ……….
So, in other words, a database is a self-describing collection of data elements, and relationships
between those elements.

Types of Database

The various types of databases are:

a) Operational databases d) Distributed databases

b) End user databases e) Personal databases

c) Centralized databases f) Commercial databases

DBA Responsibilities

The primary areas of responsibility (AOR) Database administrator include the

following:
1. Configuration
2. Data Management
3. Documentation
4. Education
5. Maintenance
6. Performance
7. Planning
8. Reporting
9. Security

Complied By: Aklilu /jack/ M.

Fiftyjack@gmail.com Page 1
Database Administration Lecture notes for level – 3 & 4 Trainees 2015

Database Security
Database Security means protection of the: -
Data from malicious attempts to steal (view) or modify data.
Database from unauthorized users.
Data security covers access and use of database objects (such as tables and views) and the
actions that users can have on the objects.

What is a DBMS?

A DBMS (Database Management System) is a collection of interrelated data and a set of

programs to access that data.
A database management system is the software that enables users to define, create, and
maintain the database and also provides controlled access to this database.
Some of the most common database applications are:
MySQL, SQL Server, Clipper, and
PostgreSQL, FileMaker, Oracle, FoxPro.
Microsoft Access, RDBMS, dBASE,

The various functions provided by a DBMS are listed below (parentheses indicate what we will
cover in this course that will address this issue):

Storage & retrieval – can be done independent of internal structures of the db

Catalog – describes all the data items stored in the db, which are accessible to users –
includes data definitions e.g. for a column, what is the data-type and what is the size (we
will look at SQL Server system objects, which store catalog information)
Shared update – to support concurrency i.e. when more than 1 user are updating the
database at the same time (transactions – will be covered in this course)
Recovery – if the db is damaged, need to be able to restore a working copy. A DBMS
provides backup and restore functions. Security – access restricted to authorized users;
users assigned permissions to carry out certain actions (e.g. to update or delete data);
usually password-protected access. Data can also be encrypted for further protection (will
look at how to create logins & users)
Integrity – mechanisms to ensure data integrity and referential integrity. Data types,
formats, check constraints and key constraints all used for this (will learn about data
types & constraints

Integrity Constraint
is a constraint which used to ensure accuracy and consistency of data in a relational database by
prevents the user from entering the duplicate rows into tables.

Types of integrity constraints:

Database Administration Lecture notes for level – 3 & 4 Trainees 2015

Entity integrity

The entity integrity constraint states that no primary key value can be null, and there is no any
duplicate entries in the primary key column.

Referential Integrity

Referential integrity is a feature provided by relational DBMS that prevents users from entering
inconsistent data.

The referential integrity constraint is specified between two tables and is used to maintain the
consistency among tuples in the two tables.

Referential integrity means the foreign key in any referencing table must always refer to a valid
row in the referenced table.

Domain Integrity

The domain integrity states that every element from a table should respect the type and
restrictions of its corresponding attribute. A type can have a variable length which needs to be
respected. Restrictions could be the range of values that the element can have, the default value if
none is provided, and if the element can be NULL.

User Defined Integrity

Business rules may dictate/state that when a specific action occurs further actions should be
triggered. For example, deletion of a record automatically writes that record to an audit table.

Data independence – the manipulation of the data is independent of where the data is
physically stored – in other words, data manipulation works with logical view of the data
and the process that is manipulating the data does not need to know where or how the
data is stored (will be learning how to use SQL)
Utility services – provides ways to import & export data, query the data etc (will be
using tools such as Query Analyzer). Manipulate data.

An Overview of Transactions
Database Administration Lecture notes for level – 3 & 4 Trainees 2015

A transaction is a sequence of operations performed as a single, logical unit of work. In a

DBMS, this is usually a series of data modifications.
For example, a transaction could be a series of database deletes and updates where all must
complete in order to complete a business process.

ACID properties of transactions

In the context of transaction processing, the acronym ACID refers to the four key properties of a
transaction: atomicity, consistency, isolation, and durability.
Atomicity
All changes to data are performed as if they are a single operation. That is, all the
changes are performed, or none of them are.
For example, in an application that transfers funds from one account to another, the
atomicity property ensures that, if a debit is made successfully from one account, the
corresponding credit is made to the other account.

Consistency
Data is in a consistent state when a transaction starts and when it ends.
For example, in an application that transfers funds from one account to another, the
consistency property ensures that the total value of funds in both the accounts is the same
at the start and end of each transaction.

Isolation
The intermediate state of a transaction is invisible to other transactions. As a result,
transactions that run concurrently appear to be serialized.
For example, in an application that transfers funds from one account to another, the
isolation property ensures that another transaction sees the transferred funds in one
account or the other, but not in both, nor in neither.
Durability
After a transaction successfully completes, changes to data persist and are not undone,
even in the event of a system failure.
For example, in an application that transfers funds from one account to another, the
durability property ensures that the changes made to each account will not be reversed.

Database Models
A Database model is a type of data model that determines the logical structure of a database and
fundamentally determines in which manner data can be stored, organized, and manipulated. The
Database Administration Lecture notes for level – 3 & 4 Trainees 2015

most popular example of a database model is the relational model, which uses a table-based
format.
Examples of data models include:
 hierarchical
 network
 relational
In the network model, the data are represented by collections of records and Relationships
between data are represented by links. This structure holds for the hierarchical model as well.
The only difference is that, in the hierarchical model, records are organized as collections of
trees, rather than as arbitrary graphs.

Hierarchical Model
In a hierarchical model, data is organized into a tree-like structure, implying a single parent for
each record. This model allows one to one and one to many relationships.

Network Model
The network model expands upon the hierarchical structure, allowing many-to-many,one to
one and one to many relationships in a tree-like structure that allows multiple parents. It was
most popular before being replaced by the relational model.
The network model is able to represent redundancy in data more efficiently than in the
hierarchical model, and there can be more than one path from an ancestor node to a descendant
Database Administration Lecture notes for level – 3 & 4 Trainees 2015

The Relational Database Model

Relational data model is the primary data model, which is used widely around the world for data
storage and processing. This model is simple and it has all the properties and capabilities
required to process data with storage efficiency. In Relational Database data represented as a set
of related tables (or relations).

Concepts

Tables − In relational data model, relations are saved in the format of Tables. This format stores
the relation among entities. A table has rows and columns, where rows represent records and
columns represent the attributes.
Tuple − A single row of a table, which contains a single record for that relation is called a tuple.
Relation instance − A finite set of tuples in the relational database system represents relation
instance. Relation instances do not have duplicate tuples.
Relation schema − A relation schema describes the relation name (table name), attributes, and
their names.
Relation key − Each row has one or more attributes, known as relation key, which can identify
the row in the relation (table) uniquely.
Attribute domain − every attribute has some pre-defined value scope, known as attribute
domain.
Constraints
Every relation has some conditions that must hold for it to be a valid relation. These conditions
are called Relational Integrity Constraints. There are three main integrity constraints −
Key constraints
Domain constraints
Referential integrity constraints
Database Administration Lecture notes for level – 3 & 4 Trainees 2015

Key Constraints
There must be at least one minimal subset of attributes in the relation, which can identify a tuple
uniquely. This minimal subset of attributes is called key for that relation. If there are more than
one such minimal subsets, these are called candidate keys.
Key constraints force that −
in a relation with a key attribute, no two tuples can have identical values for key attributes.
a key attribute can not have NULL values.
Key constraints are also referred to as Entity Constraints.
Domain Constraints
Attributes have specific values in real-world scenario. For example, age can only be a positive
integer. The same constraints have been tried to employ on the attributes of a relation. Every
attribute is bound to have a specific range of values. For example, age cannot be less than zero
and telephone numbers cannot contain a digit outside 0-9.
Referential integrity Constraints
Referential integrity constraints work on the concept of Foreign Keys. A foreign key is a key
attribute of a relation that can be referred in other relation.
Referential integrity constraint states that if a relation refers to a key attribute of a different or
same relation, then that key element must exist.
Using Entity relationship Modeling
Entity types, entity sets, attributes, and sets
1.1 What is entity in a database?
An Entity is a thing in real-world (an object with a physical existence, e.g. A particular person,
car, house, or employee or an object with a conceptual existence.
It can be roles, events, locations, tangible things or concepts.
Example: A person/an employee, department, payment, book, project, campus,
organization, or business.
Each entity must have its own identity that distinguishes it from every other entity, called unique
identifier.
Example: Each Student has a unique ID that distinguishes one from others.
1.2 In database terms, an entity is a table which is responsible for storing data in the database.
Entity type is the set of all possible values for an entity is called entity type.
Example: - student is an entity type with common attributes to all students such as student_ID,
Name, etc.
An entity set is a set of entities of the same type (e.g., all persons having an account at a bank).
An entity set is the collection of instances represented by (e1, e2, e3, .... en). It is also called an
extension of the entity type.
Example: - a company has many employees, and these employees are defined as entities (e1,
e2, e3 ...) and all these entities having the same attributes which are defined under ENTITY
TYPE employee, and set{e1, e2, .....} is called entity set.

In E-R Diagram, an entity type is represented by a rectangle, and the name is indicated in
capital letters.
Database Administration Lecture notes for level – 3 & 4 Trainees 2015

Example:
DEPARTMENT EMPLOYEE
Index on emp_id
Attributes …
… Index on
An attribute is a property that describes an entity. AMD15433F emp_id
… data type.
Each attributes has a particular value based on the defined
… is called attribute domain.
The set of all possible (allowable) values of an attribute
KFJ64308F
In E-R Diagram, attributes are named with an initial capital letter followed by Index on letters.
lowercase
Emp_id emp_id
PMA42628M …
…
An attribute can be represented by an ellipse (oval) shape with a line connected …it to the
associated entity. AMD15433F AMD15433F
Example: … …
KFJ64308F …
… KFJ64308F
…
It can also be represented by listing them within the entity rectangle, under the… entity name
(entity type). …
Example: Employee tableEMPLOYEE…
Emp_Id PMA42628M
D15433F Fname
Types of attributes … lname
KFJ64308F
An attribute can be: - simple or composite, single-value … or multi-value, stored or derived
or Null Values. …
Simple (Atomic) vs. Composite attribute
Simple attributes cannot be further divisible. … Index on
Example: SSN, Sex … emp_id
In E-R Diagram, simple attributes can be represented …by an ellipse shape. Example:
PMA42628M …
Composite attributes can be divided into smaller subparts. …
Example: - address (street address, city, state, zip code) AMD15433F
- Name (first name, last name, initial name) …
In E-R Diagram, composite attributes can be represented by: …
KFJ64308F
The value of composite attribute is the concatenation of the values of its constituent simple
attributes. …
Single-valued vs. Multi-valued attributes …
- Most attributes have a single value for a particular entity. …
PMA42628M
- In some cases, an attribute can have a set of value for the same entity, called multi-
valued.
Example: color attribute for a car, college degree for a person.
In E-R Diagram, Multi-valued attributes can be represented by double ellipse (oval) shape.
Stored vs. derived attributes.
In some cases, some attributes have a reference (derived from other attributes’ value).
Example: - The Age attribute is derived from the Birth-Date attribute.
- So, the Age attribute is derived attribute, and the Birth-Date attribute is stored
attribute.
Database Administration Lecture notes for level – 3 & 4 Trainees 2015

In E-R Diagram, Derived attributes can be represented by dotted or dashed ellipse.

Key attributes

Identifiers (more commonly called keys or key attributes) uniquely identify each instance of an
entity, called candidate key. If such an attribute doesn't exist naturally, a new attribute is defined
for that purpose.
Example: an ID number of STUDENT OR EMPLOYEE Entity type.
In some cases, more than one attribute is required to identify a unique entity, called composite
candidate key.
In E-R Diagram, key attributes (identifiers) can be represented by ellipse shape with underline.

When selecting identifiers, you should follow these guidelines:

Choose a candidate key that has values that do not change over the life of each instance of the
entity type.
Choose a candidate key that is guaranteed to always have valid values and not be null for each
instance.
If a candidate key is a composite of two or more attributes, consider creating a new key with a
single value.
Types of Keys in Relational Database Model
- Super key - Candidate key
-Primary key - Alternate key
- Foreign key
Super Key is an attribute or a composite attribute which functionally determines all of the
entity’s attributes.
Since, a super key may contain additional columns that are not necessary for unique
identification; we’re interested in identifying super keys that contain only the minimum number
of columns necessary for unique identification.
Candidate Key is a super key whose values are not repeated in the table records.
It is a super key that contains only the minimum number of columns necessary for unique
identification.
It is possible to have more than one choice of candidate key in a particular table. In that case, the
selection of the primary key would be driven by the designer’s choice or by end user
requirements.
Primary Key is a candidate key which doesn’t have repeated nor NULL values in the table.
- A primary key in any table is both a super key as well as a candidate key.
It is an attribute or a set of attributes that uniquely identify a specific instance of an entity.
Every entity in the data model must have a primary key whose values uniquely identify instances
of the entity.
Alternate key: An alternate key is a key associated with one or more columns whose values
uniquely identify every row in the table, but which is not the primary key. For example, where
the primary key for a table may be the employee id, the alternate key might combine the first,
middle, and last names of the employee
.Foreign Key is a table’s primary key attribute which is repeated in another related table (having
related data) to maintain the required data relationship.
Database Administration Lecture notes for level – 3 & 4 Trainees 2015

Relationship type, relationship sets, roles and structural constraints

A relationship is an association that exist b/n two or more participating entities.

The function of relationship is to share data b/n entities.
In E-R Diagram, a relationship type is represented by a diamond shape
with the relationship verb in it.

Some example of relationships is: - a DEPARTEMENT has EMPLOYEES

- an EMPLOYEE manages PROJECTS
- EMPLOYEES works_on PROJECTS
- EMPLOYEES process PAYMENTS
- EMPLOYEES works_for DEPARTEMENT

There are three types of relationships b/n entities:

One-to-one: - one instance of an entity (A) is associated with one other instance of another
entity (B).
Example: - the manages relationship b/n department and employee
- President to country, husband to wife, people to religion
One-to-many: - one instance of an entity (A) is associated with zero, one or many instances of
another entity (B), but for one instance of entity (B) there is only one instance of entity (A).
Example: - the has relationship b/n department and employee
- Country to people, mother to children, department to students, publisher to Titles
Many-to-many: - one instance of an entity) A) is associated with zero, one or many instances
of another entity (B), and one instance of entity (B) is associated with zero, one or many
instances of entity (A).
Example: - the works_for relationship b/n employee and project
- Students to course, employee to projects, people to language, Titles to Authors

Relationship Types

A Relationship Type defines a relationship set among entities of certain entity types.
Relationship sets
A Relationship Set is a collection of relationships all belonging to one relationship type.
Example: EMPLOYEE works_for DEPARTEMENT

Roles and structural constraints

Each entity type in a relationship plays a particular role. The role name specifies the role that a
participating entity type plays in the relationship and explains what the relationship means.
Example: in the relationship between Employee and Department (in the WORKS_FOR
relationship type),
EMPLOYEE entity type plays the role of employee or worker and DEPARTMENT
entity type plays the role of department or employer.
Database Administration Lecture notes for level – 3 & 4 Trainees 2015

In most cases, the role names do not have to be specified, but in cases where the same entity
participates more than once in a relationship type in different roles.
For example, each employee has a supervisor, we need to include role name “Supervise” and
“supervisor”, both of them are employees. Since the employee entity type participates twice in
the relationship, once as an employee and once as a supervisor, we can specify two roles,
employee and supervisor

Employee

Supervisee
Supervisor Supervi
sion

Documenting Database Design

Database design documentation is detailed information about a database’s design
specifications, its internal workings, and its functionality.
The three phases in Database design documentation should include:

Conceptual data model

Conceptual database design is the process of constructing a model of the information
used in an enterprise.
It is a High level description of the data and the constraint.
It includes:
Entities and Attributes
Entity Types, Key Attributes, and Value Sets
Relationships and Relationship Types
Weak Entity Types
Roles and Attributes in Relationship Types
ER Model Notations, Naming Conventions, Design Issues.
Logical data model is a fully attributed entity relationship diagram (ERD), which shows
each entity, its relationship to other entitles, normalized structures, and specifies the
applicable business rules.
Physical data model is the final representation of the relational database design
structures (physical implementation, access method and storage requirements).Physical
design is the process of producing a description of the implementation of the database on
secondary storage. It defines specific storage or access methods used by a database.

I.e.: - It describes the storage structures and access methods used to achieve efficient access to
the data.

Database Design Lifecycles

Database Administration Lecture notes for level – 3 & 4 Trainees 2015

The main stage of the database application design lifecycle includes:

1. Planning 5. Implementation
2. Data collection 6. Deployment
3. Data Analysis 7. Operation & Support or
4. Design Maintenance
Degree of Relationship Type
The degree of a relationship type is the number of participating entity types. Meaning if the
relationship is between two entity types (for Example, Employee and Department), then the
relationship is binary, or has a degree of two.
If the relationship is between three participating entities, it has a degree of three, and therefore is
a ternary relationship.

Example: The relationship b/n Supplier, Project and Part. Each part is supplied by a unique
supplier, and is used for a given project within a company; the relationship “Supplies” is a

Supplie
Supplier Project
s

Part
ternary (degree of three) between Suppliers, Project
And Part
Constraints on Relationship Types
Relationship types have certain constraints that limit the possible combination of entities that
may participate in relationship.
An example of a constraint on relationship is that if we have the entities Doctor and Patient, the
organization may have a rule that a patient cannot be seen by more than one doctor. This
constraint needs to be described in the schema.
There are two main types of relationship constraints: cardinality ratio, and participation.

Cardinality for Binary Relationship

Binary relationships are relationships between exactly two entities.

The cardinality ratio specifies the maximum number of relationship instances that an entity can
participate in.
The possible cardinality ratios for binary relationship types are: 1:1, 1: N, N: 1, M: N.
Cardinality ratios are shown on ER diagrams by displaying 1, M and N on the diamonds.

Participation Constraints

The participation constraint specifies whether the existence of an entity depends on its being
related to another entity via the relationship type.
Database Administration Lecture notes for level – 3 & 4 Trainees 2015

The constraint specifies the minimum number of relationship instances that each entity can
participate in.
There are two types of participation constraints:
Total :
If an entity can exist, only if it participates in at least one relationship instance, then that is called
total participation.
Example: in the relationship between Employee and Department, if company policy states that
every employee must work for a department, then an employee can exist only if he/she
Participates in at lest one relationship instance (i.e. an employee can’t exist without a
department).
It is also sometimes called an existence dependency.
Total participation is represented by a double line.
Partial:
If only a part of the set of entities participate in a relationship, then it is called partial
participation.
Example: in the relationship between Employee and Department, if company policy states that
every employee will not be a manager of a department, then the participation of an employee in
the “Manages” relationship is partial.
Partial participation is represented by a single line.
Attributes of Relationship Types
Relationships can have attributes similar to entity types.
Example: - in the relationship Works_On, between the Employee entity and the Department
entity, we would like to keep track of the number of hours an employee works on a project.
Therefore we can include Number of Hours as an attribute of the relationship.
- In the “manages” relationship between employee and department, we can add Start Date
as an attribute of the Manages relationship.
For some relationships (1:1, or 1: N), the attribute can be placed on one of the participating entity
types.
Example: the “Manages” relationship is 1:1, so StartDate can either be migrated to
Employee or Department.
For some relationships (1:1, or 1: N), the attribute can be placed on one of the participating entity
types.
Example: the “works_for” relationship is N: 1, so since can be migrated to Employee entity
type.
Weak Entity Types
Entity types that do not have key attributes (strong key) are called weak entity types.
The relationship that relates the identifying entity type (strong entity type) with the weak entity
type is called an identifying relationship.
A weak entity type always has a total participation constraint with respect to the identifying
relationship, because a weak entity cannot exist without its owner.
A weak entity type usually has a partial key, which is the set of attributes that can uniquely
identify weak entities.
Example: let’s assume in a library database, we have an entity type Book. For each book, we
keep track of the author, and title. The library may own several copies of the same book, and for
each copy, it keeps track of the copy number (a different copy number for each copy of a given
book) and price of each copy.

Has
 Database Administration Lecture notes for level – 3 & 4 Trainees 2015

Book Copy

The copy number is only unique for each book, meaning Book 123 may have copy 1, copy 2,
copy 3, and book 456 may also have copy 1, copy 2 and copy 3. The copy number cannot be
considered unique for each copy.
Therefore, the Copy entity does not have a key attribute; it is considered a weak entity type
where as the book entity is the identifying entity.
A copy cannot exist without the identifying entity (Book)
The Copy entity type has a total participation constraint with respect to the identifying
relationship.
The partial key of the Copy entity is Copy Number; for each owner entity Book, the Copy
Number uniquely identifies the copy for each book.

Normalization
Normalization is a process that involves dividing large tables (complex data structures) into
smaller (simple and stable data structures) that do not accept redundant values and defining
relationships between them.
Database normalization is the process of organizing the fields and tables of a relational
database to minimize redundancy and to avoid update anomalies (insert, modify, and delete
anomalies).
If an attribute of a table is multi-valued, it must be normalized.
Normalization is a process used to convert complex data structures into simple, stable data
structures that do not have data redundancies
A relation can be normalized to a specific form to prevent possible occurrence of update
anomalies.
Four most commonly used normal forms are: - First (1NF)
- Second (2NF)
- Third (3NF) normal forms, and
- Boyce–Codd normal form (BCNF).
1st Normal Form (1NF):
A table is in 1NF if: - There are no duplicated rows in the table.
- Each cell is single-valued.
- Entries in a column (attribute, field) are of the same kind.
2nd Normal Form (2NF):
A table is in 2NF if: - it is in 1NF
- All non-key attributes are dependent on the entire key (has no partial
dependencies).
3rd Normal Form (3NF):
A table is in 3NF if: - It is in 2NF
- If it has no transitive dependencies.
- There are no functional dependencies between two or more non-primary-
key attributes.
Functional dependency
One goal of schema design is to minimize the storage space that the base relations (files) occupy.
Database Administration Lecture notes for level – 3 & 4 Trainees 2015

Another serious problem with redundancy is the problem of update anomalies. These can be
classified into:
 Insertion Anomalies
 Deletion Anomalies
 Modification/update Anomalies

Update anomalies:

INSERT: To INSERT new records, first you must check if there is referential constraint.

DELETE: Before try to DELETE a record from a table, you must check whether it is referred by
another table field(s) or not.

UPDATE: If you want to change the value of an attribute, you must check whether it is referred
by another table field(s) or not.

Semantics of the Attributes.

Attributes in a relation must have real-world meaning.

"Semantics" refers to interpretation of attribute values in a tuple.

A functional dependency (FD) is a constraint between two sets of attributes in a table from a
table in the database.
A functional dependency occurs when one attribute in a table uniquely determines
another attribute. This can be written as: A  B ("B is functionally dependent upon A").
If a particular value of one attribute (A) in a table uniquely determines the value of another
attribute (B) in the same table, then there is a functional dependency between attributes A and B.
Examples: In a table employee’s characteristic including employee ID and name, it can be said
that name is functionally dependent upon ID because an employee's name can be uniquely
determined from their ID. However, the reverse statement is not true because more than one
employee can have the same name but different ID.
Full functional dependency
A full functional dependency occurs when you already meet the requirements for a functional
dependency and the set of attributes on the left side of the functional dependency statement
cannot be reduced any farther.
Example: {SSN, age} name is a functional dependency, but it is not a full functional
dependency because you can remove age from the left side of the statement without impacting
the dependency relationship.
Transitive dependency.
Transitivity dependency is a functional dependency between non-primary-key.
I.e.: It occurs when there is an indirect relationship that causes a functional dependency.
Example: AC is a transitive dependency when “A  B” and “B  C” are true.

Introduction to SQL
Database Administration Lecture notes for level – 3 & 4 Trainees 2015

SQL stands for Structured Query Language. SQL is used to communicate with a
database. According to ANSI (American National Standards Institute), it is the standard
language for relational database management systems.

Basic SQL Command Languages

DDL
Data Definition Language (DDL) statements are used to define the database structure or schema.
Some examples:
CREATE - to create objects in the database
ALTER - alters the structure of the database
DROP - delete objects from the database
TRUNCATE - remove all records from a table, including all spaces allocated for the
records are removed
COMMENT - add comments to the data dictionary.

Data Dictionary
A Data dictionary is a collection of descriptions of the data or items in a data model for the
benefit of programmers and others who need to refer to them. I.e.: It is a file that defines the
basic organization of a database.

A data dictionary contains:

 The definitions of all schema objects in the database (tables, views, indexes, clusters,
procedures, functions, triggers, and so on).
 Name of the tables, names of attributes of each table, Data type of each field, Length of
each field.
 How much space has been allocated for, and is currently used by the schema objects.
 Default values for columns
 Logical structure of database
 Integrity constraint information
 Privileges and roles each user has been granted
 Auditing information, such as who has accessed or updated various schema objects

 Constraints that apply to each field, if any

 Brief description of the expected data for each field (Constraints on data i.e. range of
values permitted).
 Access Authorization - the Description of database users, their responsibilities and their
access rights.

RENAME - rename an object

DML
Data Manipulation Language (DML) statements are used for managing data within schema
objects. Some examples:
SELECT - retrieve data from the a database
INSERT - insert data into a table
Database Administration Lecture notes for level – 3 & 4 Trainees 2015

UPDATE - updates existing data within a table

DELETE - deletes all records from a table, the space for the records remain.

DCL
Data Control Language (DCL) statements. Data Control Language (DCL) component of the SQL
language is used to create privileges to allow users access to, and manipulation of, the database.
There are two main commands:
GRANT : to grant a privilege to a user
REVOKE:-to revoke (remove) a privilege from a user. Some examples:
GRANT - gives user's access privileges to database.
REVOKE - withdraw access privileges given with the GRANT command.

TCL
Transaction Control (TCL) statements are used to manage the changes made by DML
statements. It allows statements to be grouped together into logical transactions.
COMMIT - save work done
SAVEPOINT - identify a point in a transaction to which you can later roll back
ROLLBACK - restore database to original since the last COMMIT
SET TRANSACTION - Change transaction options like isolation level and what rollback
segment to use.

SQL Data types

SQLdata type is an attribute that specifies type of data of any object. Each column, variable and
expression has related data type in SQL.
Numeric Date and time String data Binary Data type Misc Data Type
Data type data type type
Int Date Char Binary Table
Smallint Datetime Varchar Varbinary Timestamp
Money Smalldatetime varchar(max) varbinary(max) Xml
Smallmoney Time text Image Cursor
Numeric Nchar Uniqueidentifer
Decimal Nvarchar
Bigint nvarchar(max)
Tinyint Ntext
Float
Real
Database Administration Lecture notes for level – 3 & 4 Trainees 2015

SQL Constraints

SQL constraints are used to specify rules for the data in a table.

If there is any violation between the constraint and the data action, the action is aborted by the
constraint.

Constraints can be specified when the table is created (inside the CREATE TABLE statement) or
after the table is created (inside the ALTER TABLE statement).

In SQL, we have the following constraints:

NOT NULL - Indicates that a column cannot store NULL value
UNIQUE - Ensures that each row for a column must have a unique value
PRIMARY KEY - A combination of a NOT NULL and UNIQUE. Ensures that a
column (or combination of two or more columns) have an unique identity which helps to
find a particular record in a table more easily and quickly
FOREIGN KEY - Ensure the referential integrity of the data in one table to match
values in another table
CHECK - Ensures that the value in a column meets a specific condition
DEFAULT - Specifies a default value when specified none for this column.

Flow chart
A flowchart is a picture of the separate steps of a process in sequential order.
A flowchart is a visual representation of the sequence of steps and decisions needed to perform a
process. Each step in the sequence is noted within a diagram shape. Steps are linked by
connecting lines and directional arrows. This allows anyone to view the flowchart and logically
follow the process from beginning to end.

Basic symbols of flow chart

Symbols Name/Description/
Terminator
(Terminal Point, start or Stop Oval)

Data
(I/O)

Decision

Flow Line
(Arrow, Connector)
Database Administration Lecture notes for level – 3 & 4 Trainees 2015

Process

Different SQL Operators

Inner JOIN: Return rows when there is at least one match in both tables
LEFT JOIN: Return all rows from the left table, even if there are no matches in the right
table
RIGHT JOIN: Return all rows from the right table, even if there are no matches in the
left table
FULL JOIN: Return rows when there is a match in one of the tables.
The IN operator allows you to specify multiple values in a WHERE clause.
The LIKE operator is used in a WHERE clause to search for a specified pattern in a
column.
The LIKE operator is used to search for a specified pattern in a column.
The ORDER BY keyword is used to sort the result-set.
The ORDER BY keyword sorts the records in ascending order by default.
The DISTINCT keyword can be used to return only distinct (different) values.
The AND operator displays a record if both the first condition and the second condition
are true.
The OR operator displays a record if either the first condition or the second condition is
true.

Deny Vs Revoke
GRANT, REVOKE, and DENY commands are T-SQL commands in SQL Server for managing
permission. Although we know the correct usage of them, REVOKE and DENY have confused
most of us, what exactly SQL Server does for these two commands. Here is a brief explanation
on them;
Database Administration Lecture notes for level – 3 & 4 Trainees 2015

GRANT – Let users to perform an operation on objects.

REVOKE – Removes assigned GRANT permissions on an object for one or more
operations. Main thing you have remembered is, this does not restrict user accessing the
object completely. If user is in a role that has permission on the object for the operation,
user will be able to perform the operation.
DENY – Denies permission to the object for an operation. Once it set, since it takes
precedence over all other GRANT permissions, user will not be able to perform the
operation against the object.

SQL Functions
Function is a database object in SQL Server. Basically it is a set of SQL statements that accepts
only input parameters, perform actions and return the result. Function can return only single
value or a table. We can’t use function to Insert, Update, and Delete records in the database
table.
There are 2 basic Types of SQL functions:
1. Aggregate Function
2. Scalar functions
Aggregate functions operate on a SQL scalar functions return a single value,
collection of values and return a single based on the input value.
value. SQL aggregate functions return a Useful scalar functions:
single value, calculated from values in a UCASE() - Converts a field to upper case
column. LCASE() - Converts a field to lower case
MID() - Extract characters from a text field
Useful aggregate functions: LEN() - Returns the length of a text field
AVG() - Returns the average value ROUND() - Rounds a numeric field to the
COUNT() - Returns the number of rows number of decimals specified
FIRST() - Returns the first value NOW() - Returns the current system date
LAST() - Returns the last value and time
MAX() - Returns the largest value FORMAT() - Formats how a field is to be
MIN() - Returns the smallest value displayed.
SUM() - Returns the sum

Database Back up

Backup is the activity of copying files or databases so that they will be preserved in case of
equipment failure or other catastrophe. You should always have proper backup plan in place to
protect your database from failures.

Logical backup

Logical backup means backing up the individual database objects such as tables, views,
indexes. Using the utility called EXPORT, provided by Oracle. The objects exported in this way
can be imported into either same database or into any other database. The backed-up copy of
Database Administration Lecture notes for level – 3 & 4 Trainees 2015

information is stored in a dump file, and this file can be read using another utility called
IMPORT. There is no other way you can use this file.
The logical backup technique uses the IMPORT/EXPORT utilities to create the backup copy of
the database. A logical backup will backup the contents of the database.
It extracts every record in the database and stores it in a different format. Thus, it is not an exact
replica of the database file. Logical backups reclaim space occupied by deleted records, thereby
reducing database size. Logical backup will take more time compared to physical backup.
Hence, the restore process will need more time.

Logical backup will require less space for backup storage, while the physical backup will need
almost similar to production database size. In logical backup, you dont take the copies of any
physical things, you only extract the data from the datafiles into dump files. (Ex: using export).

Physical backup
This is an operating system backup. It saves the database data files onto tape or some other
storage media. This type of backup is often used to recover the database to the point of failure.
Physical backups rely on the Operating System to make a copy of the physical files like data
files, log files, control files that comprise the database. a physical backup creates an exact replica
of the database during backup process. Physical backup is to copy for backing up all the physical
files that belongs to database. (Like datafiles, control files, log files, executables etc).

SQL Server has four basic & common types of backups:

1. Full Database backup – Complete database backup at the time of backup.
2. Transaction Log backup – Backups up the transaction log file.
3. Differential backup– Backs up the parts of the database that is changed since the last Full
Database backup.
4. File and file group backup – Backs up the database files that you specify in the FILE or
FILEGROUP option.
In SQL Server either you can mention the backup path or you can create a backup
device use the device name during the backup.
You can either use T-SQL or SQL Server Management Studio to backup SQL
Server Database.
Recovery is the process of using the backup media to replace uncommitted,
inconsistent, or lost data.
The reasons for data loss can be divided into the following groups:
 Program errors
 Administrator (human) errors
 Computer failures (system crash)
 Disk failures
 Catastrophes (fire, flood, earthquake) or theft

Business Rule
 A business rule is statements that put some form of constraint on a specific aspect of the
database, such as the elements within a field specification for a particular field or the
 Database Administration Lecture notes for level – 3 & 4 Trainees 2015

characteristics of a given relationship. Business rules, also referred to as informal

predicates, are the total collection of restrictions on, and meaning of, the data in your
database model.
 In short, all limitations and relationships between the complete set of information
elements in your database model is called business rule.
 These are often implemented as 'Constraints' in the Database.
 These Business Rules are important because they define the conditions that the Database
must meet.
 Business rules describe the operations, definitions and constraints that apply to an
organization. Business rules can apply to people, processes, corporate behavior and
computing systems in an organization, and are put in place to help the organization
achieve its goals.
 Business rules describe the business polices that apply to the data stored on a company’s
databases. In other words, business rules reflect how a business perceives its use of data.
Some business rules are especially important to the database designer because they can
be incorporated into the logical schema of the database.
Business-rules constraints fall into two categories: field constraints within tables and
relationship constraints between tables.
1. Logical schema: The overall logical plan of a database; typically a completed ER
diagram.
2. Constraints: Rules a database designer imposes upon certain elements in a database
to preserve data integrity.
3. Data integrity: A term used to describe the quality (in terms of accuracy,
consistency, and validity) of data in a database, in the sense that values required to
enforce data relationships actually exist. Problems with data integrity occur when a
value in one table that’s supposed to relate to a value in another can’t, because the
second value either has been deleted or was never entered.

Business Rules give the proper classification of entities, attributes, relationships, and
constraints. Sources of business rules are managers, policy makers, department
managers, written documentation, procedures, standards, operation manuals, and
interviews with end users.

Example

All employees must have an age between 18 and 60 years

No employees may have illegal Social Security Numbers

Departments ------offers---------Course
Course----------generates---------Class
Professor --------teaches----------Class

Business rules are defined on four levels:

 Domain  Attributes  Entities

 Database Administration Lecture notes for level – 3 & 4 Trainees 2015

 Inter-entity

Other DBMS concepts

Meta Data?
Meta data is ‘data about other data’. Data that serves to provide context or
additional information about other data. For example, information about the title,
subject, author, enhancements, and size of the data file of a document constitute metadata
about that document. It may also describe the conditions under which the data stored in
a database was acquired, its accuracy, date, time, method of compilation and processing,
etc.
Data warehouse?
Data warehousing is defined as a process of centralized data management and
retrieval. Data warehousing represents an ideal vision of maintaining a central
repository of all organizational data. Centralization of data is needed to
maximize user access and analysis.
Data warehouse is data management and data analysis. A data warehouse is a relational
database that is designed for query and analysis rather than for transaction processing. It
usually contains historical data derived from transaction data, but it can include data from
other sources. It separates analysis workload from transaction workload and enables an
organization to consolidate data from several sources.
Data Mart?
The data mart is a subset of the data warehouse that is usually oriented to a
specific business line or team. Data marts are small slices of
the data warehouse. Whereas data warehouses have an enterprise-wide
depth, the information in data marts pertains to a single department.
Data mart is subset of data warehouse. It focuses on only the requirements of users
associated with one department or business function.

Data Mining?
It is a process used by companies to turn raw data into useful information.
Data mining (sometimes called data or knowledge discovery) is the process of
analyzing data from different perspectives and summarizing it into useful
information - information that can be used to increase revenue, cuts costs, or
both. Data mining is the process of finding correlations or patterns among
dozens of fields in large relational databases.

Dead Lock?
 Deadlock means Locking put on data when one transaction is being under operation on a
record. A deadlock occurs when two or more processes are waiting on the same resource
Database Administration Lecture notes for level – 3 & 4 Trainees 2015

and each process is waiting on the other process to complete before moving forward.
Deadlock occurs when one connection is blocked and waiting for a second to complete
its work, and this situation is again with another process as it waiting for first connection
to release the lock. Hence deadlock occurs.