Database Management System (E1UA204C) Project Report On CAMPUS MANAGEMENT SYSTEM BACHELOR OF COMPUTER APPLICATION GALGOTIAS UNIVERSITY (Gtolshed uoerGlsais Univesity Uter Pradesh At No, 142011) Session 2023-24 Submitted Submitted By to KHALID Dr. Sunita Tomar 23SCSE1470004 Associate professor SCSE SCHOOL OF COMPUTER APPLICATIONS AND TECHNOLOGY DEPARTMENT OF COMPUTER SCIENCE AND ENGINEERING GALGOTIAS UNIVERSITY, GREATER NOIDA INDIA June, 2024SCHOOL OF COMPUTING SCIENCE AND ENGINEERING GALGOTIAS UNIVERSITY, GREATER NOIDA CANDIDATE’S DECLARATION We hereby certify that the work which is being presented in the project, entitled “CAMPUS MANAGEMENT SYSTEM? in partial fulfillment of the requirements for the award of the BCA (Bachelor of Computer Application) submitted in the School of Computer Applications and Technology of Galgotias University, Greater Noida, is an original work carried out during the period of February, 2024 to June 2024, under the supervision of Dr. Sunita Tomar, Department of Computer Science and Engineering, of School of Computer Applications and Technology, Galgotias University, Greater Noida The matter presented in the thesis/project/dissertation has not been submitted by me/us for the award of any other degree of this or any other places, KHALID (23SCSE1470004) This is to certify that the above statement made by the candidates is correct to the best of my knowledge. Dr. Sunita Tomar Associate ProfessorCERTIFICATE This is to certify that Project Report entitled “CAMPUS MANAGEMENT SYSTEM.” which is submitted by” Khalid partial fulfillment of the requirement for the award of degree BCA in Department of Computer Science School of Computer Applications and Technology, Galgotias University, Greater Noida, India is a record of the candidate own work carried out by him/them under my supervision. The matter embodied in this thesis is original and has not been submitted for the award of any other degree Signature of Examiner(s) Signature of Supervisor(s) Signature of Program Ch: Date: June, 2024 Place: Greater NoidaACKNOWLEDGEMENTS Lam grateful for the guidance and support I received during this DBMS project on campus management. Dr. Sunita Tomar insighifial feedback and encouragement were invaluable in helping me develop a strong understanding of database design and implementation, I would also like to thank my fellow BCA classmates for the collaborative dis Knowledge, which significantly contributed to the project's direction. Khalid (23SCSE1470004) Silicon Multiple Campus CA 2ndSemacterABSTRACT This report describes the implementation of a Database Management System for Galgotia Multiple ‘Campus. It was developed for Storing, Managing, and Updating the Data that the Campus generates. This report mainly includes the background and development process of the project. The main steps in the development process are identifying the Entities and the Relationship among them and presenting them in graphical form with an ER Diagram. It also includes some of the SOL queries used in the implementation of the MySQL Database. This project can be useful for Galgotias and other similar institutions.Table of Contents Introduction Uses of the Project .......:ssesenessseees Background DBMS for Galgotia Multiple Campus ..........00--0000 ‘Addressing the Requirements ER Diagram ........eeseeeeeseee Mapping ER Model to Relational Model Structured Query Language(SQl) . Table Creation .. Data Insertion ... Summary . 12 18 25 26 29 45Background The project uses the following technologies. DBMS: MySQL MySQL is an open-source relational database management system (RDBMS). We have selected MySQL because of the following reasons: + MySQL supports the Standard SQL(Structured Query Language). + Itis easy to use and easily available to download and install + MySQL clients like phpMyAdmin and MySQL Workbench are easily available for installation. They provide a great user experience. + Itis portable to other systems. + There are no Licensing fees. So, we can use it free of cost. * Being an Open Source software, it has a huge community to support if you un into issues. XAMPP XAMPP is a stack of software that provides easy access to MySQL. phpMyAdmin phpMyAdmin is an open-source client for MySQL. It provides the functionality of operating and managing Databases in MySQL. Books For understanding the theory of DBMS and SQL, we took the following books for reference. 1. Saud, A.S and Saud, B.S (2073). Introduction to Database System. Kriti Books Publishers & Distributors Pvt. Ltd., 2nd ed 2. Abraham Silberchatz, Henry F. Korth, S.Sudarshan; Database system Concepts. McGraw Hill, 6th edDBMS for Galgotias Multiple Campus ThederelgmentfthShemafor RBIS arte withthe En Retonsip Mode\ER Mol, Then the R Model wes mapped the elon Model Ent RelatonshipModel Tet eta Mods ue ode te struc othe Dataset cnt he ll 1 Enesand Ents «Treaties ths ites + latistipsanongthese nes ERDiagram Thecorefthe ER Modelisthe ny Reltostp Diagram ER Diagram) 2 rapt representation ofthe Rode is ikeatueprnt ofa database, ‘Attributes Addressing the Requirements Thetis stent Motel to let beEnttesothe Steno Organization that we are interested, Ents oft Mile Campus Theenttyisnyre-word objethainginenendet extec. An enty canbe ‘peso, place, or concept. The min Enis associated with the Gagoias Mute Campus ae 1 Department 2. Pogian 3, Suet 4. lever 5. bet ‘The Properties ofan entity are called Atributes. The attributes) which ean Uniquely ieriy the Entity calles Primary Key. Themain Atibutes ofall the ove Entities a listed inthe table Below. The Primary Keys are underine. entity Atributes enactment id oo Pou ‘otal fees student ‘Contact number Firstname ‘Gender Emalladdrese LecturerCountry City Street Ward_no Subject Subject Id Subject_Code Subject_name Credit_hours Total_Preiods ER Diagram Not: Te grams shun stepwise tome cay tounderstod, Youcen ety putthe fal R gro your rjc ERDiagram: Step Dern ace PoyER Diagram: Note: Some Attributes of Student ond Lecturer entities are not present in the ER Diagram. They have been excluded to avoid the over-complexity of the ER Diagram.Mapping ER Model to Relational Model(Tables) Step 1: Department and Program Tereis Onto Many Relationship between Department and Program, So, we use Department_Mdas a Foreign Key in the Program table. Step 2: Lecturer ‘Therisa'MarytoManReltionshipbetuee the Deparentand Leto, ve wil reat new tbl for Works Fo Reetonstip. Ao, we wil cates separate table for ona nunberasit has mig vaues. Lecturer Works For Departent Lecturer EM Devarment EKLecturer_Contact_number Student Step 3; Student Theresa ‘One to Many Relationship between Program and Student. So, we use Program_id as a Foreign Key in the Student table, Also, we will create a separate table for Contact_number as it has multiple values, Student Contact numberStep 4: Subject ‘Thereis ‘Many to Many Relationship between Program and Subject. Thereis aoa Many Many Raton betuen Lecturer and ube, veil creitetwo separates Seis ad Teaches Rts Lecter Teas Sub Lecuer Wf Subject Progam Has Sujet Proxemlde] | ub i) Mapping ER Model to Relational Model: Final Relation Schemas Department Deoatment Id] Department name) HOD E ret Lecurer Wot, For Department ectuer Dexztment Lecturer Contact numberStudent Structured Query Language(SQl) \Wehave used the MySQL implementation of SOL for our project Themain OL command that we sed are: DDL- Data Definition Language In DOL, we have sed CREATE, ALTE, TRUNCATE, DROP and RENAME commands, DML- Data Manipulation Language Student Contact number In DOL, wehave used INSERT, UPDATE, DELETE, and SELECT commands, Subjet Lecture Teaches Subject ect FKL subi Progam Has Sujet rogram A) subiTable Creation Dezartmen Table CRATE TBE Depataent ( Departaent_I¢ int BOD WL AMTO_IOCRENENT eRNARE FEY, Departzet rane VARCHR(10) 10? NUL, 1D ‘VARCHAR (100) WOT ULL ii ime pean aes a es 1 Deprtet) infl) Noone AUTO NOREEN 2 epee hi) tin Yo tne 3 m0 eh tin Yo tne a Program Table CREATE TABLE Progran ( ogenié int WT LL AMID, Progran nane VARCHAR (100) WOE NLL, Ve ofytrs §——oaenau(2, 2) sor mt, Total fees aCIAL(10, 2) NOR NULL, Progra ccrdinator VAECHRIO0) HOE MEL, Department td int Bo WL, REIGN EY (Departent Id) FEFEROCESDepartnen Departent: 1) RE Pr |e UL AR RE I, Pe Let yrs ML, kes EL, CL, rg ot Ar EN Bvt re) + ane ‘pe Colton atts at Det a 1 Progam) itt Na fre ATO BERET 2 Progam sane ce tin fa te 3 Neots it) Nove 4 ales ds Yo tae 5 ogra contr vac). in No tae 6 pane) int) No te8 ‘AegurerTable Name ype coon abs les Ee Chen SE aster 1 Lact) int) Mo tine aT NCREMENT ( ected in WE LAY HORNENT FRUARE HI, 2 Fister) a a Ye First_nane ‘VARCHAR (100) NOT NULL, 3 Last name ata) ufbin No None last ame TCHR SOT, gi 4 Gender atcha) tia No hone Gender ‘ARCHAR(15) NOT NULL, nail address VARCHAR(320) HOT NULL, 5 Email adress varchar(320} ut8_bin No None Ld OAs WOE TLL, 5 DOB tte No None Designation VARCHR(LOO) WOT NULL, 7 Degen ru) Non Country VARCHAR(LOO) Wor NULL, City ee Wedet he J ‘VARCHAR (100) NOP MULL, 8 Country ‘vaschay(200) ut _bin No None Street ‘VARCHAR(100) NOT MULL, 2 oy varchar) in . Wardno int WOT NL : 1D Stet vaca tn Ye fe 11 Wal n0 i) Noe Net cu re LAM DO ON, Fae WA ‘ne OL ee MA), res WOT uae cy WG), A Se GN WL, A(ecurer Wors For Degartment Teble (QUE EE etre fo pera ( tesa 1d ist 2, Apartado tL, PROMRY HEY letaret 1, Departs. ELON EY ater) REFEREES ater acta, ORION EY epee) RDN! Deptt Depart 1) EM cee wf ea Let 2 TAG feat, I ee BR oe, REL eee WEE beige | # Name Tipe Colton Abts Nall Det Ext 1 Leowrer id) int) No None 2 Deprnen, 9) i) No tare ‘Lecturer Contact number Table CREATE TABLE Lecturer Contact_munber ( lecturer 14 int NUT NILL, Contact_nunber VARCHAR(15) NOT NULL, PROGRY FEY (Lecturer_14, Contact_number) , FOREIGN HEY (Lecturer Id) REFEREICES Lecturer (Lecturer 16) h 5S] ME eee oa ter ce A ML eae I) L, RT st cert, RENT a1) RNG tac # Kane Type Colin atributes Null Deaut Eta 1 Lecturer 9 ina) No None 2 Contact mumber vaca) tin No Nonea Student Table CREATE TABLE Student ( Student_Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, Roll_no int HoT WL, Firstname YRRCIDR(LOO) NOP NOLL, last name VARCHAR(LOO) NOT NOLL, Gender —_YARCHAR(1S) NOT ULL, ‘nail_adéross VARCHAR 220) NOD HULL, ‘DOB DATE WOT MULL, Country VARCHAR(100) NOT NULL, city ‘VARCHAR (100) NOT NULL, ‘Street ‘VARCHAR (100) NOT NULL, Ward.no int NOT MUL, Progra 1d int NO? WILL, FORELGI REY (Progran 1d) REFERENCES Program rogran 1d) (Ru et WL EE Nal WT WL, Fit THT t,t oe AT AL, ce OM WL, a es a eA yi TAL Lt ‘Wa tL, we Se rae Paya | # Name pe Colton tits Null Detat Exta 1 Sdn i) 2 Roto i) Fist nme — vache) of. tin 4 Lastname va) tin 5 Gender vhs) lin 6 Ema ates vat) tin 7 oe ae 8 Coury vat) tin 9 Gly vat) win 1 Sie tr in 1 Wad mot) 12 Program 9 in) 7 one AUTO, NCREMEATStudent Contact number Tate CHEATS TALE Student Contact, ober ( Student 16 int OT WL, Contact amber YCHAR(LS) XO? MLL, ‘RARY XT (student, 16, contact zmbe), FOREIGN REY (Student) RFERECES stadentStadent_ 1d) Ae er ee TL, a a! A, Gib oe ne, DE Sh EES Sat 7 # Name ‘Type Collation Atributes Null Default Extra 1 Sent) it) No Noe 2 ona number» vaca) fh tn No None Subject Table CREATE TABLE Subject ( Subject Id int WOT NULL ADTO_INCRBENT PRIMARY 12Y, Subject Code VARCHAR(LOO) NOT NUL, Subject nane VARCHAR(1OO) NO? NULL, Credit hours int WOT NULL, Total Preiods int YO? WL a # Name Type Colton atitues Null Deft Extra 1 Site it) Note AUTO, NCREMENT 2 Sujet Cade vat) win No tne 4) Sabet name varchar) win No tne 4 Cet hous.) Notre 5: Tl Pids in) No Nenea Lecturer Teaches Subject Table CREATE TABLE Lecturer Teaches Subject ( ecturer_Id int NOT NULL Subject Id int NOP MULL PRDORY KEY (Lecturer Id, Subject 16) FOREIGN KEY (Lecturer Id) REFERENCES Lactorer (Lecturer 1d) FOREIGK KEY (Subject_1¢) REFERENCES subject (subect_Id) t 1s WME cer Toes Se cer 1 ATL, ae jet), FREE? te FB crt 110) RDA Sete | # Name ‘Type Collin atbues null Deft Extra 1 Lecturer Mt int) No None 2 Subject > int) No-one Program Has Subject Table CREATE THRE Program Has Sjest ( Progran_Id int OD WL Subject Id int NO WILL, FRDARY FEY (Program Id, Subject 1) FOREIGN FBY (Progran 1d) sErtEEs Progran(Progran 1d) FOREIGN HEY (Subject. Td) semncas Subject Subject. Td) NF Pps et oy 8 ML et ML, 5, Saft, ELA Pea TS ya, DAE je ISR Sane | # Name ‘ype Colon Atrinies Nl Dead Extra 1 Programid )in(t) No None 2 Subject) int) No NoneFinal Tables Table o 0 Department () Lecturer (Lecturer Contact, number Lecturer Teaches Subject o Lecturer Works For Department oa Progra () Progam, Has subject 0 Student Student Contact_number = © Subject 10 tables Data Insertion {nsertngin Department Table INSERT INTO ‘Department’ (‘Department_name*, “HOD*) VALIES ——("Science', 'Alexandra') ("Cormerce' ,'Elizabeth') ('Arts", 'Isaac') DST IMT earn’ Drie rane’, “WO') VLEs, “Mca, Teamerce!, “lizabeth'), Arts, sac!) SBLECT * FROM “Department Department Id Department name HOD 1 Science ‘Alexandra 2 Commerce Eleabeth 3 ats Isaac41 Summary We developed a Database Management System for Galgotias Multiple Campus. After identifying the main Entities and the Relationship among them, we created an ER Model with the help of an ER Diagram. Then we mapped the ER Model to the Relational Model Eventually, we used the technologies like MySQL, XAMPP, PhpMyAdmin, etc to implement the DBMS. We created 10 tables in MySQL. Out of those 10 tables, 5 belonged directly to the entities whereas the remaining 5 were used to manage the Relationship among the entities. Now the Campus can use the DBMS for Storing, Managing, and Updating their data on a regular basis. This will help them get rid of the Data Duplication, Inconsistency and many other issues they were facing due to the use of traditional Methods. The two main limitations of this project are it lacks relations for Library and Fees/Billing management. This limitation was caused due to the time constraint. In the future, we plan to enhance the DBMS so that it can properly handle the data from the college's Library and Accounts Sections.