Practical Assignment: Database Design
In practical assignment three, you evaluated the analysis phase deliverables for an information system that would support a small package delivery company. Continuing with this scenario, below you will find the database design that was derived from the previous deliverables (from Practical Assignment Two).
Below, you will find the physical database design, with relationships. This may look slightly different than some of the syntax in our lecture/text, as this was generated by the database code (Data Definition Language "DDL" in the Structured Query Language "SQL" syntax) [also included] using one Microsoft's reverse engineering tools. This is very similar to what you might expect to see in the field when reviewing design documentation. However, you should be able to identify each table, the primary keys, foreign keys, relationships, constraints, and datatypes.
When evaluating the design, try to understand the database schema, and square this against the analysis deliverables you previously evaluated. For example, I'd suggest using these criteria:
A. Review the design and determine if it is adequately normalized to support the business rules.
a. Are there any enumerated fields (for example, how would multiple phone numbers for a single customer be handled? And what is the business rule?)
b. Other normalization issues to look for are missing tables that would associate entities, for example, can a customer ship multiple packages in one order?
c. Are there any redundancies that would affect either performance or data integrity?
B. Review the relationships for both semantic and technical accuracy.
a. Is the relationship aligned with the analysis E-R diagram?
b. Do the primary/foreign key relationships in the physical design reflect that relationship?
C. Look at the constraints and data types and determine if they support the business rules and data integrity of the system.
a. Do they ensure the integrity of data that is critical to the process/business rules?
b. Do datatypes accurately reflect how the data will be used, and any processing requirements, for example, dates, numbers, etc.?
c. Are there constraints where needed to enforce business rules?
D. How well do you think this design will support the business?
a. Will it perform adequately? (This usually requires adequate indexes on commonly used fields)
b. Are the fields designed in such a way to maximize efficient use of data storage? (Look at data types and lengths.)
E. Will this design support accountability requirements?
a. Your expertise as an IT auditor might lead you to recommendations of designs/tools that would help with accountability. For example,
i. How do we know when payments are received, processed, etc.?
ii. How do we know which employees handled which packages or company assets?
iii. Are there types of fraud that would be difficult to trap/investigate based on this design?
Using the deliverables below, you will provide two deliverables for this assignment:
A. Annotate on the diagrams (you can use a printout and scan physical copies for your assignment submission) and issues you identified, and proposed modifications.
B. Provide a report that explains proposed changes and any other details you believe would be relevant. (When you mark changes, you can include a reference, for example "A", and use that marking to refer to the error/change in your report.)