CMSC 661, Fall 1999, Principal of Database Systems
Contents
1. Help manual and system versions A4 1 Review applicant Faculty can review applicant basic education score and recommendations letters information based on applicant SSN However faculty can only query application information they can not modify such information faculty gt review gt Applicant basic info education info score info recommendation letters 52 CMSC 661 Fall 1999 Project Final Report 4 Query an applicant s basic info ShiangTang ssn 901008473 sex x z 1970 11 06 gt gt gt Asian E Chinese gt gt gt one es as E Faculty can make modify view their own decision to the applicant faculty gt review gt your decision gt Make new decision modify decision view decision Make new decision 53 CMSC 661 Fall 1999 Project Final Report T n ee Your Decision after viewing an application For faculty they can grant admission to applicant if they are admission committee faculty gt admission gt decision TI a sz Are you a Committee Member M1E3 If you are the admission committee member reenter your user id and password then continue 54 CMSC 661 Fall 1999 Project Final Report 4 New admission O x SSN 1 Date Decision Admit v Deny reason NA w Provisions Assigned Advisor Dr Ebert v TestiWavied GRE Sub v Prereguired Courses Nia v And Nia v And Nia v Be supported W v Type Nia2. date Ph D candiate date date expect graduation date number 5 date forssn references person forssn on delete cascade acadvisor references faculty name resadvisor references faculty name subject_type as object varchar2 30 date number varchar2 10 18 CMSC 661 Fall 1999 Project Final Report create type subject array as varray 8 of subject type varchar2 9 primary key subject array create table compexam forssn subjects result foreign key varchar2 10 forssn references student forssn on delete cascade create type committee_array as varray 6 of varchar2 30 create table phdprelim forssn testdate title abstract grade committee primary key foreign key varchar2 9 date varchar 2 80 not null varchar2 256 varchar2 10 committee_array forssn testdate forssn references student forssn on delete cascade create table thesis forssn defdate degree title abstract result committee primary key foreign key varchar2 9 date varchar2 10 not null varchar2 80 not null varchar2 256 varchar2 10 committee_array forssn defdate forssn references student forssn on delete cascade create table msproject forssn appvdate title abstract committee primary key foreign key varchar2 9 date varchar 2 80 not
3. number 4 char varchar 2 30 varchar2 10 of ta type create table gradassist forssn startdate asstype step enddate account remiscredit stipend varchar2 9 date varchar2 10 number 1 date varchar2 20 number 2 number 7 2 not null not null not null remission credits 20 CMSC 661 Fall 1999 Project Final Report health varchar2 20 supervisor varchar2 30 evaluation varchar2 80 duty varchar2 80 office varchar2 20 phone varchar2 10 tatestscore char 2 assignment ta array disdate date dismiss date disreason varchar2 80 primary key forssn startdate foreign key forssn references student forssn on delete cascade create table graduation forssn varchar2 9 primary key degree varchar2 10 not null awdate date not null company varchar2 80 startdate date foreign key forssn references student forssn on delete cascade create table dismission forssn varchar2 9 primary key disdate date not null reasons varchar2 80 foreign key forssn references student forssn on delete cascade creat index person_idx on person ssn create index applicant_idx on applicant forssn create index student_idx on student forssn 21 CMSC 661 Fall 1999 Project Final Report 3 2 Issues to highlight 1 JDBC connection Since building
4. Project Final Report Si When students finish MS project you need 901008473 Thesis student gt input gt thesis Si Add a new thesis record you need pol vene us orage Pass brevet x Dresen e Dr Ebert v Dr Ebert Award student gt input gt award 58 CMSC 661 Fall 1999 Project Final Report e Maintain awards info jot for 1990 S Add a new record 901008473 The students can only input their own information A5 2 Query student information Students can also query the above information Comp exams student gt Query gt Comp exam 59 CMSC 661 Fall 1999 Project Final Report ee Query a student s Comp Exam record 901008473 CMSC611 E 2000 01 01 100 Pass Ph D level E Ph D preliminary student gt guery gt Ph D preliminary 24 View a Ph D student s preliminary exam records Dr kamane E or 123 M MS project student gt guery gt MS project 60 CMSC 661 Fall 1999 Project Final Report SAT a student s MS project records Thesis student gt guery gt thesis K View a student s thesis records Ki T O H s E Pass EI or Eben JE or Evet E Award student gt guery gt award 61 gt s Query a person s awards st _ Publications student gt query gt publication a H KS Ouery
5. SSN test date score waived Research keywords Has4 SSN keywords CMSC 661 Fall 1999 Project Final Report Interests Interest Has7 SSN Interest Publication item title authors type month year citation Publish SSN item Award item name date description Got SSN item Applicant SSN received_date program degree_sought semester semester_year financial support reguested financial support source status last update date dept send date grad send date int send date notes Faculty Name SSN Recommendation item review date recommendation matching faculty reguired courses comments Review SSN item Name Admission Decision item admission date decision deny reason provisions assigned advisor waived tests assistantship assistantship is an object type which has awarded type amount duration Decidel SSN item Applicant Decision item decision date decision expect arrive date defer reguent notes Decide2 SSN item Student SSN program admitted date degree sought academci advisor research advisor status phd candicacy date expected grad date xerox no last update date Comp Exam subjects date Score item score grade Taken SSN item subjects date Preliminary Exam item test date title abstract grade committee CMSC 661 Fall 1999 Project Final Report committee is an array of object
6. Student Manage the student related information After the applicant is accepted and enrolled at UMBC he she will become a student the functions including modify guery student information courses grade transfer courses MS project thesis Ph D preliminary and comprehensive examinations Assistant Manage the graduate assistant related information Other functions including student graduation dismission awards publications and semester based course schedule Help Help information and version and team member of the project A3 1 Maintain Basic Information To maintain institution table staff gt file gt manage DB gt Institution Maintain Institution Table To maintain research keyword staff gt file gt manage DB gt ResKeyWord 33 CMSC 661 Fall 1999 Project Final Report Maintain research keywords 0 x Add a new research keyword Keyword Are you sure N v Delete Select one ALGORITHMS v Are you sure N v OK Cancel To maintain course information staff gt file gt manage DB gt UMBC course 2 Maintain course info Add a new course Course Number CMSC661 Course Name Principals of Database Systems OK Cancel To maintain prerequisite courses information staff gt file gt manage DB gt prerequisite Maintain prerequisite courses table 0 x Add a new prereguisite course Course Number CMSC Name Are you sure N v Delet
7. add an entry to comprehensive table Student forms his her advisory examination or defense committees update the committee information Student takes Ph D preliminary exam add an entry to the Ph D preliminary table Student defends thesis update the thesis table Student completes M S project update the MS Project table Student is admitted into Ph D candidacy add an entry to Ph D candidacy table Student receives an award add an entry to award table Student is awarded an assistantship add an entry to assistantship table Student is dismissed from his her assistantship update assistantship table and student status Student is dismissed from the program add an entry to dismissed student table Student graduates add an entry to graduated student table 2 3 E R Diagram In this diagram each entities and associated relationships are given For attributes they are listed in 2 4 CMSC 661 Fall 1999 Project Final Report Person Personal n lt gt lt gt Interests Institution Applicant Decide 1 j Decide2 Decision Decision Graduated Student lt gt ESSEN KO ESS Score Comp Exam Proposal Preliminary Exam Defense Thesis Personal Info CSEE Student Assistantship CMSC 661 Fall 1999 Project Final Report Dismissed Student Courses Faculty m lt gt lt a gt gt T_Courses MS_Project CMSC 661 Fall 1999 Project Final R
8. test date title abstract grade committee We define a VARRAY of object type name role for committee Thesis student SSN defended date degree title abstract result committee 11 CMSC 661 Fall 1999 Project Final Report MS project student SSN approved date title abstract committee UMBC Course cno cname This table stores course dictionary Course course number semester year course name instructor Course schedule for each semester Transfer Course course number SSN institution grade Attended courses student SSN course number semester year grade transfer course transfer institution Graduate Assistant Assistant SSN start date assistant type step level duration account remission credits stipend health benefit supervisor evaluation duty office phone TA test score TA assignment dismissed date dismissed reason TA assignment is an array of an object type course section level instructor evaluation Graduated student student SSN degree awarded date company startdate Dismissed student student SSN dismissa date reasons 12 CMSC 661 Fall 1999 Project Final Report 2 6 Issues 1 GUI design is a time consuming task We plan to utilize Java JFC Swing in our application the first we want to implement the independent Java application which can run any Java 2 platform 2 Choose a proper architecture a 2 tier or 3 tier For a 2 tier mo
9. Project Final Report Si When students finish Ph D Preliminary exam you need You can add modify view student comprehensive exam staff gt student gt Comp Exam ee Add a new Comp Exam record oneco mi Pass Ph D level v ok gt ean gt cancel A3 4 Manage Graduate Assistant You can add modify guery assistant s information staff gt assistant gt assistant info Add information 46 CMSC 661 Fall 1999 Project Final Report e Add a new assistantship record Full Assistantship Academic Year Stipeng 5000 E K To modify an assistant information you need to input SSN first then do it accordingly Si Modify an assistantship record n PUSA n 19980101 Drewen x 47 CMSC 661 Fall 1999 Project Final Report For TA management staff gt assistant gt TA management Add a new TA assignment Ei Add a new TA assignment record l O x SSN Course cmsce11 v Section Semeter Spring v Year Level 20HMWeek v Instructor or Ebert v Evaluation score Evaluation comments OK Clean Cancel To modify a TA assignment you need to input the SSN then do it accordingly E Modify a TA s assignment records SSN 901008473 Do you want to modify N v Course Section 1010 Semeter Fali v Year 1999 Level SHMWeek v Instructor Dr 123 v Evalua
10. a new application add new personal information into the database Update personal information when supporting materials are arriving When all application materials are received update the status Application is sent to reviewer grant appropriate user rights to the reviewer Reviewer makes a recommendation for the applicant update the reviewer s table Application is received from reviewer revoke the user rights to access the application Application is sent to admissions committee grant appropriate user rights to admission committee Application is received from admissions committee revoke the user rights to access the application Committee makes a decision update the applicant status Letters are sent departmental graduate school international office update the dates respectively Applicant replies update the applicant information Admissions committee approves defer reguest if the defer reguest is received by the department update applicant status Admitted student enrolls in a program update the student s information A new row is inserted in the student table Student change status update the student information 15 16 17 18 19 20 24 22 23 24 25 26 2T CMSC 661 Fall 1999 Project Final Report Student takes classes update the student and courses relation information Student changes academic research advisors update the student information Student takes comprehensive exams
11. grade information staff gt student gt course grade info gt add modify view Add 2 After a student got course grade you need cmsc611 Spring lt Modify after input the SSN you can modify it You can also use prev and next button for other course grades 43 CMSC 661 Fall 1999 Project Final Report Modify a student s course info you need SSN 901008473 Course NO 91394274 Grade A v Semester Fali v Year 1999 OK Cancel lt lt lt Prev Next gt gt You can also add modify view transferred courses for the student staff gt applicant gt transferred course Add 2 After a student transfers a course grade you need loj x ssn Course NO cmsce11 v Semester spring v vear Grade A v Transferred from umec v OK Cancel Modify you need to input the SSN first Modify a student s transferred course info you need O x ssn Course NO KANELA Semester Spring v vear Grade A v Transferred From umec v OK Cancel lt lt lt Prev Next gt gt gt You can add modify view student s MS project staff gt student gt MS project 44 CMSC 661 Fall 1999 Project Final Report Si When students finish MS project you need You can add modify view student s Ph D preliminary information staff gt student gt Ph D preliminary 45 CMSC 661 Fall 1999
12. the difference is that we use the method getArray of ResultSet Using Oracle stored procedure or Java class To maintain integrity of the database and perform appropriate actions on database update shall we use Oracle supplied trigger or Java class At this stage we only use Oracle supplied cascade update or delete trigger most of database updates are realized by Java class through JDBC For example to decide whether the applicants status is complete there is 3 ways to do this First use Java code to search all corresponding tables check relevant attributes and get the answer The second way is to use Oracle stored procedure let the user call this procedure or make it as a trigger invoked by a special event The third way is to do it manually let the user manually set the applications update status In this stage we have implemented the third way and are trying to implement the first or the second based on the progress of the project 23 CMSC 661 Fall 1999 Project Final Report 4 Limitation and possible improvements We implement this project by pure Java JDBC and take advantage of the advanced features of the Oracle 8 DBMS The GSO management system can run on every platforms which have Java virtual machine JDK 1 1 and above However due to different system environment the display fonts are a little different among those system We have tested our application on Sun Solaris 2 6 JDK 1 2 SGI Irix 6 5 JDK 1 1 Wind
13. type name role Proposal SSN item Thesis item degree title abstract defended date result committee Defense SSN item MS project item approved date title abstract committee DoMS SSN item Courses course number semester year course name Teach name course number semester year Attend student SSN course number semester year grade T Courses Item courses number transfer institution grade Transfer SSN item Graduate Assistant Assistant SSN start date assistant type step level duration account remission credits stipend health benefit supervisor evaluation duty office phone TA test score TA assignment dismissed date dismissed reason TA assignment is an array of an object type course section level instructor evaluation Graduated student student SSN degree awarded date employment employment is an objec type company start date Dismissed student student SSN dismissa date reasons CMSC 661 Fall 1999 Project Final Report 2 5 Relation Schema We map the E R diagram to relational schema according to 3NF This is a revised version of report 1 Users username ssn password status The table stores user information for management Superstaff username password This table stores users who has the highest privilege to manage other users The above two tables are for administration usage Person SSN nam
14. v Amount o v Beginning Date End Date OK Clean Cancel You can also modify or query the existing admission In academic affairs sections faculty can query applicants awards publications MS project comp exams Ph D preliminary and thesis Query a person s awards O x SSN 901008473 Award Name test Award Date 1990 01 01 Description justatest gt gt or In help section it is the same as GSO staff give help information and system version 55 CMSC 661 Fall 1999 Project Final Report A5 Students The system also give students an interface to input their own record and do guery accordingly Tm 4 Gso Management System for UMBC CSEE Student There are four major sections in this part File exit the system safely Input Comp exams Ph D preliminary MS project thesis awards and publications Query Comp exams Ph D preliminary MS project thesis awards and publications Help manual and version A5 1 Input student information Comp exams student gt input gt comp exam 56 3 Add a new Comp Exam record 901008473 I Pass Ph D level ok J clean gt Cancel Ph D preliminary student gt input gt Ph D preliminary CMSC 661 Fall 1999 Project Final Report 24 When students finish Ph D Preliminary exam you need 901008473 MS project student gt input gt MS project 57 CMSC 661 Fall 1999
15. varchar2 10 varchar2 30 char forssn forssn name references person ssn on delete cascade primary key primary key primary key not null not null not null not null not null not null not null not null not null default y 14 CMSC 661 Fall 1999 Project Final Report create table institution ino name address varchar2 6 varchar2 30 address_type primary key create table education forssn degree graddate instno major minor gpa gpa_scale rcv tran rcv cert primary key foreign key foreign key varchar2 9 varchar2 10 date varchar2 6 varchar2 20 varchar2 20 varchar2 6 varchar2 6 char default n char default n forssn degree graddate forssn references person ssn on delete cascade instno references institution ino create tabl forssn name title address recv_date primary key foreign key create table gr forssn testdate verbal guant anal waived primary key foreign key create table gr forssn testdate subject score waived primary key foreign key recommendation varchar2 9 varchar2 30 varchar2 10 address_type date forssn name forssn references person ssn on delete cascade varchar2 9 date number not null number not null number not null char default n
16. 10 33 25k ViewAppOtherInfo java 12 Nov 1999 11 47 4k ViewAppOtherInfo2 java 13 Nov 1999 17 39 6k ViewAppScorelnfo java 12 Nov 1999 11 33 4k ViewAppScorelnfo2 java 12 Nov 1999 11 39 18k ViewAppStatus java 10 Nov 1999 18 34 4k ViewAppStatus2 java 10 Nov 1999 18 36 5k ViewAssistant java 28 Nov 1999 00 14 4k ViewAssistant2 java 28 Nov 1999 00 22 12k ViewAttCourses java 14 Nov 1999 04 03 4k ViewAttCourses2 java 14 Nov 1999 04 12 9k ViewAward java 12 Nov 1999 01 22 4k ViewAward2 java 13 Nov 1999 18 40 5k ViewCompExam java 26 Nov 1999 02 18 4k ViewCompExam 2 java 26 Nov 1999 02 29 6k ViewDismission java 12 Nov 1999 14 37 4k ViewDismission2 java 12 Nov 1999 19 05 3k ViewGraduation java 12 Nov 1999 11 55 4k ViewGraduation2 java 12 Nov 1999 19 04 6k ViewMSProject java 20 Nov 1999 11 05 4k ViewMSProject2 java 20 Nov 1999 20 31 11k ViewPhdPrelim java 20 Nov 1999 12 34 4k ViewPhdPrelim2 java 20 Nov 1999 20 32 11k ViewPublication java 13 Nov 1999 21 55 4k ViewPublication2 java 13 Nov 1999 21 30 8k ViewRA java 28 Nov 1999 12 21 4k ViewRA2 java 28 Nov 1999 12 34 6k ViewReview java 26 Nov 1999 14 48 4k ViewReview2 java 26 Nov 1999 15 12 7k ViewStudent java 14 Nov 1999 00 01 4k ViewStudent2 java 14 Nov 1999 01 19 8k ViewTA java 28 Nov 1999 11 24 4k ViewTA2 java 28 Nov 1999 12 19 8k ViewTADismiss java 28 Nov 1999 12 55 4k ViewTADismiss2 java 28 Nov 1999 13 12 3k ViewThesis java 20 Nov 1999 11 55 4k ViewThesis2 java 20 Nov 1999 22 13 11k ViewTransCourses jav
17. 1999 10 46 4k ModifyTA2 java 28 Nov 1999 12 19 12k ModifyThesis java 20 Nov 1999 11 40 4k ModifyThesis2 java 20 Nov 1999 21 24 13k ModifyTransCourses java 14 Nov 1999 04 38 4k ModifyTransCourses2 gt 20 Nov 1999 10 53 10k PersonDataFrame java 05 Nov 1999 22 10 12k Publication java 11 Nov 1999 21 44 8k SSNInput java 10 Nov 1999 12 39 4k StaffFrame java 06 Dec 1999 17 06 36k StandardMenu java 21 Oct 1999 15 49 3k StudentAwardAdd java 04 Dec 1999 13 38 8k StudentAwardView java 04 Dec 1999 14 37 6k StudentCompAdd java 04 Dec 1999 13 13 7k StudentCompView java 04 Dec 1999 13 52 6k StudentFrame java 06 Dec 1999 17 20 8k StudentMSAdd java 04 Dec 1999 13 30 8k StudentMSView java 04 Dec 1999 14 24 11k StudentPhdAdd java 04 Dec 1999 13 26 8k StudentPhdView java 04 Dec 1999 14 00 Lik StudentPublicationAd gt 04 Dec 1999 13 45 8k StudentPublicationVi gt 04 Dec 1999 15 03 8k StudentThesisAdd java 04 Dec 1999 13 33 8k StudentThesisView java 04 Dec 1999 14 32 12k SupperStaff java 11 Nov 1999 11 39 5k TADismiss java 28 Nov 1999 13 10 4k UserCheck java 19 Oct 1999 19 43 Ik ViewAdmission java 26 Nov 1999 00 02 4k ViewAdmission2 java 26 Nov 1999 00 28 10k ViewAppBasicInfo java 12 Nov 1999 10 08 4k ViewAppBasicInfo2 java 13 Nov 1999 18 34 31k ViewAppDecision java 11 Nov 1999 16 27 4k ViewAppDecision2 java 28 Nov 1999 14 45 5k ViewAppEduInfo java 12 Nov 1999 10 23 4k 27 CMSC 661 Fall 1999 Project Final Report ViewAppEduInfo2 java 12 Nov 1999
18. CMSC 661 Fall 1999 Project Final Report CMSC 661 Fall 1999 Principal of Database Systems Final Report Dec 7 Team 3 Honglin Su hosu cs umbc edu Shilang Tang stang2 Ocs umbc edu Zhongli Ding zdingl cs umbc edu Abstract As our project s final report as well as accumulated report it gives the progress and update of the project We finished our database design and all GUI design We made a few modifications for the relational schema we proposed in the previous reports In the last section we also point out the limitation of our implementation and possible improvement A detailed user manual is given as appendix of the manual Table of Contents Abstract pnns mese et Be ais ei See eens t l Shae See eine Bs Site Deas Boa 1 Introduction to the Database Application occ eesecsseseesceseseesceeseeecseeseeecneeseeecneeseeseneeaeereneeaeers 2 Database DESM DAVA 072 V 0 os m sata EE ME E Kea LE NEE T E71 2 1 The Assumption made to run the application 0 0 0 eeecceeeceeseeceeecessceceeeeeneeceseeceneeceseeseneeceeeeeneeceaes 2 2 Tasks to 1mplement ss ST A n NIS SRS AIN T T tet ARC 2 3 ER Dl sr lts cine clean hi abi ieee kse tis Saeed ee cee ba ee heen ag seis 2 4 Attributes for each entity and relationship oosossss a n aa naa naa a a enaa a aan naa enaa asen 2 5 Relatt n Schem nssssiselassism uts kts ele tiet etti eA en oe eg es 10 2 0 I88SUe8 N issues eie ostatte museet sissi 13 357 Implementation nk a n K
19. G ko Ra teta depo dsk 14 3 1 SOL Statement e 3 sc sacri SITA SINA RIINA SIA KINAA ei NINAS whee eee bari 14 3 2 Issues to hehlightissiisses vs teet tti t it seitti et isen kit 22 4 Limitation and possible improvements ooouosss sos n a eaa n aa eaa n ana aa naa Aaa naa na aaeeeen 24 Appendix eoon hs s se a T ei ip Po SS SA Sek Miehet dede 26 AL LISt OF Source Codes tuns av en sabes ESAN h n M Kassa ha caer ss 26 A 2 User Manuals cia N a IA A UTA TN SeA MA ha liinaa is 28 CMSC 661 Fall 1999 Project Final Report 1 Introduction to the Database Application With the development of the Department of Computer Science and Electrical Engineering CSEE at University of Maryland Baltimore County the department want to automate the management of graduate students including those who are seeking admission to the department currently enrolled students and those successfully graduated or unfortunately dismissed This project is developed for the Graduate Students Office GSO of CSEE department at UMBC Since CSEE is a large department and so has a large number of students and every student occupies a lot of information should be maintained by the GSO staff it is painful to record all these information in paper and manage them by human resource And also it is inconvenient for a faculty if s he want to know some kind of information about some specific students But by using computer technology we can overcome all thes
20. Research SSN keywords interests We define a VARRAY type for keywords Publication SSN title pub type authors pub date citation We define a VARRAY type for authors Award SSN award name award date description Applicant SSN received date program degree sought semester semester year financial support reguested financial support source status last update date dept send date grad send date int send date notes Faculty Name Reviewer Faculty name applicant SSN review date recommendation matching faculty reguired courses comments Deny reason name This table stores deny reason dictionary Prereguest course cno name This table stores prereguisite course dictionary Admission Decision applicant SSN admission date decision deny reason provisions assigned advisor waived tests assistantship reguiredcourse We define an object type for assistantship which has awarded type amount duration We also define a VARRAY type for prereguisite courses Applicant Decision applicant SSN decision date decision expect arrive date defer reguent notes Student Info SSN program admitted date degree sought academci advisor research advisor status phd candicacy date expected grad date xerox no last update date Comp Exam Student SSN subjects result We define a VARRAY of an object type subject test date score grade for subjects Phd Preliminary Student SSN
21. SC 661 Fall 1999 Project Final Report Student forms his her advisory examination or defense committees update the committee information Student takes Ph D preliminary exam add an entry to the Ph D preliminary table Student defends thesis update the thesis table Student completes M S project update the MS Project table Student is admitted into Ph D candidacy add an entry to Ph D candidacy table Student receives an award add an entry to award table Student is awarded an assistantship add an entry to assistantship table Student is dismissed from his her assistantship update assistantship table and student status Student is dismissed from the program add an entry to dismissed student table Student graduates add an entry to graduated student table A3 GSO Staff When GSO staff logs in the system the following menu is shown All of the GSO staff operation can be conducted through this menu It has six main options File Applicant Student Assistant Other and Help GSO Management System for Staff 32 CMSC 661 Fall 1999 Project Final Report File Manage the database and exit From this option the staff can input the institution information research keywords UMBC courses prereguisite courses deny reasons user faculty management Applicant Manage the applicant related information including receive an application modify and guery applicant information status and applicant s reply
22. a 14 Nov 1999 04 57 4k ViewTransCourses2 java 14 Nov 1999 05 01 9k A 2 User Manual 28 CMSC 661 Fall 1999 Project Final Report User Manual for Graduate Student Office GSO Management System Department of Computer Science and Electrical Engineering University of Maryland Baltimore County Table of Contents A1 Installation Guide 30 A1 1 System requirements 30 A1 2 Start the application 30 A1 3 Users 30 A2 Functions 31 A3 GSO Staff 32 A3 1 Maintain Basic Information 33 A3 2 Manage Applicant Information 36 A3 3 Manage Student Information 42 A3 4 Manage Graduate Assistant 46 A3 5 Other management 49 A3 6 Help 51 A4 Faculty 52 A4 1 Review applicant 52 AS Students 56 A5 1 Input student information 56 A5 2 Query student information 59 A6 Other Information and Acknowledgement 63 CMSC 661 Fall 1999 Instructor Dr Kalpakis Team members Honglin Su hosu cs umbe edu Shilang Tang stang2 cs umbc edu Zhongli Ding zding1 cs umbc edu 29 CMSC 661 Fall 1999 Project Final Report A1 Installation Guide A1 1 System reguirements The GSO management system was implemented by pure Java all you need to run the application are the hardware with Java virtual machine JDK 1 1 or above we have tested the platforms of Sun SPARC SGI Indy and PC Windows NT Win95 98 The distribution of the application also includes Oracle JDBC driver Before you run the application make sure that you already set your Ja
23. a JDBC connection is a time consuming procedure we make only one connection for the whole operation procedure of each staff or faculty When the staff or faculty switches different menu which is corresponding to the Oracle table we keep the JDBC connection however for Statement or ResultSet we may create it or close it upon any requirement When the user finishes all operation the JDBC connection will be closed How to keep data integration In most cases we will use primary key constraint and referential constraint to keep data integrity We also specify the value domain which give the user choices thus to make sure that user can not have any typo error e g For research keywords only the keywords stored in the keyword table can be chosen For faculty members only if they are already in the faculty table can they be assigned to review applications we also give the staff an administration frame to add new keyword add new faculty new users and so on How to implement friendly user interface Ideally we want to offer user a very friendly interface When the user input any value to a field if there is any error we can point it out the right location and what the error is e g To input the SSN which is 9 digit number if a user types less than 9 digits or other characters this field would be marked up One difficulty point is to dynamically show the query results if there is more than one rows and some columns are user define
24. a person s publications 62 CMSC 661 Fall 1999 Project Final Report CMSC 661 Fall 1999 Project Final Report A6 Other Information and Acknowledgement SSN must be 9 digits without hyphen if not there will be a warning message prompted to you Date type must use the format YYYY MM DD Also for security reason there are some limitations on student and faculty s operation e g student can only input and view records of her his self A faculty can only make a review on behalf of her his self The system has a friendly user interface if there is any problem a warning message would be prompted If you come across any other problem kindly please let us know Honglin Su hosu cs umbe edu Shilang Tang stang2 cs umbc edu Zhongli Ding zding1 Gcs umbc edu Special thanks to Dr Kalpakis who instructed us during this semester gave us a lot of help and encouragement Also thank LIST lab we leveraged two NT machines for development 63
25. ards info Add a new award SSN Award Name Award Date 01 v 01 v 1990 v Description OK Cancel You can add view student s publications staff gt other gt Publications 50 CMSC 661 Fall 1999 Project Final Report 2 Add a new record 0 x sn Ti Authorl fo Author2 Authors Author4 Type Joumalaricle gt e Date errem citation A OK Clean Cancel And the staff can add the course schedule for specific semester staff gt other gt course schedule Make arrangement of CMSC courses 0 xi Add a new one Course IONEL Semester Spring v Year Instructor Dr Ebert v ok cancel A3 6 Help The system gives help option for the user and the version information of the system 2 About us X iof x CMSC661 Project Instructor Dr Kalpakis Group Members Honglin Su Shilang Tang Zhongli Ding Version 1 0 0 1 Copyright 1999 51 CMSC 661 Fall 1999 Project Final Report A4 Faculty Faculty share the same log in menu as GSO staff however after faculty log in a different menu will be shown e GSO Management System for Faculty There are five major functions in this part File exit the system safely Review review applicant s information and make decision Admission grant admission to applicant Academic affairs students awards publications MS project comp exams Ph D preliminary and thesis
26. char2 9 keywords_array varchar2 256 forssn references person on delete cascade primary key ssn varchar2 9 16 aname adate description primary key foreign key CMSC 661 Fall 1999 Project Final Report varchar2 30 date varchar2 80 forssn aname adate forssn references person on delete cascade not null ssn create table applicant forssn rcvdate program degree semester semyear finreguest finsource status lastdate deptdate graddate intldate notes foreign key varchar2 9 primary key date char 4 not null varchar2 10 not null varchar2 10 not null number not null char default y varchar 2 80 varchar2 12 not null date not null date date sent to department date date sent to grad school date date sent to intl office varchar 2 80 forssn references person ssn on delete cascade create table faculty creat create type name tabl varchar2 30 primary key reviewer fname forssn revdate recomm match reqcourses comments primary key foreign key foreign key foreign key varchar2 30 varchar2 9 date varchar 2 80 varchar2 30 varchar2 80 varchar 2 80 fname forssn fname references faculty name match references faculty name forssn references person forssn on delete cascade mat
27. ching faculty LA assist_type as object awarded char atype varchar2 10 amount number begdate date enddate date create table deny_reason name varchar2 30 primary key 17 creat create type cno array as varray 3 creat creat creat creat tabl CMSC 661 Fall 1999 Project Final Report cno name tabl prerequest undergraduate courses required varchar2 8 primary key varchar2 30 of varchar2 8 forssn admdate decision denyreason provisions advisor waivedtests reqcno assist foreign key tabl forssn decdate decision expdate deferreg notes foreign key tabl appd stud forssn program admdate degree acadvisor resadvisor status phdcandate expgraddate xeroxno lastdate foreign key foreign key foreign key typ subject testdate score grade admdecision varchar2 9 date varchar varchar primary key not null not null varchar2 varchar2 varchar2 cno_array assist_type forssn references person on delete cascade forssn cision varchar2 9 primary key date not null varchar2 20 not null date expect arrive date char default n varchar 2 80 forssn references person forssn on delete cascade nt varchar2 9 primary key char 4 not null date varchar2 not null
28. d object type How to manipulate the object data In our schema design we use quite a few user defined object type It is easy to insert data into the table however how can we extract data out of the database e g We define an address type as following create type address_type as object street varchar2 50 city varchar2 20 state varchar2 20 zip varchar2 10 country varchar2 30 There is a simple table as following 22 CMSC 661 Fall 1999 Project Final Report create table person name varchar2 30 primary key address address type When we do a guery we want to retrieve the city of the specified name First we do a guery to get the whole address type then get the information that we want So in the Java class we have the following code Ouery the person table Statement stmt conn createStatement ResultSet rset stmt executeQuery select address from person where name name name is the user input value if rset next Struct myStruct Struct rset getObject 1 Object allattr Object myStruct getAttributes since we already know the order of column in the address type so allattr 1 represent the value of the city if allattr 1 null System out printin The city when name lives allattr 1 toString If we want to retrieve other object data we can do it in a similar way For varying array
29. del the first tier is machine which runs the application and the second tier is the Oracle 8 database and a 3 tier model it has a Web server which will direct any client reguest to the back Oracle 8 database Currently we are using 2 tier model 13 3 Implementation CMSC 661 Fall 1999 Project Final Report We are using Oracle Jdeveloper 2 0 as our user interface design environment In order to make our database design clear and concise we try to make full use of the advanced features of the Oracle 81 e g user defined data type varying arrays However the more we use the advanced features the more workload for the user interface design 3 1 SOL statement cr CT cr cr ate table users username varchar2 15 ssn varchar2 9 pwd varchar2 20 status char ate table superstaff uname varchar2 15 upswd varchar2 15 ate type address type as object street varchar2 50 city varchar2 20 state varchar2 20 zip varchar2 10 country varchar2 30 ate table person ssn varchar2 9 name varchar2 30 sex char bdate date ethnic varchar2 10 citizen varchar2 30 language varchar2 20 homeaddr address_type workaddr address_type hphone varchar2 10 wphone varchar2 10 email varchar2 30 ate table ECP forssn name relation address phone email pricontact primary key foreign key rson varchar2 9 varchar2 30 varchar2 20 address_type
30. e Selectone Tetti Are you sure N v CMSC 661 Fall 1999 Project Final Report To maintain deny reason table staff gt file gt Manage DB gt deny reason Z fa Maintain deny reason table To manage user faculty information staff gt file gt Manage DB gt user faculty only super staff has such privilege to do this a confirmation menu would be popped up fa Are you a Supper Staff iol x 35 CMSC 661 Fall 1999 Project Final Report PU e Maintain user information I tye I Po gy fo To safely exit the system staff gt file gt exit A3 2 Manage Applicant Information To add a new applicant staff gt applicant gt receive a document gt Input basic info Input Education Info Input Score Info Input Other Info Basic Information 36 CMSC 661 Fall 1999 Project Final Report 24 Adding a new applicant N g L H okl Clean Education Information 37 CMSC 661 Fall 1999 Project Final Report ee Add a new applicant Score Information 38 CMSC 661 Fall 1999 Project Final Report wa a ee Add a new applicant Other Information 39 CMSC 661 Fall 1999 Project Final Report 34 Adding a new applicant _ a for ow ut e o me Prsessor 5 Date fore for 10009 m
31. e sex bdate ethnic citizenship language home_address work_address home_phone work_phone email We define an object type Address which has the following attributes street city state zip country Person Information is the basic table for each student that seeks admission or is was enrolled in a program SSN is the primary key Emergency_Contact_Person SSN name relationship address phone email primary This table record the student s emergency contact persons the attribute primary indicates that whether this person is the primary emergency contact person It borrows SSN from Personal_Info both SSN and name are the key Institution No name address The Institution has the No as the key which is similar to ETS number for each institution Education SSN degree gradudate_date Institution_No major minor GPA GPA_scale received_transcript received_degree_certificate Use SSN degree and graduate_date as the key It also borrow the Institution_No from the table Institution Recommendation SSN name title address received_date GRE SSN test date verbal quantitative analytical waived GRE_Sub SSN test_date subject score waived TOEFL SSN test_date listening writing comprehension waived TSE SSN test date score waived 10 CMSC 661 Fall 1999 Project Final Report Research keyword name This table stores keyword dictionary
32. e database 29 Update personal information when supporting materials are arriving 30 When all application materials are received update the status 31 Application is sent to reviewer grant appropriate user rights to the reviewer 32 Reviewer makes a recommendation for the applicant update the reviewer s table 33 Application is received from reviewer revoke the user rights to access the application 34 Application is sent to admissions committee grant appropriate user rights to admission committee 35 Application is received from admissions committee revoke the user rights to access the application 36 Committee makes a decision update the applicant status 37 Letters are sent departmental graduate school international office update the dates respectively 38 Applicant replies update the applicant information 39 Admissions committee approves defer reguest if the defer reguest is received by the department update applicant status 40 Admitted student enrolls in a program update the student s information A new row is inserted in the student table 41 Student change status update the student information 42 Student takes classes update the student and courses relation information 43 Student changes academic research advisors update the student information 44 Student takes comprehensive exams add an entry to comprehensive table 31 45 46 47 48 49 50 51 52 53 54 CM
33. e drawbacks Thus Our goal is to develop a student information management system via Java database and web technology which can make our users handle and check all kinds of student information easily and efficiently most importantly the application can run across different system platforms Here our users of this database application are going to be staff from the GSO and faculty in the CSEE department For example a faculty can access a student s information easily if he has a network computer in his office and get an account in our system We Suppose most of the modifications to the information in the database will be done by GSO staff while faculty will be accessing the database mostly in read only mode In detail there are 4 basic student types Students seeking admission into a graduate program Students that enrolled in a program Students that graduated Students that are dismissed from the program Our users should can guery or update a specific student s information directly via providing the student s SSN and also s he should can guery or update database via student type Basically our system should can handle the following major types of events events happened to an applicant students events happened to an enrolled students such as the change or guery of the student s academic information and events such as a student changes her his status from one type of student to another kind of student etc In a word our purpose is to pro
34. eCourses java 11 Nov 1999 10 20 4k ManageDenyReason java 09 Nov 1999 11 00 6k Managelnstit java 09 Nov 1999 11 02 8k ManagePrerequest java 09 Nov 1999 11 02 6k ManageResKey java 09 Nov 1999 11 02 6k ManageUser java 04 Dec 1999 12 48 10k Manual java 06 Dec 1999 16 56 2k ModifyAdmission java 25 Nov 1999 23 59 4k ModifyAdmission2 java 26 Nov 1999 00 27 12k ModifyAppBasicInfo java 10 Nov 1999 12 39 4k ModifyAppBasicInfo2 gt 13 Nov 1999 18 23 28k 26 CMSC 661 Fall 1999 Project Final Report ModifyAppEdulnfo java 10 Nov 1999 10 34 4k ModifyAppEdulnfo2 java 13 Nov 1999 21 33 25k ModifyAppOtherInfo java 11 Nov 1999 00 16 4k ModifyAppOtherInfo2 gt 04 Dec 1999 15 00 8k ModifyAppScorelInfo java 10 Nov 1999 22 54 4k ModifyAppScoreInfo2 gt 13 Nov 1999 21 33 19k ModifyAssistant java 27 Nov 1999 22 23 4k ModifyAssistant2 java 28 Nov 1999 15 02 17k ModifyAttCourses java 14 Nov 1999 02 40 4k ModifyAttCourses2 java 20 Nov 1999 10 54 9k ModifyCompExam java 26 Nov 1999 01 28 4k ModifyCompExam2 java 26 Nov 1999 02 19 9k ModifyMSProject java 20 Nov 1999 09 39 4k ModifyMSProject2 java 20 Nov 1999 21 22 12k ModifyPhdPrelim java 20 Nov 1999 12 28 4k ModifyPhdPrelim2 java 26 Nov 1999 02 01 12k ModifyRA java 28 Nov 1999 11 58 4k ModifyRA2 java 28 Nov 1999 15 12 9k ModifyReview java 26 Nov 1999 14 38 4k ModifyReview2 java 26 Nov 1999 14 45 9k ModifyStudent java 14 Nov 1999 00 12 4k ModifyStudent2 java 14 Nov 1999 01 20 10k ModifyTA java 28 Nov
35. eport 2 4 Attributes for each entity and relationship Here we list attributes for each entity and relationship we also give explanation for the non obvious attributes entities and relationships Personal Info SSN name sex bdate ethnic citizenship language home address work address home phone work phone email Object type Address has the following attributes street number city state zip country Emergency Person name relationship address phone email primary Object type Address has the following attributes street number city state zip country EP SSN name Institution NO name address Object type Address has the following attributes street number city state zip country Education SSN degree gradudate date Institution No major minor GPA GPA scale received transcript received degree certificate E record degree major graduate date minor GPA GPA scale received transcript received degree certificate R letter item name title address received date text Has6 SSN item GRE test_date verbal quantitative analytical Hasl SSN test date verbal quantitative analytical waived GRE_Sub test_date subject score Has5 SSN Test_date Subject score waived TOEFL test_date listening writing comprehension Has3 SSN test_date listening writing comprepension waived TSE test_date score Has2
36. forssn testdate forssn references person ssn on delete cascade sub varchar 2 9 date varchar 2 20 number not null char default y forssn testdate subject forssn references person ssn 15 create tabl toefl forssn testdate listen write comp waived primary foreign key key create tabl forssn testdate score waived primary foreign create tabl ts key key name create type create type create forssn ptype title authors pdate citation primary foreign create tabl resea author keywor table publication key key forssn keywords interest foreign create table forssn resea s key award CMSC 661 Fall 1999 Project Final Report on delete cascade varchar2 9 date number not null number not null number not null char default n forssn testdate forssn references person ssn on delete cascade varchar2 9 date number not null char default y forssn testdate forssn references person ssn on delete cascade rch_keyword varchar2 15 primary key _array as varray 4 of varchar2 30 ds_array as varray 4 of varchar2 20 varchar 2 9 varchar 2 20 varchar 2 30 author array varchar 2 12 varchar 2 80 forssn title forssn references person on delete cascade ssn rC var
37. null varchar2 256 committee array forssn appvdate forssn references student forssn on delete cascade create table umbc courses cno cname varchar2 8 primary key varchar2 80 create table courses cno varchar2 8 19 semester year cname instructor primary key foreign key CMSC 661 Fall 1999 Project Final Report varchar2 6 number 4 varchar2 80 varchar2 30 cno semester year cno references umbc_courses cno not null create table transcourse cno semester year forssn instno grade primary key foreign key foreign key foreign key varchar2 7 varchar2 6 number 4 varchar2 9 varchar2 6 char cno semester year cno semester year references courses cno semester forssn references student forssn on delete cascade instno references institution forssn year ino create table attcourses forssn cno semester year grade primary key foreign key foreign key varchar2 9 varchar2 7 varchar2 6 number 4 char forssn cno semester year forssn references student forssn on delete cascade cno semester year references courses cno semester year create type ta_type as object cno semester year section talevel instructor evaluation create type ta array as varray 10 varchar2 7 varchar2 6 number 4
38. ows NT JDK 1 2 and Win95 JDK 1 2 The application fits exactly on Windows it looks not very beautiful on SGI We need to change the font parameters to fix this problem At present we focus on how to keep data integration how to provide a friendly user interface and how to improve its performance We suppose that the GSO management system is to facilitate the management of CSEE graduate students and applicants It could be more intelligent if we could have more time For example we could upgrade this software so that it could give a warning to users when an international student with F1 visa takes courses less than 9 credits in one semester Or it could report that a student failed in comp exam when s he try to take the exam again after two times All would be implemented in Version 2 if we could Another possible improvement is to make our GSO management system Internet ready so that it could provide service to the users within the campus Intranet To do this we need to rewrite the code integrate it with a web server XML parser and Java Servlet to dynamically generate XML HTML files 24 CMSC 661 Fall 1999 Project Final Report 5 Acknowledgement Special thanks to Dr Kalpakis who instructed us during this semester gave us a lot of help and encouragement Also thank LIST lab we leveraged two NT machines for development 25 CMSC 661 Fall 1999 Project Final Report Appendix A 1 List of Source Codes Index of soft
39. tion score 100 Evaluation comments ok OK Reset Cancel lt lt Prev Next gt gt For RA management you can do evaluation modify and query information staff gt assistant gt RA management Evaluation 48 CMSC 661 Fall 1999 Project Final Report SSN Supervisor Dr Ebert v Evaluation score Evaluation comments Evaluation date OK Clean Cancel Modify E Modify a new RA evaluation record iof x SSN 901008473 supervisor SEM lt Evaluation score 100 Evaluation comments a very good RA Evaluation date 1999 01 01 Are you sure N v OK Reset Cancel lt lt Prev Next gt gt To dismiss an assistant staff gt assistant gt dismiss A3 5 Other management In this part the system keeps the records of graduated student dismissed students student s awards publications and course schedule You can add view students who are graduated staff gt other gt Student Graduation 49 CMSC 661 Fall 1999 Project Final Report SR student graduated o x SSN Degree ms v Graduation Date Employer Beginning Date OK Clean cancel You can add view students who are dismissed staff gt other gt Student Dismissed E Input a record when a student dismissed miel EA ssn I Dismission Reason Date Dn OK Clean cancel You can add view student s awards staff gt other gt Awards Maintain aw
40. u apf County L Professor je Date 0119 for 19915 af camy ALOR Keywora2 accor Keywords a c0R 7 KewvordafarcoR To Modify applicant information staff gt applicant gt modify gt basic info Education Info Score Info Other Info A new menu would be popped waiting for you to input the applicant SSN 40 CMSC 661 Fall 1999 Project Final Report FE Please input SSN 901008473 x canc To guery applicant information do it similarly input the SSN then view the basic info Education Info Score Info Other Info To modify view applicant s status staff gt applicant gt applicant status gt modify view input the applicant s SSN and do it accordingly fa Applicant s status incomplete _ HE N dae _ To input view applicant reply staff gt applicant gt applicant reply gt input view 41 CMSC 661 Fall 1999 Project Final Report ee Input an applicant s decision A3 3 Manage Student Information To add a new student staff gt student gt add a new student Si When a student registers To modify view student you need to input SSN then do it accordingly 42 CMSC 661 Fall 1999 Project Final Report Si Modify a student basic info or kaipais x Fumme Dw 20010521 1999120 ok gt Reset You can add modify view student s course
41. va running environment add this distribution file gso jar to your Java CLASSPATH e g for UNIX setenv CLASSPATH home grad3 hosu lib gso jar for Windows set CLASSPATH c shl lib gso jar A1 2 Start the application To start the application simply type the command java GSO CMSC661 a user log in menu will be popped out Ei Gso Management System BEE Welcome to GSO Management System User Name Password Cancel A1 3 Users We define 3 types of users to use this system One is the GSO staff who has the privilege to maintain this sytem another one is faculty member who can review the application and view applicant s information and the third one is the student who can only input or 30 CMSC 661 Fall 1999 Project Final Report guery their own relevant information such as publishing academic awards etc All three types of users shall the same log in menu After the user log in the system will pop up another menu based on different type of user A2 Functions The users of this database application are going to be personnel from GSO and faculty and students have a very limited manipulation capability Most of the modifications to the information in the database will be done by GSO staff while faculty will be accessing the database mostly in read only mode except for the reviewer s input The system handles the following event 28 Receive a new application add new personal information into th
42. vide the management functions as completely as possible And also we will provide a friendly user interface By using our system the staff and faculty can handle the information easily 2 Database Design 2 1 The Assumption made to run the application The CSEE department has a networked computing environment every computer in the department including PCs Macs Unix workstations or servers whether they are running CMSC 661 Fall 1999 Project Final Report Windows 95 98 NT Solaris or Irix they all have Java virtual machine installed suppose they all have Java 2 ready this is very fundamental assumption Our application will use Java JDBC and Oracle and they application can run every Java platforms We will also take advantage of the advanced features of the Oracle 8 DBMS In order to make the design more efficient we will use user defined data types For example we will define a data type Address Type which has several attributes such as street number city state zip and country We will also use variable array to represent many one relation such as committee information which usually has a maximum number of members such as five or six then we can define an array of with maximum six members each member has two attributes i e name and role 2 2 Tasks to implement We are considering to realize the following tasks which handle the corresponding events 1 2 3 4 5 6 10 11 12 13 14 Receive
43. ware gso Name Last modified Size Description Parent Directory 01 Dec 1999 14 17 AddAttCourses java 14 Nov 1999 04 14 7k AddCompExam java 26 Nov 1999 02 00 7k AddMSProject java 20 Nov 1999 12 19 8k AddNewAdmission java 28 Nov 1999 15 41 10k AddNewApplicant java 10 Nov 1999 14 17 25k AddNewApplicant2 java 10 Nov 1999 18 29 22k AddNewApplicant3 java 11 Nov 1999 00 00 17k AddNewApplicant4 java 12 Nov 1999 00 31 30k AddNewAssistant java 27 Nov 1999 16 04 11k AddNewRA java 28 Nov 1999 13 07 6k AddNewReview java 26 Nov 1999 13 58 9k AddNewStudent java 13 Nov 1999 23 54 9k AddNewTA java 28 Nov 1999 10 44 9k AddPhdPrelim java 28 Nov 1999 15 19 8k AddThesis java 20 Nov 1999 12 42 8k AddTransCourses java 14 Nov 1999 04 31 8k AppDecision java 28 Nov 1999 14 43 7k AppStatus java 10 Nov 1999 15 18 9k Awards java 11 Nov 1999 22 07 8k CMSC661 java 28 Nov 1999 13 41 3k CheckUser java 23 Oct 1999 13 08 1k CommitteeMember java 26 Nov 1999 15 28 5k CommitteeMember2 java 25 Nov 1999 22 20 Sk Default1 prf 28 Nov 1999 16 53 4k Dismission java 12 Nov 1999 14 39 6k FacultyFrame java 06 Dec 1999 17 18 10k FrameAbout java 06 Dec 1999 17 15 2k FrameEnterGSO java 04 Dec 1999 13 40 7k FrameSsnWarning java 09 Nov 1999 13 12 1k FrameSuccess java 09 Nov 1999 13 11 2k FrameWarning java 09 Nov 1999 13 10 2k GSO jpr 30 Nov 1999 22 17 7k Graduation java 11 Nov 1999 15 07 7k MainFrame java 21 Oct 1999 15 37 1k ManageCmscCourses java 14 Nov 1999 03 40 5k Manag
Download Pdf Manuals
Related Search