enterprise manager using the enterprise manager. purpose of the enterprise manager to design tables...

Enterprise manager

Using the Enterprise manager

Purpose of the Enterprise Manager

• To design tables

• To populate / update tables

• To draw diagrams of tables

• To create– Stored Procedures– Views– User privileges

Opening the Enterprise Manager

• Choose the Enterprise Manager from the Start menu.

• Connect as for the Query Analyser

• Open the databases folder and find your database. Register

your server by clicking this icon.

Enterprise manager screen

Expand your database…

Before you set up tables…

After you set up tables…

Table view

• The tables that you set up are User tables.

• The tables that are there for system administration are System tables.

• Sometimes it is useful to be able to see what tables are there and what is in them.

• To see what is in the tables, right click on the table and click ‘open table’.

To draw a diagram…

Diagrams folder

To add a new one

• Right click in the diagram window!

• The tables in your database are listed on the left.

• Click the ones you want on the diagram and Add them.

• Click Next and Finish.• The diagram is

generated.

Stored Procedures

Make an SQL statement into a stored Procedure

Example Query

Select categoryName,

cast(ProductName as char(12))as Product,

cast(([order details].unitPrice*Quantity)-Discount as decimal(10,2)) as income

from

categories join products on categories.categoryid = products.categoryid

join [order details] on products.productid = [order details].productid

• Right click Stored Procedure

• Choose Add New Procedure

• Change the [Procedure Name] to one of your own choosing.

• Paste or type in the SQL from the query.

• Click Check Syntax

Stored Procedure Window

This one is called ProductSalesByCategory

Stored

• Click OK. The procedure is now stored.

• This is what the browser will now show.

To Run in the Query Analyser

•You do not have write privilege to Northwind.

•Try to set up a stored procedure in your own database.