FAIRFIELD Institute of Management and Technology

LAB FILE
Information System Management
(BBA-212)

Submitted To: Submitted By:

Ms. Pooja Yadav Ajay Dhoundiyal

Asst. Professor 41951401718
IT Department BBA(G) Sem-4

1
Ajay Dhoundiyal 41951401718
 TABLE OF CONTENTS
Serial Name of Program Signature Remarks
No.
1 Introduction to relational
database management system.

2 Introduction to structure query

language.

3 To illustrate data definition

language commands in
RDBMS.

4 To illustrate data manipulation

language command in RDBMS
5 To illustrate create table
command in sql.

6 To illustrate insert values

command in sql.

7 To illustrate select table

command in sql.

8 To illustrate update table

command in sql.

9 To illustrate alter command in

sql.

2
Ajay Dhoundiyal 41951401718
10 To illustrate delete command in
sql.

11 To illustrate drop table command

in sql.

12 To illustrate difference between

delete & drop table command in
sql.

13 To illustrate rename command in

sql.

14 To illustrate integrity constraint

in sql.
15 To illustrate relational model in
DBMS

16 To illustrate Aggregate Functions

in SQL.
17 Introduction to Entity
Relationship Model in Database
Management System
18 Database design using Entity
Relationship Model.
19 Draw an ER diagram for
Banking system.

20 Draw an ER diagram for

Library Management system.

3
Ajay Dhoundiyal 41951401718
INTRODUCTION TO RELATIONAL DATABASE MANAGEMENT
SYSTEM
What is Database?

A database is an organized collection of data, generally stored and accessed electronically from a
computer system. Where databases are more complex they are often developed using formal design
and modelling techniques.

What is Database Management System?

The database management system (DBMS) is the software that interacts with end users, applications,
and the database itself to capture and analyze the data. The DBMS software additionally
encompasses the core facilities provided to administer the database. The sum total of the database,
the DBMS and the associated applications can be referred to as a "database system". Often the term
"database" is also used to loosely refer to any of the DBMS, the database system or an application
associated with the database.

Relational Database
A relational database is a digital database based on the relational model of data, as proposed by E.
F. Codd in 1970. A software system used to maintain relational databases is a relational database
management system (RDBMS). Many relational database systems have an option of using the SQL
(Structured Query Language) for querying and maintaining the database.

The term "relational database" was invented by E. F. Codd at IBM in 1970. Codd introduced the term
in his research paper "A Relational Model of Data for Large Shared Data Banks". In this paper and
later papers, he defined what he meant by "relational". One well-known definition of what constitutes
a relational database system is composed of Codd's 12 rules. However, no commercial
implementations of the relational model conform to all of Codd's rules so the term has gradually come
to describe a broader class of database systems, which at a minimum:

1. Present the data to the user as relations (a presentation in tabular form, i.e. as a collection of
tables with each table consisting of a set of rows and columns);
2. Provide relational operators to manipulate the data in tabular form.
In 1974, IBM began developing System R, a research project to develop a prototype RDBMS.
However, the first commercially available RDBMS was Oracle, released in 1979 by Relational
Software, now Oracle Corporation. Other examples of an RDBMS include DB2, SAP Sybase ASE,
and Informix. In 1984, the first RDBMS for Macintosh began being developed, code-named Silver
Surfer, it was later released in 1987 as 4th Dimension and known today as 4D.
The first systems that were relatively faithful implementations of the relational model were from:

4
Ajay Dhoundiyal 41951401718
• University of Michigan – Micro DBMS (1969)
• Massachusetts Institute of Technology (1971)
• IBM UK Scientific Centre at Peterlee – IS1 (1970–72) and its successor, PRTV (1973–79)
The first system sold as an RDBMS was Multics Relational Data Store (1978). Ingres and IBM BS12
followed.
The most common definition of an RDBMS is a product that presents a view of data as a collection of
rows and columns, even if it is not based strictly upon relational theory. By this definition, RDBMS
products typically implement some but not all of Codd's 12 rules.
A second school of thought argues that if a database does not implement all of Codd's rules (or the
current understanding on the relational model, as expressed by Christopher J. Date, Hugh Darwen and
others), it is not relational. This view, shared by many theorists and other strict adherents to Codd's
principles, would disqualify most DBMSs as not relational. For clarification, they often refer to some
RDBMSs as truly-relational database management systems (TRDBMS), naming others pseudo-
relational database management systems (PRDBMS).
As of 2009, most commercial relational DBMSs employ SQL as their query language.
Alternative query languages have been proposed and implemented, notably the pre-1996
implementation of Ingres QUEL.

5
Ajay Dhoundiyal 41951401718
 Structure Query
Language

6
Ajay Dhoundiyal 41951401718
SQL Introduction

SQL stands for “Structured Query Language” and can be pronounced as “SQL”
or “sequel – (Structured English Query Language)”.

It is a query language used for accessing and modifying information in the database.
IBM first developed SQL in 1970s. Also it is an ANSI/ISO standard. It has become
a Standard Universal Language used by most of the relational database management
systems (RDBMS). Some of the RDBMS systems are: Oracle, Microsoft SQL
server, Sybase etc. Most of these have provided their own implementation thus
enhancing its feature and making it a powerful tool.

Few of the sql commands used in sql programming are SELECT Statement,
UPDATE Statement, INSERT INTO Statement, DELETE Statement, WHERE
Clause, ORDER BY Clause,.

In a simple manner, SQL is a non-procedural, English-like language that processes

data in groups of records rather than one record at a time. Few functions of SQL are:

• store data
• modify data
• retrieve data
• modify data
• delete data
• create tables and other database objects
• delete data

Types of SQL statements

There are three basic types of SQL statements:

• Data definition language (DDL) statements

• Data manipulation language (DML) statements
• Data Control Language (DCL) statements

7
Ajay Dhoundiyal 41951401718
DATA TYPES OF SQL

1. CHAR : This data type is used to store character strings values of fixed length. The size in
brackets determines the number of characters the cell can hold. The maximum number of
characters (i.e. the size) this data type can hold is 255 characters. Syntax is

CHAR(SIZE)

Example is CHAR (20)

2. VARCHAR : This data type is used to store variable length alphanumeric data. The maximum
this data type can hold is 4000 characters. One difference between this data type and the CHAR
data type is ORACLE compares VARCHAR values using non-padded comparison semantics
i.e. the inserted values will not be padded with spaces. Syntax is VARCHAR(SIZE) Example
is VARCHAR (20) OR VARCHAR2 (20)

3.NUMBER : The NUMBER data type is used to store numbers (fixed or floating point).

Syntax is NUMBER (P, S) Example is NUMBER (10, 2)

4. DATE : This data type is used to represent data and time. The standard format id DD-MM-YY
as in 13-JUL-85. To enter dates other than the standard format, use the appropriate functions. Date
Time stores date in the 24-hour format. By default, the time in a date field is 12:00:00 am, if no
time portion is specified. The default date for a date field is the first day of the current month.
Syntax is DATE

8
Ajay Dhoundiyal 41951401718
Create a Table
To create a new table within a database, we use the SQL CREATE TABLE
statement Syntax:

CREATE TABLE <table name>

<table element>, <table

element>,

);

Example:

Create table student

(

Student_id char (10),

Student_Fname varchar (10),

Student_Lname varchar(10),

Student_address varchar(15),

Student_dob datetime

9
Ajay Dhoundiyal 41951401718
Insert values into Table
INSERT INTO `table_name` is the command to add new row into a table .

Syntax:

Insert into <table_name> values (‘<value>’, <value> , ……….);

Example:

Insert into student values (‘001’, ‘sanjeev’, ‘gupta’, ‘delhi’, ’04-12-2010’);

10
Ajay Dhoundiyal 41951401718
 Retrieving Data from Table
1. A Select statement is a SQL statement that begins with the word "select."
2. Select statements are used to retrieve data from SQL tables.
3. An asterisk after the word "select" means retrieve all fields (columns).
4. The name of the table from which you are retrieving data is specified in the From clause.

11
Ajay Dhoundiyal 41951401718
Syntax:

Select * from <table name>

Retrieve some specific data.

Select <table element > from <table name> where <condition>

Example

Select * from teacher where teacher_id=’002’

10

Ajay Dhoundiyal 41951401718

UPDATING THE CONTENTS OF A TABLE: -
The update command is used to
change or modify data values in a table. The verb UPDATE in SQL is used to either
all the rows from a table or a selected set of rows from a table.

UPDATING ALL ROWS:- The update statement updates columns in the existing
table’s rows with new values .The SET clause indicates which column data should
be modifying and the new values that they should hold. The WHERE CLAUSE
specifies which rows should be updated. Otherwise all table rows are updated.

Example:

Update student set student_address = ‘rohini’ where student_id=101;

13
Ajay Dhoundiyal 41951401718
Alter Table
The SQL ALTER TABLE command is used to add, delete or modify columns in an existing table.

Syntax:

Alter table <table name>

Add <table element> Example:

Alter table customer

Add cust_id varchar (20)

Delete Record (Rows) from Table:

The SQL DELETE Query is used to delete the existing records from a table.

14
Ajay Dhoundiyal 41951401718
You can use the WHERE clause with a DELETE query to delete the selected rows, otherwise all
the records would be deleted.

Syntax
The basic syntax of the DELETE query with the WHERE clause is as follows −
DELETE FROM table_name
WHERE [condition];

Example : Delete from student where student_id=101 ;

Drop table
Syntax:

Drop table <table name> Example:

Drop table student;

The DROP TABLE statement removes a table and its data permanently from the
database.

RENAMING TABLES: - Oracle allows renaming of tables. The rename operation

is done atomically, which means that no other thread can access any of the tables
while the rename process is running.

Syntax: - RENAME <Table name> to <New Tablename>

Example:-

SQL> rename student to candidates;

SQL Constraints

15
Ajay Dhoundiyal 41951401718
Constraints are used to limit the type of data that can go into a table.

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

We will focus on the following constraints:

• NOT NULL
• UNIQUE
• PRIMARY KEY
• FOREIGN KEY
• CHECK
• DEFAULT

SQL NOT NULL Constraint

The NOT NULL constraint enforces a column to NOT accept NULL values.

The NOT NULL constraint enforces a field to always contain a value. This means
that you cannot insert a new record, or update a record without adding a value to this
field.

SQL UNIQUE Constraint

The UNIQUE constraint uniquely identifies each record in a database table.

The UNIQUE and PRIMARY KEY constraints both provide a guarantee for
uniqueness for a column or set of columns.
A PRIMARY KEY constraint automatically has a UNIQUE constraint defined on it.

SQL PRIMARY KEY Constraint

The PRIMARY KEY constraint uniquely identifies each record in a database table.

16
Ajay Dhoundiyal 41951401718
Primary keys must contain unique values.

A primary key column cannot contain NULL values.

Each table should have a primary key, and each table can have only ONE primary
key.

To illustrate relational model in DBMS

The relational model represents the database as a collection of relations. A relation is

nothing but a table of values. Every row in the table represents a collection of related
data values. These rows in the table denote a real-world entity or relationship.

The table name and column names are helpful to interpret the meaning of values in each
row. The data are represented as a set of relations.

Some popular Relational Database management systems are:

17
Ajay Dhoundiyal 41951401718
 • DB2 and Informix Dynamic Server - IBM
• Oracle and RDB – Oracle
• SQL Server and Access – Microsoft

Aggregate functions in SQL

SQL is excellent at aggregating data the way we might in a pivot table in Excel. We
will use aggregate functions all the time, so it's important to get comfortable with them.
The functions themselves are the same ones you will find in Excel or any other analytics
program. We'll cover them individually in the next few lessons. Here's a quick preview:

• COUNT counts how many rows are in a particular column.

• SUM adds together all the values in a particular column.
• MIN and MAX return the lowest and highest values in a particular column,
respectively. • AVG calculates the average of a group of selected values.

18
Ajay Dhoundiyal 41951401718
Database Design using Entity Relationship Model
Relational Model Concepts

1. Attribute: Each column in a Table. Attributes are the properties which define a
relation. e.g., Student_Rollno, NAME,etc.
2. Tables – In the Relational model the, relations are saved in the table format. It is
stored along with its entities. A table has two properties rows and columns. Rows
represent records and columns represent attributes.
3. Tuple – It is nothing but a single row of a table, which contains a single record.
4. Relation Schema: A relation schema represents the name of the relation with its
attributes.
5. Degree: The total number of attributes which in the relation is called the degree
of the relation.
6. Cardinality: Total number of rows present in the Table.
7. Column: The column represents the set of values for a specific attribute.
8. Relation instance – Relation instance is a finite set of tuples in the RDBMS
system. Relation instances never have duplicate tuples.
9. Relation key - Every row has one, two or multiple attributes, which is called
relation key.
10. Attribute domain – Every attribute has some pre-defined value and scope which
is known as attribute domain

19
Ajay Dhoundiyal 41951401718
Operations in Relational Model
Four basic update operations performed on relational database model are

Insert, update, delete and select.

• Insert is used to insert data into the relation

• Delete is used to delete tuples from the table.
• Modify allows you to change the values of some attributes in existing tuples.
• Select allows you to choose a specific range of data.

20
Ajay Dhoundiyal 41951401718
What is ER Modeling?
Entity Relationship Modeling (ER Modeling) is a graphical approach to database design. It
uses Entity/Relationship to represent real world objects.

An Entity is a thing or object in real world that is distinguishable from surrounding

environment. For example each employee of an organization is a separate entity.

21
Ajay Dhoundiyal 41951401718
 ER DIAGRAM OF LIBRARY MANAGEMENT SYSTEM

22
Ajay Dhoundiyal 41951401718