Practice Test 2

Question 1

Process of denormalization

Answer 1

Merging tables

Question 2

MySQL Enterprise

Answer 2

High end commercial installations, like backup and security

Question 3

LOWER()

Answer 3

Function used to convert a string to lowercase

Question 4

One of the primary functions of a DBMS

Answer 4

Managing database security and access

Question 5

GROUP BY function

Answer 5

Aggregates/query’s rows based on a common criteria

Question 6

Main advantage of a sorted table

Answer 6

Optimal for queries that read data in sort order

Question 7

CONCAT()

Answer 7

Function that combines two or more strings into a single one

Question 8

JOIN

Answer 8

Function that combines rows from two tables based on related columns

Question 9

Composite attribute

Answer 9

An attribute that can be divided into smaller parts, turning into a more basic attribute

Question 10

How are weak entites represented in a ER diagram

Answer 10

Double rectangle

Question 11

Storage engine

Answer 11

Manages the physical storage of data (read, write, indexing, data integrity). Works with the query processor to read or write as needed

Question 12

query optimizer

Answer 12

Determines the most efficient way to execute an SQL query. works with the query processor

Question 13

Query Processor

Answer 13

responsible for parsing, translating, optimizing, and executing SQL queries from multiple connections (users). Works closely with the optimizer

Question 14

Connection manager

Answer 14

Manages the connections between the database and client applications.

Question 15

Hash table

Answer 15

structure that provides an efficient way to store and retrieve data using key-value pairs. Uses a function and key to determine the bucket in each row

Question 16

CURDATE()

Answer 16

Function that returns the current date but not time

Question 17

TIMESTAMP()

Answer 17

Returns current date and time

Question 18

Delete

Answer 18

removes rows from a database

Question 19

Table Cluster

Answer 19

database storage technique where multiple tables that share common columns are stored together in the same physical data blocks. Can improve performance and reduce disk space but can increase complexity and DB maintence and performs poorly for joins with non-cluster key columns

Question 20

NoSQL

Answer 20

Designed to handle large volumes of unstructured or semi-structured data.

pro: fast read and write, scaling, diverse data type

con: consistency and complexity

Question 21

MySQL has how many architecture layers?

Answer 21

four

Question 22

IN function

Answer 22

Checks if a value matches a value from a list

Question 23

Cardinality

Answer 23

Max or min number of entities that can be involved in a relationship

Question 24

2NF

Answer 24

Non-key attributes must be fully dependent on the primary key, it cannot depend on only 1 part of the composite key for example.

Question 25

3N

Answer 25

non key attributes shouldnt depend on any other non key attributes. All non key attributes should depend on the entire primary key and nothing but the key

Question 26

Boyce-Codd

Answer 26

like 3N but requires super keys

Question 27

Insert()

Answer 27

Adds a new row of data to a table

Question 28

MySQL monitor enterprise

Answer 28

Collects and displays performance info

Question 29

SQRT()

Answer 29

Function to calculate the square root of a number

Question 30

After Indexing a column when would that index get updated next?

Answer 30

After deleting a row the index would be updated

Question 31

The person that introduced the relational model

Answer 31

E.F. Codd

Question 32

MySQL Enterprise Audit component

Answer 32

Tracks all database chances including time and user details

Question 33

Heap Table

Answer 33

Table that isnt clustered indexed. No order to rows. Data is stored in the order its inserted. Tracks free space as a linked list for future inserts

Question 34

ON function

Answer 34

Specifies the condition for the join between two tables

Question 35

Database

Answer 35

Structured collection of data

Question 36

Column-oriented storage

Answer 36

Data is stored in columns rather than rows. best for analytical queries that involve large scale processing of datasets. Not good for transactions

Question 37

Row oriented storage

Answer 37

Stores data in rows. Best used for data sets that will required constant updates, inserts, deletes. Good for transactional applications

Question 38

Associative entity (Bridge entity, junction table)

Answer 38

Used to represent a many to many relationship with attributes. table that links two or more tables

Question 39

Set of tuples

Answer 39

could be a group of columns or rows. something that a select statement would return

Question 40

Intersection data

Answer 40

data that describes the relationship between two entities

Question 41

Scope, Format, and Access

Answer 41

All parts of data characteristics of a DB

Question 42

CRUD

Answer 42

Create, Read, Update, Delete

Question 43

MySQL Workbench

Answer 43

Designed for both admins and users, its a data toolset for modeling, SQL development, and data administration

Question 43

Crows Foot

Answer 43

Symbol the reps a one to many relationship in ER diagrams

Question 44

LIKE

Answer 44

Search for specified patterns in a column

Question 45

Database Admin

Answer 45

Designs the physical layout of data Secures the database against unauthorized access Monitors the database performance and makes adjustments to optimize it

Question 46

COUNT()

Answer 46

Returns the number of rows that match a specified condition