KL University

Department of Computer Science Engineering

Course code -15CS2007

Database Systems

II B.Tech – 2nd Semester

Academic Year 2017-2018

Project Based Lab

ON
KLU Alumni Database Management System
Submitted by
Section – S04
Batch No: 7

Student ID Student Name Department

160030615 K.Vishnu C.S.E
160030620 K Rahul C.S.E
160030626 K Lalitha C.S.E
 K L University
DEPARTMENT OF COMPUTER SCIENCE ENGINEERING
(DST-FIST Sponsored Department)

CERTIFICATE

This is to certify that the course based project entitled “KLU ALUMNI DATA
BASE MANAGEMENT SYSTEM” is a bonafide work done by K Vishnu (160030615),K
Rahul(160030620),K Lalitha(160030626) in partial fulfilment of the requirement for the
award of degree in BACHELOR OF TECHNOLOGY in Computer Science Engineering
during the academic year 2017-2018.

Faculty In Charge Head of the Department

Dr.Raju Anitha Prof. E. Suresh babu
 DEPARTMENT OF COMPUTER SCIENCE ENGINEERING
(DST-FIST Sponsored Department)

DECLARATION

We hereby declare that this project based lab report entitled “KLU ALUMNI DATA
BASE MANAGEMENT” has been prepared by us in partial fulfillment of the requirement
for the award of degree “BACHELOR OF TECHNOLOGY in COMPUTER SCIENCE
ENGINEERING” during the academic year 2017-2018.

We also declare that this project based lab report is of our own effort and it has not
been submitted to any other university for the award of any degree.

Date:

Place: Vaddeswaram

Name Student ID
K Vishnu 160030615
K Rahul 160030620
K Lalitha 160030626
ACKNOWLEDGMENTS

Our sincere thanks to Dr.Raju Anitha in the Lab for his outstanding support throughout the
project for the successful completion of the work.

My sincere thanks to M.Naga Bhushana Rao Course coordinator of Data base system for
helping us in the completion of our project based laboratory.

We express our gratitude to E.Suresh Babu Head of the Department for Computer Science
and Engineering for providing us with adequate facilities, ways and means by which we are
able to complete this term paper work.

We would like to place on record the deep sense of gratitude to the honourable Vice
Chancellor, K L University for providing the necessary facilities to carry the concluded term
paper work.

Last but not the least, we thank all Teaching and Non-Teaching Staff of our department and
especially my classmates and my friends for their support in the completion of our project
work.

Finally, it is pleased to acknowledge the indebtedness to all those who devoted

themselves directly or indirectly to make this project report success.

PROJECT ASSOCIATES

Name Student ID
K Vishnu 160030615
K Rahul 160030620
K Lalitha 160030626
TABLE OF CONTENTS
Abstract....................................................................................................................................................................7
Introduction..............................................................................................................................................................8
PROJECT DESCRIPTION......................................................................................................................................9
List of Entities & Attributes...................................................................................................................................10
ER Diagram (Conceptual Model)..........................................................................................................................11
Schema Diagram....................................................................................................................................................12
Normalization & Final List of Relations................................................................................................................13
Create & Insert SQL Queries.................................................................................................................................14
SQL Queries related to Report Generation............................................................................................................15
Conclusion..............................................................................................................................................................16
ABSTRACT

Alumni are an association of graduates or broadly the former students of a university. The
title of the project is KL University Alumni database . The information system for
communication with graduates represents one of many ways how university can keep in
touch with its graduates. Except for communication between university and its graduates, the
information system should allow communication between graduates themselves and their
personal presentation in public. An alumni database is very essential to carry out crucial
activities like institute events, fundraising and to maintain contacts with the corporate
organizations in which the alumni are working. The information system for communication
with graduates represents one of many ways how university can keep in touch with its
graduates. An alumni database management system brings a university’s alumni on one
platform, organize them batch wise, location wise and their functional area, which in turn
helps the university to communicate with the alumni effectively for various purposes. It is
also used to maintain the data of the alumni as well as the corporate supporters who offer
funds to the university. The presented information system includes all these points and
focuses on security, usability and comfortable user interface. In this we design a
computerized database for KL University Alumni. The database captures all relevant
information on alumni of the college and the corporate supporters of the university.
INTRODUCTION

  Database is an organized collection of data. The data is typically organized to model

aspects of reality in a way that supports processes requiring information. A DBMS makes it
possible for end users to create, read, update and delete data in a database. The DBMS
essentially serves as an interface between the database and end users or application programs,
ensuring that data is consistently organized and remains easily accessible. The DBMS
manages three important things: the data, the database engine that allows data to be accessed,
locked and modified and the database schema, which defines the database’s logical structure.
These three foundational elements help provide concurrency, security, data integrity and
uniform administration procedures. The DBMS can offer both logical and physical data
independence. That means it can protect users and applications from needing to know where
data is stored or having to be concerned about changes to the physical structure of data.

The aim of this Alumni Management System project is to build a system that
will be able to manage alumni data of a college and provide easy access . Contact between
alumni can be used to forge business connections and to gain references or insight in a new
field. It is also used to maintain the data of the alumni as well as the corporate supporters who
offer funds to the university.
PROJECT DESCRIPTION
The Dean of the business school wishes to create an UTD “family” consisting of its graduates
and corporations that have been staunch supporters of the School of Management. Finding
the existing database inadequate in terms of providing him with the information he needs to
achieve his goal, he wishes to develop a new alumni database for the college. He wants the
new database to capture all relevant information on alumni of the college and the corporate
supporters of the college. To build a long-term relationship with alumni, he wishes to keep
track of all alumni from each functional area (e.g., MIS, Acctg, Fin, Mkt, etc.) of the college,
where they work (or have worked in the past), and any donations that they have made to the
college. Similarly, for corporate supporters, the database must capture all relevant
information on donations made by each corporation.

Upon further interviews with the Dean, he has indicated the following specific requirements:

1. A report that displays alumni information for a specified area for a particular year (e.g.,
1999 MIS graduates). The report should list, for the specified area and year of graduation,
each alumnus’s name, e-mail address, the degree earned (e.g., BS, MS, MBA, Ph.D., etc.),
work phone number, and home phone number. Note that a similar report could be required
for any area and any graduating year.

2. For a specified city, a report listing all alumni who live in that city. The report will display
the name of the city, and for each alumnus in that city, their name, home address, email
address, work phone, and home phone.

3. A report listing all corporate donors who have donated a total amount greater than
Rs.25,000. The report will be sorted in descending order of the donated amount.

4. A report that displays all donations made to a particular G/L (General Ledger) account.
The ID of the donor, as well as the date and amount of the donation must be displayed.

5. A report listing all alumni working for a particular company. This report must also display
the date an individual joined the company, as well as, their job title and salary.

6. A report that displays the employment history for a particular alumnus. The report must
show, for each employer that alumnus has worked for, the employer name, the most recent
job title the alumnus had with that employer company (e.g., Vice-President), the date the
alumnus joined the company, and the date the alumnus left the company (if applicable).
LIST OF ENTITIES & ATTRIBUTES

List of entities:

1.Alumni

2. Company

3.Corporate Supporters

4.University

5.Fund

List of attributes:

1.Alumini

 Alumni id
 Alumini name
 Email
 Degree
 Phone number-residence/ personal (multivalued attribute)
 Graduating year
 Functional area
 Home address- door no,street,city,state,pincode (composite attribute)

2. Company

 Company id
 Company industry code
 Join date
 Left date
 Job title
 Salary
 Employer name
 Employee id
3. Corporate supporters

 Corporation-id
 Corporation name
 Address
 Phn number

4.University

 Branch id
 Address
 Phn no

5.Fund

 Fund id
 Donor id
 Amount
 Type of fund
 Date of donation
 Account type

List of relationships:

 An alumni works for the university

Name of the relation: works
Cardinality: A:U=N:1
Participation: A:U =partial: full
 An alumni works for a company
Name of the relation: works
Cardinality: A:C=N:1
Participation: A:U= partial :full
 An alumni donates fund to the university
Name of the relation: donates
Cardinality: A: F:U= N:N:1
Participation: A:F:U= partial :partial:full
  A corporation donates fund to the university
Name of the relation: donates
Cardinality: C:F:U=N:N:1
Participation: C:U= partial :partial:full

List of Constraints:

 An alumni’s name has both first and last names

 The address of an alumni is a composite attribute that includes the door number,
street, city ,state and pin code.
 The contact number of an alumni is a multivalued attribute that may include an
alumni’s residence and mobile phone numbers.
 The email of an alumni is a multivalued attribute that may include an alumni’s
personal and university mail ids.
 A company’s ID number is a 7 digit number uniquely identifying a particular
company and a company’s industry id is a 3 digit code.

List of assumptions:

 There cannot be a university in which no alumni is working

 There can be some alumni who are not working in a university
 There cannot be a company in which no alumni of the university is working
 There can be some alumni who are not working in any company
 There cannot be any university that does not receive donation from any alumni
 There can be some alumni who are not donating fund to the university
 There cannot be any university that does not receive donation from any corporation
 There cannot be any corporation that is not donating fund to the university
ER DIAGRAM (CONCEPTUAL MODEL)
SCHEMA DIAGRAM
ALUMNI

alumni_id first_name last_name degree graduation_year function_area door_no

street city state pincode company_id(FK) branch_id(FK)
COMPANY

comp_i com_ind_code emp_name emp_id join_date left_date job_title salary

d
CORPORATE_SUPPORTERS

corporate_i corporate_name ph_no location city state pincode

d
UNIVERSITY

branch_id branch_name ph_no city state pincode

FUND

fund_id donor_id amount type_of_fund date_of_donation account_type

ALUMNI_PHONE

ph_id ph_type ph_number alumni_id(FK)

ALUMNI_EMAIL

alumniemail_id email_type email_id alumni_id(FK)

ALUMNI_DONATION

al_donation_id alumni_id(FK) fund_id(FK) branch_id(FK)

CORP_DONATIONS

Cor_donation_id corporate_id(FK) fund_id(FK) branch_id(FK)

CREATE & INSERT SQL QUERIES

create table if not exists ALUMNI (al_id int PRIMARY KEY NOT
NULL,f_name varchar(20) NOT NULL,l_name varchar(20) NOT NULL,
degree varchar(50) NOT NULL,grad_year YEAR NOT NULL,fun_area
varchar(90) NOT NULL,door_no varchar(10) NOT NULL,street varchar(20)
NOT NULL,city varchar(20) NOT NULL,state varchar(40) NOT
NULL,pincode DECIMAL(6,0) NOT NULL,com_id int NOT NULL,branch_id
int NOT NULL );

create table if not exists COMPANY(com_id int PRIMARY KEY NOT

NULL,com_ind_code int NOT NULL,emp_name varchar(20) NOT
NULL,emp_id int NOT NULL,join_date DATE NOT NULL ,job_title
varchar(30) NOT NULL,salary int NOT NULL, left_date DATE);

create table if not exists CORPORATE_SUPPORTERS(cor_id int PRIMARY

KEY NOT NULL,cor_name varchar(40) NOT NULL,phn_no bigint NOT
NULL,location varchar(50) NOT NULL,city varchar(20) NOT NULL,state
varchar(40) NOT NULL,pincode DECIMAL(6,0) NOT NULL);

create table if not exists UNIVERSITY (branch_id int PRIMARY KEY NOT
NULL,branch_name varchar(20) NOT NULL,phn_no bigint NOT NULL,city
varchar(20) NOT NULL,state varchar(40) NOT NULL,pincode
DECIMAL(6,0) NOT NULL);

create table if not exists FUND (fund_id int PRIMARY KEY NOT
NULL,donor_id int NOT NULL,amount float(6,3) NOT NULL,type_of_fund
varchar(50) NOT NULL,date_of_donation DATE NOT NULL,account_type
varchar(30) NOT NULL);

create table if not exists ALUMNI_PHONE(alph_id int PRIMARY KEY NOT

NULL,ph_type varchar(10) NOT NULL,ph_no bigint UNIQUE NOT
NULL,al_id int NOT NULL);
create table if not exists ALUMNI_EMAIL(ae_id int PRIMARY KEY NOT
NULL,email_type varchar(10) NOT NULL, mail_id varchar(40) NOT
NULL,al_id int NOT NULL);

create table if not exists AL_DONATION(al_don_id int PRIMARY KEY NOT

NULL,al_id int NOT NULL,fund_id int NOT NULL ,branch_id int NOT
NULL);

create table if not exists COR_DONATION(cor_don_id int PRIMARY KEY

NOT NULL,cor_id int NOT NULL,fund_id int NOT NULL ,branch_id int
NOT NULL);

alter table ALUMNI add FOREIGN KEY (com_id) REFERENCES

COMPANY (com_id);

alter table ALUMNI add FOREIGN KEY (branch_id) REFERENCES

UNIVERSITY (branch_id);

alter table ALUMNI_PHONE add FOREIGN KEY (al_id) REFERENCES

ALUMNI(al_id);

alter table ALUMNI_EMAIL add FOREIGN KEY (al_id) REFERENCES

ALUMNI(al_id);

alter table AL_DONATION add FOREIGN KEY (branch_id) REFERENCES

UNIVERSITY (branch_id);

alter table AL_DONATION add FOREIGN KEY (al_id) REFERENCES

ALUMNI (al_id);

alter table AL_DONATION add FOREIGN KEY (fund_id) REFERENCES

FUND (fund_id);

alter table COR_DONATION add FOREIGN KEY (branch_id)

REFERENCES UNIVERSITY (branch_id);
alter table COR_DONATION add FOREIGN KEY (fund_id) REFERENCES
FUND (fund_id);

alter table COR_DONATION add FOREIGN KEY (cor_id) REFERENCES

CORPORATE_SUPPORTERS (cor_id);

alter table fund modify column amount bigint;

INSERT QUERIES

insert into company

values(6200026,100,'srirama',950,20050405,'hr_mngr',50000,20080812),
(7200027,101,'sita',951,20100304,'analyst',35000,20110617),
(9200029,103,'krishna',953,20050505,'manager',60000,20140607),
(8200028,102,'satya',954,20111209,'ca',75000,20120204);

insert into corporate_supporters

values(50,'abc_tech',9441867980,'madhuranagar','vizag','AP',534009),(60,
'trendset',7382045470,'benzcircle','Vijayawada','AP',534006),
(70,'esflabs',9959381093,'jublieehills','hyderabad','Telangana',634006);

insert into university values(01,'hyd',344563,'hyderabad','Telangana',543008),

(02,'vij',244571,'vijayawada','AP',670009);

insert into fund values(10,11,50000,'scholarship',20150908,'G/L'),

(20,22,30000,'general',20100305,'G/L'),
(30,33,75000,'scholarship',20161212,'G/L'),
(40,44,100000,'scholarship',20170101,'G/L'),
(50,55,40000,'general',20090909,'G/L'),(60,66,90000,'general',10000,'G/L');

insert into alumni

values(950,'srirama','ayodhya','BS',1999,'MIS','12A','RAM_NAGAR','HYDER
ABAD','TELANGANA',520004,6200026,01),
(951,'sita','mithila','BS',2000,'MIS','13A','SITA_NAGAR','NALGONDA','A_P',
520007,7200027,02),
(953,'krishna','gopala','MBA',1997,'Fin','14A','krish_nagar','NALGONDA','TEL
ANGANA',520013,6200026,01),
(954,'satya','bhama','PHD',1997,'ACCOUNTING','15B','SATYA_NAGAR','GU
NTUR','A_P',520009,8200028,02),
(955,'vishnu','murthy','BS',1999,'MIS','16B','VISHNU_NAGAR','BHUBHANE
SHWAR','ODHISA',620003,9200029,01);

insert into alumni_phone values(1,'residence',234461,950),

(2,'mobile',944134560,950),(3,'mobile',9980281064,951),
(4,'residence',231198,953),(5,'mobile',73456098839,954),
(6,'residence',254571,954),(7,'residence',5267686,955);

insert into ALUMNI_EMAIL values(11,'personal','sriram&gmail.com',950),

(22,'university_mail','[email protected]',950),(33,'university_mail','[email protected]',951),
(44,'persoanl','[email protected]',953),(55,'university_mail','[email protected]',953),
(66,'universitymail','[email protected]',954),(77,'universitymail','[email protected]',955);

insert into al_donation values (111,950,10,01),(112,951,20,02),(113,954,30,02),

(114,955,40,01);

insert into cor_donation values(1001,50,50,01),(1002,60,60,02);

SQL QUERIES RELATED TO REPORT GENERATION

1.Display alumni
first_name,last_name,degree,graduation_year,door_no,street,city,state,pincode for a specified
functional area in a particular year of 1999 MIS graduates.

Query:

select *

from alumni

where grad_year=1999 AND fun_area='MIS';

2.List all corporate names and name of the branch to which they have donated when the
amount is greater than 25000 and sort the donated amount in descnding order.

Query:

select cor_name,branch_name,amount,type_of_fund,donor_id,date_of_donation

from (((COR_DONATION c inner join corporate_supporters s on c.cor_id=s.cor_id)inner

join FUND f on f.fund_id=c.fund_id)inner join UNIVERSITY u on u.branch_id
=c.branch_id)
where amount>=25000 ORDER BY amount desc;

3. Display all donations made to a particular G/L(general ledger)account .The id of the donor
as well as the amount of donation,date of donation,type of fund donated by corporate name
abc_tech.

Query:

select donor_id,amount,date_of_donation,type_of_fund

from ((COR_DONATION c inner join corporate_supporters s on c.cor_id=s.cor_id)inner

join FUND f on f.fund_id=c.fund_id)

where cor_name='abc_tech';

4.Display all alumni working for a particular company .display the name,join date and left
date , job and salary details of individuals .

Query:

select f_name,l_name,join_date,job_title,salary,left_date

from alumni a inner join company c on a.com_id=c.com_id ;

 5.Display the details of alumni who has donated amount greater than 10000 to all the
branches in the university.

Query:

select f_name,l_name,degree,grad_year,city,state,amount

from ((al_donation d inner join alumni a on d.al_id=a.al_id)inner join FUND f on

f.fund_id=d.fund_id)

where amount>=10000 order by amount desc ;

6.Display all phone numbers,phone types,of all the alumnis and count no.of phone numbers
and group them according to phone_numbers of alumni.

Query:

select f_name,l_name,ph_no,ph_type,count(ph_no)
from alumni_phone p inner join alumni a on p.al_id=a.al_id;

7. Display the f_name,l_name,degree,grad_year of particular alumni who had studied in

university in which branch name starts with letter h.

Query:

select branch_name,f_name,l_name,degree,grad_year

from alumni a inner join university u on a.branch_id=u.branch_id

where branch_name like 'h%';

8. . Display all email_id,email types,of all the alumnis and count no.of email and group them
according to email_type of alumni.

Query:

select f_name,l_name,mail_id,email_type,count(mail_id)

from alumni_email m inner join alumni a on m.al_id=a.al_id

group by email_type;
9. Display the corparate donations type_of_fund,date_of_donation and account_type to the
hyderabad branch of university.

Query:

select type_of_fund,date_of_donation, account_type

from ((cor_donation d inner join fund f on f.fund_id=d.fund_id)

inner join university u on u.branch_id=d.branch_id)

where branch_name='hyd';

10. Display details of alumni who studied in the hyderabad branch of the university.

Query:

select f_name,l_name,degree,grad_year

from alumni a inner join university u on u.branch_id=a.branch_id

where branch_name ='hyd';

CONCLUSION

The alumni data base created will be very useful to store the data of the university’s alumni .
Apart from the alumni data the database also stores the information of the corporate
supporters that are funding the university in various aspects. A clear information can be
obtained about the alumni who are donating funds to the university. The database will make it
very easy to retrieve information and update the existing data.