For this project, you will design and implement a relational database system to support the operations...

Transcribed Image Text:

For this project, you will design and implement a relational database system to support the operations of a University Registration System. You will use HTML for the user interface, MySQL for the database server, and Java, JDBC for connectivity between the user interface and the database server. You will have to install your own web server that will host your web application (Tomcat) as well as a MySQL server. Everything will be local. All you need to complete the project (installation, etc.) can be found in the "Project Information" Module. Project Specification A University offers courses which have a course id, course name, number of credits, department offering the course, and a list of prerequisite courses. Each semester, the Department Administrator adds the courses that will be offerred by the department. The courses offerred include a section number, a list of days/times that they meet during each week (for example, Tuesdays, Thursdays from 10am-11:20am), and a professor that will teach the course. Professors are also added by the Department Administrator, a professor has ID, name, phone number, and office number. Students have an ID, name, phone, and birth date, can register for courses by selecting available sections from a list of elegible courses. Design For the first part of this project you will draw the Entity Relationship Diagram to be used by your application program, and then transform your diagram into tables in the relational model. User-level Functionality There are three types of users: Department Administrators, Students, and Professors. Department Administrators should be able to: o Add users of all types (including their passwords) o Add courses and course schdeules for a given semester Professors should be able to: ● o View a list of students of the courses that they are teaching. o Assign grades to the students taking their courses. . Students should be able to: o See all the courses that they have taken (including their grades). o Register for a course, as long as they have taken the requried prerequisites. How to work in your project 1. Work on your ER Diagram and relational schema. 2. Create all the tables in SQL 3. write down the main SQL queries such as o Given a student ID, produce a list of available course sections that the student can take and that satisfies the required prerequisites. The list should include course id, section number, the day/time that the course is offerred, and the name of the professor teaching the course. o Given a student ID, produce the list of the course sections that the student is enrolled in, including the weekly day/time of classes. o Given a semester, a course id and a section number, generate a list of the students enrolled in that course section. 4. Create a login screen (html) that has basic functionality and distinguishes among the three types of users. 5. Complete functional screens for each one of the required user-level functions described above. For this project, you will design and implement a relational database system to support the operations of a University Registration System. You will use HTML for the user interface, MySQL for the database server, and Java, JDBC for connectivity between the user interface and the database server. You will have to install your own web server that will host your web application (Tomcat) as well as a MySQL server. Everything will be local. All you need to complete the project (installation, etc.) can be found in the "Project Information" Module. Project Specification A University offers courses which have a course id, course name, number of credits, department offering the course, and a list of prerequisite courses. Each semester, the Department Administrator adds the courses that will be offerred by the department. The courses offerred include a section number, a list of days/times that they meet during each week (for example, Tuesdays, Thursdays from 10am-11:20am), and a professor that will teach the course. Professors are also added by the Department Administrator, a professor has ID, name, phone number, and office number. Students have an ID, name, phone, and birth date, can register for courses by selecting available sections from a list of elegible courses. Design For the first part of this project you will draw the Entity Relationship Diagram to be used by your application program, and then transform your diagram into tables in the relational model. User-level Functionality There are three types of users: Department Administrators, Students, and Professors. Department Administrators should be able to: o Add users of all types (including their passwords) o Add courses and course schdeules for a given semester Professors should be able to: ● o View a list of students of the courses that they are teaching. o Assign grades to the students taking their courses. . Students should be able to: o See all the courses that they have taken (including their grades). o Register for a course, as long as they have taken the requried prerequisites. How to work in your project 1. Work on your ER Diagram and relational schema. 2. Create all the tables in SQL 3. write down the main SQL queries such as o Given a student ID, produce a list of available course sections that the student can take and that satisfies the required prerequisites. The list should include course id, section number, the day/time that the course is offerred, and the name of the professor teaching the course. o Given a student ID, produce the list of the course sections that the student is enrolled in, including the weekly day/time of classes. o Given a semester, a course id and a section number, generate a list of the students enrolled in that course section. 4. Create a login screen (html) that has basic functionality and distinguishes among the three types of users. 5. Complete functional screens for each one of the required user-level functions described above.