1 / 25

Hochschule Ulm Sommersemester 2012 Database Programming Thomas Oppel Matthias Schmitt

Hochschule Ulm Sommersemester 2012 Database Programming Thomas Oppel Matthias Schmitt Benjamin Brand. Tomabe Student Self - Management Software. Overview. User Stories Feature List Specifications & Requirements Entity Relationship Model Data Definition Language

goldy
Télécharger la présentation

Hochschule Ulm Sommersemester 2012 Database Programming Thomas Oppel Matthias Schmitt

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Hochschule Ulm Sommersemester 2012 Database Programming Thomas Oppel Matthias Schmitt Benjamin Brand

  2. Tomabe Student Self-Management Software Overview • User Stories • Feature List • Specifications & Requirements • EntityRelationship Model • Data Definition Language • Data Manipulation Language • Select Instruction • StoredProcedures / Cursors • User definedFunctions • Triggers Thomas Oppel // Matthias Schmitt // Benjamin Brand

  3. Tomabe Student Self-Management Software User Stories User Stories Student Self-Management Software As a student I want ... “to see my timetable to manage the time.” “ to have an overview over my marks and ECTS-Points.” “ to have an overview over my tasks.” “ to be warned when a task deadline is less than seven days.” “ to have a list of my contacts in order to manage them.” “ to take part in a community with students of my semester.” Thomas Oppel // Matthias Schmitt // Benjamin Brand

  4. Tomabe Student Self-Management Software Feature List • Create your own profile with an avatar • Create an personalized timetable including courses, lectures and rooms • Manage tasks from your courses (e.g. homework) • Manage general tasks (e.g. book return) • Overview about your open tasks • Warning when task-deadline is close to the end • Overview of your exam results, calculate the average of the exam results • Overview about your earned ECTS-Points • Store E-Mail addresses / phone numbers from important contact people In order to manage and organize student´s life, tomabe provides the right features andfunctions. Thomas Oppel // Matthias Schmitt // Benjamin Brand

  5. Tomabe Student Self-Management Software Specifications & Requirements Specifications & Requirements • Software is based upon the programming language JAVA • MySQL-Database tostoredata • Website / Web-Applicationbased on HTML/PHP • Graphical User Interface -> Ease of use • clear architecture • Well designed database • Fast respondingdatabase • Platformindependent Some Specifications and Requirements which have to been noticed when implementing the software/database. Thomas Oppel // Matthias Schmitt // Benjamin Brand

  6. Tomabe ER-Model Thomas Oppel // Matthias Schmitt // Benjamin Brand

  7. Tomabe Student Self-Management Software Data Definition Language // Examples • # Create the table student • CREATETABLEstudent( • nicknameVARCHAR(50)NOTNULL, • prenameVARCHAR(80)NOTNULL, • lastnameVARCHAR(100)NOTNULL, • passwordVARCHAR(500)NOTNULL, • PRIMARYKEY(nickname) • ) • # Create the table course • CREATETABLEcourse( • courseIDINTauto_incrementNOTNULL, • nameVARCHAR(100)NOTNULL, • semesterVARCHAR(20)NOTNULL, • INDEXsemester_ind(semester), • PRIMARYKEY(courseID,semester) • ) Thomas Oppel // Matthias Schmitt // Benjamin Brand

  8. Tomabe Student Self-Management Software Data Definition Language // Examples • # Create the table task • CREATETABLEtask( • taskIDINTauto_incrementNOTNULL, • descriptionVARCHAR(100)NOTNULL, • deadlineTIMESTAMPDEFAULTCURRENT_TIMESTAMP, • courseIDINTNOTNULL, • nicknameVARCHAR(50)NOTNULL, • islesssevendaysINTDEFAULT0, • doneBOOLEANDEFAULTFALSE, • PRIMARYKEY(taskID), • FOREIGNKEY(courseID) REFERENCEScourse(courseID) • ONDELETECASCADEONUPDATECASCADE, • FOREIGNKEY(nickname) REFERENCESstudent(nickname) • ONDELETECASCADEONUPDATECASCADE • ) Thomas Oppel // Matthias Schmitt // Benjamin Brand

  9. Tomabe Student Self-Management Software Data Manipulation Language // Examples # Insert Into instructions INSERTINTOstudent(nickname,prename,lastname,password) VALUES('matze','Matthias','Schmitt',SHA1('matze')); INSERTINTOuniversity(name,description) VALUES('Hochschule Ulm','Hochschule für Technik und Medien'); INSERTINTOuniversity_has_student(universityID,nickname) VALUES(1,'benny'); INSERTINTOcourse(name,semester) VALUES('Informationsmanagement','WF4'); Thomas Oppel // Matthias Schmitt // Benjamin Brand

  10. Tomabe Student Self-Management Software Select instruction # Build the timetable of a student SELECTDISTINCTstudent.nicknameAS'nickname', course.nameAS'course name', timetable_has_courses.courseDayAS'day', timetable_has_courses.courseTimeAS'time', • timetable_has_courses.roomas'room' FROMstudent JOINstudent_takes_courseON student.nickname=student_takes_course.nickname JOINcourseONstudent_takes_course.courseID= course.courseID JOIN timetable_has_coursesON course.courseID=timetable_has_courses.courseIDAND student.nickname='matze'; LIVE DEMO ! Thomas Oppel // Matthias Schmitt // Benjamin Brand

  11. Tomabe Student Self-Management Software StoredProcedures # Procedure fill data into student table DELIMITER&& CREATEPROCEDUREfillInStudent(INnicknameVARCHAR(50), INprenameVARCHAR(50), INlastnameVARCHAR(50), INpasswordVARCHAR(50)) BEGIN INSERTINTOstudent(nickname,prename,lastname,password) VALUES(nickname,prename,lastname,SHA1(password)); END&& DELIMITER; Thomas Oppel // Matthias Schmitt // Benjamin Brand

  12. Tomabe Student Self-Management Software StoredProcedures • # Procedure show the courses of a teacher • DELIMITER&& • CREATEPROCEDUREshowTeachersCourses • (INteacherNameVARCHAR(50)) • BEGIN • SELECTprenameAS'Prename',lastnameAS'Lastname', • nameAS'Course',titleAS'Title'FROMlecturerl • JOINlecturer_teaches_coursesltcJOINcoursecou • ONl.lecturerID=ltc.lecturerIDAND • ltc.courseID=cou.courseID • WHEREl.lastname=teacherName; • END&& • DELIMITER; LIVE DEMO ! Thomas Oppel // Matthias Schmitt // Benjamin Brand

  13. Tomabe Student Self-Management Software StoredProcedures • # Procedure show the courses of a teacher • # RESULT for lecturer “Ottmann” CALLshowTeachersCourses ('Ottmann'); Thomas Oppel // Matthias Schmitt // Benjamin Brand

  14. Tomabe Student Self-Management Software StoredProcedures # Procedure set a flag into task table when task-deadline # is less than seven days DELIMITER&& CREATEPROCEDUREsetTaskFlaglessSevenDays(nameVARCHAR(50)) BEGIN DECLAREnoMoreINT; DECLAREdeadDATETIME; DECLAREidTaskINT; DECLARECursorOneCURSORFOR SELECTdeadlineFROMtaskWHEREnickname=name; DECLARECursorTwoCURSORFOR SELECTtaskIDFROMtaskWHEREnickname=name; DECLARECONTINUEHANDLERFORNOTFOUNDSETnoMore=1; SETnoMore=0; Thomas Oppel // Matthias Schmitt // Benjamin Brand

  15. Tomabe Student Self-Management Software StoredProcedures • WHILE(noMore=0)DO • FETCHCursorOneINTOdead; • FETCHCursorTwoINTOtaskID; • IF(noMore=0)THEN • IF(DATEDIFF(dead,now())<7)THEN • UPDATEtaskSETlesssevendays=-1WHEREtaskID=idTask; • ELSE • UPDATEtaskSETlesssevendays=1WHEREtaskID=idTask; • ENDIF; • ENDIF; • ENDWHILE; • CloseCursorOne; • CloseCursorTwo; • END&& • DELIMITER; LIVE DEMO ! LIVE DEMO ! Thomas Oppel // Matthias Schmitt // Benjamin Brand

  16. Tomabe Student Self-Management Software StoredProcedures • # Procedure set a flag into task table when task-deadline • # is less than seven days CALLsetTaskFlaglessSevenDays (‘matze'); Value -1 becausedeadlineislessthansevendaystocurrentdate Thomas Oppel // Matthias Schmitt // Benjamin Brand

  17. Tomabe Student Self-Management Software StoredProcedures • # Procedure set done to a task • DELIMITER $$ • CREATEPROCEDUREis_done() • BEGIN • DECLAREisItDoneBOOLEAN;DECLAREnoMoreDoneINT; • DECLAREtaskIDCounterINTUNSIGNED; • DECLAREgetDoneCRSCURSORFOR • SELECTdoneFROMtask; • DECLARECONTINUEHANDLERFORNOTFOUNDSETnoMoreDone=1; • SETnoMoreDone=0; • SETisItDone=FALSE; • SETtaskIDCounter=0; • OPENgetDoneCRS; Thomas Oppel // Matthias Schmitt // Benjamin Brand

  18. Tomabe Student Self-Management Software StoredProcedures • WHILE (noMoreDone=0)DOFETCHgetDoneCRSINTOisItDone; • SETtaskIDCounter=taskIDCounter+1; • IF(noMoreDone=0)AND(isItDoneisTRUE)THEN • UPDATEtaskSETdeadline=''WHEREtaskID=taskIDCounter; • ENDIF; • ENDWHILE; • CLOSEgetDoneCRS; • END $$ • DELIMITER; LIVE DEMO ! Thomas Oppel // Matthias Schmitt // Benjamin Brand

  19. Tomabe Student Self-Management Software User definedfunctions # Function calculates the sum of ECTS-Points DELIMITER// CREATEFUNCTIONectsSum(nameVARCHAR(50))RETURNSINT BEGIN DECLAREsumECTSINT;DECLAREsumNEWINT; DECLAREnoMoreECTSINT; DECLARECursornewCURSORFOR SELECTectsFROMstudent_takes_course WHEREnickname=name; DECLARECONTINUEHANDLERFORNOTFOUNDSETnoMoreECTS=1; SETnoMoreECTS=0,sumECTS=0; OPENCursornew; Thomas Oppel // Matthias Schmitt // Benjamin Brand

  20. Tomabe Student Self-Management Software User definedfunctions • WHILE(noMoreECTS=0)DOFETCHCursornewINTOsumNEW; • IF(noMoreECTS=0)AND(sumNEWISNOTNULL)THEN • SETsumECTS=sumECTS+sumNEW; • ENDIF; • ENDWHILE; • CLOSECursornew; • RETURNsumECTS; • END// • DELIMITER; LIVE DEMO ! LIVE DEMO ! Thomas Oppel // Matthias Schmitt // Benjamin Brand

  21. Tomabe Student Self-Management Software User definedfunctions • # Function calculates the sum of ECTS-Points • # RESULT: Example for the student with the nickname “matze” SELECTectsSum ('matze'); Thomas Oppel // Matthias Schmitt // Benjamin Brand

  22. Tomabe Student Self-Management Software User definedfunctions # Function calculates the average of students marks DELIMITER&& CREATEFUNCTIONavaMark(nameVARCHAR(50)) RETURNSDECIMAL(10,3) BEGIN DECLAREaMarkDECIMAL(10,3);DECLARElMarkDECIMAL(10,3); DECLAREnMarkINT;DECLAREnoMoreMarksINT; DECLARECursCURSORFOR SELECTMarkFROMstudent_takes_course WHEREnickname=nameORDERBYMarkASC; DECLARECONTINUEHANDLERFORNOTFOUNDSETnoMoreMarks=1; SETnoMoreMarks=0,aMark=0,nMark=0; OPENCurs; Thomas Oppel // Matthias Schmitt // Benjamin Brand

  23. Tomabe Student Self-Management Software User definedfunctions • WHILE(noMoreMarks=0)DOFETCHCursINTOlMark; • IF(noMoreMarks=0)AND(lMarkISNOTNULL)THEN • SETnMark=nMark+1,aMark=aMark+lMark; • ENDIF; • ENDWHILE; • CLOSECurs; • IFnMark>0THENRETURNaMark/nMark; • ELSERETURNNULL; • ENDIF; • END&& • DELIMITER; LIVE DEMO ! Thomas Oppel // Matthias Schmitt // Benjamin Brand

  24. Tomabe Student Self-Management Software Triggers # Trigger set new mark to -1 when ifis false DELIMITER $$ CREATETRIGGERbu_student_takes_course BEFOREUPDATEONstudent_takes_courseFOREACHROW BEGIN IFNEW.mark<1ORNEW.mark>5THEN SETNEW.mark=-1; ENDIF; END $$ DELIMITER; • # Trigger set deadline to current date if entry is false DELIMITER $$ CREATETRIGGERbi_taskBEFOREINSERTONtask FOREACHROW BEGIN IFNEW.deadline<CURRENT_TIMESTAMP()THEN SETNEW.deadline=CURRENT_TIMESTAMP(); ENDIF; END $$ DELIMITER; LIVE DEMO ! Thomas Oppel // Matthias Schmitt // Benjamin Brand

  25. Hochschule Ulm Sommersemester 2012 Database Programming Thomas Oppel Matthias Schmitt Benjamin Brand

More Related