GISP 7: Database Design & Management 100% Pass

GISP 7: Database Design & Management 100% Pass What is a SCHEMA (in context of database object relationships)? - structure or design of the database or database object (e.g. table, view, index, stored, procedure, trigger) - defines the tables, fields in each table, relationships between fields - will include information on which fields have domains and what those domains are What is a DBA? Database Administrator What is the definition of a Data Owner? - the user who creates tables, feature classes owns those datasets. What is Database design? - process of producing a detailed data model of a database What is the role of user access in database security? - database must verify user accounts that connect to it. What are the three main steps to the Database Design process? -Conceptual schema. -Logical Data Model -Physical database design What is a DATA DICTIONARY (in context of database object relationships)? - catalog or table containing information about datasets stored in a database What is a DOMAIN (in context of database object relationships)? - the range of values for a particular metadata element What is AUTHENTICATION in the context of database security? - database checks the list of users to make sure a user is allowed to make a connection Describe the CONCEPTUAL SCHEMA step of the Database Design process - Determine where relationships and dependency is within the data. What is a Database Administrator the person responsible for coordinating, controlling, and managing the database Describe the LOGICAL DATA MODEL step of the Database Design process - Arrange data in a logical structure that can be mapped into the storage objects supported by the database management system What are the responsibilities of a Database Administrator? -backup and data recovery -capacity planning -configuration -database design -installation -migration -performance monitoring -security -troubleshooting -high availability and ETL functions What is the PUBLIC ROLE in the context of database management? - right granted to anyone connected to database What is an ATTRIBUTE DOMAIN (in context of database object relationships)? - enforces data integrity -identify what values are allowed in a field in a feature class What are the most common types of User Access in database management? -Administrator -Editor -Reader -Creator Describe the PHYSICAL DATABASE DESIGN step of the Database Design process -Physical configuration of database on storage media *Detailed specification of data elements, data types, indexing options, and other parameters residing in DBMS data dictionary *Modules, hardware, software What does ETL mean? -extract, transform, load -three functions combined into one tool to pull data out of one database and place it into another database What is a CODED VALUE DOMAIN (in context of database object relationships)? - attribute domain that defines a set of permissible values for an attribute in a geodatabase - it has a code and its equivalent value What is a RANGE DOMAIN (in context of database object relationships)? - type of attribute domain that defines the range of permissible values for a numeric attribute What is an ADMINISTRATOR in the context of database user access? -full control of database -can read, create, update, delete features, -can create and delete feature classes, tables Why are Field Types important to Database Design? - proper field type will secure data and make databases more efficient What is RETRIEVAL in the context of database administration? - extracting data from a backup due to data loss or data corruption What is a SPATIAL DOMAIN (in context of database object relationships)? - allowable range for x, y coordinates and for m, z values What is an EDITOR in the context of database user access? - can read, update, create, and delete features What is the SHORT INTEGER field type? Integer between -32,768 and 32,768 What is GIS Feature Data Archiving? -record and access changes made to all or a subset of data in a versioned geodatabase What is the LONG INTEGER field type? Integer between -2,147,483,648 and 2,147,483,647 What are TABLES (in context of database object relationships)? - collection of related data held in structured format within a database -contains fields and rows What is an READER in the context of database user access? - can only view data What are VIEWS (in context of database object relationships)? - result set of a stored query on the data - users can query - virtual table computed dynamically from data when view is accessed What is an CREATOR in the context of database user access? - can create additional feature classes, tables, as well as read, update, create, and delete features What is the FLOAT field type? - single-precision floating-point numbers What are SEQUENCES (in context of database object relationships)? - ordered collection of objects in which repetitions are allowed (finite or infinite) -number of elements is the length of the sequence What are two ways to achieve AUTHENTICATION in the context of database security? -Operating System (OS) authentication -Database Authentication What is the DOUBLE field type? - double-precision floating-point numbers (more sig figs) What are SYNONYMS (in context of database object relationships)? - Alias or alternate name for a table, view, sequence or other object What are GROUPS in the context of database management? - users can be granted access based on their common functions. What is the TEXT field type? - could be a coded value - assign to an integer through a domain What are INDEXES (in context of database object relationships)? - data structure that improves speed of data retrieval operations in a database table *Causes more storage space and additional writes *Quickly locate data in the database *Indexes can be on multiple columns What is the DATES field type? - a calendar date and sometimes a time is associated What are CLUSTERS (in context of database object relationships)? Can be either: -Multiple servers share one storage *typically used to handle user load balancing -Databases distributed to different servers using replication *typically used when multiple users utilizing same data in different physical locations * there is a master database that replica databases sync between What is the BLOBs field type? -Binary Large OBjects -collection of binary data stored as single entity in DBMS - Typically images, multimedia, bits of code - ArcGIS stores annotation and dimensions as BLOBs What are DATABASE LINKS (in context of database object relationships)? - data stored in a different database but accessible by to the database currently being accessed What is the OBJECT IDENTIFIERS field type? - Unique IDs and FIDs What is a SNAPSHOT (in context of database object relationships)? - state of a system at a particular point in time - can be a backup What is the GLOBAL IDENTIFIERS field type? - Global ID or Globally Unique Identifier (GUID) - stores registry style strings consisting of 36 characters enclosed in curly brackets What is a PROCEDURE (in context of database object relationships)? - subroutine available to applications that access a relational database system (data validation, access control mechanisms) What is the RASTER field type? - raster can be stored within the geodatabase What is a TRIGGER (in context of database object relationships)? - procedural code automatically executed in response to certain events on a particular table or view in a database What is the GEOMETRY field type? - point, line, polygon, multipoint, multipatch What are FUNCTIONS (aka SUBROUTINES) (in context of database object relationships)? - sequence of program instructions that perform a specific task What is a PACKAGE (in context of database object relationships)? - built from source with one of the available package management systems What are NON-SCHEMA FUNCTIONS (in context of database object relationships)? - users, roles, contexts, directory objects