1. This is the first time I have ever given this homework assignment so things may be especially unclear. Please post questions to Piazza so that I can clarify and so that everyone sees my clarifications.

1. (20 points) Suppose you have been hired by the Great Smoky Mountains National Park to digitize their accumulated collection of:
   • photos, old films (you should assume the films are on reels that were taken with cameras and now need to be digitized), and tape recordings of interviews with historical figures associated with the park.
   • historical artifacts, including both physical artifacts (e.g., old farm implements) and written artifacts such as maps, letters, memos, and public announcements,
   • written historical accounts by various figures including settlers, rangers, visitors, and park personnel, and
   • scientific research reports.

   In addition to digitizing the collection, they also wish you to organize it and present it to the public via a web-site.

   Please discuss:

   1. how you will digitize the various elements of the collection.
   2. the storage formats you will use for the various elements of the collection.
   3. how you will store the collection (e.g., database, document collection, digital library, repository, etc.)

   Please refer to the information management notes and use the information they provide to justify your decisions.

2. (20 points) Suppose I want to design an XML file that encodes exam questions and their answers. There are four types of exam questions: 1) true/false, 2) multiple choice, 3) essay, and 4) matching. The questions have the following information associated with them:
   • true/false questions have a question statement and the answer.
   • multiple choice questions have a question statement, some number of choices, and the correct answer.
   • essay questions have a general question statement, and then one or more parts, each of which consists of a question statement and an answer. Finally an essay question may have an optional hint.
   • matching questions have a question statement, one or more potential answers, one or more statements that are to be matched with the answers, and an answer key that contains statement-answer pairs (i.e., each pair has two parts, a statement and the correct matched answer for that statement).
   Additionally each question contains the number of points that it is worth (the parts of an essay question do not have point values associated with them--only the overall question does).

   Your solution should show me the hierarchy of element tags you will use. Place an asterisk (*), plus (+), or question mark (?) next to an element if it can occur 0 or more times, 1 or more times, or 0/1 times respectively. If you require one of two elements or sets of elements, you can write <element1> | <element2> and use the following syntax:

   <element1> ... | <element2> ...

   Hints:

   1. Formatting, such as horizontal rules and question numbers/letters, should not be part of your structure.
   2. Here is a sample specification for the address book example presented in class:

      addressbook
          contact+
              name
                  firstname
                  middlename?
                  lastname
              address
                  street
                      street_number
                      street_name
                      street_suffix
                |
                  pobox?
                  apt
                  aptname
              city
              state
              zip
      

   3. Do not worry about the types of the data contained in the elements (e.g., integer, string, etc.). All I care about is the way you structure your data.
   4. Things you might want to think about:
      • What appear to be the major parts of the exam? The major parts might serve as tags.
      • Do the questions seem to fall into certain types of categories? The categories might serve as tags.
      • Do the questions seem to share some parts in common? How could you cleanly handle the fact that questions share some parts in common and have some differences?

3. (20 points) This problem is meant to give you practice with using html tags to specify the structure of a web file and css formatting commands to specify the presentation of a web file. Modify my exam.html file so that it re-creates the formatting of the web-page shown in the attached pdf file. Do not worry about the page breaks in the printed document--the printed document should be thought of as one continuous web-page.
   • My exam.html file contains all of the content that you need. You can copy it from /home/bvz/cs365/hw/hw7/. It appears to all run together right now because I have omitted all the required html tags. I have added html comments that show you where each question starts (e.g., <!-- 1 -->) and a few other html comments to delineate content.
   • You need to add html mark-up tags and css commands.
   • Add the css commands to the top of the file in the <head> section. You may not use in-line styles.
   • You will see several instances of the entity codes &lt; and &gt;. Do not modify these entity codes. They cause the browser to create the symbols < and > respectively.
   • There are no links in the exam. Everything that is underlined was created with a css formatting command.
   • Replicate my formatting to the best of your ability. Here are some specific requirements:
     • The blue directions at the top of the exam are using a large, sans-serif font and the text is bold-faced.
     • The left margins for the horizontal rules, section headers (e.g., "Multiple Choice Questions"), and the green directions for each exam section must be made negative to pull them over to the left. Otherwise they will appear indented inside of the list.
     • The indented lines in parts a-d for question 4 are indented by 0.25 inch.
     • I used 5 pixels of padding in my table cells for questions 4 and 8.
     • In question 13 I used spans with 0.25 inches of left and right margin to get the nice spacing in parts a-c.
     • The image you need to use for question 14 is BTreeProblem.jpg. It should exactly fit the width of the browser. You can copy this file from /home/bvz/cs365/hw/hw7/.
     • The green instructions at the start of each exam section use a large font size and are both italics and bold-faced.
     • You must provide a 4 inch answer space for the B-tree problem, question 14.
     • You must provide 2 inch answer spaces for each of the two subparts for question 15.

4. (20 points) Suppose you have a credit card application form where you collect the following information. All fields are required unless they are explicitly indicated as optional.
   • First, middle, and last name: The middle name is optional.
   • Social security number
   • Street address, apartment name, unit number, po box, city, state, zip code: The apartment name, unit number and po box are optional. Apartment names/unit numbers are fairly common but po boxes are fairly rare.
   • email address
   • phone number: optional but generally provided.
   • existing credit cards including type (e.g., visa or master card), account number, and expiration date: There may be any number of credit cards listed. Assume that credit cards expire on the last day of the month and remember to convert the expiration date to the correct format for a date type, as I have not presented them in the format generally used for storing dates in a database.

   Here is some concrete data from three applications:

   1. • Smiley/H./Vander Zanden
      • 586-39-3968
      • 1678 Cardiff Rd./Columbus/OH/43221: No apartment name, unit number, or po box
      • smiley@gmail.com
      • 865-486-3153
      • credit cards
        • visa/3868-1048-4866-1048/10-20
        • master card/4948-3867-2396-4868/06-23
        • visa/3868-1796-1863-4593/03-22
        `
   2. • Daffy/Duck: no middle name
      • 396-83-5839
      • Warner Bros./Hollywood/CA/83969/po box 240 (Warner Bros. is the street address)
      • daffy.duck@warner.com
      • phone number: not provided
      • credit cards: none provided
   3. • Smoky/The/Bear
      • 484-48-1108
      • Great Smoky Mountains/Gatlinburg/TN/36939: no po box, apartment is "KOA Campground", and unit no is 362 (Great Smoky Mountains is the street address).
      • smoky@smokies.gov
      • phone number: not provided
      • credit cards
        • american express/4768-3968-6848-4753/08-19
        • master card/4948-5000-6000-5888/12-24

   For each of sql, xml, and json, show how you would represent the information for these three applicants (show me the concrete data, not just the sql table/column names or the xml element tags--I want to see what the file would like).

   1. sql: Put the data into the appropriate tabular format and show me your column names (see question 5 for an example of how I want the tables represented).
   2. xml: Show me the xml file you would use, complete with element tags and the data embedded in these element tags. In question 2 you were just showing me your tags. In this question you are showing me the complete xml file that you would create.
   3. json: Show me the json string you would generate--please do this in a nice, easy-to-read way so that the TA's can easily read your representation.

5. (20 points) You are given the following two sql tables:

   CourseCatalog
   CourseId	Title	Description	CreditHours	LabHours	Department
   COSC102	Introduction to Programming	Problem solving and algorithm development...	3	1	EECS
   ECE255	Introduction to Logic Design	Standard codes, number systems, base conversions, ...	3	0	EECS
   MATH141	Calculus 1	Single variable calculus especially for students...	4	0	Math

   TimeTable
   CourseId	SectionId	Days	StartTime	EndTime	Room	Enrollment
   COSC102	001	TR	11:10:00	12:25:00	MK 622	125
   COSC102	002	TR	14:10:00	15:25:00	MK 622	97
   MATH141	005	MWF	11:15:00	12:05:00	AY 105	35

   Write sql queries to retrieve the following information:

   1. The CourseId and Title of all courses offered by the EECS department.
   2. The CourseId and total enrollment of all courses whose total enrollment is greater than or equal to 200. By total enrollment I mean the combined enrollment for all sections of a course.
   3. The CourseId, SectionId, Title, Description, Days, StartTime, and EndTime of all sections that start after 11:00:00 and end before 15:30:00.

What to Submit

Submit the following files:

1. answers.txt (questions #1, 2, 4, and 5)
2. exam.html (question 3)