Design and implementation of a school dismissal database

Project Description

In this exercise, you are responsible for the design and implementation of a school dismissal database. School personal 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 by school personal to look-up the names of adults and organizations who have the authority to pick-up students from the school from school. 

To design the database, you will first review the preliminary design requirements. The preliminary design requirements are minimal and incomplete. Add requirements to complete the design of the database. Describe requirements on this document as indicated by the words, Complete this section of the document.

You will use Oracle Data Modeler to create a logical and relational model. Then you will implement your database design as an Oracle database. After the Oracle database is created, reverse engineer the database to the relational and logical models.

You have one grade for this assignment. The grade is based on the submission of all the required deliverables due at the end of week 11.

Final Deliverables – Due at the end of week 11 – Upload your deliverables to a shared folder on your UMGC OneDrive. Submit your shared link to the Individual Project Assignment folder.

The deliverables for the final deliverables are:

  1. An annotated voice-over PowerPoint summary presentation of the design of the database. Limit the presentation to no more than 5 minutes. Submit presentation as a PowerPoint file or video.
  2. This document with your answers to each Complete this section of the document.
  3. The file named, Queries_Individual_Project.txt. (Task 10)
  4. The file named, DDL_Individual_Project.txt with INSERT statements for sample data. (Task 11)
  5. The file named Logical_Design_Individual_Project.zip. (Task 12)

Tasks

  1. Review the database requirements.

The following information should be stored in the database.

  • First name and last names of the students who attend the school.
  • The first and last names of the adults that can pick up a student from school. 
  • 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.

  • 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.
  • The names of the students who ride a bus home.
  • Emergency contacts for each student.

Write one or more queries that will answer the following questions. 

  1. Who can pick up a given student?
  2. What organizations can pick up students from school?
  3. Other requirements that required in the database design

Complete this section of the document.

What other information will be stored in the database? List at least 3 additional requirements.

  1. Preliminary logical model

Create the preliminary logical model using Oracle SQL Developer Data Modeler. 

Complete this section of the document.

In the space that follows, insert a screen clipping of the preliminary logical model. 

  1. Database tables and columns and primary keys

Complete this section of the document.

What are the tables and the columns of the database? Label the primary keys and foreign keys. 

  1. Indexes

Complete this section of the document.

Add some indexes – do not add indexes to the primary keys.

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.

 

  1. Oracle Database

Create the database in Oracle. In the space that follows. 

Complete this section of the document.

Insert screen clippings that show the database tables in SQL Developer.

  1. Finalizing the logical and relational model

Logical and Relational Models

Complete this section of the document.

Insert screen clippings of the final Logical and Relational Models from SQL Data Modeler. If necessary, reverse engineer of the Oracle Database to the Relational Model and then reverse engineer the relational model to the Logical Model.

  1. Sample Data

Insert sample data into your database that you can use to demonstrate that your database meets the design requirements. 

For example, here are some statements that would describe the sample data and show that the database meets the design requirements.

1. There are 10 students in the database.

2. The siblings are: Mary and John Smith; David, Thomas, and Sally Jones etc.

3. Each student has at least one designated adult who can pick up the student from school.

Etc.

 

Complete this section of the document.

Describe your sample data.

  1. Queries – Write at least 10 queries that illustrate how a user would benefit from the database. Include queries that show the additional requirements you listed in task 3. Label each query.

Complete this section of the document.

Describe queries. Do not give the SQL – do something list this:

 

Query 1 – Student Roster – An alphabetical list of all students

Query 2 – 

Write and execute the queries against your Oracle database. Save the SQL of the queries to a file named Queries_Individual_Project.txt. Include comments in the file to provide a description of the queries.

Do your queries execute without errors?

 

Insert screen clipping that show your queries executed successfully using the sample data.  each screenshot.

  1. DDL

Export the DDL with INSERT statements of your database and name the file, DDL_Individual_Project.txt. (The instructor or TA must be able to successful execute the DDL and queries.) 

Complete this section of the document.

                Does your sql file include the INSERT statements for the sample data?

 

  1. Data Modeler Design

Export and then zip the folder containing the final version of the logical design of the database. Name the zip file Logical_Design_Individual_Project.zip. 

 Welcome to one of the bestassignmenthelpcompanies  online .

·         Do you want to order for a customized assignment help task?

·          Click on the order now button 

·         Set up your topic, Fix the number of pages, Fix your Order instructions 

·         Set up your deadline, upload the necessary files required to complete the task, Complete the payment.

 We delivery high quality and non plagiarized tasks within the stipulated time given 

SL