CS206 – Database Development

Personal
Development Plan
and Portfolio
Database
Columbia Basin College

Debbie Wolf
12/8/2018
CS206 – Quarter Project

Personal Development Plan and Portfolio Database

1) Industry & Background..........................................................................................................1
► Background .........................................................................................................................1
► Enhancements Provided ....................................................................................................1
2) Requirements ..........................................................................................................................2
► Objective ..............................................................................................................................2
► Database Size ......................................................................................................................2
3) Conceptual & Logical Designs..............................................................................................3
► Entities .................................................................................................................................3
► Relational Schema Mapping ..............................................................................................5
► ERD Diagram .......................................................................................................................6
4) Physical Design ......................................................................................................................7
► Tables ...................................................................................................................................7
1. Assessment....................................................................................................................7
2. Class Assessment .........................................................................................................7
3. Class Curriculum............................................................................................................7
4. Classes...........................................................................................................................7
5. Class Faculty..................................................................................................................8
6. Class Media ...................................................................................................................8
7. Class Student .................................................................................................................8
8. Curriculum ......................................................................................................................8
9. Curriculum Faculty .........................................................................................................8
10. Events ............................................................................................................................9
11. Event Type .....................................................................................................................9
12. Faculty ............................................................................................................................9
13. Faculty Membership.......................................................................................................9
14. Media............................................................................................................................ 10
15. Media Type .................................................................................................................. 10
16. Membership ................................................................................................................. 10
17. Membership Type ........................................................................................................ 10
18. Students ....................................................................................................................... 10

i
CS206 – Quarter Project

19. Training ........................................................................................................................ 11

20. Training Available ........................................................................................................ 11
21. Training Completed...................................................................................................... 11
► Queries ............................................................................................................................... 12
1. Class Assessment Query ............................................................................................ 12
2. Faculty Committee Query ............................................................................................ 12
3. Faculty Curriculum Query ............................................................................................ 12
4. Faculty Query ............................................................................................................... 12
5. Faculty Classes Query................................................................................................. 13
6. Faculty Job Fair Query................................................................................................. 13
7. Faculty Media Query .................................................................................................... 13
8. Faculty Membership Query.......................................................................................... 14
9. Faculty Recruitment Query .......................................................................................... 14
10. Faculty Seminar Query ................................................................................................ 14
11. Faculty Training Completed Query .............................................................................. 15
12. Student Advising Query ............................................................................................... 15
13. Student Classes Query ................................................................................................ 15
14. Student Teacher Query ............................................................................................... 15
15. Training Available Query ............................................................................................. 16
► Forms .................................................................................................................................. 16
1. Add a Class .................................................................................................................. 16
2. Add New Instructor Form ............................................................................................. 16
3. Assessment Form ........................................................................................................ 16
4. Committee Form .......................................................................................................... 16
5. Job Fairs Form ............................................................................................................. 17
6. Media Form .................................................................................................................. 17
7. Organization Form ....................................................................................................... 17
8. Seminars Form............................................................................................................. 17
9. Students Form.............................................................................................................. 17
10. Training Available Form ............................................................................................... 18
11. Training Completed Form ............................................................................................ 18

ii
CS206 – Quarter Project

► Reports ................................................................................................................................ 18
1. Class Assessment Report ........................................................................................... 18
2. Class Curriculum Report.............................................................................................. 18
3. Class Listing ................................................................................................................. 18
4. Class Media Report ..................................................................................................... 18
5. Events Report .............................................................................................................. 19
6. Faculty Advising Report ............................................................................................... 19
7. Class Listing by Faculty ............................................................................................... 19
8. Faculty Committee Report ........................................................................................... 19
9. Faculty Listing by Curriculum Report........................................................................... 19
10. Faculty Recruitment Report ......................................................................................... 19
11. Faculty Report.............................................................................................................. 20
12. Seminars Report .......................................................................................................... 20
13. Media Listing ................................................................................................................ 20
14. Membership Report ..................................................................................................... 20
15. Student Class Listing ................................................................................................... 20
16. Student Listing ............................................................................................................. 20
17. Training Available Report ............................................................................................ 21
18. Training Completed Report.......................................................................................... 21
5) Implementation Notes .......................................................................................................... 21

iii
CS206 – Quarter Project

Personal Development Plan and Portfolio Database

1) Industry & Background

► Background

This database is being written for the faculty of a college which needs a better
way to document personal development records and promotion portfolios.

The people who will be using this database include the faculty and their
assistants. Because a college’s curriculums, classes, and departments are
integrated, it is foreseeable this database will be used campus-wide. The
nature of a college is to provide a learning atmosphere, while the faculty also
participates in outreach programs such as seminars, recruiting, and training
activities.

Information is currently kept in a notebook and file folder. Because much of the
data (faculty names, students, classes, and curriculums) can be mined from
other data systems on campus, the main automation task will be an initial set-
up of individual documentation.

The people using this database will have an education greater than high school.
However, the technology awareness will vary from small to great. Based on
this the database will need to be user friendly.

► Enhancements Provided

One of the processes that will be enhanced by the database is knowing what
kind of training is needed by faculty members, and when that training is
available. The training can then be assigned to a member and a training class
list can be drawn up. The database will keep track of memberships and when
dues are payable. It will also track the seminars given and recruitment activities
participated in by the faculty members.

The database will track the classes required for each curriculum and what
classes each student has taken. This will assist in determining if the student is
eligible for graduation. The database will track the media that is used in the
classes by each instructor, along with the type of assessments each instructor
uses.

Page 1 of 21
CS206 – Quarter Project

All advisors and their students will be located in the database. If a student
doesn’t know who his advisor is, any instructor or assistant will be able to look
up the information and inform the student.

2) Requirements

► Objective
The objective of the project is to give the faculty of the school the ability to
better keep their records of the faculty including but not limited to:
1. Classes
2. Media
3. Training
4. Support (Job Fairs, Seminars)

► Database Size
1. Each class can only have one instructor
2. One instructor may have many classes
3. There can be an infinite number of instructors
4. One media can have one class, but one class can have many media
5. Training, job fairs, seminars are all infinite
6. Each class is limited to its class size

Page 2 of 21
CS206 – Quarter Project Online Project Team December 11, 2008

3) Conceptual & Logical Designs

► Entities

Entity Entity/Business Notes

Assessment Assumes one faculty member responsible for assessment. Each Class can have many
assessments. Each Assessment can apply to many classes.
Class Assumes Prereq list is for display only. Future Development may warrant a circular relationship
to Classes using a Many-to-Many linking table. Each Class Offering can have many Faculty
Members. Each Faculty member can teach many classes. Each Class can have as many
students as defined by “Class_Size”. Each student can be enrolled in many classes.
Curriculum Assumes that Cirriculum is defined as a set of classes to complete a program Equivilent to a
degree or certificate program. Curriculum can be taught by many faculty members. Faculty
members can be involved in many Cirriculum programs. Curriculum can offer many classes
and classes may be part of many curriculum programs.
Event Assumes one faculty member is responsible for an event. Entity is also used to track faculty
involvement in event (hours spent, number of referals). Future development may warrant a
many-to-many relationship with Faculty which will help track any faculty involved in an event
instead of just faculty responsible for the event. This entity stores stores Event data for a
variety of EventTypes and field are up to interpretation depending on the EventType of the
Event.
EventType Provides a more clear definition of an Event entity and the data stored with a particular event.
Programatically this can be used to determine and change the meaning/display of event data
for a given EventType.
Faculty Stores basic faculty information. In this solution, faculty entities are composed into almost
every other entity we deal with. This is due to the fact that the parameters and purpose for this
particular solution was to track faculty records better. Currently Fac_Title is provided using a
text field with a listbox lookup. Further development would warrant breaking this out into a new
table with details for each title including pay-scale and responsibilities.
Media This can be generally defined as any type of required material for a given class. It is assumed
that this most often deals with Books, Workbooks, Videos, Software, etc. A single piece of
media and the properties of that media are further defined by what MediaType is assigned to a
given Media entity. A single entity of this type can be used in many classes and any given
class may make use of many pieces of media.
MediaType Provides a more clear definition of a Media entity and its properties. Different media types may
change the interpretation and display of Media data.

Page 3 of 21
CS206 – Quarter Project Online Project Team December 11, 2008

Entity Entity/Business Notes

Membership Keeps track of general membership groups and faculty responsible for them. Currently
supports MemberShipTypes of Organization and Committee. Future development warrants
tracking meeting data in a separate table as well as a last meeting and next meeting dates.
MembershipType Gives context to Membership entity. Interpretation and display of Membership data may be
altered depending on what MembershipType is assigned to a Membership.
Students Assumes that each student has one and only one faculty member responsible for them. An
overall project guideline was that this solution would be for tracking faculty progress and
records, thus student support is very basic and could easily use some expanding. Each student
is enrolled in a number of classes and each class can handle a number of students. Future
development may include have a student declare a curriculum that they are working on. Also it
may be nice to add an upper cardinality to the number of classes a student can enroll in. It
might also be nice to add support for student and class scheduling so a student can see what
time his classes are at and what location his classes are in.
Training Provides basic description of Training programs available. Currently only provides a description
but easily be expanded to hold other details like Media used for the training, size of each
training session. Our current assumption is that this data will be referenced in a log which will
track faculty training hours. Future development warrants that this table contain only defaults
for planning and log selection purposes. Actual Training description and other details would be
logged in a faculty member’s individual training log for more acurate auditing in the future and to
perserve log integrity if training program’s details happen to change or be deleted.
TrainingAvailable Provides a list of basic information on scheduling data for training sessions. Our current
assumption is that this data will be referenced in a log which will track faculty training hours.
Future development warrants that this table contain only defaults for planning and log selection
purposes. Separating the referential link between the log and this entity would also allow data
in this entity to be updated without worrying about changing the audit integrity of the log. Items
no longer offered could be deleted. Requirements and defaults could be changed without
effecting the contents of the log.
TrainingCompleted Provides a basic audit trail of training completed by faculty members. Due to the historic nature
of data in this table, future development would warrant a bit of “denormalizing” this entity to
provide a strong audit history. Class description, trainer, Credit earned fields could be added
here to make the log more permanent and less suseptible to changes implented to
TrainingAvailable entities. This way if classes are deleted or renamed or trainers reassigned or
hours required changed in TrainingAvailable, the log of what “actually” happened for the training
of a particular faculty member would stay in tact.

Page 4 of 21
CS206 – Quarter Project Online Project Team December 11, 2008

► Relational Schema Mapping

Assessment(Ast_Code, Ast_Type)
ClassAssessment(CA_ID, Class_Code, Ast_Code, Ast_Pct, Fac_Code)
ClassCurriculum(CC_ID, Class_Code, Curr_Code)
Classes(Class_Code, Class_Number, Class_Prev_Number, Class_Name, Class_PreReq, Class_Quantity, Class_Credit,
Class_Size)
ClassFaculty(ClassFacultyID, Class_Code, Faculty_Code)
ClassMedia(CM_ID, Class_Code, Media_Code)
ClassStudent(ClassStudentID, Class_Code, Student_Code)
Curriculum(Curr_Code, Curr_Name, Curr_Notes)
CurriculumFaculty(CurrFacID, Curr_Code, Fac_Code)
Events(EventID, EventTypeID, Fac_Code, Location, Date_of_Event, Topic, HoursSpent, SchoolsVisited, Referrals/Attendence)
EventType(EventTypeID, EventTypeDesc)
Faculty(Fac_Code, Fac_Title, Fac_FName, Fac_LName, Fac_Office, Fac_EMail, Fac_Phone, Fac_HireDate, Adv_Hours)
FacultyMembership(FM_ID, Fac_Code, Membership_ID, Years_Served)
Media(Media_Code, Media_TypeID, Media_Title, Media_Version, Media_Current, Media_ISBN)
MediaType(MediaTypeID, MediaTypeDescription)
Membership(MemberID, Member_TypeID, GroupTitle, Location, TermLength, ExpirationDate)
MembershipType(Membership_TypeID, Membership_TypeDesc)
Students(Student_Code, StudentID, St_FName, St_LName, Fac_Code)
Switchboard Items(SwitchboardID, ItemNumber, ItemText, Command, Argument)
Training(Trn_ID, Trn_Code, Training_Type)
TrainingAvailable(TA_ID, Trn_ID, Date_Available, Duration, Tra_Hours_Acquired)
TrainingCompleted(TC_ID, Trn_ID, Fac_Code, Date_Completed)

Page 5 of 21
CS206 – Quarter Project Online Project Team December 11, 2008

► ERD Diagram

Page 6 of 21
CS206 – Quarter Project Online Project Team December 11, 2008

4) Physical Design

► Tables
1. Assessment
• Asst_Code
- Primary key
• Ast_type

2. Class Assessment
• CA_ID
- Primary Key
• Class_Code
• Ast_Code
- Foreign Key
• Ast_Pct
• Fac_Code

3. Class Curriculum
• CC_ID
- Primary Key
• Class_Code
• Curr_Code

4. Classes
• Class_Code
- Primary Key
• Class_Number
• Class_Prev_Number
• Class_Name
• Class_Prereq
• Class_Quantity
• Class_Credit
• Class_Size

Page 7 of 21
CS206 – Quarter Project Online Project Team December 11, 2008

5. Class Faculty
• Class_FacultyID
- Primary Key
• Class_Code
- Foreign Key
• Faculty_Code
- Foreign key

6. Class Media
• CM_ID
- Primary Key
• Class_Code
- Foreign Key
• Media_Code

7. Class Student
• ClassStudentID
- Primary Key
• Class_Code
- Foreign Key
• Student_Code
- Foreign Key

8. Curriculum
• Curr_Code
- Primary Key
• Curr_Name
• Curr_Notes

9. Curriculum Faculty
• CurrFacID
- Primary Key
• Curr_Code
- Foreign Key
• Fac_Code
- Foreign Key

Page 8 of 21
CS206 – Quarter Project Online Project Team December 11, 2008

10. Events
• Event_ID
- Primary Key
• EventTypeID
- Foreign Key
• Fac_Code
- Foreign Key
• Location
• Date_Of_Event
• Topic
• Hours_Spent
• Schools_Visited
• Referrals/Attendance

11. Event Type

• Event_TypeID
- Primary Key
• Event_Type_Desc

12. Faculty
• Fac_Code
- Primary Key
• Fac_Title
• Fac_Fname
• Fac_Lname
• Fac_Office
• Fac_Email
• Fac_Phone
• Fac_Hire_Date
• Adv_Hours

13. Faculty Membership

• FM_ID
• Fac_Code
• Membership_ID
• Years_Served

Page 9 of 21
CS206 – Quarter Project Online Project Team December 11, 2008

14. Media
• Media_Code
- Primary key
• Media_TypeID
- Foreign Key
• Media_Title
• Media_Version
• Media_Current
• Media_ISBN

15. Media Type

• Media_TypeID
- Primary Key
• Media_Type_Desc

16. Membership
• MemberID
- Primary Key
• Member_TypeID
- Foreign Key
• Group_Title
• Location
• Term_Length
• Expiration_Date

17. Membership Type

• Membership_TypeID
- Primary Key
• Member_Ship_Type_Desc

18. Students
• Student_Code
- Primary Key
• StudentID
• St_FName
• St_LName
• Fac_Code
- Foreign Key

Page 10 of 21
CS206 – Quarter Project Online Project Team December 11, 2008

19. Training
• Trn_ID
• Trn_Code
• Training_Type

20. Training Available

• TA_ID
- Primary Key
• Trn_ID
• Date_Available
• Duration
• Tra_Hours_Acquired

21. Training Completed

• TC_ID
- Primary Key
• Trn_ID
- Foreign Key
• Fac_Code
- Foreign Key
• Date_Completed

Page 11 of 21
CS206 – Quarter Project Online Project Team December 11, 2008

► Queries
1. Class Assessment Query
• Class_Code
• Class_Name
• Fac_Code
• Ast_Type
• Ast_Pct

2. Faculty Committee Query

• Fac_Code
• Name
- Combination of two fields, first name and last name, so it displays in
one field.
• Membership_Type_Desc
• Membership_ID
• Group_Title
• Years_Served
• Expiration_Date
• Membership_TypeID
- This is for sorting purposes only.

3. Faculty Curriculum Query

- This is a grouping query.
• Name
- Combination of two fields, first name and last name, so it displays in
one field.
• Curr_Name
• Fac_Code

4. Faculty Query
• Name
- Combination of two fields, first name and last name, so it displays in
one field.
• Fac_Office
• Fac_Phone
• Fac_Email

Page 12 of 21
CS206 – Quarter Project Online Project Team December 11, 2008

5. Faculty Classes Query

• Fac_Code
• Name
- Combination of two fields, first name and last name, so it displays in
one field; sorted ascending.
• Class_Number
- Sorted ascending.
• Class_Code
• Class_Name
• Class_Credit
• Class_Size

6. Faculty Job Fair Query

• Name
- Combination of two fields, first name and last name, so it displays in
one field.
• Event_Type_Desc
- Limited to “Job Fair”
• Location
• Date_Of_Event
• Referrals/Attendance

7. Faculty Media Query

- This is a grouping query.
• Class_Number
• Class_Name
• Media_Type_Description
• MediaTitle
• Media_ISBN
• Media_Version
• Media_Current

Page 13 of 21
CS206 – Quarter Project Online Project Team December 11, 2008

8. Faculty Membership Query

• Fac_Code
• Name
- Combination of two fields, first name and last name, so it displays in
one field.
• Membership_ID
• Group_Title
• Expiration_Date
• Years_Served
• Membership_TypeID
- Limited to “2” which is Organizations

9. Faculty Recruitment Query

• Name
- Combination of two fields, first name and last name, so it displays in
one field.
• EventTypeDesc
- Limited to “College Recruitment”
• Location
• Date_Of_Event
• Hours_Spent
• Referrals/Attendance

10. Faculty Seminar Query

• Name
- Combination of two fields, first name and last name, so it displays in
one field.
• Location
• Date_Of_Event
• Topic
• Referrals/Attendance
• Event_Type_Desc
- Limited to “Seminar”

Page 14 of 21
CS206 – Quarter Project Online Project Team December 11, 2008

11. Faculty Training Completed Query

• Fac_Code
• Name
- Combination of first name and last name for Teachers
• Fac_HireDate
• Trn_Code
• Training_Type
• Date_Completed

12. Student Advising Query

• Name
- Combination of two fields from the Faculty table, first and last name,
and it displays in one field
• StudentID
• StudentName
- Combination of two fields from the Students table, first and last name,
and it displays in one field.
• Adv_Hours

13. Student Classes Query

• StudentID
• Name
- Combination of two fields from the Students table, first and last name,
and it displays in one field.
• Class_Code
• Class_Number
• Class_Name
• Class_Credit

14. Student Teacher Query

• StudentID
• Name
- Combination of two fields from the Students table, first and last name,
and it displays in one field.
• Faculty Name
- Combination of two fields from the Faculty table, first and last name,
and it displays in one field

Page 15 of 21
CS206 – Quarter Project Online Project Team December 11, 2008

15. Training Available Query

• Trn_Code
• Training_Type
• Date_Available
• Duration
• Trn_Hours_Acquired

► Forms
1. Add a Class
• Class Number
• Previous Number
• Class Name
• Class Pre-Req
• Class Quantity
• Class Credit
• Class Size

2. Add New Instructor Form

• Title
• First Name
• Last Name
• Office
• E-Mail
• Phone
• Hire Date
• Advising Hours

3. Assessment Form
• Class
• Faculty
• Assessment Type
• Percentage of Grade

4. Committee Form
• Faculty Name
• Committee Title
• Expiration Date
• Years Served

Page 16 of 21
CS206 – Quarter Project Online Project Team December 11, 2008

5. Job Fairs Form

• Faculty Code
• Location
• Date of Event
• Hours Spent
• Referrals

6. Media Form
• Type
• Title
• Version
• Current?
• ISBN

7. Organization Form
• Faculty Name
• Organization Title
• Expiration Date
• Years Served

8. Seminars Form
• Faculty Name
• Location
• Date of Event
• Topic
• Attendance

9. Students Form
• Student ID
• First Name
• Last Name
• Advisor

Page 17 of 21
CS206 – Quarter Project Online Project Team December 11, 2008

10. Training Available Form

• Training Code
• Training Type
• Date Available
• Duration
• Hours Acquired

11. Training Completed Form

• Faculty Code
• Training Code
• Date Completed

► Reports
1. Class Assessment Report
• Class Code
• Class Name
• Faculty Name
• Assessment Type
• Percent of Final Grade

2. Class Curriculum Report

• Curriculum
• Class Number
• Class Name

3. Class Listing
• Class Number
• Class Name
• Class Credit
• Class Size

4. Class Media Report

• Class Number
• Class Name
• Media Type
• Media Title
• Media ISBN
• Version
• Current?

Page 18 of 21
CS206 – Quarter Project Online Project Team December 11, 2008

5. Events Report
• Event Type
• Faculty Name
• Location
• Date Of Event
• Referrals/ Attendance
• Topic

6. Faculty Advising Report

• Name
• Advising Hours
• Student ID
• Student Name

7. Class Listing by Faculty

• Name
• Class Number
• Class Name
• Class Credit
• Class Size

8. Faculty Committee Report

• Committee Title
• Name
• Years Served
• Term Expiration Date

9. Faculty Listing by Curriculum Report

• Curriculum
• Name

10. Faculty Recruitment Report

• Location
• Date of Event
• Name
• Hours Spent
• Referrals/Attendance

Page 19 of 21
CS206 – Quarter Project Online Project Team December 11, 2008

11. Faculty Report

• First Name
• Last Name
• Office
• E-mail Address
• Phone

12. Seminars Report

• Name
• Location
• Date of Event
• Topic
• Referrals/Attendance

13. Media Listing

• Type
• Title
• ISBN
• Version
• Current?

14. Membership Report

• Organization Title
• Name
• Expiration Date

15. Student Class Listing

• Student ID
• Name
• Class Number
• Class Name
• Class Credit

16. Student Listing

• Student ID
• First Name
• Last Name
• Advisor

Page 20 of 21
CS206 – Quarter Project Online Project Team December 11, 2008

17. Training Available Report

• Training Code
• Type
• Date Available
• Duration
• Hours Acquired

18. Training Completed Report

• Training Code
• Training Type
• Name
• Date Completed

5) Implementation Notes

There is currently no security set on this database which would allow anyone
(including students and non-college personnel) access to the personal data of
individuals. Access should be limited to only those persons with a “need to know”.
Those persons could be the professors and/or their assistants. The access should
include a username with a strong encrypted password that expires.

In order to implement a database that would serve the entire college campus, it
would need to be installed on a server and allow multiple people to access it at the
same time. To prevent a record being changed while someone else is editing it, the
database should incorporate pessimistic locking of records. While this may cause
some issues if a person opens a record for an extended period of time, it will cause
fewer headaches in the long run when trying to decide which changes to keep.

This database is an initial start on what can be a very useful and time-saving
program. There are still some refinements that will prove helpful in addition to the
above mentioned security and access issues.

Page 21 of 21