Unformatted text preview:

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.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 descriptive name, and indicate the order in which they were executed by starting their names with a, b, c, d, … or 1, 2, 3, 4, … Use SQL Server comments for this; they start with --. c. If there are transformations required to implement your data model that are difficult (for example, involving parsing, or transformations that require programming), do not implement them. Choose a simple approach. d. Create appropriate primary and foreign keys, and relationships in the data. e. Hand in the electronic version of the database, in the zip file, including the .sql file with the queries that you wrote to implement it. Do not remove the original Residents table or any temporary tables you may have constructed, even though you normally would, to allow us to grade your assignment. Hints: a. You can create most of your tables through the SQL statement ‘SELECT select_list INTO table_name FROM table_source WHERE search_condition. Please see the Murach SQL Server book, which is online, for how to do this. After the table is created, use the SQL Server MSE client to set its primary key and possibly make other changes interactively. See the instructor for help with this; we’ll try to do a short demo in class as well. Remember that primary keys cannot have null values. b. To create domain entities, use the SQL statement ‘SELECT DISTINCT select_list INTO table_name FROM table_source WHERE search_condition. Again, set the primary key with the SQL Server MSE client. c. When creating relationships between tables in SQL Server, make sure that the corresponding columns in both the tables have exactly the same data types. For example, they may/must both be nvarchar(20): they must have the same type and the same length. If you’ve used SELECT INTO statements to create your tables, you will have consistent data types. You need to be careful with any new attributes that you create. d. For one or more tables in this homework, you will need to create a primary key that is not one of the columns present in the table. You will need to use the Identity option to create a primary key. Please see the Murach SQL Server book, which is online, for how to do this. e. While zipping the database, you will need to stop the SQL Server so that the lock on the .mdf file is removed. The procedure for stopping SQL Server is described in the “HowTo: Install SQL Server” document. Briefly: use the SQL Server Configuration manager, right click on SQL Server Express and select ‘Stop’. After copying, start SQL Server Express again. 3. Implement two interesting queries after you’ve built your database. You are free to implement any queries you wish. For example: a. How many households have CATV, FIOS and gas service in all three years? b. How many households dropped CATV (or FIOS or gas) service from any year to any other?c. What is the average size of households with CATV service, by year? Add the two queries at the end of the .sql file that you submit in part 2 above. This is not a programming or data processing homework. The focus is on modeling issues and core database concepts (entities, attributes, primary keys, foreign keys and relationships), and their application to an actual problem. Don’t spend a lot of time in mechanical manipulations; choose a simple approach, discard some offending data, or contact the instructor to discuss how to get around any data problems you’re encountering in your approach. Resident table: Lname: Last name, upper case Fname: First name, upper case Yr: Birth year (1800 if unknown) Mo: Birth month Day: Birth day Midname: Middle name, upper case Suffix: Name suffix (Jr, Sr, II, III, IV), upper case Stnum: Street number Stlet: Street letter (e.g., the ‘A’ in 12A) Stname: Street name, upper case Apartment: Apartment number or letters Sex: M or F Occ: Occupation as free form string Dist: School


View Full Document

MIT 1 204 - Problem Set 2- Municipal Database

Download Problem Set 2- Municipal Database
Our administrator received your request to download this document. We will send you the file to your email shortly.
Loading Unlocking...
Login

Join to view Problem Set 2- Municipal Database and access 3M+ class-specific study document.

or
We will never post anything without your permission.
Don't have an account?
Sign Up

Join to view Problem Set 2- Municipal Database 2 2 and access 3M+ class-specific study document.

or

By creating an account you agree to our Privacy Policy and Terms Of Use

Already a member?