CNIT 272 Final Exam Questions and Answers Rated A

CNIT 272 Final Exam Questions and Answers Rated A A Primary Key having the fewest number of attributes to make it unique is called. Minimization If the child entity requires the primary key of the parent entity for its unique identity, then you create ______. Identifying Relationship If each child record has a unique identifier of its own, then the relationship is know as a _______. Non-Identifying Relationship Entities that have no foreign keys in their pk. Independent Entity Entities that have foreign keys in their pk. Dependent Entity An entity that is created as the result of a many-to-many relationship. Associative Entity Abnormalities or problems introduced by insert, delete, and update (change) actions. Update Anomalies An auxiliary file to improve performance. Index Previous normal forms (If there are any) and no recurring attribute values are allowed in an entity (each attribute has one value per instance) First Normal Form An entity that is in any previous normal forms and every non-primary key attribute is fully functionally dependent on the full primary key. EACH non-key attribute carries a fact about the entire key of the entity in which it resides. Second Normal Form In 2NF and EACH non-key attribute depends on the key (the whole key) of that entity and... NOT on any non-key attribute in the same entity. NOT on any attribute in another entity. These dependency issues are called Transitive Dependencies Third Normal Form A requirement that a non-key requires the pk. Functional Dependence A requirement that the pk determines the non-key attributes. Determinant Representative of all possible valid values. (Datatype, size, range, default value, etc.) Domain Enforces parent-child existence constraints, table relationships must always be consistent. Referential Integrity Converts the character c in VARCHAR2 datatype to a date datatype (internal numeric) in the form as specified by dfmt. (Processing or insertion) To-Date(c, dfmt) Returns the current date. SYSDATE Returns the full date and time. SYSTIMESTAMP The table to refer to when you arnt using a real table. (Usually used for USERS or DATES) Dual Converts the date c to a value of VARCHAR2 datatype in the format specficied by dfmt. (Used to externally display a date) To-Char(c, dfmt) Substitute a value 'sub' if value in 'col' is null. NVL(col, sub) Inner join records plus any primary key (parent) values that do not have matching foreign key (child) values. Left Join Inner join records plus any foreign key null values that do not have matching primary key values from the parent table. Right Join Returns the values that are the same from both tables. INTERSECT Returns the all values from both tables except for duplicates. UNION Returns values from the first table but discluding any values that were also in the second table. MINUS Gives the ability to create users, roles, object and grant/revoke priveleges. DCL (Data Control Language) Creates and alters structure. CREATE TABLE tablename (column1 datatype(size),) ALTER TABLE tablename ADD CONSTRAINT/DROP COLUMN/MODIFY/ADD DROP TABLE tablename CASCADE CONSTRAINTS; DDL (Data Definition Language) Loads, changes, deletes, queries data INSERT INTO table (col1) VALUES (value1)/SELECT...; UPDATE table SET col = val WHERE condition; DELETE FROM table WHERE col = val; DML (Data Manipulation Language) Extract, Transform, Load Steps for Data Preparation Process for Data Warehousing Data driven; discovery, such as trends. Explanatory: Explain past events, Confirmatory: Confirm a hypothesis, Exploratory: Look for trends/unexpected relationships. Data Mining Usually refers collectively to OLAP and Data Mining Business Intelligence Technologies Represent perspectives Scope (Contextual for the planner), Business Model (Conceptual for the owner), System Model (Logical for the designer), Technology Model (Physical for the builder), Detailed Representations (Out of context for the sub-contractor), Functioning Enterprise (Real for the users) Zachman Framework Rows Represent aspects of the enterprise What (data), How (function), Where (network), Who (people), When (time), Why (motivation) Zachman Framework Columns GUI Interface: Input and output Presentation Logic Procedures, functions, programs: business rules, data management, stored procedures Processing Logic DBMS activities: data storage and retrieval Storage Logic Major components to be mapped in the architecture Presentation Logic, Processing Logic, Storage Logic truly distributes rows and columns of tables to specific database servers with little or no duplication between servers. Data Partitioning duplicates some or all tables on more than one database server Data Replication Explanatory. Explain past events. Ex: Explain why sales of pickup trucks have increased in Colorado. Confirmatory. Confirm a hypothesis. Ex: Two-income families are more likely to buy family medical insurance than one-income families. Exploratory. Look for trends or unexpected relationships. Ex: what spending patterns are likely to accompany credit card fraud. Three Goals of Data Mining Transaction processing Most systems are developed for and focus on... False T/F: Data is integrated DATA is important Databases are important because... INFORMATION is important Data is important because... Something about which we keep data A grouping concept Abstract Becomes a "table" in relational database management systems (RDBMS) Like a "class" in object analysis/programming Entity One occurrence of an entity Not abstract (specific instance of an entity) Uniquely identified with a "primary key" Becomes a "row" or a "record" in an RDBMS table Object An "object" in object analysis/programming Instance DROP TABLE tablename; -or- DROP TABLE tablename CASCADE CONSTRAINTS; Syntax to drop a table ALTER TABLE tablename DROP COLUMN column_name; Delete a column from a table DELETE FROM tablename WHERE column_name = value; Delete rows from a table describes how many records with the same key value can appear in the child entity for each unique value that is in the parent entity. Cardinality -Combine values of columns and literal values into one output column -Symbol in Oracle is || -All concatenated columns should be given an alias (renamed) with the AS clause (Example: SELECT FName || ' ' || LName AS customer, FROM customer) Concatenated Columns a technique for producing a relational database design with desirable properties Normalization -Returns n truncated to precision decimal places if precision positive. -If precision negative, it makes 0 precision places to the left of the decimal point. -If precision omitted, 0 places are returned. Trunc (n, precision) Returns the smallest integer greater than or equal to n. Ceil (n) Returns the largest integer less than or equal to n. Floor (n) The number of rows satisfying the where clause Count Returns a portion of str starting at n, m characters long. If m omitted, returns all characters to the end of str. Substr (str, n, m) Makes permanent ALL Inserts, Updates, Deletes which have occurred since the last COMMIT or ROLLBACK command or since the session began. Commit Reverses ALL changes (inserts, updates, deletes) made to tables in the database since the last COMMIT or ROLLBACK, and releases any locks on the tables Rollback ALTER TABLE<table_name> DROP CONSTRAINT <constraint_name> Delete constraint ALTER TABLE TableName ADD CONSTRAINT ConstraintName PRIMARY KEY (Column1); Syntax to specify a primary key (Separate from CREATE table) ALTER TABLE table_name ADD CONSTRAINT constraint_name FOREIGN KEY (column_name[s] ) REFERENCES table_name; Add referential integrity INSERT INTO tablename (col1, col2, col3) VALUES (value1, value2, value3); What's the SQL statement for adding new rows to a table? UPDATE tablename SET columnname1 = value, columnname2 = value WHERE condition; Update existing rows -Returns n rounded to precision places to the right of the decimal point (if precision positive) -Precision places to the left of the decimal point (if precision negative) -If precision omitted, 0 places are returned. Round (n, precision) Transactional Database: Tracks operational transactions Short-term decisions: fulfill orders, resolve complaints, tracks individual customers Decision support processing: Uses integrated, summarized, aggregated, historical data Medium and long-term decisions: capacity planning, store locations, product performance, new lines of business Types of enterprise databases User driven analysis (user knows what he is looking for) OnLine analytical processing Used to support decision support processing OLAP Online Transaction Processing Used to support transactional databases OLTP True T/F: In nested subqueries the query on the inside is ran first