Database Management Systems Lab

(BCS-551)

B.TECH – III YEAR

(ODD
ODD SEM 2024-2025)

Student Name

Roll No.

Section-Batch

Submitted To Mr. Manoj Kumar Sharma

Sharma/Assistant
/Assistant professor

DEPARTMENT OF COMPUTER SCIENCE & ENGINEERING

VISHVESHWARYA GROUP OF INSTITUTIONS

(Affiliatedto Dr A P J Abdul Kalam Technical University, Lucknow)

GENERAL LABORATORY INSTRUCTIONS

1. Students are advised to come to the laboratory at least 5 minutes before (to the starting
time), those who come after 5 minutes will not be allowed into the lab.

2. Plan your task properly much before to the commencement, come prepared to the lab with
the synopsis /program/experiment details.

3.Student should enter into the laboratory with:

 Laboratory observation notes with all the details (Problem statement, Aim, Algorithm,
Procedure, Program, Expected Output, etc.,) filled in for the lab session.

 Laboratory Record updated up to the last session experiments and other utensils (if
any) needed in the lab.

 Proper Dress code and Identity card.

4. Sign in the laboratory login register, write the TIME-IN, and occupy the computer system
allotted to you by the faculty.

5. Execute your task in the laboratory, and record the results / output in the lab observation
note book, and get certified by the concerned faculty.

6. All the students should be polite and cooperative with the laboratory staff, must maintain
the discipline the laboratory.

7. Computer labs are established with sophisticated and high end branded systems, which
should be utilized properly.

8. Students / Faculty must keep their mobile phones in SWITCHED OFF mode during the lab
sessions. Misuse of the equipment, misbehaviors with the staff and systems etc., will attract
severe punishment.

9. Students must take the permission of the faculty in case of any urgency to go out; if any
body found loitering outside the lab / class without permission during working hours will be
treated seriously and punished appropriately.

10. Students should LOG OFF/ SHUT DOWN the computer system before he/she leaves the
lab after completing the task (experiment) in all aspects. He/she must ensure the system / seat
is kept properly.

4
 DETAILS OF THE EXPERIMENTS CONDUCTED
(TO BE USED BY THE STUDENTS IN THEIR RECORDS)

INDEX

S.No TITLE OF THE EXPERIMENT DATE OF FACULTY

SUBMISSION SIGNATURE

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

5
 PRACTICAL -1

TITLE: ORACLE Installation

1.1 Objective 1.2 Installation

1.1 OBJECTIVE: learn the procedure of ORACLE installation

1.2 ORACLE INSTALLATION:

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 setup.exe 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.

6
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.

7
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:

https://<hostname>:1158/em

8
 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.

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. Gotohttps://www.oracle.com/downloads/index.html
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

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

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

10
 PRACTICAL -2

TITLE: Creating Entity-Relationship Diagram using case tools

2.1 Objective 2.2 Theory (Installation&Dia Guide) 2.3 Assignment 2.4 Solution to Assignment

2.1 OBJECTIVE: applying tool to create ER model

2.2 THEORY AND CONCEPTS:

Introduction to Dia Diagram Editor:

a. Installation Guide

1. Download the installation file for your platform from http://dia-installer.de/.

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

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

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

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

12
2.3 Assignment:
A university wants to set up a database to record details about its staff, and the departments they
belong to. They intend to record the following information.
• For each member of staff, their staff identity number, name, job title, and salary.
• For each department, its name and address.
• For each member of staff, all departments that they belong to. It is required that every member of
staff belongs to at least one department.
• For each department, the head of department. It is required that each department has exactly one
head of department.
Draw an ER diagram that expresses the requirements for the database. Make sure that you capture
all the constraints on the data mentioned above.

2.4 Solution to Assignment:

Here is one possible ER diagram:

6
 PRACTICAL -3

TITLE : CREATING TABLES, INSERT INTO TABLES & SELECT FROM TABLES

3.1 Objective 3.2Theory and concepts 3.3Assignment 3.4. Solutions

3.1 OBJECTIVE: Create tables and specify the queries in SQL

3.2 THEORY& CONCEPTS:

Introduction about SQL-
SQL (Structured Query Language) is a nonprocedural language, you specify what you want, not how
to get it. A block structured format of English key words is used in this query language. It has the
following components.

DDL (Data Definition Language)-

The SQL DDL provides command for defining relation schemas, deleting relations and modifying
relation schema.

DML (DATA Manipulation Language)-

It includes commands to insert tuples into, delete tuples from and modify tuples in the database.

View definition-
The SQL DDL includes commands for defining views.
Transaction Control- SQL includes for specifying the beginning and ending of transactions.

Embedded SQL and Dynamic SQL-

Embedded and Dynamic SQL define how SQL statements can be embedded with in general purpose
programming languages, such as C, C++, JAVA, COBOL, Pascal and Fortran.

Integrity-
The SQL DDL includes commands for specifying integrity constraints that the data stored in the
database must specify. Updates that violate integrity constraints are allowed.

Authorization-
The SQL DDL includes commands for specifying access rights to relations and views.

Data Definition Language-

The SQL DDL allows specification of not only a set of relations but also information about each
relation, including-
 Schema for each relation
 The domain of values associated with each attribute.
 The integrity constraints.
 The set of indices to be maintained for each relation.
 The security and authorization information for each relation.
 The physical storage structure of each relation on disk.

7
Domain types in SQL-

The SQL standard supports a variety of built in domain types, including-

 Char (n) - A fixed length character length string with user specified length .
 Varchar (n) - A variable character length string with user specified maximum length n.
 Int- An integer.
 Small integer- A small integer.
 Numeric (p, d)-A Fixed point number with user defined precision.
 Real, double precision- Floating point and double precision floating point numbers with
machine dependent precision.
 Float (n)- A floating point number, with precision of at least n digits.
 Date- A calendar date containing a (four digit) year, month and day of the month.
 Time- The time of day, in hours, minutes and seconds Eg. Time ’09:30:00’.
 Number- Number is used to store numbers (fixed or floating point).

DDL statement for creating a table-

Syntax-
Create table tablename
(column name datatype(size), column name datatype(size));

Creating a table from a table-

Syntax-
CREATE TABLE TABLENAME
[(columnname, columnname, ………)]
AS SELECT columnname, columnname……..FROM tablename;

Insertion of data into tables-

Syntax-
INSERT INTO tablename
[(columnname, columnname, ………)]
Values(expression, expression);

Inserting data into a table from another table:

Syntax-
INSERT INTO tablename
SELECT columnname, columnname, …….
FROM tablename;

Insertion of selected data into a table from another table:

Syntax-
INSERT INTO tablename
SELECT columnname, columnname……..
FROM tablename
WHERE columnname= expression;

8
TITLE :DATA MANIPULATION LANGUAGE

3.1 Objective 3.2 Theory and concepts 3.3 Assignment 3.4. Solution to Assignment

3.1 OBJECTIVE:Perform manipulation operations on created tables.

3.2 THEORY AND CONCEPTS:DML ( Data Manipulation Language) Data manipulation is

 The retrieval of information stored in the database.

 The insertion of new information into the database.
 The deletion of information from the database.
 The modification of information stored by the appropriate data model. There are basically
two types.
(i) Procedural DML:- require a user to specify what data are needed and how to get those
data.
(ii) Non Procedural DML : require a user to specify what data are needed without specifying
how to get those data.

Updating the content of a table:

In creation situation we may wish to change a value in table without changing all values in the tuple .
For this purpose the update statement can be used.

Update table name

Set columnname = experision, columnname =expression……
Where columnname = expression;

Deletion Operation:-
A delete request is expressed in much the same way as query. We can delete whole tuple ( rows) we
can delete values on only particulars attributes.

Deletion of all rows

Syntax:
Delete from tablename :

Deletion of specified number of rows

Syntax:

Delete from table name

Where search condition ;

Computation in expression lists used to select data

+ Addition - Subtraction
* multiplication ** exponentiation
/ Division () Enclosed operation

Renaming columns used with Expression Lists: - The default output column names can be renamed
by the user if required

12
CH3965 P00001 5
CH3965 P07975 2

Answer the following queries

Q1. Make the primary key to client_no in client_master.

Q2. Add a new column phone_no in the client_master table.
Q3. Add the not null constraint in the product_master table with the columns description, profit
percent , sell price and cost price.
Q4. Change the size of client_no field in the client_master table.
Q5. Select product_no, description where profit percent is between 20 and 30 both inclusive.

3.4 SOLUTION TO ASSIGNMENT:

Q.1. Create the following tables:

Create table challan_header (
Chalan_novarchar2(6) primary key,
S_order_novarchar2(6) foreign key references s_order_no(sales_order),
Challan_date date not null,
Billed_ynchar(i) check billed_yn in (‘Y’ or ‘N’) default ‘N’);

create table challan_details(

challan_novarchar2(6) primary key foreign key references product_no(product_master),
qty_disp number(4,2) not null);

Q.2. Insert the following values into the challan header and challan_details tables:
Insert into challan_header
Values(‘ch9001’,’019001’,’12-dec-95’,’Y’);

Insert into challan_details

Values(‘ch9001’,’p00001’,’4’);

22
 PRACTICAL - 4

TITLE : Normalization

4.1 Objective 4.2 Theory and concepts4.3 viva Question

4.1 OBJECTIVE: Use of Normalization

4.2 THEORY AND CONCEPTS: 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.

As with many formal rules and specifications, real world scenarios do not always allow for perfect
compliance. In general, normalization requires additional tables and some customers find this
cumbersome. If you decide to violate one of the first three rules of normalization, make sure that
your application anticipates any problems that could occur, such as redundant data and inconsistent
dependencies.

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.

23
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

 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.

For example, in an Employee Recruitment table, a candidate's university name and address may be
included. But you need a complete list of universities for group mailings. If university information is
stored in the Candidates table, there is no way to list universities with no current candidates. Create
a separate Universities table and link it to the Candidates table with a university code key.

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:

24
 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

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

25
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

26
 PRACTICAL – 5

TITLE: Creating CURSOR

5.1 Objective 5.2 Theory and concepts 5.3 Example for practice 5.4 viva Question

5.1 OBJECTIVE: Use of cursor

5.2 THEORY AND CONCEPTS:

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

27
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 *
FROM employees
WHERE department_id = 10)
LOOP
DBMS_OUTPUT.put_line (
employee_rec.last_name);
END LOOP;
END;

5.3 Example for practice

EXAMPLE:

BEGIN
updateabcd set no=22 where name='ramDA';
if SQL%FOUND then
dbms_output.put_line('success');
else
dbms_output.put_line('failed');
end if;
end;

DECLARE
ROWS_AFFECTED CHAR(4);
BEGIN
updateabcd set no=23 where name='ram';
ROWS_AFFECTED :=TO_CHAR(SQL%ROWCOUNT);
if SQL%ROWCOUNT>0 then
dbms_output.put_line(ROWS_AFFECTED ||'success');
else
dbms_output.put_line('failed');
end if;
end;

28
 PRACTICAL – 6

TITLE: Creating Procedure and Functions

6.1 Objective 6.2 Theory and concepts 6.3 Example for practice 6.4 Viva Question

6.1 OBJECTIVE: learning of procedure and functions in SQL

6.2 THEORY AND CONCEPTS:

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 emp.empno%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;

Example:

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

6.3 Example for practice

PROCEDURE FOR GCD NUMBERS

SQL> create or replace procedure pro
is
a number(3);
b number(3);
c number(3);
d number(3);
begin
a:=&a;
b:=&b;
if(a>b) then
c:=mod(a,b);
if(c=0) then
dbms_output.put_line('GCD is');
dbms_output.put_line(b);
else
dbms_output.put_line('GCD is');
dbms_output.put_line(c);
end if;
else d:=mod(b,a);
if(d=0) then
dbms_output.put_line('GCD is');
dbms_output.put_line(a);
else
dbms_output.put_line('GCD is');
dbms_output.put_line(d);
end if;
end if;
end;
/

Enter value for a: 8

old 8: a:=&a;
new 8: a:=8;
Enter value for b: 16
old 9: b:=&b;
new 9: b:=16;
Procedure created.

SQL> set serveroutput on;

SQL> execute pro;
GCD is 8
PL/SQL procedure successfully completed.

30
 if(mod(I,j)=0) then
c:=c+1;
end if;
j:=j+1;
end loop;
if(c=2) then
dbms_output.put_line(i);
tot:=tot+1;
end if;
i:=i+1;
end loop;
end;
/
Sql>procedure created.
declare
t number;
begin
prime_proc(10,t);
dbms_output.put_line(‘the total prime no .are’||t);
end;

Valid Test Data:

sql>set serveroutput on

OUTPUT
sql>/
2
3
5
7
The total prime no.are 4

32
 PRACTICAL – 7

TITLE: Creating Packages and triggers

7.1 Objective 7.2 Theory and concepts 7.3 Example for practice7.4 Viva Question

7.1 OBJECTIVE: learning of packages and triggers in SQL

7.2 THEORY AND CONCEPTS: 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:

33
  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:

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;

7.3 Example for practice:

Write a PL/SQL program to demonstrate Packages.

Step1: Creating package specification
CREATE or replace PACKAGE emppackage AS
procedure emp_proc2;
function fname2(a in number,b in out number)
return number;
end;

34
 PRACTICAL – 8

TITLE: Design and implementation of Payroll Processing System

8.1 Objective 8.2 Theory and concepts8.3 Exercise

8.1 OBJECTIVE:Design and implementation of payroll processing system

8.2 THEORY AND CONCEPTS:

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
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@xyz.corp', '1234', '1994-04-04', 'Male', '01919', 12221, 'Razarbagh', 'IT', 'Head',
'images/no.jpg'),
(122, 'Mohan', 'Kumar', 'mohan@gmail.com', '1234', '2018-01-01', 'Male', '0202', 323, 'Ad_______', 'CS', 'CS',
'images/no.jpg'),
(123, 'Ram', 'Singh', 'rams@xyz.corp', '1234', '1990-02-02', 'Male', '5252', 6222, 'Thames, UK', 'Creative', 'MSc',
'images/sw-google.png'),

37
(124, 'Govind', 'Iyer', 'govind@xyz.corp', '1234', '1971-12-01', 'Male', '9595', 5929, 'Chemsford, USA',
'Creative', 'MSc', 'images/test.jpg'),
(125, 'Shyam', 'Manja', 'elon@spacex.com', '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'),
(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);

38
COMMIT;

Questions:-
Q1. Print list of all the employees.
Ans1. SQL>SELECT * FROM empp;

Q2. Print name of all employees who lives in Razarbagh.

Ans2. SQL>SELECT CONCAT(first_name, ' ', last_name) as FullName from empp where addr='Razarbagh';

39
 PRACTICAL – 9

TITLE: Design and implementation of Library information system

9.1 Objective 9.2 Theory and concepts9.3 Exercise and solution

9.1 OBJECTIVE:Design and implementation of Library information system

9.2 THEORY AND CONCEPTS:

A Library Management System is a software built to handle the primary housekeeping functions of a 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),

40
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),
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;

Questions:-
Q1. Print all types of books available in Library.
Ans1. SQL>SELECT catgry_name from catgry;
Q2. Print the fullname of all the authors.
Ans2. SQL>SELECT CONCAT(first_name, ' ', last_name) as FullName FROM authr;

41
 PRACTICAL – 10

TITLE: Design and implementation of Student information system

10.1 Objective 10.2 Theory and concepts10.3 Exercise and solution

10.1 OBJECTIVE:Design and implementation of student information system

10.2 THEORY AND CONCEPTS:

A Student Information System, or SIS, is a web-based platform that helps schools and colleges take data online
for easier management and better clarity. That's at its most basic.
The SIS system is able to collect school-wide data online so that it can be easily accessed by teachers, parents,
students, and administrators. That includes records of tests, attendance, appraisal performance, and plenty more.
Essentially, a SIS allows the school to make data points for lots of areas in one place so that it's easy to keep
track of progress and performance.

Code:-
-- Database
-- Table structure for table dev
CREATE TABLE dev (
iddint(10) NOT NULL PRIMARY KEY AUTO_INCREMENT,
namevarchar(40) NOT NULL,
e_mailvarchar(40) NOT NULL,
passvarchar(40) NOT NULL
);
-- Dumping data for table dev
INSERT INTO dev (idd, name, e_mail, pass) VALUES
(1, 'Mohan Kumar', 'info.mohit@gmail.com', 'kumar'), (2, 'Rohan Singh', 'rohanfatima.it@gmail.com', 'kumar');
-- Table structure for table coursee
CREATE TABLE coursee (
coursee_idint(10) NOT NULL PRIMARY KEY AUTO_INCREMENT,
coursee_short_namevarchar(250) NOT NULL,
coursee_full_namevarchar(250) NOT NULL,
coursee_datevarchar(50) NOT NULL
);
INSERT INTO coursee (coursee_id, coursee_short_name, coursee_full_name, coursee_date) VALUES
(44, 'M.C.A', 'MASTER OF COMPUTER APPLICATION', '25-04-2019'), (45, 'M.S.C', 'MASTER OF
SCIENCE', '25-04-2019'), (46, 'B.COM', 'BACHELOR OF COMMERCE', '25-04-2019'), (48, 'M.B.A ',
'MASTER OF BUSINESS ADMINISTRATION', '25-04-2019');
-- Table structure for table folow
CREATE TABLE folow (
iddint(10) NOT NULL PRIMARY KEY AUTO_INCREMENT,
to_user_idint(20) NOT NULL,
from_user_idint(20) NOT NULL
);
-- Dumping data for table folow
INSERT INTO folow (idd, to_user_id, from_user_id) VALUES
(29, 23, 20), (33, 20, 32), (37, 0, 21), (38, 21, 21), (40, 21, 20), (45, 30, 20), (46, 40, 20), (47, 20, 20);
-- Table structure for table folowing
CREATE TABLE folowing (
iddint(10) NOT NULL PRIMARY KEY AUTO_INCREMENT,
from_user_idvarchar(20) NOT NULL,
to_user_idvarchar(20) NOT NULL
);
-- Table structure for table folows
CREATE TABLE folows (
iddint(10) NOT NULL PRIMARY KEY AUTO_INCREMENT,

42
to_user_idint(20) NOT NULL,
from_user_idint(20) NOT NULL
);
-- Table structure for table postss
CREATE TABLE postss (
post_idint(10) NOT NULL PRIMARY KEY AUTO_INCREMENT,
from_user_idvarchar(3) NOT NULL,content longtext NOT NULL,
imagevarchar(255) NOT NULL,
timestamptimestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
);
-- Dumping data for table postss
INSERT INTO postss (post_id, from_user_id, content, image, timestamp) VALUES
(10, '1', 'Priyankachopra', 'priyanka chopra.jpg', '2019-05-08 15:37:52'),
(70, '1', 'This website is developed by Social Codia.', 'socialcodia.png', '2020-04-27 21:15:56'),
(15, '1', 'The results of the best video ever try to get the way this can be its own incomplete website and I will be
its own incomplete website and I will be its own incomplete website and I will be its own incomplete website
and I will be its own incomplete websit', 'Screenshot_2019-05-05-20-08-26-
893_com.mxtech.videoplayer.ad.png', '2019-05-08 17:12:44'),
(17, '8', 'True Love &hearts;ï¸ ', '918369709214_status_eace0e54cee646309b0da5f908d6731f.jpg', '2019-05-08
19:14:13'),
(21, '8', 'Wow , Looking great Mohan... 😘😘😘', 'IMG_20190513_223507454.jpg', '2019-05-13
22:22:56'),
(22, '8', 'Ramzan Mubarak Mohan... 😘😘😘', 'B612_20190509_075024_942.jpg', '2019-05-13 22:24:01'),
(25, '20', 'Mohan Kumar The Boss', '6.jpg', '2019-05-19 05:55:07'),
(35, '20', 'Your genes decide whether you will own a dog or not, pawsible? Although dogs and other pets are
common household membrs across the globe, little is known how they impact our daily life and health.',
'dogsgenes.jpeg', '2019-05-20 00:28:40'),
(47, '21', 'demo post from student_id = 21', 'mohit.png', '2019-10-10 17:07:40'),
(48, '21', 'stack image', 'stock-vector-editable-square-frame-banner-template-for-social-media-post-red-black-
and-white-geometric-shape-1415216360.jpg', '2019-10-10 17:06:33'),
(59, '21', 'Rohan Singh', 'rohan - Copy.jpg', '2019-10-21 05:19:12');
-- Table structure for table stdnts
CREATE TABLE stdnts (
iddint(10) NOT NULL PRIMARY KEY AUTO_INCREMENT,
rollnovarchar(255) NOT NULL,
standerdvarchar(255) NOT NULL,
user_namevarchar(40) NOT NULL,
namevarchar(255) NOT NULL,
gndrvarchar(255) NOT NULL,
contctvarchar(255) NOT NULL,
e_mailvarchar(255) NOT NULL,
passvarchar(255) NOT NULL,
cityyvarchar(255) NOT NULL,
imagevarchar(255) NOT NULL
);
-- Dumping data for table stdnts
INSERT INTO stdnts (idd, rollno, standerd, user_name, name, gndr, contct, e_mail, pass, cityy, image)
VALUES
(20, '1', 'BCA', 'mohit', 'Mohan Kumar', 'male', '9867503256', 'info.mohit@gmail.com', 'kumar', 'Thane',
'mohit.png'), (21, '2', 'Botanology', 'rohan', 'Rohan Singh', 'female', '9867503256', 'rohanfatima.it@gmail.com',
'kumar', 'Sipah', 'rohan.jpg'), (22, '6', '3', 'sohan', 'Sohan Kumar', 'male', '82684000646',
'sohankumar82@gmail.com', 'kumar', 'Thane', '6.jpg'), (27, '10', '1', 'ibbu', 'Ram Kumar', 'male', '9920322293',
'info.iramkumar@gmail.com', 'kumar', 'Mumbra, Thane', '');
-- Table structure for table teacher
CREATE TABLE teacher (
iddint(10) NOT NULL PRIMARY KEY AUTO_INCREMENT,
namevarchar(40) NOT NULL,
e_mailvarchar(30) NOT NULL,
contctvarchar(13) NOT NULL,
gndrvarchar(7) NOT NULL,

43
positionvarchar(50) NOT NULL,
passvarchar(20) NOT NULL,
addrvarchar(255) NOT NULL,
imagevarchar(60) NOT NULL
);
-- Dumping data for table teacher
INSERT INTO teacher (idd, name, e_mail, contct, gndr, position, pass, addr, image) VALUES
(1, 'Mohan Kumar', 'info.mohan@gmail.com', '9555555256', 'male', 'Manager', 'kumar', 'kausa, mumbra, thane',
'mohan.png'),
(8, 'Rohan Kumar', 'rohan.it@gmail.com', '7156478954', 'female', 'Head Teacher', 'kumara', 'Sipah', 'rohan.jpg');
COMMIT;

Questions:-
Q1. Print all the information regarding courses.
Ans1. SQL>SELECT * FROM coursee;
Q2. Print all information of teachers.
Ans2. SQL>SELECT * FROM teacher;

44
 PRACTICAL – 11

TITLE: Automatic Backup of Files and Recovery of Files

11.1 Objective 11.2 Theory 11.3 Viva Question

11.1 OBJECTIVE: Automatic Backup of Files and Recovery of Files

11.2 THEORY AND CONCEPTS: Data is the heart of the enterprise, it's crucial to protect it and to
protect organization's data, one need to implement a data backup and recovery plan. Backing up file
scan protect against accidental loss of user data, database corruption, hardware failures, and even
natural disasters. It's our job as an administrator to make sure that backups are performed and that
backup tapes are stored in a secure location.

Creating a Backup and Recovery Plan

Data backup is an insurance plan. Important files are accidentally deleted all the time. Mission-critical
data can become corrupt. Natural disasters can leave office in ruin. With a solid backup and recovery
plan, one can recover from any of these.

Figuring Out a Backup Plan

It takes time to create and implement a backup and recovery plan. We'll need to figure out what data
needs to be backed up, how often the data should be backed up, and more. To help we create a plan,
consider the following:
• How important is the data on systems? The importance of data can go a long way in helping to
determine if one need to back it up—as well as when and how it should be backed up. For critical
data, such as a database, one'll want to have redundant backup sets that extend back for several
backup periods. For less important data, such as daily user files, we won't need such an elaborate
backup plan, but 'll need to back up the data regularly and ensure that the data can be recovered
easily.

• What type of information does the data contain? Data that doesn't seem important to we may be
very important to someone else. Thus, the type of information the data contains can help we
determine if we need to back up the data—as well as when and how the data should be backed up.

• How often does the data change? The frequency of change can affect our decision on how often
the data should be backed up. For example, data that changes daily should be backed up daily.

• How quickly do we need to recover the data? Time is an important factor in creating a backup
plan. For critical systems, we may need to get back online swiftly. To do this, we may need to
alter our backup plan.

• Do we have the equipment to perform backups? We must have backup hardware to perform
backups. To perform timely backups, we may need several backup devices and several sets of
backup media. Backup hardware includes tape drives, optical drives, and removable disk drives.
Generally, tape drives are less expensive but slower than other types of drives.

• Who will be responsible for the backup and recovery plan? Ideally, someone should be a
primary contact for the organization's backup and recovery plan. This person may also be
responsible for performing the actual backup and recovery of data.

45
 • What is the best time to schedule backups? Scheduling backups when system use is a slow as
possible will speed the backup process. However, we can't always schedule backups for off-peak
hours. So we'll need to carefully plan when key system data is backed up.

• Do we need to store backups off-site? Storing copies of backup tapes off-site is essential
to recovering our systems in the case of a natural disaster. In our off-site storage location, we
should also include copies of the software we may need to install tore-establish operational
systems.

The Basic Types of Backup

There are many techniques for backing up files. The techniques use will depend on the type of data
we're backing up, how convenient we want the recovery process to be, and more.

If we view the properties of a file or directory in Windows Explorer, we'll note an attribute called
Archive. This attribute often is used to determine whether a file or directory should be backed up. If
the attribute is on, the file or directory may need to be backed up. The basic types of backups we can
perform include

• Normal/full backups All files that have been selected are backed up, regardless of the setting of
the archive attribute. When a file is backed up, the archive attribute is cleared. If the file is later
modified, this attribute is set, which indicates that the file needs to be backed up.

• Copy backups All files that have been selected are backed up, regardless of the setting of the
archive attribute. Unlike a normal backup, the archive attribute on files isn't modified. This allows
us to perform other types of backups on the files at a later date.

• Differential backups Designed to create backup copies of files that have changed since the last
normal backup. The presence of the archive attribute indicates that the file has been modified and
only files with this attribute are backed up. However, the archive attribute on files isn't modified.
This allows to perform other types of backups on the files at a later date.

• Incremental backups Designed to create backups of files that have changed since the most recent
normal or incremental backup. The presence of the archive attribute indicates that the file has
been modified and only files with this attribute are backed up. When a file is backed up, the
archive attribute is cleared. If the file is later modified, this attribute is set, which indicates that the
file needs to be backed up.

• Daily backups Designed to back up files using the modification date on the file itself. If a file
has been modified on the same day as the backup, the file will be backed up. This technique
doesn't change the archive attributes of files. In we backup plan we'll probably want to perform
full backups on a weekly basis and supplement this with daily, differential, or incremental
backups. We may also want to create an extended backup set for monthly and quarterly backups
that includes additional files that aren't being backed up regularly.

Tip We'll often find that weeks or months can go by before anyone notices that a file or data source is
missing. This doesn't mean the file isn't important. Although some types of data aren't used often,
they're still needed. So don't forget that we may also want to create extra sets of backups for monthly
or quarterly periods, or both, to ensure that we can recover historical data over time.

Differential and Incremental Backups

The difference between differential and incremental backups is extremely important. To understand
the distinction between them. As it shows, with differential backups we back up all the files that have
changed since the last full backup (which means that the size of the differential backup grows over
time). With incremental backups, we only back up files that have changed since the most recent full or

46
 PRACTICAL – 12

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

12.1 Objective 12.2 Theory and concepts

12.1 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

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:

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 items in inventory

 Enter, modify, delete suppliers of inventory items

 View details of inventory items and all associated costs to aid in decision
making on placing orders with suppliers

 View details of suppliers of inventory items

 Generate reports on inventory re-order level to aid in decision making in

procurement of inventory items

49
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

Project deliverables

ER Diagrams,

Converting ER Diagrams into Tables

Making SQL Queries

50
12.1 Objective 12.2 Theory and concepts

12.1 OBJECTIVE:Material Requirements Processing (MRP) is a standard supply planning system to

help businesses, primarily product-based manufacturers, understand inventory requirements while
balancing supply and demand. Businesses use MRP systems, which are subsets of supply chain
management systems, to efficiently manage inventory, schedule production and deliver the right
product—on time and at optimal cost.

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:

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 Identifying requirements to meet demand

The first step of the MRP process is identifying customer demand and the
requirements needed to meet it, which starts with inputting customer orders and sales
forecasts.

o Using the bill of materials required for production, MRP then disassembles demand
into the individual components and raw materials needed to complete the build while
accounting for any required sub-assemblies.

o Checking inventory and allocating resources

Utilizing the MRP to check demand against inventory and allocating resources
accordingly, you can see both what items you have in stock and where they are—this
is especially important if you have inventory across several locations. This also lets
you see the status of items, which gives visibility into items that are already allocated
to another build, as well as items not yet physically in the warehouse that are in
transit, or on order. The MRP then moves inventory into the proper locations and
prompts reorder recommendations.

o Scheduling production
Using the master production schedule, the system determines how much time and
labor are required to complete each step of each build and when they need to happen
so that the production can occur without delay.

o The production schedule also identifies what machinery and workstations are needed
for each step and generates the appropriate work orders, purchase orders and transfer
orders. If the build requires subassemblies, the system takes into account how much
time each subassembly takes and schedules them accordingly.

51
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

Project deliverables

ER Diagrams,

Converting ER Diagrams into Tables

Making SQL Queries

60