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

19
Enterprise manager Using the Enterprise manager

Upload: silas-logan

Post on 19-Jan-2016

220 views

Category:

Documents


0 download

TRANSCRIPT

Page 1: Enterprise manager Using the Enterprise manager. Purpose of the Enterprise Manager To design tables To populate / update tables To draw diagrams of tables

Enterprise manager

Using the Enterprise manager

Page 2: Enterprise manager Using the Enterprise manager. Purpose of the Enterprise Manager To design tables To populate / update tables To draw diagrams of tables

Purpose of the Enterprise Manager

• To design tables

• To populate / update tables

• To draw diagrams of tables

• To create– Stored Procedures– Views– User privileges

Page 3: Enterprise manager Using the Enterprise manager. Purpose of the Enterprise Manager To design tables To populate / update tables To draw diagrams of tables

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.

Page 4: Enterprise manager Using the Enterprise manager. Purpose of the Enterprise Manager To design tables To populate / update tables To draw diagrams of tables

Enterprise manager screen

Page 5: Enterprise manager Using the Enterprise manager. Purpose of the Enterprise Manager To design tables To populate / update tables To draw diagrams of tables

Expand your database…

Page 6: Enterprise manager Using the Enterprise manager. Purpose of the Enterprise Manager To design tables To populate / update tables To draw diagrams of tables

Before you set up tables…

Page 7: Enterprise manager Using the Enterprise manager. Purpose of the Enterprise Manager To design tables To populate / update tables To draw diagrams of tables

After you set up tables…

Page 8: Enterprise manager Using the Enterprise manager. Purpose of the Enterprise Manager To design tables To populate / update tables To draw diagrams of 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’.

Page 9: Enterprise manager Using the Enterprise manager. Purpose of the Enterprise Manager To design tables To populate / update tables To draw diagrams of tables

To draw a diagram…

Page 10: Enterprise manager Using the Enterprise manager. Purpose of the Enterprise Manager To design tables To populate / update tables To draw diagrams of tables

Diagrams folder

Page 11: Enterprise manager Using the Enterprise manager. Purpose of the Enterprise Manager To design tables To populate / update tables To draw diagrams of tables

To add a new one

• Right click in the diagram window!

Page 12: Enterprise manager Using the Enterprise manager. Purpose of the Enterprise Manager To design tables To populate / update tables To draw diagrams of tables

• 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.

Page 13: Enterprise manager Using the Enterprise manager. Purpose of the Enterprise Manager To design tables To populate / update tables To draw diagrams of tables

Stored Procedures

Make an SQL statement into a stored Procedure

Page 14: Enterprise manager Using the Enterprise manager. Purpose of the Enterprise Manager To design tables To populate / update tables To draw diagrams of tables

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

Page 15: Enterprise manager Using the Enterprise manager. Purpose of the Enterprise Manager To design tables To populate / update tables To draw diagrams of tables

• Right click Stored Procedure

• Choose Add New Procedure

Page 16: Enterprise manager Using the Enterprise manager. Purpose of the Enterprise Manager To design tables To populate / update tables To draw diagrams of tables

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

• Paste or type in the SQL from the query.

• Click Check Syntax

Page 17: Enterprise manager Using the Enterprise manager. Purpose of the Enterprise Manager To design tables To populate / update tables To draw diagrams of tables

Stored Procedure Window

This one is called ProductSalesByCategory

Page 18: Enterprise manager Using the Enterprise manager. Purpose of the Enterprise Manager To design tables To populate / update tables To draw diagrams of tables

Stored

• Click OK. The procedure is now stored.

• This is what the browser will now show.

Page 19: Enterprise manager Using the Enterprise manager. Purpose of the Enterprise Manager To design tables To populate / update tables To draw diagrams of tables

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.