Deliverables
Your deliverable is to be one Word file and one Visio file:
- A single Word file containing the content of your submission outlined in the File 1 section immediately below. See the details for Pages 1—4 below and pay attention to the page requirements and be sure that your source file has appropriate page breaks in it.
File 1: Index & SQL Information (Submitted as a Word Document)
Page Headings
Page 1 will have the following elements at the top:
- Your group name
- The group member names on one line separated by commas
- Your group scenario name from the Scenarios Page.
- The Page 1 title as described for subsequent pages below.
On subsequent pages list the title (following the “Page n—” portion) at the top of the page. Page 4’s content may span more than one page. You only need to put this section’s title on the top of the first page.
‘Page Numbers’: Page numbers below may be different from the physical page counts. What I am calling Page 3 and Page 4 in particular could have more than one physical page in each.
Page 1—Transactions and Reports:
- Frequent Transactions: List the ten most frequent business events (use cases) that you feel your organization will perform on a recurring basis ranked from most frequently to least frequently performed. Just state the event, such as “Make sales to customers,” “Record receipt of replenishment inventory,” “Record employee time card entries,” etc. Use a numbered list format with double spacing. Don’t agonize over the exact sequencing of event frequencies but there should definitely be a clear winner for first place. Don’t attempt to explain anything about the use case or its rank ordering in your list.
- Reports: On the same page as the transactions above list the most frequent report you think your organization will need to develop as well as the most important report from a managerial value perspective your organization will need to generate. Reports are any output from the system designed to be printed. A customer invoice or monthly statement is a report as are a wide range of managerial outputs such as, “Sales by SalesRep for Previous Month”. Be sure to label which report is the most frequent and which is the most important.
Page 2—Most Frequent Transaction Analysis:
- SQL: Restate the most frequent business event (use case) and write all of the SQL needed to accomplish this transaction. A single event such as making sales to customers may require several distinct SQL statements. (Do not write stored procedures or triggers–but if SQL logic would be embedded in a trigger do include that SQL.
Your SQL must be typed in Courier New 10 pt font. I will be incredibly upset and impose all of the penalties I can justify if you fail to use this font.
Be sure to indent parts of your query such as continuation lines for longer lines, parts of the FROM statement. See my examples throughout the modules for indenting examples.
Historically I have had groups fail to be complete in their analysis of the SQL needed to accomplish a particular use case. This analysis needs to think back to different techniques used in systems analysis where we timelined user, interface, and system actions. In particular, you need to remember choices presented to the user which are drawn from the system. This step is insidious in its work load in that you essentially need to think through the entire SAD process for this task.
NorthWind Example: Follow this link for a sample of this process using the NorthWind database. I developed this sample for an undergraduate management information systems database course where students not only did the design steps you are doing but coded a working program that manipulated their database. The example is for the use case “Sell products to customers” and includes the user interface which you will not do (but should think about). Notice that this single use case requires seven distinct SQL statements. Some of them only presented choices to users by loading lists in the interface while others were more complicated and updated data.
- Indices: With respect to this transaction only state which indices would improve the transaction’s performance. State each index as follows:
TABLENAME
(IndexColumnList) Order (ASC or DESC) Cluster Status (“Clustered” or “NonClustered”)e.g.
PRODUCTS
ProductID ASC Clustered
ProductName ASC NonClustered
ORDER DETAILS
OrderID, ProductID ASC Clustered
Use Word’s table capabilities to format the submission into columns. Be sure to leave a blank line between information for different tables.
Remember, you are not physically implementing your indices.
Page 3—Clustered Indices:
For each table in your DB, state the table name and whether you, a) Left the PK as a clustered index (“PK Clustered”), b) had no clustered index in the table (“No Clustered Index”), or c) Made another non-PK clustered index (“Other Clustered Index”). For each table, briefly justify your decision. These justifications need not be extensive. An example for a clustered PK might be, “Records are added in PK sequence and often retrieved with a sequential range of records retrieved together.” An example for a table with no clustered index might be, “Records are not added in PK order requiring extensive insertions in random orders. A clustered index on the PK would cause extensive table reshuffling and records are not often retrieved by the PK sequence.” (I will be looking at your Top Ten list of frequent transactions when reviewing this submission.)
Here are two examples from the Northwind database.
Products: Clustered. PK values are added sequentially so there is no harm in the clustered index. There is no particular retrieval advantage for this index being clustered.
Order Details: Clustered. PK values are added roughly sequentially according to OrderID and roughly at the end of the table minimizing insertion turbulence. Records are frequently retrieved by OrderID so having records physically together is advantageous. OrderID is also chronological and records are also frequently retrieved by chronological ranges for billing purposes so having records together will be an advantage in this circumstance.
Be sure that there is a blank line between each table’s entry and list tables in alphabetical order.
Page 4 (et seq.)—Other Indices:
List each table and each index in that table using the same format specified for the SQL indices above. Note that you will include the indices you already included for the SQL question in this list as well as many others. Do indicate whether the indices are clustered or not.
- Don’t allow the index lists for a single table to span multiple pages. Force a page break to keep all information for a single table on the same page.
- Place your tables in alphabetical order.
0 comments