1 / 28

School Registration Application

School Registration Application. Chase Diem Tim Frasca. CSC545 – Databases Spring 2014. Outline. Introduction Class Model Database Diagram Use Cases – Student Use Case Diagram Examples Of Use Case Use Cases – Teacher Use Case Diagram Examples of Use Case Use Cases – Admin

isanne
Download Presentation

School Registration Application

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. School Registration Application Chase Diem Tim Frasca CSC545 – Databases Spring 2014

  2. Outline • Introduction • Class Model • Database Diagram • Use Cases – Student • Use Case Diagram • Examples Of Use Case • Use Cases – Teacher • Use Case Diagram • Examples of Use Case • Use Cases – Admin • Use Case Diagram • Examples of Use Case • Conclusion • What Was Easy • What Was Hard

  3. Course cId : int name : String number : String section : int year : Year semester : String building : String room : String maxStudents : int teacherId : int createCourse() – Teacher creates. deleteCourse() – Teacher deletes. udpateCourse() – Teacher updates. listCoursesTeaching() – Teacher lists courses they teach. listCoursesStudentRegistered() – Student/teacher lists who is registered to a course getCourse() – Get course information. listCoursesAll() – Lists summary of all courses registerStudent() – Teacher registers student for course. unregisterStudent() – Teacher unregistered student from course listStudentsInCourse() – Teacher gets listing of students in course they are teaching listStudentCourse() – Lists students in course for un-registration purposes listStudentsNotInCourse() – Teacher gets list of students that can be registered for course Class Model User uId: int email : String password : String admin : String createUser() – Admin creates users userExists() – Verify user exists deleteStudent() – Admin deletes students deleteNonStudent() – Admin deletes other users listStudent() – Teacher lists students for registration login() – User logs in deleteUserList() – Admin lists users to delete 1..* 0..* Assignment aId : int csId : int name : String points : float maxPoints : float letterGrade : String dueDate : Date createAssigment() – Teacher creates assignment deleteAssignment() – Teacher deletes assignment listAssignments() – Lists assignments for student updateAssinment() – Teacher edits assignment 0..*

  4. Database Diagram COURSE USER USER_COURSE ASSIGNMENT

  5. Use Cases - Student Click course details getCourse() Enter email, password Successful login login() listCoursesStudent Registered() Click course grades Student listAssignments() Unsuccessful login Error to re-login()

  6. Use Cases – Student – Login SQL SELECT uId, email, password, admin FROM user WHERE email = 'email’ AND password = ‘password‘

  7. Use Cases – Student – Login cont. Student has logged in successfully. SQL SELECT uc.uId, c.cId, c.name, c.number, c.section, FORMAT((SUM(points)/SUM(maxPoints) * 100), 2) as average FROM user_courseuc JOIN course c ON uc.cId = c.cId JOIN user s ON s.uId = uc.uId LEFT JOIN assignment asg ON asg.csId = uc.ucId WHERE s.uId = ‘studentId’ GROUP BY uc.uId, c.cId, c.name, c.number, c.section

  8. Use Cases – Student – View Courses SQL SELECT * FROM COURSE WHERE cId = 3

  9. Use Cases – Student – View Grades SQL SELECT * FROM test.user_courseuc JOIN test.assignmentassg ON assg.csId = uc.ucId WHERE uc.cId = 3 AND uc.uId = 6

  10. Use Cases – Student – Logout No SQL involved. Session was emptied and redirect back to login page.

  11. Use Cases - Teacher Click edit course button updateCourse() getCourse() Enter email, password Click course details Successful login Click delete course button login() deleteCourse() listCoursesTeaching() Teacher Click add course button Click students button createCourse() Unsuccessful login listStudents Registered() Click unregister student button Error to re-login() Click student grades button unregisterStudent() listAssignments() Click register student button registerStudent() deleteAssignment() createAssignment() editAssignment()

  12. Use Cases – Teacher– Login SQL SELECT uId, email, password, admin FROM user WHERE email = 'email’ AND password = ‘password‘

  13. Use Cases – Teacher – Login cont. Teacher has logged in successfully. SQL SELECT * FROM COURSE WHERE teacherId= ‘teacherId’

  14. Use Cases – Teacher – Add Courses Teacher clicks Add New Course button. SQL INSERT INTO COURSE (name, number, section, year, semester, building, room, maxStudents, teacherId) VALUES ('New Test Course', 'CSC123', 456, 15, 'FALL', '148 University Ave', '222', 20, 4)

  15. Use Cases – Teacher – Edit Courses SQL UPDATE COURSE SET name = 'Databases Course xyz', number = 'CSC456', section = 11, year = 14, semester = 'SPRINtreG', building = '25 University Ave', room = '999', maxStudents= 999, teacherId = 2 WHERE cId =  1

  16. Use Cases – Teacher – Delete Courses Teacher clicks Delete Course button. SQL // Delete from assignment table DELETE FROM assignment WHERE csId in (SELECT ucId FROM user_course WHERE cId = 2) // Delete from user_course table DELETE FROM user_courseWHERE cId = 2 // delete from course table DELETE FROM course WHERE cId = 2

  17. Use Cases – Teacher– View Students Students in the class the teacher is teaching. SQL SELECT u_c.uId, cId, email, admin, points, FORMAT((SUM(points)/SUM(maxPoints) * 100), 2) as average FROM USER_COURSE u_c LEFT JOIN USER u ON u_c.uId = u.uId LEFT JOIN ASSIGNMENT asg ON asg.csId = u_c.ucId WHERE cId = 3 GROUP BY u_c.uId, cId, email, admin

  18. Use Cases – Teacher – View Students - Grades Teacher can view, edit and delete the grades of students in his/her class SQL SELECT * FROM test.user_courseuc JOIN test.assignmentassg ON assg.csId = uc.ucId WHERE uc.cId = 3 AND uc.uId = 6

  19. Use Cases – Teacher – View Students – Add Assignment Teacher can add and delete a new assignment for any student in the class. SQL INSERT INTO ASSIGNMENT (csId, name, points, maxPoints, letterGrade, dueDate) VALUES (4, 'New Test', 99.9, 100.0, 'A', '2014-01-01')

  20. Use Cases – Teacher – View Students – Edit Assignment Teacher can view, edit and delete the grades of students in his/her class SQL UPDATE ASSIGNMENT SET name = 'Exam 1', points = 33.4, maxPoints = 100.0, letterGrade = 'F', dueDate = '2014-07-21' WHERE aId = 10

  21. Use Cases – Teacher – View Students – Delete Assignment Teacher can add and delete a new assignment for any student in the class. SQL DELETE FROM ASSIGNMENT WHERE aId = 12

  22. Use Cases – Teacher – Un/Register Student Teacher can register… …and unregister students. SQL SQL INSERT INTO user_course (uId, cId) VALUES (7, 3) DELETE FROM user_course WHERE uId=' 7' AND cId='3';

  23. Use Cases - Admin Enter email, password login() createUser() Successful login Admin Unsuccessful login deleteNon Student() Error to re-login() deleteStudent()

  24. Admin Cases – Home page Admin can add or delete any student, teacher, or another admin.

  25. Admin Cases – Add a User Admin can add or delete any student, teacher, or another admin. SQL INSERT INTO USER (email, password, admin) VALUES ('newuser@email.edu', '123456', '0');

  26. Admin Cases – Delete a User SQL DELETE FROM assignment WHERE csId in (SELECT ucId FROM user_course WHERE uId = (SELECT uId FROM user where uId = '2')) // Delete from user_course table DELETE FROM user_course WHERE uId = '2' // delete from user table DELETE FROM user WHERE uId = '2'

  27. What was easy? • Creating an E-R diagram felt more natural than UML designs. • Entities (User, Course, Assignment) were all well defined, so it was easy to build relationships with one another. • Building a method to re-create the tables and test data. • This allowed us to test, make changes, and re-set the data as often as possible. • Once the methods were finalized and tested, it was easy to build a GUI around them. • Apache Tomcat – Application container to hold the deployable. • Website: http://tomcat.apache.org/ • Bootstrap – Open source tool using CSS and JavaScript routines to make basic HTML elements look nice. • Website: http://getbootstrap.com/ • Java Servlet Pages – Making HTML pages interactive by including Java’s Model-View-Controller pattern.

  28. What was hard? • The SQL queries can sometimes be tricky: • Many times, there is more than one way to get the data: • Subselects • Join statements • Where a=b clauses • Deleting students should also delete their assignments; or else those FKs will be lost in the table. • Initial Servlet work was hard, although MVC is easy to pick up once you get started.

More Related