1 / 94

IT 390 Business Database Administration

IT 390 Business Database Administration. Unit 4 : Using MS SQL Server 2000 Key Features. Objectives. Review installation options Review how to create a database Create and Manage logins with Transact SQL and Enterprise Manager. Explain how to create and manage logins.

harmon
Télécharger la présentation

IT 390 Business Database Administration

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. IT 390 Business Database Administration Unit 4 : Using MS SQL Server 2000 Key Features

  2. Objectives • Review installation options • Review how to create a database • Create and Manage logins with Transact SQL and Enterprise Manager. • Explain how to create and manage logins. • Apply Key commands and features of Microsoft SQL Database Server and Transact SQL • Create and manage tables, views, indexes, stored procedures and triggers. • Describe basic concurrency control features and techniques.

  3. A Quick Review of a couple of Key Concepts!! • SQL Server 2000 can be installed on: • Windows 2000 and Windows XP workstations • Windows 2000 Server and Windows Server 2003 • There are two ways to create database, to insert data, and to modify its structure: • Use the GUI SQL Server Enterprise Manager • Write SQL statements and submit them to SQL Server via the SQL Query Analyzer utility • Many SQL Server professionals choose to create structures via SQL then modify them with the graphical tools

  4. View Ridge Gallery • View Ridge Gallery is a small art gallery that has been in business for 30 years • It sells contemporary European and North American fine art • View Ridge has one owner, three salespeople, and two workers • View Ridge owns all of the art that it sells; it holds no items on a consignment basis

  5. Application Requirements • View Ridge application requirements: • Track customers and their artist interests • Record gallery's purchases • Record customers' art purchases • List the artists and works that have appeared in the gallery • Report how fast an artist's works have sold and at what margin • Show current inventory in a Web page

  6. View Ridge Gallery Database Design

  7. The IDENTITY Keyword • SQL Server supports the SQL-92 standard • The IDENTITY keyword implements a new constraint for surrogate keys: • IDENTITY (m, n) creates a surrogate key with an Identity Seed of m and an Identity Increment of n: CREATE TABLE CUSTOMER( CustomerID int NOT NULL IDENTITY (1000,1), Name char(25)NOT NULL, CONSTRAINT CustomerPK RIMARY KEY (CustomerID), CONSTRAINT CustomerAK1 UNIQUE (Name) );

  8. SQL Server Enterprise Manager Right-Click Databases, then use New Database… to create a new database

  9. Creating a Database Type in the new database name, and then click the OK button.

  10. SQL Query Analyzer:Starting SQL Query Analyzer from Enterprise Manager Use the menu command Tools | SQL Query Analyzer.

  11. DDL: Using the CREATE TABLE Command • You can use the CREATE TABLE command to do the following: • Create a new table. • Create a new table with columns. • You use the CREATE TABLE command to create a new table. The syntax for creating a new table is: CREATE TABLE <table name>

  12. Creating a Table Clearly • The clear syntax to create a new table is: • CREATE TABLE <table name> • ( <column name1> <data type>, • <column name2> <data type>)

  13. Activity • Write the SQL statement to create a table named BookIssue. The column descriptions of the table are:

  14. Solution CREATE TABLE BookIssue (iIssueID int PRIMARY KEY, iBookID int FOREIGN KEY REFERENCES Books(iBookid), iMemberID int FOREIGN KEY REFERENCES Members(iMemberID), dIssueDate DateTime NOT NULL, dReturnDate DateTime NULL, mFine int NULL)

  15. SQL Query Analyzer:Creating the TRANS table in SQL Query Analyzer Use the Execute Query button after the SQL command is entered. Enter the SQL command in this window. Results appear in this window.

  16. SQL Server CREATE TABLE Statementsfor the View Ridge Schema

  17. SQL Server CREATE TABLE Statementsfor the View Ridge Schema

  18. SQL Server CREATE TABLE Statementsfor the View Ridge Schema

  19. SQL Server CREATE TABLE Statementsfor the View Ridge Schema

  20. Class Activity PQR is a software consultancy group involved in deploying projects that require back-end storage in a DBMS. The organization has recently bid on a project involving a large number of tables and records that requires multiple user access. Pedro is responsible for choosing a database to deploy the project. Which back-end application can he use in this situation?

  21. Answer: Logins • A login in SQL Server 2000 is a user account with a predefined set of permissions and privileges on databases and database objects. • For basic logins in SQL Server 2000 you can: • Add a login • Delete a login

  22. Logins (cont.) • The syntax for adding a login is: • sp_addlogin [ @login_name = ] 'login_name'     [ , [ @pass = ] 'pass' ]     [ , [ @default_db = ] 'db_name' ]     [ , [ @default_language = ] 'default_language' ]     [ , [ @sid = ] sid ]     [ , [ @encryptoption = ] 'encrypt_option' ] -- Remember the ‘@’ symbol is just a placeholder.

  23. sp_addlogin example for Microsoft T-SQL EXEC sp_addlogin ‘jonesa’, ‘ilikecocoa’, ‘northwind’, ‘us_english’

  24. Logins with SQL Server Enterprise Manager

  25. Logins with SQL Server Enterprise Manager

  26. Activity • What is the basic syntax (keywords) for dropping and granting logins using T-SQL?

  27. Answer • What is the basic syntax (keywords) for dropping and granting logins using T-SQL? sp_droplogin sp_grantdbaccess

  28. Class Activity XYZ Corp. needs to deploy a large project with a Visual Basic front end and SQL Server 2000 back end. Polly, the DBA, wants to grant different types of permissions to different sets of users. For example, for the group that needs student details, she wants to give access only to the student database and not any other database. What should Polly do in this situation?

  29. Roles (Polly should use a “role”) • Roles allow you to collect several users into a single unit for which you can apply similar permissions. • You can assign a role to a login in a database using the following syntax: sp_addrole [ @rolename = ] 'rolename' [ , [ @ownername = ] 'ownername' ]

  30. Databases • A database is a location in the memory where you can store your data. • Objects of a database are: • Tables • Views • Stored Procedures • Triggers

  31. Views • Views are abstraction of a table, similar to a general view of an object. In a view only select components need to be retrieved.

  32. Facts about Views • Views are nothing but a query block. • Views get the data from underlying base tables.

  33. Advantages of Views • Views helps in applying security policy. • Views provide data independence to users.

  34. More about Views • Views are used to implement column and row level security. • Views can be encrypted to enable enhanced level security to tables.

  35. Activity • The HR manager asks the DBA to alter the structure of the aggregates view to add department name. Write a query to alter the view.

  36. Solution ALTER VIEW aggregates (deptno, dep_name, average, maximum, minimum, sum, no_sals) AS select dep_name, dept_no, avg(sal), Max(sal), Min(sal), count(sal) FROM EMPLOYEE group by dept_no;

  37. Viewing Tablesin Enterprise Manager Right-Click the table name, then click Design Table to view table columns and properties.

  38. Viewing Table Columns and Properties in Enterprise Manager Right-click the white space and then click Properties to see table constraints.

  39. Viewing Table Constraints in Enterprise Manager

  40. Creating a Relationship Diagram in Enterprise Manager Right-click Diagrams, then click New Database Diagram to create a relationshipsdiagram.

  41. Table Relationship Diagram Right-click a relationship line, then click Properties to see the properties.

  42. WORK – TRANS Relationship Properties

  43. Creating Views:By SQL in SQL Query Analyzer Do NOT put a semi-colon (;) at the end of a CREATE VIEW statement in SQL Query Analyzer!

  44. Creating Views:By GUI in Enterprise Manager

  45. Indexes • Database Indexes are conceptually similar to indexes found in books.

  46. More about Indexes (Why do we need them?) • The need for indexes is: • To enforce integrity constraints. • For faster retrieval of data from tables.

  47. Advantages of Indexes • Index improves the performance of join queries. • Index increases the performance of query retrieval.

  48. More about Indexes • For optimizing a database, every table should have a clustered index. • Only one clustered index can be created per table.

  49. Think about it… • Is the usage of Indexes appropriate keeping in mind the database optimization factor?

  50. Clustered Index

More Related