Contents
Chapter-1........................................................................................................................1
Introduction....................................................................................................................1
1.1 Introduction..........................................................................................................1
1.2 Features................................................................................................................1
Chapter-2........................................................................................................................2
ER-Design......................................................................................................................2
2.1 Entity Relationship Diagram................................................................................2
2.2 List of the Entity and Attributes...........................................................................3
Chapter-3........................................................................................................................4
Relational Database Design...........................................................................................4
3.1 Convert ER-Diagram to RDBMS........................................................................4
Chapter-4........................................................................................................................5
Execute MYSQL............................................................................................................5
4.1 Execute Create Statement....................................................................................5
4.2 Execute Insert and Select Statement....................................................................6
4.3 Execute Pattern matching (like Clause/ Wild Card)..........................................15
4.4 Aggregate Function:..........................................................................................18
4.5 Group by Clause:................................................................................................20
4.7 MYSQL Operator...............................................................................................24
4.8 MYSQL ALIAS:................................................................................................27
CHAPTER 5.................................................................................................................31
Conclusion....................................................................................................................31
i
Chapter-1
Introduction
1.1 Introduction
This report is prepared to computerize the manual process of Employ Management
System using MYSQL server. Employee management system is a platform where all
work-related as well as important detail of an employee is stored and managed in a
secure way. This system will help us to keep track of document and other data. It will
link each employee to document they are responsible for and reduce inefficiencies
over data handling. This project includes 3 (entities) namely student, university &
course.
1.2 Features
The project includes following features;
Create table
Insert value
Select/retrieve record
Update/modify
Alter
Delete
Rename
Truncate
1
Chapter-2
ER-Design
2.1 Entity Relationship Diagram
An entity is a “thing or object” in the real world. It is represented as rectangle in ER-
Diagram. The overall logical structure (schema) of a database can be expressed
graphically by an ER-Diagram. Here we have created an ER-Diagram of student
management system as.
Ex. Student, university & course
Attributes; attribute is used to describe the property of an entity
Ex. Attribute of student are Sid, Sname, and level
Relationship; it’s a association among several entities
Ex. Enrolls in
2
2.2 List of the Entity and Attributes
Entity are; Student, university and course
Attributes; Sid, Sname, level, location, Uname, ESTD, Credit, Cname and Cid
Relationship; Enrolls in
3
Chapter-3
Relational Database Design
3.1 Convert ER-Diagram to RDBMS
Student Table (Sid, Sname, level)
Sid Sname Level
University (Location, Uname, ESTD)
Location Uname ESTD
Course (Credit, Cname, Cid)
Cid Credit Cname
Enrolls in (Cid, Sid)
Cid Sid
4
Chapter-4
Execute MYSQL
4.1 Execute Create Statement
Create database student;
Use student;
Create table student
(
Sid int not null primary key (Sid),
Sname varchar (34),
Level varchar (20)
);
Create table university
(
Location varchar (30),
Uname varchar (30),
ESTD int
);
Create table course
(
Cid int not null primary key (Cid),
Credit text,
Cname varchar (10)
);
Create table Enrolls in
(
Sid int,
5
Cid int,
Foreign key (Sid) references student (Sid)
On delete cascade on update cascade,
Foreign key (Cid) references course (Cid)
On delete cascade on update cascade
);
4.2 Execute Insert and Select Statement
Insert record into student table
Insert into student (Sid, Sname, Level) value (1,’Kamal’,’ Bachelor’);
Insert into student (Sid, Sname, Level) value (2,’Samir’,’ +2’);
Insert into student (Sid, Sname, Level) value (3,’Rocky’,’ Master’);
Insert into student (Sid, Sname, Level) value (4,’Ekraj’,’BBA’);
Insert into student (Sid, Sname, Level) value (5,’Rohit’, ‘MBA’);
Insert into student (Sid, Sname, Level) value (6,’Shahid’,’ BBS’);
Insert into student (Sid, Sname, Level) value (7,’Arun’,’ BCA’);
Insert into student (Sid, Sname, Level) value (8,’Robbin’,’ BIT’);
Insert into student (Sid, Sname, Level) value (9,’Keshav’,’ BSc CSIT’);
Insert into student (Sid, Sname, Level) value (10,’Umesh’, ‘BCIS’);
Insert into student (Sid, Sname, Level) value (11,’Aryan’, ‘BBM’);
Insert into student (Sid, Sname, Level) value (12,’Sunil’,’ BBM’);
Insert into student (Sid, Sname, Level) value (13,’Dependra’, ‘Bachelor’);
Insert into student (Sid, Sname, Level) value (14,’Dinesh’, ‘BA’);
Insert into student (Sid, Sname, Level) value (15,’Amit’, ‘Arts’);
Insert into student (Sid, Sname, Level) value (16,’Anil’, ‘CA’);
Insert into student (Sid, Sname, Level) value (17,’Ajay’, ‘B.COM’);
Insert into student (Sid, Sname, Level) value (18,’Kamal’, ‘B.TECH’);
Insert into student (Sid, Sname, Level) value (19,’Kamal’, ‘Agriculture’);
Insert into student (Sid, Sname, Level) value (20,’Keshav’, ‘Bachelor’);
6
Select * from student;
7
Insert record into Course
Insert into course (cid, cname, credit) value (21, 'Math', '43hrs');
Insert into course (cid, cname, credit) value (22, 'science', '32hrs');
Insert into course (cid, cname, credit) value (23, 'English', '20hrs');
Insert into course (cid, cname, credit) value (24, 'Social', '34hrs');
Insert into course (cid, cname, credit) value (25, 'Account', '32hrs');
Insert into course (cid, cname, credit) value (26, 'Economics', '13hrs');
Insert into course (cid, cname, credit) value (27, 'HRM', '43hrs');
Insert into course (cid, cname, credit) value (29, 'Nepali', '23hrs');
Insert into course (cid, cname, credit) value (28, 'Computer', '23hrs');
Insert into course (cid, cname, credit) value (30, 'population', '35hrs');
Insert into course (cid, cname, credit) value (31, 'Occupation', '33hrs');
Insert into course (cid, cname, credit) value (32, 'Health', '23hrs');
Insert into course (cid, cname, credit) value (33, 'GK', '32hrs');
Insert into course (cid, cname, credit) value (34, 'EPH', '34hrs');
Insert into course (cid, cname, credit) value (35, 'Math', '32hrs');
Insert into course (cid, cname, credit) value (36, 'Math', '23hrs');
Insert into course (cid, cname, credit) value (37, 'Math', '43hrs');
Insert into course (cid, cname, credit) value (39, 'Math', '23hrs');
Insert into course (cid, cname, credit) value (40, 'Math', '43hrs');
Insert into course (cid, cname, credit) value (41, 'Math', '23hrs');
Insert into course (cid, cname, credit) value (42, 'Math', '33hrs');
8
Select* From Course;
9
Insert record into Enrolls_in
Insert into Enrolls_in (sid, cid) value (1,21);
Insert into Enrolls_in (sid, cid) value (2,22);
Insert into Enrolls_in (sid, cid) value (3,23);
Insert into Enrolls_in (sid, cid) value (4,24);
Insert into Enrolls_in (sid, cid) value (5,25);
Insert into Enrolls_in (sid, cid) value (6,26);
Insert into Enrolls_in (sid, cid) value (7,27);
Insert into Enrolls_in (sid, cid) value (8,28);
Insert into Enrolls_in (sid, cid) value (9,29);
Insert into Enrolls_in (sid, cid) value (10,30);
Insert into Enrolls_in (sid, cid) value (11,31);
Insert into Enrolls_in (sid, cid) value (12,32);
Insert into Enrolls_in (sid, cid) value (13,33);
Insert into Enrolls_in (sid, cid) value (14,34);
Insert into Enrolls_in (sid, cid) value (15,35);
Insert into Enrolls_in (sid, cid) value (16,36);
Insert into Enrolls_in (sid, cid) value (17,37);
Insert into Enrolls_in (sid, cid) value (18,39);
Insert into Enrolls_in (sid, cid) value (19,40);
Insert into Enrolls_in (sid, cid) value (20,41);
10
Select*From Enrolls_in;
11
Insert record into University
Insert into University (Uname, Location, ESTD) value ('Everest',
'Thapathali', 2000);
Insert into University (Uname, Location, ESTD) value ('Everest',
'Thapathali', 2000);
Insert into University (Uname, Location, ESTD) value ('Everest',
'Thapathali', 2000);
Insert into University (Uname, Location, ESTD) value ('Everest',
'Thapathali', 2000);
Insert into University (Uname, Location, ESTD) value ('Everest',
'Thapathali', 2000);
Insert into University (Uname, Location, ESTD) value ('Everest',
'Thapathali', 2000);
Insert into University (Uname, Location, ESTD) value ('Everest',
'Thapathali', 2000);
Insert into University (Uname, Location, ESTD) value ('Everest',
'Thapathali', 2000);
Insert into University (Uname, Location, ESTD) value ('Everest',
'Thapathali', 2000);
Insert into University (Uname, Location, ESTD) value ('Everest',
'Thapathali', 2000);
Insert into University (Uname, Location, ESTD) value ('Everest',
'Thapathali', 2000);
Insert into University (Uname, Location, ESTD) value ('Everest',
'Thapathali', 2000);
Insert into University (Uname, Location, ESTD) value ('Everest',
'Thapathali', 2000);
Insert into University (Uname, Location, ESTD) value ('Everest',
'Thapathali', 2000);
Insert into University (Uname, Location, ESTD) value ('Everest',
'Thapathali', 2000);
12
Insert into University (Uname, Location, ESTD) value ('Everest',
'Thapathali', 2000);
Insert into University (Uname, Location, ESTD) value ('Everest',
'Thapathali', 2000);
Insert into University (Uname, Location, ESTD) value ('Everest',
'Thapathali', 2000);
Insert into University (Uname, Location, ESTD) value ('Everest',
'Thapathali', 2000);
Insert into University (Uname, Location, ESTD) value ('Everest',
'Thapathali', 2000);
13
Select* From University;
14
4.3 Execute Pattern matching (like Clause/ Wild Card)
Select* From Student where Sname like ‘E%’;
Select* From Student where Sname like ‘R%’;
Select* From Students where Sname like ‘A%’;
15
Select* From Student where Sname like ‘T%T’;
Select* From Course where Cname like ‘M%’;
16
Select* From Course where cname like ‘s%’;
Select* From Course where Cname like ‘%c%’;
17
4.4 Aggregate Function:
Aggregate function is used to calculated a single value from a group of
values
Some aggregate function are Min, Max, Sum, Avg, Count and So on.
Syntax: Select aggregate function(attribute) From <tablename>;
Select count (sid) from student;
Select max(credit) from course;
18
Select min(credit) From Course;
Select Count(cid) From Course;
19
4.5 Group by Clause:
Its often used with aggregate function to group the result sets by one or
more column /attribute. In other word it is used for organizing similar
data into groups.
Syntax:
Select Column-name(s)/*,<aggregate function>from <tablename> where
condition group by <column-name>;
Select cname, count(cid) from course group by cname;
20
Select sname, count(sid) from student group by sname;
21
4.6 Update Statement:
Update student set level=’BCA’ where sid=’3’;
22
Update course set cname='Finance' where cid='22';
23
4.7 MYSQL Operator
Arthmetic Operator: +,-,*,/,%.
Comparison Operator:<, >, <=, >=,!=.
Logical Operator: AND, OR, NOT, Between.
Select sname, levels from student where levels='BBA';
Select cname, credit from course where credit='34hrs';
Select sname, levels from student where levels='Bachelor';
24
Select cname, credit from course where credit='35hrs';
Select cname, credit from course where credit='23hrs';
25
Select cname, credit from course where credit<='23hrs';
Select cname, credit from course where credit>='34hrs';
26
4.8 MYSQL ALIAS:
MYSQL Alisa are used to give table or attribute of temporary
name.
They are often used to make attribute name or table name more
readable. It only exist for duration of particular time.
Syntax:
Select<attributesname> as <aliasnameL> from <tablename>;
Select levels as Grade from student;
27
Select* from student as S;
28
Select cname as Cn from course;
29
Select sname as Sn from student;
30
CHAPTER 5
Conclusion
In Conclusion this report contains the ER-Model as well as
Relational data model of Student Management system. This report
also contain DDL Statement and DML Statement.
Preparation of this report has made it easier to access various data
and understand the working of MYSQL Command and various
syntax. This report has taught me to understand what Student
Management system do and how it help the organization to track,
store and manage the record related to student. This report has also
taught me the require process to keep the backup of Student
Management System in Database.
31
