INFORMATION SYSTEM MANAGEMENT – LAB FILE

Q.1 Introduction of SQL

Ans:

 SQL (Structured Query Language) is a standardized programming language for managing and
manipulating relational databases.
 It enables users to interact with databases to perform various tasks, such as querying data,
updating records, inserting new data, and deleting existing data. SQL is essential for managing
data in relational database management systems (RDBMS) like MySQL, PostgreSQL, SQL
Server, and Oracle.

A. Key Components of SQL:

1. Data Querying (SELECT):

 The most common use of SQL is to query data using the `SELECT` statement to retrieve
specific information from one or more tables.

Example:

 SELECT column1, column2 FROM table_name WHERE condition;

2. Data Manipulation (DML):

 This includes statements like:
 `INSERT` – to add new records to a table
 `UPDATE` – to modify existing data
 `DELETE` – to remove records

Example:

 INSERT INTO table_name (column1, column2) VALUES (value1, value2);

3. Data Definition (DDL):

 These commands define or modify database structures, such as creating or altering tables.
 `CREATE` – to create a new database or table
 `ALTER` – to modify an existing database or table
 `DROP` – to delete a table or database
 Example:

 CREATE TABLE table_name ( column1 datatype, column2 datatype );

4. Data Control (DCL):

 Commands used for access control:
 `GRANT` – to provide users with access to the database
 `REVOKE` – to withdraw access rights

 CONCLUSION:
 SQL is a powerful and essential language for anyone working with relational databases.
Its widespread adoption and simplicity in querying, updating, and managing data make
it fundamental in data management, analytics, and application development.

 Understanding SQL is a critical skill for data analysts, software engineers, and database
administrators.

Q.2 Types of data type

Ans:

 SQL supports various data types that define the kind of values that can be stored in a table
column. These data types are grouped into different categories, including numeric, string,
date/time, and others. Here’s an overview of the most common types of data types in SQL:

1. Numeric Data Types:

 Numeric data types store numbers, including integers and floating-point values.

a) INT or INTEGER:
Stores whole numbers (both positive and negative). The size may vary depending on the
database system, but it typically ranges from −2,147,483,648 to 2,147,483,647.

b) SMALLINT:
Stores smaller integer values. Typically ranges from −32,768 to 32,767.

c) TINYINT:
Stores very small integer values (usually 0 to 255).
 d) BIGINT:
Stores large integer values. It can range from −9,223,372,036,854,775,808 to
9,223,372,036,854,775,807.

e) DECIMAL (p, s) or NUMERIC (p, s):

Stores fixed-point numbers with a specified precision (`p` total digits) and scale (`s` digits to
the right of the decimal point).

f) FLOAT:
Stores approximate floating-point numbers. The precision may vary across databases.

g) DOUBLE or DOUBLE PRECISION:

Stores double-precision floating-point numbers.

2. String (Character) Data Types:

These store text or string data.

a) CHAR(n):
Stores fixed-length strings, where `n` is the number of characters. If the length of the string
is less than `n`, the remaining space is padded with spaces.

b) VARCHAR(n) or VARCHAR2(n):
Stores variable-length strings, where `n` is the maximum number of characters. No padding
is added.

c) TEXT:
Stores large amounts of text data. The maximum length can vary based on the database
system.

d) NCHAR(n):
Stores fixed-length Unicode strings. The `n` specifies the number of characters, and the size
depends on the encoding used.
 e) NVARCHAR(n):
Stores variable-length Unicode strings.

3. Date and Time Data Types:

SQL provides specific data types to store date and time information.

a) DATE:
Stores date values (year, month, day).

b) TIME:
Stores time values (hour, minute, second).

c) DATETIME:
Stores both date and time values.

d) TIMESTAMP:
Stores a combination of date and time, typically used to track the creation or modification
time of records. It’s often automatically updated by the database.

e) YEAR:
Stores year values in two or four digits.

4. Boolean Data Type:

a) BOOLEAN:
Stores true or false values. Depending on the system, `TRUE` might be represented by `1`
and `FALSE` by `0`.

5. Binary Data Types:

These data types store binary data such as images, audio, and other multimedia files.

a) BINARY(n):
Stores fixed-length binary data, where `n` represents the length.

b) VARBINARY(n):
Stores variable-length binary data.
 c) BLOB:
Stores large binary objects, such as images or multimedia files.

 Summary
SQL data types define the type of data that can be stored in each table column, ranging from
numbers and strings to dates and binary data. Understanding data types helps ensure data
integrity and optimizes database performance.

Q.3 SQL DDL commands

Ans:

 SQL DDL (Data Definition Language) commands are used to define and manage the structure
of a database.
 These commands deal with database schema creation, modification, and deletion, as well as
the definition of tables, indexes, and constraints. They do not manipulate the data within the
tables but rather work on the structure of the database objects.

1. Common DDL Commands:

A. CREATE:
The `CREATE` command is used to create new database objects like tables, views, indexes,
databases, and more.

 Syntax:
 CREATE TABLE table_name ( column1 datatype constraints, column2 datatype
constraints,... );

 Example:
 CREATE TABLE Employees ( EmployeeID INT PRIMARY KEY, FirstName VARCHAR(50),
LastName VARCHAR(50), HireDate DATE );

B. ALTER:
The `ALTER` command is used to modify the structure of an existing database object, such as
altering a table by adding, deleting, or modifying columns.
  Syntax:
 ALTER TABLE table_name ADD column_name datatype;

 Example:

 Adding a column:
 ALTER TABLE Employees ADD Email VARCHAR(100);

 Dropping a column:
 ALTER TABLE Employees DROP COLUMN Email;

 Modifying a column:
 ALTER TABLE Employees MODIFY COLUMN LastName VARCHAR(100);

C. DROP
The `DROP` command is used to delete database objects such as tables, databases, indexes,
or views permanently.

 Syntax:
 DROP TABLE table_name;

 Example:
 Dropping a table:
 DROP TABLE Employees;

 Dropping a database:
 DROP DATABASE MyDatabase;

 Dropping an index:
 DROP INDEX idx_lastname;

D. TRUNCATE
The `TRUNCATE` command is used to remove all records from a table but keeps the table
structure intact. It is faster than the `DELETE` command because it does not log individual
row deletions.
  Syntax:
 TRUNCATE TABLE table_name;

 Example:
 TRUNCATE TABLE Employees;

#Note:

Unlike `DELETE`, which can be rolled back if used within a transaction, `TRUNCATE` cannot be rolled
back in most cases because it directly removes all rows.

E. RENAME
The `RENAME` command is used to rename a database object, such as a table or column.

 Syntax:
 RENAME TABLE old_table_name TO new_table_name;

 Example:
 RENAME TABLE Employees TO Staff;

F. COMMENT
The `COMMENT` command is used to add comments to tables or columns. These comments can
help document the schema.

 Syntax:
 COMMENT ON TABLE table_name IS 'This is a comment';
 COMMENT ON COLUMN table_name.column_name IS 'This is a comment';

 Example:
 COMMENT ON TABLE Employees IS 'Stores employee details';

 Conclusion
DDL commands form the foundation of SQL's capabilities by allowing users to create and
modify the schema of a database. These commands ensure that the database structure
evolves to accommodate new requirements while maintaining consistency and integrity.
Q.4 SQL DML command

Ans:

 SQL DML (Data Manipulation Language) commands are used to interact with and
manipulate data within the database.
 Unlike DDL (Data Definition Language), which defines the structure of the database (such as
tables and relationships), DML focuses on manipulating the actual data stored in those
structures. Below is a detailed explanation of the main DML commands: INSERT, UPDATE,
DELETE, and SELECT.

1. Common DML Commands:

A. INSERT Command:
The `INSERT` command is used to add new records (or rows) to a table. It can insert data into one
or multiple columns.

 Syntax:
 INSERT INTO table_name VALUES (value1, value2, value3, ...);

 Example:
 INSERT INTO employees VALUES (101, 'Alice Johnson', 'Marketing', 60000);

B. UPDATE Command:
The `UPDATE` command is used to modify existing records in a table. It can update one or more
columns based on a condition.

 Syntax:
 UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition;

 Example:
 UPDATE employees SET salary = 62000 WHERE id = 101;

C. DELETE Command:
The `DELETE` command is used to remove records from a table. You specify which records to
delete by providing a condition.
  Syntax:
 DELETE FROM table_name WHERE condition;

 Example:
 DELETE FROM employees WHERE id = 104;

D. SELECT Command:
The `SELECT` command retrieves data from one or more tables. While technically part of Data
Query Language (DQL), it’s closely related to DML because it helps you retrieve and manipulate
data.

 Syntax:
 SELECT column1, column2, ...FROM table_name WHERE condition;

 Example:
 SELECT name, department, salary FROM employees WHERE salary > 60000;

 Summary of DML Commands:

 INSERT: Adds new rows to a table.

 UPDATE: Modifies existing rows in a table.
 DELETE: Removes rows from a table.
 SELECT: Retrieves data from a table.

 Each of these DML commands is vital for maintaining and working with the data in a
database, and they are frequently used in conjunction with other SQL operations like
transactions, joins, and conditional logic.
Q.5 . Write a query to create a table called Employees with the following columns:

i. EmployeeID (INT, Primary Key)

ii. FirstName (VARCHAR)
iii. LastName (VARCHAR)
iv. Age (INT)
v. Department (VARCHAR)
vi. Salary (INT)

Ans:

Q.6 Write a query and insert 10 records into the Employees table

Ans:
Q.7 Write a query to select all columns from the Employees table.

Ans.

Q.8 Select the FirstName, LastName, and Salary of employees in the IT department

Ans:
Q.9 Retrieve details of employees with a salary greater than 55,000, sorted by Salary in
descending

order.

Ans:

Q.10 Write a query to update the salary of the employee with EmployeeID = 1 to 55,000

Ans:
Q.11 Increase the salary of all employees in the HR department by 10%

Ans:

Q.12: Write a query to add a new column HireDate (DATE) to the Employees table

Ans:
Q.13 Write a query to delete the employee with EmployeeID = 2

Ans:

Q.14 Delete all employees from the Sales department.

Ans:
Q.15 Write a query to calculate the total salary of all employees.

Ans:

Q.16 Write a query to find the average salary of employees in the IT department.

Ans:

Q.17 Write a query to count the number of employees in the HR department.

Ans:

Q.18 Write a query to find the highest and lowest salary in the Employees table

Ans:
Q.19 Write a query to group employees by Department and calculate the total salary for each
department.

Ans:

Q.20 Write a query to group employees by Age and calculate the average salary for each age
group.

Ans:

Q.21 Write a query to count the number of employees in each department.

Ans:
Q.22 Write a query to group employees by Department and calculate the maximum salary in each
department. Only include departments where the maximum salary is above 60,000

Ans:

Q.23 Write a query to select all employees and order them by LastName in ascending order.

Ans:

Q.24 Write a query to display employees from the IT department, whose salaries are in descending
order, according to their salary.

Ans:
Q.25 Write an SQL query to create a table named Students with the following structure:

 StudentID (INT, Primary Key)

 FirstName (VARCHAR)
 LastName (VARCHAR)
 Age (INT)
 Gender (VARCHAR)
 Grade (DECIMAL)
 EnrollmentDate (DATE)

Ans:

Q.26 . Insert 10 records in the students table.

Ans:

Q.27 Use various conditions to retrieve information from Students table.

Ans:

 Retrieve all students

 Retrieve all students aged 20 and above:

 Retrieve all female students:

 Retrieve students who scored a grade above 90:

 Retrieve students who enrolled in 2022:

 Retrieve students whose last name starts with 'D':

 Retrieve students who are 19 years old and female:

 Retrieve students with grades between 80 and 90:

 Retrieve all male students who enrolled after 2020:

Q.28 Write a query to increase the grade of all female students by 5%.

Ans:

Q.29 Write a query to delete the record of the student with StudentID = 3

Ans:
Q.30 Write a query to add a new column Email (VARCHAR) to the Students table.

Ans:

Q.31 Write a query to group students by Gender and calculate the average grade for each gender.

Ans:

Q.32 Discuss ER Modelling

Ans:

 Entity-relationship (ER) modeling is used to design and structure data in a database by

visualizing the relationships between data entities.
 It provides a clear, organized view of data and the connections between various entities,
which makes it a fundamental step in database design, especially for relational databases.
1. Key Components of ER Modelling

1. Entities:

 Entities represent real-world objects or concepts that have independent existence within
the system (e.g., `Student`, `Course`, `Teacher`).
 Each entity has a primary key that uniquely identifies each instance of that entity.

2. Attributes:
 Attributes are the properties or characteristics of an entity (e.g., `Student` might have
attributes such as `Student_ID`, `Name`, `Age`, and `Gender`).
 Primary Key: A unique identifier for an entity (e.g., `Student_ID`).
 Foreign Key: A reference to the primary key of another entity, establishing relationships.

3. Relationships:

 Relationships define how entities interact with each other (e.g., a `Student` "enrolls" in a
`Course`).
 Relationships can have their attributes, known as **descriptive attributes** (e.g.,
enrollment date).

2. Types of Relationships

1. One-to-One (1:1):

 An entity in one set is associated with a single entity in another set.

 Example: Each `Student` has one `Student_ID`, and each `Student_ID` belongs to only one
`Student`.

2. One-to-Many (1:N):

 An entity in one set is associated with multiple entities in another set.

 Example: A `Teacher` can teach multiple `Courses`, but each course is assigned to only one
teacher.

3. Many-to-Many (M:N):
 Entities in one set are associated with multiple entities in another set, and vice versa.
 Example: `Students` can enroll in multiple `Courses`, and each `Course` can have multiple
`Students`.
3. Benefits of ER Modeling

 Clarity: Provides a clear structure for data, helping developers and stakeholders understand
data flow.
 Consistency: Ensures consistent data organization, reducing redundancy and dependency.
 Efficiency: Helps in optimizing database design, which improves data retrieval and storage
performance.
 Flexibility: Makes it easier to update or expand the database structure as requirements
change.

 ER modeling is widely used in database design to improve the logical organization and
structure of databases, which helps ensure a stable, scalable, and maintainable system.

Q.33 Symbols of the ER Model

Ans:

 In an Entity-Relationship (ER) Model, various symbols represent entities, attributes, and

relationships. Here are the symbols used in ER modeling, along with diagrams for each:

1. Entity
 Symbol: Rectangle
 Description: An entity represents a real-world object or concept, such as a `Student`,
`Course`, or `Employee`.
 Diagram:

EMPLOYEE

2. Weak Entity:
 Symbol: Double Rectangle
 Description: A weak entity depends on a strong entity and does not have a primary key of
its own. It is uniquely identified by a combination of its attributes and a foreign key from
the strong entity it depends on.
 Diagram:

Weak Entity
3. Attribute:
 Symbol: Oval
 Description: Attributes describe the properties of an entity, such as `Name`, `Age`,
`Gender`, and `Date of Birth`.
 Diagram:

Name

4. Key Attribute:
 Symbol: Oval with Underline
 Description: A key attribute uniquely identifies each instance of an entity, such as
`Student_ID` for a `Student` entity.
 Diagram:

Studentid

5. Multivalued Attribute:
 Symbol: Double Oval
 Description: A multivalued attribute can hold multiple values, such as a `Phone_Number`
attribute for an `Employee` who may have multiple phone numbers.
 Diagram:

Phn

6. Derived Attribute:
 Symbol: Dashed Oval
 Description: A derived attribute is one that can be calculated or derived from other
attributes, such as `Age` derived from `Date of Birth`.
 Diagram:

AGE

7. Relationship:
 Symbol: Diamond
 Description: A relationship represents an association between two entities, such as a
`Teacher` teaching a `Course`.
 Diagram:

Relationship
 8. Weak Relationship :
 Symbol: Double Diamond
 Description: A weak relationship exists between a weak entity and its strong entity,
necessary for identifying the weak entity.
 Diagram:

Weak Relationship

 These symbols make up an ER Diagram (ERD), a structured way to visualize and communicate
the logical structure of a database.

Q.34 ER Diagram of Banking System

  The above Entity-Relationship (ER) diagram designed to show the relationships between a
Customer, their Credit Card Information, and Insurance details, all managed through Bank
relationships. Here's a breakdown:

1. Entities and Attributes

A. Customer: This is the main entity, representing the user of the system.
o It has two attributes:
I. Account name: Likely the customer’s name or account identifier.
II. Email address: Contact information for the customer.

B. Credit Card Information: Represents the customer's credit card details.

o It has two attributes:
I. Holder name: The name on the credit card.
II. Number: The credit card number.

C. Insurance: Represents an insurance policy related to the customer.

o It has three attributes:
I. Insurance ID: A unique identifier for the insurance policy.
II. Insurance Amount: The coverage amount or sum insured.
III. Insurance Name: The name or type of the insurance policy.

2. Relationships
A. Bank:
I. The BANK diamond-shaped nodes represent relationships between entities, implying
that the Customer is linked to both Credit Card Information and Insurance through a
banking institution.
II. BANK relationship between Customer and Credit Card Information: Suggests that
the customer’s credit card information is managed or verified by a bank.
III. BANK relationship between Customer and Insurance: Indicates that the bank might
facilitate or manage the insurance policies associated with the customer.

Summary

In summary, this ER diagram describes a system where a customer entity is linked to Credit Card
Information and Insurance entities through Bank relationships. The bank acts as an intermediary,
managing or connecting the customer's financial and insurance details.
Q.35 ER Diagram of University

This diagram represents an Entity-Relationship (ER) Diagram for a university system with the
following entities, attributes, and relationships:

1. Entities and Attributes:

A. Student: Represents the students in the university.
o Attributes:
I. Student name: The name of the student.
II. Enrollment no.: The unique enrollment number for each student.
III. Email address: The student's email address.
IV. Address: The student's home address.

B. Professor: Represents the professors or instructors at the university.

o Attributes:
I. Name: The name of the professor.
II. Emp_id: A unique employee identifier for each professor.
III. Email address: The professor's email address.
IV. Address: The professor's home address.
 C. University: Represents the university as an organization.
o Attributes:
I. Uni_id: A unique identifier for the university.
II. Uni_name: The name of the university.
III. Affiliate_colleges: A list or description of colleges affiliated with the university.

2. Relationships
A. Student - Course - Professor
o Course: This diamond represents a relationship between students and professors through
courses.
o Student to Course: Indicates that a Student can enroll in a Course.
o Course to Professor: Indicates that a Professor teaches or is associated with a Course.

This structure implies that students can enroll in courses, and each course is taught by one or more
professors.

B. Student - College - University

o College: Represents the relationship between students, professors, and the university.
o Student to College: Shows that a student belongs to a college within the university.
o Professor to College: Indicates that a professor is associated with a college.
o College to University: Indicates that the College is a part of the University.

Summary

In summary, this ER diagram models a university system where:

 Students enroll in Courses taught by Professors.

 Both Students and Professors are associated with a College within the University.
 The University has identifying attributes, including its name, ID, and affiliated colleges.

This structure provides a basic overview of the connections between students, professors, courses,
colleges, and the university.