Creating databases in SQL Server 2000
April 22, 2005
Creating databases in
SQL Server 2000 can be an easy process if you understand the following terms:
Database design key terms
Prior to creating a database, you should be familiar with the following terms:
- Data represents the values stored in a database.
- Tables are database objects that contain all the data in a
database. Within a table, data is organized into rows and columns.
- Null is defined as a missing or unknown value.
- Indexes are objects that help speed up data retrieval. There
are two types of indexes: clustered and nonclustered.
- Constraints are conditions placed on data that help maintain data
integrity. An example of a constraint would be the primary key. Adding a
constraint helps keep all values in the primary key column unique.
- Views look like database tables, but they are just
virtual tables defined by a SELECT statement.
- A primary key is a field that uniquely identifies a record within a
- A foreign key is a field that establishes a relationship between two
- A one-to-one relationship is when a single record in one
table is related to a single record in a second table.
- A one-to-many relationship is when a single record in one
table is related to one or more records in a second table.
- A many-to-many relationship is when a single record in one
table is related to one or more records in a second table and vice versa.
Asking the right
Before you create a database, you must first ask yourself the following
- Why am I creating a database?
- Who are my Subject Matter Experts (SMEs)?
- Who will access this database?
- How will people access this database?
Once these questions are answered, you need to:
- Define your objectives for creating this database. Your objectives should reflect
the purpose of the database. It is a good idea to get your objectives on
paper so you can refer to them if you ever lose focus on why you are
creating this database.
- Analyze your proposed database. This step might consist of
talking to people to understand their needs and business requirements.
- Create your tables and relationships. Use a modeling program to
begin creating your tables, fields, primary, and foreign keys. Then, you
can begin to create relationships between your tables and link them together.
- Define your business rules. This process might consist of
conducting more interviews to establish constraints on your database. The
constraints will be directly based on the needs of the company.
- Review your database. Review your database design, make sure it is correct,
and go over the previous areas to make sure you've covered all the needs
of the company.
Now that you have a basic knowledge of database design, let's look at some
different methods for creating your database in SQL Server 2000.