DBMS 110 MOAC Lesson 1

SSMS

A central feature of SSMS is the Object Explorer, which allows users to browse, select, and manage any of the objects within the server . SSMS can also be used to view and optimize database performance, as well as to create and modify databases, tables, and indexes.

database management system (DBMS)

A collection of programs that enables you to enter, organize, and select data in a database. For example, a ticket agent may run a ticket system program on his or her desk computer that in turn accesses a ticketing database.

index

A data structure that improves the speed of data retrieval operations on a database table. The disadvantage of indexes is that they need to be created and updated, which requires processing resources and takes up disk space.

XQuery

A query and functional programming language that is designed to query collections of XML data.

Drop statement

Actually removes an object from a database, but if other objects are dependent on the object you are attempting to remove, this statement will fail and an error will be raised.

Create statement

Allows you to create a variety of database objects, including tables, views, and stored procedures. For instance, say you want to create a new table named Planets within a database named AdventureWorks.

database (db)

An organized collection of data, typically stored in electronic format. It allows you to input, organize, and retrieve data quickly. Traditional databases are organized by fields, records, and files.

foreign key

Constraints can define entity relationships between tables on a continual basis. They are also referred to as____________ constraints.

databasse objects DML

DDL influences ____________, whereas____________ influences actual data stored in tables.

storage and programmability

Database objects are divided into two categories:____________ and ____________.

database server

Databases are often found on database servers so that they can be accessed by multiple users and provide a high level of performance.

select

In order to use the views object to view a data set, you must use the____________ Transact-SQL statement to show data from underlying tables.

SQLCMD

Is a command-line application that comes with Microsoft SQL Server and exposes the management features of SQL Server. It allows SQL queries to be written and executed from the command prompt. It can also act as a scripting language to create and run a set of SQL statements as a script. Such scripts are stored as .sql files, and they are used either for management of databases or to create the database schema during database deployment.

Data Definition Language (DDL)

Is a subset of the Transact-SQL language; it deals with creating database objects like tables, constraints, and stored procedures. The interface used to create these underlying DDL statements is the SSMS user interface.

relational database

Is similar to a hierarchical database in that data is stored in tables and any new information is automatically added into the table without the need to reorganize the table itself. Unlike in hierarchical databases, however, a table in a relational database can have multiple parents. A relational database is a collection of tables.

Constraints

Limitations or rules placed on a field or column to ensure that data that is considered invalid is not entered. For example, if a person's age should be input, then the data that is entered must be a positive number—a person cannot have a negative age. A variety of constraints are available with SQL Server 2008, including the following: • A unique constraint allows the database administrator to specifically identify which column should not contain duplicate values. • A check constraint allows the administrator to limit the types of data a user can insert into the database. • A default constraint is used to insert a default value into a column. If no other value is specified, the default value will be added to all new records. • A not null constraint ensures that data is entered into a cell. In other words, the cell cannot be blank. It also means that you cannot insert a new record or update a record without adding a value to this field. • The primary key constraint uniquely identifies each record in a database table. The primary key must contain unique values and it cannot contain NULL values. Each table should have a primary key, and each table can have only one primary key. • A foreign key constraint in one table points to a primary key in another table.

Self-reference

Possible for a foreign key constraint to reference columns in the same table

SQL Server Management Studio (SSMS)

Primary tool for managing the server and its databases using a graphical interface.

Data Manipulation Language (DML) statements

SELECT, INSERT, UPDATE, DELETE, or MERGE

Indexes should only be created on columns that are frequently searched. b. A self-reference arises when a foreign key constraint references a column in the same table.

Select all of the following statements that are true: a. Indexes should only be created on columns that are frequently searched. b. A self-reference arises when a foreign key constraint references a column in the same table. c. A single INSERT statement can be used to add rows to multiple tables. d. Multiple primary keys can be added to a table.

hierarchical database

Similar to a tree structure (such as a family tree). In this database, each "parent" table can have multiple "children," but each child can have only one parent.

flat-type database

Simplistic in design. They are most commonly used in plain-text formats. Because their purpose is to hold one record per line, they make access, performance, and queries very quick. An example of this type of database would be what you would find in a .txt or .ini file. Flat-type databases are considered "flat" because they are two-dimensional tables consisting of rows and columns.

sys schema

System views belong to the ____________.

create

Tables created using the____________ statement are used to store data.

Mircosoft SQL Server

The Microsoft database server that hosts relational databases is called ____________.

USE, CREATE, ALTER DROP TRUNCATE, DELETE

The core DDL statements are ____________, ____________, ____________, ____________, ____________ and ____________.

SELECT, INSERT, UPDATE,DELETE,MERGE

The core DML statements are ____________, ____________, ____________, ____________, and ____________.

relationship

The foreign key constraint is a(n)____________ identifier

Transact-SQL

The primary means of programming and managing SQL Server. It exposes keywords so that you can create and manage databases and their components and monitor and manage the server itself. When you use SSMS to perform an action or task, you are executing Transact-SQL commands.

Data Definition Language (DDL) statements

USE, CREATE, ALTER , DROP, TRUNICATE, DELETE

constraint

What are limitations or rules placed on a field or column to ensure that data that is considered invalid is not entered?a. Primary key b. index c. Foreign key d. constraint

Structured Query Language

What does SQL stand for? a. Structured Question Language b. Structured Query Language c. Strong Question Language d. Specific Query Language

self-join

When a self-reference is used to query a table, this arrangement is now referenced as a self-join.

System Tables

When you want to query system views to verify whether the object(s) you wish to drop are, in fact, in the database tables, you need to know what tables are the most useful. System views belong to the sys schema. Some of these system tables include the following: • sys.Tables • sys.Columns • sys.Databases • sys.Constraints • sys.Views • sys.Procedures • sys.Indexes • sys.Triggers • sys.Objects

UPDATE

Which SQL statement is used to update data in a database? a. SAVE b. MODIFY c. SAVE AS d. UPDATE

SELECT

Which of the following SQL statements is used to extract data from a database? a. SELECT b. OPEN c. EXTRACT d. GET

Changing the identity constraint of an existing column.

Which of the following actions is not supported by ALTER? a. Adding a new column to a table. b. Deleting multiple columns from an existing table. c. Modifying the data type of an existing column. d. Changing the identity constraint of an existing column.

MERGE

Which of the following is not a DDL statement? a. CREATE b. MERGE c. ALTER d. DROP

REMOVE

Which of the following is not a DML statement? a. REMOVE b. INSERT c. DELETE d. TRUNCATE

Range

Which of the following is not a column constraint? a. Default b. Check c. Range d. Unique

Null

Which of the following is not a constraint? a. Null b. Unique c. Check d. Primary

Alter statement

changes an existing object; you can use it to add or remove columns from a table,

Data Manipulation Language (DML)

the language element that allows you to use the core statements INSERT, UPDATE, DELETE, and MERGE to manipulate data in any SQL Server tables. Core DML statements include the following: • SELECT: Retrieves rows from the database and enables the selection of one or many rows or columns from one or many tables in SQL Server. • INSERT: Adds one or more new rows to a table or a view in SQL Server. • UPDATE: Changes existing data in one or more columns in a table or view. • DELETE: Removes rows from a table or view. • MERGE: Performs insert, update, or delete operations on a target table based on the results of a join with a source table.

DDL statements

• USE: Changes the database context. • CREATE: Creates a SQL Server database object (table, view, or stored procedure). • ALTER: Changes an existing object. • DROP: Removes an object from the database. • TRUNCATE: Removes rows from a table and frees the space used by those rows. • DELETE: Remove rows from a table but does not free the space used by those rows removed.