Scribd
Upload
10 views22 pages

BBA Information System Management Lab

The document is a practical file for the Information System Management Lab submitted by Sonali, a BBA student at Trinity Institute of Professional Studies. It includes a certificate of authenticity, a declaration of original work, and an acknowledgment of guidance from Dr. Manjari Agarwal, along with detailed SQL exercises and an overview of the Entity-Relationship Model. The contents cover various SQL commands and their applications in managing relational databases.

Uploaded by

behanik449
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
10 views22 pages

BBA Information System Management Lab

The document is a practical file for the Information System Management Lab submitted by Sonali, a BBA student at Trinity Institute of Professional Studies. It includes a certificate of authenticity, a declaration of original work, and an acknowledgment of guidance from Dr. Manjari Agarwal, along with detailed SQL exercises and an overview of the Entity-Relationship Model. The contents cover various SQL commands and their applications in managing relational databases.

Uploaded by

behanik449
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd

Trinity Institute of Professional Studies

Sec-9, DWARKA, NEW DELHI-110075

(Affiliated To)

Guru Gobind Singh Indraprastha University


SECTOR 16-C, DWARAKA, NEW DELHI

INFORMATION SYMTEM MANAGEMENT LAB

(BBA 307) PRACTICAL FILE

SUBMITTED TO SUBMITTED BY
Dr. Manjari Agarwal Sonali 05520601722
Associate Professor BBA 5A Shift 1
CERTIFICATE

This is to certify that the Research Methodology Lab file being submitted is a bonafide
work done by Sonali, student of BBA 5th Semester at Trinity Institute of Professional
Studies affiliated to Guru Gobind Singh Indraprastha University in partial fulfilment of the
award of the BBA degree.

She has worked under my guidance and supervision.

Dr. Manjari Agarwal


DECLARATION

I hereby declare that the following documented project report on “Information System
Management Lab” is an original and authentic work done by me for the partial
fulfillment of Bachelor of Business Administration degree program.

I hereby declare that the entire Endeavour put in the fulfillment of the task and genuine and
original to the best of my knowledge & I have not submitted it earlier elsewhere.

Signature:
Name- SONALI
Course- BBA
Semester- 5th
Shift- 1st
ACKNOWLEDGEMENT

I Sonali wish to express gratitude to my mentor Dr. Manjari Agarwal who co-operated
and enabled me to complete my Research Methodology Lab file.

I express my sincere thanks for her valuable suggestive, informative and illuminative
guidance.

I express my utmost thanks for her encouragement, cooperation and consent.

NAME: SONALI
ENROLLMENT NUMBER: 05520601722
CONTENTS
S. No. Topic Page No.

SQL

1. Step 1: Create the table with coloumn 7-8

2. Step 2: Alter the table to add a new column 9

3. Step 3: Rename the Students table 10

4. Step 4: Insert rows into table 11-12

5. Step 5: Update a student's marks 13-14

6. Step 6: Delete a student record 15-16

7. Step 7: Select all students 17

8. Step 8: Aggregate functions to get insights 18-22

ER Model

9. Employee Management System 25

10. Customer Order System 26

11. Student Enrollment System 27


STRUCTURED
QUERY
LANGUAGE
SQL
SQL (Structured Query Language) is a standardized programming language used to
manage and manipulate relational databases. It allows users to create, retrieve, update, and
delete data within a database. SQL is essential for managing data in Relational Database
Management Systems (RDBMS).

Key Features of SQL:

1. Data Definition Language (DDL):


o SQL provides commands to define the structure of the database, including
creating, altering, and dropping tables.
o Commands: CREATE, ALTER, DROP, TRUNCATE.
2. Data Manipulation Language (DML):
o SQL enables manipulation of the data stored in the database through querying,
inserting, updating, and deleting records.
o Commands: SELECT, INSERT, UPDATE, DELETE.
3. Data Control Language (DCL):
o SQL allows controlling access to the database and managing permissions.
o Commands: GRANT, REVOKE.
4. Transaction Control Language (TCL):
o SQL provides control over transactions to ensure the integrity and reliability
of data.
o Commands: COMMIT, ROLLBACK, SAVEPOINT.
5. Querying Data:
o SQL can retrieve specific data using the SELECT command, often combined
with filtering (WHERE), sorting (ORDER BY), grouping (GROUP BY), and
joining data across tables (JOIN).
6. Data Integrity and Constraints:
o SQL supports defining constraints (e.g., PRIMARY KEY, FOREIGN KEY,
UNIQUE, NOT NULL, CHECK) to ensure the accuracy and consistency of
data.
7. Portability:
o SQL is portable across different database systems, making it widely applicable
to various RDBMS platforms.
Popular RDBMS Systems: MySQL, Microsoft SQL Server, Oracle Database, SQLite, etc.
Step 1: Create the table with column

Q: Create the Students table with column ID INT PRIMARY KEY,

Name VARCHAR(50),

Subject VARCHAR(50),

Marks DECIMAL(5, 2)
Step 2: Alter the table to add a new column

Q. Alter Students table to add a column for Enrollment Date


Step 3: Rename the Students table

Q: Rename Students table to CollegeStudents


Step 4: Insert rows into table

Q: Insert 5 rows into CollegeStudents table:-

(1, 'Amit', 'Maths', 85.50, '2023-01-01'),

(2, 'Sara', 'English', 90.00, '2023-02-15'),

(3, 'Raj', 'Science', 75.75, '2023-03-10'),

(4, 'Neha', 'Maths', 82.00, '2023-04-12'),

(5, 'Karan', 'English', 88.50, '2023-05-20');


Step 5: Update a student's marks

Q: Update marks to 92.00 of the student with ID 2


Step 6: Delete a student record

Q: Delete the student with ID 3


Step 7: Select all students

Q: Select all records from CollegeStudents table


Step 8: Aggregate functions to get insights

Q: Find the total number of students

Q: Calculate the average marks of students


Q: Get the highest marks

Q: Get the lowest marks


Q: Get the sum of total marks
ENTITY
RELATIONSHIP
MODEL
Entity Relationship Model
The Entity-Relationship (ER) Model is a conceptual framework used in database design to
describe the structure of a database in terms of entities, attributes, and relationships. It helps
in visually representing how data is interconnected in a database, making it easier to
understand and design complex systems.
Key Components of the ER Model:
1. Entities:
o An entity represents a real-world object or concept that has data stored in the
database. Examples include Student, Employee, Product, etc.
o Entities are represented as rectangles in an ER diagram.
Example:
o Student, Course, and Teacher can all be entities.
2. Attributes:
o Attributes are the data that describe the properties of an entity. Each entity
can have several attributes, which represent information about that entity.
o Attributes are represented as ovals connected to their corresponding entity in
an ER diagram.
Example:
o For the Student entity, attributes could include StudentID, Name,
EnrollmentDate, and Marks.
3. Relationships:
o A relationship represents how entities are related to each other. Relationships
can be one-to-one, one-to-many, or many-to-many.
o Relationships are represented as diamonds in an ER diagram.
Example:
• A Student enrolls in a Course, and a Teacher teaches a Course. The
relationships here are "enrolls in" and "teaches."
Customer Order System
Employee Management System
Hospital Billing System

Common questions

Powered by AI

DDL and DML are different in purpose and functionality. DDL, which includes commands like CREATE, ALTER, and DROP, is used to define and modify the database structure, such as creating and altering tables . DML, on the other hand, consists of commands like SELECT, INSERT, UPDATE, and DELETE, and is used for manipulating the data within those database structures .

SQL ensures data integrity and reliability through Transaction Control Language (TCL) by using commands such as COMMIT, ROLLBACK, and SAVEPOINT to manage transactions effectively . Constraints like PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, and CHECK further enforce data accuracy and consistency by restricting invalid data entry and maintaining referential integrity .

ER diagrams have a significant impact on designing complex database systems by providing a clear, graphical representation of data entities, attributes, and relationships. They facilitate better understanding of data structure and interconnections, which helps in identifying potential design flaws, ensuring efficiency and consistency in database development, and facilitating communication among stakeholders .

The Entity-Relationship Model aids in database design by providing a clear visual representation of data structure through entities, attributes, and relationships. This conceptual framework helps in understanding how data is interconnected, making the design of complex systems more manageable and ensuring accurate data representation and relationships .

SQL's portability allows it to be applied across different relational database management systems (RDBMS), such as MySQL, Microsoft SQL Server, and Oracle Database, without requiring extensive modification to SQL queries. This is critical in environments where database systems may change or need to interact, ensuring consistency and interoperability .

JOIN operations in SQL are significant because they allow querying and combining rows from two or more tables based on a related column between them. This facilitates comprehensive data retrieval and analysis across multiple tables, enhancing the ability to derive insights and relationships within data that span across different entities in a relational database .

COMMIT and ROLLBACK are integral to transaction management in SQL. COMMIT saves all transactions permanently to the database, ensuring that successful operations are stored. Conversely, ROLLBACK reverses all transactions from the last COMMIT point, allowing recovery from errors or inconsistencies by undoing changes, thus ensuring data integrity and reliability .

Constraints are crucial in maintaining data integrity as they enforce rules automatically during data entry and modification. Constraints like PRIMARY KEY ensure unique identification of rows, FOREIGN KEY maintains referential integrity across tables, and UNIQUE, NOT NULL, and CHECK constraints prevent the entry of invalid or incomplete data, thereby ensuring consistency and integrity throughout the database .

SQL manages user access and secures data using Data Control Language (DCL) commands like GRANT and REVOKE. GRANT is used to provide users with specific privileges that enable them to execute certain operations, while REVOKE withdraws these privileges, thus controlling who can access and modify the data .

Aggregate functions in SQL, such as SUM, AVG, COUNT, MAX, and MIN, perform calculations on a set of values to return a single summary value. These functions are crucial for data analysis as they provide insights into the data, such as total or average values, tallies of entries, and range of numerical data, facilitating decision-making processes and trend analysis .

You might also like