CCNC/CCNC Module 5/Our example database discussed
- 1 Scenario database design
- 1.1 Small school scenario
- 1.2 High-level entities
- 1.3 Entity relationships
- 1.3.1 People to Person_Type
- 1.3.2 People to Demographics
- 1.3.3 People to Addresses
- 1.3.4 People to Contact_Methods
- 1.3.5 People to Cohorts
- 1.3.6 People to Assessment_Event
- 1.3.7 Cohorts to Cohort_Type
- 1.3.8 Cohorts to Module_Occurrence
- 1.3.9 Module_Occurrence to Curriculum_Modules
- 1.3.10 Curriculum_Modules to Module_Subject
- 1.3.11 Curriculum_Modules to Module_Outcomes
- 1.3.12 Curriculum_Modules to Module_Clusters
- 1.3.13 Assessment_Item to Curriculum_Modules
- 1.3.14 Assessment_Item to Assessment_Item_Type
- 1.3.15 Assessment_Item to Assessment_Event
- 2 Overall schema
Scenario database design
Small school scenario
We created an anchor scenario for this module. The idea of the scenario is to anchor the design and creation of a database to a consistent theme. The theme we chose was a small school. The following description is of the database schema we designed based on this scenario. Follow this link to retrieve a copy on the small school scenario.
- People - students, teachers, parent volunteers, all professional help and required contacts
- Person_Type - the type of person; a student, a teacher... (should be flexible data structure so a person can be both a student and teacher at the same time)
- Demographics - a persons birth date, gender, etc. (any other relevant family or community information)
- Cohorts - a data structure for organizing groups. primarily to organize a group of students working on the same module_occurrence.
- Cohort_Type - the type of cohort (student grade, parents, teacher committee, etc.)
- Addresses - 1 to many for each person (cannot be zero)
- Contact_Methods - 1 to many for each person; includes, phone, email, IRC, MSN, etc. (cannot be zero)
- Curriculum_Modules - learning modules as described by learning objectives
- Module_Subject - subject (math, reading, etc.) or continuing education subject grouping
- Module_Outcomes - learning outcomes for the module
- Module_Clusters - grade level or other identified competency level to associate with a cluster of curriculum modules)
- Module_Occurrence - the execution of a module in time, this allows the assignment of teachers, students and resources to a particular module for a particular period of time
- Assessment_Item - an item of assessment, should link back to a curriculum_module
- Assessment_Item_Type - the type of assessment; quizzes, assignments, exams, presentations, projects, peer assessments, etc.
- Assessment_Event - a student completing an assessment item, should include grade
The following high level relationships have been identified. It should be noted that some of these relationships are many-to-many, and will therefore be implemented as two one-to-many relationships when the database is actually built. Describing the entity relationships at this level is for design purposes only. The physical implementation will be different as shown in the completed schema.
People to Person_Type
People to Person_Type is a many-to-many relationship as a person can be a student, a parent and a teacher. Therefore a person can have many types. the reciprocal relationship exists in that a Person_Type can have many people. As an example, the student Person_Type can be held by many People.
People to Demographics
People to Demographics is a one-to-many relationship as a person will have many demographic attributes. Some of the attributes will be the same among many people (like birth date, language or gender). yet each demographic attribute is "attached" to the Person.
People to Addresses
People to Addresses is a many-to-many relationship as a person can have more than one address (home, work, mailing) and an address may be occupied (shared) by more than one person.
People to Contact_Methods
People to Contact_Methods is a one-to-many relationship where a person can have many methods to contact them (email, phone, pager, text message, etc.)
People to Cohorts
People to Cohort is a many-to-many relationship as a person can belong to many cohorts and a cohort can have many members. The idea of the cohort is to gather together a group of people who form a class.
People to Assessment_Event
People to Assessment_Event is a many-to-many relationship as a person can complete many assessments and a particular assessment can be completed by many people. The Assessment_Event is different than the Assessment_Item in the Event is the actual completed assessment with a corresponding grade, where the Item is only the described assessment approach or actual test, quiz, assignment, etc.
Cohorts to Cohort_Type
Cohorts to Cohort_Type is a one-to-many relationship in that a cohort can only be of one type, yet many cohorts can share the same type. An example would be "Grade 5" being a cohort that has many members and will be used over again for each new "Grade 5" cohort.
Cohorts to Module_Occurrence
Cohorts to Module_Occurrence is a many-to-many relationship for a cohort can engage in many modules (example: The "Grade 5" cohort requires many modules to complete the grade 5 curriculum) and a module can be used by many different cohorts (example: the grade 7 math module may be used by both the "Grade 7" and "Water Management" Cohorts).
Module_Occurrence to Curriculum_Modules
Module_Occurrence to Curriculum_Modules is a one-to-many relationship for a curriculum module can be used many times, yet a module occurrence must be based upon a single curriculum module.
Curriculum_Modules to Module_Subject
Curriculum_Modules to Module_Subject is a many-to-many relationship for a curriculum module can exist within many different subjects (example: grade 4 reading module may be used by both the "Grade 4" and "Tourism" subjects) and a subject may consist of many modules (example: the math subject would include all K7 math modules).
Curriculum_Modules to Module_Outcomes
Curriculum_Modules to Module_Outcomes is a one-to-many relationship for a curriculum module can have many learning outcomes.
Curriculum_Modules to Module_Clusters
Curriculum_Modules to Module_Clusters is a one-to-many relationship for a module-cluster is a collection of modules that complete a program or grade. As an example, if a program on "Water Management" required seven curriculum modules for program completion, these seven modules would be the cluster.
Assessment_Item to Curriculum_Modules
Assessment_Item to Curriculum_Modules is a one-to-many relationship for a curriculum module can have many assessment items, yet an assessment item can be used with only one module.
Assessment_Item to Assessment_Item_Type
Assessment_Item to Assessment_Item_Type is a one-to-many relationship for an assessment item can only be of a certain type (example: quiz, assignment, participation, etc.)
Assessment_Item to Assessment_Event
Assessment_Item to Assessment_Event is a one-to-many relationship for an assessment event can be based on only one assessment item.
The overall schema provided offers a potential solution for the described scenario. Additional attributes would be required to complete the design and some further normalization could provide a more comprehensive design.
Build your own schema