Mount St. Mary's University

Professor 

Bruce E Yelovich

 

Database Analysis and Design

MS Access

 


MSM Workshop(s) Introduction

May 21, 2007 -
 

Workshop Home
 

 Professor Yelovich Home

 

Email Link

Campus Phone: ext. 4242

Home Phone: 717-642-6395 (local call)

 

  • MS Access is a powerful local database management system (DBMS) with a sophisticated user interface, an advanced structured query language (SQL) editor (query by example or QBE) and a powerful report writer. These features make MS Access the DBMS of choice for local clients. Use of a back-end, server-side database (DB), however, requires a more robust DBMS such as Oracle or SQL Server. Whereas these latter DBMSs permit multiple, simultaneous users to read and write to the back-end DB, MS Access is generally used either as a stand-alone, one-user client DB or as a front-end interface for a more powerful DBMS by means of an open database connection (ODBC) driver. This is the technology used by the Phillips Library, with its robust Oracle 9i DB (with over 10,000 tables) connected to a client-side MS Access DB for local generation of queries and reports.
     
  • Both client-and server-side databases require planning. A careful and methodical analysis of user needs should always precede the design of a database before the database administrator attempts to create tables, to link them, to populate them and to query them. Although the temptation will be to jump right in to create tables and enter data, a bit of time spent at the outset in careful analysis of user needs and DB design based on the that analysis will be time well spent.
    • The analysis consists of the following steps:
      • Identification of user needs
      • Specification of data elements that make up those needs
      •  Information output (organized or manipulated data) required by the user
    • The design based on the analysis consists of the following steps:
      • Creation of an conceptual schema in the form of an entity relationship diagram (ERD)
        • Specification of entities (resources, events and agents (REA) about which data will be collected, stored and manipulated)
        • Identification of attributes in their atomic values
        • Identification of relationships between those entities by means of key attributes
          • Calculation of cardinalities
            • Minimum and maximum
            • 1:1; 1:M; M:N relationships
      • Creation of a logical schema
        • Mapping of entities and their attributes by means of primary and foreign key joins
           
  • On the basis of the analysis and design, the database administrator will create the database according to the following basic rules:
    • One table will be created for each entity.
    • One table will be created for each M:N relationship.
    • Atomic attributes will be mapped to fields (columns) within tables.
    • Data types (domains) will be set.
    • Primary keys will be designated.
    • Primary keys will be joined to corresponding foreign keys.
    • Data fields will be populated.
    • Queries will created to extract data according to user specified needs.
    • Reports will be created to display query results in a manner that meets user specified needs for information output.