Brainscape's Knowledge GenomeTM

Browse over 1 million classes created by top students, professors, publishers, and experts.


See full index

Data Management > units 1-5 > Flashcards

units 1-5 Flashcards

1
Q

What does a Query Language allow a user to do?

A

retrieve or change data in a database

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

What are concurrent transactions? How do DBMS systems protect data during concurrent transactions?

A

Particularly challenging requirements for database systems. Prevents conflicts between concurrent transactions. Ensure transaction results are never lost.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

What is a NoSQL database optimized for?

A

Big Data

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

Does a SELECT statement modify data in a database?

A

No, it only retrieves data from table.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

In a VARCHAR(20), what does the 20 do?

A

it indicates 20 characters in the data

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

What is the root account used for in MySQL? What permissions does it have?

A

it is an administrative account that has full control over MySQL. Gives access to databases, tables and users

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

What character in MySQL is used to indicate the end of a command?

A

semi colon

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

What two things comprise a column in the relational model?

A

a name and data

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

What are keywords in SQL? What do they do? Be able to recognize examples

A

SELECT: selects data from the database Ex: Name

FROM: specifies the table(s) in which these data columns are located Ex: City

WHERE: specifies criteria that field values must meet for the records that contain the values to be included in the query results

EX: Population >

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

What is data independence? How does it relate to physical design?

A

The principle is that physical design never affects query results. It changes the tune of query performance, but not the application programs.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

Be able to recognize the correct syntax for creating and deleting a database in MySQL.

A

Create Database and Drop Database

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

Given a CREATE TABLE statement, be able to tell how many columns are contained in the resulting table

A

SELECT COUNT(*) FROM INFORMATION_SCHEMA. COLUMNS WHERE table_name = ‘table_name’;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

Be able to recognize the correct syntax for a SELECT statement that retrieves specified columns from
a specified table.

A

Select cloumn1,…cloumn2…

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

Given column names, be able to tell the best data type to use to store the information

A

Integer, Decimal, Character, Date and Time, Binary, Spatial, Document

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

Be able to recognize the syntax for preventing NULL values from appearing in a given column

A

NOT NULL

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

What does a NULL value represent?

A

unknown data

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

Be able to recognize the correct syntax to update a given field in a specified table

A

UPDATE TableName SET Column1 = Value1, Column2 = Value2, … WHERE condition;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
17
Q

What is a primary key? What is it used for?

A

is a column, or group of columns, used to identify a row. To ensure that each value identifies exactly one row

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
18
Q

What is a foreign key? What is it used for? Be able to recognize one given tables in a relational
database

A

Is a column, or group of columns, that refers to a primary key. To link data in one table to data in another table.

CREATE TABLE TableName ( . . . FOREIGN KEY (ColumnName) REFERENCES TableName (ColumnName), . .

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
19
Q

Know how the CASCADE keyword works to maintain database integrity when fields are updated
and deleted. Be able to recognize the correct syntax for a CASCADE operation on a database

A

propagates primary key changes to foreign keys

FOREIGN KEY (ManagerID) REFERENCES Employee(ID) ON DELETE SET NULL ON UPDATE CASCADE

20
Q

What is a constraint? What does it do?

A

is a rule that governs allowable values in a database

specify rules for the data in a table

21
Q

Be familiar with how the ROUND function works. Given a statement containing it, be able to
determine the resulting output.

A

Returns n rounded to d decimal places

SELECT ROUND(16.25, 1)

22
Q

Be familiar with what SUM, AVG, MAX, and MIN do. Given a table and a SELECT query, be able to
determine and recognize what these functions would output

A

MIN() finds the minimum value in the set. (Select MIN)

MAX() finds the maximum value in the set. (Select Max)

SUM() sums all the values in the set. (Select SUM)

AVG() computes the arithmetic mean of all the values in the set. (Select AVG)

23
Q

When using a join on two tables, be familiar with the correct syntax so you can recognize where an
error in a query occurs

A

WHERE Table A column 2 = Table B column 2

24
Q

Given a SELECT statement and a sample table, be able to determine how many rows are returned
from the query.

A

WHERE (_) IN

25
Q

Know the differences between a Full join, Self-join, Left join, Right join, and Cross-join. Be able to
determine which needs to be used given a scenario

A

Full Join: Select all left and right table rows, regardless of the match

(Merging data and Handling missing data)

Self-Join: joins a table to itself

(comparing values in a hierarchical table, finding duplicate values, or comparing rows with other rows in the same table)

Left Join: selects all left table rows, but only matching right table rows

(hierarchical table, finding duplicate values, or comparing rows)

Right Join: selects all right table rows, but only matching left table rows

(hierarchical table, finding duplicate values, or comparing rows)

Cross Join: combines two tables without comparing columns

(creates a result table containing a paired combination of each row of the first table with each row of the second table)

26
Q

What is a correlated subquery?

A

when the subquery’s WHERE clause references a column from the outer query

27
Q

Be able to determine what expression needs to fill a SELECT query to return the correct
mathematical value given. The expression may need to contain grouping functions like SUM, MAX, or
MIN

A

(SELECT MAX(Percentage) FROM CountryLanguage WHERE CountryCode = C.CountryCode)

28
Q

What are the benefits of creating and using view tables?

A

Protect sensitive data

Save complex queries

Save optimized queries

29
Q

What are the characteristics of compatible tables in relational algebra?

A

same number of columns with the same data types

30
Q

In an ER diagram, what do entities, relationships, and attributes look like when implemented in SQL?

A

entity is a person, place, product, concept, or activity

(Rectangles: Rectangles represent Entities in the ER Model)

relationship is a statement about two entities.

(Ellipses: Ellipses represent Attributes in the ER Model.)

attribute is a descriptive property of an entity.

(Diamond: Diamonds represent Relationships among Entities_

31
Q

What part of speech typically represents an entity, a relationship, and an attribute?

A

Entities are usually nouns

Relationships are usually verbs

Attributes are usually nouns

32
Q

How many attribute values minimum does an optional attribute have?

A

one.

33
Q

What is an identifying entity? What does it connect in an ER diagram?

A

a weak entity usually has a relationship with another entity.

connects a weak entity and a strong entity.

34
Q

What do shared attributes an relationships look like in a supertype?

A

the common characteristics that all its subtypes inherit

35
Q

What are other names (synonyms) for strong and weak entities?

A

dependent entity or non-identifying entity.

36
Q

What is an artificial key? Under what circumstances might a DBMS designer create one?

A

is a simple primary key created by the database designer.

if the value of the natural key changes

37
Q

What does a many-to-many relationship look like when implemented in SQL?

A

when one or more items in one table can have a relationship to one or more items in another table

(Two rectangles with two crow feet connecting each other)

38
Q

What value should be allowed in an optional relationship or attribute?

A

NULL

39
Q

What type of redundancy is possible in second normal form?

A

Partial dependency

40
Q

What is normalization used to do?

A

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

41
Q

What is access time?

A

is the time required to access the first byte in a read or write operation

42
Q

What do single-level index files contain?

A

contains column values, along with pointers to rows containing the column value

43
Q

What is a bucket? What do they contain?

A

is a block or group of blocks containing index entries.

44
Q

What is a tablespace? What does it contain?

A

is a database object that maps one or more tables to a single file

45
Q

What type of expression does a hash partition require?

A

partition expression with positive integer values.

46
Q

What does EXPLAIN do? How can it help identify and resolve slow queries?

A

generates a result table that describes how the storage engine executes a statement.

each slow query to assess the effectiveness of indexes. A high value for rows and a low value for filtered indicates either a table scan or an ineffective index.

47
Q

What are the characteristics of Boyce-Codd normal form?

A

all determinants are candidate keys

48
Q

How does a hash table structure locate rows?

A

Convert the hash key by interpreting the key’s bits as an integer value.

Divide the integer by the number of buckets.

Interpret the division remainder as the bucket number.

Convert the bucket number to the physical address of the block containing the row.

Data Management (1 decks)

Brainscape helps you reach your goals faster, through stronger study habits.
© 2024 Bold Learning Solutions. Terms and Conditions