Page 72 - DCAP508_DATABASE_ADMINISTRATION

Database Administration

Notes For any database, you can create as many database diagrams as you like; each database table can
appear on any number of diagrams. Thus, you can create different diagrams to visualize different
portions of the database, or to accentuate different aspects of the design. For example, you can
create a large diagram showing all tables and columns, and you can create a smaller diagram
showing all tables without showing the columns.
Each database diagram you create is stored in the associated database.

5.7.1 Creating Database Diagrams

You can use Object Explorer to create new database diagrams. Database diagrams graphically
show the structure of the database. Using database diagrams you can create and modify tables,
columns, relationships, and keys. Additionally, you can modify indexes and constraints.
To create a new database diagram
1. In Object Explorer, right-click the Database Diagrams folder or any diagram in that folder.
2. Choose New Database Diagram on the shortcut menu.

The Add Table dialog box appears.
3. Select the required tables in the Tables list and click Add.
The tables are displayed graphically in the new database diagram.
You can continue to add or delete tables, modify the existing tables, and alter table relationships
until the new database diagram is complete.

5.8 Views

A view is a virtual table whose contents are defined by a query. Like a real table, a view consists
of a set of named columns and rows of data. However, a view does not exist as a stored set of data
values in a database. The rows and columns of data come from tables referenced in the query
defining the view and are produced dynamically when the view is referenced.
A view acts as a filter on the underlying tables referenced in the view. The query that defines the
view can be from one or more tables or from other views in the current or other databases.
Distributed queries can also be used to define views that use data from multiple heterogeneous
sources. This is useful, for example, if you want to combine similarly structured data from
different servers each of which stores data for a different region of your organization.

Notes There are no restrictions on querying through views and few restrictions on
modifying data through them.
This illustration shows a view based on two tables on next page.
Before you create a view, consider these guidelines:

You can create views only in the current database. However, the tables and views referenced
by the new view can exist in other databases or even other servers if the view is defined
using distributed queries.
View names must follow the rules for identifiers and must be unique for each user.
Additionally, the name must not be the same as any tables owned by that user.
You can build views on other views and on procedures that reference views.
Microsoft® SQL Server™ 2000 allows views to be nested up to 32 levels.

66 LOVELY PROFESSIONAL UNIVERSITY