LIS 688 – Database Design Final Project, Group 4 Student Tracking Database This report provides the documentation for the student tracking database produces by group 4 for the final project for LIS 688 – Database Design for the Fall term of 2011 at the University of North Carolina at Greensboro.
Jeremy D. Lytal; James Peterson; Anne Silva UNC Greensboro
LIS 688 – Database Design Final Project, Group 4
1
Table of Contents Background Information and Requirements................................................................................................. 2 Business Rules ............................................................................................................................................... 2 Entity Relationship Diagram .......................................................................................................................... 3 Data Dictionary .............................................................................................................................................. 3 STUDENT.................................................................................................................................................... 3 COURSE...................................................................................................................................................... 4 ADVISOR .................................................................................................................................................... 4 ENROLLMENT ............................................................................................................................................ 4 SQL Statements and Queries......................................................................................................................... 5 Basic SQL ................................................................................................................................................... 5 SQL Queries ............................................................................................................................................... 6 Evaluation ...................................................................................................................................................... 7
UNC Greensboro | LIS 688 – Lytal, Peterson, Silva
LIS 688 – Database Design Final Project, Group 4
2
Background Information and Requirements Currently, the Department of Library and Information Studies (DLIS) at the University of North Carolina and Greensboro (UNCG) has only limited ability to track the progress that students are making through the Masters of Library and Information Science (MLIS) degree as well as how close students may be to graduating. This unfortunate circumstance is due to a University-wide database system that does not adequately fulfill the DLIS’s needs. Thus, this final project for Dr. Fatih Oguz’s LIS 688 – Seminar in Selected Topic – Database Design course is to design a student tracking database that will more adequately allow the DLIS to monitor students’ progress. This will provide the DLIS with several benefits. Firstly, advisers will be able to easily see which required courses their students have taken, and which they have not. It will advisors to see how many hours that students need to complete their degrees. Secondly, it will allow the department to see how many students are close to graduating for a particular term. Finally, it can also show which core classes are still needed by students close to graduation so that the DLIS can plan it’s course offerings appropriately. A few requirements that the DLIS would need from this database are: •
• •
Students who are within one semester of graduating (for full-time students, this equates to 30 hours) must be able to be identified. This will be accomplished through a query, the SQL statement for which will be detailed in the appropriate section below. The core classes that a student has taken must be identifiable. The class that a student takes to fulfill the DLIS’s technology requirement must be identifiable.
Business Rules The following business rules guided the shape and structure of the database. They also help to determine and develop the Entity Relation Diagram (ERD) that is to follow, which serves as a model for the database. The business rules for this project are: • • • • •
A student may enroll anywhere from no classes to many classes in a single term. A professor may teach anywhere from no classes to many classes in a single term. A class may have up to one professor assigned to it, but classes just added to the schedule do not necessarily have a professor assigned to them yet. A student may have up to one adviser, but new student or students whose advisor has retired or left UNCG need may not have advisors assigned to them yet. A professor may advise anywhere from no students to many students.
UNC Greensboro | LIS 688 – Lytal, Peterson, Silva
LIS 688 – Database Design Final Project, Group 4
3
Entity Relationship Diagram
The above diagram, called an Entity Relationship Diagram or ERD, shows how the different entities that make up this database related to one another. There are three basic entities: STUDENTS – This entity represents the students that are enrolled in the DLIS’s MLIS program. COURSES – This entity represents the course that the DLIS may offer. It includes required core courses, all possible technology courses, and all electives ADVISORS – This entity represents advisors in the DLIS As shown above, the relationship between students and advisors is straight forward as one advisor may advise many students, but a student may only have one advisor. However, relationship between students and courses is a bit more awkward. Because many students may be enrolled in an one course, and any one student may take many courses, the resulting relationship is called many to many. In the relational database environment, many to many relationships are impossible to implement directly. Instead, they must be broken down into two one to many relationships. This is accomplished though a bridge table. For this project, we have name our bridge table ENROLLMENT. This table connects students and courses.
Data Dictionary The following tables define the various attributes belonging to each entity representing in this project, as well as the type of data they represent.
STUDENT Attribute STU_ID
Date Type Number
Description This is a unique identification number for each student. No two students may have the same number.
(Primary Key) STU_LN
Text
The student’s last name. UNC Greensboro | LIS 688 – Lytal, Peterson, Silva
4
LIS 688 – Database Design Final Project, Group 4
STU_FN TOTAL_CREDITS
Text Calculated Number
GRADUATED
Yes/No
SLM
Yes/No
ADV_ID
Number
The student’s first name. This filed represents the total number of credits that a student has taken in the MLIS program. It is a calculated field derived from data from the ENROLLMENT and COURSE tables. The SQL statement for this calculation will be given in the appropriate section below. This field allows the database to exclude students who have graduated from queries while still maintaining the historical accuracy of the database This field denotes whether or not a student is in the School Library Media program. This links to the ADVISOR table and indicates which professor advises each student.
(Foreign Key)
COURSE Attribute COURSE_ID (Primary Key)
COURSE_NUMBER COURSE_NAME CREDIT_HOURS CORE
Data Type Number
Description A unique identifier for each course. While in most instances, the course number would be acceptable to uniquely identify each course, there are a few courses, such as LIS 688, where the course number may actually refer to several different courses. As such, a separate course ID number if used. Number(3) The designated three digit number for each course. Text The name of each course. Number(1) The number of credit hours for each course. Yes/No Whether or not this is a core course.
ADVISOR Attribute ADV_ID
Data Type Number
Description A unique identifier for each advisor.
(Primary Key) ADV_LN ADV_FN ADV_EMAIL
Text Text Hyperlink
The advisor’s last name. The advisor’s first name. The advisor’s e-mail address.
Data Type Number
Description Identifier that links back to the student table. While each ID number links to one and only one student, because students can take many
ENROLLMENT Attribute STU_ID
UNC Greensboro | LIS 688 – Lytal, Peterson, Silva
LIS 688 – Database Design Final Project, Group 4 (Primary Key) (Foreign Key) STU_LN STU_FN COURSE_NUMBER COURSE_ID (Primary Key) (Foreign Key) GRADE CREDIT_HOURS SEMESTER YEAR
5
courses, each number may appear in this table multiple times. However, the same combination of STU_ID and COURSE_ID may not appear twice. Text The Student’s last name. Text The Student’s first name Number The Course Number Number Identifier that links back to the course table. While each ID number links to one and only one course, because course can be taken by many students, each number may appear in this table multiple times. However, the same combination of STU_ID and COURSE_ID may not appear twice. Text The grade that the student received for the course. Number(1) The credit hours that the student receives for the course Text The semester in which the student took the class Date The year in which the student took the class.
SQL Statements and Queries Basic SQL While this project was created using Microsoft Access, which contains a number of useful interface features that reduce the importance of being able to manipulate the database and its various tables and data using SQL, a basic understanding of SQL can still greatly help users in accessing the material contained in the database. Some basic SQL commands are: • • • • •
•
CREATE table [table name] ([Attribute] [data type] primary key, [Attribute] [data type], etc.); o This is used to create a new table. DROP [table name]; o This is used to delete a table. ALTER table [table name] ADD [column name] [data type]; o This is used to add a column. ALTER table [table name] DROP COLUMN [column name]; o This is used to delete a column. INSERT INTO [table name] ([column name], [column name], etc.) VALUES ([value], [value], etc.); o This is used to insert a new record into a table. o Note: If the value you are inserting is text, enclose is in single quotation makes; Ex: ‘Smith’. DELETE FROM [table name] WHERE [column name]=[value]; o This is used to delete a record from a table. o Note: If the value you are deleting is text, enclose is in single quotation makes; Ex: ‘Smith’. o Be sure that a WHERE statement has been specified. Otherwise, all records will be deleted! UNC Greensboro | LIS 688 – Lytal, Peterson, Silva
LIS 688 – Database Design Final Project, Group 4
6
•
UPDATE [table name] SET [column name]=[value] WHERE [column name]=[value]; o This is used to update information for a particular attribute. o Note: If the value you are deleting is text, enclose is in single quotation makes; Ex: ‘Smith’. o Be sure that a WHERE statement has been specified. Otherwise, all records will be updated!
SQL Queries •
•
•
•
•
SELECT ENROLLMENT.STU_ID, ENROLLMENT.STU_LN, ENROLLMENT.STU_FN, STUDENT.ADV_ID, [Total Credit Hours].TOTAL_CREDITS, STUDENT.GRADUATED FROM (STUDENT INNER JOIN ENROLLMENT ON STUDENT.STU_ID = ENROLLMENT.STU_ID) INNER JOIN [Total Credit Hours] ON STUDENT.STU_ID = [Total Credit Hours].STU_ID WHERE ((([Total Credit Hours].TOTAL_CREDITS)>=27)) GROUP BY ENROLLMENT.STU_ID, ENROLLMENT.STU_LN, ENROLLMENT.STU_FN, STUDENT.ADV_ID, [Total Credit Hours].TOTAL_CREDITS, STUDENT.GRADUATED HAVING (((STUDENT.GRADUATED)=No)); o This query will retrieve students who have not graduated, but who have taken more than 30 credit hours. SELECT ENROLLMENT.STU_ID, ENROLLMENT.STU_LN, ENROLLMENT.STU_FN, ENROLLMENT.COURSE_NUMBER, COURSE.COURSE_NAME, ENROLLMENT.COURSE_ID, ADVISER.ADV_ID, ENROLLMENT.SEMESTER, ENROLLMENT.YR FROM (ADVISER INNER JOIN STUDENT ON ADVISER.ADV_ID = STUDENT.ADV_ID) INNER JOIN (COURSE INNER JOIN ENROLLMENT ON COURSE.COURSE_ID = ENROLLMENT.COURSE_ID) ON STUDENT.STU_ID = ENROLLMENT.STU_ID WHERE (((COURSE.CORE)=Yes) AND ((STUDENT.GRADUATED)=No)); o This query will show what core course students currently enrolled in the MLIS program have completed. SELECT ENROLLMENT.STU_ID, ENROLLMENT.STU_LN, ENROLLMENT.STU_FN, ADVISER.ADV_ID, ENROLLMENT.COURSE_NUMBER, ENROLLMENT.COURSE_ID, ENROLLMENT.SEMESTER, ENROLLMENT.YR FROM (ADVISER INNER JOIN STUDENT ON ADVISER.ADV_ID = STUDENT.ADV_ID) INNER JOIN (COURSE INNER JOIN ENROLLMENT ON COURSE.COURSE_ID = ENROLLMENT.COURSE_ID) ON STUDENT.STU_ID = ENROLLMENT.STU_ID WHERE (((COURSE.TECH_RQ)=Yes)); o This query will show student who have completed their technology requirement. SELECT ENROLLMENT.STU_ID, ENROLLMENT.STU_LN, ENROLLMENT.STU_FN, ADVISER.ADV_ID, Sum(ENROLLMENT.CREDIT_HOURS) AS TOTAL_CREDITS FROM ADVISER INNER JOIN (STUDENT INNER JOIN ENROLLMENT ON STUDENT.STU_ID = ENROLLMENT.STU_ID) ON ADVISER.ADV_ID = STUDENT.ADV_ID GROUP BY ENROLLMENT.STU_ID, ENROLLMENT.STU_LN, ENROLLMENT.STU_FN, ADVISER.ADV_ID; o This Query will show the total number of credit hours that a student has taken in the MLIS program. o It can be easily modified for individual students by adding WHERE STUDENT.STU_ID = [student’s ID] immediately prior to the “Group By” phrase. SELECT ENROLLMENT.STU_ID, ENROLLMENT.STU_LN, ENROLLMENT.STU_FN, COURSE.COURSE_NUMBER, COURSE.COURSE_NAME, COURSE.COURSE_ID, STUDENT.ADV_ID, [Total Credit Hours].TOTAL_CREDITS FROM COURSE INNER JOIN ((STUDENT INNER JOIN ENROLLMENT ON STUDENT.STU_ID = ENROLLMENT.STU_ID) INNER JOIN [Total Credit Hours] ON UNC Greensboro | LIS 688 – Lytal, Peterson, Silva
LIS 688 – Database Design Final Project, Group 4
•
7
STUDENT.STU_ID = [Total Credit Hours].STU_ID) ON COURSE.COURSE_ID = ENROLLMENT.COURSE_ID WHERE ((([Total Credit Hours].TOTAL_CREDITS)>=27)) GROUP BY ENROLLMENT.STU_ID, ENROLLMENT.STU_LN, ENROLLMENT.STU_FN, COURSE.COURSE_NUMBER, COURSE.COURSE_NAME, COURSE.COURSE_ID, STUDENT.ADV_ID, [Total Credit Hours].TOTAL_CREDITS, STUDENT.GRADUATED, COURSE.CORE HAVING (((STUDENT.GRADUATED)=No) AND ((COURSE.CORE)=Yes)); o This query will show the core course completed by students who have also completed 27 or more hours in the program and are thus close to graduating. SELECT ENROLLMENT.STU_ID, ENROLLMENT.STU_LN, ENROLLMENT.STU_FN, ADVISER.ADV_ID, ENROLLMENT.COURSE_NUMBER, ENROLLMENT.COURSE_ID, [Total Credit Hours].TOTAL_CREDITS, ENROLLMENT.SEMESTER, ENROLLMENT.YR FROM ((ADVISER INNER JOIN STUDENT ON ADVISER.ADV_ID = STUDENT.ADV_ID) INNER JOIN (COURSE INNER JOIN ENROLLMENT ON COURSE.COURSE_ID = ENROLLMENT.COURSE_ID) ON STUDENT.STU_ID = ENROLLMENT.STU_ID) INNER JOIN [Total Credit Hours] ON (ADVISER.ADV_ID = [Total Credit Hours].ADV_ID) AND (STUDENT.STU_ID = [Total Credit Hours].STU_ID) WHERE ((([Total Credit Hours].TOTAL_CREDITS)>=27) AND ((COURSE.TECH_RQ)=Yes) AND ((STUDENT.GRADUATED)=No)); o This query will show students who have completed more than 27 hours in the program and have also completed their technology requirement.
Evaluation In many ways, this was a challenging project. Only one group member had any prior experience with Microsoft Access, and that was limited. The other two group members had no prior experience with either Access or SQL code. An early challenge that we faced was the fact that all three group members lived rather too far apart for any sort of in person collaboration. As such, we had to communicate through e-mail and a Google Docs. Compounding this was that we were using Microsoft Access and Dia. Unfortunately, there was not a way to work collaboratively online with these tools. This made communicating about the database structure more difficult than it would have been had we been in a face to face environment. However, we were able to work together and overcome these issues. Ultimately, we ended up dividing the work up into separate chunks. James Peterson created the ERD and the business rules, as well as assisting with the SQL and providing substantive feedback throughout. Anne Silva, who had some limited experience with Access, created the Access file and wrote the SQL queries, as well as inputting the data. Jeremy Lytal assisted with the business rules and the SQL in small ways, as well as providing feedback on the Access file. He also wrote the report, with significant feedback from both Anne and James.
UNC Greensboro | LIS 688 – Lytal, Peterson, Silva