Final Exam Fall 2020 CSCI 3287 Database Systems Concepts

Work alone to complete your own paper. Use the terms, formal notation for diagramming, and model definitions that we learned in class to answer the questions. If you make any assumptions related to your answers write them on the page. You will get credit for correct answers based on your assumptions. You are the designer. Solve the problems any way you feel is correct according to the principals and formal models that we have studied. There is more than one correct answer for some of the questions depending on the assumptions you make. Cautions: • Failure to put your name on your test (I recommend putting it on each page) will result in a grade of 0.

(This has been a serious problem all semester and I may not have time to look into it by the grading deadline, so please get your name onto your final.)

• Any kind of collaboration or academic dishonesty will result in a grade of 0 for your final, an F for your course grade and a formal report to the college about your activities.

Due Date: This is a take home test due back by Friday 12/13/2019 at 6:30 PM. Delivery:

• The easiest way to take this test is to print it, complete it (in pencil or pen), scan and upload to Canvas.

• You may also type your answers in this document and upload it to Canvas as a pdf.

Good luck and good skill!

_______________________________________________________________________

Manthan Baboo
Manthan Baboo
Manthan Baboo
Manthan Baboo
Manthan Baboo
Manthan Baboo
Manthan Baboo
Name: ___________________________________________ SID: _____________________________________________ There is a database online at the Family Watchdog site that uses the National Sex Offender registry data to deliver information to the public. You can visit the database here to get a feel for it: http://www.familywatchdog.us The purpose of the database is to store information about sex offenders and their location so that parents can do these things to protect their kids.

1. Know who is in their neighborhood 2. Use Family Watchdog to locate sex offenders near their home and their kid’s

school. 3. Empower their kids with information 4. Change the laws 5. Support the Child Safety Act of 2005

The database views include * maps with home and work locations identified * lists * recent address changes * free alerts * send maps to a friend screen * notify/add a missing school or public facility * count mappable and non-mappable (7%) offenders by area. * show parks, schools and other public places where caution should be used. * categorization of the criminals that are registered by crime. A badly designed ER diagram is attached as Exhibit A. In fact, it’s one table, unrelated to any others.

Name: ___________________________________________ SID: _____________________________________________

1. Joe Amateur has designed the OFFENDER table of the National Sex Offenders Registry without much care or concern for programming, redundancy or data integrity. You’ve been hired to straighten out the mess by implementing properly related tables. You’ve been able to come up with the ER diagram in Exhibit A. Now show functional dependency diagrams and the steps required to construct the schema constructs for tables in 3rd normal form. Assume that the following additional FD occurs in addition to what you can read from the ER diagram.

{ZipCode}¬ {City, State}

a. Show 1NF and tell what the rules are for first normal form. (10 pts)

Name: ___________________________________________ SID: _____________________________________________

b. Show 2NF and tell what the rules are for second normal form. (10 pts)

Name: ___________________________________________ SID: _____________________________________________

c. Show 3NF and tell what the rules are for third normal form. (10 pts)

Name: ___________________________________________ SID: _____________________________________________ 2. Assume that the National Sex Offenders Registry is a distributed database. RELY ON YOUR NORMALIZED TABLES TO ANSWER THE FOLLOWING QUESTIONS.

A: You may need to add tables or attributes to the ER diagram in Exhibit A. The government decided that: • OFFENDERs, ALIASES AND ADDRESSES records that have not been

changed in 20 years will be archived in New York while … • a complete set of the more current OFFENDERs, ALIASES AND

ADDRESSES records will be kept in Denver and in Washington, DC. • All MAP data is kept in both Los Angeles and New York. • CONVICTIONS data is kept in 5 regional locations as follows:

o CONVICTIONS records from WA, OR, CA, NV, ID, UT, AZ, MT, HI, AK are kept in Los Angeles.

o CONVICTIONS records from NM, CO, WY, ND, SD, NE, KS, OK and TX are kept in Wichita.

o CONVICTIONS records from MN, IA, MO, AR, LA, MS, TN, KY, IL, WI, and IN are kept in Des Moines.

o CONVICTIONS records from MI, OH, WV, VA, NC, SC, GA, AL, FL are kept in Miami.

o CONVICTIONS records from ME, VT, NH, NY, MA, CT, DE, MD, PA, RI, DC are kept in Boston.