Mahar Waqas PHP Developer 0300-8959571

Final Project
CS619 VIVA
Preparation
Notes
We will Also
Have a Project Discussion,
if needed then?
Contact with me.
0300-8959571
Mahar Waqas PHP Developer 0300-8959571

Database Management System or DBMS

Database Management System or DBMS in short refers to the

technology of storing and retrieving users’ data with utmost
efficiency along with appropriate security measures. This
tutorial
explains the basics of DBMS such as its architecture, data
models, data schemas, data independence, E-R model, relation
model, relational database design, and storage and file structure
and much more.

Database Management System: is the set of programs or

system which is used to create and maintain database.
Data: is the collection of raw, facts and figures like college admission
form consists of data.
Database: is organized collection of related data.
A database management system stores data in such a way that
it becomes easier to retrieve, manipulate, and produce
information.

Users
A typical DBMS has users with different rights and
permissions who use it for different purposes. Some users
retrieve data and some back it up. The users of a DBMS can be
broadly categorized as follows −
Mahar Waqas PHP Developer 0300-8959571

• Administrators − Administrators maintain the DBMS

and are responsible for administrating the database. They
are
responsible to look after its usage and by whom it should be
used. They create access profiles for users and apply
limitations to maintain isolation and force security.
Administrators also look after DBMS resources like
system license, required tools, and other software and
hardware related maintenance.

• Designers − Designers are the group of people who

actually work on the designing part of the database. They
keep a
close watch on what data should be kept and in what
format.
They identify and design the whole set of entities, relations,
constraints, and views.

• End Users − End users are those who actually reap the
benefits of having a DBMS. End users can range from
simple viewers who pay attention to the logs or market
rates to sophisticated users such as business analysts.
Mahar Waqas PHP Developer 0300-8959571

Keys
Super Key

Super key is a set of one or more than one keys that can be used
to identify a record uniquely in a table. Example: Primary
key, Unique key, Alternate key are subset of Super Keys.

Candidate Key

A Candidate Key is a set of one or more fields/columns that

can identify a record uniquely in a table. There can be
multiple
Candidate Keys in one table. Each Candidate Key can work as
Primary Key.

Example: In below diagram ID, RollNo and EnrollNo are

Candidate Keys since all these three fields can be work as
Primary Key.

Primary Key

Primary key is a set of one or more fields/columns of a table

that uniquely identify a record in database table. It can not
accept null,
duplicate values. Only one Candidate Key can be Primary
Key.

Alternate key

A Alternate key is a key that can be work as a primary key.

Basically it is a candidate key that currently is not primary
key.
Mahar Waqas PHP Developer 0300-8959571

Example: In below diagram RollNo and EnrollNo becomes

Alternate Keys when we define ID as Primary Key.

Composite/Compound Key

Composite Key is a combination of more than one

fields/columns of a table. It can be a Candidate key,
Primary key.

Unique Key

Uniquekey is a set of one or more fields/columns of a table

that uniquely identify a record in database table. It is like
Primary key but it can accept only one null value and it can not
have duplicate values. For more help refer the article Difference
between primary key and unique key.

Foreign Key

Foreign Key is a field in database table that is Primary key in

another table. It can accept multiple null, duplicate values. For
more help refer the article Difference between primary key
and foreign key.

Example : We can have a DeptID column in the Employee

table which is pointing to DeptID column in a department table
where it a primary key.
Mahar Waqas PHP Developer 0300-8959571

Note: Practically in database, we have only three types of keys

Primary Key, Unique Key and Foreign Key. Other types of
keys are only concepts of RDBMS that we need to know.

SQL Overview

SQL is a programming language for Relational Databases. It is

designed over relational algebra and tuple relational calculus.
SQL comes as a package with all major distributions of
RDBMS.

SQL comprises both data definition and data manipulation

languages. Using the data definition properties of SQL, one
can
design and modify database schema, whereas data
manipulation properties allows SQL to store and retrieve data
from database.

SQL Commands:
The standard SQL commands to interact with relational
databases are CREATE, SELECT, INSERT, UPDATE,
DELETE and
Mahar Waqas PHP Developer 0300-8959571

DROP. These commands can be classified into groups based

on their nature:

DDL - Data Definition Language:

Command Description

CREATE Creates a new table, a view of a table, or other

object in database

ALTER Modifies an existing database object, such as a

table.

DROP Deletes an entire table, a view of a table or other

object in the database.

DML - Data Manipulation Language:

Command Description

SELECT Retrieves certain records from one or more tables

INSERT Creates a record

UPDATE Modifies records

DELETE Deletes records

DCL - Data Control Language:

Mahar Waqas PHP Developer 0300-8959571

Command Description

GRANT Gives a privilege to user

REVOKE Takes back privileges granted from user

Data Definition Language

The DDL section is used for creating database objects, such as
tables. In practice, people often use a GUI for creating tables
and so on, so it is less common to hand-write DDL statements
than it used to be.

SQL uses the following set of commands to define database

schema −

CREATE
Creates new databases, tables and views from RDBMS.

For example −

Create database tutorialspoint;

Create table article;
Create view for_students;
DROP
Drops commands, views, tables, and databases from RDBMS.
Mahar Waqas PHP Developer 0300-8959571

For example−

Drop object_type object_name;

Drop database tutorialspoint;
Drop table article;
Drop view for_students;
ALTER
Modifies database schema.

Alter object_type object_name parameters;

For example−

Alter table article add subject varchar;

This command adds an attribute in the relation article with the
name subject of string type.

Difference between Drop, Truncate and Delete

DELETE

DELETE removes some rows if WHERE clause is used

DROP

Removes a table from the database. Table structures, indexes,

privileges, constraints will also be removed.
Mahar Waqas PHP Developer 0300-8959571

TRUNCATE

It Removes all rows from a table, but the table structures and
its columns, constraints, indexes remains.

Data Manipulation Language

The DML section is used to manipulate the data such as
querying it. While is also common to use a query builder to
create queries, people do still hand-craft DML statements, such
as queries.

SQL is equipped with data manipulation language (DML).

DML modifies the database instance by inserting, updating and
deleting its data. DML is responsible for all froms data
modification in a database. SQL contains the following set of
commands in its DML
section −

• SELECT/FROM/WHERE
• INSERT INTO/VALUES
• UPDATE/SET/WHERE
• DELETE FROM/WHERE
These basic constructs allow database programmers and users
to enter data and information into the database and retrieve
efficiently using a number of filter options.

SELECT/FROM/WHERE
• SELECT − This is one of the fundamental query command of
SQL. It is similar to the projection operation of relational
Mahar Waqas PHP Developer 0300-8959571

algebra. It selects the attributes based on the condition

described by WHERE clause.

• FROM − This clause takes a relation name as an argument

from which attributes are to be selected/projected. In case
more than one relation names are given, this clause
corresponds to Cartesian product.

• WHERE − This clause defines predicate or conditions, which

must match in order to qualify the attributes to be projected.

For example −

Select author_name
From book_author
Where age > 50;
This command will yield the names of authors from the
relation book_authorwhose age is greater than 50.

INSERT INTO/VALUES
This command is used for inserting values into the rows of a
table (relation).

Syntax−
INSERT INTO table (column1 [, column2, column3 ... ]) VALUES
(value1 [, value2, value3 ... ])
Or

INSERT INTO table VALUES (value1, [value2, ... ])

For example −
Mahar Waqas PHP Developer 0300-8959571

INSERT INTO tutorialspoint (Author, Subject) VALUES

("anonymous", "computers");
UPDATE/SET/WHERE
This command is used for updating or modifying the
values of columns in a table
(relation).

Syntax −

UPDATE table_name SET column_name = value [, column_name =

value ...] [WHERE condition]
For example −

UPDATE tutorialspoint SET Author="webmaster" WHERE

Author="anonymous";
DELETE/FROM/WHERE
This command is used for removing one or more rows from a
table (relation).
Syntax −

DELETE FROM table_name [WHERE condition];

For example −

DELETE FROM
tutorialspoints WHERE Author="unknown";
Mahar Waqas PHP Developer 0300-8959571

CRUD Operations

C stand for
create R stand
for read
U stand for update
D stand for delete

Normalization
If a database design is not perfect, it may contain anomalies,
which are like a bad dream for any database administrator.
Managing a database with anomalies is next to impossible.

• Update anomalies − If data items are scattered and are

not linked to each other properly, then it could lead to
strange
situations. For example, when we try to update one data
item having its copies scattered over several places, a few
instances get updated properly while a few others are left
with old values. Such instances leave the database in an
inconsistent state.

• Deletion anomalies − We tried to delete a record, but

parts of it was left undeleted because of unawareness, the
data is also saved somewhere else.

• Insert anomalies − We tried to insert data in a record that

does not exist at all.
Mahar Waqas PHP Developer 0300-8959571

Normalization is a method to remove all these anomalies and

bring the database to a consistent state.

First Normal Form

First Normal Form is defined in the definition of relations
(tables) itself. This rule defines that all the attributes in a
relation must have atomic domains. The values in an atomic
domain are indivisible units.

We re-arrange the relation (table) as below, to convert it to

First Normal Form.

Each attribute must contain only a single value from its

predefined domain.

Second Normal Form

Before we learn about the second normal form, we need to
understand the following −

• Prime attribute − An attribute, which is a part of the

primekey, is known as a prime attribute.
Mahar Waqas PHP Developer 0300-8959571

• Non-prime attribute − An attribute, which is not a part of

the prime-key, is said to be a non-prime attribute.

If we follow second normal form, then every non-prime

attribute should be fully functionally dependent on prime key
attribute.
That is, if X → A holds, then there should not be any proper
subset Y of X, for which Y → A also holds true.

We see here in Student_Project relation that the prime key

attributes are Stu_ID and Proj_ID. According to the rule, non-
key
attributes, i.e. Stu_Name and Proj_Name must be dependent
upon
both and not on any of the prime key attribute individually. But
we find that Stu_Name can be identified by Stu_ID and
Proj_Name can be identified by Proj_ID independently. This is
calledpartial dependency, which is not allowed in Second
Normal Form.
Mahar Waqas PHP Developer 0300-8959571

We broke the relation in two as depicted in the above picture.

So there exists no partial dependency.

Third Normal Form

For a relation to be in Third Normal Form, it must be in
Second
Normal form and the following must satisfy −

• No non-prime attribute is transitively dependent on

prime key attribute.
• For any non-trivial functional dependency, X →
A, then either − o X is a superkey or, o A
is prime attribute.

We find that in the above Student_detail relation, Stu_ID is the

key and only prime key attribute. We find that City can be
Mahar Waqas PHP Developer 0300-8959571

identified by Stu_ID as well as Zip itself. Neither Zip is a

superkey nor is City a prime attribute. Additionally, Stu_ID →
Zip → City, so there exists transitive dependency.
To bring this relation into third normal form, we break the
relation
into two relations as follows −

Anomalies

Anomalies are inconvenient or error-prone situation arising

when we process the tables. There are three types of
anomalies:

Update Anomalies
Delete Anomalies
Insert Anomalies
Mahar Waqas PHP Developer 0300-8959571

Insert Anomalies
An Insert Anomaly occurs when certain attributes cannot be
inserted into the database without the presence of other
attributes.
For example this is the converse of delete anomaly - we can't
add a new course unless we have at least one student enrolled
on the course.

StudentNum CourseNum Student Address Course

Name
S21 9201 Jones Edinburgh Accounts
S21 9267 Jones Edinburgh Accounts
S24 9267 Smith Glasgow physics
S30 9201 Richards Manchester Computing
S30 9322 Richards Manchester Maths

Delete Anomalies

A Delete Anomaly exists when certain attributes are lost

because of the deletion of other attributes. For example,
consider what
happens if Student S30 is the last student to leave the course -
All information about the course is lost.
StudentNum CourseNum Student Address Course
Name
S21 9201 Jones Edinburgh Accounts
Mahar Waqas PHP Developer 0300-8959571

S21 9267 Jones Edinburgh Accounts

S24 9267 Smith Glasgow physics
S30 9201 Richards Manchester Computing
S30 9322 Richards Manchester Maths

Update Anomalies

An Update Anomaly exists when one or more instances of

duplicated data is updated, but not all. For example, consider
Jones moving address - you need to update all instances of
Jones's address.
StudentNum CourseNum Student Address Course
Name
S21 9201 Jones Edinburgh Accounts
S21 9267 Jones Edinburgh Accounts
S24 9267 Smith Glasgow physics
S30 9201 Richards Manchester Computing
S30 9322 Richards Manchester Maths

Data Integrity

Integrity ensures that the data in a database is both accurate

and complete, in other words, that the data makes sense. There
are at
least five different types of integrity that need to be
considered:
Mahar Waqas PHP Developer 0300-8959571

Domain constraints
Entity integrity
Column constraints
User-defined integrity constraints
Referential integrity
The data analysis stage will identify the requirements of these.

Domain Constraints

A domain is defined as the set of all unique values permitted for

an attribute. For example, a domain of Date is the set of all
possible
valid dates, a domain of Integer is all possible whole numbers,
and a domain of day-of-week is Monday, Tuesday ... Sunday.
Entity Integrity

Entity integrity is concerned with ensuring that each row of a

table has a unique and non-null primary key value; this is the
same as
saying that each row in a table represents a single instance of
the entity type modelled by the table. A requirement of E F
Codd in his seminal paper is that a primary key of an entity, or
any part of it, can never take a null value.
Mahar Waqas PHP Developer 0300-8959571

Column Constraints

During the data analysis phase, business rules will identify any
column constraints. For example, a salary cannot be negative; an
employee number must be in the range 1000 - 2000, etc.
UserDefined Integrity Constraints

Business rules may dictate 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.
Referential Integrity

Referential integrity is with the relationships between the tables

of a database, ie that the data of one table does not contradict
the data of another table. Specifically, every foreign key value
in a table must have a matching primary key value in the
related table. This is the most common type of integrity
constraint. This is used to
manage the relationships between primary and foreign keys.
Joins
Join is a combination of a Cartesian product followed by a
selection process. A Join operation pairs two tuples from
different relations, if and only if a given join condition is
satisfied.
Mahar Waqas PHP Developer 0300-8959571

We will briefly describe various join types in the following

sections.

Theta (θ) Join

Theta join combines tuples from different relations provided
they satisfy the theta condition. The join condition is denoted by
the symbol θ.

Notation
R1 θ R2
R1 and R2 are relations having attributes (A1, A2, .., An) and
(B1,
B2,.. ,Bn) such that the attributes don’t have anything in
common, that is R1 ∩ R2 = Φ.

Equijoin
When Theta join uses only equality comparison operator, it is
said to be equijoin. The above example corresponds to equijoin.

Natural Join ( )
Natural join does not use any comparison operator. It does not
concatenate the way a Cartesian product does. We can perform
a
Natural Join only if there is at least one common attribute that
exists between two relations. In addition, the attributes must
have the same name and domain.

Natural join acts on those matching attributes where the values

of attributes in both the relations are same. Outer
Joins
Mahar Waqas PHP Developer 0300-8959571

Theta Join, Equijoin, and Natural Join are called inner joins.
An inner join includes only those tuples with matching
attributes and
the rest are discarded in the resulting relation. Therefore, we
need to use outer joins to include all the tuples from the
participating relations in the resulting relation. There are three
kinds of outer joins − left outer join, right outer join, and full
outer join.

Left Outer Join(R S)

All the tuples from the Left relation, R, are included in the
resulting relation. If there are tuples in R without any matching
tuple in the Right relation S, then the S-attributes of the
resulting relation are made NULL.
Right Outer Join: ( R S)
All the tuples from the Right relation, S, are included in the
resulting relation. If there are tuples in S without any matching
tuple in R, then the R-attributes of resulting relation are made
NULL.

Full Outer Join: ( R S)

All the tuples from both participating relations are included in
the resulting relation. If there are no matching tuples for both
relations, their respective unmatched attributes are made
NULL.

Indexing
Mahar Waqas PHP Developer 0300-8959571

Indexing is a data structure technique to efficiently retrieve

records from the database files based on some attributes on
which the indexing has been done. Indexing in database systems
is similar to what we see in books.

Indexing is defined based on its indexing attributes. Indexing

can
be of the following types −

• Primary Index − Primary index is defined on an ordered

data file. The data file is ordered on a key field. The key
field is generally the primary key of the relation.
• Secondary Index − Secondary index may be generated
from a field which is a candidate key and has a unique
value in every record, or a non-key with duplicate values.

• Clustering Index − Clustering index is defined on an

ordered data file. The data file is ordered on a non-key
field.