A Lab Record File

on
Database Management System
(BCS-551)

B. TECH – III YEAR

(ODD SEM 2025-2026)

Submitted By : Submitted To :
Name: Mr. Mukhtar Ali
Roll no : ( Asst. Professor)
Sem: 5TH
Branch: CSE/AIML

DEPARTMENT OF COMPUTER SCIENCE & ENGINEERING

VISHVESHWARYA GROUP OF INSTITUTIONS
(Affiliated to Dr A P J Abdul Kalam Technical University, Lucknow)

1
 DETAILS OF THE EXPERIMENTS CONDUCTED

INDEX

[Link] TITLE OF THE EXPERIMENT DATE OF FACULTY

SUBMISSIO SIGNATURE
N
1 Installing oracle/ MYSQL
2 Creating Entity-Relationship Diagram using case tools.
3 Writing SQL statements Using ORACLE /MYSQL:
a)Writing basic SQL SELECT statements.
b) Restricting and sorting data.
c)Displaying data from multiple tables.
d)Aggregating data using group function.
e) Manipulating data.
f)Creating and managing tables.
4 Normalization

5 Creating cursor

6 Creating procedure and functions

7 Creating packages and triggers

8 Design and implementation of payroll processing system

9 Design and implementation of Library Information

System
10 Design and implementation of Student Information
System
11 Automatic Backup of Files and Recovery of Files

12 Mini project (Design & Development of Data and

Application ) for following :
a) Inventory Control System.
b) Material Requirement Processing.
c) Hospital Management System.
d) Railway Reservation System.
e) Personal Information System.
f) Web Based User Identification System.
g) Timetable Management System.
h) Hotel Management System

2
 PRACTICAL -1

TITLE: Installing oracle/ MYSQL

Installing Oracle Database 11g on Windows

To install the Oracle software, you must use the Oracle Universal installer.

1. For this installation, you need either the DVDs or a downloaded version of the DVDs. In this tutorial, you
install from the downloaded version. From the directory where the DVD files were unzipped, open
Windows Explorer and double-click on [Link] from the \db\Disk1 directory.
2. The product you want to install is Oracle Database 11g. Make sure the product is selected and
click Next.

3. You will perform a basic installation with a starter database. Enter orcl for the Global Database
Name and oracle for Database Password and Confirm Password. Then, click Next

4. Oracle Configuration Manager allows you to associate your configuration information with your
Metalink account. You can choose to enable it on this window. Then, click Next.

3
5. Review the Summary window to verify what is to be installed. Then, click Install.

6. The progress window appears.

7. The Configuration Assistants window appears.

8. Your database is now being created.

4
9. When the database has been created, you can unlock the users you want to use. Click OK.

10. Click Exit. Click Yes to confirm exit.

Testing Your Installation

To test that your installation completed successfully, perform the following steps:

1. Open a browser and enter the following URL:

[Link]

where <hostname> should be changed to your machine name, IP address, or localhost.

Because Enterprise Manager Database Control is a secure site, you need a certificate. Select the Accept
this certificate permanently option, and then click OK.

5
2. Enter system as the User Name and oracle as the Password, and then click Login

3. The Database Control Home Page appears. Your installation was successful.

Another way to work on ORACLE:

1. Goto[Link]
2. Create your login and download latest Express Edition of ORACE and install in simple way
3. Run the ORACLE, It will open in Web Browser
4. Click on Application Express

5. Login again and then create a new database user by filling the below entries and click on
creating workspace

6
6. Login by using your created username and password and start using ORACLE
PRACTICAL -2

TITLE: Creating Entity-Relationship Diagram using case tools

Introduction to Dia Diagram Editor:

a. Installation Guide

1. Download the installation file for your platform from [Link]

2. Open the downloaded file, select preferred installation language, and press “OK”.

3. The Dia “Setup Wizard” window will appear. Click “Next”.

4. In the “License Agreement” window click “Next” to continue installation.

7
5. Choose the components you want to install and click “Next”.

6. Choose the installation location on your computer and click “Install”.

7. After the installation process is completed, click “Finish”.

8
b. Dia User Guide

Create an ER Diagram

In this section, basic guidelines are given on how to create ER database diagrams. An ER diagram
consists of entity sets, attributes, and the relationship sets between entity sets. Let us create an ER
diagram for a database called “Courses and Students”. The database will have two main entity sets,
i.e., “Course” and “Student”. The relation between them defines which students belong to which
course.

1. Start Via Diagram Editor.

2. On the left side of the menu click on the dropdown menu, select “Other sheets” and click on “ER”.

9
3. Now the menu consists only of shapes that are relevant to an ER diagram.

4. Let us create an entity called “Course”. Choose the “E” icon with a single frame in the shapes
menu and click on the drawing space at the center. A rectangle with the name “Entity” will
appear.

5. Double-click on the new entity set and the properties window will show up (or right-click and
choose “Properties”). Change the name of the entity set to “Course” and click “OK”. The entity
sets name will be changed.

6. The “Course” entity set has several attributes: “courseId” (primary key), “title”, “ECTS”, “level”,
“language”. In the shape menu select the “A” icon with an oval around it and click near the
created “Course” entity set. An oval with the name “Attribute” will appear.

10
11
7. Double-click on the attribute and in the properties window change the name to “courseId”.
Since this attribute is also a primary key, select “Key” value “Yes”. Click “OK”.

8. In the shape menu click on the “Participation” icon and connect the entity set with the attribute.

9. It is also possible to connect an entity set with an attribute using different connectors. Choose
the appropriate style at the end of a new connector by clicking on “arrow style at the end of the
line new lines”. Select “line (L)” connector.

4
10. Proceed with the rest of the attributes of the entity set “Course”.

11. Attributes of the “Student” entity set are: “studentId”, “firstName”, “lastName”, “startDate”.

12. Create the relationship set between “Course” and “Student” with the name “Belongs” that also
has an attribute “signUpDate”. In the shape menu select “R” with a diamond around it and click
between the two entity sets in the drawing area.

5
13. Change the name to “Belongs” and assign attribute “signUpDate” to it.

The ER diagram for database “Courses and Students” was created successfully!

Note: saveyour diagram several times through all the creation process!
Export Created Diagram

1. Open the diagram you want to export.

2. Click on “File > Export“. Enter the name of the file, select the location you want the file to be
saved, determine file type (e.g. JPG), and click “Save”.

6
 PRACTICAL -3

TITLE : Writing SQL statements Using ORACLE /MYSQL

Schema (Use for all questions)
-- Create two sample tables
CREATE TABLE Department (
dept_id INT PRIMARY KEY,
dept_name VARCHAR(50)
);

CREATE TABLE Employee (

emp_id INT PRIMARY KEY,
name VARCHAR(100),
salary DECIMAL(10,2),
designation VARCHAR(50),
dept_id INT,
CONSTRAINT fk_dept FOREIGN KEY (dept_id) REFERENCES Department(dept_id)
);

-- Sample data
INSERT INTO Department VALUES (10, 'CS');
INSERT INTO Department VALUES (20, 'IT');

INSERT INTO Employee VALUES (1, 'Amit', 45000.00, 'Analyst', 10);

INSERT INTO Employee VALUES (2, 'Rita', 55000.00, 'Senior Analyst', 10);
INSERT INTO Employee VALUES (3, 'Karan', 35000.00, 'Developer', 20);
INSERT INTO Employee VALUES (4, 'Sana', 75000.00, 'Lead', 20);

a) Writing basic SQL SELECT statements

Purpose: Retrieve data from one or more columns.
-- Select all columns
SELECT * FROM Employee;

-- Select specific columns

SELECT emp_id, name, salary FROM Employee;

-- Select distinct values

SELECT DISTINCT designation FROM Employee;

b) Restricting and sorting data

Purpose: Filter rows and order results.
-- Filter using WHERE
SELECT name, salary FROM Employee
WHERE salary > 50000;

-- Multiple conditions
SELECT * FROM Employee

7
WHERE dept_id = 10 AND salary BETWEEN 40000 AND 60000;
-- Pattern matching
SELECT name FROM Employee
WHERE name LIKE 'R%';

-- Sorting results
SELECT name, salary FROM Employee
ORDER BY salary DESC, name ASC;
-- Limiting rows (MySQL / Oracle differences)
-- MySQL
SELECT * FROM Employee ORDER BY salary DESC LIMIT 3;
-- Oracle (12c+)
SELECT * FROM Employee ORDER BY salary DESC FETCH FIRST 3 ROWS ONLY;

c) Displaying data from multiple tables (Joins)

Purpose: Combine related rows from different tables.
-- Inner join (only matching rows)
SELECT e.emp_id, [Link], d.dept_name
FROM Employee e
JOIN Department d ON e.dept_id = d.dept_id;

-- Left join (all employees, even if no department)

SELECT e.emp_id, [Link], d.dept_name
FROM Employee e
LEFT JOIN Department d ON e.dept_id = d.dept_id;

-- Aggregation with join

SELECT d.dept_name, COUNT(e.emp_id) AS emp_count
FROM Department d
LEFT JOIN Employee e ON d.dept_id = e.dept_id
GROUP BY d.dept_name;

d) Aggregating data using group functions

Purpose: Summarize data using aggregate functions.
-- Aggregate examples
SELECT COUNT(*) AS total_employees FROM Employee;
SELECT AVG(salary) AS avg_salary FROM Employee;
SELECT MIN(salary) AS min_salary, MAX(salary) AS max_salary FROM Employee;

-- Group by department
SELECT dept_id, COUNT(*) AS emp_count, ROUND(AVG(salary),2) AS avg_salary
FROM Employee
GROUP BY dept_id;

-- Group with filtering using HAVING

SELECT dept_id, COUNT(*) AS emp_count
FROM Employee
GROUP BY dept_id
HAVING COUNT(*) > 1;

8
e) Manipulating data (INSERT, UPDATE, DELETE)
Purpose: Add, modify, or remove data.
-- Insert one row
INSERT INTO Employee (emp_id, name, salary, designation, dept_id)
VALUES (5, 'Neha', 48000.00, 'Developer', 20);

-- Insert multiple rows (MySQL syntax)

INSERT INTO Employee (emp_id, name, salary, designation, dept_id) VALUES
(6, 'Vikram', 52000.00, 'Analyst', 10),
(7, 'Pooja', 43000.00, 'Developer', 20);

-- Update rows
UPDATE Employee
SET salary = salary * 1.10
WHERE designation = 'Developer';

-- Delete rows
DELETE FROM Employee WHERE emp_id = 7;

-- Truncate table (removes all rows; faster)

TRUNCATE TABLE Employee; -- use with caution

f) Creating and managing tables

Purpose: Define schemas and modify structure.
-- Create table with constraints (example shown above)
CREATE TABLE Employee (
emp_id INT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
salary DECIMAL(10,2) DEFAULT 0.00,
designation VARCHAR(50),
dept_id INT,
CONSTRAINT fk_dept FOREIGN KEY (dept_id) REFERENCES Department(dept_id)
);
-- Alter table: add column
ALTER TABLE Employee ADD (email VARCHAR(100));

-- Alter table: modify column (MySQL/Oracle syntax differs)

-- MySQL
ALTER TABLE Employee MODIFY salary DECIMAL(12,2);
-- Oracle
ALTER TABLE Employee MODIFY (salary NUMBER(12,2));

-- Drop column (MySQL)

ALTER TABLE Employee DROP COLUMN email;

-- Drop table
DROP TABLE Employee;

PRACTICAL - 4

9
TITLE : Normalization
Normalization is the process of organizing data in a database. This includes creating tables and
establishing relationships between those tables according to rules designed both to protect the data
and to make the database more flexible by eliminating redundancy and inconsistent dependency.

Redundant data wastes disk space and creates maintenance problems. If data that exists in more
than one place must be changed, the data must be changed in exactly the same way in all locations.
A customer address change is much easier to implement if that data is stored only in the Customers
table and nowhere else in the database.

What is an "inconsistent dependency"? While it is intuitive for a user to look in the Customers table
for the address of a particular customer, it may not make sense to look there for the salary of the
employee who calls on that customer. The employee's salary is related to, or dependent on, the
employee and thus should be moved to the Employees table. Inconsistent dependencies can make
data difficult to access because the path to find the data may be missing or broken.

There are a few rules for database normalization. Each rule is called a "normal form." If the first rule
is observed, the database is said to be in "first normal form." If the first three rules are observed, the
database is considered to be in "third normal form." Although other levels of normalization are
possible, third normal form is considered the highest level necessary for most applications.

The following descriptions include examples.

First normal form

 Eliminate repeating groups in individual tables.

 Create a separate table for each set of related data.
 Identify each set of related data with a primary key.

Do not use multiple fields in a single table to store similar data. For example, to track an inventory
item that may come from two possible sources, an inventory record may contain fields for Vendor
Code 1 and Vendor Code 2.

What happens when you add a third vendor? Adding a field is not the answer; it requires program
and table modifications and does not smoothly accommodate a dynamic number of vendors.
Instead, place all vendor information in a separate table called Vendors, then link inventory to
vendors with an item number key, or vendors to inventory with a vendor code key.

Second normal form

 Create separate tables for sets of values that apply to multiple records.
 Relate these tables with a foreign key.

Records should not depend on anything other than a table's primary key (a compound key, if
necessary). For example, consider a customer's address in an accounting system. The address is
needed by the Customers table, but also by the Orders, Shipping, Invoices, Accounts Receivable, and
Collections tables. Instead of storing the customer's address as a separate entry in each of these
tables, store it in one place, either in the Customers table or in a separate Addresses table.

Third normal form

10
  Eliminate fields that do not depend on the key.

Values in a record that are not part of that record's key do not belong in the table. In general,
anytime the contents of a group of fields may apply to more than a single record in the table,
consider placing those fields in a separate table.

EXCEPTION: Adhering to the third normal form, while theoretically desirable, is not always practical.
If you have a Customers table and you want to eliminate all possible interfield dependencies, you
must create separate tables for cities, ZIP codes, sales representatives, customer classes, and any
other factor that may be duplicated in multiple records. In theory, normalization is worth pursing.
However, many small tables may degrade performance or exceed open file and memory capacities.

It may be more feasible to apply third normal form only to data that changes frequently. If some
dependent fields remain, design your application to require the user to verify all related fields when
any one is changed.

Other normalization forms

Fourth normal form, also called Boyce Codd Normal Form (BCNF), and fifth normal form do exist, but
are rarely considered in practical design. Disregarding these rules may result in less than perfect
database design, but should not affect functionality.

Normalizing an example table

These steps demonstrate the process of normalizing a fictitious student table.

1. Unnormalized table:

Student# Advisor Adv-Room Class1 Class2 Class3

1022 Jones 412 101-07 143-01 159-02
4123 Smith 216 101-07 143-01 179-04

2. First normal form: No repeating groups

Tables should have only two dimensions. Since one student has several classes, these classes
should be listed in a separate table. Fields Class1, Class2, and Class3 in the above records are
indications of design trouble.

Spreadsheets often use the third dimension, but tables should not. Another way to look at this
problem is with a one-to-many relationship, do not put the one side and the many side in the
same table. Instead, create another table in first normal form by eliminating the repeating
group (Class#), as shown below:

Student# Advisor Adv-Room Class#

1022 Jones 412 101-07
1022 Jones 412 143-01
1022 Jones 412 159-02
4123 Smith 216 101-07
4123 Smith 216 143-01
4123 Smith 216 179-04

11
 3. Second normal form: Eliminate redundant data

Note the multiple Class# values for each Student# value in the above table. Class# is not
functionally dependent on Student# (primary key), so this relationship is not in second normal
form.

The following tables demonstrate second normal form:

Students:

Student# Advisor Adv-Room

1022 Jones 412
4123 Smith 216

Registration:

Student# Class#
1022 101-07
1022 143-01
1022 159-02
4123 101-07
4123 143-01
4123 179-04

4. Third normal form: Eliminate data not dependent on key

In the last example, Adv-Room (the advisor's office number) is functionally dependent on the
Advisor attribute. The solution is to move that attribute from the Students table to the Faculty
table, as shown below:

Students:

Student# Advisor
1022 Jones
4123 Smith

Faculty:

Name Room Dept

Jones 412 42
Smith 216 42

PRACTICAL – 5

TITLE: Creating CURSOR

12
The central purpose of the Oracle PL/SQL language is to make it as easy and efficient as possible to
query and change the contents of tables in a database. You must, of course, use the SQL language to
access tables, and each time you do so, you use a cursor to get the job done. A cursor is a pointer to a
private SQL area that stores information about the processing of a SELECT or data manipulation
language (DML) statement (INSERT, UPDATE, DELETE, or MERGE). Cursor management of
DML statements is handled by Oracle Database, but PL/SQL offers several ways to define and
manipulate cursors to execute SELECT statements.

SELECT-INTO offers the fastest and simplest way to fetch a single row from a SELECT
statement. The syntax of this statement is
SELECT select_list INTO variable_list FROM remainder_of_query;
EXAMPLE:
Get the last name for a specific employee ID (the primary key in the employees table):
DECLARE
l_last_nameemployees.last_name%TYPE;
BEGIN
SELECT last_name
INTO l_last_name
FROM employees
WHERE employee_id = 138;

DBMS_OUTPUT.put_line (
l_last_name);
END;
Fetch an entire row from the employees table for a specific employee ID:
DECLARE
l_employeeemployees%ROWTYPE;
BEGIN
SELECT *
INTO l_employee
FROM employees
WHERE employee_id = 138;

DBMS_OUTPUT.put_line (
l_employee.last_name);
END;

Using the Cursor FOR Loop

The cursor FOR loop is an elegant and natural extension of the numeric FOR loop in PL/SQL. With a
numeric FOR loop, the body of the loop executes once for every integer value between the low and
high values specified in the range. With a cursor FOR loop, the body of the loop is executed for each
row returned by the query.

The following block uses a cursor FOR loop to display the last names of all employees in
department 10:
BEGIN
FOR employee_rec IN (
SELECT *

13
 FROM employees
WHERE department_id = 10)
LOOP
DBMS_OUTPUT.put_line (
employee_rec.last_name);
END LOOP;
END;

PRACTICAL – 6

TITLE: Creating Procedure and Functions

14
A procedure is a block that can take parameters (sometimes referred to as arguments) and be invoked.
Procedures promote reusability and maintainability. Once validated, they can be used in number of
applications. If the definition changes, only the procedure are affected, this greatly simplifies
maintenance. Modularized program development:
 Group logically related statements within blocks.
 Nest sub-blocks inside larger blocks to build powerful programs.
 Break down a complex problem into a set of manageable well defined logical modules
and implement the modules with blocks.

Procedure and function blocks:

Procedure:
- No return.
- PROCEDURE name IS
Function:
- Returns a value
- FUNCTION name RETURN data-type IS
Syntax for procedure:
Create [or Replace] PROCEDURE procedur_name
(parameter1 [model1] datatype1,
(parameter2 [model2] datatype2,
…)
IS|AS
PL/SQL Block;

Example:
Create [or Replace] PROCEDURE leave_emp
(v_id IN [Link]%TYPE)
IS
BEGIN
DELETE FROM emp
WHERE empno=v_id;
END leave_emp;

Syntax for function:

Create [or Replace] function function_name
(parameter1 [model1] datatype1,
(parameter2 [model2] datatype2,
…) return type
IS|AS
PL/SQL Block;

PRACTICAL – 7

TITLE: Creating Packages and triggers

15
Packages are PL/SQL constructs that enable the grouping of related PL/SQL objects, such as
procedures, variables, cursors, functions, constants, and type declarations. Informix Dynamic Server
does not support the package construct.

A package can have two parts: a specification and a body. The specification defines a list of all
objects that are publicly available to the users of the package. The body defines the code that is used
to implement these objects, such as, the code behind the procedures and functions used within the
package.

The general PL/SQL syntax for creating a package specification is:

CREATE [OR REPLACE] PACKAGE package_name {IS | AS}

procedure_specification
..function_specification
..variable_declaration
..type_definition
..exception_declaration
..cursor_declaration
END [package_name];

The general PL/SQL syntax for creating a package body is:

CREATE [OR REPLACE] PACKAGE BODY package_name {IS | AS}

..procedure_definition
..function_definition
..private_variable_declaration
..private_type_definition
..cursor_definition
[BEGIN
executable_statements
[EXCEPTION
..exception_handlers]]
END [package_name];

The package body is optional. If the package contains only variable, cursor and type definitions then
the package body is not required.

As the package specification is accessible to users of the package, it can be used to define global
variable definitions within PL/SQL.

The Migration Workbench automatically creates packages during the conversion process for the
following reasons:

 The Utilities package, which is used to emulate built-in Informix Dynamic Server functions,
is not available in Oracle.

 Packages have to be created to emulate Informix Dynamic Server GLOBAL variable

definitions.

A trigger is a named PL/SQL block stored in the Oracle Database and executed automatically when a
triggering event takes place. The event can be any of the following:

16
A data manipulation language (DML) statement executed against a table e.g., INSERT, UPDATE, or
DELETE. For example, if you define a trigger that fires before an INSERT statement on the
customers table, the trigger will fire once before a new row is inserted into the customers table.
A data definition language (DDL) statement executes e.g., CREATE or ALTER statement. These
triggers are often used for auditing purposes to record changes of the schema.
A system event such as startup or shutdown of the Oracle Database.
A user event such as login or logout.
The act of executing a trigger is also known as firing a trigger. We say that the trigger is fired.

Oracle trigger usages

Oracle triggers are useful in many cases such as the following:

Enforcing complex business rules that cannot be established using integrity constraint such as
UNIQUE, NOT NULL, and CHECK.
Preventing invalid transactions.
Gathering statistical information on table accesses.
Generating value automatically for derived columns.
Auditing sensitive data.

How to create a trigger in Oracle

To create a new trigger in Oracle, you use the following CREATE TRIGGER statement:

CREATE [OR REPLACE] TRIGGER trigger_name

{BEFORE | AFTER }triggering_event ON table_name
[FOR EACH ROW]
[FOLLOWS | PRECEDES another_trigger]
[ENABLE / DISABLE ]
[WHEN condition]
DECLARE
declaration statements
BEGIN
executable statements
EXCEPTION
exception_handling statements
END;

PRACTICAL – 8

TITLE: Design and implementation of Payroll Processing System

Payroll Processing refers to the complete set of steps involved in calculating the total remuneration
of each employee. The process typically involves three to four stages and tasks such as defining

17
salary structures, gathering employee data, components, deductions, allowances, and setting up the
necessary policies with respect to taxes and other adjustments, and then calculating the total salary
after adjusting all the company policies. After the salaries are disbursed, filing, reporting and providing
payslips to employees also comes under the entire payroll processing cycle.
In simplest words, if payroll is the amount paid by the employer to employee, payroll processing is the
whole methodology to accurately calculate the net pay of the employees as per statutory compliances
and company policies.
-- Database
-- Table structure for table loginn
CREATE TABLE loginn (
iddint(10) NOT NULL PRIMARY KEY AUTO_INCREMENT,
e_mailtinytext NOT NULL,
passlongtext NOT NULL
);
-- Dumping data for table loginn
INSERT INTO loginn (idd, e_mail, pass) VALUES
(1, 'dev', 'dev');
-- Table structure for table empp
CREATE TABLE empp (
iddint(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
first_namevarchar(100) NOT NULL,
last_namevarchar(100) NOT NULL,
e_mailvarchar(100) NOT NULL UNIQUE KEY,
pass text NOT NULL,
d_o_b date NOT NULL,
gndrvarchar(10) NOT NULL,
contctvarchar(20) NOT NULL,
nidint(20) NOT NULL,
addrvarchar(100) DEFAULT NULL,
deprtmntvarchar(100) NOT NULL,
degvarchar(100) NOT NULL,
imgg text NOT NULL
);
-- Dumping data for table empp
INSERT INTO empp (idd, first_name, last_name, e_mail, pass, d_o_b, gndr, contct, nid, addr, deprtmnt, deg,
imgg) VALUES
(121, 'Mohit', 'Kumar', 'mohit@[Link]', '1234', '1994-04-04', 'Male', '01919', 12221, 'Razarbagh', 'IT', 'Head',
'images/[Link]'),
(122, 'Mohan', 'Kumar', 'mohan@[Link]', '1234', '2018-01-01', 'Male', '0202', 323, 'Ad_______', 'CS', 'CS',
'images/[Link]'),
(123, 'Ram', 'Singh', 'rams@[Link]', '1234', '1990-02-02', 'Male', '5252', 6222, 'Thames, UK', 'Creative', 'MSc',
'images/[Link]'),
(124, 'Govind', 'Iyer', 'govind@[Link]', '1234', '1971-12-01', 'Male', '9595', 5929, 'Chemsford, USA',
'Creative', 'MSc', 'images/[Link]'),
(125, 'Shyam', 'Manja', 'elon@[Link]', '1234', '1971-06-28', 'Male', '8585', 5258, 'LA, USA', 'SpaceTech',
'BSc', 'images/330px-Elon_Musk_Royal_Society.jpg'),
-- Table structure for table emp_leave
CREATE TABLE emp_leave (
iddint(11) DEFAULT NULL,
tokenint(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
start date DEFAULT NULL,
end date DEFAULT NULL,
reason char(100) DEFAULT NULL,
statuss char(50) DEFAULT NULL,
FOREIGN KEY (idd) REFERENCES empp (idd) ON DELETE CASCADE ON UPDATE CASCADE
);
-- Dumping data for table emp_leave
INSERT INTO emp_leave (idd, token, start, end, reason, statuss) VALUES
(101, 301, '2019-04-07', '2019-04-08', 'Sick Leave', 'Approved'),

18
(102, 302, '2019-04-07', '2019-04-08', 'Urgent Family Cause', 'Approved'),
(103, 303, '2019-04-08', '2019-04-08', 'Concert Tour', 'Approved'),
(105, 304, '2019-04-26', '2019-04-30', 'Launching Tesla Model Y', 'Pending'),
(104, 305, '2019-04-08', '2019-04-09', 'Emergency Leave', 'Pending');
-- Table structure for table projectt
CREATE TABLE projectt (
piddint(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
eiddint(11) DEFAULT NULL,
p_namevarchar(100) DEFAULT NULL,
due_date date DEFAULT NULL,
sub_date date DEFAULT '0000-00-00',
markkint(11) NOT NULL,
statussvarchar(50) DEFAULT NULL,
FOREIGN KEY (eidd) REFERENCES empp (idd) ON DELETE CASCADE ON UPDATE CASCADE
);
-- Dumping data for table projectt
INSERT INTO projectt (pidd, eidd, p_name, due_date, sub_date, markk, statuss) VALUES
(213, 101, 'Database', '2019-04-07', '2019-04-04', 10, 'Submitted'),
(214, 102, 'Test', '2019-04-10', '0000-00-00', 0, 'Due'),
(215, 105, 'Maruti Model Y', '2019-04-19', '2019-04-06', 10, 'Submitted'),
(216, 105, 'Maruti Model X', '2019-04-03', '2019-04-03', 10, 'Submitted'),
(217, 103, 'Statistical', '2019-04-19', '2019-04-04', 6, 'Submitted'),
-- Table structure for table rank
CREATE TABLE rankk (
eiddint(11) NOT NULL PRIMARY KEY,
pointsint(11) DEFAULT 0,
FOREIGN KEY (eidd) REFERENCES empp (idd) ON DELETE CASCADE ON UPDATE CASCADE
);
-- Dumping data for table rank
INSERT INTO rankk (eidd, points) VALUES
(101, 10),(102, 0),(103, 6),(104, 0),(105, 20);
-- Table structure for table salaryy
CREATE TABLE salaryy (
iddint(11) NOT NULL PRIMARY KEY,
baseeint(11) NOT NULL,
bonussint(11) DEFAULT NULL,
totint(11) DEFAULT NULL,
FOREIGN KEY (idd) REFERENCES empp (idd) ON DELETE CASCADE ON UPDATE CASCADE
);
-- Dumping data for table salaryy
INSERT INTO salaryy (idd, basee, bonuss, tot) VALUES
(101, 55000, 10, 60500), (102, 16500, 0, 16500), (103, 65000, 6, 68900), (104, 78000, 0, 78000), (105, 105000,
20, 126000);
COMMIT;

PRACTICAL – 9

TITLE: Design and implementation of Library information system

A Library Management System is a software built to handle the primary housekeeping functions of a

19
library. Libraries rely on library management systems to manage asset collections as well as
relationships with their members. Library management systems help libraries keep track of the books
and their checkouts, as well as members’ subscriptions and profiles.
Library management systems also involve maintaining the database for entering new books and
recording books that have been borrowed with their respective due dates.

CREATE TABLE resr_status (

idd INT,
status_val VARCHAR(50),
CONSTRAINT res_status PRIMARY KEY (idd)
);
CREATE TABLE catgry (
idd INT,
catgry_name VARCHAR(100),
CONSTRAINT catgry PRIMARY KEY (idd)
);
INSERT INTO catgryVALUES(101, 'maths'),(102, 'physics'),(103, 'chemistry'),(104, 'biology'),(105, 'english');
CREATE TABLE bokk (
idd INT,
titlee VARCHAR(500),
catgry_id INT,
pub_date DATE,
copies_owned INT,
CONSTRAINT bokk PRIMARY KEY (idd),
CONSTRAINT bokk_catgry FOREIGN KEY (catgry_id) REFERENCES catgry(idd)
);
CREATE TABLE authr (
idd INT,
first_name VARCHAR(300),
last_name VARCHAR(300),
CONSTRAINT authr PRIMARY KEY (idd)
);
CREATE TABLE bok_authr (
bokk_id INT,
author_id INT,
CONSTRAINT bokkauthor_bokk FOREIGN KEY (bokk_id) REFERENCES bokk(idd),
CONSTRAINT bokkauthor_author FOREIGN KEY (author_id) REFERENCES authr(idd)
);
CREATE TABLE membr_status (
idd INT,
status_val VARCHAR(50),
CONSTRAINT membrstatus PRIMARY KEY (idd),
);
CREATE TABLE membr (
idd INT,
first_name VARCHAR(300),
last_name VARCHAR(300),
joined_date DATE,
actv_status_id INT,
CONSTRAINT membr PRIMARY KEY (idd),
CONSTRAINT membr_status FOREIGN KEY (actv_status_id) REFERENCES membr_status(idd)
);
CREATE TABLE resrvtn (
idd INT,
bokk_id INT,
membr_id INT,
resrvtn_date DATE,
resrvtn_status_id INT,
CONSTRAINT resrvtn PRIMARY KEY (idd),

20
CONSTRAINT res_bokk FOREIGN KEY (bokk_id) REFERENCES bokk(idd),
CONSTRAINT res_membr FOREIGN KEY (membr_id) REFERENCES membr(idd)
);
CREATE TABLE finee_paymtn (
idd INT,
membr_id INT,
paymtn_date DATE,
paymtn_amount INT,
CONSTRAINT finee_paymtn PRIMARY KEY (idd),
CONSTRAINT fineepay_membr FOREIGN KEY (membr_id) REFERENCES membr(idd)
);
CREATE TABLE loann (
idd INT,
bokk_id INT,
membr_id INT,
loann_date DATE,
retrned_date DATE,
CONSTRAINT loann PRIMARY KEY (idd),
CONSTRAINT loann_bokk FOREIGN KEY (bokk_id) REFERENCES bokk(idd),
CONSTRAINT loann_membr FOREIGN KEY (membr_id) REFERENCES membr(idd)
);
CREATE TABLE finee (
idd INT,
bokk_id INT,
loann_id INT,
finee_date DATE,
finee_amount INT,
CONSTRAINT finee PRIMARY KEY (idd),
CONSTRAINT finee_bokk FOREIGN KEY (bokk_id) REFERENCES bokk(idd),
CONSTRAINT finee_loann FOREIGN KEY (loann_id) REFERENCES loann(idd)
);
COMMIT;

PRACTICAL – 10

TITLE: Design and Implementation of Student Information System

Aim

21
Design and implement a simple Student Information System (SIS) database to store and
manage student, teacher, course, and post data. Provide a clean schema and sample data for
lab submission.

Brief Description
A Student Information System (SIS) centralizes academic records — student profiles,
courses, teachers, attendance, and posts — allowing easy access for administrators, teachers,
and students.

-- Departments / Courses
CREATE TABLE course (
course_id INT AUTO_INCREMENT PRIMARY KEY,
short_name VARCHAR(50) NOT NULL,
full_name VARCHAR(250) NOT NULL,
start_date DATE
);

-- Students
CREATE TABLE student (
student_id INT AUTO_INCREMENT PRIMARY KEY,
roll_no VARCHAR(50) NOT NULL,
standard VARCHAR(50),
username VARCHAR(50) NOT NULL UNIQUE,
full_name VARCHAR(150) NOT NULL,
gender VARCHAR(10),
contact VARCHAR(20),
email VARCHAR(255) NOT NULL UNIQUE,
password VARCHAR(255) NOT NULL, -- store hashed passwords in real apps
city VARCHAR(100),
image VARCHAR(255)
);

-- Teachers
CREATE TABLE teacher (
teacher_id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(255) NOT NULL UNIQUE,
contact VARCHAR(20),
gender VARCHAR(10),
position VARCHAR(100),
password VARCHAR(255) NOT NULL,
address VARCHAR(255),
image VARCHAR(255)
);

22
-- Followers (relationships)
CREATE TABLE follow (
id INT AUTO_INCREMENT PRIMARY KEY,
to_user_id INT NOT NULL,
from_user_id INT NOT NULL
);

-- Posts / Announcements
CREATE TABLE post (
post_id INT AUTO_INCREMENT PRIMARY KEY,
author_id INT NOT NULL,
content TEXT NOT NULL,
image VARCHAR(255),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Sample data (minimal, clean)

INSERT INTO course (short_name, full_name, start_date) VALUES
('MCA', 'Master of Computer Applications', '2019-04-25'),
('MSC', 'Master of Science', '2019-04-25');

INSERT INTO student (roll_no, standard, username, full_name, gender, contact, email,
password, city, image) VALUES
('1', 'BCA', 'mohit', 'Mohan Kumar', 'Male', '9867503256', '[Link]@[Link]', 'kumar',
'Thane', '[Link]'),
('2', 'BSc', 'rohan', 'Rohan Singh', 'Female', '9867503256', 'rohan@[Link]', 'kumar',
'Sipah', '[Link]');

INSERT INTO teacher (name, email, contact, gender, position, password, address, image)
VALUES
('Mohan Kumar', '[Link]@[Link]', '9555555256', 'Male', 'Manager', 'kumar', 'Kausa,
Mumbra, Thane', '[Link]');

INSERT INTO post (author_id, content, image) VALUES

(1, 'Welcome to the Student Information System.', NULL);

COMMIT;
PRACTICAL – 11

TITLE: Automatic Backup of Files and Recovery of Files

23
Aim:
Implement an automatic backup solution for database and application files and demonstrate
recovery steps to restore data reliably.

Objectives
 Learn automated backup techniques for database and file systems.
 Write backup scripts for MySQL (or general files) and schedule them.
 Demonstrate restoring data from backups.
 Validate backup integrity and implement basic retention.

Tools & Environment

 Operating System: Linux (Ubuntu) or Windows (PowerShell).
 Database: MySQL / MariaDB (or export method for any DB).
 Utilities: mysqldump, tar, gzip, cron (Linux) or Task Scheduler (Windows).
 Text editor: vim / nano / Notepad++.

Theory
Backups copy data to a safe location so it can be restored if originals are lost or corrupted. For
databases, logical backups (dumps) and physical backups (data files) are common. Automation uses
scripts and schedulers to run backups regularly and enforce retention policies.

Procedure
Follow these steps on a Linux machine (adaptable to Windows with PowerShell equivalents).

1) Prepare backup directories

sudo mkdir -p /var/backups/sis
sudo chown $USER:$USER /var/backups/sis

2) Create a MySQL backup script (backup_mysql.sh)

#!/bin/bash
# backup_mysql.sh - logical backup using mysqldump
BACKUP_DIR="/var/backups/sis/mysql"
mkdir -p "$BACKUP_DIR"
TIMESTAMP=$(date +"%F_%H%M")
DB_USER="root"
DB_PASS="your_password"
DB_NAME="sis_db"

mysqldump -u${DB_USER} -p${DB_PASS} ${DB_NAME} | gzip > ${BACKUP_DIR}/${DB_NAME}_$

{TIMESTAMP}.[Link]
# remove backups older than 7 days
find ${BACKUP_DIR} -type f -name "*.[Link]" -mtime +7 -delete
 Make script executable: chmod +x backup_mysql.sh.

3) Create a file-system backup script (backup_files.sh)

24
#!/bin/bash
# backup_files.sh - archive important folders
BACKUP_DIR="/var/backups/sis/files"
mkdir -p "$BACKUP_DIR"
TIMESTAMP=$(date +"%F_%H%M")
SOURCE_DIRS=("/home/sis/appdata" "/etc/sis")
tar -czf ${BACKUP_DIR}/files_${TIMESTAMP}.[Link] "${SOURCE_DIRS[@]}"
# rotate: keep 14 days
find ${BACKUP_DIR} -type f -name "*.[Link]" -mtime +14 -delete

4) Schedule with cron

 Edit cron with crontab -e and add entries:
# Daily MySQL backup at 02:00
0 2 * * * /path/to/backup_mysql.sh >> /var/log/sis_backup.log 2>&1
# Daily files backup at 03:00
0 3 * * * /path/to/backup_files.sh >> /var/log/sis_backup.log 2>&1

5) Test backup and recovery

a. Test backup
 Run scripts manually and verify files in /var/backups/sis.
b. Recover MySQL from dump
# decompress and restore
gunzip -c /var/backups/sis/mysql/sis_db_2025-08-01_0200.[Link] | mysql -u root -p sis_db_restore
c. Recover files
tar -xzf /var/backups/sis/files/files_2025-08-01_0300.[Link] -C /tmp/sis_restore

Verification & Validation

 Check backup file sizes and timestamps.
 Run mysqlcheck or run test restores on a dev instance to confirm integrity.
 Keep a log /var/log/sis_backup.log and monitor for errors.

Conclusion
Automating backups using scripts and schedulers ensures regular, reliable copies of critical data.
Periodic test restores are essential to guarantee recovery.

PRACTICAL – 12

TITLE: Mini project (Design & Development of Data and Application)

25
a) Inventory Control System.
Aim:
Design and implement a database to manage inventory — record stock in/out, maintain balances,
and support basic reports.

OBJECTIVE: Objective of inventory control system project is to design a database to record proper
variety of required items in inventory, maintain optimized inventory, safety stock levels and obtain
low raw material prices, storage cost, insurance cost, taxes

1. Entities (brief)
 Product(product_id, name, category, department)
 Supplier(supplier_id, name, contact)
 Stock_In(in_id, product_id, supplier_id, qty, price_per_unit, date)
 Stock_Out(out_id, product_id, qty, purpose, date)
 Monthly_Closing(closing_id, product_id, opening_qty, in_qty, out_qty, closing_qty,
closing_value, month, year)
2. Relational Schema (copy-ready)
PRODUCT(product_id PK, name, category, department)
SUPPLIER(supplier_id PK, name, contact)
STOCK_IN(in_id PK, product_id FK -> PRODUCT(product_id), supplier_id FK -> SUPPLIER(supplier_id),
qty, price_per_unit, date)

26
STOCK_OUT(out_id PK, product_id FK -> PRODUCT(product_id), qty, purpose, date)
MONTHLY_CLOSING(closing_id PK, product_id FK -> PRODUCT(product_id), opening_qty, in_qty,
out_qty, closing_qty, closing_value, month, year)

3. SQL — Create Tables (MySQL syntax)

-- Products
CREATE TABLE Product (
product_id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(150) NOT NULL,
category VARCHAR(80),
department VARCHAR(80)
);

-- Suppliers
CREATE TABLE Supplier (
supplier_id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(150) NOT NULL,
contact VARCHAR(50)
);

-- Stock In (receipts)
CREATE TABLE Stock_In (
in_id INT AUTO_INCREMENT PRIMARY KEY,
product_id INT NOT NULL,
supplier_id INT,
qty INT NOT NULL,
price_per_unit DECIMAL(10,2) NOT NULL,
date DATE NOT NULL,
FOREIGN KEY (product_id) REFERENCES Product(product_id),
FOREIGN KEY (supplier_id) REFERENCES Supplier(supplier_id)
);

-- Stock Out (issues)

CREATE TABLE Stock_Out (
out_id INT AUTO_INCREMENT PRIMARY KEY,
product_id INT NOT NULL,
qty INT NOT NULL,
purpose VARCHAR(255),
date DATE NOT NULL,
FOREIGN KEY (product_id) REFERENCES Product(product_id)
);

-- Monthly Closing
CREATE TABLE Monthly_Closing (
closing_id INT AUTO_INCREMENT PRIMARY KEY,
product_id INT NOT NULL,
opening_qty INT DEFAULT 0,
in_qty INT DEFAULT 0,
out_qty INT DEFAULT 0,
closing_qty INT DEFAULT 0,
closing_value DECIMAL(12,2) DEFAULT 0.00,

27
 month TINYINT NOT NULL, -- 1..12
year SMALLINT NOT NULL,
FOREIGN KEY (product_id) REFERENCES Product(product_id)
);

4. Sample Data (INSERT)

-- Products
INSERT INTO Product (name, category, department) VALUES
('Bolt M8', 'Hardware', 'Maintenance'),
('Lubricant 1L', 'Chemical', 'Maintenance'),
('Notebook A4', 'Stationery', 'Office');

-- Suppliers
INSERT INTO Supplier (name, contact) VALUES
('ABC Traders', '9876543210'),
('Global Supplies', '9123456780');

-- Stock In
INSERT INTO Stock_In (product_id, supplier_id, qty, price_per_unit, date) VALUES
(1, 1, 100, 2.50, '2025-08-01'),
(2, 2, 50, 120.00, '2025-08-02'),
(3, 1, 200, 15.00, '2025-08-03');

-- Stock Out
INSERT INTO Stock_Out (product_id, qty, purpose, date) VALUES
(1, 10, 'Maintenance job #A1', '2025-08-05'),
(3, 20, 'Office usage', '2025-08-06');

-- Monthly Closing (example for Aug 2025)

INSERT INTO Monthly_Closing (product_id, opening_qty, in_qty, out_qty, closing_qty, closing_value,
month, year) VALUES
(1, 50, 100, 10, 140, 350.00, 8, 2025),
(2, 10, 50, 0, 60, 7200.00, 8, 2025),
(3, 30, 200, 20, 210, 3150.00, 8, 2025);

5. Normalization (short)
 Each table represents a single entity → attributes atomic.
 Product, Supplier separate → avoids duplication.
 Stock_In and Stock_Out are transaction tables.
 Monthly_Closing stores aggregated monthly figures (derived), used for reporting.
(Design is in 3NF.)

6. Sample Queries (must include in lab file)

a) Basic SELECT
SELECT * FROM Product;
b) Restrict & Sort
SELECT name, category FROM Product WHERE department = 'Maintenance' ORDER BY name;
c) Join — latest stock in with product
SELECT p.product_id, [Link], [Link], si.price_per_unit, [Link], [Link] AS supplier
FROM Stock_In si
JOIN Product p ON si.product_id = p.product_id

28
LEFT JOIN Supplier s ON si.supplier_id = s.supplier_id
ORDER BY [Link] DESC;
d) Aggregate: total stock in per product
SELECT [Link], SUM([Link]) AS total_in
FROM Stock_In si
JOIN Product p ON si.product_id = p.product_id
GROUP BY p.product_id, [Link];
e) Compute current stock (opening + in - out)
SELECT p.product_id, [Link],
COALESCE(mc.opening_qty,0) + COALESCE(SUM([Link]),0) - COALESCE(SUM([Link]),0) AS
current_stock
FROM Product p
LEFT JOIN Monthly_Closing mc ON p.product_id = mc.product_id AND [Link] = 8 AND [Link] =
2025
LEFT JOIN Stock_In si ON p.product_id = si.product_id
LEFT JOIN Stock_Out so ON p.product_id = so.product_id
GROUP BY p.product_id, [Link], mc.opening_qty;
f) Update stock (example)
-- Correct a stock-in record price
UPDATE Stock_In SET price_per_unit = 2.60 WHERE in_id = 1;
g) Delete (example)
DELETE FROM Stock_Out WHERE out_id = 999; -- use real id after verifying

7. Expected Output Examples (paste these below queries in lab file)

Query: SELECT * FROM Product;
Output table:
product_id | name | category | department
1 | Bolt M8 | Hardware | Maintenance
2 | Lubricant 1L | Chemical | Maintenance
3 | Notebook A4 | Stationery | Office
Query: SELECT [Link], SUM([Link]) FROM Stock_In si JOIN Product p ... GROUP BY [Link];
Output:
name | SUM([Link])
Bolt M8 | 100
Lubricant 1L | 50
Notebook A4 | 200

b) Material Requirement Processing.

OBJECTIVE
Material Requirements Processing (MRP) is a standard supply planning method used to help
businesses—especially manufacturing organizations—determine inventory requirements,
schedule production, and balance supply with customer demand. The objective of this project

29
is to design a database that supports MRP operations by managing raw materials, production
units, item allocations, and production costing.

Scope of Mini Project

Scope of project will include the following:
 Develop ER Diagrams
 Design Database by converting ER Diagrams into Tables
 Development of DDL, DML and Database Queries to achieve the following
functionalities

● Identifying Requirements
The MRP process begins with identifying customer orders and sales forecasts. The system
determines what materials are required and the quantities needed to meet production demand.
● Breaking Down Material Structures
Using the bill of materials (BOM), the system disassembles demand into individual raw
materials and required components. This ensures accurate planning for assemblies and sub-
assemblies.
● Checking Inventory & Allocating Resources
MRP checks existing inventory and determines what items are in stock, already allocated, in
transit, or on order. This helps allocate resources efficiently and ensures timely availability.
● Scheduling Production
Based on production needs, the system generates schedules, calculates required labor and
machinery, and helps plan production steps. For assemblies, MRP calculates the time
required for each sub-process.
● Monitoring Production & Usage
The system tracks production flow, raw material consumption, and work-in-progress items. It
moves inventory into proper locations and provides recommendations to reorder materials
when thresholds are reached.
● Identifying Issues & Making Recommendations
The system identifies shortages, overconsumption, delays, and under-utilized resources. It
assists management by providing automated suggestions for improving efficiency and
production flow.

Technology to be Used – MySQL

Project Deliverables
 ER Diagrams
 Converting ER Diagrams into Tables
 Making SQL Queries

30
31
c) Hospital Management System.
OBJECTIVE

The objective of the Hospital Management System mini-project is to design a database

for recording and managing hospital-related information. This system reduces the
burden of manual handling across departments such as reception, laboratory,
inpatient/outpatient services, diagnostics, doctor details, and billing. It improves
accuracy, accessibility, and processing efficiency.

12.2 THEORY AND CONCEPTS

Scope of Mini Project

(Project description listing out functionalities / features to be developed)

Scope of project will include the following:

 Develop ER Diagrams

 Design Database by converting ER Diagrams into Tables

 Development of DDL, DML and Database Queries to achieve the following

functionalities

● Out-Patient, In-Patient & Doctor Details Management

Enter, modify, and delete records of out-patients, in-patients, and doctors. Maintain
structured information for all hospital departments.

● Treatment Details & Diagnostic Charges

Enter, update, and delete patients’ treatment details, diagnostic procedures, and
charges associated with all medical activities.

● Appointment Scheduling

Provide facilities for booking appointments with doctors, recording visit details, and
managing follow-ups.

● Viewing Patient & Doctor Information

View complete patient details including medical history, assigned doctor, diagnosis,
and treatments. Also view doctor specialization, availability, and schedules.

● Search Facility

Search based on:

 Doctor specialization

32
  Availability of doctors

 Diagnostic test charges

 ICU and Operation Theatre charges

 Room category and availability

● Billing

Generate and view patient bills including consultation, diagnostic tests, treatments,
room charges, and other services.

Technology to be Used – MySQL

Timeline of Mini Project

(In terms of number of lab classes required for completion) – 6 hours

Team Size

3 students per project team

Project Deliverables

 ER Diagrams

 Converting ER Diagrams into Tables

 Making SQL Queries

33
d) Railway Reservation System.
OBJECTIVE: Objective of Railway reservation system project is to design a database to efficiently
manage Railway Reservation SYSTEM for a city tour.

THEORY AND CONCEPTS:

Scope of Mini Project (project description listing out functionalities / features to be developed)

Scope of project will include the following:

o Develop ER Diagrams

o Design Database by converting ER Diagrams to Tables

o Development of DDL, DML and Database Queries to achieve the following

functionalities

 Enter, modify, delete staffs details

 Enter, modify, delete passengers details

 Enter, modify and delete Train details (Train No, Category, Vendor etc.)

 View details of reservation day wise, month wise etc.

 View details of regular passengers

 View staffs details categories wise

34
  Generate reports of unsold seat train wise, Class wise etc.

o Technology to be used – MySql

o Timeline of Mini Project (in terms of no. of lab classes required for project completion)– 6
hrs

o Team size – 3 students/project team

o Any special requirement of h/w, s/w environment, tools -No

35
e) Personal Information System.
OBJECTIVE: Objective of personal information system project is
Personal Information Management Systems (or PIMS) are systems that help give individuals
more control over their personal data. PIMS allow individuals to manage their personal data
in secure, local or online storage systems and share them when and with whom they choose.
Providers of online services and advertisers will need to interact with the PIMS if they plan to
process individuals’ data. This can enable a human centric approach to personal information
and new business models.

THEORY AND CONCEPTS:

Scope of Mini Project (project description listing out functionalities / features to be developed)

Scope of project will include the following:

o Develop ER Diagrams

o Design Database by converting ER Diagrams to Tables

o Development of DDL, DML and Database Queries to achieve the following

functionalities

o Database: PIS core offering consists of a database for storing employee information.
HR professionals can store all personnel data into the system that can be accessed
from any time, from anywhere.

o Time and Labor Management: Functions like time and labor management requires a
lot of time. PIS packages allow employees to input their hours worked and help
managers to instantly verify vacation requests, and the information is fed to the
payroll directly.

o Payroll Activity: Payroll activity is another important component of an HRIS or PIS

model. HR can unload or download employee hours easily, and issue checks or
payroll deposits to employees.

o Benefits: Some PIS packages allow employers to develop and maintain medical,
retirement, and other benefits through their software.

36
 o Employee Interface: Most PIS packages allow limited user access for an employee.

o Hiring and Retention: Hiring and retention are the most crucial components of PIS.

Technology to be used – MySql

Timeline of Mini Project (in terms of no. of lab classes required for project completion)– 6
hrs
Team size – 3 students/project team
Project deliverables
ER Diagrams, Converting ER Diagrams into Tables and Making SQL Queries.
Any special requirement of h/w, s/w environment, tools -No

37
f) Web Based User Identification System.

OBJECTIVE: The process of identifying users depends on the type of device they are using (e.g. a
smartphone or laptop) and whether they are using a web browser or mobile app. For example, a user
visiting web pages in a web browser, either on a mobile device or computer, would be identified by
browser-based identification methods. A user playing a mobile-app game on a smartphone or tablet
would be identified by a mobile identifier.

THEORY AND CONCEPTS:

Scope of Mini Project (project description listing out functionalities / features to be developed)

Scope of project will include the following:

o Develop ER Diagrams

o Design Database by converting ER Diagrams to Tables

o Development of DDL, DML and Database Queries to achieve the following

functionalities

o A user identification or user ID is an entity used to identify a user on a website,

software, system or within a generic IT environment. It is the most common
authentication mechanism used within computing systems.

o Regardless of the type of user and their rights, each user has a unique identification
that distinguishes it from other users. System administrators use these IDs to assign
privileges, track user activity and manage overall operations on a particular system,
network or application.

o Many analytics technologies can’t identify unique users if they use multiple devices
across multiple sessions, as each time a user does this, a new user is counted. By
having a unique user ID, this eliminates this issue, allowing for all activity being
attributed to one user in an analytics report

38
Technology to be used – MySql

Timeline of Mini Project (in terms of no. of lab classes required for project completion)– 6 hrs

Team size – 3 students/project team

Any special requirement of h/w, s/w environment, tools

g) Timetable Management System.

OBJECTIVE: Timetable management software helps design timetables and mark attendance for
teachers and [Link] helps to regulate proper schedules and allocate faculty according to their
availability by outlining the classes, sections, and other details fed into the [Link] a digital
system for timetable management enhances the authenticity of data as it would be sensitive towards
manipulations and boosts efficiency due to workflow automation

THEORY AND CONCEPTS:

Scope of Mini Project (project description listing out functionalities / features to be developed)

Scope of project will include the following:

o Develop ER Diagrams

o Design Database by converting ER Diagrams to Tables

o Development of DDL, DML and Database Queries to achieve the following

functionalities

o unplanned systems can be taxing: A well-timed routine can help by keeping students
and teachers involved and disciplined. It will also save time by lowering the amount
of time spent in chaos and confusion.

o Rearrangement of the timetable based on circumstances: When necessary, new

classes could be added or removed from the timetable.

o Event and holiday calendar planner: Holidays and events could be added in the
backend which can further be viewed by the user in the form of a fully furnished
yearly calendar.

39
Technology to be used – MySql

Timeline of Mini Project (in terms of no. of lab classes required for project completion)– 6 hrs

Team size – 3 students/project team.

Any special requirement of h/w, s/w environment, tools –No

h) Hotel Management System

OBJECTIVE: Objective of this project is to design database for recording and managing day to day
activity of a hotel to reduce the burden of handling manually the activities of all sections of hotel like
reception, employee, booking status, Visitors check in, check out status, and billing etc., which
improve the processing efficiency.

THEORY AND CONCEPTS:

Scope of Mini Project (project description listing out functionalities / features to be developed)Scope
of project will include the following:

o Develop ER Diagrams

o Design Database by converting ER Diagrams to Tables

o Development of DDL, DML and Database Queries to achieve the following

functionalities

 Enter, modify, delete employee details

 Enter, modify, delete visitors details

 Enter, modify, and delete Room details (Room No, Category etc.)

 View details of reservation day wise, month wise etc.

 View staffs details categories wise

 Generate reports of unsold Room Fare wise, Class wise etc.

40
Technology to be used – MySql
Timeline of Mini Project (in terms of [Link] lab classes required for project completion)– 6 hrs
Team size – 3 students/project team.
Any special requirement of h/w, s/w environment, tools –No

41