Badesa Tvet Collage

Database Model

A Database model defines the logical design of data. The model describes the relationships
between different parts of the data. In history of database design, three models have been in use.

 Hierarchical Model
 Network Model
 Relational Model

Hierarchical Model

In this model each entity has only one parent but can have several children . At the top of
hierarchy there is only one entity which is called Root.

Network Model

In the network model, entities are organised in a graph,in which some entities can be accessed
through sveral path

Set by:- Haile.D

Badesa Tvet Collage

Relational Model

In this model, data is organised in two-dimesional tables called relations. The tables or relation
are related to each other.

Codd's Rule

E.F Codd was a Computer Scientist who invented Relational model for Database management.
Based on relational model, Relation database was created. Codd proposed 13 rules popularly
known as Codd's 12 rules to test DBMS's concept against his relational model. Codd's rule
actualy define what quality a DBMS requires in order to become a Relational Database
Management System(RDBMS). Till now, there is hardly any commercial product that follows all
the 13 Codd's rules. Even Oracle follows only eight and half out(8.5) of 13. The Codd's 12 rules
are as follows.

Rule zero

This rule states that for a system to qualify as an RDBMS, it must be able to manage database
entirely through the relational capabilities.

Rule 1 : Information rule

All information(including metadeta) is to be represented as stored data in cells of tables. The

rows and columns have to be strictly unordered.

Rule 2 : Guaranted Access

Each unique piece of data(atomic value) should be accesible by : Table Name + primary
key(Row) + Attribute(column).

Set by:- Haile.D

Badesa Tvet Collage

NOTE : Ability to directly access via POINTER is a violation of this rule.

Rule 3 : Systemetic treatment of NULL

Null has several meanings, it can mean missing data, not applicable or no value. It should be
handled consistently. Primary key must not be null. Expression on NULL must give null.

Rule 4 : Active Online Catalog

Database dictionary(catalog) must have description of Database. Catalog to be governed by

same rule as rest of the database. The same query language to be used on catalog as on
application database.

Rule 5 : Powerful language

One well defined language must be there to provide all manners of access to data. Example:
SQL. If a file supporting table can be accessed by any manner except SQL interface, then its a
violation to this rule.

Rule 6 : View Updation rule

All view that are theoretically updatable should be updatable by the system.

Rule 7 : Relational Level Operation

There must be Insert, Delete, Update operations at each level of relations. Set operation like
Union, Intersection and minus should also be supported.

Rule 8 : Physical Data Independence

The physical storage of data should not matter to the system. If say, some file supporting table
were renamed or moved from one disk to another, it should not effect the application.

Rule 9 : Logical Data Independence

If there is change in the logical structure(table structures) of the database the user view of data
should not change. Say, if a table is split into two tables, a new view should give result as the
join of the two tables. This rule is most difficult to satisfy.

Rule 10 : Integrity Independence

The database should be able to conforce its own integrity rather than using other programs. Key
and Check constraints, trigger etc should be stored in Data Dictionary. This also make RDBMS
independent of front-end.

Set by:- Haile.D

Badesa Tvet Collage

Rule 11 : Distribution Independence

A database should work properly regardless of its distribution across a network. This lays
foundation of distributed database.

Rule 12 : Nonsubversion rule

If low level access is allowed to a system it should not be able to subvert or bypass integrity rule
to change data. This can be achieved by some sort of looking or encryption.

Codd's Rule

E.F Codd was a Computer Scientist who invented Relational model for Database management.
Based on relational model, Relation database was created. Codd proposed 13 rules popularly
known as Codd's 12 rules to test DBMS's concept against his relational model. Codd's rule
actualy define what quality a DBMS requires in order to become a Relational Database
Management System(RDBMS). Till now, there is hardly any commercial product that follows all
the 13 Codd's rules. Even Oracle follows only eight and half out(8.5) of 13. The Codd's 12 rules
are as follows.

Rule zero

This rule states that for a system to qualify as an RDBMS, it must be able to manage database
entirely through the relational capabilities.

Set by:- Haile.D

Badesa Tvet Collage

Rule 1 : Information rule

All information(including metadeta) is to be represented as stored data in cells of tables. The

rows and columns have to be strictly unordered.

Rule 2 : Guaranted Access

Each unique piece of data(atomic value) should be accesible by : Table Name + primary
key(Row) + Attribute(column).

NOTE : Ability to directly access via POINTER is a violation of this rule.

Rule 3 : Systemetic treatment of NULL

Null has several meanings, it can mean missing data, not applicable or no value. It should be
handled consistently. Primary key must not be null. Expression on NULL must give null.

Rule 4 : Active Online Catalog

Database dictionary(catalog) must have description of Database. Catalog to be governed by

same rule as rest of the database. The same query language to be used on catalog as on
application database.

Rule 5 : Powerful language

One well defined language must be there to provide all manners of access to data. Example:
SQL. If a file supporting table can be accessed by any manner except SQL interface, then its a
violation to this rule.

Rule 6 : View Updation rule

All view that are theoretically updatable should be updatable by the system.

Rule 7 : Relational Level Operation

There must be Insert, Delete, Update operations at each level of relations. Set operation like
Union, Intersection and minus should also be supported.

Rule 8 : Physical Data Independence

The physical storage of data should not matter to the system. If say, some file supporting table
were renamed or moved from one disk to another, it should not effect the application.

Rule 9 : Logical Data Independence

Set by:- Haile.D

Badesa Tvet Collage

If there is change in the logical structure(table structures) of the database the user view of data
should not change. Say, if a table is split into two tables, a new view should give result as the
join of the two tables. This rule is most difficult to satisfy.

Rule 10 : Integrity Independence

The database should be able to conforce its own integrity rather than using other programs. Key
and Check constraints, trigger etc should be stored in Data Dictionary. This also make RDBMS
independent of front-end.

Rule 11 : Distribution Independence

A database should work properly regardless of its distribution across a network. This lays
foundation of distributed database.

Rule 12 : Nonsubversion rule

If low level access is allowed to a system it should not be able to subvert or bypass integrity rule
to change data. This can be achieved by some sort of looking or encryption.

RDBMS Concepts

A Relational Database management System(RDBMS) is a database management system based

on relational model introduced by E.F Codd. In relational model, data is represented in terms of
tuples(rows).

RDBMS is used to manage Relational database. Relational database is a collection of

organized set of tables from which data can be accessed easily. Relational Database is most
commonly used database. It consists of number of tables and each table has its own primary key.

What is Table ?

In Relational database, a table is a collection of data elements organised in terms of rows and
columns. A table is also considered as convenient representation of relations. But a table can
have duplicate tuples while a true relation cannot have duplicate tuples. Table is the most
simplest form of data storage. Below is an example of Employee table.

ID Name Age Salary

1 Adam 34 13000
2 Alex 28 15000
3 Stuart 20 18000
4 Ross 42 19020

What is a Record ?

Set by:- Haile.D

Badesa Tvet Collage

A single entry in a table is called a Record or Row. A Record in a table represents set of related
data. For example, the above Employee table has 4 records. Following is an example of single
record.

1 Adam 34 13000

What is Field ?

A table consists of several records(row), each record can be broken into several smaller entities
known as Fields. The above Employee table consist of four fields, ID, Name, Age and Salary.

What is a Column ?

In Relational table, a column is a set of value of a particular type. The term Attribute is also
used to represent a column. For example, in Employee table, Name is a column that represent
names of employee.

Name
Adam
Alex
Stuart
Ross

Database Keys

Keys are very important part of Relational database. They are used to establish and identify
relation between tables. They also ensure that each record within a table can be uniquely
identified by combination of one or more fields within a table.

Super Key

Super Key is defined as a set of attributes within a table that uniquely identifies each record
within a table. Super Key is a superset of Candidate key.

Candidate Key

Candidate keys are defined as the set of fields from which primary key can be selected. It is an
attribute or set of attribute that can act as a primary key for a table to uniquely identify each
record in that table.

Primary Key

Primary key is a candidate key that is most appropriate to become main key of the table. It is a
key that uniquely identify each record in a table.

Set by:- Haile.D

Badesa Tvet Collage

Composite Key

Key that consist of two or more attributes that uniquely identify an entity occurance is called
Composite key. But any attribute that makes up the Composite key is not a simple key in its
own.

Secondary or Alternative key

The candidate key which are not selected for primary key are known as secondary keys or
alternative keys

Set by:- Haile.D

Badesa Tvet Collage

Non-key Attribute

Non-key attributes are attributes other than candidate key attributes in a table.

Non-prime Attribute

Non-prime Attributes are attributes other than Primary attribute.

E-R Diagram

ER-Diagram is a visual representation of data that describes how data is related to each other.

Symbols and Notations

Set by:- Haile.D

Badesa Tvet Collage

Set by:- Haile.D

Badesa Tvet Collage

Components of E-R Diagram

The E-R diagram has three main components.

1) Entity

An Entity can be any object, place, person or class. In E-R Diagram, an entity is represented
using rectangles. Consider an example of an Organisation. Employee, Manager, Department,
Product and many more can be taken as entities from an Organisation.

Weak Entity

Weak entity is an entity that depends on another entity. Weak entity doen't have key attribute of
their own. Double rectangle represents weak entity.

Set by:- Haile.D

Badesa Tvet Collage

2) Attribute

An Attribute describes a property or characterstic of an entity. For example, Name, Age,

Address etc can be attributes of a Student. An attribute is represented using eclipse.

Key Attribute

Key attribute represents the main characterstic of an Entity. It is used to represent Primary key.
Ellipse with underlying lines represent Key Attribute.

Set by:- Haile.D

Badesa Tvet Collage

Composite Attribute

An attribute can also have their own attributes. These attributes are known as Composite
attribute.

3) Relationship

A Relationship describes relations between entities. Relationship is represented using diamonds.

Set by:- Haile.D

Badesa Tvet Collage

There are three types of relationship that exist between Entities.

 Binary Relationship
 Recursive Relationship
 Ternary Relationship

Binary Relationship

Binary Relationship means relation between two Entities. This is further divided into three types.

1. One to One : This type of relationship is rarely seen in real world.

Set by:- Haile.D

Badesa Tvet Collage

The above example describes that one student can enroll only for one course and a course
will also have only one Student. This is not what you will usually see in relationship.

2. One to Many : It reflects business rule that one entity is associated with many number of
same entity. The example for this relation might sound a little weird, but this menas that
one student can enroll to many courses, but one course will have one Student.

The arrows in the diagram describes that one student can enroll for only one course.

3. Many to One : It reflects business rule that many entities can be associated with just one
entity. For example, Student enrolls for only one Course but a Course can have many
Students.

Set by:- Haile.D

Badesa Tvet Collage

4. Many to Many :

The above diagram represents that many students can enroll for more than one courses.

Recursive Relationship

When an Entity is related with itself it is known as Recursive Relationship.

Ternary Relationship

Set by:- Haile.D

Badesa Tvet Collage

Relationship of degree three is called Ternary relationship.

Generalization is a bottom-up approach in which two lower level entities combine to form a
higher level entity. In generalization, the higher level entity can also combine with other lower
level entity to make further higher level entity.

Specialization

Specialization is opposite to Generalization. It is a top-down approach in which one higher level

entity can be broken down into two lower level entity. In specialization, some higher level
entities may not have lower-level entity sets at all.

Set by:- Haile.D

Badesa Tvet Collage

Aggregration

Aggregration is a process when relation between two entity is treated as a single entity. Here the
relation between Center and Course, is acting as an Entity in relation with Visitor.

Set by:- Haile.D

Badesa Tvet Collage

Introduction to SQL

Structure Query Language(SQL) is a programming language used for storing and managing data
in RDBMS. SQL was the first commercial language introduced for E.F Codd's Relational
model. Today almost all RDBMS(MySql, Oracle, Infomix, Sybase, MS Access) uses SQL as the
standard database language. SQL is used to perform all type of data operations in RDBMS.

SQL Command

SQL defines following data languages to manipulate data of RDBMS.

DDL : Data Definition Language

All DDL commands are auto-committed. That means it saves all the changes permanently in the
database.

Command Description
create to create new table or database
alter for alteration
truncate delete data from table
drop to drop a table
rename to rename a table

DML : Data Manipulation Language

DML commands are not auto-committed. It means changes are not permanent to database, they
can be rolled back.

Command Description
insert to insert a new row
update to update existing row
delete to delete a row
merge merging two rows or two tables

TCL : Transaction Control Language

These commands are to keep a check on other commands and their affect on the database. These
commands can annul changes made by other commands by rolling back to original state. It can
also make changes permanent.

Command Description
commit to permanently save
rollback to undo change
savepoint to save temporarily

Set by:- Haile.D

Badesa Tvet Collage

DCL : Data Control Language

Data control language provides command to grant and take back authority.

Command Description
grant grant permission of right
revoke take back permission.

DQL : Data Query Language

Command Description
select retrieve records from one or more table

create command

create is a DDL command used to create a table or a database.

Creating a Database

To create a database in RDBMS, create command is uses. Following is the Syntax,

create database database-name;

Example for Creating Database

create database Test;

The above command will create a database named Test.

Creating a Table

create command is also used to create a table. We can specify names and datatypes of various
columns along.Following is the Syntax,

create table table-name

{
column-name1 datatype1,
column-name2 datatype2,
column-name3 datatype3,
column-name4 datatype4
};

create table command will tell the database system to create a new table with given table name
and column information.

Example for creating Table

Set by:- Haile.D

Badesa Tvet Collage

create table Student(id int, name varchar, age int);

The above command will create a new table Student in database system with 3 columns, namely
id, name and age.

alter command

alter command is used for alteration of table structures. There are various uses of alter
command, such as,

 to add a column to existing table

 to rename any existing column
 to change datatype of any column or to modify its size.
 alter is also used to drop a column.

To Add Column to existing Table

Using alter command we can add a column to an existing table. Following is the Syntax,

alter table table-name add(column-name datatype);

Here is an Example for this,

alter table Student add(address char);

The above command will add a new column address to the Student table

To Add Multiple Column to existing Table

Using alter command we can even add multiple columns to an existing table. Following is the
Syntax,

alter table table-name add(column-name1 datatype1, column-name2 datatype2,

column-name3 datatype3);

Here is an Example for this,

alter table Student add(father-name varchar(60), mother-name varchar(60), dob

date);

The above command will add three new columns to the Student table

To Add column with Default Value

Set by:- Haile.D

Badesa Tvet Collage

alter command can add a new column to an existing table with default values. Following is the
Syntax,

alter table table-name add(column-name1 datatype1 default data);

Here is an Example for this,

alter table Student add(dob date default '1-Jan-99');

The above command will add a new column with default value to the Student table

To Modify an existing Column

alter command is used to modify data type of an existing column . Following is the Syntax,

alter table table-name modify(column-name datatype);

Here is an Example for this,

alter table Student modify(address varchar(30));

The above command will modify address column of the Student table

To Rename a column

Using alter command you can rename an existing column. Following is the Syntax,

alter table table-name rename old-column-name to column-name;

Here is an Example for this,

alter table Student rename address to Location;

The above command will rename address column to Location.

To Drop a Column

alter command is also used to drop columns also. Following is the Syntax,

alter table table-name drop(column-name);

Here is an Example for this,

alter table Student drop(address);

The above command will drop address column from the Student table

Set by:- Haile.D

Badesa Tvet Collage

QL queries to Truncate, Drop or Rename a Table

truncate command

truncate command removes all records from a table. But this command will not destroy the
table's structure. When we apply truncate command on a table its Primary key is initialized.
Following is its Syntax,

truncate table table-name

Here is an Example explaining it.

truncate table Student;

The above query will delete all the records of Student table.

truncate command is different from delete command. delete command will delete all the rows
from a table whereas truncate command re-initializes a table(like a newly created table).

For eg. If you have a table with 10 rows and an auto_increment primary key, if you use delete
command to delete all the rows, it will delete all the rows, but will not initialize the primary key,
hence if you will insert any row after using delete command, the auto_increment primary key
will start from 11. But in case of truncate command, primary key is re-initialized.

drop command

drop query completely removes a table from database. This command will also destroy the table
structure. Following is its Syntax,

drop table table-name

Here is an Example explaining it.

drop table Student;

The above query will delete the Student table completely. It can also be used on Databases. For
Example, to drop a database,

drop database Test;

The above query will drop a database named Test from the system.

rename query

rename command is used to rename a table. Following is its Syntax,

rename table old-table-name to new-table-name

Set by:- Haile.D

Badesa Tvet Collage

Here is an Example explaining it.

rename table Student to Student-record;

The above query will rename Student table to Student-record.

DML command

Data Manipulation Language (DML) statements are used for managing data in database. DML
commands are not auto-committed. It means changes made by DML command are not
permanent to database, it can be rolled back.

1) INSERT command

Insert command is used to insert data into a table. Following is its general syntax,

INSERT into table-name values(data1,data2,..)

Lets see an example,

Consider a table Student with following fields.

S_id S_Name age

INSERT into Student values(101,'Adam',15);

The above command will insert a record into Student table.

S_id S_Name age

101 Adam 15

Example to Insert NULL value to a column

Both the statements below will insert NULL value into age column of the Student table.

INSERT into Student(id,name) values(102,'Alex');

Or,

INSERT into Student values(102,'Alex',null);

The above command will insert only two column value other column is set to null.

S_id S_Name age

101 Adam 15
102 Alex

Set by:- Haile.D

Badesa Tvet Collage

Example to Insert Default value to a column

INSERT into Student values(103,'Chris',default)

S_id S_Name age
101 Adam 15
102 Alex
103 chris 14

Suppose the age column of student table has default value of 14.

Also, if you run the below query, it will insert default value into the age column, whatever the
default value may be.

INSERT into Student values(103,'Chris')

2) UPDATE command

Update command is used to update a row of a table. Following is its general syntax,

UPDATE table-name set column-name = value where condition;

Lets see an example,

update Student set age=18 where s_id=102;

S_id S_Name age
101 Adam 15
102 Alex 18
103 chris 14

Example to Update multiple columns

UPDATE Student set s_name='Abhi',age=17 where s_id=103;

The above command will update two columns of a record.

S_id S_Name age

101 Adam 15
102 Alex 18
103 Abhi 17

3) Delete command

Delete command is used to delete data from a table. Delete command can also be used with
condition to delete a particular row. Following is its general syntax,

Set by:- Haile.D

Badesa Tvet Collage

DELETE from table-name;

Example to Delete all Records from a Table

DELETE from Student;

The above command will delete all the records from Student table.

Example to Delete a particular Record from a Table

Consider the following Student table

S_id S_Name age

101 Adam 15
102 Alex 18
103 Abhi 17
DELETE from Student where s_id=103;

The above command will delete the record where s_id is 103 from Student table.

S_id S_Name age

101 Adam 15
102 Alex 18

TCL command

Transaction Control Language(TCL) commands are used to manage transactions in

database.These are used to manage the changes made by DML statements. It also allows
statements to be grouped together into logical transactions.

Commit command

Commit command is used to permanently save any transaaction into database.

Following is Commit command's syntax,

commit;

Rollback command

This command restores the database to last commited state. It is also use with savepoint
command to jump to a savepoint in a transaction.

Following is Rollback command's syntax,

rollback to savepoint-name;

Set by:- Haile.D

Badesa Tvet Collage

Savepoint command

savepoint command is used to temporarily save a transaction so that you can rollback to that
point whenever necessary.

Following is savepoint command's syntax,

savepoint savepoint-name;

Example of Savepoint and Rollback

Following is the class table,

ID NAME
1 abhi
2 adam
4 alex

Lets use some SQL queries on the above table and see the results.

INSERT into class values(5,'Rahul');

commit;
UPDATE class set name='abhijit' where id='5';
savepoint A;
INSERT into class values(6,'Chris');
savepoint B;
INSERT into class values(7,'Bravo');
savepoint C;
SELECT * from class;

The resultant table will look like,

ID NAME
1 abhi
2 adam
4 alex
5 abhijit
6 chris
7 bravo

Now rollback to savepoint B

rollback to B;
SELECT * from class;

The resultant table will look like

Set by:- Haile.D

Badesa Tvet Collage

ID NAME
1 abhi
2 adam
4 alex
5 abhijit
6 chris

Now rollback to savepoint A

rollback to A;
SELECT * from class;

The result table will look like

ID NAME
1 abhi
2 adam
4 alex
5 abhijit

DCL command

Data Control Language(DCL) is used to control privilege in Database. To perform any operation
in the database, such as for creating tables, sequences or views we need privileges. Privileges are
of two types,

 System : creating session, table etc are all types of system privilege.
 Object : any command or query to work on tables comes under object privilege.

DCL defines two commands,

 Grant : Gives user access privileges to database.

 Revoke : Take back permissions from user.

To Allow a User to create Session

grant create session to username;

To Allow a User to create Table

grant create table to username;

To provide User with some Space on Tablespace to store Table

alter user username quota unlimited on system;

Set by:- Haile.D

Badesa Tvet Collage

To Grant all privilege to a User

grant sysdba to username

To Grant permission to Create any Table

grant create any table to username

To Grant permission to Drop any Table

grant drop any table to username

To take back Permissions

revoke create table from username

WHERE clause

Where clause is used to specify condition while retriving data from table. Where clause is used
mostly with Select, Update and Delete query. If condititon specified by where clause is true then
only the result from table is returned.

Syntax for WHERE clause

SELECT column-name1,
column-name2,
column-name3,
column-nameN
from table-name WHERE [condition];

Example using WHERE clause

Consider a Student table,

s_id s_Name age address

101 Adam 15 Noida
102 Alex 18 Delhi
103 Abhi 17 Rohtak
104 Ankit 22 Panipat

Now we will use a SELECT statement to display data of the table, based on a condition, which
we will add to the SELECT query using WHERE clause.

SELECT s_id,
s_name,
age,
address
from Student WHERE s_id=101;

Set by:- Haile.D

Badesa Tvet Collage

s_id s_Name age address

101 Adam 15

SELECT Query

Select query is used to retrieve data from a tables. It is the most used SQL query. We can retrieve
complete tables, or partial by mentioning conditions using WHERE clause.

Syntax of SELECT Query

SELECT column-name1, column-name2, column-name3, column-nameN from table-name;

Example for SELECT Query

Conside the following Student table,

S_id S_Name age address

101 Adam 15 Noida
102 Alex 18 Delhi
103 Abhi 17 Rohtak
104 Ankit 22 Panipat
SELECT s_id, s_name, age from Student.

The above query will fetch information of s_id, s_name and age column from Student table

S_id S_Name age

101 Adam 15
102 Alex 18
103 Abhi 17
104 Ankit 22

Example to Select all Records from Table

A special character asterisk * is used to address all the data(belonging to all columns) in a query.
SELECT statement uses * character to retrieve all records from a table.

SELECT * from student;

The above query will show all the records of Student table, that means it will show complete
Student table as result.

S_id S_Name age address

101 Adam 15 Noida
102 Alex 18 Delhi

Set by:- Haile.D

Badesa Tvet Collage

103 Abhi 17 Rohtak

104 Ankit 22 Panipat

Example to Select particular Record based on Condition

SELECT * from Student WHERE s_name = 'Abhi';

103 Abhi 17 Rohtak

Example to Perform Simple Calculations using Select Query

Conside the following Employee table.

eid Name age salary

101 Adam 26 5000
102 Ricky 42 8000
103 Abhi 22 10000
104 Rohan 35 5000
SELECT eid, name, salary+3000 from Employee;

The above command will display a new column in the result, showing 3000 added into existing
salaries of the employees.

eid Name salary+3000

101 Adam 8000
102 Ricky 11000
103 Abhi 13000
104 Rohan 8000

Set by:- Haile.D