Understanding the fundamental database objects is a pivotal aspect of preparing for the DP-900 Microsoft Azure Data Fundamentals exam. To understand database operations, it is important to grasp the key components, such as tables, views, stored procedures, indexes, and triggers.
1. Tables
Tables are at the heart of any database. They are the object in which data is stored in a structured manner, with rows representing unique records and columns representing attributes or properties of those records.
For instance, consider a table named ‘Students’ on a school database.
| StudentID | Name | Age | Class |
|---|---|---|---|
| 001 | John | 15 | 10th |
| 002 | Alice | 14 | 9th |
Here, the table ‘Students’ has four columns — StudentID, Name, Age, and Class, and each row represents a unique student.
2. Views
Views are virtual tables that are composed of a set of rows and columns just like a table. They do not store data themselves; instead, they pull data from existing tables or other views. Views are used to simplify complex database queries and enhance database security by hiding sensitive data.
For instance, a view can be created on the ‘Students’ table that only shows the Name and Class columns.
3. Stored Procedures
Stored procedures are sets of SQL statements compiled into a single routine that can be run to perform a specific task. They are used to encapsulate repetitive tasks, improve performance, and enhance security.
Example of a stored procedure that returns the count of students in the ‘Students’ table:
CREATE PROCEDURE GetStudentCount
AS
BEGIN
SELECT COUNT(*) FROM Students;
END;
4. Indexes
An index is a database object that speeds up the retrieval of rows from a table or view. It is like a pointer to data in a table. An index in a database works in the same way an index in a book helps to quickly locate specific information.
For instance, an index can be created on ‘Students’ table to improve query performance for a specific column like ‘StudentID’.
5. Triggers
Triggers are special types of stored procedures that run automatically when an event occurs in the database server. They are generally used to maintain the integrity of the information on the database.
For instance, a trigger can be setup on the ‘Students’ table to send a notification email each time a new record is added.
In summation, tables, views, stored procedures, indexes, and triggers are fundamental database objects. Understanding these effectively shapes the understanding related to Azure databases, an essential aspect needed to crack the DP-900 Microsoft Azure Data Fundamentals exam.
Practice Test
True or False: In a database, a table is the primary storage for data organized in rows and columns.
- Answer: True
Explanation: Tables are a crucial database object as they provide the primary storage structure for data. They have rows for entries and columns for data types.
In relational database systems, which one of the following is NOT considered a “database object”?
- a) Tables
- b) Triggers
- c) Departments
- d) Views
Answer: c) Departments
Explanation: Departments might be a term used in the data of the database, but it is not an object. Tables, triggers, and views are all examples of database objects.
Which of the following statements regarding indexes in a database are correct? (Multiple Choices)
- a) They can speed up the retrieval of rows.
- b) They operate independently of tables and views.
- c) They are optional.
- d) They are arranged by data blocks.
Answer: a, c, d
Explanation: Indexes can significantly speed up data retrieval, they are optional, and they are arranged by data blocks. However, they do not operate independently of tables and views; instead, they are tied to them.
True or False: Stored Procedures are database objects which enable us to write scripts to manipulate data.
- Answer: True
Explanation: Stored procedures are database objects that embody compiled SQL statements. They are very powerful tools that can be used to manipulate data among other functions.
In a database, which type of object represents a subset of data in one or more tables?
- a) Schema
- b) View
- c) Index
- d) Sequence
Answer: b) View
Explanation: A View in a database is an object that represents a subset of data from one or more tables.
What is a “Schema” in a database?
- a) A collection of tables
- b) A collection of database objects, including tables, views, indexes, and synonyms.
- c) A collection of views.
- d) A collection of stored procedures.
Answer: b) A collection of database objects, including tables, views, indexes, and synonyms.
Explanation: A schema can include a variety of objects including tables, views, indexes, and synonyms, not just one type of database object.
True or False: A synonym in database terms is used for renaming a column in a table.
- Answer: False
Explanation: A synonym in a database is not used to rename a column. Instead, it is used to provide an alternative name to a table, view, sequence or a stored procedure.
What is the primary function of a database trigger?
- a) To initiate start-up actions
- b) To control data input and output
- c) To automate database maintenance
- d) To respond to specific data modifications with pre-defined actions
Answer: d) To respond to specific data modifications with pre-defined actions
Explanation: A trigger is a stored procedure in a database that automatically responds to an event such as inserting, updating, or deleting data.
What is a “sequence” in a database?
- a) It’s an order in which data is stored
- b) It’s an automatically generated numeric value
- c) It’s a related set of data entries
- d) It’s a repeatable read of database entries
Answer: b) It’s an automatically generated numeric value
Explanation: A sequence in a database is an object that generates a sequence of numeric values according to specified parameters.
True or False: Constraints are used in a database to restrict the type of data that can go into a table.
- Answer: True
Explanation: Constraints are used in a database to limit the type of data that can be stored in a table. They enforce the data integrity and accuracy.
Interview Questions
What are Tables in the context of a database?
Tables are objects that hold and categorize data in a database. Each table consists of numerous rows and columns, where each column represents a category of data and each row presents a single record.
Can you define Indexes in a database?
Indexes are database objects that enhance the speed of data retrieval operations on a database table. They are similar to textbook indexes, making it faster and easier to find specific data within the database.
What is a Stored Procedure in a database?
A Stored Procedure is a prepared SQL code which can be saved and reused. When it is required to perform a particular procedure repeatedly, the user can create a Stored Procedure and call it any time instead of writing the same code repeatedly.
How would you describe a View in a database?
A View is a virtual table in a database, based on the result-set of an SQL statement. It contains rows and columns, similar to a real table. However, the fields in a view are fields from one or more real tables within the database.
What are Triggers in a database?
Triggers are database objects that are automatically executed or fired when certain actions occur. Triggers can be defined for actions such as insert, update, or delete that occurs on a table or view.
What does a Query do in a database?
A Query is used to retrieve data from a database. It serves as a way to get information that matches specific criteria from one or more particular tables.
How would you describe Sequences in a database?
Sequences are database objects used to generate unique integers. They are often used to create primary key values or any other data that needs to be unique within the database.
Can you explain a Schema in a database?
A Schema in a database is a collection of database objects, including tables, views, indexes, and synonyms. Essentially, the schema defines the structure or organization of the database.
What are Constraints in a database?
Constraints are rules used in a database to limit the type of data that can be stored in a particular table. They ensure the accuracy and reliability of the data in the table.
How would you describe a Cursor in terms of a database?
A Cursor in a database is a database object used to retrieve rows from a result set one row at a time. Cursor allows row-by-row processing of the result sets.
What is a User-defined Function in a database?
User-defined Function is a function provided by the user of a program or an environment, in a context where the usual assumption is functions are built into the program or environment. In a database, these are routines that take parameters, perform an action, and return the result.
What is a Synonym in the context of a database?
Synonym is a database object that is used to provide a layer of abstraction, allowing the user to use a different name for a table, view, sequence, procedure, or other objects. It provides both data independence and location transparency.
Define Partitioning in a database?
Partitioning in a database is the process of dividing a large table into smaller, more manageable pieces called partitions. It improves the performance, manageability, and availability of a wide variety of applications and helps reduce the total cost of ownership.
What is a Column in a database?
A Column in a database is a set of data values of a particular type, one value for each row of the database. A Column may contain text values, numbers, or even pointers to files in the operating system.
What is a Database?
A Database is an organized collection of data. It is designed to offer an efficient way to store, retrieve and manage information. Databases can be classified according to types of content: bibliographic, full-text, numeric, and images.
extutorials.com