Database Design and Implementation Assignment: Alumni Database Project

This assignment presents a comprehensive database design and implementation for an alumni management system. It begins with the creation of an Entity-Relationship Diagram (ERD) to visually represent the database structure, followed by a detailed data dictionary that defines each table, column, data type, and constraint. The core of the assignment involves the SQL implementation, including the creation of tables such as CITY, SCHOOL, ALUMNI_GROUP, ALUMNI_PERSONAL_DETAILS, ALUMNI_QUALIFICATION, ALUMNI_JOB_DETAILS, ALUMNI_DONATIONS, ALL_ALUMNI_SUPPORTERS, EVENT_GUEST, EVENT, and EVENT_TYPE, along with appropriate primary and foreign key constraints. Furthermore, the assignment includes the creation of several views (A-H) to retrieve and present specific data subsets, such as alumni from a certain year, alumni living in a particular city, business supporters, and donation details. These views demonstrate the ability to query and manipulate the database to extract meaningful insights. The SQL code for the tables and views is provided, offering a complete solution to the assignment.

Part 1: ERD

Part 2: Data dictionary
Table : CITY
Column Name Data Type Constraint
cityId NUMBER PRIMARY KEY
cityName VARCHAR(50)
Table : SCHOOL
Column Name Data Type Constraint
schoolId NUMBER PRIMARY KEY
schoolName VARCHAR(90)
Table : ALUMNI_GROUP
Column Name Data Type Constraint
groupId NUMBER PRIMARY KEY
groupType CHAR(10) CHECK (groupType in
('GRADUATES','BUSINESSES'))
Table : ALUMNI_PERSONAL_DETAILS
Column Name Data Type Constraint
alumniId NUMBER PRIMARY KEY
alumniName VARCHAR(30)
workContact VARCHAR(12)
homeContact VARCHAR(12)
emailId VARCHAR(70)
cityId NUMBER CONSTRAINT cityId_FK
FOREIGN KEY (cityId)
REFERENCES CITY(cityId)
ON DELETE CASCADE
address VARCHAR(100)
alumniGroupTypeId NUMBER CONSTRAINT
alumniGroupTypeId_FK
FOREIGN KEY
(alumniGroupTypeId)
REFERENCES
ALUMNI_GROUP(groupId) ON
DELETE CASCADE

Table : ALUMNI_QUALIFICATION
Column Name Data Type Constraint
alumniId NUMBER CONSTRAINT alumniId_FK FOREIGN
KEY (alumniId) REFERENCES
ALUMNI_PERSONAL_DETAILS(alumniId)
ON DELETE CASCADE
schoolId NUMBER CONSTRAINT schoolId_FK FOREIGN
KEY (schoolId) REFERENCES
SCHOOL(schoolId) ON DELETE
CASCADE
disciplineArea VARCHAR(70)
degreeName VARCHAR(70)
passoutYear NUMBER(4)
Table : ALUMNI_JOB_DETAILS
Column Name Data Type Constraint
alumniId NUMBER CONSTRAINT alumniId_JOB_FK
FOREIGN KEY (alumniId) REFERENCES
ALUMNI_PERSONAL_DETAILS(alumniId)
ON DELETE CASCADE
companyName VARCHAR(50)
companyHonor VARCHAR(30)
jobTitle VARHCAR(40)
salary NUMBER
doj DATE
endDate DATE
Table : ALUMNI_DONATIONS
Column Name Data Type Constraint
alumniId NUMBER CONSTRAINT alumniId_DONATIONS_FK
FOREIGN KEY (alumniId) REFERENCES
ALUMNI_PERSONAL_DETAILS(alumniId)
ON DELETE CASCADE
donationAmount NUMBER
datee DATE

Table : ALL_ALUMNI_SUPPORTERS
Column Name Data Type Constraint
alumniId NUMBER CONSTRAINT
alumniId_SUPPORTERS_FK FOREIGN
KEY (alumniId) REFERENCES
ALUMNI_PERSONAL_DETAILS(alumniId)
ON DELETE CASCADE
supportType VARCHAR(70)
datee DATE
Table : EVENT_GUEST
Column Name Data Type Constraint
eventId NUMBER CONSTRAINT alumniId_EVENT_FK
FOREIGN KEY (eventId) REFERENCES
EVENT(eventId) ON DELETE CASCADE
guestId NUMBER CONSTRAINT guestId_GUEST_FK
FOREIGN KEY (guestId) REFERENCES
ALUMNI_PERSONAL_DETAILS(alumniId)
ON DELETE CASCADE
guestTypeDesc VARCHAR(60)
guestStatus CHAR(3)
Table : EVENT_TYPE
Column Name Data Type Constraint
eventTypeId NUMBER PRIMARY KEY
eventType CHAR(30) check (eventType in ('SOCIAL',
'PROFESSIONAL
DEVELOPMENT'))
Table : EVENT
Column Name Data Type Constraint
eventId NUMBER
eventTypeId NUMBER CONSTRAINT
eventTypeId_FK FOREIGN
KEY (eventTypeId)

REFERENCES
EVENT_TYPE(eventTypeId)
ON DELETE CASCADE
eventName VARCHAR(50)
eventDate DATE
eventPlace NUMBER CONSTRAINT eventPlaceId_FK
FOREIGN KEY (eventPlace)
REFERENCES CITY(cityId)
ON DELETE CASCADE
Part 3: Implementation
CREATE TABLE CITY(cityId NUMBER PRIMARY KEY,cityName VARCHAR(50));
CREATE TABLE SCHOOL(schoolId NUMBER PRIMARY KEY,schoolName VARCHAR(90));
CREATE TABLE ALUMNI_GROUP(groupId NUMBER PRIMARY KEY,groupType CHAR(10)
CHECK (groupType in ('GRADUATES','BUSINESSES')));
CREATE TABLE ALUMNI_PERSONAL_DETAILS(alumniId NUMBER PRIMARY
KEY,alumniName VARCHAR(30),workContact VARCHAR(12),homeContact VARCHAR(12),emailId
VARCHAR(70),
cityId NUMBER ,address VARCHAR(100),alumniGroupTypeId NUMBER , CONSTRAINT
alumniGroupTypeId_FK FOREIGN KEY (alumniGroupTypeId) REFERENCES
ALUMNI_GROUP(groupId) ON DELETE CASCADE, CONSTRAINT cityId_FK FOREIGN KEY
(cityId) REFERENCES CITY(cityId) ON DELETE CASCADE);
CREATE TABLE ALUMNI_QUALIFICATION(alumniId number,schoolId NUMBER,disciplineArea
VARCHAR(70),degreeName VARCHAR(70),passoutYear NUMBER (4), CONSTRAINT alumniId_FK
FOREIGN KEY (alumniId) REFERENCES ALUMNI_PERSONAL_DETAILS(alumniId) ON DELETE
CASCADE);
CREATE TABLE ALUMNI_JOB_DETAILS(alumniId NUMBER,companyName VARCHAR(50),
companyHonor VARCHAR(30),jobTitle VARCHAR(40),salary NUMBER,doj date,endDate date,
CONSTRAINT alumniId_JOB_FK FOREIGN KEY (alumniId) REFERENCES
ALUMNI_PERSONAL_DETAILS(alumniId) ON DELETE CASCADE);

CREATE TABLE ALUMNI_DONATIONS(alumniId NUMBER,donationAmount NUMBER,datee
DATE,CONSTRAINT alumniId_DONATIONS_FK FOREIGN KEY (alumniId) REFERENCES
ALUMNI_PERSONAL_DETAILS(alumniId) ON DELETE CASCADE);
CREATE TABLE ALL_ALUMNI_SUPPORTERS(alumniId NUMBER,supportType
VARCHAR(70),datee DATE,
CONSTRAINT alumniId_SUPPORTERS_FK FOREIGN KEY (alumniId) REFERENCES
ALUMNI_PERSONAL_DETAILS(alumniId) ON DELETE CASCADE);
CREATE TABLE EVENT_GUEST(eventId NUMBER,guestId NUMBER,invitee
VARCHAR(30),guestTypeDesc VARCHAR(60),guestStatus CHAR(3) CHECK (guestStatus
in('YES','NO') ) ,CONSTRAINT alumniId_GUEST_FK FOREIGN KEY (eventId) REFERENCES
EVENT(eventId) ON DELETE CASCADE ,CONSTRAINT guestId_GUEST_FK FOREIGN KEY
(guestId) REFERENCES ALUMNI_PERSONAL_DETAILS(alumniId) ON DELETE CASCADE);
CREATE TABLE EVENT(eventId NUMBER PRIMARY KEY,eventTypeId NUMBER,eventName
VARCHAR(50),
eventDate DATE,eventPlace NUMBER, CONSTRAINT eventTypeId_FK FOREIGN KEY
(eventTypeId) REFERENCES EVENT_TYPE(eventTypeId) ON DELETE CASCADE, CONSTRAINT
eventPlaceId_FK FOREIGN KEY (eventPlace) REFERENCES CITY(cityId) ON DELETE
CASCADE);
CREATE TABLE EVENT_TYPE(eventTypeId NUMBER PRIMARY KEY, eventType CHAR(30)
check (eventType in ('SOCIAL', 'PROFESSIONAL DEVELOPMENT')));

Part 4: Views
View A: List the Business Information Systems alumni from 2015 including
their name, e-mail address, work phone number, and home phone number
CREATE OR REPLACE VIEW ALUMNI_BUSINESS_VIEW AS(select alumniName,
emailId, workContact, homeContact from alumni_personal_details where alumniId IN (select
alumniId from alumni_qualification where passoutYear >= 2015));
View B: List the alumni who live in Perth along with the alumns’ contact details
CREATE OR REPLACE VIEW ALUMNI_IN_PERTH_VIEW AS (SELECT * from
ALUMNI_PERSONAL_DETAILS WHERE cityId =(SELECT cityId FROM CITY where cityName
like 'Perth'));

View C: List all the business supporters who provide non-donation support, and the support they
provide.
CREATE OR REPLACE VIEWALUMNI_NON_DONATION_SUPPORT _VIEW AS (
SELECT alumniName, supportType FROM ALUMNI_PERSONAL_DETAILS ap,
ALL_ALUMNI_SUPPORTERS aas WHERE ap.alumniId IN( SELECT alumniId FROM
ALL_ALUMNI_SUPPORTERS WHERE alumniId IN (SELECT alumniId FROM
ALUMNI_PERSONAL_DETAILS where alumniGroupTypeId = (SELECT groupId FROM
ALUMNI_GROUP WHERE groupType LIKE 'BUSINESSES'))) and ap.alumniId = aas.alumniId);
View D: List all of the business supports who have donated a total amount greater than
$25,000. The report should be sorted in descending order of the tot/al donated amount.
CREATE OR REPLACE VIEW BUSINESS_SUPPORTS_VIEW AS (SELECT alumniName ,
SUM( donationAmount) donationAmount FROM ALUMNI_DONATIONS ad,
ALUMNI_PERSONAL_DETAILS apd WHERE ad.alumniId IN (SELECT alumniId FROM
ALUMNI_DONATIONS aad WHERE aad.alumniId IN (SELECT alumniId FROM
ALUMNI_PERSONAL_DETAILS apd where alumniGroupTypeId = (SELECT groupId FROM
ALUMNI_GROUP WHERE groupType LIKE 'BUSINESSES') and apd.alumniId = aad.alumniId ) and
aad.alumniId = ad.alumniId) and ad.alumniId = apd.alumniId group by ad.alumniId, alumniName having
SUM( donationAmount) > 25000);
View E: List all donations made, by both individual alumni and business donors.
The Name and ID of the donor, as well as the date and amount of the donation must
be displayed.
CREATE OR REPLACE VIEW DONOR_DETAILS_VIEW AS(SELECT alumniName "NAME", ad.alumniId
"DONOR_ID", datee "DATE", donationamount AMOUNT FROM ALUMNI_DONATIONS ad,
ALUMNI_PERSONAL_DETAILS apd WHERE ad.alumniId = apd.alumniId );
View F: List all the alumni working for a particular company (e.g., Telstra). This
report must also display the date an individual joined the company, as well as, their
job title and salary.

CREATE OR REPLACE VIEW ALUMNI_JOB_DETAILS_VIEW AS(SELECT alumniId, doj “DATE_OF_JOINING”,
jobTitle “JOB_TITLE”, salary “SALARY” FROM ALUMNI_JOB_DETAILS WHERE companyName LIKE
'Telstra');
View G: List 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).
CREATE OR REPLACE VIEW EMPLOYMENT_HISTORY AS( SELECT companyName , alumniName
"employer_name", jobTitle, companyHonor, doj date_of_joining, endDate FROM
ALUMNI_JOB_DETAILS ajd, ALUMNI_PERSONAL_DETAILS apd WHERE ajd.alumniId = apd.alumniId and
alumniName LIKE '%Joel' order by doj DESC);
View H: List all of the social events held in Singapore during 2015, along with the
total number of business and alumni attendees at each.
SQL>CREATE OR REPLACE VIEW SOCIAL_EVENT_INFO_VIEW AS (SELECT COUNT(distinct
guestId) TOTAL_GRADUATES_ATTENDEES,
2 TOTAL_BUSINESS_ATTENDEES FROM (SELECT TOTAL_BUSINESS_ATTENDEES FROM
3 (SELECT COUNT(distinct guestId) TOTAL_BUSINESS_ATTENDEES FROM EVENT_GUEST
4 WHERE guestId IN(SELECT alumniId FROM ALUMNI_PERSONAL_DETAILS WHERE
5 alumniGroupTypeId =( SELECT groupId FROM ALUMNI_GROUP WHERE groupType LIKE
6 '%BUSINESSES%')) and eventId IN(SELECT eventId FROM EVENT WHERE eventTypeId
7 IN(SELECT eventTypeId FROM EVENT_TYPE WHERE eventType LIKE '%SOCIAL%') and
8 eventPlace = (SELECT cityId FROM CITY WHERE cityName LIKE '%Singapore%') and
9 to_char(to_date(eventDate,'DD-MM-YYYY'),'yy') >= 15) )),EVENT_GUEST WHERE guestId
10 IN(SELECT alumniId FROM ALUMNI_PERSONAL_DETAILS WHERE alumniGroupTypeId
11 =(SELECT groupId FROM ALUMNI_GROUP WHERE groupType LIKE '%GRADUATES%'))
and
12 eventId IN(SELECT eventId FROM EVENT WHERE eventTypeId IN(SELECT eventTypeId
FROM
13 EVENT_TYPE WHERE eventType LIKE '%SOCIAL%') and eventPlace = (SELECT cityId FROM
14 CITY WHERE cityName LIKE '%Singapore%') and to_char(to_date(eventDate,'DD-MM-
YYYY'),'yy')

15 >= 15) group by total_business_attendees);
TOTAL_GRADUATES_ATTENDEES TOTAL_BUSINESS_ATTENDEES
------------------------- ------------------------
2 1