A database is an organized collection of data

Document Sample
A database is an organized collection of data Powered By Docstoc
					A General Introduction
A database is an organized collection of data.
One possible definition is that a database is a collection of records stored in a computer in a
systematic way, such that a computer program can consult it to answer questions. For better
retrieval and sorting, each record is usually organized as a set of data elements (fields).
The computer program used to manage and query a database is known as a database
management system (DBMS).
There are a number of different ways of organizing and modeling the database structure: these
are known as database models (or data models). The model in most common use today is the
relational model, which in layman's terms represents all information in the form of multiple
related tables each consisting of rows and columns (the true definition uses mathematical
terminology). This model represents relationships by the use of values common to more than
one table. Other models such as the hierarchical model and the network model use a more
explicit representation of relationships.

Strictly speaking, the term database refers to the collection of related records, and the
software should be referred to as the database management system or DBMS. When the
context is unambiguous, however, many database administrators and programmers use the
term database to cover both meanings.

A great deal of the internal engineering of a DBMS is independent of the data model, and is
concerned with managing factors such as performance, concurrency, integrity, and recovery
from hardware failures. In these areas there are large differences between products.


A key that can be used to uniquely identify a row in a table is called a unique key. Typically
one of the unique keys is the preferred way to refer to row; this is defined as the table's
primary key.

A key that has an external, real-world meaning (such as a person's name, a book's ISBN, or a
car's serial number), is sometimes called a "natural" key. If no natural key is suitable (think of
the many people named Brown), an arbitrary key can be assigned (such as by giving
employees ID numbers). In practice, most databases have both generated and natural keys,
because generated keys can be used internally to create links between rows that cannot break,
while natural keys can be used, less reliably, for searches and for integration with other
databases.

Ms Access
Microsoft Access is a relational database management system from Microsoft, packaged with
Microsoft Office Professional which combines the relational Microsoft Jet Database Engine
with a graphical user interface. It can use data stored in Access/Jet, SQL Server, Oracle, or
any ODBC-compliant data container. Skilled software developers and data architects use it to
develop powerful, complex application software. Relatively unskilled programmers and non-
programmer "power users" can use it to build simple applications without having to deal with
features they don't understand. It supports substantial object-oriented (OO) techniques but
falls short of being a fully OO development tool.
Uses

A computer database is a structured collection of data that is stored in a computer
system. It enables to organize the storage of the data and to extract desired
information. A basic unit of storage is usually called a record which is divided into
the fields. It should be stressed that the term "database" refers to the collection of
related records, and the software for managing it should be referred to as the
database management system (DBMS); this is sometimes shortened to database
system.

Data structures (fields, records, files and objects) are optimized to deal with very
large amounts of data stored on a permanent data storage device.

Access is widely used by small businesses and hobby programmers to create ad hoc
customized systems for handling small tasks. Its ease of use and powerful design tools give
the non-professional programmer a lot of power for little effort. However, this ease of use can
be misleading. This sort of developer is often an office worker with little or no training in
application or data design. Because Access makes it possible even for such developers to
create usable systems, many are misled into thinking that the tool itself is limited to such
applications.



The Design Process

   1.   Identify the purpose of the database.
   2.   Review existing database.
   3.   Make a preliminary list of fields.
   4.   Make a preliminary list of tables and enter the fields.
   5.   Identify the key fields.
   6.   Draft the table relationships.
   7.   Enter sample data and normalize the data.
   8.   Review and finalize the design.

Expansion and explanations

   1. Identify the purpose of the database.

        You will rarely be handed a detailed specification for the database. The desire for
        a database is usually initially expressed as things the client wants it to do. Things
        like:

              We need to keep track of our inventory.
              We need an order entry system.
              I need monthly reports on sales.
              We need to provide our product catalog on the Web.

        It will usually be up to you to clarify the scope of the intended database.
        Remember that a database holds related information. If the client wants the
        product catalog on the Web and sales figures and information on employees and
        data on competitors, maybe you're talking about more than one database.
        Everyone involved needs to have the same understanding of the scope of the
   project and the expected outcomes (preferably in order of importance). It can be
   helpful to write a statement of purpose for the database that concerned parties
   can sign off on. Something like: "The Orders database will hold information on
   customers, orders, and order details. It will be used for order entry and monthly
   reports on sales." A statement like this can help define the boundaries of the
   information the database will hold.

   The early stages of database design are a people-intensive phase, and clear and
   explicit communication is essential. The process is not isolated steps but is, to a
   point, iterative. That is, you'll have to keep going back to people for clarification
   and additional information as you get further along in the process. As your design
   progresses, you'll also need to get confirmation that you're on the right track and
   that all needed data is accounted for.

   If you don't have it at the beginning of the design process, along the way you'll
   also need to develop an understanding of the way the business operates and of
   the data itself. You need to care about business operations because they involve
   business rules. These business rules result in constraints that you, as the
   database designer, need to place on the data. Examples include what the
   allowable range of values is for a field, whether a certain field of data is required,
   whether values in a field will be numbers or characters, and will numbers ever
   have leading zeros. Business rules can also determine the structure of and
   relationship between tables. Also, it will be difficult for you to determine what
   values are unique and how the data in different tables relates if you don't
   understand the meaning of the data. The reasons will be clearer when you
   actually get to those points in the process.

2. Review existing data.

   You can take a huge step in defining the body of information for the database by
   looking at existing data repositories. Is there an existing database (often called a
   legacy database) even if it isn't fitting the bill anymore? Is someone currently
   tracking some of the information in spreadsheets? Are there data collection forms
   in use? Or are there paper files?

   Another good way to help define the data is to sketch out the desired outcome.
   For example, if the clients say they need a monthly report of sales, have them
   draft what they have in mind. Do they want it grouped by product line? By region?
   By salesperson? You can't provide groupings if you haven't got a field containing
   data you can group on. Do they want calculations done? You can't perform
   calculations if you haven't stored the component values.

   Your goal is to collect as much information as you can about the desired products
   of the database and to reverse engineer that information into tables and fields.

3. Make a preliminary list of fields.

   Take all you have learned about the needs so far and make a preliminary list of
   the fields of data to be included in the database. Make sure that you have fields to
   support the needs. For example, to report on monthly sales, there's going to have
   to be a date associated with each sale. To group sales by product line, you'll need
   a product line identifier. Keep in mind that the clients for a database have
   expressed their need for information; it's your job to think about what data is
   needed to deliver that information.
      Each field should be atomic; this means each should hold the smallest meaningful
      value and, therefore, should not contain multiple values. The most common
      disregard of this rule is to store a person's first name and last name in the same
      field.

      Do not include fields to hold data that can be calculated from other fields. For
      example, if you had fields holding an employee's hourly pay rate and weekly
      hours, you would not include a gross pay field.




The Designer's Dilemma

As you think about the design of a database and work through the process, keep point
#4 in mind. If five different database designers were given the same information, they
would most likely come up with five different database designs. But that is all the more
reason for following a process and complying with the design principles. The resultant
design will be a valid design.