Page 65 - DCAP508_DATABASE_ADMINISTRATION

Unit 5: SQL Server Databases

For this purpose, database developers use tools that range in technical complexity from pencils Notes
and scratch paper to word processing and spreadsheet programs, and even to software programs
created specifically for the job of data modeling for database designs. Whatever tool you decide
to use, it is important that you keep it up to date.
Step 4: Identifying the Types of Information for Each Object

After the primary objects in the database have been identified as candidates for tables, the next
step is to identify the types of information that must be stored for each object. These are the
columns in the table of the object. The columns in a database table contain a few common types
of information:

Raw data columns: These columns store tangible pieces of information, such as names,
determined by a source external to the database.
Categorical columns: These columns classify or group the data and store a limited selection
of data such as true/false, married/single, and VP/Director/Group Manager.
Identifier columns: These columns provide a mechanism to identify each item stored in
the table. These columns frequently have an ID or number in their name, for example,
employee_id, invoice_number, and publisher_id. The identifier column is the primary
component used by both users and internal database processing for gaining access to a
row of data in the table. Sometimes the object has a tangible form of ID used in the table,
for example, a social security number, but in most situations you can define the table so
that a reliable, artificial ID can be created for the row.
Relational or referential columns: These columns establish a link between information in
one table and related information in another table. For example, a table that tracks sales
transactions will generally have a link to the customers table so that the complete customer
information can be associated with the sales transaction.
Step 5: Identifying the Relationship between Objects
One of the strengths of a relational database is the ability to relate or associate information
about various items in the database. Isolated types of information can be stored separately,
but the database engine can combine data when it is required. Identifying the relationship
between objects in the design process requires looking at the tables, determining how they
are logically related, and adding relational columns that establish a link from one table to
another.
For example, the designer of the AdventureWorks2008R2 database has created tables for products
and product models in the database. The Production.Product table contains information for each
product that includes an identifier column named ProductID; data columns for the product
name, the price of the product, and the product color, size, and weight. The table contains
categorical columns, such as Class, or Style, that lets the products be grouped by these types.
Each product also has a product model, but that information is stored in another table. Therefore,
the Production.Product table has a ProductModelID column to store just the ID of the product
model. When a row of data is added for a product, the value for ProductModelID must exist in
the Production.ProductModel table.

5.4 Creating Databases

To create a database determine the name of the database, its owner (the user who creates the
database), its size, and the files and file groups used to store it.

LOVELY PROFESSIONAL UNIVERSITY 59