Micro Project Report on

COURSE MANAGEMENT SYSTEM

Academic Year: 2020-21 Institute Code: 1643

Program: Computer Engineering [Second Shift] Semester: THIRD

Course: DATABASE MANAGEMENT SYSTEM Course Code: 22319

Group No: 10

Maharashtra State Board of Technical Education, Mumbai

 (Autonomous)(ISO-9001-2008) (ISO/IEC 27001:20013)

Maharashtra State Board of Technical Education, Mumbai

CERTIFICATE

This is to certify that,

Roll No Student Name Seat No Enrollment No

28 MOHIT RAJESH KADAM 1916430046

29 PRACHI PRADEEP PARAB 1916430047

30 SAHIL NELSON GABRU 1916430048

Of Third Semester of Diploma in Computer Engineering (Second Shift) of

Institute Pravin Patil College of Diploma Engineering and Technology,
Bhayander(E)(Code: 1643) has completed the Micro Project satisfactorily in
subject DATABASE MANAGEMENT SYSTEM (22319) for the academic
year 2020–2021 as prescribed in the curriculum.

Place: Bhayander (E)

Date: 10/10/2020
 PART A

Micro Project Proposal

COURSE MANAGEMENT SYSTEM

Brief Introduction:
A course management system (CMS) is a collection of software tools providing an online environment
for course interactions. A CMS typically includes a variety of online tools and environments, such as:

 An area for faculty posting of class materials such as course syllabus and handouts
 An area for student posting of papers and other assignments
 A gradebook where faculty can record grades and each student can view his or her grades
 An integrated email tool allowing participants to send announcement email messages to the entire
class or to a subset of the entire class
 A chat tool allowing synchronous communication among class participants
 A threaded discussion board allowing asynchronous communication among participants

In addition, a CMS is typically integrated with other databases in the university so that students enrolled in
a particular course are automatically registered in the CMS as participants in that course.

The decision to use a CMS in a traditional face-to-face course has implications for course design that often
go unnoticed by instructors in their initial use of such systems. This module lists technical and pedagogical
tips that instructors should consider as they place materials into a CMS. While it is intended primarily for
instructors who are using a CMS for the first time, instructors who have already used a CMS in other
courses might benefit by using these tips as a checklist.

Aim of the Micro-Project

To learn and understand the concept of Database Management System.

Action Plan
Sr. Planned Planned Name of Responsible
Details of Activity
No Start Date Finish Date Team Members

Understand the
1 17-08-2020 22-08-2020 ALL
work
SAHIL & MOHIT
2 Information provide 24-08-2020 29-08-2020

Analyzing the PRACHI

3 31-08-2020 05-09-2020
activity

4 Project schedule 07-09-2020 12-09-2020 ALL

5 Confusion Cleared 14-09-2020 19-09-2020 ALL

6 PPT making 21-09-2020 26-09-2020 PRACHI

7 Report making 28-09-2020 03-10-2020 PRACHI

Report Print out and PRACHI AND MOHIT

8 05-10-2020 10-10-2020
Mailing

Resources Required

Sr. No Name of Resource/Material Specifications Qty Remarks

1 MS Word MS Office 2019 1 For report purpose

2 SQL W3Schools 1 Executing Queries

 PART B

Micro Project Proposal

COURSE MANAGEMENT SYSTEM

Brief Description:
A course management system (CMS) is a collection of software tools providing an online environment
for course interactions. A CMS typically includes a variety of online tools and environments, such as:

 An area for faculty posting of class materials such as course syllabus and handouts
 An area for student posting of papers and other assignments
 A gradebook where faculty can record grades and each student can view his or her grades
 An integrated email tool allowing participants to send announcement email messages to the entire
class or to a subset of the entire class
 A chat tool allowing synchronous communication among class participants
 A threaded discussion board allowing asynchronous communication among participants

In addition, a CMS is typically integrated with other databases in the university so that students enrolled in
a particular course are automatically registered in the CMS as participants in that course.

The decision to use a CMS in a traditional face-to-face course has implications for course design that often
go unnoticed by instructors in their initial use of such systems. This module lists technical and pedagogical
tips that instructors should consider as they place materials into a CMS. While it is intended primarily for
instructors who are using a CMS for the first time, instructors who have already used a CMS in other
courses might benefit by using these tips as a checklist.

Aim of the Micro-Project

To learn and understand the concept of Database Management System
 Course Outcomes Integrated

1. An area for faculty posting of class materials such as course syllabus and
handouts
An area for student posting of papers and other assignments
2. A gradebook where faculty can record grades and each student can view his or her
grades
3. An integrated email tool allowing participants to send announcement email
messages to the entire class or to a subset of the entire class
4. A chat tool allowing synchronous communication among class participants
5. A threaded discussion board allowing asynchronous communication among
participants.

Actual Procedure Followed

Here, we work with our project team members who have already been identified to
establish and start the project:
 Define roles and responsibilities
 Analyze project quality and risks.
 Create the Project Schedule
 Identify and recruit project members.
E-R Diagram
CREATE TABLE
CREATE TABLE STUDENT
{
STUDENT_ID INT PRIMARY KEY,
STUDENT_NAME VARCHAR(30),
DOB VARCHAR(30),
DOOR# VARCHAR(30),
STRET VARCHAR(30),
CITY VARCHAR(30),
STATE VARCHAR(30),
PIN INT,
COURSE_ID VARCHAR(30),
}

INSERT INTO STUDENT

VALUES(1,'PRACHI','12/05/2003','12/67','LOMAN','DAHISAR','MAHARASHTRA',401304,'C01',)
INSERT INTO STUDENT VALUES(2,'MOHIT','01/04/2003','34/78','SHIV
VALLABH','VASAI','MAHARASHTRA',403456,'C02',)
INSERT INTO STUDENT
VALUES(3,'SAHIL','08/04/2003','13/45','KURUD','VIRAR','MAHARASHTRA',456789,'C03',)
INSERT INTO STUDENT
VALUES(4,'PARAG','18/09/2003','78/65','MATHER','DADAR','MAHARASHTRA',432156,'C04',)
INSERT INTO STUDENT
VALUES(5,'SOHAM','30/06/2003','90/67','MASHJIT','VASAI','MAHARASHTRA',476534,'C05',)

----------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------

CREATE TABLE LECTURER

{
LECTURER_ID VARCHAR(30);
LECTURER_NAME VARCHAR(30);
COURSE_ID VARCHAR(30);
}

INSERT INTO LECTURER VALUES('L01','ROHAN','C01');

INSERT INTO LECTURER VALUES('L02','SHILPA','C02');
INSERT INTO LECTURER VALUES('L03','TUKARAM','C03');

----------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------

CREATE TABLE SUBJECT

{
SUBJECT_ID VARCHAR(30);
SUBJECT_NAME VARCHAR(30);
LECTURER_ID VARCHAR(30)
}

INSERT INTO SUBJECT VALUES('S01','MARATHI','L01');

INSERT INTO SUBJECT VALUES('S02','MATHS','L02');
INSERT INTO SUBJECT VALUES('S03','HINDI','L03');
----------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------

CREATE TABLE COURSE

{
COURSE_ID INSERT INTO SUBJECT VALUES('!','@');;
COURSE_NAME VARCHAR(30);
}
INSERT INTO SUBJECT VALUES('C01','COMPUTER');
INSERT INTO SUBJECT VALUES('C02','IT');
INSERT INTO SUBJECT VALUES('C03','ELECTRONICS');

----------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------

CREATE TABLE STUDY_HOBBY

{
STUDENT_ID VARCHAR(30);
HOBBY VARCHAR(30);
}

INSERT INTO SUBJECT VALUES('S01','PAINTING');

INSERT INTO SUBJECT VALUES('S02','DRAWING');
INSERT INTO SUBJECT VALUES('S03','DANCING');

Write Procedures,trigger,function:

PROCEDURES

A procedure (often called a stored procedure) is a subroutine like a

subprogram in a regular computing language, stored in database. There are
many useful applications of SQL procedures within a database or database
application architecture. SQL procedures can be used to create simple scripts
for quickly querying transforming, updating data, generating basic reports,
improve application performance, modularizing applications, and improve
overall database design, and database security.
Features of SQL procedures
 Can contain SQL Procedural Language statements and features which support the
implementation of control-flow logic around traditional static and dynamic SQL
statements.
 Easy to implement, because they use a simple high-level, strongly-typed language.
 SQL procedures are more reliable than equivalent external procedures.
 Support input, output, and input-output parameter passing modes.
 Support a simple, but powerful condition and error-handling model.
 Return multiple results sets to the caller or to a client application.
 CREATE PROCEDURES select all student
AS
BEGIN
SELECT student_id, student_name, DOB, street, city, state,pin, from Student table
END

FUNCTIONS

Select all the student name and convert it into upper case: SELECT
UCASE(‘student_name’) FROM ‘student’WHERE 1

Select all the student name and convert it into lower case: SELECT
LCASE(‘student_name’) FROM ‘student’ WHERE 1

Removes leading and trailing spaces from student Name: SELECT

TRIM(‘student_name’) FROM ‘student’ WHERE 1

TRIGGERS
Triggers are stored programs, which are automatically
executed or fired when some events occur. Triggers are, in
fact, written to be executed in response to any of the following
events
−
 A database manipulation (DML) statement (DELETE, INSERT, or UPDATE)
 A database definition (DDL) statement (CREATE, ALTER, or DROP).

 A database operation (SERVERERROR, LOGON, LOGOFF, STARTUP, or

SHUTDOWN).

Triggers can be defined on the table, view, schema, or

database with which the event is associated.

Benefits of Triggers
 Triggers can be written for the following purposes −
 Generating some derived column values automatically
 Enforcing referential integrity
 Event logging and storing information on table access
  Auditing
 Synchronous replication of tables
 Imposing security authorizations
 Preventing invalid transactions

Trigger for Insert Statement:

DELIMITER $$
CREATE TRIGGER student
ALTER INSERT ON student
FOR EACH ROW
BEGIN
INSERT INTO student_id
SET
student_id = NEW.student_id,
date = NOW(),
action = student “ Details Inserted”
END$$
DELIMITER;

Trigger for Delete Statement:

DELIMITER $$
CREATE TRIGGER delete_subject
BEFORE DELETE ON subject
FOR EACH ROW
BEGIN
DELETE FROM subject WHERE
student_id = OLD student_id;
END$$
DELIMITER;

SECURITY ON DATABASE
GRANT:
GRANT select, Insert ,Update, Delete ON Sudents To
prachi;
GRANT All ON Student To prachi;

REVOKE:
REVOKE Delete ON Students FROM prachi;
REVOKE All ON Students FROM prachi
 Annexure–IV
Micro Project Evaluation
Sheet

Name of Student : MOHIT RAJESH KADAM.

Enrollment No :1916430046
Name of Program : COMPUTER ENGINEER (Second Shift) Semester : THIRD

Course Title : DATABASE MANAGEMENT SYSTEM Code :22319

Title of MicroProject : COURSE MANAGEMENT SYSTEM

Course Outcome Achieved:

1. Design Normalized database on given data.

2. Create and Manage Database using SQL
Command
3.Write PL/SQL code for given database.
4. Apply triggers on database also create procedure and function according to condition
5. Apply security and confidentiality on given database.

Sr. Poor Average Good Excellent Sub

Characteristics to be assessed
No (1-3) (4-5) (6-8) (9-10) Total
(A) Process and Product Assessment (Convert above total marks out of 6 Marks)
1 Relevance to the course
Literature Review / Information
2
Collection
Completion of the Target as per project
3
proposal
4 Analysis of Data and representation
5 Quality of Prototype / Model
6 Report Presentation
(B) Individual Presentation / Viva (Convert above total marks out of 4 Marks)
7 Presentation
8 Viva

(A) Process and Product Assessment (B) Individual Presentation / Viva

Total Marks (10)
(6 Marks) (4 Marks)

Comments / Suggestions about team work / leadership / inter-personal

communication (if any)
 Annexure–IV
Micro Project Evaluation Sheet

Name of Student : PRACHI PRADEEP PARAB Enrollment No

Name of Programme : COMPUTER ENGINEER (Second Shift)

Course Title : DATABASE MANAGEMENT SYSTEM

Title of Micro-Project : COURSE MANAGEMENT SYSTEM

Course Outcome Achieved:

1. Design Normalized database on given data.
2. Create and Manage Database using SQL Command
3. Write PL/SQL code for given database.
4. Apply triggers on database also create procedure and
function according to condition
5. Apply security and confidentiality on given database.
Poor Average Good
Sr. No Characteristics to be assessed
(1-3) (4-5) (6-8)
(A) Process and Product Assessment (Convert above total marks out of 6 Marks)
1 Relevance to the course
2 Literature Review / Information Collection
3 Completion of the Target as per project proposal
4 Analysis of Data and representation
5 Quality of Prototype / Model
6 Report Presentation
(B) Individual Presentation / Viva (Convert above total marks out of 4 Marks)
7 Presentation
8 Viva

(A) Process and Product Assessment (B) Individual Presentation / Viva

(6 Marks) (4 Marks)

Comments / Suggestions about team work /

leadership / inter-personal communication (if
any)
 Annexure–IV

Micro Project Evaluation Sheet

Name of Student : SAHIL NELSON GABRU Enrollment No :1916430048

Name of Programme : COMPUTER ENGINEER (Second Shift) Semester : THIRD

Course Title : DATABASE MANAGEMENT SYSTEM Code :22319

Title of Micro-Project : COURSE MANAGEMENT SYSTEM

Course Outcome Achieved:
1. Design Normalized database on given data.
2. Create and Manage Database using SQL Command
3. Write PL/SQL code for given database.
4. Apply triggers on database also create procedure and function according to condition
5. Apply security and confidentiality on given database.
Sr. Poor Average Good Excellent
Characteristics to be assessed Sub Total
No (1-3) (4-5) (6-8) (9-10)
(A) Process and Product Assessment (Convert above total marks out of 6 Marks)
1 Relevance to the course
2 Literature Review / Information Collection
Completion of the Target as per project
3
proposal
4 Analysis of Data and representation
5 Quality of Prototype / Model
6 Report Presentation
(B) Individual Presentation / Viva (Convert above total marks out of 4 Marks)
7 Presentation
8 Viva

(A) Process and Product Assessment (B) Individual Presentation / Viva

Total Marks (10)
(6 Marks) (4 Marks)

Comments / Suggestions about team work / leadership / inter-personal communication (if

any