Omaha Middle School DB

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 Sub- view/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.

TASK 1 of 2: External Schemas (5 Sub-view/External Schemas)

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.
• 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 drag- and-drop the required entities into the Sub-view. DO NOT ATTEMPT TO CREATE MULTIPLE ENTITIES WITH THE SAME NAME.

Sub-view/External Schema 1: 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.

Report 1: Sample Student Emergency Contact List

The school must be able to produce an emergency contact report for each student as required. See the business rules for more details.

Figure: see image.

Additional Business Rules:

• 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.
• Each parent/guardian/emergency contact entry includes the address, city, state, zip code and up to 2 phone numbers for each person.
• A parent can have more than one student in attendance at the school.

Sub-view/External Schema 2: Report 2: Daily Attendance Reports

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.

Figure: see image.

Additional Business Rules:

• 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.
• For each day, many students may be absent.
• For each day, many students may be tardy.
• 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 3 - Report 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.

Figure: see image.

Additional Business Rules:

• 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.
• Each class meets in a specific classroom.
• A teacher may be assigned to teach many classes, but they may only teach a single class each period.
• Each class must be taught by only one teacher.
• The semester name (Fall or Spring) and the year is stored with each offered course.

Sub-view/External Schema 4: Report 4: Student Schedule

Each student will have a printable schedule available showing their assigned classes for the semester.

Figure: see image.

Additional Business Rules:

• Each student may be registered for many classes.
• 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:

• Each student may participate in before or after school activities.
• One teacher is assigned to each before or after school activities as the faculty sponsor.

TASK 2 of 2: Conceptual Schema (Logical Model)

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:

• 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.
• Right-click a relationship line and select 'add elbow' to allow the relationship line to bend.
• 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.
• 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 super- type, even though it is not visible in the Data Modeler model.

Reflection

In this portion of the assignment, you will reflect back on your experience doing the tasks above. Write at least a paragraph, answering such questions as: What challenges or problems did you encounter? How did you resolve them? What general principles did you learn that you can apply in other contexts? There is no specific list of challenges I am looking for. Different students will have different experiences and will reflect differently upon them.