MIT1_204S10_assn2

MIT1_204S10_assn2 - 1.204 Computer Algorithms in Systems...

Info iconThis preview shows pages 1–3. Sign up to view the full content.

View Full Document Right Arrow Icon
1.204 Computer Algorithms in Systems Engineering Spring 2010 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, Arlington1204.mdf. This database contains just a single table, Resident; it’s really just a data file. The attributes are defined below. 1. Build a fully normalized data model, using Visual Paradigm’s entity-relationship (data) model diagram. a. Identify the entity names, primary and foreign keys, relationships and their cardinality, and all attributes. b. 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.) c. 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. d. As you browse the Resident data file, resolve data inconsistencies arbitrarily and simply. For example, if one member of a household has cable TV (CATV) or fiber optic (FIOS) or gas service, the household has the service. e. 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. f. 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 relationships. g. Hand in the .vpp file as part of the zip file you submit for the homework. 2. Implement your data model in MS SQL Server, with all the data included in the Arlington1204 database except as noted below . a. Build the database following the data model you create in the first step of the homework. 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.
Background image of page 1

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
b. Include the queries and temporary tables that you used to construct the database, even though you would normally delete them. Give each query a
Background image of page 2
Image of page 3
This is the end of the preview. Sign up to access the rest of the document.

This note was uploaded on 12/04/2011 for the course ESD 1.204 taught by Professor Georgekocur during the Spring '10 term at MIT.

Page1 / 6

MIT1_204S10_assn2 - 1.204 Computer Algorithms in Systems...

This preview shows document pages 1 - 3. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online