Fundamentals of Databases

Guide
Introduction to Database Systems
• What is a database ?
• An organized collection of data, generally stored and accessed electronically
from a computer system.
• A collection of files storing related data.
• Data is a collection of facts and figures that can be processed to produce information.

• Examples of databases
• IST student database; Company XYZ payroll database; Amazon’s products
database; airline reservation database
Cont’d
• What is a DBMS ?
• A big program written by someone else that allows us to manage efficiently a
large database and allows it to persist over long periods of time.

• Examples of DBMSs – Oracle, Microsoft SQL Server

• Open source: MySQL (Sun/Oracle), PostgreSQL
• Open source library: SQLite
 Cont’d
• Evolution of file processing systems
• Use of papers(Before computers)
• Use of files e.g. notepad file(After computers)
• Search
• Open
• Show
• Close
• Updating
• Deleting
• Using Languages such as C, C++ that can be used to do above tasks.
• Difficult for programmers.
 Cont’d
• Faster databases were needed
• Advantages
• Data accuracy
• Easy access to data
• Data integrity
• Flexibility
• Normalization
• High Security
• Ease of backup
 Cont’d
• Types of Database
• Relational Database Management System
• Manipulated through the use of SQL or Structured Query Language
• Examples: Oracle, IBM, Ms SQLServer, mySQL, PostgreSQL
• Flat file based database management systems
• Example: comma spared values or CSV
• Hierarchical database management systems
• Example: XML document
• Network Database management systems
• Example: Integrated Data Store (IDS)
• Object oriented database management systems
• Mongodb
 Cont’d
• Core components of a database environment
• The database system consists of many components.
• It defines and regulates the collection, storage, management and use of data
within a database environment.
• The components of database system are
• Hardware,
• software,
• procedures,
• people and
• data.
 Cont’d
• Role of databases in organizations
• Better Data Access Within the Company
• Stronger Relationships Between Data
• Improved Data Security
• It helps brand managers to search data in a better manner
 Data Modeling
• Data modeling (data modelling) is the process of creating a data
model for the data to be stored in a database.
• This data model is a conceptual representation of Data objects, the
associations between different data objects, and the rules.
 Cont’d
• Advantages
• Ensures that all data objects required by the database are accurately represented.
Omission of data will lead to creation of faulty reports and produce incorrect results.
• A data model helps design the database at the conceptual, physical and logical levels.
• Data Model structure helps to define the relational tables, primary and foreign keys
and stored procedures.
• It provides a clear picture of the base data and can be used by database developers
to create a physical database.
• It is also helpful to identify missing and redundant data.
• Though the initial creation of data model is labor and time consuming, in the long
run, it makes your IT infrastructure upgrade and maintenance cheaper and faster.
 Cont’d
• Types of Data Modeling Techniques
• Entity Relationship (E-R) Model
• UML (Unified Modelling Language) class diagram
• Data Dictionary
 Cont’d
• Types of Data Models
• Conceptual data models,
• Logical data models, and
• Physical data models
Cont’d
• Conceptual Data Model
• A Conceptual Data Model is an organized view of database concepts and
their relationships. The purpose of creating a conceptual data model is to
establish entities, their attributes, and relationships.
• In this data modeling level, there is hardly any detail available on the actual
database structure.
• Business stakeholders and data architects typically create a conceptual data
model.
Cont’d
Cont’d
• Logical Data Model
• The Logical Data Model is used to define the structure of data elements and
to set relationships between them.
Cont’d
Cont’d
• Physical Data Model
• A Physical Data Model describes a database-specific implementation of the
data model.
Cont’d
Cont’d
• Database development life cycle
Cont’d
• Entity Relationship Diagrams
• Also referred to as ER diagrams or ERDs. Entity-Relationship modeling is a
default technique for modeling and the design of relational (traditional)
databases.
• Draw ER diagram online.
• https://lucid.app/lucidchart/2c832bbf-efc3-4416-bff5-c5eb76b85343/edit?page=0_0#
Cont’d
Cont’d
• UML Class Diagrams
• UML (Unified Modeling Language) is a standardized family of notations for
modeling and design of information systems.
Cont’d
Cont’d
• Data Dictionary
• Techniques mentioned above were visual and were based on diagrams, and
data dictionaries are a tabular definition/representation of data assets.
• Data dictionary is an inventory of data sets/tables with the list of their
attributes/columns.
Cont’d
Cont’d
• Types of database models
• Hierarchical database model
• Relational model
• Network model
• Object-oriented database model
• Entity-relationship model
• Document model
• Entity-attribute-value model
• Star schema
• The object-relational model, which combines the two that make up its name
Cont’d
• Relational model
• The most common model, the relational model sorts data into tables, also
known as relations, each of which consists of columns and rows.
Cont’d
Cont’d
• Hierarchical model
• The hierarchical model organizes data into a tree-like structure, where each
record has a single parent or root.
• Sibling records are sorted in a particular order. That order is used as the
physical order for storing the database.
• This model is good for describing many real-world relationships.
Cont’d
Cont’d
• Network model
• The network model builds on the hierarchical model by allowing many-to-
many relationships between linked records, implying multiple parent records.
Cont’d
Cont’d
• Object-oriented database model
• This model defines a database as a collection of objects, or reusable software
elements, with associated features and methods. There are several kinds of
object-oriented databases:
Cont’d
Cont’d
• Object-relational model
• This hybrid database model combines the simplicity of the relational model
with some of the advanced functionality of the object-oriented database
model.
• In essence, it allows designers to incorporate objects into the familiar table
structure.
Cont’d
• Entity-relationship model
• This model captures the relationships between real-world entities much like
the network model, but it isn’t as directly tied to the physical structure of the
database.
• Instead, it’s often used for designing a database conceptually.
Cont’d
Cont’d
• NoSQL database models
• In addition to the object database model, other non-SQL models have
emerged in contrast to the relational model:
The Relational Model and Normalization
• Relational model :
• Relational Model was proposed by E.F. Codd to model data in the form of
relations or tables.
• After designing the conceptual model of Database using ER diagram, we need
to convert the conceptual model in the relational model which can be
implemented using any RDBMS languages like Oracle SQL, MySQL etc.
Cont’d
Cont’d
• Attribute: Attributes are the properties that define a relation. e.g.;
ROLL_NO, NAME
• Relation Schema: A relation schema represents name of the relation
with its attributes. e.g.; STUDENT (ROLL_NO, NAME, ADDRESS,
PHONE and AGE) is relation schema for STUDENT.
• If a schema has more than 1 relation, it is called Relational Schema.
• Tuple: Each row in the relation is known as tuple. The above relation
contains 4 tuples, one of which is shown as:
• 1 RAM DELHI 9455123451 18 (See the above table)
Cont’d
• Relation Instance: The set of tuples of a relation at a particular
instance of time is called as relation instance.
• Table above shows the relation instance of STUDENT at a particular time. It
can change whenever there is insertion, deletion or updating in the database.
• Degree: The number of attributes in the relation is known as degree
of the relation. The STUDENT relation defined above has degree 5.
Cont’d
• Cardinality: The number of tuples in a relation is known as cardinality.
The STUDENT relation defined above has cardinality 4.
• Column: Column represents the set of values for a particular
attribute. The column ROLL_NO is extracted from relation STUDENT.
• NULL Values: The value which is not known or unavailable is called
NULL value. It is represented by blank space. e.g.; PHONE of STUDENT
having ROLL_NO 4 is NULL.
Cont’d
• Constraints in Relational Model
• While designing Relational Model, we define some conditions which must
hold for data present in database are called Constraints.
• These constraints are checked before performing any operation (insertion,
deletion and updating) in database.
• If there is a violation in any of constrains, operation will fail.
Cont’d
• Domain Constraints: These are attribute level constraints.
• An attribute can only take values which lie inside the domain range. e.g,; If a
constrains AGE>0 is applied on STUDENT relation, inserting negative value of AGE will
result in failure.

• Key Integrity: Every relation in the database should have atleast one set of
attributes which defines a tuple uniquely.
• Those set of attributes is called key. e.g.; ROLL_NO in STUDENT is a key. No two
students can have same roll number. So a key has two properties:

• It should be unique for all tuples.

• It can’t have NULL values.
Cont’d
• Referential Integrity: When one attribute of a relation can only take
values from other attribute of same relation or any other relation, it is
called referential integrity.
Cont’d
• ANOMALIES

• An anomaly is an irregularity, or something which deviates from the expected

or normal state.
• When designing databases, we identify three types of anomalies: Insert,
Update and Delete.
Cont’d
• Normalization of Database
• Database Normalization is a technique of organizing the data in the database.
• Normalization is a systematic approach of decomposing tables to eliminate
data redundancy(repetition) and undesirable characteristics like Insertion,
Update and Deletion Anomalies.
• It is a multi-step process that puts data into tabular form, removing
duplicated data from the relation tables.
Cont’d
• Problems Without Normalization
• If a table is not properly normalized and have data redundancy then it will not
only eat up extra memory space but will also make it difficult to handle and
update the database, without facing data loss.
• Insertion, Updation and Deletion Anomalies are very frequent if database is
not normalized.
Cont’d
• Normalization Rule
• Normalization rules are divided into the following normal forms:

• First Normal Form

• Second Normal Form
• Third Normal Form
• BCNF
• Fourth Normal Form
Cont’d
• First Normal Form (1NF)
• For a table to be in the First Normal Form, it should follow the following 4
rules:

• It should only have single(atomic) valued attributes/columns.

• Values stored in a column should be of the same domain
• All the columns in a table should have unique names.
• And the order in which data is stored, does not matter.
Cont’d
• Second Normal Form (2NF)
• For a table to be in the Second Normal Form,

• It should be in the First Normal form.

• And, it should not have Partial Dependency.
Cont’d
• Third Normal Form (3NF)
• A table is said to be in the Third Normal Form when,

• It is in the Second Normal form.

• And, it doesn't have Transitive Dependency.
Cont’d
• Boyce-Codd Normal Form or BCNF is an extension to the third normal
form, and is also known as 3.5 Normal Form.
Cont’d
• Boyce and Codd Normal Form (BCNF)
• Boyce and Codd Normal Form is a higher version of the Third Normal form.
This form deals with certain type of anomaly that is not handled by 3NF.
• A 3NF table which does not have multiple overlapping candidate keys is said
to be in BCNF.
• For a table to be in BCNF, following conditions must be satisfied:

• R must be in 3rd Normal Form

• and, for each functional dependency ( X → Y ), X should be a super Key.
Cont’d
• Fourth Normal Form (4NF)
• A table is said to be in the Fourth Normal Form when,

• It is in the Boyce-Codd Normal Form.

• And, it doesn't have Multi-Valued Dependency.
Physical Database Design
• Physical design is performed in two stages:

• Conversion of the logical design into table definitions (often performed by an

application developer): includes pre-deployment design, table definitions,
normalization, primary and foreign key relationships, and basic indexing.

• Post deployment physical database design (often performed by a database

administrator): includes improving performance, reducing I/O, and
streamlining administration tasks.
SQL Language
• Structure Query Language(SQL) is a database query language used for
storing and managing data in Relational DBMS.
• SQL was the first commercial language introduced for E.F
Codd's Relational model of database.
• Today almost all RDBMS(MySql, Oracle, Infomix, Sybase, MS Access)
use SQL as the standard database query language.
• SQL is used to perform all types of data operations in RDBMS.
Cont’d
• SQL: create command
• Create is a DDL SQL command used to create a table or a database in
relational database management system.
Cont’d
• Creating a Database
• To create a database in RDBMS, create command is used. Following is the
syntax,

• CREATE DATABASE <DB_NAME>;

• Example for creating Database
• CREATE DATABASE Test;
Cont’d
• Creating a Table
• create command can also be used to create tables. Now when we create a table, we have to specify the
details of the columns of the tables too. We can specify the names and datatypes of various columns in the
create command itself.

• Following is the syntax,

• CREATE TABLE <TABLE_NAME>

(
column_name1 datatype1,
column_name2 datatype2,
column_name3 datatype3,
column_name4 datatype4
);
Cont’d
• Most commonly used datatypes for Table columns
• Here we have listed some of the most commonly used datatypes used
for columns in tables.
Cont’d
Datatype Use
INT used for columns which will store integer values.

FLOAT used for columns which will store float values.

DOUBLE used for columns which will store float values.

VARCHAR used for columns which will be used to store characters and integers, basically a
string.

CHAR used for columns which will store char values(single character).

DATE used for columns which will store date values.

TEXT used for columns which will store text which is generally long in length. For
example, if you create a table for storing profile information of a social
networking website, then for about me section you can have a column of
type TEXT.
Cont’d
• SQL: ALTER command
• alter command is used for altering the table structure, 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.
• to drop a column from the table.
Cont’d
• ALTER Command: Add a new Column
• Using ALTER command we can add a column to any existing table.
Following is the syntax,

• ALTER TABLE table_name ADD(

column_name datatype);
• Example,

• ALTER TABLE student ADD(

address VARCHAR(200)
);
Cont’d
• ALTER Command: Add multiple new Columns
• Using ALTER command we can even add multiple new columns to any existing table. Following is the syntax,

• ALTER TABLE table_name ADD(

column_name1 datatype1,
column-name2 datatype2,
column-name3 datatype3);
• Example,

• ALTER TABLE student ADD(

father_name VARCHAR(60),
mother_name VARCHAR(60),
dob DATE);
Cont’d
• ALTER Command: Add Column with default value
• ALTER command can add a new column to an existing table with a default value too. The
default value is used when no value is inserted in the column. Following is the syntax,

• ALTER TABLE table_name ADD(

column-name1 datatype1 DEFAULT some_value
);
• Example,

• ALTER TABLE student ADD(

dob DATE DEFAULT '01-Jan-99'
);
Cont’d
• ALTER Command: Modify an existing Column
• ALTER command can also be used to modify data type of any 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(300));
Cont’d
• ALTER Command: 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 new_column_name;
• Example,

• ALTER TABLE student RENAME

address TO location;
Cont’d
• ALTER Command: Drop a Column
• ALTER command can also be used to drop or remove columns. Following is
the syntax,

• ALTER TABLE table_name DROP(

column_name);
• Example,

• ALTER TABLE student DROP(

address);
Cont’d
• SQL Truncate, Drop or Rename a Table
• TRUNCATE command
• TRUNCATE command removes all the records from a table. But this command
will not destroy the table's structure. When we use TRUNCATE command on a
table its (auto-increment) primary key is also initialized. Following is its syntax,

• TRUNCATE TABLE table_name

• Example,

• TRUNCATE TABLE student;

Cont’d
• DROP command
• DROP command completely removes a table from the database. This
command will also destroy the table structure and the data stored in
it. Following is its syntax,

• DROP TABLE table_name

• Example,

• DROP TABLE student;

Cont’d
• RENAME query
• RENAME command is used to set a new name for any existing table.
Following is the syntax,

• RENAME TABLE old_table_name to new_table_name

• Example.

• RENAME TABLE student to students_info;

Cont’d
• Using INSERT SQL 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.
Cont’d
• INSERT command
• Insert command is used to insert data into a table. Following is its
general syntax,

• INSERT INTO table_name VALUES(data1, data2, ...)

Cont’d
• INSERT INTO student VALUES(101, 'Adam', 15);
• The above command will insert a new record into student table.
Cont’d
• Insert value into only specific columns
• We can use the INSERT command to insert values for only some
specific columns of a row.
• We can specify the column names along with the values to be
inserted like this,

• INSERT INTO student(id, name) values(102, 'Alex');

• The above SQL query will only insert id and name values in the newly
inserted record.
Cont’d
• 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 values and the other
column is set to null.
Cont’d

S_id S_Name age

101 Adam 15
102 Alex
Cont’d
• 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
Cont’d
• Suppose the column age in our tabel has a 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')

Cont’d
• Using UPDATE SQL command
• UPDATE command is used to update any record of data in a table. Following is
its general syntax,

• UPDATE table_name SET column_name = new_value WHERE

some_condition;
• WHERE is used to add a condition to any SQL query, we will soon study about
it in detail.
Cont’d
Sample table student
student_id name age
101 Adam 15
102 Alex
103 chris 14

UPDATE student SET age=18 WHERE student_id=102;

S_id S_Name age
101 Adam 15
102 Alex 18
103 chris 14
Cont’d
• Updating Multiple Columns
• We can also update values of multiple columns using a single UPDATE
statement.

• UPDATE student SET name='Abhi', age=17 where s_id=103;

Cont’d
• Using DELETE SQL command
• DELETE command
• DELETE command is used to delete data from a table.

• Following is its general syntax,

• DELETE FROM table_name;

Cont’d

Sample table student

s_id name age

101 Adam 15
102 Alex 18
103 Abhi 17
Cont’d
• Delete all Records from a Table
• DELETE FROM student;
• The above command will delete all the records from the table student.
Cont’d
• Delete a particular Record from a Table
• In our student table if we want to delete a single record, we can use
the WHERE clause to provide a condition in our DELETE statement.

• DELETE FROM student WHERE s_id=103;

• The above command will delete the record where s_id is 103 from the
table student.
Cont’d
• Commit, Rollback and Savepoint SQL commands
• Transaction Control Language(TCL) commands are used to manage
transactions in the database.
Cont’d

Using Savepoint and Rollback

id name
1 Abhi
2 Adam
4 Alex
Cont’d
• 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;

Cont’d

id name
1 Abhi
2 Adam
4 Alex
5 Abhijit
6 Chris
7 Bravo
Cont’d
• Now let's use the ROLLBACK command to roll back the state of data
to the savepoint B.

• ROLLBACK TO B;

• SELECT * FROM class;

Cont’d

id name
1 Abhi
2 Adam
4 Alex
5 Abhijit
6 Chris
Cont’d
• Now let's again use the ROLLBACK command to roll back the state of
data to the savepoint A

• ROLLBACK TO A;

• SELECT * FROM class;

Cont’d

Now the table will look like,

id name
1 Abhi
2 Adam
4 Alex
5 Abhijit
Cont’d
• SELECT SQL Query
• SELECT query is used to retrieve data from a table.
• It is the most used SQL query.
• We can retrieve complete table data, or partial by specifying conditions using
the WHERE clause.
Cont’d
• Syntax of SELECT query
• SELECT query is used to retieve records from a table. We can specify the
names of the columns which we want in the resultset.

• SELECT
column_name1,
column_name2,
column_name3,
...
column_nameN
FROM table_name;
Cont’d
• Select all records from a 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, for all the columns.

• SELECT * FROM student;

Cont’d
• Select a particular record based on a condition
• We can use the WHERE clause to set a condition,

• SELECT * FROM student WHERE name = 'Abhi';

Cont’d
Operators for WHERE clause condition

Operator Description
= Equal to
!= Not Equal to
< Less than
> Greater than
<= Less than or Equal to
>= Greate than or Equal to
BETWEEN Between a specified range of values
LIKE This is used to search for a pattern in
value.
IN In a given set of values
Cont’d
• SQL LIKE clause
• LIKE clause is used in the condition in SQL query with the WHERE clause.
• LIKE clause compares data with an expression using wildcard operators to
match pattern given in the condition.

• Wildcard operators
• There are two wildcard operators that are used in LIKE clause.

• Percent sign %: represents zero, one or more than one character.

• Underscore sign _: represents only a single character.
Cont’d
• SQL ORDER BY Clause
• Order by clause is used with SELECT statement for arranging retrieved data in
sorted order.
• The Order by clause by default sorts the retrieved data in ascending order.
• To sort the data in descending order DESC keyword is used with Order by
clause.
Cont’d
• SQL Group By Clause
• Group by clause is used to group the results of a SELECT query based on one or more
columns. It is also used with SQL functions to group the result from one or more
tables.

• Syntax for using Group by in a statement.

SELECT column_name, function(column_name)

FROM table_name
WHERE condition
GROUP BY column_name
Cont’d
• SQL HAVING Clause
• Having clause is used with SQL Queries to give more precise condition for a
statement.
• It is used to mention condition in Group by based SQL queries, just like WHERE
clause is used with SELECT query.

• Syntax for HAVING clause is,

• SELECT column_name, function(column_name)
• FROM table_name
• WHERE column_name condition
• GROUP BY column_name
• HAVING function(column_name) condition
Cont’d
• DISTINCT keyword
• The distinct keyword is used with SELECT statement to retrieve unique values
from the table. Distinct removes all the duplicate records while retrieving
records from any table in the database.

• Syntax for DISTINCT Keyword

• SELECT DISTINCT column-name FROM table-name;

• Example
• SELECT DISTINCT salary FROM Emp;
Cont’d
• SQL AND & OR operator
• The AND and OR operators are used with the WHERE clause to make more
precise conditions for fetching data from database by combining more than
one condition together.

• AND operator
• AND operator is used to set multiple conditions with the WHERE clause,
alongside, SELECT, UPDATE or DELETE SQL queries.
Cont’d
• SQL Constraints
• SQL Constraints are rules used to limit the type of data that can go into a
table, to maintain the accuracy and integrity of the data inside table.

• Constraints can be divided into the following two types,

• Column level constraints: Limits only column data.

• Table level constraints: Limits whole table data.
Cont’d
• NOT NULL Constraint
• By default, a column can hold NULL values. If you do not want a column to
have a NULL value, use the NOT NULL constraint.

• It restricts a column from having a NULL value.

• We use ALTER statement and MODIFY statement to specify this constraint.
• One important point to note about this constraint is that it cannot be defined
at table level.
Cont’d
• UNIQUE Constraint
• It ensures that a column will only have unique values. A UNIQUE constraint
field cannot have any duplicate data.

• It prevents two records from having identical values in a column

• We use ALTER statement and MODIFY statement to specify this constraint.
Cont’d
• Primary Key Constraint
• Primary key constraint uniquely identifies each record in a database.
• A Primary Key must contain unique value and it must not contain null value.
• Usually Primary Key is used to index the data inside the table.
Cont’d
• Foreign Key Constraint
• Foreign Key is used to relate two tables.
• The relationship between the two tables matches the Primary Key in one of
the tables with a Foreign Key in the second table.

• This is also called a referencing key.

• We use ALTER statement and ADD statement to specify this constraint.
Cont’d
• Behaviour of Foriegn Key Column on Delete
• There are two ways to maintain the integrity of data in Child table, when a
particular record is deleted in the main table.
• When two tables are connected with Foriegn key, and certain data in the main
table is deleted, for which a record exits in the child table, then we must have
some mechanism to save the integrity of data in the child table.
Cont’d
Cont’d
• CHECK Constraint
• CHECK constraint is used to restrict the value of a column between a range.
• It performs check on the values, before storing them into the database.
• Its like condition checking before saving data into a column.
Cont’d
• What are SQL Functions?
• SQL provides many built-in functions to perform operations on data.
• These functions are useful while performing mathematical calculations, string
concatenations, sub-strings etc.
• SQL functions are divided into two categories,

• Aggregate Functions
• Scalar Functions
Cont’d
• FIRST() Function
• First function returns first value of a selected column

• Syntax for FIRST function is,

• SELECT FIRST(column_name) FROM table-name;

Cont’d
• LAST() Function
• LAST function returns the return last value of the selected column.

• Syntax of LAST function is,

• SELECT LAST(column_name) FROM table-name;

Cont’d
• MAX() Function
• MAX function returns maximum value from selected column of the table.

• Syntax of MAX function is,

• SELECT MAX(column_name) from table-name;

Cont’d
• MIN() Function
• MIN function returns minimum value from a selected column of the table.

• Syntax for MIN function is,

• SELECT MIN(column_name) from table-name;

Cont’d
• SUM() Function
• SUM function returns total sum of a selected columns numeric values.

• Syntax for SUM is,

• SELECT SUM(column_name) from table-name;

Cont’d
• SQL query to find sum of salaries will be,

• SELECT SUM(salary) FROM emp;

Cont’d
• Scalar Functions
• Scalar functions return a single value from an input value. Following are some
frequently used Scalar Functions in SQL.

• UCASE() Function
• UCASE function is used to convert value of string column to Uppercase characters.

• Syntax of UCASE,

• SELECT UCASE(column_name) from table-name;

Cont’d
• LCASE() Function
• LCASE function is used to convert value of string columns to Lowecase
characters.

• Syntax for LCASE is,

• SELECT LCASE(column_name) FROM table-name;

Cont’d
• MID() Function
• MID function is used to extract substrings from column values of string type in
a table.

• Syntax for MID function is,

• SELECT MID(column_name, start, length) from table-name;

Cont’d
• SQL query will be,

• SELECT MID(name,2,2) FROM emp;

Cont’d
• ROUND() Function
• ROUND function is used to round a numeric field to number of nearest
integer. It is used on Decimal point values.

• Syntax of Round function is,

• SELECT ROUND(column_name, decimals) from table-name;

Cont’d
• SQL query is,

• SELECT ROUND(salary) from emp;

Cont’d
• SQL JOIN
• SQL Join is used to fetch data from two or more tables, which is joined to
appear as single set of data.
• It is used for combining column from two or more tables by using values
common to both tables.

• JOIN Keyword is used in SQL queries for joining two or more tables.
• Minimum required condition for joining table, is (n-1) where n, is number of
tables.
• A table can also join to itself, which is known as, Self Join.
Cont’d
• Types of JOIN
• Inner
• Outer
• Left
• Right
Cont’d
• Cross JOIN or Cartesian Product
• This type of JOIN returns the cartesian product of rows from the tables in Join.
• It will return a table which consists of records which combines each row from
the first table with each row of the second table.

• Cross JOIN Syntax is,

SELECT column-name-list
FROM
table-name1 CROSS JOIN table-name2;
Cont’d
• INNER Join or EQUI Join
• This is a simple JOIN in which the result is based on matched data as per the
equality condition specified in the SQL query.

• Inner Join Syntax is,

SELECT column-name-list FROM

table-name1 INNER JOIN table-name2
WHERE table-name1.column-name = table-name2.column-name;
Cont’d
• Natural JOIN
• Natural Join is a type of Inner join which is based on column having same
name and same datatype present in both the tables to be joined.

• The syntax for Natural Join is,

SELECT * FROM
table-name1 NATURAL JOIN table-name2;
Cont’d
• OUTER JOIN
• Outer Join is based on both matched and unmatched data. Outer Joins
subdivide further into,

• Left Outer Join

• Right Outer Join
• Full Outer Join
Cont’d
• SQL Alias - AS Keyword
• Alias is used to give an alias name to a table or a column, which can be a
resultset table too.
• This is quite useful in case of large or complex queries.
• Alias is mainly used for giving a short alias name for a column or a table with
complex names.

• Syntax of Alias for table names,

• SELECT column-name FROM table-name AS alias-name

Cont’d
• SET Operations in SQL
• SQL supports few Set operations which can be performed on the table data.
• These are used to get meaningful results from data stored in the table, under
different special conditions.

• 4 different types of SET operations

• UNION
• UNION ALL
• INTERSECT
• MINUS
Cont’d
• UNION Operation
Cont’d
• UNION ALL
• This operation is similar to Union. But it also shows the duplicate
rows.
Cont’d
• INTERSECT
• Intersect operation is used to combine two SELECT statements, but it
only returns the records which are common from both SELECT
statements.
• In case of Intersect the number of columns and datatype must be
same.
Cont’d
• MINUS
• The Minus operation combines results of two SELECT statements and return
only those in the final result, which belongs to the first set of the result.
Cont’d
• What is an SQL Sequence?
• Sequence is a feature supported by some database systems to produce
unique values on demand.
• Some DBMS like MySQL supports AUTO_INCREMENT in place of Sequence.

• AUTO_INCREMENT is applied on columns, it automatically increments the

column value by 1 each time a new record is inserted into the table.

• Sequence is also some what similar to AUTO_INCREMENT but it has some

additional features too.
Cont’d
• SQL VIEW
• A VIEW in SQL is a logical subset of data from one or more tables. View is
used to restrict data access.
Cont’d
• Types of View
• There are two types of view,

• Simple View
• Complex View

Simple View Complex View

Created from one table Created from one or more table
Does not contain functions Contain functions
Does not contain groups of data Contains groups of data
Two-Tier and Three-Tier Architectures
• What is Database Architecture?
• A Database Architecture is a representation of DBMS design.
• It helps to design, develop, implement, and maintain the database
management system.
Cont’d
• 1-Tier Architecture
• 1 Tier Architecture in DBMS is the simplest architecture of Database in which
the client, server, and Database all reside on the same machine.
• A simple one tier architecture example would be anytime you install a
Database in your system and access it to practice SQL queries.
• But such architecture is rarely used in production.
Cont’d
Cont’d
• 2-Tier Architecture
• A 2 Tier Architecture in DBMS is a Database architecture where the
presentation layer runs on a client (PC, Mobile, Tablet, etc.), and data is stored
on a server called the second tier.
• Two tier architecture provides added security to the DBMS as it is not
exposed to the end-user directly.
• It also provides direct and faster communication.
Cont’d
Cont’d
• 3-Tier Architecture
• A 3 Tier Architecture in DBMS is the most popular client server architecture in
DBMS in which the development and maintenance of functional processes,
logic, data access, data storage, and user interface is done independently as
separate modules.
• Three Tier architecture contains a presentation layer, an application layer, and
a database server.
Cont’d
Internet Database Environment
• Web-Related Terms
• World Wide Web (WWW)
• The total set of interlinked hypertext documents residing on Web servers worldwide
• Browser
• Software that displays HTML documents and allows users to access files and software related
to HTML documents
• Web Server
• Software that responds to requests from browsers and transmits HTML documents to
browsers
• Web pages – HTML documents

• Static Web pages – content established at development time

• Dynamic Web pages – content dynamically generated, usually by obtaining data from
database
Cont’d
• Communications Technology
• IP Address
• Four numbers that identify a node on the internet
• e.g. 131.247.152.18
• Hypertext Transfer Protocol (HTTP)
• Communication protocol used to transfer pages from Web server to browser
• HTTPS is a more secure version
• Uniform Resource Locator (URL)
• Mnemonic Web address corresponding with IP address
• Also includes folder location and html file name
Cont’d

Figure 10-2:
Typical URL
Cont’d
• Internet-Related Languages
• Hypertext Markup Language (HTML)
• Markup language specifically for Web pages
• Java
• Object-oriented programming language for applets
• JavaScript
• Scripting languages that enable interactivity in HTML documents
• Cascading Style Sheets (CSS)
• Control appearance of Web elements in an HML document
Cont’d
• World Wide Web Consortium (W3C)
• An international consortium of companies working to develop open standards
that foster the deployment of Web conventions so that Web documents can
be consistently displayed on all platforms
• See www.w3c.org
Cont’d
• Web Servers
• Provide HTTP service
• Passing plain text via TCP connection
• Serve many clients at once
• Therefore, multithreaded and multiprocessed
• Load balancing approaches:
• Domain Name Server (DNS) balancing
• One DNS = multiple IP addresses
• Software/hardware balancing
• Request at one IP address is distributed to multiple servers
• Reverse proxy
• Intercept client request and cache response
Introduction to Data Warehousing
• Data Warehousing
• Data Warehousing may be defined as a collection of corporate information
and data derived from operational systems and external data sources.
• A data warehouse is designed with the purpose of inducing business decisions
by allowing data consolidation, analysis, and reporting at different aggregate
levels.
• Data is populated into the DW by extraction, transformation, and loading.
Data Quality & Db Administration
• Data administrator
• A data administration (also known as a database administration manager,
data architect, or information center manager) is a high level function
responsible for the overall management of data resources in an organization.
• In order to perform its duties, the DA must know a good deal of system
analysis and programming.
Cont’d
• Database adminstrator
• Database administration is more of an operational or technical level function
responsible for physical database design, security enforcement, and database
performance.
• Tasks include maintaining the data dictionary, monitoring performance, and
enforcing organizational standards and security.
Object-Oriented Databases
• Object-oriented database is a database management system in which
information is represented in the form of objects as used in object-
oriented programming.