1.204 Computer Algorithms in Systems Engineering
Problem Set 2: Municipal Database
Due: 12 noon, Wednesday, February 24, 2010
1. Problem statement
You are given a realistic, though fictitious, database drawn from town census, postal,
telephone and other data for the town of Arlington, Massachusetts.
This is in
Arlington1204.zip, a zip file containing a Microsoft SQL Server database,
This database contains just a single table, Resident; it’s really just a
data file. The attributes are defined below.
Build a fully normalized data model, using Visual Paradigm’s entity-relationship
(data) model diagram.
Identify the entity names, primary and foreign keys, relationships and their
cardinality, and all attributes.
Your model does not have to define the data types and it need not define
whether nulls are allowed. (It’s best to do these but, to keep the time
commitment lower, they are not required.)
All data elements in the Residents table are at an individual person level,
though many should be represented at a household level.
The major entities in
the Residents table are persons, households, services and set of domain
entities such as precincts, districts, etc.
Examine the data from the Residents
data file to infer the system rules so that you can create the data model.
As you browse the Resident data file, resolve data inconsistencies arbitrarily
For example, if one member of a household has cable TV
(CATV) or fiber optic (FIOS) or gas service, the household has the service.
You must decide how to model landline phones; there may be zero or more
phones per household, and some may be associated with individuals within
the household. Choose a simple approach.
You are likely to have about 10 or 12 entities in your data model, with at least
one many-to-many relationship. The other relationships will be many-to-one
Hand in the .vpp file as part of the zip file you submit for the homework.
Implement your data model in MS SQL Server, with all the data included in the
Arlington1204 database except as noted below
Build the database following the data model you create in the first step of the
Change the data model if it doesn’t model the actual data
properly. However, you may ignore inconsistencies and errors in the data, or
you may choose a simple approach to resolve the errors.