Project Part - Data Modeling and Entity-Relationship Diagramming
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. 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 Sub- view/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 drag- and-drop the required entities into the Sub-view. DO NOT ATTEMPT TO CREATE MULTIPLE ENTITIES WITH THE SAME NAME.
Business Rules (Your model must support the following rules as well as those represented in the Sub- view/External Schema descriptions and reports outlined below)
1. Each student must have one or more emergency contact entries. Emergency contacts may be a parent (father, mother), guardian, grandparent or other.
2. Each student must have one or more parent/guardian entries including the address, city, state, zip code and up to 2 phone numbers for each parent/guardian.
3. A parent can have more than one student in attendance at the school.
4. 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.
5. For each day, many students may be absent.
6. For each day, many students may be tardy.
7. 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.
8. Each student may be registered for many classes.
9. Each class can have many students registered for it.
10. 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.
11. Each class meets in a specific classroom.
12. A teacher may be assigned to teach many classes, but they may only teach a single class each period.
13. Each class must be taught by only one teacher.
14. Each student may participate in before or after school activities.
15. One teacher is assigned to each before or after school activities as the faculty sponsor.
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, Parent/Guardian, or 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, and emergency contacts, their relationship to the student is stored.
Report 1: Student Emergency Contact List (attached)
The school must be able to produce an emergency contact report for each student as required. See the business rules for more details.
Sub-view/External Schema 2: Reports 2 and 3: Daily and Semester 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 semester number and year. Fall semester is semester #1; spring semester is semester #2.
Each semester, an attendance report is sent home with students summarizing the student's attendance during the previous semester. The attendance report includes the student's first and last names, the students address (street, city, state, and zip), the student's home phone number, the total number of absences and the total number of times the student is tardy.
Sub-view/External Schema 3 - Report 4: Course Schedule
Classes scheduled for a semester 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.
Sub-view/External Schema 4: Report 5: Student Schedule
Each student will have a printable schedule available showing their assigned classes for the semester.
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.
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:
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.
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 super- type, even though it is not visible in the Data Modeler model.
Attachment:- project part.rar