Project Description In this exercise, you are responsible for the design and implementation of a school dismissal
Question:
Project Description
In this exercise, you are responsible for the design and implementation of a school dismissal database. School
employees need a database to determine whether students ride the bus at the end of the school day or if
students are pickup by an organization or designated adult.
The database will be used to look-up the names of adults and organizations who have the authority to pick-up
students from the school from school.
The completed assignment must include a logical model, relational model, DDL statements to create the
database, and DML to populate and query the database.
Requirements
The following information must be stored in the database.
1. First name and last names of the students who attend the school and the days of the week the student is in after-
school activities.
2. The first and last names and the days of the week of the adults that can pick up a student from school.
3. A description of the of the adult that can pick up a student from school.
I.e., parent, mother, father, aunt, uncle, legal guardian, au pair, nanny, housekeeper, etc.
4. A description of the of the organization that can pick up a student from school I.e. YMCA, Kuman Math and Reading
Center, Kids Robotic Academy, etc.
5. The names of the students who ride a bus home.
The following queries are required. The queries much provide answers the following questions.
6. What organizations can pick up students from school?
7. What students are enrolled in after-school activities on a given day?
8. Who is picking up a student on a given day?
Assignment Guide
1. Please help me create a logical model and relational model for the database.
2. Generate DDL for the database and create the Oracle database from the design.
3. Populate the database.
4. Add indexes.
5. Please help me write queries, add indexes, and generate execution plans.
6. Modify the database, indexes, queries, and/or data models as needed. (Make sure the final version of the
relational model represents the latest version of the database.)
7. Save final version of the DDL with INSERT statements to a file named DDL_Individual_Project.txt.
8. Save the final version of the relational model to a file named Design_Individual_Project.zip.
9. Save the final version of the queries to a file named Queries_Individual_Project.txt.
Please help me answer the questions as well, thanks
1. In the space that follows, insert a screen clipping of the relational model.
2. Describe the indexes and their effect on query execution. Insert screenshots of each Execution Plan that
shows the effect of the indexes on the query. Compare the Execution Plan of the queries without the
index to the queries with the index.
3. Insert screen clippings that show you successfully created and populated the database.
4. Insert screen clipping that show that your database design and queries satisfy the requirements 1 - 8 of
the database.
5. Describe any object-relational features of Oracle you used in this assignment
Database Systems Design Implementation and Management
ISBN: 978-1285196145
11th edition
Authors: Carlos Coronel, Steven Morris