MTA Exam 1

Secure the SQL server using ports 1433 and 1434. These ports are commonly used to connect to the SQL Server engine.

Two ways to grant user permissions: 1. Adding users to an existing role: Assign users to a role already having the required permissions. 2. Granting individual permissions: Provide specific access rights to each user directly.

A foreign key is a field in a table that references the primary key of another table. It ensures data integrity by enforcing relationships between tables.

Use a foreign key constraint to ensure that an employee can only be assigned to an existing department. This ensures the integrity of data in the Employee table.

The 'TRUNCATE' command removes all data from a table, but keeps the table structure intact. It's faster than deleting data row by row.

The ALLOW SELECT command grants permission to read data from a table. This is needed for users who only need to view data.

The SELECT statement is used to retrieve data from a table. It's used for querying and displaying information.

The UPDATE statement is used to modify existing data in a table. This is used to change or update specific records.

A unique index ensures that the index key contains no duplicate value and that every row in the table or view is unique.

A non-clustered index is defined on a table or view by using a clustered index or heap.

To improve query performance, an index on the 'Salary' column would be beneficial.

The 'DROP TABLE' command removes a table and all its associated data from the database.

A constraint in a relational database management system ensures that data entered into a field in a column is valid.

The 'MONEY' data type is recommended for storing and performing financial calculations.

The 'ALTER TABLE' command adds a new column to an existing table.

A row holds information for a single record in a table. It's like a single line in a spreadsheet.

A database object that ensures that each record in a dependent table (e.g., Sales) has a valid corresponding record in a related table (e.g., SalesPerson). It acts like a link between two tables, enforcing the data integrity by ensuring that the referenced data exists.

A database object that is added to a table to create a unique identifier for each record, preventing redundant entries and ensuring data integrity. It determines the order in which the rows of a table are physically stored.

A Data Definition Language (DDL) keyword used to remove an existing table from a database, effectively deleting all associated data.

A database object that helps in organizing the data retrieval process. It stores only a sorted index of the data, not the whole data itself, providing a faster way to locate specific data.

A database object that stores data in a sorted order based on the index key. It is used to improve data retrieval efficiency.

The first stage of database normalization, where a table is designed to eliminate repeating groups of data. Each row represents a unique entity, and there are no duplicate rows.

A database object that defines a relationship between two tables, allowing data from one table to be referenced in another. This enforces data integrity by ensuring that the referenced data exists.

A database object that acts as a unique identifier for each record within a table. It is used to prevent data redundancy and ensure data integrity.

A SQL Server login can use Windows Authentication to verify the identity of a user. This allows for seamless integration with Active Directory.

Roles in SQL Server manage object-level permissions, granting access to specific functions or actions within a database.

The WHERE clause is used to specify conditions for data retrieval, filtering the results based on certain criteria.

The PRIMARY KEY constraint ensures each record in a table has a unique identifier, preventing duplicates and maintaining data integrity.

When working with data in a table, the SELECT statement is used to retrieve information or rows.

The MONEY data type is specifically designed to accurately store and perform calculations on monetary values, ensuring precise financial operations.

Adding an index to the salary column can significantly improve query performance by creating a faster way to find specific salary values.

A FOREIGN KEY constraint establishes a relationship between two tables, linking a column in one table to the primary key of another table.

A clustered index orders and stores the data columns of a table or view based on the clustered index key.

An update statement modifies existing data in a table. It specifically targets rows that meet a given condition.

The CREATE TABLE statement is used to define a new table structure in a database. This includes specifying column names, data types, and potential constraints.

The CREATE INDEX statement is used to create an index on a table. An index helps to speed up data retrieval by providing a quick lookup path.

A data type that stores variable-length text strings, optimizing storage by using only the space needed for the actual text.

A data type that stores fixed-length text strings, allocating a predetermined amount of space regardless of the actual text length.

A database object that presents a specific view of the underlying data in a table, without storing data itself.

Ensures data integrity by preventing duplicate values within a column. It helps maintain unique records within a table.

Allows for the specification of a default value automatically assigned to a column when no value is explicitly provided during data insertion.

A rule that restricts the values allowed in a column, ensuring data validity and consistency within a table.

A relational database design principle that minimizes redundancy and promotes data integrity by storing data in separate tables with relationships defined between them.