Database Foundations: Object Management

Name: Database Foundations: Object Management
Author: Rodney Harris

Learn the essentials of managing database objects and SQL programmability

Preview the first lesson free — get full access to all 4 lessons.

Course: On-Demand

In this lesson, we will cover the fundamentals of database management objects in a relational database. Database management objects, or DMOs for short, define the structure, organization, and relationships of data within a database. They enable efficient management and manipulation of databases. DMOs make managing and working with databases easier and more efficient. Some common DMOs include tables, views, indexes, store procedures, and functions. To further illustrate, let's take a closer look at the SQL Server Management Objects in Management Studio. To view the management objects within the AdventureWorks database, we will start by connecting to our SQL Server instance. From there, in the Object Explorer pane on the left-hand side, to see the available database, we will expand the databases folder. Then we can expand the AdventureWorks folder, and from within that folder, we can see several common management objects. First are tables. Tables are fundamental objects in SQL Server that store data in a structured manner. To see the available tables within the AdventureWorks 2019 database, we expand this Tables folder to give us our list. Underneath Tables, we can also see the Views folder. Views are virtual tables that provide a customized view of data stored in tables. To see a list of views available in the database, we can expand the Views folder. We can also browse to available indexes. Indexes help speed up data retrieval by providing a quick way to locate data within the table. Indexes are available under their corresponding table. To see the list of indexes, we double-click on the table folder, and then we can double-click on a table, where we can browse to the available indexes underneath this table. Next, we can browse to store procedures. Door procedures are pre-compiled database objects that can execute on-demand to perform specific tasks. In contrast to previous objects, to see available store procedures, we must first expand the programability folder. And then we can expand the store procedures folder. Likewise, under the programability folder are functions. Functions are database objects that return a single value or a table of value. To see the available user-defined functions, we can expand this folder. To further illustrate these concepts, we can go over an example of how to create each object in management objects. Our first example are table objects. As we recall, tables are fundamental objects in SQL Server that store data in a structured manner. To retrieve data from our Sales.Sales order header table, we can execute the following command. Here, our query fetches all columns and rows from the Sales order header table. This displays information such as Sales order ID, order date, due date, or ship date, or the rest of the columns within that table. All these columns and tables all relate to the Sales schema. Subsequently, in our second example are views. We will run the following statement to create our first view. Create view order summary as select order ID, order date, due date from Sales.Sales order header. To further break down this statement, the create view order summary is the clause that signals we want to create a new view called order summary. The keyword as is used to specify that the view definition is coming next. Our definition of the view is in our following select statement. Select Sales order ID, order date, and due date in the Sales order header table. So let's go ahead and run our query. By creating the order summary view, we can simplify our query to retrieve only the columns we specify in the view without having to join multiple tables. So let's see how that works. If we run the query select star from order summary, we will see we only return the columns, Sales order ID, order date, or due date. This process can make it easier to write queries and improve query performance. Building upon that, let's go over example three. As we recollect, index is help speed up data retrieval by providing a quick way to locate data within a table. For example, imagine a book without a table of content. You would need to search through every page until you find the topic you were looking for. To further illustrate, let's create an index on the last name column of the person.person table. By running the following statement, create index. Ix underscore person underscore last name is the name we would like to give our newly created index. This creates a non clustered index called Ix person underscore last name on the last name column of the person table. This index would now speed up searches based on the last name column. In a similar vein, let's discuss our fourth example store procedures. Store procedures are pre written database objects that can be executed on demand to perform specific tasks. To create our store procedure, we will write the following syntax. Create procedure get customers as begin select star from sales.customer in. Here's the breakdown of the SQL structure for the store procedure get customers. On the first line, create procedure is a SQL clause that is used to create a new store procedure. Followed by get customers, which is the name of the store procedure we intend to create. As is the key word used to introduce the body of the store procedure. Followed by begin, which is the key word that indicates the start of our store procedures body. It is used to enclose a block of SQL statements that make up our procedures logic. In this case, we have select star from sales.customer. This statement will retrieve all the data from the customer table in the sales schema of the database. Finally, the end. This is the key word that indicates the end of the store procedures body. It is used to mark the end of the block of SQL statements that make up our procedures logic. Now let's create our store procedure. When the store procedure is executed, it will return the results of the select statement as a result. To execute our store procedure, we will use the following SQL statement execute get customers. This will execute the get customer store procedure and return the results of our select statement. Stay tuned for the next lesson where we will cover the basics of database tables and data types. Thank you for watching.

Beginner Provider : Rodney Harris 4 Lessons · 32m in English

Course Description

In these lessons on Database Foundations: Object Management, you will dive into the world of database management objects (DMOs) and SQL programmability. DMOs define the structure, organization, and relationships of data within a database, making them crucial for efficient database management. You will explore the core concepts of database objects, including tables, views, indexes, stored procedures, and functions, and understand their roles in managing and manipulating data effectively. Using SQL Server and SQL Server Management Studio, you will learn how to create, modify, and work with these objects.

These lessons begin with an introduction to object management, providing an overview of DMOs and SQL RDBMS concepts. You will learn about the significance of DMOs in managing databases and how they make database management easier and more efficient. Moving on, you will explore working with database tables. You will learn how to create a database table in SQL Server and add data into it, understanding the importance of choosing appropriate data types for columns. Next, we’ll explore stored procedures, views, and functions. You will also gain an understanding of SQL Server functions and their programmability, as well as how to create stored procedures that can be executed on demand to perform specific tasks. Lastly, you will explore user-defined functions, which can return a single value or a table of values, enhancing performance and data accuracy.

What You'll Learn

Understand the role of database management objects (DMOs) in efficient database management and SQL programmability
Create and modify database tables in SQL Server, selecting appropriate data types for columns and adding data
Develop stored procedures that can be executed on demand to perform specific tasks and optimize query performance
Create views to provide customized views of data and simplify queries
Implement user-defined functions that return a single value or a table of values to enhance performance and ensure data accuracy
Work with core database objects including tables, views, indexes, stored procedures, and functions using SQL Server and SQL Server Management Studio

Key Takeaways

Database management objects (DMOs) define the structure, organization, and relationships of data within a database, making them crucial for efficient database management.
Core database objects include tables, views, indexes, stored procedures, and functions, each playing a role in managing and manipulating data.
SQL Server and SQL Server Management Studio are used to create, modify, and work with database objects.
Choosing appropriate data types for columns is important when creating a database table and adding data.
User-defined functions can return a single value or a table of values, enhancing performance and data accuracy.

Frequently Asked Questions

What does this course cover?

It covers database management objects (DMOs) and SQL programmability, including tables, views, indexes, stored procedures, and functions, and how to create, modify, and work with these objects using SQL Server and SQL Server Management Studio.

What tools are used in this course?

The lessons use SQL Server and SQL Server Management Studio to create, modify, and work with database objects.

What skills will I gain from this course?

You will gain skills in Data Management, Data Modeling, and Database Management Systems.

How is the course structured?

The course includes four lessons: Introduction to Object Management; Working With Database Tables; Stored Procedures, Views, and Functions (Part 1); and Stored Procedures, Views, and Functions (Part 2).

What will I learn about user-defined functions?

You will learn how to implement user-defined functions, which can return a single value or a table of values, to enhance performance and ensure data accuracy.