This assignment is all about modifying the design and implementation of your database when you find further information that was not revealed earlier and getting data into your database. This will then allow you to create T-SQL queries to report on that data. In the process of adding data to your database you often find your design either isn't optimal—in one or several areas—or might not work at all in which case you need to go back and rework some of the design and implementation of your database.
When using SSMS, there are two ways to enter new data into your database tables: using the T-SQL INSERT statement or using the Edit Table feature in SSMS.
Inserting Data into Table via SSMS Edit:
Note: The screen captures below do not reflect any tables in your design, but are intended to help you understand how to add data using SSMS to your database.
Steps:
Right-click on the name of the table into which you wish to enter data.
Select the "Edit Top 200 Rows" option from the resulting menu.
You will now see the edit panel with all columns pre-filled with NULL values.
Fill in your data on the row with the asterisk * on the left of the row.
Right-Click on Table:
Select "Edit Top 200 Rows":
Edit Panel:
Note 1: Just because a column is pre-filled with NULL doesn't mean that the column will allow a null value. In my example, the email address does NOT allow NULL values—therefore I must enter an email address—in this example.
Note 2: In my example, the Id column is an auto-incrementing column (an "identity specification" column). This means that I MUST leave the null value here. Because this is an auto-incrementing field, once I am finished entering data for the row the next available Id value will be filled in more me.
Fill in your Data:
Note 1: To move through each column simply press the Tab key. To go back a column hold the Shift key and then press the Tab key.
Note 2: When you reach the last column in your row simply Tab one more time and the database will attempt to add your new row.
Note 3: If any data in your row violates any rules or constraints then the row will not be added, and you will receive an error message. If you receive any error messages you will have to spend time to diagnose the problems and fix the issues causing such problems. This may be as simple as ensuring a column is not NULL if it doesn't allow NULL values. It may be something more severe in that the data violates a foreign key constraint in which you will need to modify the design of the impacted tables before you can add your data.
Task:
In preparing your progress report you will need to perform all the following:
1. You will need to add data to the tables in your design that matches the data in the supplied Excel documents. If your database design doesn't accommodate a given piece of data you must modify your design and implementation to ensure that all data provided in the spreadsheet can be added.
a. You may any other data you wish and as your design requires. If your design is such that the needed data is not supplied in the spreadsheet you will need to simply "mock" up some sample data and add it into your database.
b. Important: Your instructor cannot provide you with specific information how to complete this step. Each person's database design will be different and therefore, how data must be entered will be different from person to person.
2. Write a paragraph from you about your experiences (good or bad) adding data to your database. Did everything go smoothly? If everything was not perfect or not as smooth as you would otherwise like what went wrong and how did you fix it?
3. Write a paragraph describing any changes that you needed to make to your database design and implementation as a result of the materials provided in this lesson's guide.
4. You do not need to add a calculated column to your table (in the case of the student's GPA) but do write a short paragraph of your understanding of how this works according to the research you do on this topic. Also, how best do you feel the GPA should be handled in your database. Make sure to discuss this in your paragraph.
5. 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.
a. Write a query to show the student information for all students in your database.
b. Write a query to show all the faculty members in your database.
c. You will copy the text of the T-SQL query statements that you write into your progress report, making sure that you carefully document what each query does and what it produces.
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 basic T-SQL queries to report on the data has been added.