Refer to the assignment 3 for definitions of what is meant by a database design outline and an entity-relationship diagram.  In all three questions, assume you not have access to a zip code table.  This assignment does not have an MS Access component.
 

Q1.  Central Jersey Adult Education:  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, maximum number of students allowed, 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, 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, city, state, zip code, and phone number.  For each instructor, you want to store a first name, last name, address, city, state, zip code, 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, city, state, zip code, phone number, mobile phone number, and e-mail address.  Students do not receive grades or college credits for CJAE's courses.

Design a database to store this information.  Draw an entity-relationship diagram and write a database design outline.  You may add "ID" fields wherever necessary.
 

Q2.  EcoBus Corporation:  EcoBus Corporation makes hybrid natural-gas fueled buses for various clients, including city, state, and county transit authorities.  This database is concerned with the ordering of buses.  EcoBus currently has dozens of basic bus models, each of which has a unique model number, along with a length, width, height, basic gross weight, and list price.  For each of the firm's customers, you want to store a name (for example, "NJ Transit"), address, city, state zip, code, phone number, and contact person name.  Over time, customers may place multiple orders; for each order, you want to store the date of the order and the percent discount negotiated from the list price of the order.  Each order consists of one or more "production batches".  A production batch consists of a number of buses which are all of exactly the same model and have exactly the same option packages.  Each production batch is identified by the serial number of the first bus in the batch; the remaining buses in the production batch have consecutive serial numbers.  Your system needs to know the number of buses in each batch, the model of bus, and which option packages apply to buses in the batch.

Options are sold only in option "packages", each consisting of one or more options.  Each option package has a unique catalog number, a name (for example, "cold climate engine kit" or "bicycle storage"), a description, and a list price.  Each individual option has a unique option code and description.  Some individual options are in more than one option package.

Design a database to store this information.  Draw an entity-relationship diagram and write a database design outline.  You may add "ID" fields wherever necessary.
 

Q3.  Normalizing a Pizza Delivery Database:   The following tables (with only some of the rows shown) are part of the information system Checkers Pizza uses to track its home delivery business.

The interpretation of the data shown is:

• On November 10, Lucinda Philip placed an order for a large pepperoni pizza (PieNum=1) and a small garlic pizza (PieNum=2). 
• On November 10, Yu-Ran Chen ordered a large plain pizza (with no toppings). Note that the PieNum field is only unique within a given order; thus, the first (and only) pizza in this order also has PieNum=1.
• On November 10, Mohan Singh ordered a large mushroom and peppers pizza (two toppings on the same pizza, with PieNum=1), along with a large artichoke hearts pizza (PieNum=2). For this order, note that the lines with MUSH and PEP both have attribute PieNum equal to 1, meaning they are both for the first pizza in the order.
• On November 11, William Hurd ordered a small sausage, mushroom, and garlic pizza (three toppings on the same pizza – again note there are multiple lines with the same value for PieNum), and a large plain pizza.
• On November 12, Lucinda Philip placed another order, this time for three pizzas: a large sausage, a large extra cheese, and a small garlic.

Design a third-normal-form database storing the same information as these two tables.  Draw an entity-relationship diagram and write a database design outline.  You may add "ID" fields wherever necessary.  This is relatively challenging problem, so please read the above interpretation of the data carefully and be sure you understand it before proceeding with your normalization.