417 ACTIVITY 14: Developing a Data-to-Process CRUD Matrix

WHY:

This activity reviews the creation of a Data-to-Process CRUD matrix, which is important in the synchronization of system models. Without such synchronization, we would have two sets of incomplete diagrams, data model diagrams and process model diagrams, without any way to determine what is missing from each. You will use this matrix in your projects and document its entries in the description box for each attribute in the data model. (I.e. The attribute is Created by process A, Read or used by processes B, C, ..., Updated by processes 1, 2, ..., and Deleted by process Z.)

LEARNING OBJECTIVES:

1. Review data and process models
2. Learn how to construct a Data-to-Process CRUD matrix.
3. Discover how to use this matrix for model synchronization.

PERFORMANCE CRITERIA:

1. Quality of the answers to the Critical Thinking Questions.
   • Completeness
   • Clarity
   • Depth
2. Quality of the CRUD matrix you complete for your project.

RESOURCES:

1. Chapter 7, Systems Analysis and Design Methods
2. Activity 11.
3. Lab Activity 5
4. 30 minutes

PLAN:

1. Choose roles if you have not already done so.
2. Look over the model and answer the Critical Thinking Questions.
3. Work on your project milestone if time.

MODEL:

Consider the Textbook Inventory System for which we developed a process model in Activity 11. The data model for this system is given below, followed by its Data-to-Process CRUD matrix. Note that the processes in the D-to-P CRUD matrix are taken from the Systems Diagram in Activity 11.

Entity List

The entities in this system are TextBook, Purchase Order, Packing Slip, and Book List. These were discovered by looking at the data stores in the System Diagram in Activity 11. Every data store in the System Diagram must correspond to an Entity in the data model.

The relationship matrix is as follows:

                  Textbook     Purchase Order	  Packing slip     Book List
Textbook                      ordered through	   listed on	  contained in
Purchase Order	  orders			   generates
Packing Slip	   lists       generated by
Book List	  contains	 	  

The Context data model is as follows:

 ___________________					__________
 |  Purchase Order |		generates		| Packing|
 |	       	   |-++------------------------------+<-|   SLIP |
 -------------------					----------
	W						    W
	0						    0
	|						    |
      orders						    |
	|						    |
	+						    |
	M						    |
 --------------						    |
 | Textbook   |			listed on		    |
 |	      |->+-------------------------------------------
 --------------
        W
        +
	|
   contained in
	|	
	0
	M
 -------------	
 | Book List |
 |___________|

Key-Based Data Model

The Primary Keys of the entities are: ISBN for Textbook, PO-Number for Purchase Order, Student-ID, Date, and Time for Book-List, and Pack-Slip-No for the Packing Slip. The relationships between all the entities is many-to-many, except for the Purchase Order to Packing Slip which is 1 to many. (It is possible for the items on one purchase order to be delivered in several batches, thus generating several packing slips). All the many-to-many relationships must be replaced by Associative Entities yielding the following key-based ERD:

 ________________					__________________
 |Purchase Order|	generates			| Packing Slip   |
 |---Key Data-- |-++---------------------------------+<-|---Key Data-----|
 |PO-Number[PK1]|					|Pack-Slip-No[PK1]
 ----------------					|PO-Number [FK]  |
	+						------------------
	+						    +
	|						    +
      orders						contains
	|						    |
	+						    +
	M						    M
 -------------------				  -----------------------
 | Textbook-PO     |	      	 		  |Textbook-Packing Slip|
 |-----Key Data----| 				  |-----Key Data--------|
 | ISBN [PK1][FK]  |				  |ISBN [PK1] [FK]	|
 |PO-Number[PK1][FK]				  |Pack-Slip-No[PK1][FK]|
 -------W-----------				  ----------W------------
	0		includes			    +
	|-----------------------------------------|         |
      						  |     includes
						  |	    |
						  +	    +
						  +	    +
 --------------------				  ------------
 |Textbook-Book-List|	  lists 		  |Textbook  |
 |---Key Data-------|->+-----------------------0+-|-Key Data-|
 | ISBN [PK1][FK]   |				  |ISBN [PK1]|
 |Student-ID[PK1][FK]				  ------------
 | Date [PK1][FK]   |
 | Time [PK1][FK]   |						  
 -------------------- 
        W
        +
	|
   contained in
	|	
	+
	+
 ----------------	
 | Book-List    |
 |---Key-Data---|
 |Student-ID[PK1]
 |Date [PK1]    |
 |Time [PK1]    |
 ----------------

The fully articulated data model is as follows:

 ________________					__________________
 |Purchase Order|	generates			| Packing Slip   |
 |---Key Data-- |-++---------------------------------+<-|---Key Data-----|
 |PO-Number[PK1]|					|Pack-Slip-No[PK1]
 |-Non-Key-Data-|					|PO-Number [FK]  |
 |Company-Name  |					|--Non-Key-Data--|
 |Company-Add.  |					|Date-Shipped    |
 |PO-Date       |					|Ship-Method     |
 ----------------					------------------
	+						    +
	+						    +
	|						    |
      orders						contains
	|						    |
	+						    +
	M						    M
 -------------------				  -----------------------
 | Textbook-PO     |	      	 		  |Textbook-Packing Slip|
 |-----Key Data----| 				  |-----Key Data--------|
 | ISBN [PK1][FK]  |				  |ISBN [PK1] [FK]	|
 |PO-Number[PK1][FK]				  |Pack-Slip-No[PK1][FK]|
 |---Non-Key-Data--|				  |----Non-Key-Data-----|
 |Quantity-Ordered |				  |Quantity-Shipped     |
 -------W-----------				  ----------W------------
	0		includes			    +
	|-----------------------------------------|         |
      						  |     includes
						  |	    |
						  +	    +
						  +	    +
 --------------------				  ----------------
 |Textbook-Book-List|	  lists 		  |Textbook      |
 |---Key Data-------|->+-----------------------0+-|---Key Data---|
 | ISBN [PK1][FK]   |				  |ISBN [PK1]    |
 |Student-ID[PK1][FK]				  |-Non-Key-Data-|
 | Date [PK1][FK]   |				  |Title	 |
 | Time [PK1][FK]   |				  |Author	 |
 -------------------- 				  |Edition	 |
        W					  |Publisher     |
        +					  |Price	 |
	|					  |Quant-on-hand |
   contained in					  ----------------
	|	
	+
	+
 ----------------	
 | Book-List    |
 |---Key-Data---|
 |Student-ID[PK1]
 |Date [PK1]    |
 |Time [PK1]    |
 |-Non-Key-Data-|
 |Student-Name  |
 |Student-Phone |
 |Student-Add.  |
 ----------------

The Data-to-Process CRUD matrix is as follows:

			1	2	3	4	5	6	7
Purchase-Order		C	R	R	
  PO-Number		C	R
  Company-Name		C	U	R
  Company-Address	C	U	R
  PO-Date		C	R	R
Packing-Slip			C	R
  Pack-slip-No			C	R
  Date-Shipped			C	R
  Ship-Method			C	R
Textbook-PO		C	R	R
  Quantity-Ordered	C	R	
Textbook-Packing-Slip		C	R
  Quantity-Shipped		C	R
Textbook-Book-List				C	U	R	R
Textbook		CU	U	R	R	R	U	R
  ISBN			CU	R	R	R	R	R	R
  Title			CU	U	R	R	R	U	R
  Author		CU	U	
  Edition		CU	U
  Publisher		CU	U
  Price			CU	U	R		R		R
  Quantity-on-Hand	C	U		R		U
Book-List					C	R	R	R
  Student-ID					C			R
  Date						C			R
  Time						C
  Student-Name					CU			R
  Student-Phone					CU
  Student-Address				CU

The following processes correspond to the numbers at the top of the CRUD matrix.:

1. Generate Purchase Order
2. Update Inventory After Shipment
3. Request Payments
4. Check Availability
5. Look Up Prices
6. Update Inventory After Sale
7. Prepare Sales Receipt

CRITICAL THINKING QUESTIONS

These will be handed out in class.

Return to the List of Activities