ISQA 3310 – Managing the Database Environment
Project Part 1 – Data Modeling and Entity-Relationship Diagramming
© University of Nebraska at Omaha, College of Information Science and Technology
Database Project Part 1: 150 points Due: THURSDAY, Feb. 28, 2019 by 11:59 p.m.
Objective:
This assignment is part 1 of 3 that will lead you through the database development process for a specific application. This
portion of the project focuses on data modeling. You will be designing a database to support attendance tracking and
reporting for a typical middle school. A detailed description of the requirements for the database is provided below.
In the ANSI/SPARC 3-schema model of databases, the external schema reflects the view of the data held by an individual
user, application, report, form, etc. For this project, you will develop E-R diagrams reflecting the data requirements of
attendance reports, and then integrate them into a single conceptual schema that can support the entire application.
Description of data modeling scenario
Design a database to support a Middle School Scheduling and Attendance system (See Tasks 1-2 on the following
pages of this assignment sheet for task details). Your database model should be designed using the Subview/External Schema descriptions and business rules listed below. The reports used by the school are shown below with
a short description and a sample report or portion of a report provided for clarity.
Note: Application software will NOT be developed for this assignment, and no user interfaces, reports, calculations, or
business logic will be written. Focus only on the data and information requirements. In other words, you will be creating
the database that supports the application not the application itself.
You must use the Oracle SQL Data Modeler data modeling tool. See Canvas for instructions on installing
the software. This software is also available in the computer lab at PKI.
TASK 1 of 2: External Schemas (5 Sub-view/External Schemas) 125 Points (25 points per Subview/External Schema)
Create a new Data Modeler file and give it a meaningful name (i.e. AttendanceSystem). Develop an E-R Diagram with
Sub-view/External Schemas as indicated on the following pages of this document using Information Engineering
notation. You will need a minimum of five (5) logical Sub-view/External Schemas in your model as described
on the following pages.
? The ‘Logical Model’ in the Data Modeler will represent your Conceptual Schema for your design. You may develop
additional Sub-view/External Schemas if desired.
? Set the Notation to Information Engineering for your Logical model and Sub-view/External
Schemas
? Set your Data Modeler preferences as defined in the Data Modeler Tutorial #1 before beginning this
part of the project.
Note: See the Data Modeler Tutorial #1 posted to Canvas for details on how to create Sub-view. When creating a
new Sub-view in the Data Modeler, if an entity is needed in the Sub-view that was created previously, drag-and-drop
that entity from the browser window into the Sub-view and modify it as needed.
? The same entity may appear in more than one Sub-view/External Schema in your model.
? Attributes added in one Sub-view will appear in all Sub-views containing the shared entity. To add an existing
entity to a Sub-view: Expand the Logical Model in the Navigator pane; then expand the Entities; then dragand-drop the required entities into the Sub-view. DO NOT ATTEMPT TO CREATE MULTIPLE ENTITIES
WITH THE SAME NAME.
FAILURE TO CREATE THE REQUIRED DATA MODELER Sub-view/External Schemas WILL RESULT IN AN
AUTOMATIC 20% REWORK PENALTY. THE FILES WILL BE RETURNED TO YOU TO CREATE THEM.
ISQA 3310 – Managing the Database Environment
Project Part 1 – Data Modeling and Entity-Relationship Diagramming
© University of Nebraska at Omaha, College of Information Science and Technology
Sub-view/External Schema 1: Daily Attendance Data and Report
The daily attendance report is used to report all students absent or tardy on a given day. The report shows the student’s
name, attendance status, whether parents notified the school, and the excused status of their attendance. Each teacher
reports the students that are absent or tardy from their first period class to the office. For any student reported absent or
tardy on any given day, a record is saved in the database along with the date, whether parent approval was received, the
excused status, and the teacher reporting the status.
Omaha Middle School
DAILY ATTENDANCE REPORT DATE: Jan. 24, 2019
Last
Name
First
Name
ID Status Parent
Approval
Excused
Status
Reported by Teacher
Schwartz Joe 1234 Absent Y Excused Amy Jacobs
Hsu Wang 2251 Tardy Y Excused Pratik Patel
Jacobs Joan 3321 Absent N Unexcused Roberto Rodriguez
Mason Terry 4521 Tardy Y Excused Roberto Rodriguez
Robertson Jim 5432 Absent Y Excused Donald Perrin
Additional Business Rules:
1. Parents will call the school to notify the school that their son or daughter is not going to be in school. The school
must record that students have been reported absent by the parent.
2. For each day, many students may be absent.
3. For each day, many students may be tardy.
4. The school must be able to determine who is absent from school without the school having received a call from
the student’s parent/guardian. If students are not reported absent, the school will need to call a parent to check
the status of the student.
Sub-view/External Schema 2: Student, Parent, Emergency Contact, and Teacher information
The school must store basic information for each parent, emergency contact, student, and teacher assigned to the school.
Each person is assigned a unique ID number. First and last names, addresses (including street, city, state, and zip code),
and up to 2 contact phone numbers must be stored. The person type is recorded for each person – type values are
Student, Teacher, or Parent/Guardian/Emergency Contact. For teachers, their primary subject area taught, their start
date at the school, and their highest level of college degree earned is also stored. For students, their date of birth and
their grade level are stored. Grade levels are 6, 7, or 8 at this school. For parent/guardians/emergency contacts, their
relationship to the student is stored.
continued next page
ISQA 3310 – Managing the Database Environment
Project Part 1 – Data Modeling and Entity-Relationship Diagramming
© University of Nebraska at Omaha, College of Information Science and Technology
Sample Student Parent/Emergency Contact Report
The school must be able to produce an emergency contact report for each student as required. See the business
rules for more details.
Omaha Middle School
STUDENT EMERGENCY CONTACT REPORT
Student: Teresa Gomez Student Number: 11223
Last Name First Name Relationship Contact Number 1 Contact Number 2
Gomez Marina Mother 402-123-1234 402-654-3211
Gomez Roberto Father 402-123-1234 402-654-2323
Gomez Sara Grandmother 402-234-1234
Anderson James Other 402-321-1111 402-444-6578
Additional Business Rules:
1. Each student must have one or more emergency contact entries. At least one contact should be a
parent/guardian. Emergency contacts may be a parent (father, mother), guardian, grandparent or other.
2. Each parent/guardian/emergency contact entry includes the email address, address, city, state, zip code and up
to 2 phone numbers for each person.
3. A parent/guardian/emergency contact can match more than one student in attendance at the school.
Sub-view/External Schema 3 – Course Schedule
Classes are assigned a unique course ID number, a classroom number, period number from 1 to 7, and a single teacher.
Each classroom is used for a single class each period. The following table shows a snapshot of a portion of the course
schedule.
Omaha Middle School
Spring 2019 Schedule:
Course Number Period Class Name Teacher Room
1 1 Homeroom Jacobs, Amy 123
2 1 Homeroom Stevens, Roberto 102
3 2 Algebra Stevens, Roberto 102
4 2 Biology Perrin, Donald 444
5 3 PE Morse, Mary Gym
6 4 English Washington, Amy 443
7 5 Study Hall Rodriguez, Roberto 113
8 6 French Patel, Pratik 123
9 7 Science Perrin, Donald 444
10 7 Reading Patel, Pratik 123
ISQA 3310 – Managing the Database Environment
Project Part 1 – Data Modeling and Entity-Relationship Diagramming
© University of Nebraska at Omaha, College of Information Science and Technology
Additional Business Rules:
1. Each class may meet during different periods during the day. For example, Science for grade 6 may meet during
periods 1, 2 or 3. Different students would be assigned to different sections.
2. Each class meets in a specific classroom.
3. A teacher may be assigned to teach many classes, but they may only teach a single class each period.
4. Each class must be taught by only one teacher.
5. The semester name (Spring or Spring) and the year is stored with each offered course.
Sub-view/External Schema 4: Student Schedule
Each student will have a printable schedule available showing their assigned classes for the semester.
Omaha Middle School
Spring 2019 Student Schedule: Teresa Gomez Student Grade: 8 Student Number: 11223
Period Room Class Teacher
1 123 Homeroom Jacobs, Amy
2 102 Algebra Stevens, Roberto
3 Gym PE Morse, Mary
4 443 English Washington, Amy
5 113 Study Hall Rodriguez, Roberto
6 123 French Patel, Pratik
7 444 Science Perrin, Donald
Additional Business Rules:
1. Each student may be registered for many classes.
2. Each class can have many students registered for it.
Sub-view/External Schema 5: Student Activities
Each student may participate in before or after school activities. One teacher is assigned to each before or after school
activity as the faculty sponsor. Teachers may sponsor more than one activity and students may participate in more than
one activity. For each activity, the name of the activity is recorded, the location where the activity takes place, and
whether it meets before or after school. The location can be one of the following: a valid room number at the school, the
gym, or outside the school. Activities include: Band, Jazz Band, Choir, Soccer, Football (American), Basketball, Track,
International Club, Student Council, and Community Service Club.
Additional Business Rules:
1. Each student may participate in more than one before or after school activities.
2. One teacher is assigned to each before or after school activities as the faculty sponsor.
3. Teachers can sponsor more than one activity.
ISQA 3310 – Managing the Database Environment
Project Part 1 – Data Modeling and Entity-Relationship Diagramming
© University of Nebraska at Omaha, College of Information Science and Technology
TASK 2 of 2: Conceptual Schema (Logical Model) 25 Points
After all Sub-view/External Schemas are created, return to the Logical Model and organize the entities and relationships
until all items are visible. Ensure all data requirements are supported by your model and all relationships are defined AND
LABELED with verb-phrases correctly.
Data Modeler Hints:
1) Right-click on the background of the Logical Model, select ‘Layout’ – ‘Auto Layout’ – Choose a Layout (1, 2, 3,
or 4) to have the software reorganize the layout for better viewing. You may also manually drag the entities
to make the model more readable.
2) Right-click a relationship line and select ‘add elbow’ to allow the relationship line to ‘bend’.
3) Right-click on the background of the Logical model or any of the Sub-view/External Schema model screens
and select ‘Show’ then ‘Labels’ to view your verb phrases.
4) To rename a foreign key, double-click the entity with the foreign key, view the Attributes page. Double-click
the name of the foreign key attribute to open the Attribute Properties page. Edit the Name field to change
the name. This is useful when relationships exist between multiple sub-types (of the same super-type) and
another entity. Note that the primary key of a sub-type entity is the same as the primary key of the supertype, even though it is not visible in the Data Modeler model.
Submitting this Assignment on Canvas:
Zip your model file (*.dmd file) AND the associated directory/folder created by the Data Modeler software
into a single file. Post this zipped file to the Assignment link for Project Part 1 on the course Canvas page.
