cs636 class 5

CS636 Class 5

Hw2 is available 

Database Tables-- look at createdb.sql, found in the database directory of the pizza1 project.

To use createdb.sql:  the database directory has a separate build.xml, so after "cd database", ant commands use this second build.xml.

In the database directory, use “ant load-oradb” or “ant load-mysqldb” or “ant load-hsqldb” with build.xml in pizza1/database.

Also see targets show-oradb, drop-oradb, etc.

Note: for HSQLDB, you first need to “ant start-hsqldb” in a separate window.

Looking at createdb.sql:

Tables menu_toppings and menu_sizes just list possible toppings and sized: ones on the menu presented to students

Note their UNIQUE constraints, so "Pepperoni" is listed only once here.

Table pizza_orders holds orders. Orders can have multiple toppings, but only one size.

To attach multiple toppings to one pizza order, there is a pizza_topping table with a FK to orders for the order this row is for.  This is a N-1 relationship.

Note that pizza_topping does not have a UNIQUE constraint on topping_name. That allows multiple orders to have the same topping--see example below.

Each order has its own rows in pizza_size and pizza_toppings to describe its details. That way, we can delete an order and its details without disturbing any other order.

There is no FK from the pizza_topping topping_name to the menu_topping topping_name. This allows a topping to be deleted from the menu when it is no longer available. The program will use a topping_name from menu_toppings to create a particular topping for a new pizza.

Example of Database

All but one of the tables have "id" columns that have artificial values generated by the program, and id used as the PK.

pizza_orders

id

room_number

size_id

day

status

1

5

1

1

1

2

3

2

1

2

pizza_toppings: id is PK, order_id is FK, topping_name not unique. One row represents an individual topping for a certain pizza. Here pizza order 1 has Onions and Pepperoni. Order 2 has only Pepperoni.

id

order_id

topping_name

1 1 Onions

2

1

Pepperoni

3 2 Pepperoni

pizza_sizes: id is PK, size_name is not unique

id

size_name

1

small
2 large

From these 3 tables, we see that there are two pizza orders, order 1 with two toppings, Onions and Pepperoni, and size small, and order 2 with one topping, Pepperoni, and size large. Order 1 is PREPARING (status=1), for room 5, and order 2 is BAKED (status = 2), for room 3. Both were made on day 1.

These orders were created with the help of menu_toppings and menu_sizes.  Here are possible contents for them:

menu_toppings: id is PK, topping_name is UNIQUE

id

topping_name

1

Pepperoni

2 Onions

menu_sizes: id is PK, size_name is UNIQUE

id

size_name

1

small

2 large
3 huge

Java Objects of Application

Remember the layers. We were just looking at the database at the bottom. The DAO layer converts database data to Java objects that are used by the business programmers of the service layer, the heart of the application. Let's look at those objects now.

Our domain classes are POJOs, or Java Beans

Here the domain classes are PizzaOrder, PizzaTopping, and PizzaSize. Also, MenuTopping and MenuSize. Of these, PizzaOrder is mutable, because its status changes: preparing->baked->finished. PizzaTopping and PizzaSize (as well as MenuTopping and MenuSize) are immutable/invariant : once created as objects, they don’t change.

Pizza order with topping and size classes. (The MenuTopping and MenuSize classes are separate, not used for a "live" order.)

Simple POJO – plain old Java object has “properties”, each of which has a getter and / or a setter

Example: PizzaOrder

   public int getId()

   public void setId(int id)

Defines “id” as an int property of PizzaOrder. Usually we also see a field private int id; (but not required)

Java Beans also are expected to have a no-args constructor and implement Serializable, See pp. 174-175 in Murach. Also see Java tutorial at http://docs.oracle.com/javase/tutorial/javabeans

Drawing of objects in ovals: put arrowheads on the lines going downwards from PizzaOrder and the set implementation objects. Each arrow represents a Java object reference.

 
   <PizzaOrder>-----><Set_implementation>-----><PizzaTopping>
           |                           \
           |                            \
        <PizzaSize>             <PizzaTopping>                       
      

This is an object graph. The PizzaOrder class has field "private PizzaSize pizzaSize;" that specifies an object reference from each PizzaOrder to a PizzaSize object.

The PizzaOrder class has field "private Set<PizzaTopping> pizzaToppings;" that specifies an object reference from a PizzaOrder object to a set implementation object (of class HashSet or TreeSet).  Then that set object contains refs to the individual PizzaTopping objects for the pizza order.

Note: it is essential that you understand object graphs and how to draw them.

What does it mean to “navigate” between objects?

It means to follow an object reference from one object to another. For example, the PizzaOrder object has a field of type PizzaSize, so it has an object ref from PizzaOrder to PizzaSize.You can see this by looking at the object graph diagram.

This setup allow navigation from a PizzaOrder to its PizzaSize, but there is no Set in a PizzaSize to allow navigation from a PizzaSize object to all the PizzaOrders using that size.  Therefore, the arrow goes from PizzaOrder to PizzaSize, but not vice versa. 

Optional Material: UML Class Diagram

Similar diagram at the Java class level, the UML Class diagram :  Stars are used to show cardinalities of relationships, like this: (diagram using Gliffy.com).

 

Arrows in UML Diagrams

UML class diagram for Pizza Project Domain Classes:  arrows indicate uni-directional relationships.  PizzaOrder has a Set field, but Topping does not have a Set field. 

This means you can “navigate” from a given PizzaOrder to its various Topping by iterating through the Set, but you can’t navigate from given Topping object to all the various PizzaOrders using this topping, because there is no information in the Topping object to help you do this.

See http://en.wikipedia.org/wiki/Class_diagram

End of optional material

Entity-Relationship Diagrams of the Database Tables

E-R Diagram has three entities, PizzaOrder, PizzaTopping, and PizzaSize, with an N-1 relationship between PizzaOrder and PizzaTopping, and a 1-1 relationship between PizzaOrder and PizzaSize. 

E-R diagram in UML style: just drop the arrow points, since database tables do not express directionality in relationships.

Here the two stars mean N-N, so there needs to be a relationship table in the database.

The relationship with one star is N-1.  It is implemented by a foreign key in the table at the * end. Here, each pizza order has one pizza size, so the pizza order row can “point” to the right pizza size row using a foreign key, with value the id of the pizza size row. More on this next time.

 

pizzaer.png

The Pizza database ER diagram. (The MenuTopping and MenuSize entities are separate and unrelated to these.)

Chen Notation There are several styles for E-R diagrams. The most classic and common in textbooks is the Chen notation, which puts a diamond on each relationship connection.

Murach uses something in between Chen and UML. See pg. 663 for an example. He would use *:* to label PizzaOrder-Topping and *:1 to label PizzaOrder-PizzaSize (PizzaOrder---*:1---PizzaSize). 

You can see that you can move the * down to PizzaOrder, and the 1 down to PizzaSize, and end up with the UML setup.

Review of PK and FKs:

Primary Key (PK): must have a unique id for each row. Only one PK column (or set of columns) is allowed for a table, although a table may have other unique columns. We use a UNIQUE constraint to mark an additional unique column (or set of columns) in a table.

Example : The menu_toppings table has column "id" for PK, so id must be unique, while topping_name also has a unique constraint, so it’s a key as well, but not the primary key.  Here id is an artificial key or surrogate key, one that is assigned at insert time to be unique but its value has no meaning in the application.  Topping_name is called a natural key or a business key, since it does have meaningful values like « mushrooms » or « pepperoni ».

Foreign Key (FK): has value on each row that equals a PK value of another table (or sometimes the same table) to specify which row this row is related to.

(A FK value can be null, if allowed by the column setup, in which case no other row is related to this one.) 

(Also, you can override the default of using the PK of the target table, but we will not need to.)

Using FK constraints is important to keep the database healthy. With the FK from pizza_order to pizza_size in place, an insert of an order with a bad size_id will fail. Also, the deletion of a size row that is in use by pizza_orders rows will fail. Also, FK constraints make clear what the relationships really are between the tables.

How pizza1’s  SystemTest Runs

SystemTest execution : involves two pizza orders, and actions on them

But first, it reinitializes the DB, adds one size and one topping.

Because of this, all runs of SystemTest end up with the same database contents.

First pizza : for room 5

Second pizza : for room 1

Database contents after whole run :

menu_toppings: id is PK, topping_name is UNIQUE

id

topping_name

1

Pepperoni

menu_sizes: id is PK, size_name is UNIQUE

id

size_name

1

small

pizza_orders

id

room_number

size_id

day

status

1

5

2

1

3

2

1

3

1

3

pizza_toppings: id is PK, order_id is FK, topping_name not unique

Here both orders have topping Pepperoni

id

order_id

topping_name

1

1

Pepperoni

2 2 Pepperoni

pizza_sizes: id is PK, size_name is not unique

id

size_name

2

small
3 small

Pizza_sys_tab (one row table, not discussed in class, but here for completeness)

| next_menu_topping_id | next_menu_size_id | next_order_id | next_pizza_topping_id | next_pizza_size_id | current_day |
|                    2 |                 1 |             3 |                     3 |                  4 |           2 |

  Note: day 1 was finished, so current day is 2

File test.dat, input to SystemTest: tests both student and admin actions

ai          admin initializes system (after “ant load-oradb” for example)
so 5        student order from room 5
so 1        student order from room 1
ss  5       status of orders from room 5  (answer—one PREPARING)
anr         admin reports next pizza done (the one for room 5)
ss  5       status of orders from room 5  (answer--one, now BAKED)
aip         admin report on in-progress orders (one PREPARING, one BAKED)
sr 5        student receives the room-5 order (acknowledges its receipt)
ss 5        status of orders from room 5  (answer—one FINISHED)
aip         admin report on in-progress orders (one PREPARING)
aad         admin advances day (so pizza for room 1 is now FINISHED)
aip         admin report on in-progress orders (none)


Actions to try:  Progression from command line execution, to using ant, to using eclipse