Guru Gobind Singh Indraprastha University

Sector -16 C, Dwarka, Delhi, India

‘INFORMATION SYSTEM MANAGEMENT’

SUBMITTED IN PARTIAL FULFILMENT OF THE REQUIREMENT TO
ATTEND THE DEGREE OF
BACHELOR OF BUSINESS ADMINISTRATION
(2022-2025)
Practical- ISM Lab
By

Name: KARAN SINGH

Enrolment No: 01124301722

Under the guidance:

Dr. ANUKOOL BAJPAI

Principal

Sirifort Institute of Management Studies

Plot No.8, Institutional Area Rohini Sector-25, New Delhi 110085.
1
 CERTIFICATE

This is to certify that ISM Practical lab File is the original work of KARAN SINGH
(01124301722) student of BBA(G) 5th semester and has duly completed her project under my
guidance and supervision to my satisfactory level. This work has been done in partial
fulfillment of the requirement for the award of the degree of Bachelor of Business
Administration from SIRIFORT INSTITUTE OF MANAGEMENT STUDIES, GGSIPU and
has not been submitted in any other university for the award of any degree.

MR. ANUKOOL BAJPAI KARAN SINGH

(PRINCIPAL) (01124301722)

2
 ACKNOWLEDGMENT

It is not possible to prepare the project report without the assistance and encouragements
of other people. This one is certainly no exception.
On the very outset of this report, I would like to extend my sincere and heartfelt obligation
towards all the personages who have helped me in this endeavor. Without their active
guidance, help, cooperation and encouragement. I would not have headway in the project.
I am extremely thankful and pay my gratitude to my faculty MR. ANUKOOL BAJPAI for
his valuable guidance and support on completion of this project in it presently.
I also acknowledge with a deep sense of reverence, my gratitude towards my parents and
my friends, who has always supported me morally.
Any omission in this brief acknowledgment does not mean lack of gratitude.

Thanking You
KARAN SINGH

3
 INDEX

S.NO CONTENT PAGE SIGN

NO.
1 Introduction of SQL and Data type 5-9
2 SQL DDL Command system 10-12
3 SQL DML commands g 13-14
4 Create table customer (c_id, c_name, address, city, picode, 14-15
country) insert at least 10 values. Display the table
5 Create table named employee containing columns (emp_id, 15-16
emp_name, emp_desig, dob, emp_sal, emp_dept) insert 10 at
least values. Constraints applied on employee table
A. emp_id should be primary key
B. emp_sal should not contain any null value
C. emp_desig should be unique
6 Using the table of experiment 5, perform various SQL 17-22
Commands and Display table
A. Add column in above table date of joining, experience
B. Complete the table definition
C. Delete the column DESG
D. Find out details of an employee whose salary is above
25000

7 Create the following table and perform SQL Commands 22-25

STUDENT (ROLL_NO, NAME, AGE,
COURSE, MARKS)
A. Select * from students where age is>18 and course
=MBA
B. Find out total number of records in table
C. Display name and course of student
D. Find out the average of all the marks. Display it as
average marks.
8 What do you understand by ER Modelling and explain all the 26-31
symbols of ER model with examples?
9 What is relationship and cardinality in ER model? Explain 32-37
with examples
10 Diagram of Organisation 38
11 Diagram of banking system 39
12 Diagram of hospital management 40
13 Diagram of a University 41

4
Question 1: Introduction to SQL. Explain what different types of data types in SQL are.
SQL (Structured Query Language) is a domain-specific language used in programming and designed for
managing data held in a relational database management system (RDBMS), or for stream processing in a
relational data stream management system (RDSMS). It is particularly useful in handling structured data, i.e.
data incorporating relations among entities and variables.

SQL (Structured Query Language) is a database sublanguage for querying and modifying relational databases. It
was developed by IBM Institutes)
in 1986.
The Structured Query Language (SQL) is a language of databases. All modern relational databases, including
Access, FileMaker pro, Microsoft SQL Server and Oracle use SQL as their basic building block. All of the
graphical user interfaces that provide data entry and manipulation functionality are nothing more than SQL
translators. They take the actions you perform graphically and convert them to SQL commands understood by
the database. Using SQL, you can insert records, update records, and delete records. You can also create new
database objects such as databases and tables. And you can drop (delete) them.

SQL offers two main advantages over older read write APIs such as ISAM or VSAM. Firstly, it introduced the
concept of accessing many records with one single command. Secondly, it eliminates the need to specify how to
reach a record, e.g. with or without an index.

Originally based upon relational algebra and tuple relational calculus, SQL consists of many types of statements,
which may be informally classed as sub languages, commonly: a data query language (DQL), a data definition
language (DDL), a data control language (DCL), and a data manipulation language (DML). The scope of SQL
includes data query, data manipulation (insert, update and delete), data definition (schema creation and
modification), and data access control. Although SQL is essentially a declarative language (4GL), it includes also
procedural elements.

SQL was one of the first commercial languages to utilize Edgar F.Codd relational model. The model was
described in his influential 1970 paper, "A Relational Model of Data for Large Shared Data Banks". Despite not
entirely adhering to the relational model as described by Codd, it became the most widely used database language.

History

SQL was initially developed at IBM by Donald D. Chamberlin and Raymond F. Boyce after learning about the
relational model from Ted Codd in the early 1970s. This version, initially called SEQUEL (Structured English
Query Language), was designed to manipulate and retrieve data stored in IBM's original quasirelational

5
database management system, System R, which a group at IBM San Jose Research Laboratory had developed
during the 1970s.

Chamberlin and Boyce's first attempt of a relational database language was Square, but it was difficult to use
due to subscript notation. After moving to the San Jose Research Laboratory in 1973, they began work on
SEQUEL. The acronym SEQUEL was later changed to SQL because "SEQUEL" was a trademark of the UK-
based Hawker Siddeley Dynamics Engineering Limited company. After testing SQL at customer test sites to
determine the usefulness and practicality of the system, IBM began developing commercial products based on
their System R prototype including System/38, SQL/DS, and DB2, which were commercially available in
1979, 1981, and 1983, respectively.

In the late 1970s, Relational Software, Inc. (now Oracle Corporation) saw the potential of the concepts
described by Codd, Chamberlin, and Boyce, and developed their own SQL-based RDMS with aspirations of
selling it to the U.S. Navy, Central Intelligence Agency, and other U.S. government agencies. In June 1979,
Relational Software, Inc. introduced the first commercially available implementation of SQL, Oracle V2
(Version2) for VAX computers.

By 1986, ANSI and ISO standard groups officially adopted the standard "Database Language SQL" language
definition. New versions of the standard were published in 1989, 1992, 1996, 1999, 2003, 2006, 2008, 2011]
and, most recently, 2016.

Design

SQL deviates in several ways from its theoretical foundation, the relational model and its tuple calculus. In that
model, a table is a set of tuples, while in SQL, tables and query results are lists of rows: the same row may
occur multiple times, and the order of rows can be employed in queries (e.g. in the LIMIT clause).Critics argue
that SQL should be replaced with a language that returns strictly to the original foundation: for example, see
The Third Manifesto. However, no known proof exists that such uniqueness cannot be added to SQL itself, or at
least a variation of SQL. In other words, it's quite possible that SQL can be "fixed" or at least improved in this
regard such that the industry may not have to switch to a completely different query language to obtain
uniqueness. Debate on this remains open.

What Can SQL do?

SQL can execute queries against a database.

SQL can retrieve data from a database.
SQL can insert records in a database.
SQL can update records in a database.
SQL can delete records from a database.
6
SQL can create new databases.
SQL can create new tables in a database.
SQL can create stored procedures in a database.
SQL can create views in a database.
SQL can set permissions on tables, procedures, and views.

Different data type in sql

SQL Data Types define the type of value that can be stored in a table column. For example, if we want a column
to store only integer values, then we can define its data type as int.

SQL Data Types

SQL data types can be broadly divided into following categories.

1. Numeric data types such as int, tinyint, bigint, float, real etc.
2. Date and Time data types such as Date, Time, Datetime etc.
3. Character and String data types such as char, varchar, text etc.
4. Unicode character string data types, for example nchar, nvarchar, ntext etc.
5. Binary data types such as binary, varbinary etc.
Miscellaneous data types clob, blob, xml, cursor, table etc.

7
8
The following table lists the general data types in SQL: -
CHARACTER(n)- Character string. Fixed-length n
VARCHAR(n) or CHARACTER VARYING(n)- Character string. Variable length. Maximum
length n
BINARY(n)- Binary string. Fixed-length n
BOOLEAN- Stores TRUE or FALSE values
VARBINARY(n) or BINARY VARYING(n)- Binary string. Variable length. Maximum length n
INTEGER(p)- Integer numerical (no decimal). Precision p
SMALLINT- Integer numerical (no decimal). Precision 5
EIGINT- Integer numerical (no decimal). Precision 19

9
Question 2: Explain SQL DDL Commands. With Syntax and Examples.

The Data Definition Language (DDL) is used to create and destroy databases and databases objects. These
SQL statements define the structure of a database, including rows, columns, tables, indexes, and databases
specifics such as file locations.
CREATE To create objects in the database.
ALTER Alters the structure of the database.

DROP Delete objects from the databases.

TRUNCATE is used to remove all records from a table, including all spaces allocated for the records
are removed.
COMMENT is used to add comments to the data dictionary.
RENAME is used to rename an object existing in the database. The create
table command: -
The create table command defines each column of the table uniquely. Each column has minimum of three
attributes.
• Name
• Data type
• Size (column width).
Each table column definition is a single clause in the create table syntax. Each table column definition is
separated from the other by a comma. Finally, the SQL statement is terminated with a semicolon.
The Structure of Create Table Command Table
name is Student
Column name Data type Size

Reg_no varchar2 10

Name Char 30

DOB Date

Address varchar2 50

Example:
CREATE TABLE Student

10
(Reg_no varchar2(10),
Name char (30),
DOB date,

Address varchar2(50));

The DROP Command: -

Syntax:
DROP TABLE <table_name>

Example:
DROP TABLE Student;

It will destroy the table and all data which will be recorded in it.

The TRUNCATE Command: -

Syntax:
TRUNCATE TABLE <Table_name>

Example:
TRUNCATE TABLE Student;

The RENAME Command: -

Syntax:
RENAME <OldTableName> TO <NewTableName>

Example:
RENAME <Student> TO <Stu>

The old name table was Student now new name is the Stu.

The ALTER Table Command: -

By The use of ALTER TABLE Command, we can modify our exiting table.

Adding New Columns

Syntax:

ALTER TABLE <table_name>

11
ADD (<NewColumnName> <Data_Type>(<size>),......n)

Example:
ALTER TABLE Student ADD (Age number(2), Marks number(3));

The Student table is already existing and then we added two more columns Age and Marks respectively, by
the use of above command.

Dropping a Column from the Table

Syntax:

ALTER TABLE <table_name> DROP COLUMN <column_name> Example:

ALTER TABLE Student DROP COLUMN Age

This command will drop particular column

Modifying Existing Table

Syntax:
ALTER TABLE <table_name> MODIFY (<column_name> <NewDataType>(<NewSize>))

Example:
ALTER TABLE Student MODIFY (Name Varchar2(40));

The Name column already exist in Student table, it was char and size 30, now it is modified by Varchar2 and
size 40.
Restriction on the ALTER TABLE
Using the ALTER TABLE clause the following tasks cannot be performed.
Change the name of the table
Change the name of the column
Decrease the size of a column if table data exists .

12
13
SET Syntax

DELETE from table WHERE condition;

Example

The following example delete the record where EID equals to 10 from the Employee table, DELETE from
Employee WHERE EID=10;

Question 4: Create a table named Customer (C_id, C_name, Address, city, pin code, Country). Insert at least
10 values. Display the table.

create database db1; use

db1;
Create table customer (customer_id int, customer_name varchar(20), address varchar(50), city
varchar(20), pin code int, country varchar (20));

➢ insert into customer value(01, 'pradip', '2/22 shastri park', 'New delhi', 110212, 'India');
➢ insert into customer value(02, 'ishan', '2/23 rohini', 'New delhi', 110213, 'India');
➢ insert into customer value(03, 'gaurav', '2/24 rohini sec 10', 'New delhi', 110214,
'India');
➢ insert into customer value(04, 'nikhil', '2/25 welcome', 'New delhi', 110215, 'India');
➢ insert into customer value(05, 'sagar', '2/26 tila mor', 'ghaziabad', 110216, 'India');
➢ insert into customer value(06, 'pranav', '2/27 dilshad garden', 'New delhi', 110216,
'India');
➢ insert into customer value(07, 'naman', '2/28 welcome', 'New delhi', 110217, 'India');
➢ insert into customer value(08, 'himanshu', '2/29 dwarka', 'New delhi', 110218, 'India');
➢ insert into customer value(09, 'vishal', '2/30 rohini sec 24', 'New delhi', 110219,
'India');
➢ insert into customer value(10, 'faiz', '2/31 kalkaji', 'New delhi', 110220, 'India');
select * from customer;

14
Question 5: Create table named Employee containing columns (emp_id, emp_name, emp_desig, DOB, emp_sal,
emp_dept). Insert atleast 10 values.

Constraints applied on Employee table:

1. Emp_id should be primary key

2. Emp_sal should not contain any Null Value
3. Emp_desig should be Unique

create database emp1; use

emp1;
create table employee(emp_id int primary key, emp_name varchar(20), emp_desig varchar(30)
unique, DOB date, emp_sal int not null, emp_dept varchar(50));

15
 insert into employee values(01, 'AB', 'marketing manager', '1990/01/10', 50000, 'marketing');
insert into employee values(02, 'BA', 'HR manager', '1991/02/11', 60000, 'HR'); insert into
employee values(03, 'CD', 'finance manager', '1992/03/12', 70000, 'finance'); insert into
employee values(04, 'DE', 'production manager', '1993/04/13', 80000, 'production'); insert into
employee values(05, 'EF', 'investment manager', '1994/05/14', 90000, 'investment'); insert into
employee values(06, 'FG', 'sales manager', '1995/06/15', 100000, 'sales'); insert into employee
values(07, 'GH', 'purchase manager', '1996/07/16', 110000, 'purchase'); insert into employee
values(08, 'HI', 'Research manager', '1997/08/17', 120000, 'Research'); insert into employee
values(09, 'IJ', 'Promotion manager', '1998/09/18', 130000, 'promotion'); insert into employee
values(10, 'JK', 'Operations manager', '1999/10/19', 140000, 'operational');

16
Question 6: Create Employee table: -

EID, ENAME, DESG, BRANCH, SALARY, ADDRESS.

Insert 10 Records.

*salary Column should not contain any Null value

*Salary should be between 5000 to 350000

*ENAME should be Unique

Perform various SQL commands

1. Add column in above table date of joining, Experience.

2. Display the Table
3. Complete the Table Definition
4. Delete the Column DESG
5. Find out details of employee whose salary is above 25000.
6. Find out details of employee order by salary.
7. Calculate total no. of records in employee table.
8. List Employees whose salary is between 10000 and 30000.

use database emp; use

emp;
create table employee(eid int, ename varchar(20) unique, desg varchar(30), branch varchar(50), salary
int not null check (salary<350000 and salary>5000), address varchar(50));
insert into employee values(1, 'Randhir', 'assistant manager', 'indirapuram', 40000, 2/210
insert into employee values(2, 'Pankaj', 'hr manager', 'mohan nagar', 40000, '2/211 near w
insert into employee values(3, 'Rajesh', 'worker', 'dilshad garden', 20000, '2/212 Pocket
insert into employee values(4, 'Anirudh', 'hr', 'rohini', 50000, '2/212 Sector 5'); insert into
employee values(5, 'Raj', 'manager', 'GTB nagar', 60000, '2/213 hudson lane'); insert into
employee values(6, 'Nitin', 'hr', 'noida', 70000, '2/212 sector 16'); insert into employee
values(7, 'Niranjan', 'factory head', 'Netaji subhash place', 80000, insert into employee
values(8, 'Abhishek', 'branch head', 'Kohat', 120000, 2/223); insert into employee

17
values(9, 'Vivek', 'finance manager', 'Shalimar garden', 100000, 2/24 insert into employee
values(10, 'Abhay', 'factory head', 'shastri nagar', 110000, 2/248);

18
 select * from employee;

Add column in above table date of joining, Experience.

alter table employee add column experience date;

1. Display the Table

19
 2. Complete the Table Definition

update employee set experience= '1990/12/12';

3. Delete the Column DESG

alter table employee drop desg;

4. Find out details of employee whose salary is above 25000. select * from employee where

salary>25000;

20
5. Find out details of employee order by salary. select * from employee order by salary;

6. Calculate total no. of records in employee table.

select count(eid) from employee;

7. List Employees whose salary is between 10000 and 30000.

21
 select * from employee where salary between 10000 and 30000;

Question 7: Create the following table and perform SQL commands. Student (Roll_no, name, age, course,
marks).

1. List all those students who are greater than 18 years of age and have opted for MBA course.
2.
3. Find out total number of records in table.
4. Find out the name, course, marks and sort in the order of marks.
5. Display name and course of student.
6. Find the Student with Max marks.
7. List the name of students ORDER BY Roll_no in Descending order.
8. Find out the Average of all the Marks. Display it as Average_Marks.

create database st1; use st1; create table student(roll_no int, name varchar(20), age int,
course varchar(10), marks int);
insert into student values(1 'pradip', 20, 'mba', 90); insert
into student values(2, 'ishan', 19, 'bba', 80); insert into
student values(3, 'gaurav', 20, 'mba', 85); insert into
student values(4, 'nikhil', 21, 'bcom', 75); insert into
student values(5, 'aditi', 18, 'bba', 60); select * from
student;

1. List all those students who are greater than 18 years of age and have opted for MBA
course.
22
select * from student where age>18 and course= 'mba';

2.

select * from student where name like 'i%';

3. Find out total number of records in table.

select count(roll_no) from student;

23
 4. Find out the name, course, marks and sort in the order of marks.

select name, course, marks from student order by marks;

5. Display name and course of student.

select name, course from student;

6. Find the Student with Max marks.

select max(marks), name from student;

24
 7. List the name of students ORDER BY Roll_no in Descending order.

select name from student order by roll_no desc;

8. Find out the Average of all the Marks. Display it as Average_Marks

select avg(marks) from student;

25
Question8: Create the Following Tables. Insert at least 10 records in each.
Table 1: Supplier (S_No, Sname, Status, City)
Table 2: Parts (P_No, Pname, Color, Weight, City)
Table 3: SP (S_No, P_No, Quantity)
Answer the following queries in SQL:
a) Find the supplier for the city = ‘DELHI’
b) Find the suppliers whose name starts with ‘AB’
c) Find all suppliers whose status is 10, 20 or 30
d) Find total number of cities of all suppliers
e) Find the name of suppliers who supplies quantity of the item P1 more than 50
ANSWER
• Create database dat;
• Use dat;
• create table supplier(s_no int primary key, s_name varchar(20), status int, city varchar(30));

• insert into supplier values(10, 'aman', 20, 'delhi');

• insert into supplier values(20, 'bhavna', 30, 'aligarh');
insert into supplier values(30, 'sheetal', 60, 'aligarh');

• insert into supplier values(40, 'Abhishek', 30, 'mumbai'

• insert into supplier values(50, 'pradeep', 20
'mumbai'); insert into supplier values(60, 'sagar', 50,
'delhi'); insert into supplier values(70, 'ishan', 30,
'goa'); insert into supplier values(80, 'Gaurav', 70,
'Mathura'); insert into supplier values(90, 'Nikhil', 40,
'Ambala');

26
27
28
29
1

2.

3.

30
4. Find all suppliers whose status is 10, 20 or 30
Select s_name from supplier where status in (10,20,30);

5. Find the name of suppliers who supplies quantity of the item P1 more than 50

select s_name from supplier, sp where supplier.s_no=sp.s_no and sp.p_no='p1' and sp.quantity>50;

31
Question9: What do you understand by ER Modelling? Explain all the symbols of ER Model with examples.

An entity relationship diagram (ERD), also known as an entity relationship model, is a graphical representation
of an information system that depicts the relationships among people, objects, places, concepts or events within
that system. An ERD is a data modelling technique that can help define business processes and be used as the
foundation for a relational database.

There are three basic components of an entity relationship diagram:

1. Entities, which are objects or concepts that can have data stored about them.

2. Attributes, which are properties or characteristics of entities. An ERD attribute can be denoted as a
primary key, which identifies a unique attribute, or a foreign key, which can be assigned to multiple
attributes.

3. The relationships between and among those entities.

An entity relationship diagram showing relationships between sales reps, customers and product orders.

For example, an ERD representing the information system for a company's sales department might start with
graphical representations of entities such as the sales representative, the customer, the customer's address, the
customer's order, the product and the warehouse. Then lines or other symbols can be used to represent the
relationship between entities, and text can be used to label the relationships.

A cardinality notation can then define the attributes of the relationship between the entities. Cardinalities can

denote that an entity is optional (for example, a sales rep could have no customers or could have many) or

mandatory (for example, there must be at least one product listed in an order.) The three main cardinalities are:

1. A one-to-one relationship (1:1). For example, if each customer in a database is associated with one
mailing address.
2. A one-to-many relationship (1: M). For example, a single customer might place an order for multiple
products. The customer is associated with multiple entities, but all those entities have a single
connection back to the same customer.

32
33
34
35
36
37
 Question10: Draw Entity Relationship diagram of an Organization.

Entity: A definable thins such as person, abject, concept or event that can have data stored about it think of
entities as noun.
In this ERD of organization we have three Entities: - Department, Project, Employee.
Attributes: A property or characteristic of an entity. Often shown as an oval.

In this ERD of organization: -

Department has: - Name, D_no, Location

Project has: - P_Name, P_Duration, P_No.
Employee: -Name, DOB, Gender, Phone_No. Email.

Relationship: - Relationships are typically shown as diamonds on the connecting lines. In this ERD of
organization we have four relationships: - works for, supervise, control.

PRIMARY KEY: - The field that is unique for all the record occurrences.it denotes as Underline. In this ERD
we have D_no in as primary key in Department, P_no in Project and Email in Employee.

38
 .

39
Question12: Draw Entity Relationship diagram of a Hospital Management System.

Entity: A definable thins such as person, Object, concept or event that can have data stored about it think of
entities as noun.
In this ERD we have five Entities: - Patient, Medical records, Hospital, Doctor, Attributes: A
property or characteristic of an entity. Often shown as an oval.

In this ERD: -
Patient: - P. Name, P.Id, P. Address, P.Diagnosis Hospital: -
Hos.Id, Hos.Name, Hos.No.
Medical records: - R.No, Room. Charge, Doc.Charge
Doctor:-Doc_id, Doc_Name, Salary, Qualification

Relationship: - Relationships are typically shown as diamonds on the connecting lines. In this ERD of
organization we have four relationships: -Has, admitted in, has

PRIMARY KEY: - The field that is unique for all the record occurrences.it denotes as Underline. In this ERD
we have P-id in patient, Hos-id in Hospital, R.No in Medical record, Doc-id in Doctor.

40
Question13: Draw Entity Relationship diagram of a University.

Entity: A definable thins such as person, Object, concept or event that can have data stored about it think of
entities as noun.
In this ERD we have five Entities: - Student, University, College, Course, Faculty Attributes: A
property or characteristic of an entity. Often shown as an oval.

In this ERD:-
Student: - Stu_Id, Stu_Name, DOB, Phone_no
University: - Uni_name, Uni_id, Location, Fees
College: - Name, Location, Clg_id
Course: -Course_Id, Name, Duration
Faculty: -Salary, Fac_name, Fac_id, Qualification

Relationship: - Relationships are typically shown as diamonds on the connecting lines. In this ERD of
organization we have four relationship: -Has, Study in, Enrols in, Teaches by

PRIMARY KEY: - The field that is unique for all the record occurrences.it denotes as Underline. In this
ERD we have Stu_id In Student, Uni_id in University, Clg_id in College, Course_id in Course, Fac_name in
Faculty.

41