TEACHER DATABASE MANAGEMENT SYSTEM

CHAPTER 1
INTRODUCTION
Databases and database technology have had a major impact on the growing use of
computers. It is fair to say that databases play a critical role in almost all areas where computers
are used, including business, electronic commerce, social media, engineering, medicine,
genetics, law, education, and library science.
A database is a collection of related data. By data, we mean known facts that can be
recorded and that have implicit meaning. A database management system(DBMS) is a
computerized system that enables users to create and maintain a database. The DBMS is a
general-purpose software system that facilitates the processes of defining, constructing,
manipulating, and sharing databases among various users and applications.

1.1 INTRODUCTION TO SQL

Structure Query Language (SQL) is a programming language used for storing and
managing data in Relational Database Management System (RDBMS). SQL was the first
commercial language introduced for E.F Codd's Relational model. Today almost all RDBMS
(MySQL, Oracle, Info mix, Sybase, MS Access) uses SQL as the standard database language.
SQL is used to perform all type of data operations in RDBMS.Most of the actions you need to
perform on a database are done with SQL statements.SQL defines following data languages to
manipulate data of RDBMS:

1. DDL: Data Definition Language

All DDL commands are auto-committed. That means it saves all the changes
permanently in the database.

E.g.: create-To create new table or database, alter-For alteration, truncate-Delete data from table,
drop-To drop a table

2.DML: Data Manipulation Language

DML commands are not auto-committed. It means changes are not permanent to
database, they can be rolled back.

Dept of CSE, GAT Page 1

TEACHER DATABASE MANAGEMENT SYSTEM

E.g. Insert-To insert a new row, update-To update existing row, delete-To delete a row, merge-
merging two rows or two tables

3.TCL: Transaction Control Language

These commands are to keep a check on other commands and their effect on the database.
These commands can annul changes made by other commands by rolling back to original state. It
can also make changes permanent.

E.g.: commit-to permanently save, rollback-to undo change, save point-to save temporarily

4.DCL: Data Control Language

Data control language provides command to grant and take back authority.

E.g.: grant-grant permission of right, revoke-take back permission

5.DQL-Data Query Language

DQL is used to operate on queries.

E.g.: Select-retrieve records from one or more table

1.2 INTRODUCTION TO FRONT END SOFTWARE

Swing is a GUI widget toolkit for Java. It is part of Oracle's Java Foundation
Classes (JFC) – an API for providing a graphical user interface (GUI) for Java programs.

Swing was developed to provide a more sophisticated set of GUI components than the
earlier Abstract Window Toolkit (AWT). Swing provides a native look and feel that emulates
the look and feel of several platforms, and also supports a pluggable look and feel that allows
applications to have a look and feel unrelated to the underlying platform. It has more powerful
and flexible components than AWT. In addition to familiar components such as buttons, check
boxes and labels, Swing provides several advanced components such as tabbed panel, scroll
panes, trees, tables, and lists.

Dept of CSE, GAT Page 2

TEACHER DATABASE MANAGEMENT SYSTEM

Unlike AWT components, Swing components are not implemented by platform-specific

code. Instead, they are written entirely in Java and therefore are platform-independent. The term
"lightweight" is used to describe such an element.

Though Swing is intended to be replaced by JavaFX, it will remain part of the Java SE
specification for the foreseeable future.

Swing's heavy reliance on runtime mechanisms and indirect composition patterns allows
it to respond at run time to fundamental changes in its settings. For example, a Swing-based
application is capable of hot swapping its user-interface during runtime. Furthermore, users can
provide their own look and feel implementation, which allows for uniform changes in the look
and feel of existing Swing applications without any programmatic change to the application
code.

 JEditorPanes and JPasswordFields - for displaying web pages and inputting confidential
information

 Dialogs - for displaying warnings, errors, prompting for input

 Check Boxes - for selecting a small set of options

 Radio Buttons - for mutually exclusive selection of options

 Combo Boxes - for selecting a small set of qualified options

 JLists - for selecting from a large set of qualified options

 JTables - for table displays

 JMenuBar, JMenu, and JMenuItems - for creating menus

 File Chooser - for navigating through files and directories, and selecting files and
directories

 Tabbed Panels - for allowing different panels to occupy the same screen area

Dept of CSE, GAT Page 3

TEACHER DATABASE MANAGEMENT SYSTEM

1.3 PROJECT REPORT OUTLINE

The report is arranged in the following way:

Chapter1: Introduction
Chapter 2: Requirement Specification
Chapter 3: Objective of the Project
Chapter 4: Implementation
Chapter 5: Front end design
Chapter 6: Testing
Chapter 7: Results

Dept of CSE, GAT Page 4

TEACHER DATABASE MANAGEMENT SYSTEM

CHAPTER 2
REQUIREMENT SPECIFICATION

A high-level requirements specification is required. The purpose of the

requirements analysis is to identify requirements for the proposed system. The emphasis
is on the discovery of user requirements.

2.1 SOFTWARE REQUIREMENTS

Operating System: Windows 10
Database: MYSQL
Tools: Eclipse, MySQL, swings

2.2 HARDWARE REQUIREMENTS

Processor: Any Processor above 500 MHz
RAM:8GB
Hard Disk:1TB HDD
Compact Disk:700MB
Input device: Keyboard, mouse
Output device: Monitor

Dept of CSE, GAT Page 5

TEACHER DATABASE MANAGEMENT SYSTEM

CHAPTER 3
OBJECTIVE OF THE PROJECT
The first objective of this system is to maintain the data of all the student and teacher in
database. It maintains the personal record as well as the academic record of the student. It
maintains teacher service record. Teacher personal record and salary record are also store in the
software. Reports are the most important outputs of Teacher management system (TMS).

TMS create the following reports.

Teacher joining reports

Teacher Joining Letter

Teacher Relieving reports

Counseling report

Detail Marks Certificate

Attendance report of student

The teacher management system facilitates the admin in following respects.

 Data Entry

Entering the correct and reliable data to the proposed system is the most important factor.
The proposed system will facilitate the admin with user-friendly screen, which will help the
admin to enter the correct data to proposed system easily. Different checks have been applied in
the program for the validity of data so that wrong information cannot be entered into the system
easily.

 Updating

Mistake in the entry process are possible to occur. These mistakes needed to be corrected at
the right time so that the data remain correct. Any mistake during the entry process can be
corrected through this option.

Dept of CSE, GAT Page 6

TEACHER DATABASE MANAGEMENT SYSTEM

 Deletion

We can easily delete a particular record by requesting through entering the identity number.
The specified record is deleted from the database, which is not required anymore. This rarely
occurs because most of the record are stored permanently, which may be referenced in later time.

 Data Retrieval

The main purpose of data retrieval is to describe and explain ways and methods by means of
which we get the required information from the system. The most imported operation in a system
is the utilization or retrieval of the stored data. Different programs have been developed for this
purpose. In these programs different queries are developed. These queries provide different
facilities to the admin.

Software quality attributes:

These are several quality attributes in our proposed system

 Correctness

 Efficiency

 Flexibility

 Security

 Maintainability

 Reliability

Dept of CSE, GAT Page 7

TEACHER DATABASE MANAGEMENT SYSTEM

CHAPTER 4
IMPLEMENTATION
4.1 ER DIAGRAM: An entity relationship diagram (ERD) shows the relationships of
entity sets stored in a database. An entity in this context is a component of data. ER diagrams
illustrate the logical structure of databases.

4.2 MAPPING OF THE ER DIAGRAM TO SCHEMA

A database schema is the skeleton structure that represents the logical view of the entire
database. A database schema defines its entities and the relationship among them. It contains a
descriptive detail of the database, which can be depicted by means of schema diagrams.

Dept of CSE, GAT Page 8

TEACHER DATABASE MANAGEMENT SYSTEM

4.2 MAPPING OF THE ER DIAGRAM TO SCHEMA

A database schema is the skeleton structure that represents the logical view of the entire
database. A database schema defines its entities and the relationship among them. It contains a
descriptive detail of the database, which can be depicted by means of schema diagrams.

TEACHER

TID TName Age Degree Phone Email Address Salary

STUDENT

USN Name TID SCode Marks Attendance Age Sex PID Result

SUBJECT

Sub_Code USN TID Sub_Name Experience

PARENT

PID TID PName Phone Email

Dept of CSE, GAT Page 9

TEACHER DATABASE MANAGEMENT SYSTEM

4.3 MAPPING OF THE ER SCHEMA TO RELATIONS

1. Mapping of Binary 1:1 Relationship Types

For each binary 1:1 relationship type R in the ER schema, identify the relations S and T
that correspond to the entity types participating in R. There are three possible approaches:

 The foreign key approaches

 The merged relationship approaches
 The cross reference or relationship relation approach

The first approach is the most useful and should be followed unless special conditions
exist.

Foreign key approach: Choose one of the relations-S, and include as a foreign key in S
the primary key of T. It is better to choose an entity type with total participation in R in the role
of S. Include all the simple attributes of the 1:1 relationship type R as attributes of S.

Here 1:1 relationship type HAS is mapped by choosing the primary key of Ticket
Booking relation and included as foreign key in Payment relation.

2. Mapping of Binary 1:M Relationship Types

There are two possible approaches:

 The foreign key approaches

 The cross-reference or relationship relation approach

The first approach is generally preferred as it reduces the number of tables.

The foreign key approach: For each regular binary 1: N relationship type R,
identify the relation S that represents the participating entity type at the N-side of the
relationship type. Include as foreign key in S the primary key of the relation T that
represents the other entity type participating in R. Include any simple attributes of the 1:
N relationship type as attributes of S.

In this database viewed By, books, cancel, give are 1:M relationship type.

Dept of CSE, GAT Page 10

TEACHER DATABASE MANAGEMENT SYSTEM

TEACHER

TID TName Age Degree Phone Email Address Salary

STUDENT

USN Name TID SCode Marks Attendance Age Sex PID Result

3. Mapping of weak entity types

For each weak entity type W in the ER schema with owner entity type E, create a relation
R and include all simple attributes of W as attributes of R. IN addition, include as foreign key
attributes of R, the primary key attributes of the relation that correspond to the owner entity
type.

TEACHER

TID TName Age Degree Phone Email Address Salary

PARENT

PID TID PName Phone Email

Dept of CSE, GAT Page 11

TEACHER DATABASE MANAGEMENT SYSTEM

4.4 CREATION OF TABLES

CREATE DATABSE TEACHERDBMS;

USE DATABASE TEACHER_DATABASE;

TEACHER TABLE

CREATE TABLE TEACHER (

TID VARCHAR (10), TNAME VARCHAR (50),

AGE VARCHAR (10), DEGREE VARCHAR (50),

PHNO VARCHAR (20),

MAIL_ID VARCHAR (30),

ADDRESS VARCHAR (50),

PRIMARY KEY(TID),

SALARY VARCHAR (20));

PARENT TABLE

CREATE TABLE PARENT (

PID VARCHAR (10), TID VARCHAR (10),

PNAME VARCHAR (50),

PHNO VARCHAR (50),

MAIL_ID VARCHAR (50),

PRIMARY KEY (PID, TID),

FOREIGN KEY(TID) REFERENCES TEACHER(TID));

Dept of CSE, GAT Page 12

TEACHER DATABASE MANAGEMENT SYSTEM

STUDENT TABLE

CREATE TABLE STUDENT (

USN VARCHAR (30),

SNAME VARCHAR (50),

TID VARCHAR (30),

SUB_CODE VARCHAR (30),

MARKS VARCHAR (10),

ATTENDENCE VARCHAR (10),

AGE VARCHAR (10),

SEX VARCHAR (10),

PID VARCHAR (10),

RESULT VARCHAR (10),

PRIMARY KEY (USN, TID),

FOREIGN KEY(TID) REFERENCES TEACHER(TID));

SUBJECT TABLE

CREATE TABLE SUBJECT (

SUB_CODE VARCHAR (10), TID VARCHAR (10),

SUB_NAME VARCHAR (50), USN VARCHAR (30),

EXPERIENCE VARCHAR (10),

PRIMARY KEY (TID, SUB_CODE),

FOREIGN KEY(USN) REFERENCES STUDENT(USN));

FOREIGN KEY(TID) REFERENCES TEACHER(TID));

Dept of CSE, GAT Page 13

TEACHER DATABASE MANAGEMENT SYSTEM

4.5 INSERTION OF TUPLES

1) TEACHER
 INSERT INTO TEACHER
VALUES("TD125","ASHITHA","28","PHD","9449001278","ASHITHA125@GMAIL.
COM","BANGALORE","35456");
 INSERT INTO TEACHER
VALUES("TD250","RAKSHITHA","36","M.SC","9449801678","RAKSHITHA128@G
MAIL.COM","MANGALORE","45678");
 INSERT INTO TEACHER
VALUES("TD375","ANUPAMA","29","M.SC","9999001278","ANUPAMA789@GM
AIL.COM","BANGALORE","34567");

2)PARENT

 INSERT INTO PARENT VALUES("PATID55","TD125","RASHIKA",

"8124569836","[email protected]");
 INSERT INTO PARENT VALUES("PATID60","TD250","GURURAJ",
"8127531536","[email protected]");
 INSERT INTO PARENT VALUES("PATID65","TD375","SAHANA","9878969836",
"[email protected]");
 INSERT INTO PARENT VALUES("PATID70","TD500","TRISHIKHA",
"9689569836","[email protected]");

3)STUDENT

 INSERT INTO STUDENT VALUES("KV15033","SANJANA","TD125","KV032",

"98%","79.85%","15","FEMALE","PATID236","");
 INSERT INTO STUDENT VALUES("KV14029","SANGEETHA","TD128","KV033",
"91%","89.85%","12","FEMALE","PATID246","");
 INSERT INTO STUDENT VALUES("KV13026","SUSHMA","TD251","KV035",
"88%","99.05%","16","FEMALE","PATID369","");
 INSERT INTO STUDENT VALUES("KV15015","UMA","TD251","KV031",
"80%","63.45%","13","FEMALE","PATID584","");

Dept of CSE, GAT Page 14

TEACHER DATABASE MANAGEMENT SYSTEM

4)SUBJECT

 INSERT INTO SUBJECT VALUES("KV031","KV15015","TD251","SCIENCE","5");

 INSERT INTO SUBJECT VALUES("KV032","KV13023","TD315","SOCIAL
SCIENCE","2");
 INSERT INTO SUBJECT VALUES("KV033","KV14029","TD128","MATHS","15");
 INSERT INTO SUBJECT VALUES("KV034","KV15001","TD366","ENGLISH","6");

4.6 CREATION OF TRIGGERS

CREATE TRIGGER DEMO BEFORE INSERT ON STUDENT FOR EACH ROW
BEGIN
IF NEW.MARKS>=85 THEN
SET NEW.RESULT='FCD';
ELSEIF NEW.MARKS>=60 AND NEW.MARKS<85 THEN
SET NEW.RESULT='FC';
ELSEIF NEW.MARKS>35 AND NEW.MARKS<60 THEN
SET NEW.RESULT='PASS';
ELSEIF NEW.MARKS<35 THEN
SET NEW.RESULT='FAIL';
END IF; END;
$$;
4.7 CREATION OF STORED PROCEDURES
DELIMITER $$
CREATE PROCEDURE GET3(IN ID VARCHAR (30))
BEGIN
SELECT T.TID, T. TNAME, T. MAIL_ID, S. SERVICE_ID, S.DOJ, S.DOR, S. PLACE, S.
DESIGNATION FROM TEACHER T, SERVICE_DETAIL S
WHERE T.TID=S.TID AND T.TID=ID;
END; $$

Dept of CSE, GAT Page 15