CCNC/CCNC Module 5/Our example database discussed

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.

High-level entities

• 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

Figure 1. Scenario entities

Entity relationships

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.

Overall schema

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.

Figure 2. Scenario schema

Build your own schema

Activity

re-read the small school scenario Identify all the entities described in the scenario On a piece of paper write all the identified entities Draw arrows between the boxes, identifying the relationships use double headed arrows to show many-to-many relationships use single headed arrows to show one-to-many relationships (the arrow should point toward the one) On a second piece of paper write sentences to describe the relationships On a third piece of paper list the attributes for each entity On a fourth piece of paper design the schema draw the entities with space for the PK, FKs and attributes add _xref_ tables for the many-to-many relationships add all the attributes for each entity identify the attributes that are PK and FK On a fifth piece of paper identify indexes for each entity