1 / 102

Unit 3: Microsoft Transact SQL and the Query Analyzer

IT390 Business Database Administration. Unit 3: Microsoft Transact SQL and the Query Analyzer. Objectives. Briefly trace the history of SQL Apply Key commands and features of Microsoft SQL Database Server and Transact SQL.

nowles
Télécharger la présentation

Unit 3: Microsoft Transact SQL and the Query Analyzer

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. IT390 Business Database Administration Unit 3: Microsoft Transact SQL and the Query Analyzer

  2. Objectives • Briefly trace the history of SQL • Apply Key commands and features of Microsoft SQL Database Server and Transact SQL. • Explain other key commands associated with the Microsoft SQL Server and Transact SQL language. • Create and Manage databases with Transact SQL and Enterprise Manager. • Create a Database with Transact SQL and Enterprise Manager. • Explain how to create and manage Microsoft Databases through both the Query Analyzer and Enterprise Manager. • Explain Normalization with Data Models. • Describe the fundamentals of Normalization with the Relational Data Model and basic Relational Design. • Describe normalization anomalies and their results.

  3. Databases: An Overview History SQL came into existence when Dr. E. F. Codd, who worked for IBM, defined the relational model for databases. In 1986 and 1987, American National Standards Institute (ANSI) and International Standards Organization (ISO) formed SQL Standards committees. As a result, SQL became an open standard known as ANSI-SQL. To implement the designs in a relational database management system (RDBMS), you need a tool. Structured query language (SQL) is one such tool.

  4. Think about it… • What is SQL? • Why is SQL known as a non-procedural language? Is it just another language?

  5. SQL Products and Application Developers

  6. Processing of a SQL Command You can perform various database operations by using SQL commands. These operations include updating data in a database and retrieving information from a database. When you query a database using an SQL command, the SQL query is sent to an RDBMS. The SQL query process can be summarized as follows: • A user makes a request by using an SQL statement. • The RDBMS receives the request and forwards it to the database engine. • The database engine processes the request and sends the result to the RDBMS. • The RDBMS forwards the result to the user.

  7. Processing of a SQL Command • SQL provides a number of commands to perform various database-related tasks. You perform database-related tasks in SQL by using three different primary types of SQL (sub)languages: • Data Definition language (DDL) • Data Manipulation Language (DML) • Data Control Language (DCL) • For instance, a table that is created using a DDL command stores the : • Name of a table • Columns of a table • Constraints on the table

  8. Sub-Languages of SQL DDL DML TCL DCL Sub-Languages of SQL

  9. 1) Joe, the database administrator of Ethnic Blends Inc., wants to create a new table named Brands, which will be a part of the organization’s database. 3) Joe wants to undo a fake transaction that occurred in the database. 2) Joe wants to insert a fresh set of values in the Garments table 4) The developers of Ethnic Blends Inc. want to assign permissions to certain MIS level users. Activity • Identify the sub-languages of SQL.

  10. Solution • For case 1: DDL • For case 2: DML • For case 3: TCL • For case 4: DCL

  11. Data Definition Language

  12. Creating a Database (RAW SQL CODE!) • The syntax for creating a database in T-SQL is: CREATE DATABASE <db_name>[ON [PRIMARY]        [ <filename> [,...n] ]        [, <filegroupname> [,...n] ]][ LOG ON { <logfiles> [,...n]} ][ FOR LOAD | FOR ATTACH ] <filespec> ::=    ( [ NAME = file_name, ]  FILENAME = 'os_filename'  [, SIZE = filesize]  [, MAXSIZE = { maximum_size | UNLIMITED } ]  [, FILEGROWTH = file_growthincrement] ) [,...n] <filegroup> ::= FILEGROUP name_filegroup <filsespec> [,...n]

  13. Creating a Database (cont.) • You can use also use Enterprise Manager to create a database.

  14. System Databases (some Databases already exist!) • They are of the following types: • master • model • msdb • tempdb

  15. 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. • Create a new table with constraints defined on columns. • You use the CREATE TABLE command to create a new table. The syntax for creating a new table is: CREATE TABLE <table name>

  16. DDL: Using the CREATE TABLE Command • You can use the CREATE TABLE command to create a new table with columns: CREATE TABLE <table name> <column name> <data type> • You can use some optional constraints with the basic CREATE TABLE command. The syntax having the optional constraints is as follows: CREATE TABLE <table name> <column name> <data type>

  17. Using Other DDL Commands • DDL provides the following commands to modify and delete existingobjects in a database: • ALTER • DROP • TRUNCATE • COMMENT

  18. ALTER TABLE <table name> • ALTER TABLE <table name> • ALTER COLUMN <column name> Modifying Table and Column • The syntax to modify an existing table is: • The syntax to alter a column in a table is:

  19. Using Other DDL Commands • ALTER command: You use the ALTER command to add columns, add constraints, or modify columns and constraints that were already created using the CREATE command. • The syntax to use the ALTER TABLE command to add a column to a table is as follows: ALTER TABLE <table name> ADD <column name> <data type> <constraint>

  20. Using Other DDL Commands (cont.) Deleting Table and Column Objects • DROP Command: You use the DROP command to delete a table or columns that has constraints. • The syntax to use a DROP command to delete an object is as follows: DROP <OBJECT><object name>. Here, the <OBJECT> could be a TABLE, COLUMN, or a CONSTRAINT with its respective name. • DROP OBJECT <object name> • where, • object name can be the name of • a column or a constraint

  21. Using Other DDL Commands (cont.) • You use the TRUNCATE command to delete only specific rows in a table. • The syntax to use a TRUNCATE command to delete an object is as follows: TRUNCATE <table name>

  22. Using Other DDL Commands(cont.) • The COMMENT command enables you add a comment about a table or a column. COMMENT ON TABLE <table name> IS “………….” The COMMENT in T-SQL is “ -- “

  23. SQL Language • Designed to talk to relational databases • Perform various functions: DDL-Data Definition DML-Data Manipulation DCL-Data Control (Security) • We will talk about DML

  24. The Syntax of the DML “SELECT” statement

  25. select * from garment SELECT * FROM garment Class Activity 2 • Will the following SELECT statements run and what is the difference between them?

  26. Solution 2 • Both the statements will run as SQL is not normally case sensitive. BE CAREFUL though…it is good to be in the habit of consistent coding for READABILITY.

  27. Activity • In an Employee table, there are 3 columns named firstname, lastname, and middlename. Is there any error in the following query? SELECT firstname lastname middlename FROM Employee

  28. Solution • The column names should be separated by a comma (,). • The correct code is: SELECT firstname, lastname, middlename FROM employee

  29. select * from garment SELECT * FROM garment select * From garment Select * From garment Activity • Which of the following syntax will not give an error?

  30. Solution • All the statements will work.

  31. Normalization Premise • We have received one or more tables of existing data • The data is to be stored in a new database • QUESTION: Should the data be stored as received, or should it be transformed for storage?

  32. How Many Tables? Should we store these two tables as they are, or should we combine them into one table in our new database?

  33. But first - • We need to understand MUCH more about: • The relational model • Relational model terminology

  34. The Relational Model • Introduced in 1970 • Created by E.F. Codd • He was an IBM engineer • The model used mathematics known as “relational algebra” • Now the standard model for commercial DBMS products

  35. Important Relational Model Terms • Entity • Relation • Functional Dependency • Determinant • Candidate Key • Composite Key • Primary Key • Surrogate Key • Foreign Key • Referential integrity constraint • Normal Form • Multivalued Dependency

  36. Entity • An entity is some identifiable thing that users want to track: • Customers • Computers • Sales

  37. Relation • Relational DBMS products store data about entities in relations, which are a special type of table • A relation is a two-dimensional table that has the following characteristics: • Rows contain data about an entity • Columns contain data about attributes of the entity • All entries in a column are of the same kind • Each column has a unique name • Cells of the table hold a single value • The order of the columns is unimportant • The order of the rows is unimportant • No two rows may be identical

  38. A Relation

  39. A Relation with Values of Varying Length

  40. Tables That Are Not Relations:Multiple Entries per Cell

  41. Tables That Are Not Relations:Table with Required Row Order

  42. Alternative Terminology • Although not all tables are relations, the terms table and relation are normally used interchangeably • The following sets of terms are equivalent:

  43. Functional Dependency • A functional dependency occurs when the value of one (a set of) attribute(s) determines the value of a second (set of) attribute(s): StudentID  StudentName StudentID  (DormName, DormRoom, Fee) • The attribute on the left side of the functional dependency is called the determinant. • Functional dependencies may be based on equations: ExtendedPrice = Quantity X UnitPrice (Quantity, UnitPrice)  ExtendedPrice • Function dependencies are not equations!

  44. Functional Dependencies Are Not Equations ObjectColor  Weight ObjectColor  Shape ObjectColor (Weight, Shape)

  45. Composite Determinants • Composite determinant: A determinant of a functional dependency that consists of more than one attribute (StudentName, ClassName)  (Grade)

  46. Functional Dependency Rules • If A  (B, C), then A  B and A C • If (A,B)  C, then neither A nor B determines C by itself

  47. Functional Dependencies in the SKU_DATA Table

  48. Functional Dependencies in the SKU_DATA Table SKU  (SKU_Description, Department, Buyer) SKU_Description  (SKU, Department, Buyer) Buyer  Department

  49. Functional Dependencies in the ORDER_ITEM Table

  50. Functional Dependencies in the ORDER_ITEM Table (OrderNumber, SKU)  (Quantity, Price, ExtendedPrice) (Quantity, Price)  (ExtendedPrice)

More Related