Download Database Systems: Concepts, Design, and SQL and more Exams Computer Security in PDF only on Docsity!
D426 ASSESSMENT QUESTIONS AND
ANSWERS PROVIDED
Software that helps business users interact with database systems. - ANSWER Database Application Responsible for securing the database system against unauthorized users. A database administrator enforces procedures for user access and database system availability. - ANSWER Database Administrator 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. - ANSWER Authorization Database systems ensure data is consistent with structural and business rules. - ANSWER Rules 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. - ANSWER Query Processor 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. - ANSWER Storage Manager 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. - ANSWER Transaction Manager Data about the database, such as column names and the number of rows in each table. - ANSWER Metadata
Stores data in tables, columns, and rows, similar to a spreadsheet. - ANSWER Relational Database All _________ ________ systems support the SQL query language. - ANSWER Relational Database Relational systems are ideal for databases that require an accurate record of every transaction, such as banking, airline reservation systems, and student records. - ANSWER Relational Database The newer non-relational systems are called NoSQL, for 'not only SQL', and are optimized for big data. - ANSWER MongoDB (NoSQL) INSERT inserts rows into a table. SELECT retrieves data from a table. UPDATE modifies data in a table. DELETE deletes rows from a table. - ANSWER SQL Statements 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. - ANSWER CREATE TABLE (Statement) INT stores integer values. DECIMAL stores fractional numeric values. VARCHAR stores textual values. DATE stores year, month, and day. - ANSWER Data Type Analysis Logical design Physical design - ANSWER Database Design 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. - ANSWER Analysis Phase, Conceptual Design, Entity-Relationship Modeling (Database Design)
Is an unnamed tuple of values. Each value corresponds to a column and belongs to the column's data type. - ANSWER Row A ____ _____ is a named set of values, from which column values are drawn. - ANSWER Data Type Selects all rows of two tables. - ANSWER Union Computes functions over multiple table rows, such as sum and count. - ANSWER Aggregate Rules that are logical constraints and ensure data is valid. - ANSWER Relational Rules SQL is the standard language for relational databases, and is commonly supported in non-relational databases. - ANSWER The standard language for Relational Databases... 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. - ANSWER Literals (SQL Syntax) Words with special meaning. ex. SELECT, FROM, WHERE - ANSWER Keywords (SQL Syntax) Objects from the database like tables, columns, etc. ex. City, Name, Population - ANSWER Identifiers (SQL Syntax) 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. - ANSWER SQL Sublanguages A single column of a single row. - ANSWER Cell
Rows - ANSWER Not Ordered Rule 7. Allows database administrators to improve query performance by changing the organization of data on storage devices, without affecting query results. - ANSWER Data Independence Statement that deletes a table, along with all the table's rows, from a database. - ANSWER DROP TABLE Statement that adds, deletes, or modifies columns on an existing table. - ANSWER ALTER TABLE 4 bytes - ANSWER INT 2 bytes - ANSWER SMALLINT 8 bytes - ANSWER BIGINT 1 byte - ANSWER TINYINT 3 bytes - ANSWER MEDIUMINT Divides one numeric value by another and returns the integer remainder - ANSWER % (Modulo) Raises one numeric value to the power of another. - ANSWER ^ Compares two values for equality. - ANSWER = Compares two values for inequality. - ANSWER != A special value that represents either unknown or inapplicable data. - ANSWER NULL 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. - ANSWER INSERT Statement (Clauses)
Is a column, or group of columns, that refer to a primary key. An empty circle (○) represents foreign keys in table diagrams, and an arrow leads to the referenced primary key. When a ________ _____ constraint is specified, the database rejects insert, update, and delete statements that violate referential integrity. Referential integrity requires ________ ____ values must either be NULL or match some value of the referenced primary key. - ANSWER Foreign Key ________ rejects an insert, update, or delete that violates referential integrity. - ANSWER RESTRICT Propagates primary key changes to foreign keys. - ANSWER CASCADE A __________ is a rule that governs allowable values in a database based on relational and business rules. - ANSWER Constraint The _______ constraint ensures that values in a column, or group of columns, are NEVER REPEATED. - ANSWER UNIQUE (Constraint) The _______ constraint specifies an expression on one or more columns of a table. - ANSWER CHECK (Constraint) Constraints are added and dropped with the ALTER TABLE TableName followed by an ADD, DROP, or CHANGE clause. - ANSWER Adding and Dropping Constraints The ________ operator provides an alternative way to determine if a value is between two other values. - ANSWER BETWEEN (Operator) The _____ operator, when used in a WHERE clause, matches text against a pattern using the two wildcard characters % and _. - ANSWER LIKE (Operator) 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. - ANSWER ORDER BY (Clause)
Returns the absolute value of n. Distance from zero. Always a positive number or zero. - ANSWER ABS(n) (Function) Returns the lowercase "s". Or any specified characters. - ANSWER LOWER(s) (Function) Returns the string "s" without leading and trailing spaces - ANSWER TRIM(s) (Function) Returns the hour, minute, or second from time t - ANSWER HOUR(t) MINUTE(t) SECOND(t) (Function) 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. - ANSWER Aggregate (Function) The HAVING clause is used with the GROUP BY clause to filter group results. - ANSWER HAVING (Clause) 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. - ANSWER JOIN A column name can be replaced with an alias. The alias follows the column name, separated by an optional AS keyword. - ANSWER AS (Alias) Selects only matching left and right table rows. - ANSWER INNER JOIN Selects all left and right table rows, regardless of match. - ANSWER FULL JOIN Selects all left table rows, but only matching right table rows. - ANSWER LEFT JOIN
A group of related entities. - ANSWER Subject Area Commonly used for software development. Software data structures are similar to database structures - ANSWER Unified Modeling Language (UML) Each entity becomes a table and each attribute becomes a column. Tables and columns are revised in subsequent steps. - ANSWER Logical Design Phase (First Step) Informally, a table is in ______ ________ ______ when all non-key columns depend on the key, the whole key, and nothing but the key. - ANSWER Third Normal Form A table is in _________ ________ ______ when all non-key columns depend on the whole primary key. In other words, a non-key column cannot depend on part of a composite primary key. A table with a simple primary key is in this form. - ANSWER Second Normal Form Every non-key column depends on the primary key. Every primary key value appears in exactly one row, and each non-key cell contains exactly one value. Each primary key is related to exactly one non-key value. - ANSWER First Normal Form An ______ __________________ ________ is a high-level representation of data requirements, ignoring implementation details. - ANSWER Entity- Relationship Model A person, place, product, concept, or activity. - ANSWER Entity A statement about two entities. - ANSWER Relationship A descriptive property of an entity. - ANSWER Attribute A _________ ____________ relates an entity to itself. - ANSWER Reflexive Relationship
An _____________ ______________, commonly called an ER diagram, is a schematic picture of entities, relationships, and attributes. Entities are drawn as rectangles. - ANSWER Entity-Relationship Diagram An ______ _______ is a set of things. Ex: All employees in a company. - ANSWER Entity Type A _____________ _____ is a set of related things. - ANSWER Relationship Type An attribute instance is an individual value. - ANSWER Attribute Instance __________ develops an entity-relationship model, capturing data requirements while ignoring implementation details. - ANSWER Analysis __________ design converts the entity-relationship model into tables, columns, and keys for a particular database system. - ANSWER Logical __________ design adds indexes and specifies how tables are organized on storage media. - ANSWER Physical
- Discover entities, relationships, and attributes
- Determine cardinality
- Distinguish strong and weak entities
- Create supertype and subtype entities - ANSWER Analysis Steps
- Implement entities
- Implement relationships
- Implement attributes
- Apply normal form - ANSWER Logical Design Steps Refers to maxima and minima of relationships and attributes. - ANSWER Cardinality ______________ ____________ is the least number of instances of one entity that can relate to a single instance of another entity. - ANSWER Relationship minimum A _________ entity is a subset of another entity type, called the supertype entity. - ANSWER Subtype
automatically by the database as new rows are inserted to the table. Artificial keys are stable, simple, and meaningless. - ANSWER Artificial Key Dependence of one column on another is called functional dependence. - ANSWER Functional Dependence ________________ is the repetition of related values in a table. - ANSWER Redundancy ________ ______ are rules for designing tables with less redundancy. - ANSWER Normal forms When the columns of A are a subset of the columns of B, A always depends on B. Ex: FareClass depends on (FlightCode, FareClass). These dependencies are called ________. - ANSWER Trivial Redundancy is eliminated with _______________, the last step of logical design. - ANSWER Normalization _______________ eliminates redundancy by decomposing a table into two or more tables in higher normal form. Ex: A table in first normal form might be replaced by two tables in third normal form. In principle, normalization decomposes tables to any higher normal form. - ANSWER Normalization _______________________ ___________ _______ is ideal for tables with frequent inserts, updates, and deletes. - ANSWER Boyce-Codd normal form ____________________ means intentionally introducing redundancy by merging tables. - ANSWER Denormalization Heap table Sorted table Hash table Table cluster - ANSWER Databases commonly support four alternative table structures: In a _______ _______, no order is imposed on rows. - ANSWER Heap Table
________ ________ optimize insert operations. Heap tables are particularly fast for bulk load of many rows, since rows are stored in load order. - ANSWER Heap Tables In a ______ _______, rows are assigned to buckets. A bucket is a block or group of blocks containing rows. - ANSWER Hash Table The ___________ function is a simple hash function with four steps:
- 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. - ANSWER Modulo A _______ _________ is a database operation that reads table blocks directly, without accessing an index. - ANSWER Table Scan An ________ _________ is a database operation that reads index blocks sequentially, in order to locate the needed table blocks. - ANSWER Index Scan _____ ______, also called filter factor or selectivity, is the percentage of table rows selected by a query. When a SELECT query is executed - ANSWER Hit Ratio In a ________ search, the database repeatedly splits the index in two until it finds the entry containing the search value: - ANSWER Binary Search A _______ _______ contains an entry for every table row. - ANSWER Dense index A ________ _______ contains an entry for every table block. - ANSWER Sparse index A ____________ ________ stores column values and row pointers in a hierarchy. - ANSWER Multi-level index
