Database Foundations: Database Management

Database Foundations: Database Management

English | MP4 | AVC 1280×720 | AAC 48KHz 2ch | 2h 14m | 445 MB

In the realm of relational databases, the table is the main event, the showstopper, the lead singer, the king. However, there are several other components of databases that are just as vital to a fully functioning system. In this course—the third part of his Database Foundations series—Adam Wilbert turns the spotlight on the supporting cast of database management characters. He covers topics like view objects, query performance, transactions, and stored procedures, and shows how each is an integral part of database management. Adam also includes challenges designed to test your skills as the course advances. As Adam illustrates, knowledge of these elements of database management can enhance your system performance and lead to a more consistent end-user experience.

+ Table of Contents

1 Managing database performance
2 What you should know
3 Set up the database servers
4 Create the Two Trees database

Database Views
5 Save queries as database view objects
6 Create a database view
7 Views and database maintenance
8 Views and database security
9 Challenge Create a view
10 Solution Create a view

Query Performance
11 The query optimizer
12 Reading execution plans
13 View a SQL Server execution plan
14 View a PostgreSQL execution plan
15 Control query plans with hints

16 Transactions and data consistency
17 ACID properties
18 Commit and rollback transactions
19 Understanding concurrency and locks

Data and Object Management
20 Explore the system tables
21 Duplicate tables
22 Delete data with TRUNCATE
23 Remove database objects with DROP
24 Create temporary tables

Functions and Stored Procedures
25 Add programming elements to the database
26 Create a function
27 Create a stored procedure in PostgreSQL
28 Create a stored procedure in SQL Server
29 Challenge Write a stored procedure
30 Solution Write a stored procedure

31 Next steps