ERD/DFD Assignment Sample Solutions

In the accompanying lab session, students were also directed to describe any assumptions they found it necessary to make and to provide a list of questions for the client, to cover areas where the description seemed unclear or incomplete.

Original problem description Timetabling and Registration System: This system supports both the process of student registration in courses and the process of scheduling courses and assigning them to rooms. The description here covers the relationships between courses, students, sections, instructors, rooms, and course prerequisites. The description is not meant to describe the design or decomposition of a database, just some logical requirements of the system. The system includes (but does not have to be limited to) Courses The college offers a number of different courses, in different faculties and departments. Each course has a departmental abbreviation and course number, a collection of prerequisites and/or corequisites, and a required number of hours of lecture and lab time. In any given semester can be composed of a variety of sections, with an upper limit of 99. Prerequisites specify both the course and a required minimum grade (defaulting to a pass). Sections A section is an offering of a specific course, and has a single instructor and zero or (hopefully) more students. An instructor may be teaching multiple sections of a course, and there may be multiple instructors teaching different sections of the same course. A student is only enrolled in one section of a course in any given semester, but may take the course repeatedly in different semesters (e.g. to improve their grade). Each section has its own limit on the number of students who can be enrolled in it, and has specific start and end dates. Rooms and bookings Each room is identified by a campus, a building, a room number, and a phone extension. Rooms can be offices, labs, meeting rooms, or classrooms, each of which has their own properties. Meeting rooms, labs, and classrooms can be dedicated or general use. General use rooms can be booked for any courses, whereas dedicated rooms are tied to specific sets of courses or departments. Useful room attributes to record (for aiding automated room selection) include designations for computer labs, data projectors, network capabilities, accessible hours, etc. Offices can be associated with specific staff and faculty members, or with specific tasks or jobs. Each section has specific weekly room bookings for lectures and labs, and there must be a means of identifying the variety of conflicts that can arise: rooms booked for more students than the room capacity different courses booked into the same room at the same time instructors scheduled to be in two places at once students registering in courses with conflicting timetables Note that a section might be in different rooms and different time slots on different days (e.g. Mon/Wed in room xxx from 9-10:30, Fridays in room yyy from 2-3). Students Students can be enrolled in a variety of sections over a variety of semesters - with grades recorded for completed results (for the purposes of checking prerequisites). Students have surnames and zero or more given names, a student number, and contact information (phone number, email address). Instructors Instructors can be teaching a variety of sections of one or more courses, over a variety of semesters. Instructors have surnames and zero or more given names, an employee number, and contact information (office, email address). Queries Typical system actions are likely to include: Updating instructor information A small collection of these are likely to take place each semester, as new instructors are hired, others leave, offices are changed, etc. Updating course information A small collection of these are likely to take place each semester, as new courses are added, prerequisites changed, etc. Updating student information (not including course registration) A large number of these are likely to take place each semester, but primarily in concentrated batches. This includes changes to student names, contact information, etc. Updating section information (not including course registration) A large number of these are likely to take place each semester, as the new semester's offerings are determined, instructors and rooms are assigned, section sizes are determined, etc. Updating enrollments A very large number of these are likely to take place each semester, with some periods of peak usage. This includes students registering for courses and dropping courses, the assignment/changing of grades, etc. General Informational queries Queries for each information type are likely to be much more common and more widely distributed than updates. Formal reports At certain times of the year large reports will be generated on the entire collection (generating the complete timetable, generating the class lists, generating grade reports, running an across-the-board pre-requisites check, etc).

Assumptions and questions

Questions
• Precisely who interacts with the system and when, both with respect to providing/generating the queries described, and providing/updating the data used in the system? We have assumed the basic entity roles are student, instructor, registration personnel, curriculum personnel, timetabler, and department chairs. These might not be appropriate names for the roles, and might not be accurate. Should advisors also be considered? Should there be a seperate role for bookings personnel (currently joined with the timetabler role)?
• What processes are followed to submit updates and queries for each of the data entities discussed?
• What processes are followed to validate access credentials for each of the query/update types discussed?
• Follow on - does the general public have access to any of the information, and if so, which portions?
• Do students/instructors update their own information, or just a portion of that information (if so, which portion), or is an official third party responsible for vetting/applying the updates?

  Assumptions

• No mention is made of grade allocation or lookup, so we assume that is all handled by an entirely seperate system. If grade information is to be included with the enrolment data then the Enrolment data entity will be the likely candidate to hold such information.
• No mention is made of any of the human resources/payroll side of instructor information, so we assume that is all handled by an entirely seperate system.
• Student information (both enrolment and contact) would logically be highly private and secure, while the course descriptions, pre-requisites, availability/timetables, who the instructors are plus their contact information, all appear to be relatively open-access information (at least within the campus community). We thus assume the division of the system into two seperate subsystems at the process.

Part 1: Preliminary Context diagram, DFD, and description

Similarly, we are guessing at the actual sequencing/processes in use, based on the data and queries involved and using the (guessed) entities above. The preliminary process-based description of the system is given below.

Questions that specifically require answers are highlighted in red in the document.

Context diagram and system overview

The system is intended to model the registration and timetabling system for the university.

We make the following presumptions about who uses the system and how, with the context diagram below illustrating these uses.

• Students:
  (1) We assume the students need the capability to submit queries/updates regarding their personal information.
  (2) Similarly, we assume the students need the capability to submit queries to course descriptions and timetabling information to plan their course loads, and to be able to submit queries and updates to their course enrolments (requesting to add/drop courses and review their current timetable).

• Registration personnel
  (1) We assume the university has registration personnel with the capability to review, validate, and update student personal information.
  (2) Similarly, we assume registration personnel must have the capability to review, validate, and update student enrolment information, enforce pre-requisites, withdraw students if fees go unpaid, etc.

• Instructors
  (1) We assume instructors need to be able to generate class lists for their sections.
  (2) We assume instructors need to be able to query timetable information for their courses.

• Department chairs
  (1) We assume department chairs have the ability to set the allocation of instructors to course sections.
  (2) Similarly, we assume the chairs provide the specifics on how many sections will be offered for each course each semester.

• Curriculum personnel
  (1) We assume the university has curriculum personnel in charge of validating and updating the descriptions of university courses, their pre/co-requisites, contact hours, etc.

• Timetabling/booking personnel
  (1) We assume the university has timetabling personnel in charge of the allocation of which rooms are allocated to which sections, on what days, and at what times.

Note: in the context diagram above, the symbols used system are NOT consistent with those used in the data flow diagram in the sections below. This needs to be corrected for subsequent versions of this document.

Level 1 DFDs and description

Here we provide a preliminary division of the system activities into the entities (people) that use it, the key processes involved, the data stores used, and the flow of information between all those components.

First we provide a general overview, followed by the diagram to visualize the system, followed by the detailed description of all the diagram components.

Overview

The six key types of people (entities) identified are described in the context diagram above.

The six key processes identified are as follows:

• Curriculum updates: Providing or updating course pre/co-requisites and contact hours.

• Course allocation: Determining section offerings and instructor allocation.

• Section timetabling: Updating the allocation of course sections to dates/times/rooms.

• Course information lookup: Looking up information about the courses themselves, the section offerings/timetables, and the instructors associated with the sections.

• Student data access: Accessing or updating students' personal information.

• Enrolment access: Accessing or updating the enrolment of students in course sections.

Three seperate data stores are identified. The rationale is to seperate sensitive student data from the more public course offering information, and with respect to the student data it is also recommended we seperate personal information from enrolment information.

• Course information: covering both general course description information and scheduling/timetabling information for the course sections offered.

• Student personal data: covering student contact information.

• Enrolment data: covering the current collection of student enrolments in sections.

The specific flows of data between the entities, processes, and data stores are discussed in the full DFD Description following the data flow diagram below.

Level 1 dataflow diagram

To reduce diagram clutter, the data flows between components are represented as unlabeled directionl edges going from the entity that initiates communication between the two components. In fact, each such flow would also result in a response flow in the opposite direction. Both the shown flows and the response flows are discussed with the source component in the descriptions below. Fully-labelled flows in each direction are provided in the level-2 data flow diagrams where the core processes are modeled independently.

Full descriptions of the DFD components are given following the diagram itself.

DFD Description

The DFD component descriptions are seperated into the entities, processes, and data stores.

Entities:

• Students

     Student interaction with the system is divided into four types,
        handled through interaction with four seperate processes:
        (1) personal data access
               - to review/update personal information and
                 general registration status/information
        (2) enrolment access
               - to review/update enrolment in specific course sections
        (3) course info lookup
               - to look up course descriptions and pre-requisites
        (4) timetable access
               - to look up the days/times/locations for specific
                 course sections and build a semester timetable
  

• Instructors

     Instructor interaction with the system is divided into three types,
        handled through interaction with three seperate processes:
        (1) enrolment access
               - to produce class lists for sections the instructor teaches
        (2) course info lookup
               - to review course descriptions and pre-requisites
        (3) timetable access
               - to look up the days/times/locations for specific
                 course sections and build a semester timetable
  

• Timetablers

     Timetabler interaction with the system is handled through the
        (1) timetabling process
               - allowing the timetabler to review current room and
                 booking data, current section scheduling information,
                 update this data as needed, and identify potential
                 scheduling conflicts with rooms, courses, or instrutors.
  

• Registration personnel

     Registration interaction with the system is divided into two types,
        handled through interaction with two seperate processes:
        (1) personal data access
               - to review/update personal information and
                 general registration status/information
        (2) enrolment access
               - to review/update enrolment in specific course sections
  

• Department chairs

     Chair interaction with the system is divided into two types,
        handled through interaction with two seperate processes:
        (1) curriculum update 
               - allowing the chair personnel to review current
                 course information and pre-requisite information.
        (2) course allocation 
               - allowing the chair to review and update the
                 course sections offered and the assignment of
                 instructors to teach those sections
  

• Curriculum personnel

     Curriculum interaction with the system is handled through the
        (1) curriculum update process 
               - allowing the curriculum personnel to review and update
                 current course information and pre-requisite information.
  

Processes:

• Access control (Implied?)

     The issue of access control (login/validation) is never discussed,
        but seems like a necessity given the information in question.
     The preliminary model assumes the existence of a single central
        access control/credential validation process, implicitly
        used by all the other processes.
  

• Personal data access

     This process allows students and registration personnel to access and
        update student information such as name, contact information, and
        any other registration system other than enrolment.
        Should any other registration system be included?
     The information used is found in the Personal Data data store.
     ***The actual process mechanisms, sequences, data validation,
        and exception handling have NOT yet been provided.
  

• Enrolment access

     This process allows students and registration personnel to access and
        update student enrolment in course sections.  Together with the
        course timetabling information it also allows students to build
        semester timetables and check for scheduling conflicts.
     It also allows course instructors to generate class lists and timetables
        for the course sections they instruct.
     The information used is found in the Enrolment Data and the Course data stores. 
     ***The actual process mechanisms, sequences, data validation,
        and exception handling have NOT yet been provided.
  

• Course info lookup

     This process allows students and instructors to access course and pre-requisite
        descriptions.
     The information used is found in the Course data store.
     ***The actual process mechanisms, sequences, data validation,
        and exception handling have NOT yet been provided.
  

• Timetable access

     This process allows students and instructors to look up course and section
        offerings and timetables for a semester, permitting them to build their
        timetables and check for scheduling conflicts.
     The information used is found in the Course data store.
     ***The actual process mechanisms, sequences, data validation,
        and exception handling have NOT yet been provided.
  

• Timetabling

     This process allows the timetabler to look up planned course and section
        offerings, update their scheduing information (times, dates, locations)
        and check for scheduling conflicts (for rooms, courses, and instructors).
     The information used is found in the Course data store.
     ***The actual process mechanisms, sequences, data validation,
        and exception handling have NOT yet been provided.
  

• Course allocation

     This process allows the department chairs to look up or alter planned course
        and section offerings for their department's courses, to assign instructors
        to teach those sections (or portions thereof) and to check instructor workloads.
     The information used is found in the Course data store.
     ***The actual process mechanisms, sequences, data validation,
        and exception handling have NOT yet been provided.
  

• Curriculum update

     This process allows department chairs and curriculum personnel to review and
        update information related to course content and/or pre-requisites.
     The information used is found in the Course data store.
     ***The actual process mechanisms, sequences, data validation,
        and exception handling have NOT yet been provided.
  

  Data stores:

  It is likely all three data stores would be handled through a traditional RDBMS with conventional secure-access mechanisms and restrictions, logging, backup, and monitoring processes.

• Personal data

     This data store handles the personal student information
        (corresponding to the Student entity in the ERD below).
     This information is accessed and updated by the personal
        data access process used by students and registration.
     ***The actual data fields, formats, access restrictions,
        validation rules, and exception handling have NOT yet been provided.
  

• Enrolment data

     This data store handles the records of which students are
        enrolled in which sections of the university courses
        (corresponding to the Enrolment entity in the ERD below).
     This information is accessed and updated by the enrolment
        access process used by students, instructors, and registration.
     ***The actual data fields, formats, access restrictions,
        validation rules, and exception handling have NOT yet been provided.
  

• Course data

     This data store handles the general access portions of the
        system data (corresponding to the following entities in the
        ERD below: Instructor, Instructor Assignment, Course, Section,
        Pre/co-requisites, Room, Room Facilities, Room Booking).
        booking information
     This information is accessed and updated by the following processes:
           - the timetabling process, used by the timetabler,
           - the curriculum update process, used by curriculum personnel
             and the department chairs, and
           - the course allocation process, used by department chairs.
     ***The actual data fields, formats, access restrictions,
        validation rules, and exception handling have NOT yet been provided.
  

Part 2: Preliminary ERD and description

• Overview
  The timetabling and registration system data model below follows roughly the user division of the data into key logical units:
  • the Students registered at the university,
  • the Courses officially sanctioned by the university,
  • the Sections of those courses that are actually offered,
  • the Rooms available for campus use,
  • the Bookings which record when rooms are in use, and for which sections of which course,
  • the Instructors who are part of the university faculty.

  It also splits off some of the information into four new entities to efficiently support the common query types mentioned in the user documentation. The four new entities are

  • the list of Pre- and co-requisites associated with each course,
  • the Instructor assignments allocating specific instructors to teach all or part of various sections offered,
  • the Enrolments of specific students in specific course sections, and
  • the Room facilities available in each room on campus.

  Each of the entities is discussed in some detail below, providing preliminary descriptions, attributes, and relationships with other entities. Unfortunately, for each of the entities we are currently lacking information on who supplies the entity data originally, who validates it, who updates it, and who issues the queries in which it is used.

  There are nine relationships identified between the ten entities:

  • Each Instructor Teaches zero or more Sections of zero or more courses.
  • Each Course is Offered in zero or more Sections (since some courses are not offered).
  • Each Section is Taught by one or more instructors, as recorded in the Instructor Assignment data.
  • Each Section is Located in one or more rooms (at different times), as recorded in the Room bookings data.
  • Each Student is taking zero or more courses, as recorded by their section enrolment within the Enrolment data.
  • Each Section's Class list is thus composed of the students identified in the Enrolment data.
  • Each Room Contains zero or more Facilities. We assume the existence of codes to indicate facility types, such as specialized labs, data projectors, phones, network capabilities, etc.
  • Each Course can require zero or more Pre/co-requisites.
  • Each Room is Used during the times identified in the Room bookings data.

  Relationship details are discussed with the relevant entities following the diagram.

• Preliminary ERD
  The diagram below provides the visual representation of our preliminary data model. Entities and their attributes are represented using rounded rectangles, while relationships are represented using edges labelled with diamonds.

  Description of each of the components follows the diagram itself.

  

• ERD Description

  Here we provide preliminary detail for each of the components of the ERD. All points need to be reviewed in detail at this point, as there are many questions. In particular, we still need to identify the source and destination of each data element, the format of each data attribute, and the validation that needs to take place for each.

  • Entity: Student

    Description: 
       This represents registered students at the university,
          and their relevant personal information.
       ***The actual data fields, formats, access restrictions,
          validation rules, and exception handling have NOT yet been provided.
       
    Relationships: 
       Is taking: relates the student to their active enrolments in course sections.
       
    Attributes:
       Student id: the student's unique identifier at the university. (format?)
       Name: surname and given names.
       Phone number: is this mandatory? what format?
          are multiple numbers recorded?
       Email address: is this mandatory?
          are multiple addresses recorded?
    

  • Entity: Course

    Description:
       This is a valid university course (how far back in university
          history does this go?  are old course variants supported?)
       ***The actual data fields, formats, access restrictions,
          validation rules, and exception handling have NOT yet been provided.
    
    Relationships:
       Requires: identifies the pre/co-requisites for the course.
    
    Attributes:
       Department abbreviation: the university code for the course,
          usually a departmental abbreviation?  What is the list of codes?
       Course number: the specific course identifier within the 
          department abbreviation category.  What formats are possible?
       Contact hours: lecture/lab/tutorial?  What format?
    

  • Entity: Pre/co-requisite

    Description:
       Represents another course a student must have taken prior
          to, or concurrent with, another desired course (e.g.
          CSCI 160 is a pre-requisite for CSCI 161).
       ***The actual data fields, formats, access restrictions,
          validation rules, and exception handling have NOT yet been provided.
    
    Relationships:
       Requires: a course requires another course as a pre-requisite.
    
    Attributes:
       Course id: the course that requires the pre-requisite.
       Prereq-course id: the course that is required before/with
          the course id listed above (also applies to co-requisites).
       Required grade: specifies the minimum grade that must be obtained
          for the pre-requisite course to be usable.
       Pre/co-requisite type: specifies whether it is a pre-requisite
          or a co-requisite.
    

  • Entity: Section

    Description:
       This describes an offering of a particular course,
          both to allow students to enrol in the course and
          to timetable the course.
       ***The actual data fields, formats, access restrictions,
          validation rules, and exception handling have NOT yet been provided.
        
    Relationships:
       Class list: the class list for the section is actually
          determined by the enrolment in that section.
       Taught by: ties the assignment of instructors to the
          specific course sections.
    
    Attributes:
       Section id: uniquely identifies the section.  What is
          the format for this?
       Start/end dates: the first day of instruction
          and last day of instruction for this section
       Size limit: the upper limit on the number of
          students who can register in the section.
    

  • Entity: Enrolment

    Description:
       this records the enrolment of a single student to a
          single section of the relevant course.
       ***The actual data fields, formats, access restrictions,
          validation rules, and exception handling have NOT yet been provided.
    
    Relationships:
       Is taking: relates the student to their active enrolments in course sections.
       Class list: the class list for the section is actually
          determined by the enrolment in that section.
    
    Attributes:
       Student id: the student's unique identifier within the university
          (font color="FF0000">(format needs to be specified).
       Section id: the unique identifier for the course section
          (font color="FF0000">(format needs to be specified).
       Grade: the grade the student received for the course, INP if in progress.  
         It is not clear if this information belongs in this system.
    

  • Entity: Instructor

    Description:
       Each university instructor has their core contact information recorded.
       ***The actual data fields, formats, access restrictions,
          validation rules, and exception handling have NOT yet been provided.
    
    Relationships:
       Taught by: ties the assignment of instructors to the
          specific course sections.
       Teaches: each instructor teaches (part of?) the course
          section(s) they are assigned to.
    
    Attributes:
       Section id: the unique identifier for the course section
          (format needs to be specified).
       Instructor: identified by employee number
          (format needs to be specified).
    

  • Entity: Instructor assignment

    Description:
       Each semester, an instructor can teach one or more sections of 
          different courses.
       (could this be zero?  can there be instructors in the database 
          who do not actually teach?)
       ***The actual data fields, formats, access restrictions,
          validation rules, and exception handling have NOT yet been provided.
    
    Relationships:
       Teaches: each instructor teaches (part of?) the course
          section(s) they are assigned to.
       Taught by: ties the assignment of instructors to the
          specific course sections.
    
    Attributes:
       Employee number - presumed unique, format unspecified?
       Name - surname plus one or more given names
       Email address - do all instructors have one?
              - can there be multiple?
       Office - do all instructors have one?  
              - can there be multiple?
    

  • Entity: Room

    Description: records information about usable rooms on campus.
         are all rooms on all campuses in the database?
           are some rooms off campus?
       ***The actual data fields, formats, access restrictions,
          validation rules, and exception handling have NOT yet been provided.
    
    Relationships:
       Contains: each room contains zero or more special facilities
          (computer labs, special equipment, etc)
          what categories of feature are there?
       Used during: each room is booked for use at specific dates/times.
          are offices included in this (e.g. if all rooms are in
             the system, then some must be booked 24/7 if they are a
             dedicated office?)
    
    Attributes:
        Campus: the campus is the room located in
        Building: the building is the room located in
        Room number: the unique room identifier within the building
        Room type: the classification/use type the room has
           (classroom, lab, office, ??? what should be supported?)
    

  • Entity: Room facilities

    Description: the facilities (equipment) associated with the room.
       ***The actual data fields, formats, access restrictions,
          validation rules, and exception handling have NOT yet been provided.
    Relationships: 
       Contains: each room can have zero or more facilities associated with it.
    Attributes:
       Room id: specifies the room is the facility is located in.
       Facility category: specifies the general category of facility/equipment.
          (desk, table, phone, computer, audio-visual aid, ??? what should be supported?)
       Facilty capacity: specifies how many of the items are present (seats, computers, etc).
       Facility info: specialization information (phone number, linux vs windows lab, etc)
          What kinds of information should be supported?
          Are additional attributes necessary to handle the possibilities?
    

  • Entity: Room booking

    Description: this is the allocation of a specific room to
       a specific course or activity, at a specific date and time.
       what kinds of recurring bookings need to be handled?
       ***The actual data fields, formats, access restrictions,
          validation rules, and exception handling have NOT yet been provided.
    
    Relationships:
       Located in: this relationship indicates which sections are
          associated with which room bookings
       Used during: this relationship indicates which rooms are
          associated with which room bookings
    
    Attributes:
       Room id: specifies which room is being assigned
       Section id: specifies which section is the room assigned to
       Start/end date: specifies the first/last dates the booking
          applies to (should there be an "ongoing" option?)
       Start/end time: what time does the booking start/end on
          each date (is it safe to assume that if the times are 
          different on different dates then we can use seperate bookings)
       Recurrence data: specifies if/how the booking repeats over time
           can this occur once? weekly? daily? monthly?
          what types of recurring scheduling should be supported?