Page 64 - DCAP508_DATABASE_ADMINISTRATION

Database Administration

Notes represent those business functions. Make sure that you accurately design the database to model
the business, because it can be time-consuming to significantly change the design of a database
after you implement it. A well-designed database also performs better.
The first step in creating a database is creating a plan that serves both as a guide to be used when
implementing the database and as a functional specification for the database after it has been
implemented. The complexity and detail of a database design is dictated by the complexity and
size of the database application and also the user population.

The nature and complexity of a database application, and also the process of planning it, can
vary significantly. A database can be relatively simple and designed for use by a single person,
or it can be large and complex and designed, for example, to handle all the banking transactions
for thousands of clients. In the first case, the database design may be slightly more than a few
notes on some scratch paper. In the latter case, the design may be a formal document hundreds
of pages long that contains every possible detail about the database.
In planning the database, regardless of its size and complexity, use the following basic steps:

Gather information.
Identify the objects.
Model the objects.
Identify the types of information for each object.
Identify the relationships between objects.
Step 1: Gathering Information
Before creating a database, you must have a good understanding of the job the database is
expected to perform. If the database is to replace a paper-based or manually performed
information system, the existing system will give you most of the information that you need.
You should interview everyone involved in the system to determine what they do and what
they need from the database. It is also important to identify what they want the new system to
do, and also to identify the problems, limitations, and bottlenecks of any existing system.
Collect copies of customer statements, inventory lists, management reports, and any other
documents that are part of the existing system, because these will be useful to you in designing
the database and the interfaces.

Step 2: Identifying the Objects
During the process of gathering information, you must identify the key objects or entities that
will be managed by the database. The object can be a tangible thing, such as a person or a
product, or it can be a more intangible item, such as a business transaction, a department in a
company, or a payroll period. There are generally a few primary objects, and after these are
identified, the related items become visible. Each distinct item in your database should have a
corresponding table.

The primary object in the AdventureWorks2008R2 sample database included with SQL Server is
a bicycle. The objects related to bicycle within this company’s business are the employees who
manufacture the bicycle, the vendors that sell components used to manufacture the bicycle, the
customers who buy them, and the sales transactions performed with the customers. Each of
these objects is a table in the database.
Step 3: Modeling the Objects
As the objects in the system are identified, you should record them in a way that represents the
system visually. You can use your database model as a reference during implementation of the
database.

58 LOVELY PROFESSIONAL UNIVERSITY