DB and SQL Con Study Guide

In an Organization, who is usually responsible for improving databases to provide efficient and effective access to their information?
Click the card to flip 👆
1 / 89
Terms in this set (89)
In relational algebra, which command retrieves rows from a table based on specified criteria?SelectIn access, if you want a query to list the ProductID, Category, and Description Field values and sort the records by description within each category, how should you arrange the fields from left to right in the query design grid?ProductID, Category, DescriptionWhich of the following criteria selects all records containing a value greater than 250?> 250You can create compound criteria (conditions) in a query by using which of the following two keywords?AND, ORIn general, what should be in place before you select fields from more than one table in Access Query Design View?The tables should be related in a proper one-to-many relationshipsWhich of the following fields would be the best candidate for a primary key field in a table of employee data?EmployeeIDWhat type of Access query creates a new table using the query results?Make-tableWhich of the following is a technique for increasing the speed of Access queries?Delete field lists that are not needed for the queryBased on the table description and SQL shown, which query finds the description and price of the items that have a price field value greater that Wireless charger, ItemNum W208?Select Description, Price From Items Where Price > (Select Price From Items Where ItemNum = 'W208');Which of the following SQL Statements selects ProductDescription and Price fields for every record in the Products table with a Price value Greater than $50.00?Select ProductDescription, Price From Products Where Price > 50;When you place one query inside another, the inner query is called a _______ and is evaluated first.SubqueryHow are compound SQL Criteria Conditions formed? Give an example.When you create a query using UNION. An example would be joining two select commands together.While not always required, how should you end an SQL commandSemicolon (;)What SQL command do you use to make changes to existing data in a table?UpdateWhat SQL command can you use to join two tables?WhereWhich query will return the number of rows from Customers table where the State field is 'AZ'?Select Count(*) From Customers Where State = 'AZ';Which SQL commands do you use to update the values of an existing field?Update and SetIn what type of SQL operation must two tables have the same field structure?UnionWhy would you set an index on a field?To increase the speed of retrieving dataList five Technical competencies common to most database administration positions.SQL, knowledge on database security, understanding of data modeling, mathematical knowledge, analytical skills.What does the Bureau of Labor Statics Project for database administration Jobs?They will grow 11 percent through the year 2026Which Access tool provides details about each table, query, report, form, and other object in a database?Database DocumenterWhat is the term for a general set of skills that involves defining dissecting, communicating, and solving problems using data and information?Data analysisTo specify the CustomerNum field as the primary key for the Customer Table, which clause should you use?Primary Key(CustomerNum)Views are often called a _____ representation of the data in the database.LogicalAlthough Access does not have stored procedures, you can achieve some of the same benefit with ____.Data macrosWhat does the INNER JOIN command select from related tables?Records that have matching values in both tablesWhich of the following SQL commands do you use to change the structure of the Vendors table?Alter Table VendersWhich of the following table events occurs after an existing record has been changed?After UpdateWhich of the following pairs has the clearest field name and atomic value?DistanceInMiles_____ Refers to how attributes(Fields) depend on or relate to other attributes in a relation (Table).Functional dependenceWhich of the following statements describes a table in First Normal Form?It contains no repeating groupsA table with no repeating groups in any attribute and atomic values in each column is, at a minimum, in what normal form?First Normal FormWhat is the difference between a table in First Normal Form and one in Second Normal Form?When each table is in 1NF, and each non key column is dependent on the entire primary key field.Define Third Normal Form and Give an Example of a transitive dependency.Must be in 1NF and 2NF and exclude Transitive Dependencies. Examples is a professor's table has a DepartmentID and a Department Field.A _____ helps constrain the values in a single field to a specific list, which eliminates update anomalies.Lookup tableIn the following Projects table, Which field is functionally dependent on the ClientID field?Projects (ProjectID, ProjectType, ProjectName, ProjectManager, Budget, ClientID)None of the fields in the Projects table is functionally dependent on ClientIDWhen the primary key field consists of a combination of two or more fields, it is called a____.Composite keyWhich of the following tables is most likely a lookup table?Specialties (Specialty)What is a nonkey Column?A column that is not part of the primary keyWhat is the problem with designing a database in which the last name of an employee functionally determines the employee's address?More than one employee could have the same last nameIn Access table Datasheet View, how can you display the records from a related table?Click the Expand button to expand a SubdatasheetWhen you build a relational database for an organization, what is a good starting point for identifying required user views?Collect the organization's forms and reportsProductType is a foreign key field in the Orders table and is constrained to a list of valid entries stored in the ProductTypes table. What Problem does this design prevent?Update anomaliesWhich of the following is a valid reason for creating a one-to-one relationship between two tables?Data needs to be imported from separate sourcesDescribe the different types of keys illustrated in Database Design Language (DBDL) and how they are notated.Primary key, foreign key, composite key, and candidate key.Which of the following is NOT true about one-to-many relationships?The linking field must have the same name in the primary and related tablesHow might you resolve an inappropriate Cartesian join?Connect the two tables through a third junction tableWhich field in a table does Access index by default?Primary key fieldDefine the types of cardinality in a database and explain the cardinality of primary keys and foreign keys.Uniqueness of data values contained in a single field. Sometimes used to refer to the actual relationships between tables.What do you call applying and reconciling updates from various copies of a database with the master copy?SynchronizationIn an enterprise-level DBMS, each task that a user completes, such as selecting a product for an order, is called a _____.TransactionWhich type of data integrity constraint states that the value entered for any field should be consistent with the data type for that field?Data typeWhich Access feature restricts a field to a special format for entering data?Input maskWhat type of constraints help to ensure the accuracy and consistency of field values?Data integrity constrains_____ is the process of returning a database that has encountered a problem to a state known to be correct.RecoveryWhich of the following is NOT a common service provided by a database management system?Format user views, forms, and reportsHow does Access handle concurrent updates?With record-level lockingWhich of the following is Not a common security feature used by database management systems?Backward recoveryWhich Microsoft Access feature do you use to set legal value constraints?Validation Text propertyWhich of the following is NOT a major category of responsibility for typical database administrators?Data-entry managementWhich of the following is NOT one of the top five database certifications?MySQL Certified Database AnalystWhat is the DBA's role about access privileges?The DBA should have all permissions to the database he/she oversees. They are also in charge on handing out permissions to other users.Describe what a data visualization expert does to support an organization's efforts.They use visualization to help explain and show data to their organization.Which of the following is NOT a typical area that a database operations manager focuses on?Database performanceWhat do you call the log of all database maintenance, with information such as dates, license keys, and issues or updates?Run-bookWhich of the following database professionals would focus on how large-scale research produces better products?Data scientistWhich of the following database professionals is most likely to manage indexes?Performance analystWhich of the following is generally NOT considered a source of big data?Sales transactionsIn cloud computing, although many clients share the same resources, a company's systems operate as if they were running on their own dedicated systems because of ____.VirtualizationDiscuss four of Codd's 12 rules for OLAP Systems.Multidimensional Conceptual view, Transparency, Accessibility, Consistent Reporting Performance, Client/Server Architecture, Generic Dimensionality, Dynamic sparse matrix handling, multiuser support, unrestricted cross-dimensional operations, intuitive data manipulation, flexible reporting, and unlimited dimensions and aggregation levelsWhat do you call an arrangement of location data with three levels, such as cities within states within countries?HierarchyIdentify the type of data stored in data warehouses and how organizations use it.Data mining and external storageIn object-oriented technologies, what is a class?An object that functions as a blueprint for more objects___ means that when using a distributed database, users feel that the data they work with comes from a single central database.Fragmentation transparencyWhich of the following is NOT software that is primarily used for data visualization?Adobe DreamweaverWhich of the following is NOT a common data file format that database management systems use to export data?PowerPoint Presentation(PPTX)In _____, Clients perform the presentation functions, a database server performs the database functions, and application servers perform the business functions.Three-tier architectureExplain how new application development projects might select a relational DBMS and given an example of their selection.Figuring out what their needs and what kind of data they would need to save.