Methodology 2: Specification

Four Stages of the Methodology

1. Analysis

Output is the Information Flow Diagram

2. Specification

A. First step is to create an EER Diagram representing the data, then
B. Outlining the Tasks (or applications) that need to run on the database represented by the EER Diagram.

3. Design

A. Translate the EER Diagram to a Relational Schema, then
B. Convert the tasks that represent the actions that need to happen in the EER Diagram and represented them with Abstract Code.

4. Implementation

A. Use something like MySQL to create the Relational Database.
B. PHP Code is used with embedded SQL to rep

2. Specification

  • Next, an Extended Entity Relationship (EER) diagram is designed from the input/output documents. This is a specification of the Database.
  • Then, we will examine the tasks of the IFD, and from each one of those, we will design what the tasks will do relative to each of the input/output documents and the EER diagram we have now designed.
  • After more work and specification, the output of the specification phase will be:
    • EER Diagram
    • Data Formats
    • Constraints
    • Task Decomposition

This phase involves:

  • Generating an EER Diagram,
    • Look at Data formats
    • Look at Constraints
  • Decompose tasks
    • Write abstract code for the tasks

Example Requirements

All Users are uniquely identified by his or her Email Address. Providing a valid Email Address and Password combination will log the user into the system

From this requirement, the following EER Diagram is generated.

Each of these attributes are included in the log-in and registration screens of the GTOnline system.

Note: Use the attribute names in the EER Diagram on the appropriate fields of the documents. This way, there is a tight coupling between the data input document and the EER Diagram, so the EER Diagram remains a good model of the real-world system as the actual system evolves during development.

A user must be either an administrator or a regular user, but never both.

Requirement set forth above gives rise to the disjointness constraint shown.

A list of schools, from which the user can select, is maintained in the system. Assume that all School Names will be unique.

Note: The phrase “School Names will be unique” indicates that the SchoolName is an identifying attribute for the Schools entity.

Each school must have a School Type. There are four possible types…

A particular user can have requested multiple other users to be friends. For each of these such requests, there is an instance in the requests for that user. Every time a user accepts a request, an instance is added to the accepts relationship.

Reading EER Diagrams Mechanically

Customers may not be able to understand the EER diagrams, but they will be able to understand an English description of the system. See example below.

GTONline stores Users. There are two kinds of Users. Every User must either be a RegularUser or an AdminUser. All Users have a unique Email, a Password, and a Name consisting of FirstName and LastName. An AdminUser also has a LastLogin. A RegularUser also has a Sex, a Birthdate, a CurrentCity, a HomeTown, and multiple Interests.

Data Formats – Beg, Steal, Borrow

  • Don’t reinvent the wheel. Leverage work and thought that other people have done.
  • Determining what the data formats need to be may require examining the input/output documents as well as interacting with the customer.


  • User:
    • Email: max 36 chars.
    • Password: max 20 chars
    • Name: Firstname: max 25 chars, Last: max 40 chars
    • Addresses are very difficult. There is a 206 page guideline available.
  • RegularUser:
    • Birthdate: Date: ‘YYYY-MM-DD’
    • Sex: {M, F}
    • CurrentCity, HomeTown: max 20 chars, each.
    • Interests: multi-value with 16 chars, each.



  • DateConnected is NULL until request is accepted.
  • Cannot be Friend with yourself.
  • Can only comment on Status of Friends.

The constraints that are mentioned above are constraints that are not:

  • Data formatting constraints.
  • Constraints that can be expressed in the EER Diagram.

Note: These constraints cannot be described in the data format, and cannot be expressed in the EER Diagram. The only means to enforce them is to program them into the application program.

Task Decomposition

Rules of Thumb, based on a deep understanding of the internal workings of a database management system.

Lookup vs Insertion, Deletion, Update?

These are very different from a database perspective because they require different database locks. If many different things take place, this is an indication that the task should be decomposed further.

How many schema constructs are involved?

This can involve many database locks, which can indicate that the task should again be further decomposed. The bigger the portion of the database is required, the harder it is to acquire all the locks that are needed to support concurrent execution of the tasks.

Are enabling conditions consistent across tasks?

Let run what can run, for improved scheduling. Smaller tasks can generally run more readily.

Are frequencies consistent across tasks?

High frequency tasks and low frequency tasks should be split apart because high frequency tasks may need to be indexed, whereas low frequency tasks do not need to be indexed.

Is consistency essential?

Is it crucial that all parts of a task be executed at one time? Or is it okay that the different parts be spaced out in time? An example of a task that should be executed in “one go” is a money transfer at a bank. (ACID transaction properties)

Is mother task control needed or not?

The money transfer mentioned in previous bullet would require a mother task.

Types of Applications

Web Apps

  • Traditionally, almost stateless.
  • Must have some state, ex: email of the session user.
  • May need some click stream history.
  • Things are changing. So-called Web 2.0 and AJAZ technologies provide more rich user interface in the web browser. These technologies make it easier to keep state info locally on the browser or behind the scenes on the server (but not in the DB).
  • In this sense, the web apps are beginning to act more like traditional apps.

Traditional Apps

  • In a traditional app, it is much easier to manage local state separately from the DB (ex: using smart widgets, etc.)
  • A whole slew of changes can be collected before submitting them all to the database.
  • Supports better control of ACID transactions execution.

Task Decomposition Examples

View Profile

  • Requires three lookups of Personal, Education, and Professional information for a RegularUser.
  • All three are read-only.
  • All three are enabled by a user’s login or a friend’s lookup.
  • All three have the same frequency.
  • Several different schema constructs are needed.
  • Consistency is not critical, even if the profile is being edited by the user while a friend is looking at it.
  • They can be done in any order.
  • All three tasks must be done, so a mother task is needed.
  • The foregoing indicate that the task should be decomposed into three sub-tasks.

Edit Profile

  • Lookups of Personal, Education, and Professional information of a RegularUser (use: View Profile)
  • Lookups of School and Employer lists
  • Edits of Personal, Education, and Professional information
  • Read, insert, delete, and update.
  • All three are enabled by a user’s login and separate edit request.
  • Different frequencies
  • Several different schema constructs are needed
  • Consistency is not critical, even if the profile is being looked at by a friend of the user
  • Lookup done first followed by any number of edits and lookups
  • Mother task is needed
  • The foregoing must be decomposed into sub-tasks.