Exercise 2

Database Design

Learning objectives:

  • To gain technology competency in managing science data.
  • To obtain hands-on experience in designing and planning data management.
How to proceed:

  • Take the information from Exercise 1 as an example for your database design practice.
  • You need to complete the following steps fro this exercise:
    • Analyze the information in both tables to decide what information categories (data attributes) may form an entity.
    • combine two tables from Exercise 1 and add a column to the right as shown in Table 2:
      Table 2. Grouping information categories.

      Information category Information identified Entity
      Maintenance authority USGS Repository
      Time coverage 1920-present Repository
    • Draw a separate table that contains all entities only in a matrix format and define the business rules between entities (which mainly fall into one of the three relationships: one-to-one, one-to-many, and many-to-many). The following table is an example for this step:
      Table 3. A matrix of entities.

      Organization Dataset Repository
      Organization 1:M (one organization has many datasets) 1:M (one organization has many repositories)
      Dataset M:1 (many datasets may belong to the same organization) M:1 (many datasets may belong to the same repository)
      Repository M:1 (many repositories may belong to the same organization) 1:M (one repository has many datasets)
    • Draw an entity-relationship diagram based on the analysis result from your matrix table to show 1) attributes in each entity, 2)relationships between the entities, and 3) data type for each attribute.
    • You may add annotations to the diagram to explain your rationale for choosing entities and attributes within the entities and relationships between entities.
    • You may choose to use MS Visio or other software to draw the diagram as long as the diagram can show the required information. Hand-drawn diagram is not acceptable.
Submission:

  1. Printout for the new table and diagram due in class as hard copy.
  2. A electronic copy of the diagram to WebCT ropbox. The deadline for submission is midnight of the due date.