Create a Select query to provide all data necessary for the Student Details and Academic sections of form below.

Towson University

ITEC 315 – Project

Dr. Hilberg

 

 

Overview

You work for My Favorite University and have been tasked with designing and developing the portion of the database needed to support the Student Information & Schedule application form below.  The database must include student biographic, academic and class schedule data.  Multiple entities are required and all data elements on the form must be included in the database.  Apply the appropriate database design techniques covered this semester.  Use appropriate data definitions for each data element and identify all necessary keys.

 

Use the MS Access database (ITEC315_Project.accdb) provided to get started (includes several tables with some data).  Add any additional table(s) necessary to support the application form.  Create SQL command/procedure queries to create the STUDENT table, add a 2 records to the STUDENT table, and modify a record in the STUDENT table.  In addition, create a Select query to provide all data necessary for the Student Details and Academic sections of form below.

 

 

 

Requirements

 

  1. Use Visio to create a fully attributed ER diagram (Crowfeet notation) to represent the database model. Be sure to include all entities, attributes, relationships and connectivities.

 

  1. Using the attached MS Access database, create and save the SQL command/procedure query to create the STUDENT table. Name the query Be sure to include all necessary attributes with appropriate data types and appropriate keys (primary and foreign).

 

  1. Create and save the SQL command/procedure query to add a record for Mary C. Alexandar to the STUDENT table – include all appropriate data with appropriate data types from the form above. Name the query

 

  1. Create and save the SQL command/procedure query to add a record for Juan Cinaros to the STUDENT table – include all appropriate data (see data below) with appropriate data types from the form above. Name the query

 

ID = 1025689

Juan Cisnaros

854 Burke Ave

Towson, MD 21204,

410-555-2041,

9/23/1996,

Male

Major = Biology

Minor = Sociology

Advisor = Dr. Marie Issennock

GPA = 3.2

Units = 54

 

  1. Create and save the SQL command/procedure query to modify Mary C. Alexandar’s record in the STUDENT table. Name the query Update her address and phone to be:

 

387 Chesapeake Ave

Towson, MD 21204-0001

410-555-5254

 

  1. Create and save a Select query to provide all data necessary for the Student Details and Academic sections of form above for Mary Alexandar only. Name the query SelectStudentInfo.

 

Submission

 

Submit two files via BlackBoard Assignments – (1) the final MS Access database (ITEC315_Project.accdb) and (2) the Visio ER diagram (ITEC315_Project.vsd).   No other files or formats will be accepted!

 

Grading Rubric

 

Component Possible Points Earned Points
1. Creation of a fully attributed ER diagram (Crowfeet notation) database model. Includes all appropriate entities, attributes, relationships and connectivities. 15  
2. Creation of an SQL command/procedure query to create the STUDENT table. Query is named CreateStudent and includes all necessary attributes with appropriate data types and appropriate keys (primary and foreign). 10  
3. Creation of an SQL command/procedure query to add a record for Mary C. Alexandar to the STUDENT table. Query is named AddStudent and includes all appropriate data with appropriate data types from form. 5  
4. Creation of an SQL command/procedure query to add a record for Juan Cisnaros to the STUDENT table. Query is named AddStudent2 and includes all appropriate data with appropriate data types from form. 5  
5. Creation of an SQL command/procedure query to modify Mary C. Alexandar’s record in the STUDENT table. Query is named

UpdateStudent.  Address and phone are updated properly.

5  
6. Creation of Select query to provide all data necessary for the Student Details and Academic sections of the form above for Mary Alexandar

only (should include multiple inner joins).  Query is named SelectStudentInfo and includes all necessary data.

10  
               Total 50  

 

Extra Credit

 

For 5 extra credit points, create and save a Select query to provide all data necessary for the Student Schedule section of form above for Mary Alexandar only.  Name the query SelectStudentSchedule and be sure to include all necessary schedule data.

Type of paper Academic level Subject area
Number of pages Paper urgency Cost per page:
 Total: