Your assignment is to write a professional progress report to the partners at National Investments. The background and full description for this assignment can be found in the first Case Study document. (Link to case study here.)
You will submit the report in either Word or as a PDF.
Note: Always read the assignment instructions carefully and make sure you understand what is being asked of you before you submit your work.
Excel example data to be used for this project: Case Study 6 Data.xlsx
This is the sixth and final installment of the case study for a company called National Investments. Part of your work over the course of the semester will be to design and build a database for this fictional company. Though fictitious, the narrative and issues discussed are very real for many companies. You must read these case studies carefully and thoroughly to fully understand their business and their needs. This is part of the requirement for building effective and efficient databases.
Youâ€™re finished! Hopefully. In your previous assignment you created your database using SSMS, added data, and ran a simple query. In this project, you will add a new student (Raymond Johnson) to your database, four new classes being taken by this student, two classes that were taken in the previous semester, and create several queries.
The data that needs to be added can be found in the Excel document provided in Blackboard.
The student you are adding is a Canadian student. Canada is not comprised of states, but rather of provincesâ€”10 to be specific. How are you going to accommodate the province of â€œOntario,â€ in this specific example? Also, Canadian zip codes are alphanumeric. They are in the format A1A 1A1, where A is a letter and 1 is a digit, with a space separating the third and fourth characters. Can your database design handle this zip code format?
And, yes, this is new information and your database design may need to change as a result. You already have data in your database, and certain design changes may require you to first delete the data that you already have. Keep this in mind.
Important: The information you receive in the Excel document may cause you to have to make other changes to your database design. The challenge will be incorporating what needs to be done for this school while ensuring the design works for the schools represented by the data you have already received. In some cases, you will not have all the data that you received from the other examples; in such cases, if you have columns in your database for which there is no data on this spreadsheet the data will have to be entered as â€˜NULL.â€™ In other cases, you may receive extra data on this spreadsheet that you did not have before; this means that you will have to adjust your design to accommodate this new data
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.
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.
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.
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.â€
Q. I am getting errors when I try to add data to my database. What should I do?
A . You will need to figure out where the errors are coming from and fix the source problem. Your instructor cannot help you here. Each personâ€™s database design is unique and a problem for one person may not be the same problem for another person.
Make sure the data you are entering for a column matches the data-type for that column.
Make sure that you do not have any NULL values for a column that requires data.
Make sure that you are not violating a referential constraint. This usually occurs either when you are entering data for a child table before that of the parent table, or when the foreign key does not match the primary key in the parent table.
Note : In the case of an error due to something being wrong in the table relationships, you may have to delete the relationship constraint, attempt to add the data, and then recreate the relationshipâ€”this often will show you where such an error might be.
Q. My database design doesnâ€™t have all the columns that the spreadsheet has. What should I do?
A . You will have to change the design of your database such that all the data provided in the spreadsheet can be entered into your database.