Download Database Concepts and SQL Queries: Q&A for Exam Preparation and more Exams Advanced Education in PDF only on Docsity!
WGU - D426: Objective Assessment Database Application - ANSWER Software that helps business users interact with database systems. Database Administrator - ANSWER Responsible for securing the database system against unauthorized users. A database administrator enforces procedures for user access and database system availability. Authorization - ANSWER Many database users should have limited access to specific tables, columns, or rows of a database. Database systems authorize individual users to access specific data. Rules - ANSWER Database systems ensure data is consistent with structural and business rules. Query Processor - ANSWER Interprets queries, creates a plan to modify the database or retrieve data, and returns query results to the application. Performs query optimization to ensure the most efficient instructions are executed on the data. Storage Manager - ANSWER Translates the query processor instructions into low-level file-system commands that modify or retrieve data. Database sizes range from megabytes to many terabytes, so the storage manager uses indexes to quickly locate data. Transaction Manager - ANSWER Ensures transactions are properly executed. The transaction manager prevents conflicts between concurrent transactions. The transaction manager also restores the database to a consistent state in the event of a transaction or system failure. Metadata - ANSWER Data about the database, such as column names and the number of rows in each table. Relational Database - ANSWER Stores data in tables, columns, and rows, similar to a spreadsheet. Relational Database - ANSWER All _________ ________ systems support the SQL query language. Relational Database - ANSWER Relational systems are ideal for databases that require an accurate record of every transaction, such as banking, airline reservation systems, and student records. MongoDB (NoSQL) - ANSWER The newer non-relational systems are called NoSQL, for 'not only SQL', and are optimized for big data.
SQL Statements - ANSWER INSERT inserts rows into a table. SELECT retrieves data from a table. UPDATE modifies data in a table. DELETE deletes rows from a table. CREATE TABLE (Statement) - ANSWER A statement that creates a new table by specifying the table and column names. Each column is assigned a data type that indicates the format of column values. Data types can be numeric, textual, or complex. Data Type - ANSWER INT stores integer values. DECIMAL stores fractional numeric values. VARCHAR stores textual values. DATE stores year, month, and day. Database Design - ANSWER Analysis Logical design Physical design Analysis Phase, Conceptual Design, Entity-Relationship Modeling (Database Design) - ANSWER This phase specifies database requirements without regard to a specific database system. Requirements are represented as entities, relationships, and attributes. An entity is a person, place, activity, or thing. A relationship is a link between entities, and an attribute is a descriptive property of an entity. Logical Design (Database Design) - ANSWER This phase implements database requirements in a specific database system. For relational database systems, _________ design converts entities, relationships, and attributes into tables, keys, and columns. Physical Design - ANSWER Specifies indexes, table structures, and partitions. This phase adds indexes and specifies how tables are organized on storage media. Affects query processing speed but never affects the query result. Data Independence - ANSWER Allows database designers to tune query performance without changes to application programs.
Literals (SQL Syntax) - ANSWER Explicit values that are string, numeric, or binary.Strings must be surrounded by single quotes or double quotes.Binary values are represented with x'0' where the 0 is any hex value. Keywords (SQL Syntax) - ANSWER Words with special meaning. ex. SELECT, FROM, WHERE Identifiers (SQL Syntax) - ANSWER Objects from the database like tables, columns, etc. ex. City, Name, Population SQL Sublanguages - ANSWER Data Definition Language (DDL) defines the structure of the database. Data Query Language (DQL) retrieves data from the database. Data Manipulation Language (DML) manipulates data stored in a database. Data Control Language (DCL) controls database user access. Data Transaction Language (DTL) manages database transactions. Cell - ANSWER A single column of a single row. Not Ordered - ANSWER Rows Data Independence - ANSWER Rule 7. Allows database administrators to improve query performance by changing the organization of data on storage devices, without affecting query results. DROP TABLE - ANSWER Statement that deletes a table, along with all the table's rows, from a database. ALTER TABLE - ANSWER Statement that adds, deletes, or modifies columns on an existing table. INT - ANSWER 4 bytes SMALLINT - ANSWER 2 bytes BIGINT - ANSWER 8 bytes TINYINT - ANSWER 1 byte
MEDIUMINT - ANSWER 3 bytes % (Modulo) - ANSWER Divides one numeric value by another and returns the integer remainder ^ - ANSWER Raises one numeric value to the power of another. = - ANSWER Compares two values for equality. != - ANSWER Compares two values for inequality. NULL - ANSWER A special value that represents either unknown or inapplicable data. INSERT Statement (Clauses) - ANSWER INTO clause names the table and columns where data is to be added. The keyword INTO is optional. VALUES clause specifies the column values to be added. INSERT Statement (Syntax) - ANSWER INSERT [INTO] TableName (Column1, Column2, ...)VALUES (Value1, Value2, ...); UPDATE Statement (Clauses) - ANSWER Uses the SET clause to specify the new column values. An optional WHERE clause specifies which rows are updated. Omitting the WHERE clause results in all rows being updated. DELETE Statement (Keywords/Clauses) - ANSWER The FROM keyword is followed by the table name whose rows are to be deleted. An optional WHERE clause specifies which rows should be deleted. Omitting the WHERE clause results in all rows in the table being deleted. TRUNCATE Statement - ANSWER Deletes all rows from a table. Primary Key - ANSWER A _______ ___ is a constraint on a column, or group of columns, used to identify a row. The ________ ____ is usually the table's first column and appears on the left of table diagrams, but the position is not significant to the database. A solid circle (●) notates these in table diagrams. MUST BE "Unique" and "Not NULL" Simple Primary Key - ANSWER A key that consists of a SINGLE column.
Adding and Dropping Constraints - ANSWER Constraints are added and dropped with the ALTER TABLE TableName followed by an ADD, DROP, or CHANGE clause. BETWEEN (Operator) - ANSWER The ________ operator provides an alternative way to determine if a value is between two other values. LIKE (Operator) - ANSWER The _____ operator, when used in a WHERE clause, matches text against a pattern using the two wildcard characters % and _. ORDER BY (Clause) - ANSWER The _____ ___ clause orders selected rows by one or more columns in ascending (alphabetic or increasing) order. The DESC keyword with the ORDER BY clause orders rows in descending order. ABS(n) (Function) - ANSWER Returns the absolute value of n. Distance from zero. Always a positive number or zero. LOWER(s) (Function) - ANSWER Returns the lowercase "s". Or any specified characters. TRIM(s) (Function) - ANSWER Returns the string "s" without leading and trailing spaces HOUR(t) MINUTE(t) SECOND(t) (Function) - ANSWER Returns the hour, minute, or second from time t Aggregate (Function) - ANSWER COUNT() counts the number of rows in the set. MIN() finds the minimum value in the set. MAX() finds the maximum value in the set. SUM() sums all the values in the set. AVG() computes the arithmetic mean of all the values in the set. HAVING (Clause) - ANSWER The HAVING clause is used with the GROUP BY clause to filter group results. JOIN - ANSWER A _____ is a SELECT statement that combines data from two tables, known as the left table and right table, into a single result. The tables
are combined by comparing columns from the left and right tables, usually with the = operator. AS (Alias) - ANSWER A column name can be replaced with an alias. The alias follows the column name, separated by an optional AS keyword. INNER JOIN - ANSWER Selects only matching left and right table rows. FULL JOIN - ANSWER Selects all left and right table rows, regardless of match. LEFT JOIN - ANSWER Selects all left table rows, but only matching right table rows. RIGHT JOIN - ANSWER Selects all right table rows, but only matching left table rows. OUTER JOIN - ANSWER Any join that selects unmatched rows, including left, right, and full joins. UNION (Keyword) - ANSWER The ______ keyword combines the two results into one table. EQUIJOIN - ANSWER An ___________ compares columns of two tables with the = operator. This is the most common type of join. NON-EQUIJOIN - ANSWER A non-equijoin compares columns with an operator other than =, such as < and >. CROSS-JOIN - ANSWER A __________ combines two tables without comparing columns without an ON clause. This is the only JOIN that does not require a condition. SELF-JOIN - ANSWER A ___________ joins a table to itself. Subquery - ANSWER Sometimes called a nested query or inner query, is a query within another SQL query. Materialized View - ANSWER In some databases, view data can be stored. A ________________ ______ is a view for which data is stored at all times. Whenever a base table changes, the corresponding view tables can also change, so this view must be refreshed. WITH CHECK OPTION - ANSWER When ______ ______ __________ is specified, the database rejects inserts and updates that do not satisfy the view query WHERE clause.
Entity Type - ANSWER An ______ _______ is a set of things. Ex: All employees in a company. Relationship Type - ANSWER A _____________ _____ is a set of related things. Attribute Instance - ANSWER An attribute instance is an individual value. Analysis - ANSWER __________ develops an entity-relationship model, capturing data requirements while ignoring implementation details. Logical - ANSWER __________ design converts the entity-relationship model into tables, columns, and keys for a particular database system. Physical - ANSWER __________ design adds indexes and specifies how tables are organized on storage media. Analysis Steps - ANSWER 1. Discover entities, relationships, and attributes
- Determine cardinality
- Distinguish strong and weak entities
- Create supertype and subtype entities Logical Design Steps - ANSWER 1. Implement entities
- Implement relationships
- Implement attributes
- Apply normal form Cardinality - ANSWER Refers to maxima and minima of relationships and attributes. Relationship minimum - ANSWER ______________ ____________ is the least number of instances of one entity that can relate to a single instance of another entity. Subtype - ANSWER A _________ entity is a subset of another entity type, called the supertype entity. IsA relationship - ANSWER A supertype entity identifies its subtype entities. The identifying relationship is called an ____ ________________. Partition - ANSWER A __________ of a supertype entity is a group of mutually exclusive subtype entities.
Database Design - ANSWER After entities, relationships, attributes, cardinality, and strong and weak entities are determined, the database designer looks for supertype and subtype entities. Last Step - ANSWER Creating supertype and subtype entities is the _____ of four analysis steps:
- Discover entities, relationships, and attributes
- Determine cardinality
- Distinguish strong and weak entities
- Create supertype and subtype entities Logical Design - ANSWER Logical design converts an entity-relationship model to tables, columns, and keys for a specific database system. Crow's Foot Notation - ANSWER One popular convention, called Crow's Foot Notation, depicts cardinality as a circle (zero), a short line (one), or three short lines (many). The three short lines look like a bird's foot, hence the name "crow's foot notation". Intangible Entity - ANSWER An __________ ______ is documented in the data model, but not tracked with data in the database. The logical rules and constraints that govern the data but aren't explicitly stored as data themselves. Simple Primary Key - ANSWER _________ _________ _____ values should be easy to type and store and is associated with a SINGLE COLUMN. Small values are easy to specify in an SQL WHERE clause and speed up query processing. Ex: A 2-byte integer is easier to type and faster to process than a 15-byte character string. Artificial Key - ANSWER An __________ _____ is a single-column primary key created by the database designer when no suitable single-column or composite primary key exists. Usually artificial key values are integers, generated automatically by the database as new rows are inserted to the table. Artificial keys are stable, simple, and meaningless. Functional Dependence - ANSWER Dependence of one column on another is called functional dependence.
- 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. Table Scan - ANSWER A _______ _________ is a database operation that reads table blocks directly, without accessing an index. Index Scan - ANSWER An ________ _________ is a database operation that reads index blocks sequentially, in order to locate the needed table blocks. Hit Ratio - ANSWER _____ ______, also called filter factor or selectivity, is the percentage of table rows selected by a query. When a SELECT query is executed Binary Search - ANSWER In a ________ search, the database repeatedly splits the index in two until it finds the entry containing the search value: Dense index - ANSWER A _______ _______ contains an entry for every table row. Sparse index - ANSWER A ________ _______ contains an entry for every table block. Multi-level index - ANSWER A ____________ ________ stores column values and row pointers in a hierarchy. Fan-out - ANSWER The number of index entries per block is called the __________ of a multi-level index. Tablespace - ANSWER A tablespace is a database object that maps one or more tables to a single file. Set - ANSWER A ___ is an UNORDERED collection of elements enclosed in braces. Ex: {a, b, c} and {c, b, a} are the same, since ___ are not ordered. Referential Integrity - ANSWER A fully NULL foreign key is a simple or composite foreign key in which all columns are NULL. ________________ ________ is a relational rule that requires foreign key values are either fully NULL or match some primary key value.
In a relational database, foreign keys must obey ________________ ________ at all times. Occasionally, data entry errors or incomplete data result in violations. Violations must be corrected before data is stored in the database.
