Building Course Registration Project with Azure SQL Database

Anagha Todalbagi

Kendall Hoffman

June 11th, 20240 0

This semester long project was completed by Master’s students at Cornell University with mentorship from the Azure SQL database product team at Microsoft.

Project Mission

The purpose of this project is to design a high-fidelity university course registration system utilizing Microsoft Azure cloud services. Our target audience includes universities given that the intended target users are university students. The final result is a full-stack system consisting of the student frontend perspective and the backend Azure SQL database database necessary to support this.

Project Goals

Develop a high-fidelity, full-stack website that reimagines the Cornell University course registration system currently in place through consideration of the user-centered design principles.
Create an intuitive and streamlined interface to enhance the overall student experience with course registration.
Implement key features like a course catalog, class schedule builder, registration tools, and student dashboard based on research of existing systems.
Design a robust backend infrastructure utilizing Microsoft Azure cloud services to support scalability.
Conduct user research and testing to collect feedback and iterate on the prototype to best address student needs.

Meet the Team

Kendall Hoffman: Project Manager
Zhichao Song: Product Manager
Medha Bulumulla: Backend Developer
Kellan Liu: Backend Developer
Sonia Sunil: Frontend Developer
Claire Xu: UX Designer

Muazma Zahid: Microsoft Mentor
Anagha Todalbagi: Microsoft Mentor

User Experience Research and Prototyping

Primary & Secondary Research

To grasp the intricacies of the course registration system’s information architecture, our team conducted a thorough secondary analysis of our university’s existing systems. Specifically, we scrutinized the course registration processes at Cornell University, Carnegie Mellon University, and the National University of Singapore. The findings were meticulously compiled into a comprehensive Case Study slide deck.

Subsequently, using Figma, we synthesized the case study features, identifying five pivotal modules crucial to the student course registration experience: the Home Page, Course Catalog, Schedule, Registration, and Student Information modules. This analysis formed the basis for our Research Analysis, where we distilled key feature takeaways and defined must-haves and nice-to-haves based on our findings.

The primary research for the course registration system is focused on understanding user behavior and pain points, particularly with Cornell’s Course Registration System. This involves collecting data for UI/UX enhancements. Using a survey method, we aimed to evaluate ease of use, function usability, and identify potential improvements. Feedback from 28 respondents highlighted several user pain points, including complexity in navigation, an outdated user interface, difficulty in finding courses, an inconsistent experience with waitlist features, and a frustrating course search process. There’s also a preference for direct enrollment, a lack of intuitive scheduling tools, and dependence on external sites for course reviews. The findings suggest the need for a more intuitive interface, streamlined processes, clearer waitlist icons, embedded ratings, and a user-friendly scheduling tool.

Prototyping & Design System

We then delved into the Information Architecture, providing a Figma diagram and its corresponding description. Additionally, we detailed the Figma Prototype, including preliminary sketches, Lo-fi/Mid-fi, and Hi-fi Prototypes. This comprehensive approach ensures a clear and systematic understanding of the essential elements guiding the course registration system’s development.

In developing the prototype, our team implemented a design system with a color and font scheme from Cornell University. It was chosen as an illustrative example due to our accessibility to its resources. This intentional design choice ensures scalability, facilitating the seamless replacement of colors to align with those of other universities. Within this framework, we integrated PrimeENG Components for development purposes, contributing to a cohesive and visually consistent design system for the prototype.

Database Design and Development

ER Diagram: Using LucidChart to collaborate on the diagram

The database centers around the class table, which provides information about each semester offered. Class and course have a one-to-many relationship in which each course has multiple classes, one for each semester is shown. Each class has a table with information about the class, including its class type, classroom, classroom type, building, instructor, status (open/closed), semester, and which department it is a part of. The student table is another critical component of the database. Information about their first and last name, identifier number, degree type, expected graduation year, start year, department, standing, and level is available. The student key and class keys are linked in the class_list table, which identifies what courses the students are registered for and the grades they have received.

Excel/Sample Data

Using the database structure created above, we created a set of sample data with the aid of OpenAI. Each table had its sheet in the spreadsheet. We made sure to ensure the IDs were linked with each other and told a comprehensive story for each student.

Leveraging Azure to create a Database using SQL

With the tables of our sample data, we used Table Convert to create each SQL query easily.

`FOREIGN KEY REFERENCES office(office_id)`

CREATE TABLE office (
     office_id int PRIMARY KEY,
     room_num int,
     building_id int FOREIGN KEY REFERENCES building(building_id)
 )

CREATE TABLE instructor(
     instructor_id int PRIMARY KEY,
     first_name varchar(100),
     middle_name varchar(100),
     last_name varchar(100),
     email varchar(50),
     office_id int FOREIGN KEY REFERENCES office(office_id)
)

Frontend Development

For front–end development, we decided to utilize the Javascript framework, AngularJS for several reasons – it is a framework our frontend developer is most familiar with, it has a code structure that maintains consistency and is thus easier to comprehend, and it comes complete with in-built HTTP, routing, etc. for configuration.

In addition to this, we also used PrimeNG components and FullCalendar to develop the different screens. PrimeNG is an AngularJS-compatible library with UI components that make development faster, cleaner, and more efficient. It is combined with PrimeFlex, a responsive CSS utility library to accompany Prime UI libraries. The most challenging component to develop was the Scheduler screen, as we needed a third-party component compatible with AngularJS. Hence, after much investigation, we used FullCalendar, which had most of the functionalities we needed for our development. However, as we need Premium features to fully display all the features we planned to develop, our Scheduler as of now, is only capable of displaying static data.

The PrimeNG components used are available at https://primeng.org/ and imported in the app.module.ts file. The PrimeFlex style classes can be referred to at https://primeflex.org/
FullCalendar at https://fullcalendar.io/
https://github.com/MPS-Project-23/MPS_Project.git

Integration

Azure Platform offers simplified hosting for Static Web Apps (SWAs) seamlessly using frameworks like React, Angular, Vue, or Blazor. It integrates with Azure Functions, allowing addition of serverless API capabilities to deployed static web apps and native GitHub workflows for CI/CD(Continuous Integration/Continuous Deployment). This feature enables dynamic functionality like database connections using REST API and GraphQL API. The platform offers built-in continuous integration and deployment through GitHub or Azure DevOps. This allows for automated building and deployment of your app directly from your repository every time changes are made to the repository.

The built-in function to deploy the Angular code to static web apps in the Azure platform portal of Visual Studio Code with Azure Static Web App extension installed facilitates no difference when deploying a static web app from the repository between MacOS and Windows.

Using the online demonstration, by modifying the parameters such as app location and output path which can be found in the angular.json file in the repository. After the deployment, there will be action for CI/CD in the GitHub repository which allows the workflow to automatically begin deploying the changes into the static web app every time there is a push to the GitHub.

To fully use the feature of the Azure database, one must connect using the Static Web App’s (SWA) built-in REST and GraphQL data API. SWA CLI and Azure CLI are needed to test the function locally on a MacOS device. The homebrew installation was the most useful and effective method. After the CLI installation and environment setup, the Microsoft Learning page provided instructions to connect locally and later in the Azure portal. The separate repository was used on GitHub to test the data API and fetch functionality and connection. Once the SWA CLI installation is done, the rest of the local connection between the static web app and Azure SQL database is easy; the only thing that needs to be careful is that the database connection configuration file (staticwebapp.database.config.json) must be in its corresponding folder, swa-db-connections, and should be located at the root of the repository.

Testing

In the Testing phase, we mainly performed Usability Testing and Functional Testing. The usability testing, aimed at enhancing user experience for a course registration system, was conducted with three Cornell University students representing diverse academic backgrounds. The testing focused on essential user tasks: navigating the home page, searching courses in the catalog, previewing schedules, adding courses to a wishlist, registering for courses, and accessing the student information dashboard. The evaluation criteria included task completion rate and overall satisfaction. Key findings highlighted issues with clarity and navigation, particularly around main buttons and specific terms. There was a consensus on needing larger text in course descriptions and a more intuitive wish list and enrollment process. The feedback led to recommendations for improving navigation, enhancing text readability, streamlining wish list features, and optimizing the dashboard display to better cater to diverse students’ needs.

Takeaways

Azure provides a useful mechanism for storing and creating databases. We used Azure SQL database and Data API builder and were able to leverage instant REST APIs and GraphQL endpoints. A MacOS user can use the extensions in VSCode or use Azure Data Studio as an easy way to connect to the database. Throughout these 3 months, the team learned several new technologies and methods in a short amount of time. It was crucial to refer to experts in the field and rely on the documentation to gain a deeper understanding of the tools used. Performing research simultaneously with development can save time, however this leads to the development team using their assumptions of the data in the database and ideal organization. Ideally, the research should be done first, followed by the creation of the front-end, and finally, the creation of the database and then link it to the final product.