D426 SET

Authorization

Which requirement within large, complex databases ensures users have limited access to the database?

Conceptual design

Which design type specifi es database requirements without regard to a specifi c database system?

They are logical constraints that ensure the data is valid.

What characterizes the rules of relational databases?

When an SQL statement is syntactically incorrect

Which event will result in an error code and error description when using MySQL Server?

INT

Which data type should be used to store whole integer values, such as age?

MySQL Command-Line Client

Which text-based interface is included in the MySQL Server Download?

MySQL

Which database system includes the World database during its installation?

Structured Query Language

What is the standardized language of relational database systems?

Data Control Language (DCL)

Which SQL sublanguage is used to manage database access?

Data Transaction Language (DTL)

Which SQL sublanguage is used to roll back database changes?

hh:mm:ss

Which format is used by the TIME data type in MySQL?

OR

Which MySQL operator is the last in the order of operator precedence?

<=

Which operator is used to compare columns from the left and right tables in MySQL?

CROSS

Which type of join combines two tables without comparing columns?

Equijoin

Which type of join compares columns using only the = operator?

EQUIJOIN

Which type of join is demonstrated by the following query?

SELECT Dog.Nickname, Kennel.Address

FROM Dog, Kennel

WHERE Dog.KennelID = Kennel.ID

In a nested query, the outer query is evaluated first.

What is another name for a subquery?

IN

Which operator is used to compare against a list of values when determining a match in a WHERE clause?

%

Which wildcard character is used to represent zero or more characters when searching for a specified pattern using a LIKE operator?

MIN

Which function is considered an aggregating function in SQL?

GROUP BY

Which clause or function is used with aggregate functions to produce summary rows?

Inner Join

Which type of join returns only the matching values when selecting rows from two or more tables?

Inner Join

Which join type selects only matching left and right table rows?

Materialized View

Which phrase refers to the view in which data is persisted and is automatically changed as the underlying data is changed?

UPDATE Employee SET Salary = 50000 WHERE ID = 1;

Which SQL command uses the correct syntax to update the salary of an employee in the Employee table?

Metadata

What describes elements such as column name and data type?

Tuple

Which term identifies an ordered collection of elements enclosed in parenthesis in a tablespace?

SELECT name, salary FROM Employee WHERE salary > 50000ORDER BY salary DESC;

Which SQL command uses the correct syntax to select the name and salary columns from the Employee table, where the salary is greater than 50000, and sort the results by salary in descending order?

INSERT INTO table_name (column1, column2) VALUES (value1,value2);

Which INSERT statement demonstrates valid syntax in SQL?

UPDATE table_name SET column1 = value1, column2 = value2WHERE condition;

Which UPDATE statement uses valid syntax in SQL?

TRUNCATE

Which statement used to remove data from temporary tables?

CREATE

Which keyword is a DDL (data definition language) keyword in SQL?

Not Null

Which property is enforced by a primary key fi eld?

Auto-increment

Which property or function allows SQL to insert a fi eld value?

CASCADE

Which property specifies an expression on one or more columns of a table?

Simple primary key

Which type of key is a single column in a table used to identify a row?

Composite

Which type of key is often represented by parentheses to show multiple columns?

Simple

Which primary key characteristic means it includes information that is easy to type and store?

One-to-many

Which type of relationship is established by a foreign key in a relational database?

INSERT

Which statements may be rejected when a foreign key constraint is specified?

Candidate key

Which key describes the unique columns in a table that do not contain a primary key?

Multi-level index

Which index stores column values and row pointers in a hierarchy?

Hash index

Which type of index refers to entries that are assigned to buckets?

Bitmap index

Which type of index is a grid of bits where each index row corresponds to a unique row in a table?

Foreign key

Which part of a junction table in a relational database establishes a many-to-many relationship between two tables?

Logical design

Which type of development activity is used to convert an entity-relationship model (ERM) into tables, columns, and keys?

Trivial dependency

Which term addresses columns of A being a subset of the columns of B, with A always depending on B?

Merging tables

Which term describes the process of denormalization?

It is a first normal form table.

A database designer is working with a table that has a primary key and no duplicate rows.

What can be concluded about the table?

Normalization

Which process eliminates redundancy by decomposing a table into two or more tables in a higher normal form?

Boyce-Codd normal form

Which term describes the optimal normal form for frequent inserts, updates, and deletes of data?

One-to-many

Which relationship is depicted in the following entity-relationship (ER) diagram?

IsA relationship

Which relationship or association exists between a supertype entity and its subtype entities?

Square

Which symbol is used in an entity-relationship (ER) diagram for an entity?

Circle

Which symbol is used in an entity-relationship (ER) diagram to indicate an attribute?

Identify entities.

What must be done before creating supertype and subtype entities?

Entity

Which real-world object can be distinctly identified and grouped, such as all employees in a company?

Weak entity

Which item in an entity-relationship (ER) diagram follows the attribute name and is placed outside of parentheses?

Unique identifier analysis

Which strategy can be used to identify primary keys when investigating data relationships?

In the direction the relationship verb is facing

How should an entity relationship phrase be read to correctly understand how one entity relates to another entity in an entity-relationship diagram (ER diagram)?

Attribute

What is a property or characteristic of an entity?

Modality

What is the minimum number of entity occurrences?

Cardinalities

What is the maximum number of entities that can be involved in a particular relationship?

BETWEEN

Allows you to specify a range of numeric values in a search

IN

Allows you to specify a list of character strings included in a search.

LIKE

Allows you to specify partial characters in a wildcard sense.

Query Processor

Interprets queries, creates a plan to modify the database or retrieve data, and returns query results to the application.

Application Programming Interface

A library of procedures or classes that links a host programming language to a database.

Attribute

What is a formal term for a column in entity-relationships & relational models?

Analysis steps

1. Discover entities, relationships, and attributes.

2.Determine cardinality.

3. Distinguish strong and weak entities.

4. Create supertype and subtype entities.

Logical Design steps

5.Implement entitles.

6. Implement relationships.

7. Implement attributes.

8. Apply normal form.

SELECT FORMAT

SELECT

FROM

WHERE;

Data Type

Named set of values, from which a column values are drawn.

Literals

Explicit values that are a string, numeric, or binary. Strings must be surrounded by single quotes or double quotes.

Synonyms

Often, entity, relationships, and attributes use this in tables?

CREATE INDEX

CREATE INDEX IndexName ON TableName (Column1, Column2, ..., ColumnN);

DROP INDEX

DROP INDEX IndexName ON TableName;

SHOW INDEX

SHOW INDEX FROM TableName;

ON

Which clause specifies the join columns?

INNER JOIN or FULL JOIN

Specifies the right table.

FROM

Specifies the left table.

Outer Join

Any join that selects unmatched rows, including left, right, and full joins

Syntax for ALTER Add statement.

ALTER TABLE TableNAME

ADD ColumnName DataType;

Syntax for ALTER Change statement.

ALTER TABLE TableName CHANGE CurrentColumnName NewColumnName NewDataType;

Syntax for ALTER Drop statement.

ALTER TABLE TableName DROP ColumnName;

Inserting values for auto-increment primary keys

Omitting values for primary keys that are not auto-increment columns.

Database users make the following errors when inserting auto_increment primary keys?

INSERT INTO Department (Name, ManagerID)

Values ('Engineering', 2538)

CREATE TABLE Department ( Code TINYINT UNSIGNED AUTO_INCREMENT,

Name VARCHAR(20) NOT NULL,

ManagerID SMALLINT UNSIGNED,

PRIMARY KEY (Code)

);

What is the correct syntax for Insert statement?

INSERT INTO Department (Name, ManagerID)

VALUES (' ', NULL);

CREATE TABLE Department (

Code TINYINT UNSIGNED AUTO_INCREMENT,

Name VARCHAR(20) NOT NULL,

ManagerID SMALLINT UNSIGNED,

PRIMARY KEY (Code)

);

Which statement correctly inserts an unnamed department with no manager?

Composite Primary Key

Consists of multiple columns, denoted with parentheses.

Logical design

Specifies tables, columns, and keys.

Physical design

Specifies indexes, table structures, and partitions.

CREATE INDEX Syntax

CREATE INDEX IndexName ON TableName (Column1, Column2, ..., ColumnN);

DROP INDEX Syntax

DROP INDEX IndexName ON TableName;

SHOW INDEX Syntax

SHOW INDEX FROM TableName;

Similar entities

Has many common attributes and relationships.

Primary Key columns

Solid bullets (●) denotes what?

Foreign Key columns

Open bullets (⚬) denotes what?

TINYINT

1 byte

SMALLINT

2 bytes

MEDIUMINT

3 bytes