Lovely Professional University Phagwara (Punjab)
Submitted Term Paper report, 2013 of
INT 308 In
DataBase Management System By Name - FIROZ ALAM (Reg. No) – 11114211 Roll no. A05
Under the guidance of
Vipin Kumar
TABLE OF CONTENTS:-
1. Objective 2. Introdction 3. Overvie! ". Entitie# $nd T$b%e &. E' (i$)r$* +. Nor*$%i,$tion . Conc%#ion 8. 'eerence#
AC/NOLE(EENT
The satisfaction that accompanies the successful completion of any task ould be incomplete ithout the mention mention of people hose ceaseless cooperation made it possible, hose constant guidance and encouragement cron all efforts ith success! I ould like to e"press my gratitude to my sub#ect teacher $r!%ipin &umar for the guidance, inspiration and constructi'e suggestions that has help in completion and final e"ecution of my case study! I ould also like to thank the almighty god,my parents and all those ho guided and supported me!
Firo, A%$*
OBECTI4E:-
$in $i* in deve%o5in) ($t$b$#e: To provide an easy way not only to automate automate all functionalities of a college, but also to provide full functional reports to top management of college with the finest of details about any aspect of college. College Management System is software has the perspective of attaining attraction of those colleges which don’t have onegood performingsoftware for eeping their information secure and mae their management easier. College Management System provides one attractive environment where you can manipulate data and information about students and staff easily. So we can say the Core purpose of designing !College Management System" is to manage the tas related to the college students#employees and to reduce time to searching of appropriate candidates in college view.
The software helps you with the daily routine of college management and reduces the paper wor to $ero, not only this the software saves your time, helps in proper utili$ation of time and most important, helps you to have complete control on your college
INT'O(6CTION:-
College management is an integrated web application to handle various academic and non %academic activities of college. The system can access by every student#faculties#employees of the institution through internet connected computers. This system provides the detail structure of the college campus and its departments. College Management System synchroni$es the woring of all the departments. &t loos on all aspects of a college, its students, faculties, Departments, mars and other co 'curricular activities. CMS is the easiest way to manage all functionalities of a college. &t is a value%added service offered by ()*+-, which facilitates colleges to maintain the functionalityrelated to college employees and their students. College Management Software is a simple yet powerful one oint integratedplatform that connects all the various departments of an institution liedministration, ttendance, Staff details and many more speciali$ed modules.
O4E'4IE OF CASE ST6(7: The system C/--)0) DTBS) can be used to manage the data ofall type of educational institutes. &t will support both stand alone and also networing environment.
Fe$tre# o 5ro5o#ed ##te* $re:1. Student dmission 2. Student 3ee Management 4. Student ttendance Management 5. Staff ttendance Management 6. Staff payroll Management 7. -ibrary Management 8. *ser management 9. :ostel management ;. Security Management 1<. :uman resource
T9e *$in *od%e# invo%ved in t9i###te* $re: 1. -ogin 2. 3orms 4. +eports 5. =indowModule wise description
LOIN:-ogin module is used to chec whether the user is an authori$ed person to use the systemor not. 3or this the user should give the correct user name and password.
The different types of users are>%
1. dmin 2. Student 4. 3aculty 5.?arents
Ad*in:-&t heads the entire college management system and has the responsibility for setting up and maintaining the system administrator.&t may be member of an information technology department. &t is responsible for following things>% 1. *ser access 2. Maintaining system 4. @erify that system wors properly or not. 5. +egular update as new version of /S is easily available. 6. Create file system 7. Create a bacup and recover for the software 8. Set up security policies for users.
Stdent: They have login facility to access all the information related to their courses,mars,fee details.=ith the help of a userid and password they can ensure all the relevant information.They are assigned with an id that has all the data stored for a particular facility.
E*5%oee: They are of two types>% 1. Teaching staff *nder teaching staff faulty are there which are of different departments. &t includes :/D, dean, :/S as well. 2. (on%teaching *nder non%teaching staff%security worers, cleanliness, department, employees from other department came. They also have assigned one &D. This id contains all the information related to them.
For*# This module consists of the following sub modules>% 1. Student +egistration 3orm 2. Student 3ee 3orm 4. Student Mars 3orm 5. Student &D 3orm 6. )mployee Detail 3orm 7. )mployee Salary 3orm 8. )mployee &D 3orm
9. Course Detail 3orm
%AThe Student +egistration 3orm is designed for registering the new student’s details andcourse details. %AThe Student 3ee 3orm is used to enter the student’s fee details.The Student Mars 3orm is designed for %ASubmitting the semester or eam mars of thestudents for a particular course or an individual student %AThe Student &d 3orm is used to create the identity number for each student for different course. %AThe )mployee details form is designed for entering the staff details and other relevant details. %AThe )mployee &d used to create the identity number for each student for different course. %AThe salary form is used to derive the salary for employees. %AThe Course details form is designed for entering the different course available in the campus and other relevant details.
'EO'TS:ll the above mentioned data are stored in the bac end and can be retrieved as reports with filtering options. The 3ollowing are the reports can be taen from this system>% 1. Student +eport 2. )mployee +eport 4. Course Detail +eport 5. 3ee Detail +eport
'IE Fe$tre# o Or Co%%e)e $n$)e*ent S#te* Sot!$re:
Si*5%e $nd Eicient 6#er Inter$ce (e#i)n
sall ey elementstaen care of while designing and this maes it *ser 3riendly.
T9e #ot!$re i# #ecre $nd re%i$b%e
s all maor threats areconsidered and all preventive measures are taen in the software.
S*$rt 6#er $n$)e*ent
s it assigns different privileges to thedifferent users. (ot only this, you can manually set theparameters for different users.
ide '$n)e o od%e#
eepingin mind all the reuirements withseparate login for students, teachers, parents and administrator.
T9e #ot!$re i# F%e;ib%e $nd C#to*i,$b%e
s per your needs.
Lo! co#t o o!ner#9i5.
ENTITIES AN( TABLES FO' COLLEE (ATABASE:-
Entit Attribte# s we define different entities, we find that we are digging deeper and deeper into details. &t is also a good idea to eep trac of entity attributes as we eep researching the business model. )ntity attributes become table columns when the actual database tables are created. 3or eample, 1.)nrolment E&D cademicFear Term> Spring#Summer#3all Section&D Student&D Date)nrolled Midterm0rade 3inal0rade 2. Student E&D 3irst (ame -ast (ame College &D )mail
4. &nstructor E&D
College &D 3irst (ame -ast (ame +an> &G&nstructor, ?G?rofessor CGDept. Chair Type> 3G3ull Time, Gdunct Department &D
5. Department E&D (ame Chair &D> Department ChairHs &nstructor &D Contact ?hone Contact )mail
6. Course E&D (ame Description Type> /nline#-ecture#:ybrid Term> Spring#Summer#3all 7. Section E&D (ame Course &D
Schedule &D &nstructor &D +oom =e spent some time analysing ICollegeI database and discovered a number of useful relationships between the tables. &n practice, these relationships are established by creation of ?rimary eys in each table, and 3oreigneys pointing from one table toanother.
The following diagram shows some of these relationships on my computer>
Fi)1.1: F i)1.1:-TABLES TABLES S
(This e"ample shos primary and foreign keys that e"ist in my on copy of )ccess *atabase as a result of testing S+ statements from pre'iously posted hand outs! -ou can see your on diagram by clicking Database ools, Relationshi!s! .y default, the tables aren/t aligned nicely, but you can 'isually rearrange them any ay you like! •
Primary keys are marked by the key icons ne"t to each I* column in e'ery table!
•
onnecting lines sho relationships beteen the tables! The lines ha'e symbols ne"t to the foreign "ey columns! The other side is marked by digit 1, indicating relation to !rimary "ey in another table!
•
4ecall that during the database modelling stage e referred to each table as entity! entity ! 5ence the name of the abo'e diagram 6 Entity Relationsh Relationship ip
•
The meaning of 1 and ( is like digit 7 on its side, indicating indicating infinity is that that prim primar ary8 y8fo fore reig ign n key key pair pairss form form one-to-many relatio relationshi nships! ps! 9ach 9ach primary key column can contain only one unique valueidentifying value identifying one ro in the table! :n the other hand, there can be many non-unique values stored values stored in the foreign key column of another table! Those are the pointers back to the table ros containing the primary key!
onne onnect cting ing colum columns ns shoul should d ha'e ha'e 'alue 'aluess that that match match or compar comparee easil easily, y, representing the same or similar data in each of the tables participating in the the #oi #oin! ;or ;or e"am "ample, the *epar epartm tmeent!I* t!I* column umn matc match hes the Instructor!*epartmentI* column<
The folloing S99T S99T =uery =uery prints instructor names and their departments< _____________________________________________________________________ ______________________________________________________ SELECT_Example
S99T Instructor!;irst>ame, Instructor!ast>ame,
*epartment!>ame FROM Instructor, *epartment WHERE
Instructor!*epartmentI* ? *epartment!I* The result may look like this<
ENTIT7 'ELATIONS 1./ne to /ne 2./netoMany 4.Many to /ne
Different boos and design tools use different formats, symbols, and notations to describe database design.
+elations are the bonds between entities. They tell about certain rules that apply if you want to insert, update or delete occurrences JrowsK from what later becomes a table. +elations also tell us much about the logic connections between entities. &n the physical design of the database, relationships become constraints that govern how you are allowed to manipulate data in different tables. The relationships are there to enforce referential integrity.. &n my diagrams, capitali$ed words Jsuch as StudentK identify entities.
ri*itive %ine# $nd $rro!# #9o! re%$tion#9i5# bet!een entitie#. T9e re%$tion#9i5# $%#o 9$ve de#cri5tion#. 3or eample, the diagram Student %%%enrolls%%%%%A Course Suggeststhat IStudent enrolls in a course.I :ere, the Student and Course are eamples of entities, and IenrollsI is the name of the relationship between them.
lternatively, Course %%%%%%has%%%%%%A Students Suggeststhe same thing> ICourse has Lenrolled students.I )ither way, the Course and the Student are related.
There is a significant issue that maes the above relationships between students and courses more comple>% •
ny student can be enrolled in multiple courses
•
ny course can have multiple students.
Course %%%%%%has%%%%%%A Section N%%%%%enroll%%%%%% Students ICourse has sectionsI IStudents enroll in sectionsI (ote that the arrows suggest the direction in which the relationships apply. Direction helps to translate the diagram into plain )nglish sentences.The database table storing course sections should have a column for the &nstructor. Since course can have multiple sections, different sections of the same course can be taught by different instructors> Section %%%%%%has%%%%%%A &nstructor
#ig $.%&'R iagram of stuent atabase
NO'ALI=ATION Database normali$ation is the process of organi$ing the fields and tables of a relational database to minimi$e redundancy and dependency. (ormali$ation usually involves dividing large tables into smaller Jand less redundantK tables and defining relationships between them. The obective is to isolate data so that additions, deletions, and modifications of a field can be made in ust one table and then propagated through the rest of the database using the defined relationships.
Nor*$%i,$tion 9e%5# e%i*in$te rednd$ncie# $nd incon#i#tencie# in t$b%e d$t$. &t is the process of reducing tables to a set of columns where all the non%ey columns depend on the primary ey column. &f this is not the case, the data can become inconsistent during updates.
This section briefly reviews the rules for first, second, third, and fourth normal form>% •
•
•
•
3irst> % t each row and column position in the table, there eists one value, never a set of values. Second>%)ach column that is not part of the ey is dependent upon the ey. Third>%)ach non%ey column is independent of other non% ey columns, and is dependent only upon the ey. 3ourth>%(o row contains two or more independent multi% valued facts about an entity.
t this early database design stage, when we say IStudents enroll in sectionsI,
Section N%%%%%enroll%%%%%% Students Does it mean that the student record must eep a list of every section where the student is currently enrolledO Maybe. But how do we eep trac of sections#students from previous semestersO
The answer is> we need another table to mae connection between the academic year, the student, and the section in which the student is enrolled. ?otential name for such new entity could be )nrolment. The diagram becomes
Section N%%%%%%has%%%%%% )nrolment %%%%%%has%%%%%%A Students Discovering the need for )nrolment table is a step towards database normali$ation.
(ormali$ation procedures are design changes that follow guidelines based on widely%accepted database organi$ation standards. Maing your tables match these standards is called normali$ation.
T5ic$% nor*$%i,$tion #te5# inc%de:
1. Splitting tables into two or more tables with fewer columns and well%defined primary ey. 2. +educing number of data redundancies within your tables.
CONCL6SION:This case study incorporate all acti'ities re=uired to access college database!It pro'ides all necessary information to the management as ell ell as the student ith the use of this system! The user can simply sit in front of the system and login using id and passord to monitor each and e'ery =uery related to college ithout any physical mo'ement of file!*atabase can ser'ice the student faculty re=uests best in time! The system pro'ides =uickly and 'aluable information!The modules ha'e been integrated for effecti'e use for future forecasting and for the current need! ) self6dri'en module in the proposed system ill accomplish the automated tasks such as<6email alerts, sms alerts and notification to the administrator etc!
SCOE OF F6'T
'EFE'ENCES:1! http<88!c6 #ump!com8bcc8common8Talk8S+8S+@20@*.design8S+@20@*.design!html #ump!com8bcc8common8Talk8S+ 8S+@20@*.design8S+@20@*.design!html 2!http<88forums!de'shed!com8database6management6AB8database6design6for6 college6dept6BAC12!html 3! !slideshare!net