Management Information Systems (33:136:370) 
Spring 2016, Professor Eckstein
Assignment 3

Due: Friday, February 12

Q1.   Compute the amount of storage needed for the following.  Show your work.

  1. A 50-line e-mail, transmitted as text, with an average of 65 characters per line.  Express the result in binary-style KB.
  2. The BOOK table from assignment 1, Q1, if it contains information on 20,000 books.  Assume that the ID number is a long (32-bit) integer.  Express the result in binary-style MB.
  3. A color photo scanned at 1200 dots per inch (dpi), 3 x 5 inches, in 24-bit color.  Express you answer in binary-style MB.  
  4. A 5-minutes video clip with 24 frames per second, 1920 x 1080 resolution (usually referred to as "1080p)", and 24 bits per pixel, with no audio.  Express your result in binary-style GB.

For each of the following situations, design a database to hold the specified information drawing an entity-relationship (ER) diagram and writing a database design outline for the database required to hold the information described in the problem.  A database design outline is a description of a database using the following notation (shown here for an example we used in class).  Primary key fields must be underlined and foreign keys annotated with "attribute foreign key to TABLE".

CUSTOMER(CustomerID, FirstName, LastName, StreetAddress, City, State,
                      Zip, Phone)

LOAN(LoanID, Date, Loan Amount, Rate, Term, Type, CustID)
        CustID foreign key to CUSTOMER

PAYMENT(LoanID, PaymentNumber, Date, PaymentAmount)
        LoanID foreign key to LOAN

In your answers, all tables must have a primary key; you may add synthetic-key "ID" fields to tables whenever necessary. 

An entity-relationship (ER) diagram means a graphical depiction of the database structure as given in class.  For the example above, the diagram would be as follows: 

In each question below, assume that any “address information” specified is in U.S. format, consisting of fields for street address, city, state code, and zip code.  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.

Q2.  Problem 7 on page 30 of draft chapter 4 (swim club membership records).

Q3.  Problem 8 on pages 30-31 of draft chapter 4 (the one-person consulting business).

Q4.  Problem 17 on page 33 of draft chapter 4 (the student case competition).