Download Database Fundamentals and SQL Concepts and more Exams Deductive Database Systems in PDF only on Docsity!
D426 Objective Assessment (150 Questions
with 100% Correct Answers).
Software that helps business users interact with database systems. - Correct answer Database Application Responsible for securing the database system against unauthorized users. A database administrator enforces procedures for user access and database system availability. - Correct 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. - Correct answer Authorization Database systems ensure data is consistent with structural and business rules. - Correct 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. - Correct 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. - Correct 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. - Correct answer Transaction Manager Data about the database, such as column names and the number of rows in each table. - Correct answer Metadata Stores data in tables, columns, and rows, similar to a spreadsheet. - Correct answer Relational Database
All _________ ________ systems support the SQL query language. - Correct 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. - Correct answer Relational Database The newer non-relational systems are called NoSQL, for 'not only SQL', and are optimized for big data. - Correct 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. - Correct 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. - Correct answer CREATE TABLE (Statement) INT stores integer values. DECIMAL stores fractional numeric values. VARCHAR stores textual values. DATE stores year, month, and day. - Correct answer Data Type Analysis Logical design Physical design - Correct 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. - Correct answer Analysis Phase, Conceptual Design, Entity- Relationship Modeling (Database Design) 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. - Correct answer Logical Design (Database Design)
A ____ _____ is a named set of values, from which column values are drawn. - Correct answer Data Type Selects all rows of two tables. - Correct answer Union Computes functions over multiple table rows, such as sum and count. - Correct answer Aggregate Rules that are logical constraints and ensure data is valid. - Correct answer Relational Rules SQL is the standard language for relational databases, and is commonly supported in non-relational databases. - Correct 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. - Correct answer Literals (SQL Syntax) Words with special meaning. ex. SELECT, FROM, WHERE - Correct answer Keywords (SQL Syntax) Objects from the database like tables, columns, etc. ex. City, Name, Population - Correct 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. - Correct answer SQL Sublanguages A single column of a single row. - Correct answer Cell Rows - Correct 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. - Correct answer Data Independence Statement that deletes a table, along with all the table's rows, from a database. - Correct answer DROP TABLE Statement that adds, deletes, or modifies columns on an existing table. - Correct answer ALTER TABLE 4 bytes - Correct answer INT 2 bytes - Correct answer SMALLINT 8 bytes - Correct answer BIGINT 1 byte - Correct answer TINYINT 3 bytes - Correct answer MEDIUMINT Divides one numeric value by another and returns the integer remainder - Correct answer % (Modulo) Raises one numeric value to the power of another. - Correct answer ^ Compares two values for equality. - Correct answer = Compares two values for inequality. - Correct answer != A special value that represents either unknown or inapplicable data. - Correct 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. - Correct answer INSERT Statement (Clauses) INSERT [INTO] TableName (Column1, Column2, ...)VALUES (Value1, Value2, ...); - Correct answer INSERT Statement (Syntax)
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. - Correct answer Foreign Key ________ rejects an insert, update, or delete that violates referential integrity. - Correct answer RESTRICT Propagates primary key changes to foreign keys. - Correct answer CASCADE A __________ is a rule that governs allowable values in a database based on relational and business rules. - Correct answer Constraint The _______ constraint ensures that values in a column, or group of columns, are NEVER REPEATED. - Correct answer UNIQUE (Constraint) The _______ constraint specifies an expression on one or more columns of a table. - Correct answer CHECK (Constraint) Constraints are added and dropped with the ALTER TABLE TableName followed by an ADD, DROP, or CHANGE clause. - Correct answer Adding and Dropping Constraints The ________ operator provides an alternative way to determine if a value is between two other values. - Correct answer BETWEEN (Operator) The _____ operator, when used in a WHERE clause, matches text against a pattern using the two wildcard characters % and _. - Correct 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. - Correct answer ORDER BY (Clause) Returns the absolute value of n. Distance from zero. Always a positive number or zero. - Correct answer ABS(n) (Function) Returns the lowercase "s". Or any specified characters. - Correct answer LOWER(s) (Function) Returns the string "s" without leading and trailing spaces - Correct answer TRIM(s) (Function) Returns the hour, minute, or second from time t - Correct 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. - Correct answer Aggregate (Function) The HAVING clause is used with the GROUP BY clause to filter group results. - Correct 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. - Correct answer JOIN A column name can be replaced with an alias. The alias follows the column name, separated by an optional AS keyword. - Correct answer AS (Alias) Selects only matching left and right table rows. - Correct answer INNER JOIN Selects all left and right table rows, regardless of match. - Correct answer FULL JOIN
distinguished with special notation, such as a dashed rectangle or distinct color. - Correct answer Intangible Entity Weak Entities - Correct answer Dependent Entities A group of related entities. - Correct answer Subject Area Commonly used for software development. Software data structures are similar to database structures - Correct answer Unified Modeling Language (UML) Each entity becomes a table and each attribute becomes a column. Tables and columns are revised in subsequent steps. - Correct 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. - Correct 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. - Correct 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. - Correct answer First Normal Form An ______ __________________ ________ is a high-level representation of data requirements, ignoring implementation details. - Correct answer Entity-Relationship Model A person, place, product, concept, or activity. - Correct answer Entity A statement about two entities. - Correct answer Relationship A descriptive property of an entity. - Correct answer Attribute
A _________ ____________ relates an entity to itself. - Correct answer Reflexive Relationship An _____________ ______________, commonly called an ER diagram, is a schematic picture of entities, relationships, and attributes. Entities are drawn as rectangles. - Correct answer Entity-Relationship Diagram An ______ _______ is a set of things. Ex: All employees in a company. - Correct answer Entity Type A _____________ _____ is a set of related things. - Correct answer Relationship Type An attribute instance is an individual value. - Correct answer Attribute Instance __________ develops an entity-relationship model, capturing data requirements while ignoring implementation details. - Correct answer Analysis __________ design converts the entity-relationship model into tables, columns, and keys for a particular database system. - Correct answer Logical __________ design adds indexes and specifies how tables are organized on storage media. - Correct answer Physical
- Discover entities, relationships, and attributes
- Determine cardinality
- Distinguish strong and weak entities
- Create supertype and subtype entities - Correct answer Analysis Steps
- Implement entities
- Implement relationships
- Implement attributes
- Apply normal form - Correct answer Logical Design Steps Refers to maxima and minima of relationships and attributes. - Correct answer Cardinality
integer is easier to type and faster to process than a 15-byte character string. - Correct answer Simple Primary Key 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. - Correct answer Artificial Key Dependence of one column on another is called functional dependence. - Correct answer Functional Dependence ________________ is the repetition of related values in a table. - Correct answer Redundancy ________ ______ are rules for designing tables with less redundancy. - Correct 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 ________. - Correct answer Trivial Redundancy is eliminated with _______________, the last step of logical design. - Correct 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. - Correct answer Normalization _______________________ ___________ _______ is ideal for tables with frequent inserts, updates, and deletes. - Correct answer Boyce-Codd normal form ____________________ means intentionally introducing redundancy by merging tables. - Correct answer Denormalization Heap table
Sorted table Hash table Table cluster - Correct answer Databases commonly support four alternative table structures: In a _______ _______, no order is imposed on rows. - Correct answer Heap Table ________ ________ optimize insert operations. Heap tables are particularly fast for bulk load of many rows, since rows are stored in load order. - Correct answer Heap Tables In a ______ _______, rows are assigned to buckets. A bucket is a block or group of blocks containing rows. - Correct 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. - Correct answer Modulo A _______ _________ is a database operation that reads table blocks directly, without accessing an index. - Correct answer Table Scan An ________ _________ is a database operation that reads index blocks sequentially, in order to locate the needed table blocks. - Correct 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 - Correct answer Hit Ratio In a ________ search, the database repeatedly splits the index in two until it finds the entry containing the search value: - Correct answer Binary Search A _______ _______ contains an entry for every table row. - Correct answer Dense index
