OPEN.MICHIGAN Database Project

OPEN.MICHIGAN Database Project

1 | P a g e

Table of Contents

Client Information and Needs ... 2

Users ... 2

Contents ... 3

E-R Diagram ... 4

Queries ... 5

Forms and Reports ... 6

Sustainability ... 7

Team Member Contributions ... 7

Documentation ... 10

2 | P a g e

Client Information and Needs

According to the Open.Michigan website, “Open.Michigan is a University of Michigan initiative that enables faculty, students, and others to share their educational resources and research with the global learning community.”1 _{Currently, the organization publishes open}

educational resources from ten University of Michigan schools. Open educational resources (OER) are educational materials that are openly licensed and offered freely for any one to use, remix, and redistribute.

Open.Michigan is currently working to publish the lecture materials from the first two years (M1/M2) of the University of Michigan Medical School (UMMS) curriculum. The

organization’s publishing assistants currently use Google spreadsheets to track and manage the M1/M2 UMMS project. However, the Google spreadsheets are visually confusing and do not provide easy and accurate reporting functionalities. Open.Michigan wanted to import the Google spreadsheet data into a database that could provide trustworthy tracking, managing, and reporting functionalities. The database that our team created will allow the publishing assistants to retrieve the needed data on the publication of the UMMS curriculum.

Users

Users can be divided into two categories: primary and secondary. Primary users include the publishing assistants and project manager. There are currently 2 primary users. They will use the database to track, manage, and report on the M1/M2 UMMS project. Secondary users include a publishing manager and two OER specialists. There are currently three secondary users. They will use the database’s reporting functionality as a reference tool to assist in decision making for current and future Open.Michigan projects. New types of users are not anticipated.

1

3 | P a g e

Contents

The database tracks the status of UMMS lecture materials acquired and licensed for use by Open.Michigan. It also tracks the history and progress of contact with UMMS faculty members regarding the use of their lecture materials through Open.Michigan. Finally, it tracks who at Open.Michigan is responsible for pursuing permission from which faculty members for what materials.

In accordance with the needs of the Open.Michigan publishing assistants, the database focuses on which materials have received permission for publication and which ones have not. It also reports on the status of this process. Correspondence with faculty regarding materials is also tracked, including the type of correspondence and the date of the correspondence. The database also tracks which Open.Michigan staff member is responsible for which set of materials and contacting which relevant faculty member.

Finally, the database contains data on material names and formats, sequence year, sequence, process status, permission date, license type, date published, publishing

assistant, faculty names, faculty phone numbers, faculty emails, sequence directors, and correspondence type and date. Detailed information on the contents of the database is included in the user manual.

4 | P a g e

E-R Diagram

The final E-R Diagram includes the following tables: FACULTY, FACULTY_MATERIALS, CORRESPONDENCE, and MATERIALS (See Figure 1). The FACULTY table includes: faculty ID, faculty first initial and last name, email address, phone number, and whether or not the faculty member is a sequence director. The FACULTY_MATERIALS table includes the

permission status for each item in MATERIALS. The MATERIALS table tracks the material name, format, type, the sequence it is associated with, the year that the sequence belongs to, the process status of the material, the date that material received permission for

publication if permission was received, the license type assigned to the material, the date it was published, and the publishing assistant responsible for it. Finally, the

CORRESPONDENCE table tracks correspondence date, reason, and type.

5 | P a g e

Queries

We divided our queries into three sections: Materials, Faculty, and Correspondence and Licenses. We were able to write successful SQL code for all of the queries that we planned for. We were not able to create automated alarms, but the functionality to create these in the future remains. Below is a list of our queries by section. All of these queries work.

Materials

1. Show total number of materials published.

2. Show total number of materials published - order by date published. 3. Show materials published - order by date published.

4. Group materials published by sequence. 5. Group by date to make a chart.

6. Show percentage of materials published - order by date published. 7. Show number of materials by sequence and process flow status. 8. Sort materials by process flow status.

9. Show unedited materials. 10. Show materials in editing.

11. Show materials in feedback stage. 12. Show materials in final editing. 13. Show materials in internal review. 14. Show materials in layout review. 15. Show materials in legal review. 16. Show published materials.

17. Show materials where faculty declined to participate. 18. Show overall percentage of materials in queue permission. 19. Show sequence directors and license selected.

20. Show percentage of materials in queue non-permission.

21. Show percentage of materials in queue permission but not yet published. 22. Show percentage of materials in queue permission but not yet published

6 | P a g e

23. Percentage of materials in a sequence already published (as a fraction of total materials desired).

Faculty

1. Show total number of faculty in queue permission. 2. Show total number of faculty in queue non-permission.

3. Show faculty in queue permission by date their permission was received. 4. Show number of faculty who have declined to participate, sort by name

and sequence. Correspondence and Licenses

1. Show total number of emails sent to faculty by date. (# of emails sent per date).

2. Show sequence directors, and sort by name and license selected. 3. Show faculty license selection ordered by license, including names of

faculty.

4. Show totals for each license selected.

Sort faculty by least to most number of times contacted, and order by Sequence.

Forms and Reports

We initially planned to create forms to enter data about faculty, materials, and correspondence. However, in consultation with our client, we realized that these forms would not be necessary because of the small number of people using the database. Instead data can be added to the database through phpMyAdmin. Instructions on how to insert records can be found in the user guide.

Our report page is a dynamic page with widgets that can be re-arranged. When a user first goes to the page he/she sees two static widgets. The one on the left hand side is “Materials Currently at Editing” and will always show the material currently being edited. The one on the top right, “Daily Counts,” will always show the number of published

7 | P a g e

number of faculty participating, and the number of faculty in non-permission. The other two widgets on the right hand side of the page would allow the database users to access information from other SQL queries via drop down menus (See Figures 4-6).

Sustainability

Since Open.Michigan has a full-time IT staff, including programmers, and the client is a member of our team, we are confident that they will be able to sustain the database over time. They will also be able to make changes when and if the need arises. Finally, if they desire additional features, they have the staff to implement them. Our installation and user guides are also meant to ensure sustainability by providing information that staff can access if our client is not available, particularly concerning the significance of the fields.

Additionally, our client plans to provide training on the use of the database that will ensure all Open.Michigan staff knows how to use it and are able to transfer that knowledge to new staff members.

Team Member Contributions

Dave Malicke

At our meetings, I contributed to the discussions and in project management. I worked with the group to produce our project proposal. I collaborated in crafting the ER diagram. I participated in revisions to the ER diagram. I reviewed our progress report. I assisted in refining presentation deliverables. I organized team meetings via doodle polls, created PDF mock ups for the database application with Adobe Illustrator, prepared sets of data, and wrote draft explanations and meanings for the user manual. I also learned a great deal about being a client and communicating needs to a team and answering questions regarding those needs to the best of my ability.

8 | P a g e

At our meetings, I contributed to the discussions and in project management. I worked with the group to produce our project proposal. Additionally I prepared the project proposal for submission. I collaborated in crafting the ER diagram. I participated in

revisions to the ER diagram. I prepared the first draft of the progress report and helped to edit it. I prepared the first draft of our presentation slides and assisted in refining

presentation deliverables. I also created the group website where we could refine our database, as well as test our SQL queries. In learning how databases are created, refined, and implemented I have a much better understanding of how information can be used. Also I believe that the skills I acquired will be helpful in my future career.

Christine Murray

At our meetings, I contributed to the discussions and in project management. I worked with the group to produce our project proposal. I collaborated in crafting the ER diagram. I participated in revisions to the ER diagram. I reviewed our progress report. I assisted in refining presentation deliverables. With Alexis, I wrote SQL for queries and views, substantially improving my skills in that area. I also wrote part of the user manual and assembled the other sections of the manual.

Gin Corden

At our meetings, I contributed to the discussions and in project management. I worked with the group to produce our project proposal. I collaborated in crafting the ER diagram. I participated in revisions to the ER diagram. I reviewed our progress report. I assisted in refining presentation deliverables. I wrote the SQL create and insert code for making our database, and refined the fields as became necessary. I wrote the installation guide and part of the user manual.

Andrew Gordon

I contributed to discussions and overall project management at meetings. In the first half of the project, I helped in producing our project proposal and progress report. I also helped craft and revise the ER diagram, ensuring that the chosen data types were

appropriate and that the relationships reflected the best possible (read: efficient, simple and clean) structuring of the data. I also helped others in the group to refine SQL queries so that they produced the intended results. During the later stages of the project, I invested myself in learning PHP and AJAX in order to develop a web-based database reporting tool.

9 | P a g e

This tool built off of the SQL queries and HTML views others in the group had created. I spent a good amount of time experimenting with various programmatic means by which to access and distribute the database information. I must admit that I was not entirely

successful in producing the intended tool (see documentation section for further information on this). This was due to some information design choices for which there was a lack of a sufficient explanation on how to do across the available references online and in book form (e.g. Using AJAX to operate drop-down menus for a set of SQL queries already baked into separate PHP functions). However, I learned a lot more than I knew before about the relationship between PHP, jQuery and HTML in creating dynamic web-based database applications (such as event and form handling) and the subtle nuances of accessing a database in a useful and efficient manner. Finally, I contributed to drafting and editing the final report.

Alexis Antracoli

At our meetings, I contributed to the discussions and in project management. I worked with the group to produce our project proposal, and collaborated in crafting the ER diagram. I participated in revisions to the ER diagram and reviewed our progress report. I assisted in refining presentation deliverables. I gave the initial presentation and part of the second presentation. With Christine, I wrote SQL queries, and I coded the mockups of possible input screens in HTML and CSS used in the presentation. I also wrote portions of the final report and formatted it for submission.

10 | P a g e

Documentation

Interface, Database, and Styling Files

File Name Description

dashboard.php

File contains all the HTML and PHP code that

renders the dashboard and allows for dynamic

querying of the database in the form of reports.

openmichdb.db

File containing the database structure

openmich_create.sql

File to create tables for database in any SQL-based

DBMS that creates the tables, attributes, data

types, and referential integrity constraints.

openmich_insert.sql

File to insert data into database.

db.inc

File used by the PHP commands to connect to the

database.

widgets.js

Javascript file that provides AJAX functionality for

the widgets in the database so that results can be

pulled into the widgets without refreshing the

entire page or going to another page.

jquery-1.2.6.min.js

JQuery support file containing code to facilitate

AJAX functionality.

jquery-ui-personalized-1.6rc2.min.js

JQuery support file containing code to facilitate

AJAX functionality.

After consulting with our client, we collaboratively decided that the reporting and project management functions of the database would be the most critical elements for our client. Therefore, we chose to focus on creating a sustainable, flexible, and robust database structure to ensure maximum performance for our client’s needs.

Our initial E-R diagram contained four tables: FACULTY, MATERIALS, CORRESPONDENCE, and FACULTY_COMMENTS (See Figure 2). It also included an

intersection table: FACULTY_MATERIALS. FACULTY_COMMENTS was intended to contain comments on the faculty member’s attitudes toward and feedback on the publishing

process. Eventually, because this table was not essential for our client, and because of the complexities of implementation under our time constraints, we decided to drop it.

11 | P a g e

Figure 2

We continued to refine the E-R model after our first draft. The second version contained only three tables and an intersection table, after the removal of

FACULTY_COMMENTS. We also moved the Permission_Status field from the MATERIALS table to the FACULTY_MATERIALS table to track it accurately (See Figure 3). This was necessary because Permission_Status is determined by both the material and the faculty member and because a faculty member may choose different permissions for different materials. This especially may be the case as future semesters are added to the project. Even though there are currently no faculty members with multiple permission choices, our team wanted to account for this possibility as the database grows.

12 | P a g e

Figure 3

The final version of the E-R model included a change in the Permission_Status data type, which we shifted from BOOLEAN to VARCHAR to avoid the null value problem (See Figure 1). Initially Permission_Status values were 0 or 1, with 0 indicating no permission, and 1 indicating permission obtained. However, we realized that this would not allow Open.Michigan to tell the difference between a faculty member who declined to participate and one whose permission has not yet been determined. Consequently, we changed this from BOOLEAN to VARCHAR with three possible enumerated values, allowing us to account for both declined participation and unknown permission status, as well as permission received.

A careful analysis of our client’s data guided several of our data type choices. For example, we chose to use several controlled value fields in order to ensure data

consistency. The most important of these is Process_Status in the MATERIAL table. We chose to use numbers at the beginning of the Process_Status values to assist with logical ordering in queries. For instance, a search for all materials sorted by Process_Status will

13 | P a g e

show the materials with the results in the order in which they are processed rather than alphabetical sorting, which would have been meaningless for our client. Another key decision in our process was the choice of surrogate keys for the FACULTY, MATERIAL, and CORRESPONDENCE tables. Surrogate keys are immutable and make processing more efficient. In the case of the FACULTY table, this decision also ensures that if there are two or more faculty members with the same first initial and last name, it will not negatively impact the functioning of the database.

Additionally, in order to ensure data integrity, we gave thought to useful triggers. For example, so that every material with a publication date would also have a

Process_Status indicating publication, we wrote: delimiter |

CREATE TRIGGER PubDate AFTER UPDATE ON MATERIAL FOR EACH ROW BEGIN

UPDATE MATERIAL SET Process_Status=’8.

converted : published’

WHERE Date_Pub IS NOT NULL; END;

|

delimiter ;

Although we believe this code would have served its purpose, we were unfortunately unable to implement or test it because we lack SUPER USER access to the projects2.si.umich.edu

server and could not acquire it within the time frame of our project. However, it was an interesting exercise in SQL and given additional time and resources, it might have been a useful component of our database design.

The group also attempted to develop a PHP-based web application after refining the structure of our database. We chose to draft a web-based dashboard to dynamically query the Open Michigan database (See Figure 4). We hoped to create a fully functioning

interface that would give immediate access to approximately 30 different queries which our client identified as being most important (See the HTML_SQL.html file). We began

14 | P a g e

to produce a design that would display query results next to each other on the same webpage; therefore we decided to add AJAX functionality (See Figure 4).

Figure 4

15 | P a g e

Figure 6 (example of the License/Contact Reports drop down menu)

This tool has not been completed due to limitations of time and fluency with the interaction between PHP and AJAX (in this case jQuery). Unfortunately we will have to leave completion of the dashboard tool to someone who is more skilled at dynamic web

development.

Finally, due to time constraints and the possibility that this tool might be changed drastically to suit the specific needs of the client organization, we have not created PHP functions for all of the recommended SQL queries as outlined in the HTML_SQL.html file. In order to make the dashboard be fully functional, one would need to edit the

dashboard.php file and widgets.js file in order to achieve a connection between the drop down selection boxes and the PHP functions, which query the database. This would enable the database users to select a specific query from one of the drop down menus, and display the associated SQL query results in the widget. Further queries may be added by following the design pattern for the PHP functions in the dashboard.php file.

16 | P a g e

Client Reflection

At the conclusion of our project, we solicited feedback from our client, who had this to say:

“The Open.Michigan 572 group has built a database that is very valuable for our work at Open.Michigan. Working with the 572 group to build this database really forced Open.Michigan to look at its current process for managing the M1/M2 project. This process, which has generally served as a reference for how Open.Michigan approaches the

management of its other projects, had several kinks that needed to be worked out. These kinks were worked out by collaborating with the 572 group to build a robust database that can handle the many tracking and reporting functions required for the M1/M2 project. In doing so, we have refined Open.Michigan’s project management work flow and have created a solid foundation for the management of other projects within the organization.

Moving forward, Open.Michigan will likely build a new custom application on top of the 572 database, or will use the database as an outline for building a database within its current content management platform. If possible, Open.Michigan will import the

database directly into the content management platform or into a new customer relationship management platform currently under review. In either case, the relational database built to manage the newly refined Open.Michigan project work flow will likely have a far reaching impact on how future projects are managed within the organization.”