Practice Test 2 Flashcards
Process of denormalization
Merging tables
MySQL Enterprise
High end commercial installations, like backup and security
LOWER()
Function used to convert a string to lowercase
One of the primary functions of a DBMS
Managing database security and access
GROUP BY function
Aggregates/query’s rows based on a common criteria
Main advantage of a sorted table
Optimal for queries that read data in sort order
CONCAT()
Function that combines two or more strings into a single one
JOIN
Function that combines rows from two tables based on related columns
Composite attribute
An attribute that can be divided into smaller parts, turning into a more basic attribute
How are weak entites represented in a ER diagram
Double rectangle
Storage engine
Manages the physical storage of data (read, write, indexing, data integrity). Works with the query processor to read or write as needed
query optimizer
Determines the most efficient way to execute an SQL query. works with the query processor
Query Processor
responsible for parsing, translating, optimizing, and executing SQL queries from multiple connections (users). Works closely with the optimizer
Connection manager
Manages the connections between the database and client applications.
Hash table
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
CURDATE()
Function that returns the current date but not time
TIMESTAMP()
Returns current date and time
Delete
removes rows from a database
Table Cluster
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
NoSQL
Designed to handle large volumes of unstructured or semi-structured data.
pro: fast read and write, scaling, diverse data type
con: consistency and complexity
MySQL has how many architecture layers?
four
IN function
Checks if a value matches a value from a list
Cardinality
Max or min number of entities that can be involved in a relationship
2NF
Non-key attributes must be fully dependent on the primary key, it cannot depend on only 1 part of the composite key for example.
3N
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
Boyce-Codd
like 3N but requires super keys
Insert()
Adds a new row of data to a table
MySQL monitor enterprise
Collects and displays performance info
SQRT()
Function to calculate the square root of a number
After Indexing a column when would that index get updated next?
After deleting a row the index would be updated
The person that introduced the relational model
E.F. Codd
MySQL Enterprise Audit component
Tracks all database chances including time and user details
Heap Table
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
ON function
Specifies the condition for the join between two tables
Database
Structured collection of data
Column-oriented storage
Data is stored in columns rather than rows. best for analytical queries that involve large scale processing of datasets. Not good for transactions
Row oriented storage
Stores data in rows. Best used for data sets that will required constant updates, inserts, deletes. Good for transactional applications
Associative entity (Bridge entity, junction table)
Used to represent a many to many relationship with attributes. table that links two or more tables
Set of tuples
could be a group of columns or rows. something that a select statement would return
Intersection data
data that describes the relationship between two entities
Scope, Format, and Access
All parts of data characteristics of a DB
CRUD
Create, Read, Update, Delete
MySQL Workbench
Designed for both admins and users, its a data toolset for modeling, SQL development, and data administration
Crows Foot
Symbol the reps a one to many relationship in ER diagrams
LIKE
Search for specified patterns in a column
Database Admin
Designs the physical layout of data
Secures the database against unauthorized access
Monitors the database performance and makes adjustments to optimize it
COUNT()
Returns the number of rows that match a specified condition