Handling Student Data: Metadata, Race Tables, Major Tables, Status Tables, and More

Student List Metadata

You asked several different employees (developers and architects--to walk you through the data provided) and you created a matrix of information (meta-data) about the data from the spreadsheets.

The student id is school specific. We have no control over how this ID is created or what it looks like. For some schools, this value might be numeric, but for others there is a good chance that it will contain string data.

Different schools will format the student's names in various ways. Some might be "First Middle Last" while others could be "Last, First Middle." This means that a full name will have to be stored using three separate columns for first name, middle name, and last name. If a school only wants the middle initial we will give them the first letter of the person's middle name.

From the data we have currently, it would appear that most institutions are interested in capturing the following main race groups:

• White Non/Hispanic
• African American
• Asian
• Hispanic

However, there may be others. Some schools may be interested in further breaking some of these down to include Middle Eastern rather than lumping this group into white. Or some have told us they work with a lot of American Indian students and may want to include them as a separate category.

Clearly, this means that 'Race,' as a category, must be dynamic in the sense that the customer can control the values in this category. There will have to be a table for Race.

We can't possibly know all the majors that every school might have. This implies that major must be a separate table that the school maintains.

Status   Like Major, above, this will have to be a separate table to which the student's table is linked. Every institution will define their own status values.

A student may have many courses in a given semester and the new database must be able to handle this possibility. The maximum number of courses taken by any one student--that we have seen--is six. But the number of current courses enrolled by any one student might be greater.

You also recorded some additional notes in your interactions with various people, as follows:

• From your conversations you have concluded that you must have tables that will provide foreign key contraints to the Students table that include Race, Major, Status. There may be others, but this is a minimum.
• The faculty list will include many of the same columns as the student list. Question . . . can the Race table used as a foreign constraint to the Students table also be used for the Faculty table, or do you need two separate race tables (one for students and another for faculty)?
• Like students taking classes where the number of classes are one-to-many (one student can take many classes) a faculty member can teach many classes.

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

1. A copy of your Draw.IO diagram of the database design as you have it after making any adjustments given the new information you now have. You can simply export your Draw.IO as a .png image file, and then insert that image into your word document.
2. This diagram should incorporate everything you know about the company's clients thus far and show as many tables as you believe needed given the information at your disposal to-date.
3. For each table list the attributes (columns) you believe will be required.
4. You will also need to identify the connecting points between tables. And here you need to be thinking in terms of the foreign key relational constraints—the proper relationships between tables.
5. Critical: you will need to identify the primary key for each table.
6. Implement your design using Microsoft SQL Server Management Studio (SSMS). You will need all the following:
7. A database named <cougar id>NEA
8. Within your database create each of the tables you defined in your diagram; it is time to implement the diagram or conceptual model and create a physical model.
9. For each table create all the necessary columns with appropriate data-types.
10. Identify the primary key for each table.
11. Create the relationships between the tables as need and as specified by your design.
12. Create a new database diagram using Database Diagrams feature in SSMS.
13. Place each of the tables you have created onto the SSMS diagram.
14. Change the view to use the Standard option from the Table View menu. This will show not only the column names but also the datatypes.
15. Copy the diagram to the Windows clipboard and paste to your Word document.

Much of your work in this assignment is that of refining your design according to the new information provided, determining the data-types for the columns within each table, and implementing this design in SSMS.