. Write a paragraph describing any changes that you needed to make to your database design and implementation as a result of the materials provided here and in the Excel document. In a few sentences, what could you have done in the early design process that would have prevented any issues at this point in your project?

Learning Goal: I’m working on a databases project and need an explanation and answer to help me learn.

PURPOSE:

This assignment is all about ensuring your database and its design is capable of handling new data—especially when that data might be in a slightly different form than the data you have already been working with. Furthermore, this assignment gives you an opportunity to see if you can create some fairly complex queries from the data that you have.

TASK:

In preparing your progress report you will need to perform all the following:

1. First, you need to add the data to the database that has been provided to you in the Excel document. But there is one additional requirement that was not part of the previous project—you must add the new student using the T-SQL Insert statement. All other data—other than the stdent—can be entered using the SSMS Edit Table feature.

Important: In your report, paste the text of your T-SQL Insert statement for review by your instructor.

2. Write a paragraph describing any changes that you needed to make to your database design and implementation as a result of the materials provided here and in the Excel document. In a few sentences, what could you have done in the early design process that would have prevented any issues at this point in your project?

3. White a series of T-SQL SELECT queries to report the data that you have inserted into your database. There is no specific “look-and-feel” for your reports/queries. The T-SQL Select statement doesn’t allow you to do much in the way of formatting your output. But make sure you can accomplish the following querying tasks—several of which will require you to join multiple tables together. The goal is to demonstrate that you can produce the desired output and that you can do so with a single T-SQL Select statement for each of the following (all queries in this project should use the data provided as part of this project for Raymond Johnson):

a. Create a query that shows the name of the student, the address information, and the four classes the student is currently enrolled in.

b. Create a query that displays the name of the student, the student’s major, and then a list of all classes taken (past and present). In this case, there are four classes being taken in this semester and two classes that were taken previously. Along with the classes taken previously you must show the letter grade earned.

c. Create a query that shows the name and description for each ‘Active’ class in your database and then the name of the faculty members teaching these classes. Active in this case means that the class is currently being taught this semester. Oh, wait, you don’t have a way of denoting something is active or inactive, do you? You will have to add this column to your table of classes. This can be a simply ‘bit’ (boolean) flag that is either true or false, or it may be the name of the semester (spring 2021, for example), in which case you would query for the semester name to show which classes are active in that semester.

d. Create a query that shows each faculty member, any classes assigned to them in the current semester, and the building and room numbers where those classes are being taught.

You will copy the T-SQL query statements that you write into your progress report, making sure that you carefully document what each query does with what it produces. To document the results simply include a screen capture of your SSMS screen.

4. Include your ER diagram. The diagram should be in 3rd normal form and reflect the new criteria for this assignment.

CRITERIA:

Much of your work in this assignment is that of adding data to your implemented database, changing the design of your database if your data needs dictate such, and then writing T-SQL queries to report on the data has been added.

SUBMISSION:

• You will submit a single document in either Word of PDF format to Blackboard. This document will be your Progress Report to the partners at National Investments.

Important: You might want to go back and read the section on “Reports” from Introduction to Professional Communications. (Link to reports information here.) Focus on the information about the “Progress Report” and “Report Organization.” Note, with respect to the “Report Organization” section you only need to focus on the information about the “Introduction,” “Body,” and “Conclusion.”