MS SQL SERVER 2014 - Databses You recently started work as a researcher for a co
ID: 3768518 • Letter: M
Question
MS SQL SERVER 2014 - Databses
You recently started work as a researcher for a college. The jobs main responsibility is to query data from the colleges Database and produce reports. The first report you have been asked to create involves student demographic data (age, race, ethnicity, gender, etc). After doing some digging around the database you locate which tables you need to query to generate the report. You break the report in to age ranges of: under 18, 18 to 24, 25 to 39, 40 to 65, 65 to 99, 100+. You then release the report to your boss to distribute to the college executives. A few days later the college president stops by your office. He is impressed with your work but had a few questions. He noticed that the college has 5 students who are over 100 years old. He wants to do something special for their birthdays to show his appreciation and maybe use the occasion to generate some good publicity for the college. He asks you to pull up the addresses and birthdays for those 5 students. After he leaves you quickly get to work. You find the correct fields in the database and generate a report. Looking over the report you notice that all five students have the same birthday 1/11/1911. You decide to make some phone calls and find out what’s going on. To make a long story shorter you discover that the “Date of birth” field is a required field when registering students but some of the remote sites put 1/11/1911 in this field when students fail to provide a date of birth or the one provided is clearly wrong (for example when they put in today’s date).
Based on this information prepare a post that addresses the following:
What do you think should be done to address the data integrity issue of dummy values?
What should you always check for when creating a report to list ages?
Is there anything else you can do to detect bad date of births besides looking for 1/11/1911?
Explanation / Answer
Database is just a warehouse for storing information. The issue with dummary values like the one said here with dates is common in any database. my view is that the programmer should take good care when entering data into DB, either validate or let the DB accept null values or enter something like '1/1/1900' which is pretty standard equivalent to null date - there are no much options to it.
When creating a report one must be thorough for checking the final report thorougly like counts, totals, other data grouping/summary etc. Mainly when operating on dates one should take care of timezones information, group by datetime columns have different values when seconds are also stored, no more care is required when field is date with time.
Nothing much can be done to check bad DOBs liek 1/11/1911 - we can take 1/1/1900 also which is somewhat default value provided when wrong DOB is entered.
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.