University Database Design: ECM38IS CW2, Fall 2017, Database Tables

This assignment focuses on designing a database for an educational institution. It begins by creating tables to store various data, including colleges, schools, student details, eligible students, seat allotments, student reporting, fellowship details, and a details view. The assignment defines primary and foreign keys and presents an entity-relationship diagram to illustrate the connections between tables. It also includes a discussion of business and integrity rules. The solution addresses database anomalies related to insert, delete, and update processes. It normalizes the database to 1NF, 2NF, and 3NF. Finally, the assignment outlines user management, backup/recovery strategies, and storage management considerations for maintaining the database.

Running head: PG-DSA-ECM38IS-Fall-17-CW2
PG-DSA-ECM38IS-Fall-17-CW2
PART A
Name of the Student
Name of the University
Author Note

1
PG-DSA-ECM38IS-Fall-17-CW2
Table of Contents
Task 2:............................................................................................................................2
Task 3:............................................................................................................................3
Task 4:............................................................................................................................4
Task 5:............................................................................................................................7

2
PG-DSA-ECM38IS-Fall-17-CW2
Task 2:
The tables has been created as per the case study to cover the entire requirement.
DHE had developed the previous version of their working procedure with the help of excel.
The new system is to be developed using a database. The basic requirement of the
organization needs to be stored with the help of the following tables:
1. colleges
2. schools
3. studentDetails
4. eligibleStudents
5. seatAllotment
6. studentReporting
7. fellowship
8. detailsView
The details are to be stored in the above tables in the form of:
Colleges (collegeID (PK), names, address, affiliation, degreeOffered, duration,
feesYearly)
Schools (schoolID (PK), name, address, modulesOffered)
studentDetails (studentID (PK), schoolID (FK), passingYear, modCY, modPY)
eligibleStudents (studentID (FK), modulesTaken, modulesCleared, applyDate)
seatAllotment (studentID (FK), totalMarks, allotment, payment, deadlineProvided)
studentReporting (StudentID (FK), dadlineProvided, reportingDate )

3
PG-DSA-ECM38IS-Fall-17-CW2
fellowship (studentID (FK), collegeID (FK), attendance, toAttend, provided)
detailsView (studentID (FK), seatAllotment, fellowship)
The primary key and the foreign keys have been declared in the above portion and the
connection among the tables are shown in the following entity relationship diagram:

4
PG-DSA-ECM38IS-Fall-17-CW2

0
PG-DSA-ECM38IS-Fall-17-CW2
Task 3:
colleges
collegeID
(PK) names address affiliation
degreeOffere
d
durati
on
feesYea
rly
schools
schoolID
(PK) name address
modulesOff
ered
studentDetai
ls
studentID
(PK)
schoolID
(FK) passingYear modCY modPY
eligibleStud
ents
studentID
(FK)
modulesTak
en
modulesCle
ared applyDate
seatAllotme
nt
studentID
(FK) totalMarks allotment payment
deadlineProv
ided
studentRepo
rting
StudentID
(FK)
dadlineProv
ided
reportingDa
te
fellowship
studentID
(FK)
collegeID
(FK) attendance toAttend provided
detailsView
studentID
(FK)
seatAllotme
nt fellowship
The following are the business and integrity rules followed:
1. A college has many students
2. A college has different degree
3. A school has different modules to teach

1
PG-DSA-ECM38IS-Fall-17-CW2
4. A student has to pass in all modules
5. A student has to provide details of two years
6. All students with cleared modules is eligible
7. Eligible students needs to make a payment
8. Successful payment provides with a deadline for admission
9. Student has to report before the deadline
10. Fellowship is granted based on the attendance of the student
11. Students would be able to view their seat allotment details on the portal
12. Students would be able to view their fellowship details on the portal
Task 4:
The anomalies are related to the process of insert, delete and update. If all data is to be
stored in a single table then there would be repetition of large amount of data. The name of
schools has to be written every time a student’s name is to be entered. This can be removed
by decomposing the main table to store information of the schools in one table and connect
the students details with the help of the school id. The school table has to be divided into two
so that the courses offered and the maximum marks that is offered to the subject can be
recorded and cross-referenced with the main table. The new payment table is to be made so
that the payment related to each of the selected students can be stored easily without any
anomaly.
1NF:
colleges
collegeID
(PK) names address affiliation
degreeOffere
d
durati
on
feesYea
rly
schools
schoolID
(PK) name address
modulesOff
ered

2
PG-DSA-ECM38IS-Fall-17-CW2
studentDetai
ls
studentID
(PK)
schoolID
(FK) passingYear modCY modPY
eligibleStud
ents
studentID
(FK)
modulesTak
en
modulesCle
ared applyDate
seatAllotme
nt
studentID
(FK) totalMarks allotment payment
deadlineProv
ided
studentRepo
rting
StudentID
(FK)
dadlineProv
ided
reportingDa
te
fellowship
studentID
(FK)
collegeID
(FK) attendance toAttend provided
detailsView
studentID
(FK)
seatAllotme
nt fellowship
2NF:
colleges
collegeID
(PK) names address
affiliati
on
degreeOffere
d
durati
on
feesYea
rly
schools
schoolID
(PK) name address
courses
schoolID
(FK)
modulesOffe
red totalMarks
studentDetail
s
studentID
(PK)
schoolID
(FK) passingYear modCY modPY

3
PG-DSA-ECM38IS-Fall-17-CW2
eligibleStude
nts
studentID
(FK)
modulesTak
en
modulesClea
red
applyD
ate
seatAllotmen
t
studentID
(FK) totalMarks allotment
paymen
t
deadlineProvi
ded
studentRepor
ting
StudentID
(FK)
dadlineProvi
ded
reportingDat
e
fellowship
studentID
(FK)
collegeID
(FK) attendance
toAtten
d provided
detailsView
studentID
(FK)
seatAllotme
nt fellowship
3NF:
colleges
collegeID
(PK) names address
affiliati
on
degreeOffe
red
durati
on
feesYea
rly
schools
schoolID
(PK) name address
courses
schoolID
(FK)
modulesOffer
ed totalMarks
studentDetail
s
studentID
(PK)
schoolID
(FK) passingYear modCY modPY
eligibleStude
nts
studentID
(FK)
modulesClear
ed applyDate

4
PG-DSA-ECM38IS-Fall-17-CW2
seatAllotment
studentID
(FK) allotment
deadlineProvi
ded
payment
studentID
(FK) payment
studentReport
ing
StudentID
(FK)
deadlineProvi
ded reportingDate
fellowship
studentID
(FK)
collegeID
(FK) attendance
toAtten
d provided
detailsView
studentID
(FK) seatAllotment fellowship
Task 5:
User management: The users would be able to use the database for the management of
proper data for the students. As the details are sensitive in nature there needs to be the proper
management of the data. The data is to be collected for the students across the schools and the
colleges, which would be then composed, into a single database. This would help in the
compilation of the database for the proper completion of the scenario provided in the case
study.
Backup/recovery strategy: The database has to be properly maintained by the security
officer and take regular or periodical backup of the database in case of any dispute that might
occur at any time on the database.
Storage management: The storage needs to maintained at a regular basis and to be
looked after by the officer in charge of the database. The database needs to be cleared and
reformatted for the properly maintained.