Homework answers / question archive / For each of the following problems, design an appropriate database (drawing an ER diagram and writing a database design outline) In each question below, assume that any “address information” specified is in US format, consisting of fields for street address, city, two-letter state code, and zip code

For each of the following problems, design an appropriate database (drawing an ER diagram and writing a database design outline) In each question below, assume that any “address information” specified is in US format, consisting of fields for street address, city, two-letter state code, and zip code

Management

Share With

For each of the following problems, design an appropriate database (drawing an ER diagram and writing a database design outline) In each question below, assume that any “address information” specified is in US format, consisting of fields for street address, city, two-letter state code, and zip code. Unless stated otherwise in an individual problem, assume that you do not have the master zip code table in your databases, so that city, state, and zip code may be treated as independent. When the problems specify that you should store a “date/time,” assume that you will store dates and times together in MS?Access?style “date/time” fields, which are able to store a date and time together in a single attribute. 1. You operate a community website where registered users may post ratings and reviews of area restaurants. Each registered user has a unique screen name, along with a first name, last name, address information, e?mail address, and phone number; however, your public website displays only the screen name. For each restaurant, you want to store a name, address information, phone number, website URL, category (“steakhouse,” “Chinese,” etc.). Each review consists of a single registered user’s opinion of a single restaurant and consists of a food quality rating on a scale of 1 to 5, dinner cost paid per person, and comments (like a “long text” field in Access 2013 or later, or a “memo” field in earlier Access versions). Each registered user may review many restaurants but is allowed to only have one review per restaurant. Draw an ER diagram‚ and write a database design outline for a database appropriate for this information. 2. You are creating an electronic version of the records of your county’s register of deeds. The county has a number of municipalities (towns, villages, cities, etc.), and for each one you want to store a name, a type (city, township, borough, etc.), and date of incorporation. Each municipality has numerous buildings, each of which is identified by a block number and lot number (each building within a given municipality has a unique combination of block number and lot number). For each building, you want to store a street address, zip code, and the year in which it was constructed. Note that zip codes do not correspond perfectly to municipalities: some municipalities have multiple zip codes, and the boundaries of zip codes and municipalities are not perfectly aligned. Each building may have been sold many times; for each sale, you want to record its price, date, and the name of the person or company the building was sold to (you record owners’ names using a single, long “name” field because some owners are corporations). Finally, a small percentage of buildings in the county are in the national register of historic places; for these buildings, you want to record a 255?character description of the unique features of the building, the date it was inducted into the historic register, and the ID number the register assigned to it. Assume that you have access to a data table that includes the zip code and corresponding post office name for each zip code that intersects the county; include this table in your database (all these zip codes are in the same state as your county, so the state need not be stored). Set up your database so that the records for non?historic buildings do not have to store empty fields for historic? register?related data. Draw an ER diagram‚ and write a database design outline for a database appropriate for this information. 3. You manage a tour company that operates tours of local historical and natural attractions. You have several dozen different tour packages, each with a name (such as “Exploring Native Ruins” or “Bird?Watching Hike”), a scheduled duration in minutes, a per?guest price, and a maximum number of guests. You also have a staff of dozens of guides, for each of which you want to store a first name, middle initial, last name, and date hired. For each guest who has ever taken or has booked a reservation to take one of your tours, you want to store a first name, middle initial, last name, gender, date of birth, and mobile phone number. You group guests into households, for each of which you want to store address information (street address, city, state, and zip code), and a primary credit card number. Assume that you have access to a national zip code table that you wish to include in your database design, and that each guest can be a member of only one household. A tour consists of one of your guides taking a group of guests on one of the tour packages. For each tour, you want your system to remember what tour package it was, which guide conducted it, and which guests participated. You also want to store a scheduled start date and time, and an actual completion time (blank for tours that are in the future). Over time, guests can participate in more than one tour. Draw an ER diagram‚ and write a database design outline for a database appropriate for this information. 4. Central Jersey Adult Education (CJAE) runs a variety of night and weekend classes in cooking, exercise, stress management, gardening, art, and other popular topics at various locations throughout central New Jersey. Each course CJAE offers is identified by a course code and has a name, description, tuition fee, and number of course meetings (some courses consist of just one meeting; others may consist of as many as ten meetings). Each course may be offered at multiple times and locations. Each time/location a course is offered is called a “class.” For each class, your computer system must store its location, maximum number of students allowed, instructor, and date/time of first meeting. All CJAE classes with multiple meetings are held once per week, at the same day of the week and time of day as the first meeting. For each location, you want to store a name, address information, and phone number. For each instructor, you want to store a first name, last name, address information, phone number, mobile phone number, and e?mail address. Each class has only one instructor, but instructors can teach more than one class. Students can enroll in more than one class, including classes that are offerings of the same course. For example, somebody might take “pottery studio” several times over the span of a year. However, it makes no sense for a student to enroll more than once in the same class. For each student, your system should store a first name, last name, address information, phone number, mobile phone number, and e? mail address. Students do not receive grades or college credits for CJAE’s courses. Assume that you do have access to a zip code table. Design a database to store this information. Draw an ER diagram, and write a database design outline. You may add “ID” fields wherever necessary.