INFORMATION SYSTEM MANAGEMENT

LAB FILE

Submitted in partial fulfillment of the requirements for

the award of the degree of

Bachelor of Business Administration (BBA)

To

Guru Gobind Singh Indraprastha University, Delhi

Guide- Submitted by
Chirag Sengar

Gitarattan International Business School

New Delhi -110085 Batch 2021-24

1
 TABLE OF CONTENT

[Link] Particulars Pg. No. Sign

1. Practical 1 3
2. Practical 2 7
3. Practical 3 11
4. Practical 4 14
5. Practical 5 21

2
 PRACTICAL-1

1. Create any table related to Student records with columns roll no, name, subject, marks

This query creates a table named "StudentRecords" with columns for RollNo, Name, Subject,
and Marks. The RollNo column is set as the primary key, assuming that each student has a
unique roll number.

[Link] the table.

To describe the structure of the previously created "Student" table in SQL, you can use the
DESCRIBE statement in some database management systems, such as MySQL.
Alternatively, you can use the SHOW COLUMNS statement in MySQL or DESCRIBE
TABLE in some other database systems like PostgreSQL or SQLite.

[Link] five records in the student table

This example inserts five records into the "StudentRecords" table. Adjust the values
according to the actual data you want to insert. The assumption here is that the RollNo is a
unique identifier for each student.

3
After running this SQL query, your "StudentRecords" table will have these five records. You
can continue to add more records using similar INSERT INTO statements.

4. Display the details of all the students.

[Link] the details of students whose marks is greater than 85.

To display the details of students whose marks are greater than 85 from the
"[Link]" table, you can use the SELECT statement with a WHERE
clause.

4
This query retrieves all columns (*) from the "StudentRecords" table where the "Marks"
column is greater than 85. Adjust the column names if needed based on your actual table
structure.

After running this SQL query, you will get a result set containing the details of students who
have scored more than 85 marks.

[Link] the details of students whose subject is ‘history’.

[Link] the details of students whose marks are 80 or 90.

5
 To display the details of students whose marks are either 80 or 90 from the
"StudentRecords" table, you can use the SELECT statement with a WHERE clause using
the IN operator.

[Link] the details of students whose subject is either ‘science’ or ‘history’.

To display the details of students whose subject is either 'Science' or 'History' from the
"StudentRecords" table, you can use the SELECT statement with a WHERE clause using the
IN operator.

6
 PRACTICAL-2
1. Create a table student with columns roll no, name, subject, marks.

[Link] the marks of the student to 95 whose rollno is 3.

To update the marks of the student with roll number 3 to 95, you would typically perform an
update operation in a database or a similar data storage system. The specific syntax for
updating data depends on the type of database you are using. In SQL, for example, the query
might look like:

7
[Link] a column whose name is ‘phone no’ and datatype is ‘int’.

To add a column named 'phone no' with a datatype of 'int' to a table, you can use SQL.

4. selete the column whose name is phoneno.

8
[Link] the datatype of column named ‘name’ to char (200).

9
[Link] the table name to Student Details

To modify the name of a table in SQL, you can use the ALTER TABLE statement.
However, it's important to note that not all database systems support renaming tables
directly.

10
 PRACTICAL-3

1. Create a table student with columns roll no, name, subject, marks.

2. Find the average marks and group by subject.

To find the average marks and group them by subject in SQL, you can use the SELECT
statement along with the AVG function and the GROUP BY clause.

3. Find the maximum marks and group by subject.

To find the maximum marks and group them by subject in SQL, you can use the SELECT
statement along with the MAX function and the GROUP BY clause.

11
4. Find the minimum marks and group by subject.
To find the minimum marks and group them by subject in SQL, you can use the SELECT
statement along with the MIN function and the GROUP BY clause.

5. Find the sum of marks and group by subject

To find the sum of marks and group them by subject in SQL, you can use the SELECT
statement along with the SUM function and the GROUP BY clause.

12
6. Find the total count of marks and group by subject.
To find the total count of marks and group them by subject in SQL, you can use the
SELECT statement along with the COUNT function and the GROUP BY clause.

13
 PRACTICAL-4
[Link] a table ‘BAKERY’ with any no of columns.

To create a table named 'BAKERY' with any number of columns, you can use the CREATE
TABLE statement in SQL.

1. Describe the table.

2. Insert five records in the table.

To insert five records into a 'BAKERY' table with an assumed 'Indian Brand' column, you
can use the INSERT INTO statement in SQL

14
3. Display the details of all the items in the table.

4. Display the details of all items whose item quantity is greater than 200. To display the
details of all items in the 'BAKERY' table whose quantity is greater than 30.00, you can
use the SELECT statement with a WHERE clause.

5. Display the details of all items whose item name is ‘samosa’.

To display the details of all items in the 'BAKERY' table whose item name is 'bread', you
can use the SELECT statement with a WHERE clause.

15
7. Display the details of all items whose price is either 6.00 or 20.00

To display the details of all items in the 'BAKERY' table whose price is either 6.00 or
20.00, you can use the SELECT statement with a WHERE clause using the IN operator.

8. Display the details of all items whose item name is ‘samosa’ or ‘gulab jamun’.

To display the details of all items in the 'BAKERY' table whose item name is either
'samosa' or 'gulab jamun', you can use the SELECT statement with a WHERE clause
using the IN operator

16
9. Update the price of the item to 70 whose item number is 1

To update the price of the item to 70 for the item with item number 1 in the 'BAKERY'
table, you can use the UPDATE statement.

10. Delete the record of the item whose item number is 5.

To delete the record of the item with item number 5 in the 'BAKERY' table, you can use the
DELETE statement

17
11. Add a column named ‘Supplier Phone No’ and datatype is ‘Int’.
To add a column named 'Supplier Phone No' with a data type of 'Int' to the 'BAKERY'
table, you can use the ALTER TABLE statement.

12. Delete the column whose name is ‘Supplier Phone No’.

To delete the column named 'Supplier Phone No' from the 'BAKERY' table, you can use
the ALTER TABLE statement

18
13. Modify the datatype of column whose name is Item_Name to char(30)
To modify the data type of the column named 'Item_Name' to CHAR(30) in the
'BAKERY' table, you can use the ALTER TABLE statement

14. Modify the table name to BakeryProducts.

The ability to rename a table might depend on the specific database system you are
using. Always make sure to check the documentation for your particular database to
confirm the syntax and support for table renaming

19
20
 INTRODUCTION ENTITY RELATIONSHIP (ER) DIAGRAM

An Entity Relationship (ER) Diagram is a type of flowchart that illustrates how “entities”
such as people, objects or concepts relate to each other within a system. ER Diagrams are
most often used to design or debug relational databases in the fields of software engineering,
business information systems, education and research. Also known as ERDs or ER Models,
they use a defined set of symbols such as rectangles, diamonds, ovals and connecting lines to
depict the interconnectedness of entities, relationships and their attributes. They mirror

grammatical structure, with entities as nouns and relationships as verbs.

ER diagrams are related to data structure diagrams (DSDs), which focus on the relationships
of elements within entities instead of relationships between entities themselves. ER diagrams
also are often used in conjunction with data flow diagrams (DFDs), which map out the flow of

information for processes or systems.

QUESTION : Draw Entity Relationship diagram of a Hospital Management System

21
Creating a detailed Entity-Relationship Diagram (ERD) for a Hospital Management System
(HMS) involves various entities and their relationships. Below is a simplified representation
of an ERD for an HMS.

QUESTION : Draw Entity Relationship diagram of a University.

Designing a complete Entity-Relationship Diagram (ERD) for a University involves multiple

entities, relationships, and attributes. Below is a simplified representation that includes some
key entities and relationships found in a typical university system.

Step 1 − Identifying the entity sets.

The entity set has multiple instances in a given business scenario.

As per the given constraints the entity sets are as follows −

• Department
• Course
• Student
• Instructor

22
Head of the Department (HOD) is not an entity set. It is a relationship between the instructor
and department entities.

Step 2 − Identifying the attributes for the given entities

• Department − the relevant attributes are department Name and location.

• Course − The relevant attributes are courseNo, course Name, Duration, and
prerequisite.
• Instructor − The relevant attributes are Instructor Name, Room No, and telephone
number.
• Student − The relevant attributes are Student No, Student Name, and date of birth.

Step 3 − Identifying the Key attributes

• Department Name is the key attribute for Department.

• CourseNo is the key attribute for Course entity.
• Instructor Name is the key attribute for the Instructor entity.
• StudentNo is the key attribute for Student entities.

Step 4 − Identifying the relationship between entity sets

• The department offers multiple courses and each course belongs to only one
department, hence cardinality between department and course if one to many.

• One course is enrolled by multiple students and one student for multiple courses.
Hence, relationships are many to many.

• One department has multiple instructors and one instructor belongs to one and only
one department, hence the relationship is one to many.

23
 • Each department has one “HOD” and one instructor is “HOD” for only one
department, hence the relationship is one to one. Here, HOD refers to the head of the
department.

• One course is taught by only one instructor but one instructor teaches many courses
hence the relationship between course and instructor is many to one.

The relationship between instructor and student is not defined because of the following
reasons − • There is no significance in the relationship.
• We can always derive this relationship indirectly through course and instructors, and
course and student.

Step 5 − Complete ER model

24
The complete ER Model is as follows −

25