Overview
This is the fourth of a multi-part 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.
Case Study Part 4
Keep up the good work! In your previous assignment you created a diagram for the new database. And you worked to understand the data as much as you could, but there were questions about what some of the data given you represents. In this case study we look at the meaning of the data that was given and you go back and refine your design again, but also begin developing the database in SQL Server.
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.
Student List Meta-Data:
Student Id 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.
Student Name 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.
Race 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.
Major
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.
Course Schedule List Meta-Data:
Course List 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.
Student Id and Name The Id and Name of the student should come directly from the Students table.
Catalogue Meta-Data:
Semesters Offered The semesters we have seen from the existing clients are as follows:
• Fall
• Winter (also called Spring by some)
• Summer–sometimes also broken down by:
• Summer A
• Summer B
Additional Notes:
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.
Assignment
Purpose:
You know feel that you have more information than you did before and can begin to rethink and possibly refine your initial database diagram. You can now also begin thinking about the actual implementation of your design.
Task:
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. If your design doesn’t need to be changed from what you had then simply use that existing design, but chances are there will be some adjustments. You can simply export your Draw.IO as a .png image file, and then insert that image into your word document.
a. 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.
b. For each table list the attributes (columns) you believe will be required.
c. 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.
d. Critical: you will need to identify the primary key for each table.
2. Implement your design using Microsoft SQL Server Management Studio (SSMS). You will need all the following:
a. A database named
b. 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.
c. For each table create all the necessary columns with appropriate data-types.
d. Identify the primary key for each table.
e. Create the relationships between the tables as need and as specified by your design.
3. Create a new database diagram using Database Diagrams feature in SSMS.
a. Place each of the tables you have created onto the SSMS diagram.
b. 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.
c. Copy the diagram to the Windows clipboard and paste to your Word document.
Criteria:
• 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.
Submission:
• You will submit a single document in Word format to Blackboard. This document will be your Progress Report to the partners at National Education Associates.
Important: You might want to go back and read the section on “Reports” from Introduction to Professional Communications. (Link to reports information here https://pressbooks.bccampus.ca/professionalcomms/chapter/5-5-reports/.) 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.”