Understanding Databases

Сентябрь 4, 2022

Главная
Информатика
Understanding Databases

Содержание

2. Objective Domain Matrix
3. Data Anomalies Insert Anomaly You cannot insert new data because of unrelated dependency Delete Anomaly Deleting
4. Data Normalization The process of data normalization ensures that a database design is free of any
5. First Normal Form (1NF) In order for a table to be in the first normal form
6. Second Normal Form (2NF) For a table to be in second normal form (2NF), it must
7. Third Normal Form (3NF) For a table to be in third normal form (3NF), it must
8. Structured Query Language (SQL) SQL is the language used by most database systems to manage the
9. SQL Queries SELECT, INSERT, UPDATE, and DELETE statements are the four main types of SQL statements
10. Running SQL Queries There are many way to communicate with SQL Server in order to run
11. Selecting Data The SELECT statement is used to retrieve data from one or more database tables.
12. SELECT Examples The following SELECT statement matches each order with corresponding customer: SELECT OrderID, Customers.CustomerId, ContactName
13. Updating Data The UPDATE statement is used to update information in database tables. The following statement
14. Inserting Data The INSERT statement is used to add one or more rows to a database
15. Deleting Data The DELETE statement is used to remove information from database tables. The following statement
16. Stored Procedures A stored procedure is a set of SQL statements that is stored in a
17. Creating Stored Procedures You use T-SQL’s CREATE PROCEDURE statement to create a stored procedure. The following
18. Parameterized Stored Procedures The following stored procedure, when executed, returns total sales for a given customer:
19. Working with Flat Files The data in a flat file can be plain text or binary.
20. Extensible Markup Language (XML) XML is a text-based format for representing structured data.
21. Working with XML The classes that work with XML data are organized in the System.Xml namespace:
22. Working with DataSet A DataSet is an in-memory representation of relational data. A DataSet can have
24. Скачать презентацию

Слайд 2

Objective Domain Matrix

Слайд 3

Data Anomalies
Insert Anomaly
You cannot insert new data because of unrelated dependency
Delete

Anomaly
Deleting one piece of data causes unintended loss of other data
Update Anomaly
Updating a single data value requires multiple rows to be updated

Слайд 4

Data Normalization
The process of data normalization ensures that a database design

is free of any problems that could lead to loss of data integrity.
This lesson discusses three normal forms
First Normal Form
Second Normal Form
Third Normal Form

Слайд 5

First Normal Form (1NF)
In order for a table to be in

the first normal form (1NF), none of the columns in the table should have multiple values in the same row of data.
The following table is not in 1NF because the PhoneNumber column is storing multiple values

Слайд 6

Second Normal Form (2NF)
For a table to be in second normal

form (2NF), it must first meet the requirements for 1NF.
2NF also requires that all non-key columns are functionally dependent on the entire primary key.
The following table violates the 2NF because the non-key columns are functionally dependent on only part of the primary key.

Слайд 7

Third Normal Form (3NF)
For a table to be in third normal

form (3NF), it must first meet the requirements for 2NF.
3NF also requires that that there is no functional dependency between non-key attributes.
The following table violates the 3NF because the non-key columns are functionally dependent on only part of the primary key.

Слайд 8

Structured Query Language (SQL)
SQL is the language used by most database

systems to manage the information in their databases.
SQL is declarative in nature -- you tell the database what needs to done, and it’s the database’s job to figure out how to do it.
There are two main ways to submit T-SQL to SQL Server:
Ad-hoc SQL statements
Stored procedures

Слайд 9

SQL Queries
SELECT, INSERT, UPDATE, and DELETE statements are the four main

types of SQL statements used to manipulate SQL Server data:
SELECT statement: retrieves data
INSERT statement: adds new data
UPDATE statement: modifies data
DELETE statement: deletes data

Слайд 10

Running SQL Queries
There are many way to communicate with SQL Server

in order to run database queries:
Visual Studio Integrated Development Environment
C# application
SQL Query Analyzer
osql command prompt utility

Слайд 11

Selecting Data
The SELECT statement is used to retrieve data from one

or more database tables.
SELECT list_of_fields
FROM list_of_tables
WHERE where_clause
GROUP BY group_by_clause
HAVING having_clause
ORDER BY order_by_clause

Слайд 12

SELECT Examples
The following SELECT statement matches each order with corresponding customer:
SELECT

OrderID, Customers.CustomerId, ContactName
FROM Orders INNER JOIN Customers
ON Orders.CustomerId = Customers.CustomerId
The following SELECT statement gets all orders shipped to Canada:
SELECT *
FROM Orders
WHERE ShipCountry = 'Canada'

Слайд 13

Updating Data
The UPDATE statement is used to update information in database

tables.
The following statement a specific customer’s contact name:
UPDATE Customers
SET ContactName = 'Maria Anderson'
WHERE CustomerId = 'ALFKI'

Слайд 14

Inserting Data
The INSERT statement is used to add one or more

rows to a database table.
The following statement inserts a new record to the Order Details table:
INSERT INTO [Order Details]
(OrderId, ProductId, UnitPrice, Quantity, Discount)
VALUES (10248, 2, 19.00, 2, 0)

Слайд 15

Deleting Data
The DELETE statement is used to remove information from database

tables.
The following statement deletes a record from the Customers table:
DELETE FROM Customers
WHERE CustomerId = 'ALFKI'

Слайд 16

Stored Procedures
A stored procedure is a set of SQL statements that

is stored in a database.
Stored procedures benefits:
You can use them to save complex SQL statements for future execution.
SQL Server compiles stored procedures so that they run faster than ad hoc queries.

Слайд 17

Creating Stored Procedures
You use T-SQL’s CREATE PROCEDURE statement to create a

stored procedure.
The following stored procedure, when executed, returns all customers from France:
CREATE PROCEDURE GetCustomersFromFrance
AS
SELECT * FROM Customers
Where Country = 'France'
RETURN

Слайд 18

Parameterized Stored Procedures
The following stored procedure, when executed, returns total sales

for a given customer:
CREATE PROCEDURE dbo.GetCustomerSales
(
@CustomerId char(5),
@TotalSales money OUTPUT
)
AS
SELECT @TotalSales = SUM(Quantity * UnitPrice)
FROM (Customers INNER JOIN Orders
ON Customers.CustomerId = Orders.CustomerId)
INNER JOIN [Order Details]
ON Orders.OrderId = [Order Details].OrderId
WHERE Customers.CustomerId = @CustomerId
RETURN

Слайд 19

Working with Flat Files
The data in a flat file can be

plain text or binary. These files are called “flat files” to distinguish them from more structured forms of storage, such as relational databases and XML files.
The StreamReader and StreamWriter classes allows you to manipulate text files.
The BinaryReader and BinaryWriter classes allows you to manipulate binary files.

Слайд 20

Extensible Markup Language (XML)
XML is a text-based format for representing structured

data.

Слайд 21

Working with XML
The classes that work with XML data are organized

in the System.Xml namespace:
XmlReader and XmlWriter: These classes provide a fast, non-cached, forward-only way to read or write XML data.
XmlDocument: This class is an in-memory representation of XML data and allows navigation and editing of the XML document.

Слайд 22

Working with DataSet
A DataSet is an in-memory representation of relational data.

A DataSet can have tables, relations, and data-integrity constraints such as unique constraints or foreign-key constraints.
The DataAdapter acts as a bridge between the data source and the DataSet.
The DataAdapter stores the data connection and data commands needed to connect to the data source. The DataAdapter also provides commands for retrieving data from the data source and commands for updating the data source with any changes.

Understanding Databases

Содержание

Objective Domain Matrix

Data AnomaliesInsert AnomalyYou cannot insert new data because of unrelated dependencyDelete

Data NormalizationThe process of data normalization ensures that a database design

First Normal Form (1NF)In order for a table to be in

Second Normal Form (2NF)For a table to be in second normal

Third Normal Form (3NF)For a table to be in third normal

Structured Query Language (SQL)SQL is the language used by most database

SQL QueriesSELECT, INSERT, UPDATE, and DELETE statements are the four main

Running SQL QueriesThere are many way to communicate with SQL Server

Selecting DataThe SELECT statement is used to retrieve data from one

SELECT ExamplesThe following SELECT statement matches each order with corresponding customer:SELECT

Updating DataThe UPDATE statement is used to update information in database

Inserting DataThe INSERT statement is used to add one or more

Deleting DataThe DELETE statement is used to remove information from database

Stored ProceduresA stored procedure is a set of SQL statements that

Creating Stored ProceduresYou use T-SQL’s CREATE PROCEDURE statement to create a

Parameterized Stored ProceduresThe following stored procedure, when executed, returns total sales

Working with Flat FilesThe data in a flat file can be

Extensible Markup Language (XML)XML is a text-based format for representing structured

Working with XMLThe classes that work with XML data are organized

Working with DataSetA DataSet is an in-memory representation of relational data.

Похожие презентации