The E-R Diagram should be developed through a step-by-step process. The steps which will be followed are listed below:

Part A:

• A listing of objects (potential entities) on a process-by-process and/or report-by-report basis.
• A listing of attributes for the entities and composite entities.  (Dynamic) Derived attributes should appear only in the validation section.

• E-R diagram showing connectivity, cardinality, optional/mandatory, weak entities, etc.

The relational model should be developed through a step-by-step process. The steps which will be followed are listed below:

• Review the identification of attributes and the E-R diagram from Deliverable I. If modifications are needed (as indicated in grading of Deliverable I) please include those.
• Using the E-R diagram developed in Deliverable I, along with the entities, relationships, and their attributes, determine and specify the relations. Provide the relational schema for each relation.
• Normalize each relation, explaining the introduction of any new attributes, and why each relation does or does not need to be normalized.
• Indicate the primary key in each relation by underlining, and double-underline or italicize foreign keys.
• Turn in a relational schema for each relation in the form
           TABLE_NAME (KEY_ATTRIBUTE¹, ATTRIBUTE², ...ATTRIBUTE^K) 

Using the relational model developed in Deliverable II, create a database and the appropriate tables. Use these notes as a guide.  Be sure to create tables representing both the entities and the composite entities.  Using the reports, displays, and figures in the problem description, enter some sample data for each table. Make certain that you enter data into the composite tables that provides the linkage between the two tables being bridged.  The steps which will be followed are listed below:

• Using your relational model, create each table.  
• I recommend that if you have numeric keys that you use the AutoNumber data type.
• Specify table linkages using the diagram wizard.
• Using the problem description, enter some sample data into each table.  If you have to, make some reasonable data up.  Enter at least five tuples into each table, and be sure that you provide data that allows the tables to be linked.  See note above.
• Print each table and include it in your deliverable.  To display the table data simply right click on the table name, click Open table, and then click Return all rows.  You may need a program like Snag It to capture a copy of each table.
• Print the table structure.  You can display the table structure simply by clicking on the table name.  Again, use SnagIt to capture the table structure.
• Print the table relationships. To display the table relationships that you set up earlier double click Diagrams in the left panel, and then in the right panel click the diagram that you set up.  SnagIt will come in handy again.
• Submit the database location, in addition to the items listed above.

Deliverable IV – Query design  

Using the tables developed earlier, develop a set of queries as specified in the query list.  Any queries that make use of a Join operation should use the WHERE clause rather than the INNER JOIN.  Points will be deducted for queries that use the INNER JOIN.  Include data in your tables to test each query.  Be sure to save the queries and submit the database.   Provide printouts of both the SQL view and the query result.  Be sure that there is data in your tables so that I can duplicate your results!

• Submit the queries on disk.
• For each query, provide a printout of the SQL view as well as the query result.
  • To display the SQL view: from the query menu select View, and then select SQL View from the drop-down menu.
  • To display the query result: from the query menu select Query, and then select Run from the drop-down menu.

Query List

Deliverable V – Data Entry Forms design

Using tables developed in Deliverable III, design and create the data entry forms specified below.  The forms will be graded on appearance and content.  Use VB or ASP to create the forms.  The steps which will be followed are listed below:

• Using your tables, design and create the data entry forms specified below.  The forms should be usable for data entry.  Test the forms to be sure data is entered properly.  Save the forms to disk.  
  • Customer entry 
  • Mechanic entry 
    
• Note that mechanic data is stored in both the Employee table as well as the Mechanic table. 
• When designing your forms, think about which data items can have their values supplied through a selection in a list box.  Some may have a logical default.
• Submit the forms on disk, with the names clearly indicated.
• Provide a printout of the forms, using Snag It or the Print command.

Sample forms:

Customer Entry Form

Mechanic Entry Form

example

image

Deliverable VI – Stored Procedures

sProc #1:

The first stored procedure was done as an exercise in class.  Refer to this link.

sProc #2:

The second stored procedure requires that you modify the Product_Category table to include a new Boolean (Yes/No) attribute called ReorderNecessary that will be set to true when an item needs to be reordered. The attribute should default to false.

Since the default value may not be valid for all the data in your table, write a stored procedure to set the value of the new ReorderNecessary attribute to its correct value. If the ModelInventoryCount is less than or equal to the ModelReorderQuantity, then set the ReorderNecessary value to true. Otherwise set the ReorderNecessary value to false. Execute the procedure to reset the value.

sProc #3:

Next create a new stored procedure to validate a credit card by copying and pasting the following:

CREATE PROCEDURE [dbo].[prValidateCreditCard] (@creditCardNum char(16)) AS

     BEGIN
     DECLARE @counter INT, @sum INT, @number INT, @tmp INT, @result BIT
     SET @result=0
          IF @creditCardNum IS NULL
               GOTO lblFail
          IF len(@creditCardNum)=0
               GOTO lblFail
          SET @counter=1
          WHILE @counter<=len(@creditCardNum)
               BEGIN
                    IF ISNUMERIC(substring(@creditCardNum,@counter,1))=0
                         GOTO lblFail
                    SET @counter=@counter+1
               END
          SET @sum=0
          SET @number=0
          SET @counter=len(@creditCardNum)
          WHILE @counter>0
               BEGIN
                    IF @counter>1
                         BEGIN
                              SET @tmp=(ASCII(substring(@creditCardNum,@counter-1,1))-48)*2
                              IF @tmp>9
                                   SET @sum=@sum+@tmp-9
                              ELSE
                                   SET @sum=@sum+@tmp
                              END
                    SET @number=@number+(ASCII(substring(@creditCardNum,@counter,1))-48)
                    SET @counter=@counter-2
               END
          SET @sum=(@sum+@number) % 10
          IF @sum=0
               BEGIN
                    print 'good'
                    SET @result=1
               END
     lblFail:
          RETURN (@result)
     END
     GO
 

(Yes, it's that easy.  Stop being so suspicious!)

Deliverable VII -- Triggers

Trigger #1:

The first trigger represents a typical inventory management task. Whenever an item is sold the associated inventory count is decremented. The system should automatically check to see if the inventory count has fallen below the reorder quantity. Write a trigger to compare the Product_Category attribute ModelInventoryCount to the ModelReorderQuantity. If the value is less than or equal to the reorder quantity then the ReorderNecessary attribute should be set to true. The trigger should be invoked whenever the ModelInventoryCount or ModelReorderQuantity attributes are updated.

Trigger #2:

The next trigger calls a stored procedure. When an item is purchased by credit card, the card number must be validated. Write a trigger to execute the stored procedure that you developed to validate the card number when the value of the PurchaseAgreementCardNumberUsed field is inserted or updated.

To execute a stored procedure with parameters you use the format
EXEC @returnValue = prStoredProcName  @parameterName=@localVariable