Create a new Data Modeler file using Oracle SQL developer 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. 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.

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 students 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. 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 students 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. 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. 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. 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. 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. 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.

TASK 2 of 2: Conceptual Schema, 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.