Databases
Segments
-A set of extents allocated for a user object eg, a table,or index. -Undo data or temporary data.
Recursive foreign key
A foreign key in a relation that references the primary key values of the same relation
Dialogue diagramming
A formal method for designing and representing human-computer dialogues using box and line diagrams
Transitive dependency
A functional dependency between the primary key and one or more non-key attributes that are dependent on the primary key via another non-key attribute
Entity-relationship model (E-R model)
A logical representation of the data for an organization or for a business area, using entities for categories of data and relationships for associations between entities.
Agile Development
An alternative to traditional project management where emphasis is placed on empowering people to collaborate and make team decisions in addition to continuous planning, continuous testing and continuous integration.
_____ is a web application server that provides the ability to connect web servers to multiple data sources
ColdFusion/JRun by Adobe WebSphere Application Server by IBM, WebLogic Server by Oracle Fusion by NetObjects Visual Studio .NET by Microsoft WebObjects by Apple
The Oracle _____ function compares an attribute or expression with a series of values and returns an associated value or a default value if no match is found
DECODE
n-tier Software Architectures
First two tiers are Client and Server. Sometimes an application server/web server/middle tier which runs apps and stores business rules (making for a total of 2 servers - application server and client server, as well as client)
Composite Key
a key that consists of two or more columns
Candidate Key
a key that determines all of the other columns in a relation
Selecting database management system (DBMS) software is part of the _____ phase of the Database Life Cycle (DBLC)
database design
In the context of data backup and recovery, the activities of a _____ includes the DBA operations that are classified as disaster management
database security officer - DSO
A _____ lock prevents the use of any tables in the database from one transaction while another transaction is being processed
database-level lock (chapter 10 - 467)
Schema descriptions and XML Namespaces
defines the set of commands that can be used
The decision to store _____ attributes in database tables depends on the processing requirements and the constraints placed on a particular application
derived
External/View Level External Schema/User Views
describes the part of the database that a particular user group views schema is typically implemented using a representational data model
Attribute A _____ attribute B if all of the rows in the table that agree in value for attribute A also agree in value for attribute B
determines
Client Server
device from the computer on which the database resides
From a data analyst's point of view, decision support data differ from operational data in three main areas: time span, granularity, and _____
dimensionality
The basic star schema has four components: facts, _____, attributes, and attribute hierarchies
dimensions
functionality not improved
disadvantage of distributed presentation
-large storage requirements -complexity and cost of updating
disadvantages of data replication
-software costs and complexity -data integrity -slow response if not properly distributed or queries not formulated properly
disadvantages of distributed databases
-inconsistent access speed -backup vulnerability
disadvantages of horizontal and vertical partitioning
In the context of total completeness, in a(n) _____, every supertype occurrence is a member of only one subtype
disjoint constraint
Schema Diagram
displayed schema displays the structure of each record type but not the actual instances of the records displays only some aspects of the schema: names of record types, data items, & constraints
In the context of the database design process, the conceptual design step that determines end-user views, outputs, and transaction-processing requirements is _____
distributed database design
In the ODBC architecture, a(n) _____ is in charge of managing all database connections
driver manager
t/f? minimum cardinality influences relational schemas
false.
Interactive Query
interface that allows casual users with occasional need for information from the database to interact with the database
Large pool
is an optional memory area in the SGA. It is intended for memory allocations that are larger than is appropriate to store in the shared pool. Eg, UGA and ORacle XVA andd buffers for RMAN I/O slaves.
The reliance on _____ as the design methodology for relational databases is seen as a stumbling block to its use in OLAP systems
normalization
A single-user database system automatically ensures _____ of the database, because only one transaction is executed at a time
serializability and isolation
cloud
set of hardware, networks, storage, services and interfaces that combine to deliver aspects of computing as a service
A(n) _____ phase in a two-phase lock is when a transaction releases all locks and cannot obtain any new lock
shrinking (chapter 10 - 471)
encryption and authentication requirements
state what encryption and authentication requirements are needed for what data
access control requirements
state who can access what data and what type of access is permitted - whether the individual can create, read, update and/or delete the data
Key-value stores
stores key value pairs, fast lookup key -> value massive scalability good for simple associative data and big data bad for complex highly relational data ex. redis
Graphed Data Model
stores objects as graph nodes and relationships among objects as directed graph edges
Functions of the database
stores the user data, the database metadata, indexes, triggers, stored procedures and application metadata.
If an entity can exist apart from all of its related entities, then it is existence-independent, and it is referred to as a(n) _____ entity
strong
A(n) _____ is the attribute in the supertype entity that determines to which entity subtype each supertype occurrence is related
subtype discriminator
SDLC
systems design life cycle: planing > analysis > design > implementation > maintenance > (repeat)
ANSI/SPARC
the American National Standards Institute, Standards Planning And Requirements Committee, is an abstract design standard for a Database Management System (DBMS), first proposed in 1975.
An enterprise-Class database system has four components
the user, the database application, the database management system (DBMS) and the database.
entity vs. enterprise data model
these two are very different: entity - a noun in the user environment about which the organization wishes to maintain data vs. enterprise data model - The first step in database development, in which the scope and general contents of organizational databases are specified.
The ANSI standard defines _____ type(s) of outer join(s)
three
When you define a table's primary key, the DBMS automatically creates a(n) _____ index on the primary key column(s) you declared
unique
Multifactor authentication (part of access controls)
using multiple authentication methods for increased reliability
Partial dependency
values in a non-key column are functionally dependent on a part of the composite primary key
What are Daemon/ Application Processes
- Networking listeners - Grid Infrastructure daemons
server
-a computer program or hardware that provides services to other computer programs in the same or other computers -responds to queries with requested data -hides server system from client and from end user
A table that is in 1NF and includes no partial dependencies is said to be in _____
2NF
Before converting a table into 3NF, it is imperative that the table already be in
2NF
Data warehouse routinely uses _____ structures in its complex, multilevel, multisource data environment
2NF
From a structural point of view, 3NF is better than _____
2NF
For most business transactional databases, we should normalize relations into _____
3NF
Some very specialized applications may require normalization beyond the
4NF
Entity intergrity
?
Intangible cost
A cost associated with an information system that cannot be easily measured in terms of dollars or with certainty.
Supply Chain
The entire network of business entities, directly or indirectly interlinked and interdependent in serving the same consumer or customer.
Identifying owner
The entity type on which the week entity type depends.
DFD consistency
The extent to which information contained on one level of a set of nested DFDs is also included on other levels
Attribute inheritance
A property by which subtype entities inherit values of all attributes and instances of all relationships of their supertype.
Recovery Logs
A record of transactions made to the Database and the state of the database at that time
Ternary relationship
A simultaneous relationship among instances of three entity types.
Entity instance
A single occurrence of an entity type
ERP systems
A system that integrates individual traditional business functions into a series of modules so that a single transaction occurs seamlessly within a single information system, rather than several separate systems.
Stub testing
A technique used in testing modules, especially modules that are written and tested in a top-down fashion, where a few lines of code are used to substitute for subordinate modules.
To generate database object statistics manually, following syntax should be used in Oracle
ANALYZE object_name COMPUTE STATISTICS; (chapter 11 - 492)
A(n) _____ is an alternate name given to a column or table in any SQL statement
Alias
What is a disadvantage of using an API web-server interface?
An API error can bring down the entire web server
Weak Entity
An Entity which doesn't have sufficient attributes to uniquely identify it, i.e. it doesn't have a Primary Key. It can be considered primary with the key of another entity.
Online Transaction Processing (OLTP)
An application which enforces concurrency control.
Deliverable
An end product of an SDLC phase.
2 NF
Be in 1NF Have no partial functional dependencies most databases ar 2nf
Functional Decomposition
Breaking high-level abstract information into smaller units for detailed planning.
System testing
Bringing together all of the programs that a system comprises for testing purposes for testing purposes. Programs are typically integrated in a top-down, incremental fashion.
Some RDBMSs, such as Oracle, automatically _____ data changes when issuing data definition commands
COMMIT
_____ expresses the minimum and maximum number of entity occurrences associated with one occurrence of the related entity
Cardinality
Perfective maintenance
Changes made to a system to add new features or to improve performance.
Preventive Maintenance
Changes made to a system to avoid possible future problems.
Union R ∪ S
Combines both inputs and eliminates duplicates. Both sets must be Union Compatible.
Natural/ business key
Come from real world (SSN)
Which of the following is a well-defined web-server interface?
Common Gateway Interface
Deadlock Detection
Create a Wait-For-Graph showing the dependencies, it can choose a victim preferably younger / less updates
Database administration operations are commonly defined and divided according to the phases of the _____
DBLC - Database Life Cycle
The _____ function returns the current system date in MS Access
Date ()
Weak Entity
Dependent on a strong entity (identifying owner), meaning it cannot exist on its own Does not have a unique identifier (only a partial identifier, underlined with a dashed line) Entity rectangle has a double line border
Relational database term for View or Result Set
Derived relvar
Relational Calculus
Describing the output of an operation rather than the process to attain it
DIA0 or Diagnostic Process back
Detects and resolves hangs and deadlocks.
Composite attribute
Divided into smaller subparts which represent basic attributes with independent meanings
Rigorous 2PL
Each transactions keeps all locks until a commit or rollback
Atomicity Property
Either all database operations in a transaction are executed or none are.
2NF to 3NF
Eliminate all transitive functional dependencies
Maximum cardinality
The maximum number of instances of one entity that may be associated with each instance of another entity
Relation schema
Fixed set of attributes were each attribute corresponds to exactly one of the underlying domains
Miscellaneous issues 1
For composite attributes, include only the atomic attributes: CUSTOMER(CustID, Age, Street, City, State, Zip) For a composite attribute that serves as a primary key, you would need to underline all sub-attributes in the relation to clarify this (e.g., for a composite primary key such as FullName, this may break down into: FirstName, MiddleName, LastName).
Homogeneous vs Heterogeneous DBMS
Homogeneous- use the same DBMS software at all the sites Heterogeneous- can use different DBMS software at each site
Pseudotransitivity
If X → Y and YZ → W, then XZ → W
Fourth Normal Form
If every nontrivial multivalued dependency X --> Y in F(+17)X is a super key for R -Violated when a relation has undesirable multivalued dependencies.
Structured data
Information stored in databases that is represented in a strict format
The implementation and loading phase of the Database Life Cycle (DBLC) involves _____
Install the DBMS Create the database(s) Load or convert the data
Interleaved Processes/Parallel Processing
Interleaved: Concurrent execution of processes interleaved in a single CPU Parallel: Processes executed in multiple CPUs
SMON
Is in charge of a variety of system level cleanup duties. performing instance recover at instance startup if necessary recovering any terminated translocation that were skipped during instance recovery cleaning up unused temporary segments
User global area (UGA)
It is a memory associated with a user session.
Fixed SGA
It is an internal housekeeping area. It contains general information required by the background processes about the state of the database and the instance. The size of the fixed SGA is set by the oracle database and cannot be altered manually.
_____ is the body of information and facts about a specific subject
Knowledge
Information
Knowledge derived from data
_____ is a string function that returns the number of characters in a string value
LENGTH
DML
Language for manipulations including retrieval insertion deletion and modification
Unstructured data
Limited indication of the type of data
Identifying relationship
Links a strong entity to a weak entity
Systems analyst
The organizational role most responsible for the analysis and design of information systems.
Logical design
The part of the design phase of the SDLC in which all functional features of the system chosen for development are described independently of any computer platform.
The _____ pseudo-column is used to select the next value from a sequence
NEXTVAL
Internal schema
The physical storage structure of the database
Based on Microsoft's Component Object Model (COM), _____ is database middleware that adds object-oriented functionality for access to relational and nonrelational data
Object Linking and Embedding for Database — OLE-DB
First normal form (1NF)
Of relation that has a primary key and in which there are no repeating groups
two basic memory structures associated with an instance
PGA and SGA
Heuristic 1
Performing σ and π as early as possible to reduce the cardinality
Legal and contractual feasibility
The process of assessing potential legal and contractual ramifications due to the construction of a system.
The _____ is a shared, reserved memory area that stores the most recently executed SQL statements or PL/SQL procedures, including triggers and functions
SQL cache, or procedure cache
The Oracle ____ function returns the current date
SYSDATE
Normalization
The process of decomposing relations with anomalies to produce smaller, well-constructed relations
Leaf nodes
Simple elements
XPath
Simple query language used to select parts of an XML Document. -Queries nodes are separated by '/' character -result is whatever is at the end of the expression -attribute name prefixed with @ -* --> stands for any element
Rename ρ
Simply renames a table of column which can be done by name or from the position in the tuple.
In DBLC, the phase after the database initial study is _____
Six phases: Database Initial Study Database Design Implementation and Loading Testing and Evaluation Operation Maintenance and Evolution
The traditional Database Life Cycle (DBLC) is divided into _____ phases
Six phases: Database Initial Study Database Design Implementation and Loading Testing and Evaluation Operation Maintenance and Evolution
Application program interface (API)
Software building blocks that are used to ensure that common system capabilities, such as user interfaces and printing, and modules are standardized to facilitate the data exchange between clients and servers.
PSP0 or Process Spawner Process back
Spawns Oracle background processes after initial instance startup.
Business rules
Specification that preserves the integrity of the logical data model.
Functional dependency
Specifies a constraint on the possible tuples that conform a relation state *denoted by X --> Y
Overlap rule
Specifies that an entity instance can simultaneously be a member of two (or more) subtypes.
Persistent storage
Storing information for longer than one session . ex. hard drive, database
The _____ role of a DBA covers activities that involve evaluating, selecting, installing, and maintaining a DBMS and its related utilities and applications
Technical
Political feasibility
The process of evaluating how key stakeholders within the organization view the proposed system.
Database Recovery
The process of restoring a Database following a failure
Read Committed
The DBMS enforcing Strict 2PL
The _____ statement combines rows from two queries and excludes duplicates
UNION
Query
a request for data or information from a database table or combination of tables
Solution to ternary problem
aggregation
Database
an integrated collection of stored data that is centrally managed and controlled
A(n) _____ might be written by a programmer or it might be created through a DBMS utility program
application
Another name for a composite entity is a(n) _____ entity
bridge
Logical Storage
data blocks extents segments tablespaces
Conceptual Level/Schema
describes the structure of the whole database for a group of users schema hides the details of storage & concentrates on describing entities, data types, relationships, user operations, & constraints; uses a representational data model
Students and classes have a _____ relationship
many-to-many
Attribute
one vertical group of data attribute values
A _____ key is defined as a key that is used strictly for data retrieval purposes
secondary
transparency
some aspect of the distributed system that is hidden from the user
A _____ is any key that uniquely identifies each row
superkey
Testing
the process of examining a component, subsystem, or system to determine its operational characteristics and whether it contains any defects
Main restriction of XPath path expressions
-path that specifies pattern also specifies items retrieved -difficult to specify certain conditions while specifying which result items should be retrieved
Menu interaction
A human-computer interaction method whereby a list of system options is provided and a specific command is invoked by user selection of a menu option
Natural language interaction
A human-computer interaction method whereby inputs to and outputs from a computer-based application are in a conventional speaking language such as English
Key constraints
A relation must have at least one candidate key such that for every tuple, the value must be unique and no value may be NULL (the latter point is known as the entity integrity constraint)
Binary relationship
A relationship between instances of two entity types.
Composite attribute
An attribute that has two or more component parts.
Anomalies
For more complex relations, database operations may cause problems, inconsistencies, or errors known as anomalies Insertion Anomaly: new data cannot be entered as desired Modification Anomaly (aka Update Anomaly): changing data in one location requires changing data in many locations Deletion Anomaly: removal of data leads to unintended or unexpected data loss
1 NF
Have no duplicate tuples Have no multi-valued attributes By definition, a valid relation will conform to 1NF Data that do not conform to 1NF (e.g., because of multi-valued attributes) are said to be in unnormalized form (UNF).
_____ is a higher-level, object-oriented application interface used to access remote database servers
Remote Data Objects — RDO
The _____ table space is used to store the data dictionary tables
System table space
T/F? Supertype/subtype relationships can span multiple levels
True
-centralized -distributed
Two types of database architecture
Database Listener
The listener is a separate process that runs on the database server computer. It receives incoming client connection requests and manages the traffic of these requests to the database server.
very few changes in system
advantage of distributed presentation
-local optimization -security
advantages of horizontal and vertical partitioning
Key-value Data Model
associates a unique key with each value (record or object) & provides very fast access to a value given its key
An atomic attribute _____
cannot be further subdivided
Schema Evolution
changing the database schema as requirements change
thin client
contain just a small portion of the application logic;- little application logic on the client side; most shifted to server side
In business intelligence framework, data are captured from a production system and placed in _____ on a near real-time basis
data warehouse - DW
Once the data has been loaded into the database, the _____ tests and fine-tunes the database for performance, integrity, concurrent access, and security constraints
database administrator
Every XML document has a _____
root element
A(n) _____ is a logical grouping of several data files that store data with similar characteristics
table space or file group
Surrogate keys
(those with no business meaning such as 001, 002, etc. [sometimes referred to as synthetic or system-generated keys]).
horizontal partitioning
When you have an organizational design in which functions are replicated, what type of partitioning do you use?
encryption
a means of disguising information by the use of mathematical algorithms (or formulas)
object request brokers
-the programming that acts as a "broker" between a client request for a service from a distributed object or component and the completion of that request -provides the mechanism required for distributed objects to communicate with one another, whether locally or on remote devices, written in different languages, or at different locations on a network
3 Main Types of Constraints
1. Inherent/Implicit - based on data model itself 2. Schema-based/Explicit - expressed in schema (e.g. cardinality ratio constraint) 3. Application-based/semantic - enforced by applications
How many rows would be returned from a cross join of tables A and B, if A contains 8 rows and B contains 18?
144
At the implementation level, the supertype and its subtype(s) depicted in a specialization hierarchy maintain a(n) _____ relationship
1:1
Self Determination
A derivation of Armstrong's Axioms. A → A
Union
A derivation of Armstrong's Axioms. if A → B and A → C then A → B, C
Entity-relationship data model (E-R model)
A detailed, conceptual representation of the entities, associations, and data elements for an organization or business area.
conceptual schema
A detailed, technology-independent specification of the overall structure of organizational data. Typically the conceptional schema is then broken down into a logical schema.
Project Scope Statement (PSS)
A document prepared for the customer that describes what the project will deliver and outlines generally at a high level all work required to complete the project.
Object class
A logical grouping of objects that have the same (or similar) attributes and behaviors (methods).
Drop-down menu
A menu-positioning method that places the access point of the menu near the top line of the display; when accessed, menus open by dropping down onto the display
Interface
A method by which users interact with information systems
Armstrong's Axioms
3 Inference rules that can be applied to FDs to rearrange them, useful in finding BCNF
-Presentation middleware -application middleware -database middleware
3 classes of middleware
Full dependency
: values in a non-key column are functionally determined exclusively by the complete primary key
Internal level (physical schema)
Details of data storage and access paths. Actual data exists only in this level!
Client/server architecture
Developed to deal with computing environment in which a large number of people and computers are connected via a network
The typical flow when designing a database involves:
Developing an ER diagram Mapping the ER diagram into the relational model Normalizing to avoid anomalies, reduce redundancy, and increase integrity Instantiating the database (e.g., via SQL)
Business-to-business (B2B)
Electronic commerce between business partners, such as suppliers and intermediaries
Dialogue
The sequence of interaction between a user and a system
Entities
Can be objects, events, concepts, etc. rectangle box
System librarian
A person responsible for controlling the checking out and checking in of baseline modules for a system when a system is being developed or maintained.
Checkpoints
A point in the database to be restored to.
Subtype
A subgrouping of the entities in an entity type that is meaningful to the organization and shares common attributes or relationships distinct from other subgroupings.
Subtype
A subgrouping of the entities in an entity type that is meaningful to the organization and that shares common attributes or relationships distinct from other sub groupings.
Derived attribute
An attribute whose values can be calculated from related attribute values.
Candidate key
An attribute, or combination of attributes, that uniquely identifies a row in a relation
Associative entity
An entity type that associates the instance of one or more entity types and contains attributes that are peculiar to the relationship between those entity instances
Actor
An external entity that interacts with a system in a use case
Adaptive maintenance
Changes made to a system to evolve its functionality to changing business needs or technologies.
Corrective maintenance
Changes made to a system to repair flaws in its design, coding, or implementation.
Presentation logic
any processing done in UI
Composite and atomic attributes
?
Walkthrough
A peer group review of any product created during the systems development process.
Index
A table used to determine the location of rows in a file that satisfy some condition.
Data sublanguage
DML
External schema
Describes part of the database that a particular group is interested in
What to use to specify Queries in XQuery
FLWR
Icon
Graphical pictures that represent specific functions within a system
PGA(Program Global Area)
The Program Global Area (PGA) contains information used for private or session-related information that individual users need. The PGA contains the following: Session memory and Private SQL area
Maintainability
The ease with which software can be understood, corrected, adapted, and enhanced.
_____ data refer to data whose values change over time and for which one must keep a history of the data changes
Time-variant
From the performance point of view, _____ databases eliminate disk access bottlenecks
in-memory
A _____ lock locks the entire diskpage
page-level
Presentation layer
the actual user interface
data replication
to ensure a fast response from databases and to enable the system to be resilient to hardware errors
certificate authority
trusted organization that can vouch for the authenticity of the person or organization using authentication
Entity
A representation of a real world object, represented by a set of attributes
Referential integrity constraint
A rule that states that either each foreign key value must match a primary key value in another relation or the foreign key value must be null
Referential integrity
A rule that states that either each foreign key value must match a primary key value in another relation or the foreign key value must be null (i.e., have no value)
PERT
A technique that uses optimistic, pessimistic, and realistic time estimates to calculate the expected completion time for a particular task.
_____ are more detailed and specific than policies and describe the minimum requirements of a given DBA activity
Standards
Internal documentation
System documentation that is part of the program source code or is generated at compile time.
Cascadeless schedule
If every transaction read only items that were written by committed transactions
When is a schedule conflict serializable
If it is conflict equivalent to some serial schedule a.k.a. schedule is correct
Third normal form
If it satisfies 2NF and no nonprime attribute of R is transitively dependent on the primary key
Serializable schedule
If that is equivalent to some serial schedule of n transactions. Correct when concurrent *no cycle!*
Conflict equivalence
If two schedules have the same operations and the order of any two conflicting operations is the same in both schedules
Artificial or surrogate key
When row IDs or sequential numbers are assigned as keys to identify rows in a table
A primary key's main function is to uniquely identify a(n) _____ within a table
entity instance or row
HTTP (allows programmer to communicate with a browser through a standard interface)
give an example of presentation middleware
Referential _____ dictates that the foreign key must contain values that match the primary key in the related table, or must contain null
integrity
Internal nodes
Complex elements
Database engine
The (back-end) portion of the client/server database system running on the server that provides database processing and shared access functions.
3NF
The previous NF is applied plus Transitive Dependencies are removed
Identifying relationship
The relationship between a weak entity type and its owner
SGA
System Global Area - an oracle area of shared memory or an instance
F in FLWR
assigns variable to range over items in a sequence in XQuery
client-server architectures
attempt to balance the processing between client devices and one or more server devices
The data cache or _____ is a shared, reserved memory area that stores the most recently accessed data blocks in RAM
buffer cache
Thin Web-based client
business and data access logic on the server side; always connected to server.
Project closedown
The final phase of the project management process that focuses on bringing a project to an end.
Outsourcing
The practice of turning over some or all of an organization's information systems applications and operations to an outside firm.
Normalization
The prcess of organizing data into tables such a way that the results of using the database are always unambiguous and as intended
2NF
The previous NF is applied plus Partial Dependencies are removed.
BCNF
The previous NF is applied plus all FDs for the Relation are a candidate key
Operational feasibility
The process of assessing the degree to which a proposed system solves business problems or takes advantage of business opportunities.
Several vital server requirements for database installation.
memory, Virtual memory CPU speed Disk
the self describing data is called
metadata which is data about data
Discount Rate
The rate of return used to compute the present value of future cash flows.
The main disadvantage of using CGI scripts is that
they cause a resource bottleneck since they have to execute separately for each user request
Replicated database server architecture
complete database copies are hosted by cooperating DBMSs running on multiple servers
A _____ attribute can be further subdivided to yield additional attributes
composite
Composite keys contain ...
multiple attributes
Relation
Unnamed two-dimensional table of data
Don't want name as primary identifier
names can change/ be duplicate
ORM
object-relational mapping
What are the additional files used during the operation of the database
parameter file, password file, backup files, archived redo log files, trace files, alert log file
One important inheritance characteristic is that all entity subtypes inherit their _____ key attribute from their supertype
primary
Foreign key
primary key from another table
High-level/Conceptual Data Model
provide concepts that are close to the way many users perceive data
A(n) _____ is bidirectional
relationship
Column-based Data Model
store the columns of rows clustered on disk pages for fast access and allow multiple versions of data
database design from new systems development
create data model from application requirements Transform data model into database design
A _____ contains at least all of the attribute names and characteristics for each table in the system
data dictionary
Database State/Snapshot
data in a database at a particular moment in time also called Current Set of Occurences/Instances in the database
In the context of the database design process, the conceptual design step that identifies ER modules and validates insert, update, and delete rules is _____
data model verification
Single-user Systems
support only one user at a time are mostly used with pcs
What is a user process
the application or tool that connects to the Oracle database
The extended entity relationship model (EERM) is sometimes referred to as the
enhanced entity relationship model
A _____ is a character or group of characters that has a specific meaning
field
User-friendly Interfaces
for interacting with the database for naive & parametric users can also be used by casual users or others who don't want to learn higher-level languages
Transivity
if X → Y and Y → Z, then X → Z
User Process
When a user runs an application program (such as a Pro*C program) or an Oracle tool (such as Enterprise Manager or SQL*Plus), Oracle creates a user process to run the user's application.
First normal form
When the domain of an attribute must include only atomic values. Disallows relations within relations as actual values within tuples
Lost update
When two transactions that access the same item have their operations interleaved that makes the value of some database items incorrect
vertical partitioning
When you have organizational functions with separate data requirements, what type of partitioning do you use?
Access control
a control that restricts which persons or programs can add, modify, or view information resources
three-schema architecture
a model of database development that defines 3 schemas: 1. External schema 2. Conceptual schema 3. Internal schema.
entity
a noun in the user environment about which the organization wishes to maintain data
zero client/ultra thin client
a small box that connects a keyboard, mouse, monitor and Ethernet connection to a remote server. The server hosts everything
Partial Specialization Rule
a supertype may have a subtype Indicated with a single line (e.g., previous examples)
Certifying authority
a widely accepted issuer of digital certificates
Denormalization
an approach to speeding up read performance (data retrieval) in which the administrator selectively adds back specific instance of redundant data after the data structure has been normalized. Should not be confused with a database that has never been normalized.
authentication
digital signatures
Encryption (part of Data Encryption, Security Controls)
the process of altering data so unauthorized users can't view them
Shared pool contains what?
the shared pool contains the library cache and Shared SQL area, the data dictionary cache, the server results cache
replication transparency
the term used to describe the fact that the user should be unaware that data is replicated
When two or more tables share the same number of columns, and when their corresponding columns share the same or compatible domains, they are said to be _____
union-compatible
As long as two transactions, T1 and T2, access _____ data, there is no conflict, and the order of execution is irrelevant to the final outcome
unrelated
Test data
Test data - a set of starting states and events used to test a module, group of modules, or entire system
Client
The (front-end) portion of the client/server database system that provides the user interface and data manipulation functions.
Relation state
Set of n-tuples
message queues
-a queue of messages sent between applications -includes a sequence of work objects that are waiting to be processed
Domain contraints
Attributes must be of the same type and consistent with domain rules. For instance, the attribute NumberOfChildren could not be negative
DBMS Languages
Data Definition Language - A language used where no separation of levels exists; used to define conceptual and internal schemas When a separation does exist: 1. Storage Definition Language: Specifies internal schema 2. View Definition Language: Specifies user views and their mappings to conceptual schema (SQL often takes on this role, but usually the DDL is dominent) 3. Data Manipulation Language: Perform insertions, retrievals, deletions, modifications
DDL
Data Definition Language. Used to specify the structure, types and constraints of a Database.
data vs. information
Data are raw facts; information is data converted into a meaningful & useful context for decision-making
_____ refers to the number of different values a column could possibly have
Data sparsity
_____ are in charge of presenting data to the end user in a variety of ways
Data visualization tools
Database schema
Description of the database (of which a displayed form is known as a "schema diagram")
Detect and Recover
The Scheduler allows Deadlocks to occur; rolls back, finds a victim Transaction and restarts that.
LGWR
Manages the redo log buffer a user commits a transaction an online redo log switch occurs 3 seconds have passed The redo log buffer is one third full. DBWN must write modified buffers to disk.
Relation
Table. Unordered set of tuples
A database stores
data and relationships
Intersection R ∩ S
Returns all rows that exist in both R and S, R and S must be Union Compatible
The _____ data type is compatible with NUMBER
SMALLINT
If there is no index, the DBMS will perform a _____ scan
full table scan
data model
An analysis model that depicts the logical structure of data relationships
Derived attribute
An attribute that can be derived from two other attributes
Primitive DFD
The lowest level of decomposition for a DFD
Miscellaneous issues 3
***Will be on midterm Derived attributes are not mapped in relational schemas!
Tablespaces
-A database is divided into logical storage units called table spaces. -It is a logical container for a segment. -can be permeanent or temporary
Data mart
A data warehouse that is limited in scope; its data are obtained by selecting and (where appropriate) summarizing data from the enterprise data warehouse.
Database
A database is a set of files, located on disk, that store data.
Database Design Steps
1. Requirements Specification And Analysis 2. Conceptural Design 3. Logical Design 4. Physical Design
problems from impedance mismatch
1. data types in PLM do not match DBM (must have a binding between both data types) 2. Queries return multiple rows
Level-n diagram
A DFD that is the result of n nested decompositions of a series of subprocesses from a process on a level-0 diagram
Level-0 diagram
A DFD that represents a system's major processes, data flows, and data stores at thefirst level below the context diagram
Transaction
A Logical unit of work to be executed on a Database.
Thin client
A client device designed so that most processing and data storage occur on the server.
Data type
A coding scheme recognized by system software for representing organizational data.
Database
A collection of data (and the way it is stored) which is logically related..
primary key
A field (or group of fields) that uniquely identifies a given entity in a table
Pointer
A field of data that can be used to locate a related field or row of data.
Calculated Field
A field that can be derived from other database fields.
GANTT chart
A graphical representation of a project that shows each task as a horizontal bar whose length is proportional to its time for completion.
Entity-relationship diagram (E-R diagram, or ERD)
A graphical representation of an entity-relationship model
Supertype/subtype hierarchy
A hierarchical arrangement of super types and subtypes in which each subtype has only one supertype
Critical path scheduling
A scheduling technique whose order and duration of a sequence of task activities directly affect the completion date of a project.
What is a Segment
A segment is a set of extents allocated for a certain logical structure such as: data segments, index segments, undo segments, temporary segments, etc.
Term
A word or phrase that has a specific meaning for the business.
Relationship
An association between the instances of one or more entity types that is of interest to the organization.
ICOM (IDEF0)
An input, control, output, or mechanism associated with an IDEF0 activity
Foreign Key
An key attribute that is the primary key of another Entity
Input (IDEF0)
An object or information converted by the activity into an output and materially appear in the output
RUP
An object-oriented systems development methodology. This methodology establishes four phases of development, each of which is organized into a number of separate iterations: inception, elaboration, construction, and transition.
Information systems planning (ISP)
An orderly means of assessing the information needs of an organization and defining the systems, databases, and technologies that will best satisfy those needs.
Lock Rule 1
Any Access to data must be preceded by a lock request and proceeded by a unlock
_____ tools focus on the strategic and tactical use of information
Business intelligence
Cost Assesment
Calculating a relative cost of executing a Query
Which of the following types of HDFS nodes acts as the interface between the user application and the HDFS?
Client node
Process Structure
Client process Background process Server process
Relational schemas
Conceptual frame work and actual framework that it is able to be implemented. Ex. PROFESSOR(ProfID, Name, Dept)
FD
Constraints on a field showing what an attribute relies on. In the form id → name, number meaning the 'name' and 'number' are dependant on 'id'
Complex elements
Constructed from other elements hierarchally
_____ security breaches include database access by computer viruses and by hackers whose actions are designed to destroy or alter data
Corrupting
Designing Relational Databases Based on the Domain Model Class Diagram
Create a table for each class Choose a primary key for each table (invent one, if necessary) Add foreign keys to represent one-to-many associations Create new tables to represent many-to-many associations Represent classification hierarchies Define referential integrity constraints Evaluate schema quality and make necessary improvements Choose appropriate data types Incorporate integrity and security controls
Automatic query optimization means that the
DBMS finds the most cost-effective access path without user intervention
What is a database management system?
DBMS is a collection of programs that enables users to define, construct, and manipulate a database (more detailed definition later)
On the server side, the database environment must be properly configured to respond to clients' requests in the fastest way possible, while making optimum use of existing resources. The activities required to achieve this goal are commonly referred to as _____ tuning
DBMS performance
Database state
Data in the database at a particular moment in time
Data management as a "necessary evil" (1950s, 60s) -EAM AND EDP
EAM: electrical accounting machine EDP: electronic data processing Associated with the bureaucracy of designing, manufacturing or distributing products/services Purpose of IS (EAM, EDP) was to reduce the cost of paper processing, especially, accounting
what are control files
Each database has one unique control file that contains data about the database itself (that is, physical database structure information). Multiple copies may be maintained to protect against total loss. It can also contain metadata related to backups. The control file is critical to the database. Without the control file, the database cannot be opened.
what is the relationship between databases, tablespaces, and datafiles
Each database is comprised of two or more tablespaces. Tablespaces can have multiple data files
Unit testing
Each module is tested alone in an attempt to discover any errors in its code; also called module testing.
Isolation Property
Each transaction appears to execute in isolation from other transactions
ERP
Enterprise resource planning - business management system that integrates all functions of the enterprise, such as manufacturing, sales, finance, marketing, inventory, accounting, and human resources. ERP systems are software applications that provide the data necessary for the enterprise to examine and manage its activities.
Strong Entity
Exists independently of other types of entities Has its own unique identifier, underlined with a single line
The traditional Systems Development Life Cycle (SDLC) is divided into _____ phases
Five phases: Planning Analysis Detailed System Design Implementation Maintenance
Conceptual level (conceptual schema)
Hides details of storage structures and concentrates on describing entities and the interrelationships between them; integrated conceptual organization of data.
Which of the following is a Business intelligence appliances vendor for BI applications?
IBM, Netezza, Greenplum and AsterData
Union
If X → Y and X → Z, then X → YZ
Decomposition
If X → YZ, then X → Y and X → Z
deadlocks
In a database, a deadlock is a situation in which two or more transactions are waiting for one another to give up locks. For example, Transaction A might hold a lock on some rows in the Accounts table and needs to update some rows in the Orders table to finish.
Buffer
In the phrase database buffer cache the term buffer refers to database blocks. A buffer, also called buffer memory, is a portion of a computer's memory that is set aside as a temporary holding place for data that is being sent to or received from an external device, such as a hard disk drive (HDD), keyboard or printer.
Electronic Commerce (EC)
Internet-based communication to support day-to-day business activities
_____ is a scripting language that allows web authors to design interactive sites
JavaScript
Describe the LREG
Listener Registration Process Registers information about the database instance and dispatcher processes with the Oracle Net Listener LREG provides the listener with the following information: • Names of the database services • Name of the database instance associated with the services and its current and maximum load • Service handlers (dispatchers and dedicated servers) available for the instance, including their type, protocol addresses, and current and maximum load
Disadvantages
More complicated SQL required for multitable subqueries and joins Extra work for DBMS
The _____ constraint can be placed on a column to ensure that every row in the table has a value for that column
NOT NULL
NoSQL
Not only Sequential Query Language ex. graph / k-v / document databases
Output (IDEF0)
Objects or information that is produced by the activity
Parity
Parity is a mathematical technique of maintaining special bits of data to re-create data if a disk is lost.
Which of the following is the first step of query processing at the DBMS server end?
Parsing
_____ returns only the attributes requested, in the order in which they are requested
PROJECT
Result equivalence
Produce the same final output
RECO or Recoverer recovers distributed transactions back
RECO uses the information in the pending transaction table to finalize the status of in-doubt transactions. At timed intervals, the local RECO attempts to connect to remote databases and automatically complete the commit or rollback of the local portion of any pending distributed transactions. All transactions automatically resolved by RECO are removed from the pending transaction table.
The _____ cache stores the most recently executed SQL statements
SQL cache or Procedure cache
structured data vs. unstructured data
Structured data refers to information with a high degree of organization, such that inclusion in a relational database is seamless and readily searchable by simple, straightforward search engine algorithms or other search operations; unstructured data is essentially the opposite. The lack of structure makes compilation a time and energy-consuming task. It would be beneficial to a company across all business strata to find a mechanism of data analysis to reduce the costs unstructured data adds to the organization.
When does the DBWn actually write to disk?
The DBWn process writes dirty buffers to disk under the following conditions: • When a server process cannot find a clean reusable buffer after scanning a threshold number of buffers, it signals DBWn to write. DBWn writes dirty buffers to disk asynchronously while performing other processing. • DBWn writes buffers to advance the checkpoint, which is the position in the redo thread (log) from which instance recovery begins. This log position is determined by the oldest dirty buffer in the buffer cache. In all cases, DBWn performs batched (multiblock) writes to improve efficiency. The number of blocks written in a multiblock write varies by operating system.
Determinant
The attribute on the left side of the arrow in a functional dependency
Information systems analysis and design
The complex organizational process whereby computer-based information systems are developed and maintained.
Time Value of Money (TVM)
The concept the money available today is worth more than the same amount tomorrow.
Serialisability
The conditions that guarantee non-interference between concurrent Transactions
Balancing
The conservation of inputs and outputs to a DFD process when that process is decomposed to a lower level
Present value
The current value of a future cash flow.
Implementation
The fourth phase of the SDLC in which the information system is coded, tested, installed, and supported in the organization.
Inheritance
The hierarchical arrangement of classes enable subclasses to inherit properties of superclasses
Competitive strategy
The method by which an organization attempts to achieve its mission and objectives.
Indexed file organization
The rows are stored either sequentially or nonsequentially, and an index is created that allows software to locate individual rows.
Sequential file organization
The rows in the file are stored in sequence according to a primary key value.
Intension/Extension
The schema of the database vs. a state of the database as it relates to the schema
In-Doubt Transactions
The two-phase commit mechanism ensures that all nodes either commit or perform a rollback together. What happens if any of the three phases fails because of a system or network error? The transaction becomes in-doubt. A server machine running Oracle Database software crashes A network connection between two or more Oracle Databases involved in distributed processing is disconnected An unhandled software error occurs
BCNF
When ever a nontrivial functional dependency X --> A holds in R, then X is a super key of R
Second normal form
When every nonprime attribute A in R is fully functionally dependent on the primary key of R
Overlap Constraint
Whether 2 subclasses are allowed to contain the same Entity.
Covering Constraint
Whether an Entity's subclasses collectively cover all entity in the superclass. i.e. there are no instances of just the superclass.
ODBC works on the _____ operating system (ODBC - Open Database Connectivity)
Windows (chapter 14 - 627)
Integrity control
a control that rejects invalid data inputs, prevents unauthorized data outputs, and protects data and programs against accidental or malicious tampering
Facts about instances
You can set up multiple instances to access the same set of files or one database. An instance can only access one database. A database can exist without an instance, but would be useless. An instance can exist without a database.
Set Type
a 1:N, or one to many, relationship that relates one instance of a record to many record instances using some pointer linking mechanism in the model aka- CODASYL DBTG model has an associated record-at-a-time language that must be embedded in a host programming language
Relational database management system
a DBMS that organizes data in tables (relations)
Data Definition Language (DDL)
a DBMS where no strict separation of levels is maintained uses this language to define conceptual and internal schemas compiler processes the DDL statements in order to identify descriptions of the schema constructs & store the schema description in the DBMS where a clear separation is maintained between the schema levels, DDL is used to specify conceptual schema only
Complex update control
a control that prevents errors that can occur when multiple programs try to update the same data at the same time or when recording a single transaction requires multiple related database updates
Encryption key
a binary input to the encryption algorithm—typically a long string of bits
Primary Key
a candidate key selected as the primary means of identifying rows in a relation
Attribute
a column/field in the relation corresponds to an attribute in ER
Key
a combination of one or more columns that is used to identify rows in a relation
Encryption algorithm
a complex mathematical transformation that encrypts or decrypts binary data
Referential integrity
a consistent state among foreign key and primary key values
Phased deployment
a deployment method that installs a new system and makes it operational in a series of steps or phases
Direct deployment
a deployment method that installs a new system, quickly makes it operational, and immediately turns off any overlapping systems Higher risk, lower cost
A(n) _____ plan is a set of instructions generated at application compilation time that predetermines how the application will connect to and communicate with the database at run time
access
Data layer
actual storage data
LANs
allow for databases to be spread over multiple computers a communication network to allow this: local area networks
Dynamic Aspect/Behavior
allows the database designer to specify a set of valid user-defined operations that are allowed
Document Data Model
based on JSON & stores the data as documents that somewhat resemble complex objects
In standard SQL, the optimizer hint ALL_ROWS is generally used for _____ mode processes
batch mode processes
SQL Data types
char(size) Fixed-length character string. Size is specified in parenthesis. Max 255 bytes. varchar(size) Variable-length character string. Max size is specified in parenthesis. number(size) Number value with a max number of column digits specified in parenthesis. date Date value number(size,d) Number value with a maximum number of digits of "size" total, with a maximum number of "d" digits to the right of the decimal.
Relational DBMS
evolving quickly/continuously and have been incorporating many ideas of the object databases lead to OR-DBMS systems based on this model known as SQL systems
local optimization
example: data partitioned into seniors, juniors, sophomores, or freshmen in different physical sites
-central computer corporate databases -personal computer databases -host-based
examples of centralized database architecture
-ODBC -JDBC
examples of database middleware
T/F? ER's have foreign keys
false. Note there is the possibility of a NULL (optional) foreign key value
A _____ occurs when there is one entity in two 1:M relationships to other entities, thus producing an association among the other entities that is not expressed in the model
fan trap
An entity is said to be _____-dependent if it can exist in the database only when it is associated with another related entity occurrence
existence
Tree-structured Data Model
experimental DBMSs that are based on XML aka- native XML DBMSs several commercial DBMSs have added XML interfaces and storage to their products
In a typical star schema, each dimension record is related to thousands of _____ records
fact (chapter 13 - 585)
security controls
firewalls, intrusion detection systems, and routine backup and recovery options
In a real-world environment, we must strike a balance between design integrity and
flexibility
performance requirements
focus on performance issues such as response time, capacity and reliability
availability and reliability requirements
focus on the extent to which users can assume that the system will be available for them to use
Graph Databases
good for highly relational data with complex relationships; can query for nodes based on data and relationships bad for tree like data ex. Neo4J
multilingual requirements
have to support users who speak different languages and write with non-English letters
Purpose of a database
help people track things of interest to them
One of the limitations of the _____ model is that there is a lack of standards
hierarchical
In organizations that generate large number of transactions, _____ are often a top priority in database design
high processing speeds
speed requirements
how fast the system must operate
select statement (SQL)
in SQL -- queries the database to retreive selected data General: select "column1" [,"column2",etc] from "tablename" [where "condition"]; [] = optional ex. select first, last, from empinfo where last LIKE '%s'; // selects first and last columns from table (empinfo) // where last name ends with s select first, last, age from empinfo where last LIKE '%illia%'; // selects first last and age columns from empinfo where last name contains illia
A data administrator is also known as a(n) _____
information resource manager
Concurrency Control & Backup/Recovery Systems
integrated into the working of the runtime database processor for purposes of transaction management
server virtualization
involves partitioning a physical server into smaller virtual servers
According to Codd's _____ rule of relational database, "If the system supports low-level access to the data, users must not be allowed to bypass the integrity rules of the database."
nonsubversion
Functional dependency
occurs when the value of one (set of) attribute(s) determines the value of a second (set of) attribute(s): Example: StudentID -> StudentName StudentID -> (DormName, DormRoom, Fee)
Ideally, an entity identifier is composed of _____ attribute(s)
one
how many databases associated with an instance?
one and only one database
discrete multilingual system
one installation will only use one language
Relational Data Model
represents a database as a collection of tables where each table can be stored as a separate file most use the high level query language (SQL) & support a limited form of user views have been expanding their models to incorporate object database concepts & capabilities
Entity
represents a real-world object or concept from the miniworld that is described in the database
Hierarchical Model
represents data as hierarchical tree structures each hierarchy represents a number of related records there is no standard language Ex: DL/1 of IMS system
Network Model
represents data as record types & represents a limited type of 1:N relationships (set type)
What is a data mart?
A subset of a data warehouse dedicated to a single project, department, or team
Simple (or atomic) attribute
An attribute that cannot be broken down into smaller components that are meaningful to the organization
simple attribute
An attribute that cannot be subdivided into meaningful components. Compare to composite attribute.
Homonym
An attribute that may have more than one meaning - for example, the term account may refer to a bank's checking account, savings account, loan account, or other type of account (and therefore, account refers to different data, depending on how it is used).
Multivalued attribute
An attribute that may take on more than one value for each entity instance.
Required attribute
An attribute that must have a value for every entity (or relationship) instance with which its associated.
Required attribute
An attribute that must have a value for every entity instance.
A relationship is an association between _____
entities
Knowing the minimum and maximum number of _____ occurrences is very helpful at the application software level
entity
The purpose of a(n) _____ is to simplify an entity-relationship diagram (ERD) and thus enhance its readability
entity cluster
The _____ model uses the term connectivity to label the relationship types
entity relationship
The _____ model was developed to allow designers to use a graphical tool to examine structures rather than describing them with text
entity relationship
Mapping Weak Entities
except the primary key(s) from any attached strong entity or entities must be included with the weak entity MOVIE(MovieID, MovieName, ReleaseDate) VIDEO-TAPE(CopyNumber, DateAcquired, MovieID)
A DBA determines the initial size of the data files that make up the database; however, as required, the data files can automatically expand in predefined increments known as _____
extends
XQuery
extension that provides a language for more complex and general queries -same data model -similar to SQLs relationship to relational database -Uses FLWOR expressions
Precompiler
extracts DML commands from an application program written in a host programming language, sends it to the DML compiler for object compilation, links the objects and the rest of the program, calls the runtime database processor
In the context of RAID levels, striped arrays provide
increased read performance but no fault tolerance
two-tiered architecture
it uses only 2 sets of computers - clients and servers
Data model
Collection of concepts that can be used to describe the structure of a database
What is a database system?
Collectively, the database model, the DBMS, and the database itself
Three-schema Architecture
proposed to help achieve/visualize 3 characteristics of a database: use of a catalog to store the description, insulation of programs & data, & support of multiple users goal is to separate the applications from the database
Low-level/Physical Data Model
provide concepts that describe the details of how data is stored on the computer; generally meant for computer specialists
NoSQL databases
provide fault tolerance
The _____ is actually a system-created database whose tables store the user/designer-created database characteristics and contents
system catalog
When a table contains only one candidate key, _____ are considered to be equivalent
the 3NF and the BCNF
_____ extends SQL so that it can differentiate between access requirements for data warehouse data and operational data
DBMS performance
In Oracle, the _____ command is used to change the display for a column, for example, to place a $ in front of a numeric value
FORMAT
Logical Structures
Tablespaces, Segments, Extents, Oracle Blocks
Design
The third phase of the SDLC in which the description of the recommended solution is converted into logical and then physical system specifications.
Direct deployment
a deployment method that installs a new system, quickly makes it operational, and immediately turns off any overlapping systems. Higher risk, lower cost
Driver
a method or class developed for unit testing that simulates the behavior of a method that sends a message to the method being tested
Surrogate Key
a primary key field that is automatically assigned by the computer. An example is the StudentNumber field in the STUDENT table above.
Foreign Key
a primary key in one table that is used as an identifier in another table.
Server
a system containing both hardware and software that can provide services to the client machines such as fille access, printing, archiving, database access, etc
User acceptance test
a system test performed to determine whether the system fulfills user requirements
Transaction logging
a technique by which any update to the database is logged with such audit information as user ID, date, time, input data, and type of update
Client Program
accesses the DBMS running on a separate computer/device from where the database is stored
Conceptually, MDBMS end users visualize the stored data as a three-dimensional cube known as a _____
data cube
data dependence vs. data independence
data dependence entails that data descriptions are included by the application programs that use the data; data independence entails that data descriptions are separated from the application programs.
Data redundancy produces _____
data integrity problems
A typical byproduct of poor design is ...
data redundancy, which involves repeated/duplicated information in multiple locations
Self-describing Data Model
data storage combines the description of the data with the data values themselves
_____ is defined as the condition in which all of the data in the database are consistent with the real-world events and conditions
Data integrity
Relational database model
Data represented as a set of related tables or relations.
You must use plug-ins and other client-side extensions
to improve processing capabilities of a browser
Change and Version Control
tools and processes handle the complexity associated with testing and supporting a system through multiple versions
Operational data are commonly stored in many tables, and the stored data represent information about a given _____ only
transaction
The information stored in the _____ is used by the DBMS for a recovery requirement triggered by a ROLLBACK statement, a program's abnormal termination, or a system failure such as a network discrepancy or a disk crash
transaction log
VKRM or Virtual Schedular for Resource Manager Process back
Serves as centralized scheduler for Resource Manager activity. VKRM manages the CPU scheduling for all managed Oracle processes. The process schedules managed processes in accordance with an active resource plan.
Domain
Set of atomic values. All the possible values and attribute can take. Indivisible.
Superkey
Set of attributes where for any valid relation state, no two tuples have the same superkey value. Every key is a superkey (specifically a "minimal" superkey) but not vice versa.
DBRM or Database Resource Manager back
Sets resource plans and performs other tasks related to the Database Resource Manager. If a resource plan is not enabled, then this process is idle.
Higher normal forms
Several normal forms exist beyond 3NF including: Elementary Key Normal Form (EKNF) Boyce-Codd Normal Form (BCNF or 3.5NF) Fourth Normal Form (4NF) Essential Tuple Normal Form (ETNF) Fifth Normal Form (5NF) Sixth Normal Form (6NF) Domain/Key Normal Form (DKNF) Typically it is sufficient to have tables in 3NF to avoid most types of anomalies, although exceptions do exist
Desirable Attributes of Organizational Memory
Shareable Transportable Secure Accurate Timely Relevant
SGA components
Shared pool Database buffer cache Redo log buffer Large pool Java pool
Baseline modules
Software modules that have been tested, documented, and approved to be included in the most recently created version of a system.
Sophisticated User
Someone who creates Database requests in a query Language
Database Designer
Someone who designs the schema of a Database
Partial Specialization
Specifies that an entity instance of the supertype does not have to belong to any subtype.
Relational Algebra
The performing of operations on sets of tuples
Parallel deployment
a deployment method that operates the old and the new systems for an extended time period Lower risk, higher cost
Parallel deployment
a deployment method that operates the old and the new systems for an extended time period. Lower risk, higher cost
macros
a rule or pattern that specifies how a certain input sequence should be mapped to a replacement output sequence according to a defined procedure
Database Instance
a set of memory structures that manage database files. An oracle instance consists of a shared memory area and set of background processes.
n-tier Architectures
allows the layers to be divided even further between the user and stored data; dividing up the business logic layer afford the advantage that any one tier can run on an appropriate processor/os platform and can be handled independently
Relationship
among 2+ entities; represents an association among the entities
Candidate identifier/keys
an attribute or attributes that can potentially be chosen as the primary identifier
Foreign key
an attribute that duplicates the primary key of a different (or foreign) table
derived attributes
an attribute whose value can be calculated when needed aka store date_of_birth and use this attribute to calculate age: age is a derived attribute
heterogeneous database
an automated system for the integration of heterogeneous, disparate database management systems to present a user with a single, unified query interface
When moving data from permanent storage to RAM, an I/O disk operation retrieves
an entire physical disk block
Index
an example of access path that allows direct access to data using and index term/keyword may be organized linear, hierarchical, or other
mission critical system
an information system that is literally critical to the survival of the organization
Streams pool
is used exclusively by oracle streams. It stores buffered queue messages and provides memory fro streams capture and apply processes.
A table is in 4NF if it is in 3NF, and
it has no multivalued dependencies
A(n) _____ condition occurs when two or more transactions wait for each other to unlock data
deadlock
Cross-platform frameworks
develop in web-based technologies and use framework to deploy to multiple devices.
Web/Email Servers
fall into the specialized server category
A _____ is a collection of related records
file
architectural components
hardware and software of a system
When using a(n) _____ join, only rows that meet the given criteria are returned
inner
Which of the following is a hardware-induced database failure
memory chip errors disk crashes bad disk sectors disk-full errors
In the context of RAID levels, _____ refers to writing the same data blocks to separate drives
mirroring or duplexing
Union
produces a relation that includes all the of tuples in two relations, if they are union compatible
Outer Join
produces a relation with all of the tuples of one relation, and also the matching tuples of another relation
architecture
segregation of layers depends on?
server-based architecture
server performing all four application functions
The _____ cache is used as a temporary storage area for ORDER BY or GROUP BY operations, as well as for index-creation functions
sort cache
"A password must have a minimum of five characters" is an example of _____
standards
Primary key
the key chosen by a database designer to represent relationships among rows in different tables
public key
the key used to encrypt data
asymetric algorithm
the key used to encrypt data is different from the one used to decrypt it
Authorization (part of access controls)
the process of allowing or restricting a specific authenticated user's access to a specific resource based on an access control list
location transparency
the use of names to identify network resources rather than their actual location
Attribute value
the value held in a single table cell Key -- an attribute or set of attributes, the values of which occur only once in all the rows of the table
tables are called integrated because
they store data about the relationships between the rows of data
If you have three different transitive dependencies, _____ different determinant(s) exist
three
Normalization works through a series of stages called normal forms. For most purposes in business database design, _____ stages are as high as you need to go in the normalization process
three
In the optimistic approach, during the _____ phase, changes are permanently applied to the database
write
Physical Structures
Data Files, OS blocks
Disadvantages of File Processing Systems
- two independent systems with a clear functional boundary -uncontrolled redundancy -inconsistent data -inflexibility -limited data sharing -poor enforcement standards -excessive program maintenance
Extents
-An extent is a specific number of logicallly contiguous data blocks, obtained in a single allocation.
Data blocks
-At the finest level of granularity, data is sorted in data blocks. one data blocks corresponds to a specific number of bytes on disk.
Java Database Connectivity
-an application programming interface (API) for the programming language Java -defines how a client may access a database
Document type definition(DTD)
-simple schema definition language -Each possible element in document is defined -Data types are not very general -elements always forced to follow specified ordering (unordered elements not permitted)
distributed database
-single logical database -physically located in multiple computers -interconnected by networks (data is shared)
Well formed XML Document
-starts with <? xml version ="1.0" standalone="yes"?> -forms a tree where every child is contained in parent element
XQuery Features
-support for joins -support for aggregate functions -support for conditional branching(if--then branching)
distributed DBMS
-the DBMS that coordinates access to data at various nodes -determines the location from which to retrieve requested data -can translate request from one node to a proper request to another node -provides data management functions such as security concurrency, deadlock control, query optimization, and failure recovery
CKPT
-updates the control file and data file headers. -performs full checkpoints only at database shutdown or on request.
middleware
-utility software that interfaces systems built with incompatible technologies
An XML document is considered valid
-well formed -follows a particular schema in a standard definition language
Criteria for identifiers
-won't change in value -will not be null -Avoid intelligent identifiers (e.g., containing locations or people that might change) -In general, substitute new, simple keys for long, composite keys
3 Main approaches to Indexing
1. Clustering 2. Hash Table 3. B-Trees
4 common approaches to organize XML Document contents
1. Using DBMS to store as text 2. Using DBMS to store as data elements 3. Designing a system for storing native data 4. Creating/Publishing customized XML Docs from preexisting Relation databases
The Two Query Language Standards
1. XPath 2.XQuery
The relational data model was developed in the _____
1970s
The _____ relationship should be rare in any relational database design
1:1
Assume you are using the MINUS operator to combine the results from two tables with identical structure, CUSTOMER and CUSTOMER_2. The CUSTOMER table contains 10 rows, while the CUSTOMER_2 table contains 7 rows. Customers Dunne and Olowski are included in the CUSTOMER table as well as in the CUSTOMER_2 table. How many records are returned when using the MINUS operator?
8
Normalisation
A Database design technique to: - Reduce the attributes required - Group logically related attributes - Minimize data redundancy
Serial Schedule
A Schedule where operations are executed without concurrency
Non-Recoverable Schedule
A Schedule which reads before a commit then wants to write after it.
Report
A business document that contains only predefined data; it is a passive document used only for reading or viewing. It typically contains data from many unrelated records or transactions.
Form
A business document that contains some predefined data and may include some areas where additional data are to be filled in. An instance on such a document is typically based on one database record.
Cache
A cache is a temporary area in memory created for a quick fetch of information that might otherwise take longer to retrieve. For example, the cache's mentioned in the preceding list contain pre-computed information. Instead of a user having to compute values every time, the user can access the information in a cache.
Identifier
A candidate key that has been selected as the unique, identifying characteristic for an entity type.
Equi-Join
A case of join using an '=' in the condition
repository
A centralized knowledge base for all data definitions, data relationships, screen and report formats, and other system components.
Entity type
A collection of entities that share common properties or characteristics
Entity Type
A collection of entities that share common properties or characteristics.
database application
A collection of forms, reports, queries, and application programs that facilitates users' processing of a database. A database can be processed by many different database applications.
Middleware
A combination of hardware, software, and communication technologies that brings together data management, presentation, and analysis into a three-tiered client/server environment.
IoT (Internet of Things)
A computing concept that describes a future where everyday physical objects will be connected to the Internet and be able to identify themselves to other devices.
Serialisable Schedule
A concurrent Schedule that executes the same as its Serial counterpart
Control (IDEF0)
A condition that governs the activity e.g., policies, when, how many, how fast, etc.
Participation
A constraint applied to a Relation determining the number of times an entity can be in that relation.
One-time cost
A cost associated with project start-up and development or system start-up.
Description
A data set representing a single item
Composition
A derivation of Armstrong's Axioms. if A → B and C → D then A, C → B, D
Decomposition
A derivation of Armstrong's Axioms. if A→ B, C then A → B and A → C
Partial functional dependency
A functional dependency in which one or more non-key attributes are functionally dependent on part (but not all) of the primary key
Top-down planning
A generic ISP methodology that attempts to gain a broad understanding of the information system needs of the entire organization.
Bottom-up planning
A generic ISP methodology that identifies and defines IS development projects based upon solving operational business problems or taking advantage of some business opportunities.
Supertype
A generic entity type that has a relationship with one or more subtypes
Object-based interaction
A human-computer interaction method whereby symbols are used to represent commands or functions
Command Line Interface
A human-computer interaction method whereby users enter explicit statements into a system to invoke operations
Left Outer Join R ⟕ p S
A join which returns all rows in R if they match or not, filling extraneous columns with NULL
Candidate Key
A key which is part of a relation schema with multiple keys. One must be designated a primary key, the rest secondary keys
Description
A labeled element of a tuple. ex "address" or "data of Birth"
What is a data lake?
A less structured/processed variant of a data warehouse designed to cater to data scientists; more agile and flexible with maximal data diversity
Parameter values
A method is passed or returns a value that was unexpected, such as a negative number for a price.
Locking
A method of controlling concurrent transactions by making Transactions request and release locks on data
Enhanced entity-relationship (EER) model
A model that has resulted from extending the original E-R model with new modeling constructs.
Attribute
A named property or characteristic of an entity that is of interest to the organization.
Physical table
A named set of rows and columns that specifies the fields in each row of the table.
Relation
A named, two-dimensional table of data. Each consists of a set of named columns and an arbitrary number of unnamed rows.
Entity
A person, place, object, event or concept in the user environment about which data is to be maintained.
Entity
A person, place, object, event, or a concept in the user environment about which the organization wishes to maintain data.
Client process Connection
A physical communication pathway between a client process and database instance.
Data flow diagram (DFD)
A picture of the movement of data between external entities and the processes and data stores within a system.
Composite key
A primary key that consists of more than one attribute
Enterprise key
A primary key whose value is unique across all relations
Technical feasibility
A process of assessing the development organization's ability to construct a proposed system.
Economic feasibility
A process of identifying the financial benefits and costs associated with a development project.
Attribute
A property or characteristic of an entity or relationship type that is of interest to the organization.
Second normal form (2NF)
A relation in first normal form in which every non-key attribute is fully functionally dependent on the primary key.
Unary relationship
A relationship between instances of a single entity type - An ER term used to describe an association within an entity. Example: A COURSE is a prerequisite to another COURSE.
Identifying Relationship
A relationship that relates a weak entity type to its owner
Recursive Relationship
A relationship type between the same entity type in multiple distinct roles
Constraint (SQL)
A rule associated with a column that the data entered into that column must follow. For example, a "unique" constraint specifies that no two records can have the same value in a particular column. They must all be unique. The other two most popular constraints are "not null" which specifies that a column can't be left blank, and "primary key". A "primary key" constraint defines a unique identification of each record (or row) in a table.
Partial specialization rule
A rule that specifies that an entity instance of a subtype is allowed not to belong to any subtype
Overlap rule
A rule that specifies that an instance of a supertype may simultaneously be a member of two (or more) subtypes
Disjoint rule
A rule that specifies that an instance of the supertype may not simultaneously be a member of two (or more) subtypes
Cardinality constraint
A rule that specifies the number of instances of one entity that can (or must) be associated with each instance of another entity.
surrogate primary key
A serial number or other systemized primary key for a relation (Usually created to simplify the key structures)
Objective statements
A series of statements that express an organization's qualitative and quantitative goals for reaching a desired future position.
Session
A session is a logical entity in the database instance memory that represents the state of a current user login to a database. For example, when a user is authenticated by the database with a password, a session is established for this user. A session lasts from the time the user is authenticated by the database until the time the user disconnects or exits the database application.
Cascading style sheets (CSS)
A set of style rules that tell a Web browser how to present a document.
Description
A set of tuples sharing the same attributes; a set of columns and rows
Ternary relationship
A simultaneous relationship among the instances of three entity types - Example: A CONTRIBUTOR contributes money to a FUND from which a RECIPIENT receives money.
Entity instance (instance)
A single occurrence of an entity type.
Help desk
A single point of contact for all user inquiries and problems about a particular information system or for all users in a particular department.
Repeating group
A situation that arises when an entity has two or more attributes that refer to the same underlying concept (e.g., Course1, Course2, Course3, etc.)
DBMS
A software system that is used to create, maintain, and provide controlled access to user databases (ie MySQL)
Null value
A special field value, distinct from a zero, blank, or any other value, that indicates that the value for the field is missing or otherwise unknown.
Systems development methodology
A standard process followed in an organization to conduct all the steps necessary to plan, analyze, design, implement, and maintain information systems.
Normal form
A state of relation that requires that certain rules regarding relationships between attributes (or functional dependencies) are satisfied
Business rule
A statement that defines or contains some aspect of the business. It is intended to assert business structure to control or influence the behavior of the business.
Incremental commitment
A strategy in systems analysis and design in which the project is reviewed after each phase and continuation of the project is re-justified.
Feasibility study
A study that determines if the proposed information system makes sense for the organization from an economic and operational standpoint.
Join Dependency
A table T is subject to a join dependency if T can always be recreated by joining multiple tables each having a subset of the attributes of T. If one of the tables in the join has all the attributes of the table T, the join dependency is called trivial.
File organization
A technique for physically arranging the records of a file.
Completeness constraint
A type of constraint that addresses whether an instance of a supertype must also be a member of at least one subtype
Break-even analysis
A type of cost-benefit analysis to identify at what point (if ever) benefits equal costs.
Mapping Unary 1:1 and 1:M Relationships
A unary 1:1 or 1:M relationship requires using an entity's primary key as a foreign key within the relational schema. Ex. MECHANIC(MechID, Salary, SupervisorID)
What is a data warehouse?
A unified, central repository of contemporary and historical data accumulated (i.e., copied) from a wide range of sources within a company for the purposes of BI or management decision making; highly structured and processed
Attribute
A value of an Entity that distinguishes it, i.e. a persons name, phone number and etc.
Oracle Block
An Oracle block is the minimum unit that Oracle will read or write at any given time.
When setting optimizer hints, _____ instructs the optimizer to minimize the overall execution time, that is, to minimize the time it takes to return the total number of rows in the query result set. This hint is generally used for batch mode processes
ALL_ROWS
Which of the following is a client-side extension?
ActiveX VBScript Java JavaScript Plug-ins
Hybrid
Acts as a multifunctional database. Most hybrid databases contain transactional, processing ad hoc querying, and batch processing. Larger databases that have service-level requirements are generally isolated to their own databases for performance and manageability reasons.
Three tier architecture
Adds an intermediate layer between the client and database server
Processes
Again, Oracle processes only exist when the instance is running. The running instance has some core mandatory processes, whereas others are optional, depending on what features are enabled. These processes typically show up on the OS process listing.
what are Online redo log files
Allow for instance recovery of the database. If the database server crashes and does not lose any data files, the instance can recover the database with the information in these files.
Alias
An alternative name used for an attribute
Triggering operation (trigger)
An assertion or rule that governs the validity of data manipulation operations such as insert, update, and delete.
Relationship
An association between entities
Strong entity type
An entity that exists independently of other entity types - instances of a strong entity type always have a unique characteristic called an identifier - that is an attribute or combination of attributes that uniquely distinguish each occurrence of that entity
Abstract entity
An entity that has an existence, but does not correspond to a tangible entity. For example, the film Citizen Kane is an abstract entity, while the DVD which records the film is a tangible entity.
Weak entity type
An entity type were existence depends on some other entity type
Anomaly
An error or inconsistency that may result when a user attempts to update the table that contains redundant data. The three types of anomalies are insertion, deletion, and modification anomalies.
Composite identifier
An identifier that consists of a composite attribute - Primary key composed of more than one attribute
N-tuple
An ordered list of n values were each value is an element of domain
Usability
An overall evaluation of how a system performs in supporting a particular user for a particular task.
Context diagram
An overview of an organizational system that shows the system boundary, external entities that interact with the system, and the major information flows between the entities and the system
The database initial study phase of the Database Life Cycle (DBLC) involves _____
Analyze the company situation Define problems and constraints Define objectives Define scope and boundaries
Value chain analysis
Analyzing an organization's activities to determine where value is added to products and/or services and the costs incurred for doing so.
Resources
Any person, group of people, piece of equipment, or material used in accomplishing an activity.
Description
Any set of tuples; a data report from the RDBMS in response to a query
Pessimistic Concurrency
Assumes frequency conflicts & locking is prone uses timestamping
ACID
Atomic consistent isolate durable
_____ requires that all operations of a transaction be completed
Atomicity
ACID Properties
Atomicity Consistency Preservation Isolation Durability/Permanency
ACID
Atomicity, Consistency, Isolation, Durability
Relational database term for column
Attribute or field
To generate a surrogate key, Microsoft Access uses a(n) _____ data type
AutoNumber
Memory Management
Automatic Memory Management (AMM) Automatic Shared Memory Management (ASMM) Manual Shared Memory Management (MSMM)
The syntax for creating an index is _____
CREATE [UNIQUE] INDEX indexname ON tablename( column1 [, column2]);
Phased installation
Changing from the old information system to the new one incrementally, starting with one or a few functional components and then gradually extending the installation to cover the whole new system.
what are Archived redo log files
Contain an ongoing history of the data changes (redo) that are generated by the instance. Using these files and a backup of the database, you can recover a lost data file. That is, archive logs enable the recovery of restored data files.
what are data files
Contain the user or application data of the database, as well as metadata and the data dictionary
Control Files
Control files contain the following things: Names and locations of your data files and redo log files Recovery information Backup information Checkpoint information Archiving information Database name Log history Current logging information
The PL/SQL block starts with the _____ section
DECLARE
Data store
Data at rest, which may take the form of many different physical representations
Three main types of XML documents
Data centric, document centric, hybrid
DDL
Data definition language. Used by DBA and database designers to define both schemas
Virtual Data
Data derived from (though not explicitly stored in) the Database
_____ can render data useless to unauthorized users who might have violated some of the database security layers
Data encryption
Physical storage
Data files control files Online redo log files
_____ can serve as a test vehicle for companies exploring the potential benefits of data warehouses
Data marts
Functional dependencies
Data redundancy is often associated with functional dependencies (FDs) A functional dependency occurs when values in certain columns can be used to determine values in other columns
Describe DBWn
Database Writer Process The DBWn processes are responsible for writing modified (dirty) buffers in the database buffer cache to disk. DBWn writes non- frequently used buffers to the data files from the tail of the LRU list so that processes can replace buffers when they need them. DBWn also writes from the tail of the checkpoint queue to keep the checkpoint advancing.
Database schema
Description of the database
parsing
Each time you execute a SQL statement, a lot happens in the background. This background activity is called parsing. Parsing can be quite expensive. During parsing, some of these things happen: The statement syntax is checked to make sure you typed everything correctly. The objects you're referring to are checked. For example, if you're trying access a table called emp, Oracle makes sure it exists in the database. Oracle makes sure that you have permission to do what you're trying to do. The code is converted into a database-ready format. The format is called byte-code or p-code. Oracle determines the optimum path or plan. This is by far the most expensive part.
Strict 2PL
Each transaction keep exclusive locks until a commit or rollback
Describe the UGA
Found in the PGA The UGA is composed of the following: • Cursor area for storing runtime information on cursors • User session data storage area for control information about a session • SQL working areas for processing SQL statements consisting of: - A sort area for functions that order data such as ORDER BY and GROUP BY - A hash area for performing hash joins of tables - A create bitmap area used in bitmap index creation common to data warehouses - A bitmap merge area used for resolving bitmap index plan execution In a shared server environment, multiple client users share the server process. In this model, the UGA is moved into the SGA (shared pool or large pool if configured) leaving the PGA with only stack space.
Database utility
Functions that help the DBA manage the database system
Functional dependancy closure
Given a set of FDs, F, the closure of F is often notated as F+ Given an existing set of FDs, these rules may also be used to determine any FDs that follow based on that set. This is referred to as finding the closure of a given set of FDs
Build routines
Guidelines that list the instructions to construct an executable system from the baseline source code.
Snn or Shared Server Process back
Handles client requests in the shared server architecture. In the shared server architecture, clients connect to a dispatcher process, which creates a virtual circuit for each connection. When the client sends data to the server, the dispatcher receives the data into the virtual circuit and places the active circuit on the common queue to be picked up by an idle shared server. The shared server then reads the data from the virtual circuit and performs the database work necessary to complete the request. When the shared server must send data to the client, the server writes the data back into the virtual circuit and the dispatcher sends the data to the client. After the shared server completes the client request, the server releases the virtual circuit back to the dispatcher and is free to handle other clients. Several initialization parameters relate to shared servers. The principal parameters are: DISPATCHERS, SHARED_SERVERS, MAX_SHARED_SERVERS, LOCAL_LISTENER, REMOTE_LISTENER.
Host language
Hi and low level DML commands that are embedded
Query language
High level DML that can be used in standalone interactive manner
Transaction Management
How a DBMS ensures the integrity of a database with faults and concurrency
Query Processing
How an SQL Query is executed, using relational algebra and indexing
Reflexivity
If Y ⊆ X, then X → Y In other words, an attribute always determines itself Reflexivity relates to trivial functional dependencies: FDs that include themselves (e.g., X → X or X, Y → X). Again, these are typically omitted from FD sets because they add little information
Data management
Involves designing, using, and managing memory systems of modern organizations
what is a parameter file
Is used to define how the instance is configured when it starts up
Java pool
It stores all session-specific java code and data writhin the Java virtual machine.
Database Buffer Cache
It stores copies of data blocks read from data files. A buffer is an address where the buffer manager temporarily caches a currently or recently used data blocks. It is optimize physical I/O; to keep frequently accessed blocks in the buffer chache; and to write infrequently accessed blocks to disk.
The entity relationship model uses the associative entity to represent a(n) _____ relationship between two or more entities
M:N
Aggregate Functions
MIN returns the smallest value in a given column MAX returns the largest value in a given column SUM returns the sum of the numeric values in a given column AVG returns the average value of a given column COUNT returns the total number of values in a given column COUNT(*) returns the number of rows in a table
The _____ statement in SQL combines rows from two queries and returns only the rows that appear in the first set but not in the second
MINUS
Information for operations support (70s, early 80s)
MIS ("information factory") churning out reports for managers MIS: management information system
When using the Oracle TO_DATE function, the code _____ represents a three-letter month name
MON
Trivial Multivalued Dependency
MVD where Y is a subset of X
Relation schema
Made up of a relation name and a list of attributes
Cardinality possibilities
Mandatory one mandatory many optional one optional many
Data centric
Many small data items that follow A specific structure - formatted as XML to exchange or display on web -usually follow a predefined schema
Hybrid
May have parts that contain structured data and other parts that are predominantly textual or unstructured -May or may not have predefined schema
_____ provide a description of the data characteristics and the set of relationships that link the data found within the database
Metadata
Which of the following is a personal analytics vendor for BI applications?
MicroStrategy, QlikView, and Actuate
Which of the following types of HDFS nodes stores all the metadata about a file system?
Name node
Attribute
Name of a role played by some domain in the schema. Column header
Costs of the database approach
Need new, specialized personnel Need for explicit backup Interference with shared data Possibility of organizational conflict
Problem with ternary relationship
No hierarchy between entities Hard to understand cardinality with ternary relationship. Just don't draw it usually if they're different Primary identifier can be created from multiple attributes
Three types of maximum cardinality
One to one 1:1 One to many 1:M Many to many M:N
Archived Logs database
Oracle Database lets you save filled groups of redo log files to one or more offline destinations, known collectively as the archived redo log, or more simply the archive log. The process of turning redo log files into archived redo log files is called archiving. This process is only possible if the database is running in ARCHIVELOG mode. You can choose automatic or manual archiving.
Oracle Flashback
Oracle Flashback, a means of retrieving data as it existed in an Oracle database at an earlier time
Database Schema
Organization of individual stored data items into higher level groups, such as tables Associations among tables or classes Details of physical data store organization, including types, lengths, locations, and indexing of data items Access and content controls, including allowable values for specific data items, value dependencies among multiple data items, and lists of users allowed to read or update data items
Server and Initialization parameter files
PFILEs and SPFILEs have information about how your running database is configured. This is where you configure the following settings: Memory size Database and instance name Archiving parameters Processes Over 1,900 other parameters Whenever you start your database, the very first file read is the parameter file. It sets up all your memory and process settings and tells the instance where the control files are located. It also has information about your archiving status.
Background Processes
PMON SMON Database writer process Log writer process Check Point process MMON AND MMNL RECO
Recovery Manager
Part of a DBMS that is responsible for commits and rollbacks, also makes sure Transactions are atomic and durable.
SMON or System Monitor Process back
Performs critical tasks such as instance recovery and dead transaction recovery, and maintenance tasks such as temporary space reclamation, data dictionary cleanup, and undo tablespace management SMON performs many database maintenance tasks, including the following: * Creates and manages the temporary tablespace metadata * Reclaims space used by orphaned temporary segments * Maintains the undo tablespace by onlining, offlining, and shrinking the undo segments based on undo space usage statistics * Cleans up the data dictionary when it is in a transient and inconsistent state * Maintains the SCN to time mapping table used to support Oracle Flashback features In an Oracle RAC database, the SMON process of one instance can perform instance recovery for other instances that have failed. SMON is resilient to internal and external errors raised during background activities.
Open Database Connectivity
Provides an API through which client-side programs call DBMS
Personalization
Providing Internet content to users based upon knowledge of that customer.
Support
Providing ongoing educational and problem-solving assistance to information system users. Support material and jobs must be designed along with the associated information system.
QMNC or streams AQ Monitor coordinator back
QMNC is responsible for facilitating various background activities required by AQ and Oracle Streams: time management of messages, management of nonpersistent queues, cleanup of resources, and so on. QMNC dynamically spawns Qnnn processes as needed for performing these tasks.
RVWR or Recovery Writer back
RVWR writes flashback data from the flashback buffer in the SGA to the flashback logs. RVWR also creates flashback logs and performs some tasks for flashback log automatic management.
Integrity Constraints/Business Rules
Referential Integrity Constraint - "Every x record must be related to a y record" Key/Uniqueness Constraint - "Every x record must have a unique value for y attribute"
Mapping Aggregation
Relationships involving aggregations should reference all necessary primary keys from inside the aggregation Ex. STUDENT(StudentID, StudentName) COURSE(CourseID, CourseName) PROFESSOR(ProfID, ProfName) TAKES(StudentID, CourseID) TEACHES(ProfID, StudentID, CourseID
The _____ isolation level ensures that queries return consistent results
Repeatable Read
Using DBMS to store document contents as data elements
Requires mapping algorithms to design schema compatible with XML Structure
Referential Integrity
Restriction, Cascade, and nullification are methods to resolve violations of what...
Difference R - S
Returns all rows in R which do not exist in S, R and S must be Union Compatible.
Select σ p (R)
Returns all the rows which satisfies the condition p
Tuple
Row in table. And ordered set of attributes. Values derived from an appropriate domain
According to Codd's _____ rule of relational database, "Application programs and ad hoc facilities are logically unaffected when changes are made to the table structures that preserve the original table values (changing order of columns or inserting columns)."
Rule of Logical Data Independence
Parallel installation
Running the old information system and the new one at the same time until management decides the old system can be turned off.
To be considered minimally relational, the DBMS must support the key relational operators _____, PROJECT, and JOIN
SELECT
Which of the following is used to select partial table contents?
SELECT <column(s)> FROM <Table name> WHERE <Conditions>;
The query used to list the P_CODE, P_DESCRIPT, P_INDATE, and P_PRICE fields from the PRODUCT table in ascending order by P_PRICE is _____
SELECT P_CODE, P_DESCRIPT, P_INDATE, P_PRICE FROM PRODUCT ORDER BY P_PRICE;
The current fully approved version of standard SQL prescribed by the ANSI is _____
SQL-2003
Mapping Ternary Relationships
STUDENT(StudentID, StudentName) COURSE(CourseID, CourseName) PROFESSOR(ProfID, ProfName) RELATESTO(StudentID, CourseID, ProfID)
The SQL aggregate function that gives the total of all values for a selected attribute in a given column is _____
SUM
Self describing data
Schema information is mixed in with data values
3 Level Architecture
Separation of the user's view and the physical representation of a Database. External > Conceptual > Internal.
Server Processes
Server processes perform work based on a client request.
MMAN / MMon or Memory Manager Process back
Serves as the instance memory manager. This process performs the resizing of memory components on the instance.
define Session
Session: Specific connection of a user to an instance through a user process A session represents the state of a current user login to the database instance
Total Specialization
Specifies that each entity instance of the supertype must be a member of some subtype in the relationship.
VDL
Specifies user views and their mappings to the conceptual schema
SuperKey
Subset of the relation attributes where all tuple values must be distinct
data
Symbols, characters, images and numbers are all types of data. When data is processed and acquires meaning it becomes information. Computers process data to produce information
Describe the SMON
System Monitor Process • Performs recovery at instance startup • Cleans up unused temporary segments SMON checks regularly to see whether the process is needed. Other processes can call SMON if they detect a need for it.
External documentation
System documentation that includes the outcome of structured diagramming techniques, such as data flow and entity-relationship diagrams.
OOAD
Systems development methodologies and techniques based on objects rather than data or processes.
In Oracle, the _____ function converts a date to a character string
TO_CHAR()
Databases record data but they do so in such a way
That we can produce information from the data The data on STUDENTs, CLASSs, and GRADEs could produce information about each student's GPA
Dependent attribute
The attribute to the right side of the functional dependency
Data independence
The capacity to change the schema at one level of a database system
Which of the following is the first step in the exchange between a web browser and a database?
The client's Web browser requests a page from a Web server
DBW or Database Writer back
The database writer process (DBWn) writes the contents of buffers to datafiles. The DBWn processes are responsible for writing modified (dirty) buffers in the database buffer cache to disk. Although one database writer process (DBW0) is adequate for most systems, you can configure additional processes (DBW1 through DBW9 and DBWa through DBWj) to improve write performance if your system modifies data heavily. These additional DBWn processes are not useful on uniprocessor systems.
Impedance Mismatch Problem
The incompatibility between the DBMS' data structures and the programming languages' data structures
Insert (SQL)
The insert statement is used to insert or add a row of data into the table. General: insert into "tablename" (first_column,...last_column) values (first_value,...last_value); ex. insert into employee (first, last, age, address, city, state) values ('Luke', 'Duke', 45, '2130 Boars Nest', 'Hazard Co', 'Georgia'); Note: All strings should be enclosed between single quotes: 'string'
Business case
The justification for an information system, presented in terms of the tangible and intangible economic benefits and costs, and the technical and organizational feasibility of the proposed system.
Schema Evolution
The modification of a schema, usually in favor of storing additional information
DBMS Throughput
The number of operations executed on a Database in a given time interval
Integration testing
The process of bringing together all of the modules that a program comprises for testing purposes. Modules are typically integrated in a top-down, incremental fashion.
Normalization
The process of converting complex data structures into simple, stable data structures.
Generalization
The process of defining a more general entity type from a set of more specialized entity types - an example would be looking at three entity types: CAR, TRUCK, and MOTORCYCLE and deriving their supertype of VEHICLE (bottom up approach)
Specialization
The process of defining one or more subtypes of the supertype and forming supertype/subtype relationships. (top-down approach)
Gap analysis
The process of discovering discrepancies between two or more sets of DFDs or discrepancies within a single DFD
Work breakdown structure
The process of dividing the project into manageable tasks and logically ordering them to ensure a smooth evolution between tasks.
Concurrency Control
The process of running simultaneous transactions, making sure they do not interfere with each other. Can be Optimistic or Pessimistic
Analysis
The second phase of the SDLC in which system requirements are studied and structured.
Project planning
The second phase of the project management process that focuses on defining clear, discrete activities and the work needed to complete each activity within a single project.
Field
The smallest unit of named application data recognized by system software.
HTML (Hypertext Markup Language)
The standard language for representing content on the Web through the use of hundreds of command tags.
Traditional Approach vs. Database Approach
The traditional approach is process oriented, a characteristic of the file processing approach The database approach is data-driven rather than process driven The database approach emphasizes the integration and sharing of data across the organization
SDLC
The traditional methodology used to develop, maintain, and replace information systems.
Importance of databases
The use of databases by Web applications and smartphone apps is illustrated here. In this figure, people have computers (desktop or notebook) and smartphones, which are examples of devices used by people, who are referred to as users. On these devices are client applications (Web browsers, apps) used by people to obtain services such are searching, browsing, on-line purchasing, and tweeting over the Internet or cell phone networks. These services are provided by server computers, and these are the computers that hold the databases containing the data needed by the client applications.
Online analytical processing (OLAP)
The use of graphical software tools that provide complex analysis of data stored in a database.
Reuse
The use of previously written software resources, especially objects and components, in new applications.
Lightweight graphics
The use of small, simple images to allow a Web page to be displayed more quickly.
Client process
These process run the software code for an application program or an oracle tool. Client process interact with serve processes in order to interface with the database. When the client process runs on the client machine, it is knows as two tier architecture.
Starvation
Transaction has to keep waiting or restarts, and never gets to proceed further
Two Phase Locking
Transactions run in 3 stages, building up locks, running operations, then releasing its locks. This removes dirty reads and data inconsistency
Aggregation
Treating a Relation between Entities as an Entity by itself and then having a 3rd Entity reference that relation. Can be used instead of a Ternary Relation.
trivial vs non trivial functional dependancies
Trivial functional dependencies are typically omitted from FD sets because they add little information. The more meaningful alternative to trivial FDs are non-trivial functional dependencies: FDs in which X → Y holds but Y is not a subset of X, such as CourseID → Topic, Title or Major → School
Single-location installation
Trying out a new information system at one site and using the experience to decide if and how the new system should be deployed throughout the organization.
Updatable databases
Typically the operational databases of a company, such as the online transaction processing (OLTP) system
_____ data exist in the format in which they were collected
Unstructured
application middleware
What is essential in multi-tier application development?
repository vs. database
When I worked on repository software, many years ago. Back then, the difference between (general purpose) databases and repositories was the difference between "data" and "meta-data". So, a database stores data. A repository is a special class of database which is designed to store meta-data, that is, data that describes other data.
Transaction Starvation
When a Transaction is chosen as the victim too many times and it never executes
Referential integrity constraint
a constraint, stored in the schema, that the DBMS uses to automatically enforce referential integrity
Communications Software
allows users at locations different from the database storage site to access the database from terminals, workstations, or pcs connected to the database site through data communications hardware such as internet routers, phone lines, networks, or satellites many work with the DBMS software; if integrated it is called the DB/DC system
"What are the requirements of the current system's end users?" is a question asked during the _____ phase of the Systems Development Life Cycle (SDLC)
analysis
The logical systems design is created during the _____ phase of the Systems Development Life Cycle (SDLC)
analysis
Database design from existing data
analyze spreadsheets and other data tables extract data from other databases design using normalization principles
Associative entity
can be used to help map M:N relationships while also serving as its own entity (making it an alternative to a relationship with attributes) Can have a unique identifier and should also have other attributes May participate in other relationships other than the entities of the associated relationship looks like diamond and box
Distributed DBMS
can have actual database/DBMS software distributed over many sites connected by a computer network Ex: big-data systems are massively distributed and replicated so failure with the system will not make the data lost/unavailable
Specialized Servers
certain servers that are dedicated to a specific function
The database structure in a DBMS is stored as a
collection of files
Query Compiler
compiler that compiles queries into an internal form which is subject to query optimization
The conflicts between design efficiency, information requirements, and performance are often resolved through _____
compromises that include denormalization
open systems
computer systems that provide some combination of interoperability, portability, and open software standards
network
connects the computers; can vary in speed
Logical data
consists of data only veiwable from within the database itself.
Cloud Computing
consists of thousands of large servers managing the big data on the web
Physical data
consists of what is viewable at the operating level eg, datafiles can be listed using IS command
Stored Data Manager Module
controls access to DBMS info that is stored on the disk, whether it is part of the database or the catolog
A derived attribute is indicated in the Chen notation by a _____ that connects the attribute and an entity
dashed line
The _____ must be set large enough to permit as many data requests to be serviced from cache as possible
data cache
The statement SELECT * FROM T1, T2 produces a(n) _____ join
cross
Bill Inmon and Chuck Kelley created a set of 12 rules to define a(n) _____
data warehouse
The term _____ refers to an organization of components that define and regulate the collection, storage, management and use of data within a database environment
database system
Internal Level/Schema
describes the physical storage structure of the database schema uses a physical data model & describes complete details of storage/access paths for the database
Database Schema
description of the database is specified during database design & is not expected to change frequently mostly displayed as diagrams sometimes called the intension; database is an extension
Client Module
designed so that it will run on a mobile device, workstation, or PC; typically used in applications and user interfaces that access the database; handles user interaction & provides a GUI
The design of the system's processes is completed during the _____ phase of the Systems Development Life Cycle (SDLC)
detailed systems design
Disjointess contraint:
determines whether a supertype can have one or more than one subtype Disjoint Rule: each supertype may have only one subtype Indicated with a letter 'd' in the circle Overlap Rule: a supertype may have more than one subtype Indicated with a letter 'o' in the circle
Completeness Constraint
determines whether or not a supertype must have a corresponding subtype total specialization rule partial specializatin rule
A diskpage, or page, is the equivalent of a _____
diskblock
Schema Construct
each object in the schema
instance
each row in a table stores data about an occurrence (or instance) of the thing of interest.
Total Specialization Rule
each supertype must have a subtype Indicated with a double line to the circle Indicated with a double line to the circle
scalable
easy to increase or decrease the storage and processing capabilities of the servers
A system table space, a user data table space, an index table space, and a temporary table space are examples of _____
file groups
What is the Recycle Pool
found in the Database Buffer Cache in the SGA. The recycle buffer pool is designed to flush buffers from memory faster than the LRU normally would.
The Database Buffer Cache contains what?
found in the SGA. Holds copies of data blocks that are read from data files Contains keep pool, recycle pool, nK buffer cache
describe the Sever Results Cache
found in the shared pool in SGA. contains the SQL query result cache and PL/SQL function result cache, which share the same infrastructure. The server result cache contains result sets, not data blocks. Saves memory on repeat SQL results
describe the Shared SQL area
found in the shared pool in SGA. all SQL statements run through the Shared SQL area. Oracle recognizes when two users use same SQL. Saves memory on repeat SQL statements.
describe the Reserved Pool
found in the shared pool in the SGA can use to allocate large contiguous chunks of memory.
Query Language
high-level DML used in a standalone interactive manner retrieval & update commands are considered part of this language
In a database context, the word _____ indicates the use of the same attribute name to label different attributes
homonym
customization requirements
how much of the application will be controlled by a central group and how much of the application will be managed locally
The first step in building an entity-relationship diagram (ERD) is _____
identifying the attributes and primary keys that adequately describe the entities
Augmentation
if X → Y, then XZ → YZ In other words, that StudentID and Major together determine both Name and Major
MVD (Multivalued Dependency)
if two tuples t1, t2 exist in R, t1[X] = t2[X] for attribute subset X t3[X] = t4[X] = t1[X] = t2[X]. t3[Y] = t1[Y] and t4[Y] = t2[Y]. t3[Z] = t2[Z] and t4[Z] = t1[Z].
The database contents are loaded during the _____ phase of the Systems Development Life Cycle (SDLC)
implementation
The _____ manager is responsible for assigning and policing the locks used by the transactions
lock manager
Application logic
logic and processing required to support actual business application and rules
application logic
logic documented in the DFDs, use cases and functional requirements
File Servers
maintain the files of the client machines
The last step in the Database Life Cycle (DBLC) is _____
maintenance and evolution
When designing a database, you should
make sure that entities are in normal form before table structures are created
A _____ is a dynamic table that not only contains the SQL query command to generate the rows, but also stores the actual rows
materialized view
Controls
mechanisms and procedures that are built into a system to safeguard the system and the information within it
XPath axes
move in multiple directions from current node in path expression. -include self, child, descendent, attribute, parent, ancestor, previous sibling, and next sibling
Partitioned database server architecture
multiple distributed database servers are used and the database schema is partitioned
Online Transaction Processing (OLTP) Systems
must support a large number of concurrent transactions without imposing excessive delays
A _____ key is a real-world, generally accepted identifier used to uniquely identify real-world objects
natural
A(n) _____ join links tables by selecting only the rows with common values in their common attribute(s)
natural
A(n) _____ join will select only the rows with matching values in the common attribute(s)
natural
Surrogate primary keys are especially helpful when there is no _____ key
natural
Rules for primary key
o There is only one primary key per relation o The primary key may be a composite key o The ideal primary key is short, numeric, and never changes
A consistent database state is _____
one in which all data integrity constraints are satisfied
symmetric encryption algorithm
one in which the key used to encrypt a message is the same as one used to decrypt it
Single database server architecture
one or more databases are hosted by a single DBMS running on a single server
The _____ process analyzes SQL queries and finds the most efficient way to access data
optimizer
The existence of a(n) _____ entity indicates that its minimum cardinality is zero
optional
If Tiny College has some departments that are classified as "research only" and do not offer courses, the COURSE entity of the college database would be _____ the DEPARTMENT entity
optional to
A(n) _____ join returns not only the rows matching the join condition (that is, rows with matching values in the common columns) but also the rows with unmatched values
outer
In subquery terminology, the first query in the SQL statement is known as the _____ query
outer
In the context of total completeness, in a(n) _____, every supertype occurrence is a member of at least one subtype
overlapping constraint
A(n) _____ data dictionary is not updated automatically and usually requires a batch process to be run
passive
system developers
persons such as system analysts and programmers who design new applications
The preferred placement for a foreign key when working with a 1:1 relationship is to
place the foreign key in one of the entities
architecture design
plan for how the information system components will be distributed across multiple computers and what hardware, operating system software and application software will be used on each computer
"All users must have passwords" is an example of a _____
policy
Entity-relationship Model
popular high-level conceptual data model
Instructions to create a password are an example of a _____
procedure
Mappings
process of transforming requests & results between levels may be time consuming, so some do not support external views, but it is still necessary to transform results between levels
host-based architecture or mainframe system
processing is performed in the host computer only
data access logic
processing required to access data, often meaning database queries in SQL
Application Development Environments
quite popular systems that provide an environment for developing database applications & include facilities that help in many facets of database systems help with design, GUI, querring, updating, & program development
In the optimistic approach, during the _____ phase, a transaction scans the database, executes the needed computations, and makes the updates to a private copy of the database values
read
Data
recorded facts and figures
If an employee within an EMPLOYEE entity has a relationship with itself, that relationship is known as a _____ relationship
recursive
virtualization
refers to the creation of a virtual device or resource such as a server or storage device
Data Abstraction
refers to the suppression of organization/storage details, highlighting of essential features for better understanding of data; users can perceive data at different detail levels
Java Database Connectivity (JDBC)
related to the ODBC, but for java client programs
RDBMS
relational database management system accessed using SQL ex. MS SQL Server, IBM DB2, Oracle, MySQL, Microsoft Access, Postgres, Informix interfaces with SQL
Attribute
represents some property of interest that further describes an entity
elasticity
resources allocated can be increased or decreased on demand
Second normal form (2NF)
restriction that a table is in 1NF and that each non-key attribute is functionally dependent on the entire primary key
Third normal form (3NF)
restriction that a table is in 2NF and that no non-key attribute is functionally dependent on any other non-key attribute
First normal form (1NF)
restriction that all rows of a table must contain the same number of columns (No Repeating attributes)
Low-level/Procedural DML
retrieves individual records/objects form the database & processes each one separately needs to use programming language constructs to retrieve & process each record separatley from a set of records; called record-at-a-time due to this must be embedded in a general-purpose programming language
XPath expression
returns a sequence of items that satisfy a certain pattern. -values are leaf nodes, elements, or attributes -qualfier condition: further restrict nodes that satisfy pattern
In the context of the Oracle Enterprise Manager, a(n) _____ is a named collection of database access privileges that authorize a user to connect to the database and use the database system resources
role
A _____ lock allows concurrent transactions to access different rows of the same table
row-level
vertical partitioning
some columns of a relation are project in one site and another set of columns in another site
horizontal partitioning
some rows of a relation are put in one site and another set of rows in another site
middleware
sometimes called plumbing because it connects two sides of an application and passes data between them
Subtypes and supertypes
specialization/ generalization All attributes of a supertype are inherited by each of its subtypes, including a common identifier Subtypes have their own unique attributes beyond those of the supertype Indicated using a circle and connecting lines, along with a U symbol indicating direction Every supertype have to have a common identifier
cultural and political requirements
specific to the countries in which the system will be used
physical schema
specifications for how data from a logical schema are stored in a computer's secondary memory by a database management system.
W in FLWR
specifies any conditions in XQuery
R in FLWR
specifies the elements to be retrieved in XQuery
Defining A Database
specify its database schema only to the DBMS database state is empty
XML Schema Language
standard for specifying structure of XML Documents
gateway
the computer that routes the traffic from a workstation to the outside network that is serving the web pages
describe the data dictionary
the data dictionary is found in the Shared pool in the SGA. It is both in the data dictionary cache (row cache: holds data as rows instead of buffers) and in the library cache. It is a collection of database tables and views containing reference info about the database, its structures, and users
data warehouse vs. ERP system
the data warehouse is a central repository that gathers data from the various operational databases. the data warehouse is then integrated into decision support database vs. an enterprise resource planning system which is the ability to deliver an integrated suite of business applications. Erp tools are more-so a black box software suite in comparison (take peoplesoft for example).
presentation logic
the display of information to the user and the acceptance of the user's commands (the user interface)
What is a data block?
the finest level of granularity. Oracle database data is stored in data blocks. One data block corresponds to a specific number of bytes of physical space on the disk
client computers
the input-output devices employed the user and are usually desktop or laptop computers, but can also be handheld devices, smartphones, tablet devices, special-purpose terminals and so on
Decryption (part of Data Encryption, Security Controls)
the process of converting encrypted data back to their original state
Authentication (part of access controls)
the process of identifying users who request access to sensitive resources
concurrent multilingual system
the same system supports several different languages simultaneously
T/F a relationship and also be an entity
true
Server Module
typically handles the data storage, access, search, etc
Servers
typically larger multi-user computers used to store software and data that can be access by anyone who has permission
A _____ relationship exists when an association is maintained within a single entity
unary
Cloud-based database server architecture
use of a cloud computing service provider to provide some or all database services
Storage Definition Language (SDL)
used to specify the internal schema, when the separation of the schemas is clear specifies the internal schema by a combination of functions, parameters & specs related to storage of files permits the DBA to control indexing choices & mapping of data storage
Client
user machine that provides the interface capabilities and local processing needed when it requires access to additional functionality, it connects to the appropriate server and provides the functionality
What are the three major Process groups in an oracle database system
user processes, database processes, Daemon/Application Processes
storage area network (SAN)
uses storage virtualization to create a high-speed subnetwork of shared storage devices
A _____ entity has a primary key that is partially or totally derived from the parent entity in the relationship.
weak
A _____ entity has a primary key that is partially or totally derived from the parent entity in the relationship
weak entity
Special Purpose DBMS
when performance is a primary consideration, one can be built for a specific application & cannot be used for another application without major changes ex: airline reservations, telephone directories, etc...; falls into the OLTP category
The implicit beginning of a transaction is _____
when the first SQL statement is encountered
Column names are written
with an initial capital letter, and compound names are written with a capital letter on each word: - Term, Section, ClassNumber, StudentName
Multiple attributes as key
you now have multiple places to introduce an error
Enterprise data warehouse (EDW)
A centralized, integrated data warehouse that is the control point and single source of all data made available to end users for decision support applications throughout the entire organization.
Controlled Redundancy
Using tasteful amounts of redundancy to improve query performance. For instance, storing the student_name and course_number redundantly in a grade_report file, since such info will probably be wanted any time a grade_report is retrieved
_____ code is embedded inside an HTML page and is activated by triggering events such as clicking on a link
VBScript
Another name for a database server-side extension program is _____
Web-to-database middleware
Host Language
When DML commands are embedded in a general-purpose programming language DML is the Data Sublanguage
Aborted
When a Transaction failed and the Database's state is rolled back to before the transaction was started
Isolation
When a Transaction is executed the result is always the same whether other Transactions are running at the same time.
Lost Update Problem
When a Transaction overrides an update operation that was just performed
Atomicity
When all or none of a transaction's operations are executed
Transitive Dependency
When an attribute is inferred through another attribute rather than directly from the Primary Key
Partial Dependency
When an attribute only depends on a subset of the primary key
Complex attribute
When you represent arbitrary nesting by grouping composite with parentheses and separating components with commas and by displaying multi value attributes between braces
XML Data Model
has emerged as a standard for exchanging data over the web & has been used as a basis for implementing several prototype native XML systems uses a tree hierarchy, combines database concepts with document representational models data is represented by elements, tags can nest data to make the tree more complex conceptually resembles the object model, but uses different terminology
A _____ key can be described as a minimal superkey, a superkey without any unnecessary attributes
candidate
data storage
data entities documented in ERDs
A _____ is optimized for decision support and is generally represented by a data warehouse or a data mart
data store
Index
data structure used to speed up retrieval of info. -stores in DB alongside data -contains references to data
Schema
database component that contains descriptive information about the data stored in the physical data store
The _____ schema must support complex (non-normalized) data representations
decision support database (chapter 13 - 574)
portability requirements
define how the technical operating environments may evolve over time and how the system must respond
Augmentation
if A → B then A, C → B, C
Reflexivity
if A ⊆ B then B ⊆ A
Fifth Normal Form
if and only if every join dependency in it is implied by the candidate keys
relational database
A database that uses more than one table. Tables are linked together by common data items, such as ID number, known as keys.
Operation Composition
Using multiple Relational operations which feed into each other
Downside to clustering
-Only one per table allowed -have to arrange tables on inserts, deletes, etc..
thick client (fat client)
-a networked computer with most resources installed locally -provides rich functionality independent of the central server
client
-a piece of computer hardware or software that accesses a service made available by a server -presents the user interface -forms queries or commands in specific language -may be thin or thick client
distributed presentation
-a solution in which the presentation layer (and probably presentation logic) is shifted from server of a legacy system to GUI client -sometimes called the poor person's client/server system
homogeneous distributed database
-all sites have identical software and aware of each other and agree to cooperate in processing user requests -each site surrenders part of its autonomy in terms of right to change schema or software
presentation middleware
-allows a programmer to build UI components that can talk to web browsers or desktop GUI -corresponds to presentation logic framework
database middleware
-allows a programmer to pass SQL commands to a database engine for processing -corresponds to the data manipulation logic framework
Application server
A computing server where data analysis functions primarily reside.
Use case
A depiction of a system's behavior or functionality under various conditions as the system responds to requests from users
Weak Entity
An entity that cannot be defined by its attributes alone (since it lacks a key attribute), and requires a foreign key
Associative entity
An entity type that associates the instances of one or more entity types and contains attributes that are peculiar to the relationship between those entity instances.
Baseline project plan
An estimate of the project's tasks and resource requirements and is used to guide project execution
Transaction
An executing program that forms a logical unit of database processing. Includes access operations.
subtype discriminators
Hourly- H Salaried- S Consultant- C The letters H, S, and C indicate subtype discriminators
Data Independence
Logical Data Independence - The ability to change the conceptual schema without changing external schemas/application programs Physical Data Independence - The ability to change the internal/physical schema without changing the conceptual schema.
The query to join the P_DESCRIPT and P_PRICE fields from the PRODUCT table and the V_NAME, V_AREACODE, V_PHONE and V_CONTACT fields from the VENDOR table, where the values of V_CODE match and the output is ordered by the price is _____
SELECT PRODUCT.P_DESCRIPT, PRODUCT.P_PRICE, VENDOR.V_NAME, VENDOR.V_CONTACT, VENDOR.V_AREACODE, VENDOR.V_PHONE FROM PRODUCT, VENDOR WHERE PRODUCT.V_CODE = VENDOR.V_CODE; ORDER BY PRODUCT.P_PRICE;
Which of the following queries will list all the rows in which the inventory stock dates occur on or after January 20, 2010?
SELECT P_DESCRIPT, P_QOH, P_MIN, P_PRICE, P_INDATE FROM PRODUCT WHERE P_INDATE >= '20-JAN-2010';
Slave Processes
Slave processes perform additional tasks for a background or server process.
CASE tools
Software tools that provide automated support for some portion of the systems development process.
Concurrency Control
Software which ensures that users trying to simultaneously update the database do so in a controlled manner
Which of the following is the last step in the exchange between a web browser and a database?
The HTML output is displayed on the client computer's web browser
Electronic data interchange (EDI)
The use of telecommunications technologies to directly transfer business documents between organizations
Template-based HTML
The use of templates to display and process common attributes of high-level, more abstract items.
Null
The value that may be assigned to an attribute when no other value applies or when the applicable value is unknown
A(n) _____ is the set of possible values for a given attribute
domain
failure transparency
either all the actions of a transaction are committed or none at all
Advantages of normalization
eliminate modification anamolies Reduce duplicated data Eliminate data integrity problems Save file space Single table queries will run faster
The default comparison condition for the subtype discriminator attribute is the _____ comparison
equality
Legacy Data models
have 2 forms: network model & hierarchical model
Downside to Hash Table
not good for range based tests
system integration requirements
those that require the system to operate with other information systems
Name 8 background processes
• Database Writer process (DBWn) • Log Writer process (LGWR) • Checkpoint process (CKPT) • System monitor process (SMON) • Process monitor process (PMON) • Recoverer process (RECO) • Listener registration process (LREG) • Manageability monitor process (MMON) • Manageability monitor lite process (MMNL) • Job queue coordinator (CJQ0) • Job slave processes (Jnnn) • Archiver processes (ARCn) • Queue monitor processes (QMNn)
Serial schedule
When operations are not interleaved and one transaction at a time is active. * commit/abort signals new transaction
Input control
a control that prevents invalid or erroneous data from entering the system
distributed database
a database in which storage devices are not all attached to a common processor
Bottom-up development
a development order that implements low-level detailed modules first Use drivers for testing
Top-down development
a development order that implements top-level modules first and it uses stubs for testing
BCNF can be violated only if the table contains more than one _____ key
candidate
metadata
data that describes other data. (i.e. describes the properties or context)
Downside to Indexing
-requires more storage space -requires time to create on existing tables -require time to update when records are insert/remove/updated
Entity integrity
A rule designed to ensure that every relation has a primary key and that the data values for that primary key are valid. It guarantees that every primary key attribute is non-null
Object
A structure that encapsulates (or packages) attributes and the methods that operate on those attributes. It is an abstraction of a real-world thing in which data and processes are placed together to model the structure and behavior of the real-world object.
Data warehouse
A subject-oriented, integrated, time-variant, nonvolatile collection of data used in support management decision making.
Inspection testing
A testing technique in which participants examine program code for predictable language-specific errors.
Desk checking
A testing technique in which the program code is sequentially executed manually by the reviewer.
Query Tree
A tree structure used to represent the relational operations used in a Query. There can be multiple trees for the same SQL Query
Default value
A value a field will assume unless an explicit value is entered for that field.
Include relationship
An association between two use cases where one use case uses the functionality contained in the other
_____ serve as the intermediary between the user and the database
DBMSs
The conceptual model can handle _____ relationships and multivalued attributes
M:N
Degree of relation
Number of attributes of the relation schema
According to the rules of precedence, which of the following computations should be completed first?
Performing operations within parentheses
Stakeholder
People who have a vested interest in the system being developed
Projection π l (R)
Returns all the columns which in the given list l
Informational systems
Systems designed to support decision making based on stable point-in-time or historical data.
RAD
Systems development methodology that relies on extensive user involvement, prototyping, integrated CASE tools, and code generators.
Mobile Web app
browser-based; platform independent. Most limited user experience
Rich Internet application
browser-based; uses some technologies on client device to provide a rich user interface (e.g., Flash)
From a database point of view, the collection of data becomes meaningful only when it reflects properly defined
business rules
DFD completeness
The extent to which all necessary components of a DFD have been included and fully described
Maintenance
The final phase of the SDLC in which an information system is systematically repaired and improved.
Project initiation
The first phase of the project management process in which activities are performed to assess the size, scope, and complexity of the project and to establish procedures to support later project activities.
LGWR or Redo Log writer or Log writer process back
The log writer process (LGWR) is responsible for redo log buffer management--writing the redo log buffer to a redo log file on disk. LGWR writes all redo entries that have been copied into the buffer since the last time it wrote. The redo log buffer is a circular buffer. When LGWR writes redo entries from the redo log buffer to a redo log file, server processes can then copy new entries over the entries in the redo log buffer that have been written to disk. LGWR normally writes fast enough to ensure that space is always available in the buffer for new entries, even when access to the redo log is heavy.
Domain
The set of all data types and values that an attribute can assume.
Critical path
The shortest time in which a project can be completed.
Cookie crumbs
The technique of placing "tabs" on a web page that show a user where he or she is on a site and where he or she has been
Symmetric key encryption
encryption method that uses the same key to encrypt and decrypt the data
A noun in a business rule translates to a(n) _____ in the data model
entity
In a star schema, attributes are often used to search, filter, or classify _____
facts
Fact and dimension tables are related by _____ keys
foreign
A _____ is the primary key of one table that has been placed into another table to create a common attribute
foreign key
Relational database term for row
Tuple or record
DSS(Decision Support System)
Used for processing data and making judgments on data for making decisions. A DSS database usually involves many ad hoc queries.
completeness control
a control that ensures that all required data values describing an object or transaction are present
data validation control
a control that ensures that numeric fields that contain codes or identifiers are correct
PGA(Program Global Area)
a memory region that is not shared, it is private. It contains data and control information exclusively for the use of an oracle process. A pga is created when an oracle process is started.
Tuple
a row of data in the relation corresponds to an instance in ER
The first step in the ER model verification process is to _____
identify the ER model's central entity
The first step in developing the conceptual model using ER diagrams is to _____
identify, analyze, and refine the business rules
Transitivity
if A → B and B → C then A → C
Are logical and physical structure of oracle completely separate
YES
Database definition
a self-describing collection of integrated tables
Relation
a two-dimensional data table corresponds to an entity in ER
middleware
a type of software designed to translate between different vendors' software
Primary Key
a unique identifier field within a table
Shared pool
caches various types of program data required by the server. A partial list includes storing parsed sql, pl/sql code, system parameters, and data dictionary information. It is involved in almost every operation that occurs in the database.
Using DBMS to store document as text
can be used if DBMS has special module for document processing. Would work for storing schema less and document centric XML docs
Data _____ implies that all business entities, data elements, data characteristics, and business metrics are described in the same way throughout the enterprise
integration
One of the three most common data integrity and consistency problems is _____
lost updates uncommitted data inconsistent retrievals
Secure Hypertext Transport Protocol (HTTPS)
an Internet standard for securely transmitting Web pages
weak entity
an entity that cannot be uniquely identified by its attributes alone; therefore, it must use a foreign key in conjunction with its attributes to create a primary key. (dashed underline)
High-level/Nonprocedural DML
can be used on its own to specify complex database operations concisely either are entered interactively from a display monitor, terminal, or embedded in a general-purpose programming language specify & retrieve many records in a single DML statement; called set-at-a-time/set-oriented DMLs declarative b/c its queries specify which data to retrieve
Logical Data Independence
capacity to change the conceptual schema without having to change external schemas/applications only the view definition & mappings need to change in a DBMS strict requirement- it allows structural and constraint changes without affecting application programs
User-access management is a subset of _____
authorization management
Physical Data Independence
capacity to change the internal schema without having to change the conceptual schema; external schema should not be changed as well generally exists in most databases and file environments where physical details are hidden from the user
What are the three files that comprise an Oracle database?
control files, data files, online redo log files
When introducing a database into an organization, the database approach creates a more controlled and structured information flow and thus affects people, functions, and interactions. This leads to a(n) _____ impact of the new database system
cultural
cloud services
delivery of software, infrastructure and storage over the internet
From a system functionality point of view, _____ attribute values can be calculated when they are needed to write reports or invoices
derived
3 major structures in Oracle Database server
memory structures, process structures, storage structures
The DBMS _____ the SQL query and chooses the most efficient access/execution plan
parses
"Passwords must be changed every six months" is an example of a _____
policy
-Leverages benefits of microcomputer technology -Allows most of the processing to be close to the source as possible, improving response time and reducing network traffic -facilitates the use if GUI available on the client -allows for and encourages acceptance of open systems
Advantages of Client/Server
Fact
An association between two or more terms.
Two-tier Architectures
software components are distributed over two systems; client and server advantages- simplicity & compatability
data modeling and design tools
software tools that provide automated support for creating data models (ie visio)
When using the Crow's Foot notation, the associative entity is indicated by _____ relationship lines between the parents and the associative entity
solid
Forms Specification Languages
special languages that help programmers specify forms that correspond to the data in the database
A _____ relationship exists when three entities are associated
ternary
data independence
the separation of data descriptions from the application programs that use the data
Manual Shared Memory Mangement
you set the sizes of several individual SGA components and manually tune individual SGA components. This option provides complete control of individual SGA component size.
The Oracle string concatenation function is _____
||
Process, Job, task
A Thread of control
File server
A device that manages file operations and is shared by each client PC attached to a LAN.
Network diagram
A diagram that depicts project tasks and their interrelationships.
Supertype
A generic entity type that has a relationship with one or more subtypes.
Mean time between failures (MTBF)
A measurement of error occurrences that can be tracked over time to indicate the quality of a system.
Entity cluster
A set of one or more entity types and associated relationships grouped into a single abstract entity type
Some capabilities of IS
Ability to automate, informate, transform
The SQL command that allows a user to permanently save data changes is _____
COMMIT
The _____ is the most specialized object in the ADO.NET framework
DataAdapter
Describe the Java Pool
Found in the SGA Java pool memory is used to store all session-specific Java code and data in the JVM.
Describe the Streams Pool
Found in the SGA Streams pool memory is used exclusively by Oracle Streams to: • Store buffered queue messages • Provide memory for Oracle Streams processes Unless you specifically configure it, the size of the Streams pool starts at zero. The pool size grows dynamically as needed when Oracle Streams is used.
What are the files that set up your database operating environment?
PFILE SPFILES
Background Process
PMON, SMON, DBWn, LGWR, CKPT, Other
_____ splits a table into subsets of rows or columns and places the subsets close to the client computer to improve data access time
Partitioning
_____ is the central activity during the parsing phase in query processing
Query optimization
Relational database term for table
Relation or base relvar
Which of the following queries will output the table contents when the value of V_CODE is not equal to 21344?
SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE FROM PRODUCT WHERE V_CODE <> 21344;
What does the PGA contain?
Stack Space and User Global Area(UGA)
Conceptual schema
Structure of the whole database for a community of users
Determinant
The attribute to the left side of the functional dependency
Isolation Level
The degree of how a DBMS enforces locks, user definable
Online transaction processing (OLTP)
The immediate automated responses to the requests of users.
Mapping Relational Schemas
Typically entities are mapped first, followed by relationships
Deadlock
When 2 Transactions A and B are waiting for each other to lift a lock. i.e. A locks value y and waits for x and B locks value x and waits for y
Durability
When a Transactions is executed its result it permanent
What is a dirty buffer?
When a buffer in the database buffer cache is modified, it is marked dirty and is added to the head of the checkpoint queue that is kept in system change number (SCN) order. This order therefore matches the order of redo that is written to the redo logs for these changed buffers.
constraint
a rule that cannot be violated by database users
Security controls
are part of the operating system and the network and tend to be less application specific.
capacity requirements
attempt to predict how many users the system will have to support, both in total and simultaneously
viruses
cause unwanted events
Each table _____ represents an attribute
column
n-tier architecture
distributes the work of the application (the middle tier) among multiple layers of more specialized server computers
Coding, testing, and debugging are part of the _____ phase of the Systems Development Life Cycle (SDLC)
implementation
A _____ attribute is one that cannot be subdivided
simple
Native app
written to run on specific device with specific operating system
Derived Attribute
An attribute whose value is not stored but rather is computed from other attribute(s) Attribute ellipse has a dashed line border Want to use a derived attribute- when requested you get most recent version
Primary key
An attribute whose value is unique across all occurrences of a relation.
Testing harness
An automated testing environment used to review code for errors, standards violations, and other design flaws.
The three schema architecture
User level (external schema) -> conceptual level (conceptual schema) -> internal level (physical schema)
Beta testing
User testing of a completed information system using real data in the real user environment.
Referential Integrity/Foreign Key Constraint
Value in foreign key column of referencing relation can be either null or a possible value in the referenced relation
Key properties of relational modeling
Values are atomic (i.e., a data cell cannot contain multiple values) Values in a given column are of the same domain/type (e.g., float) No rows contain completely duplicate data The order of rows and columns is completely irrelevant The names of every relation are unique and within relations, the names of columns/attributes are also unique
IDEF0 Activity
Verb&noun identifier and description of a business work practice and processes that yield valued products and services.
_____ is a technique that creates logical representations of computing resources that are independent of the underlying physical computing resources
Virtualization
Tempfile
a tempfile is a special type of database file. Physically, it's just like a regular data file, but it only holds temporary information. For example, a tempfile is used if you perform sorts on disk or if you're using temporary tables. The space is then freed to the file either immediately after your operation is done or once you log out of the system.
Centralized DBMS
all the functionality, application execution, and user interface processing occur on one machine its architecture is: terminal -> network -> software -> hardware/firmware data is stored on a single computer; can have multiple users, but DBMS & database are on one computer only
Knowing the sparsity of a column helps you decide whether the use of _____ is appropriate
an index
Hash Table
associate a key to a record by taking a value and returning an associated value -good for looking up values based on equality tests (equijoins)
Server processes
communicate with client processes and interact with the database to fulfill requests. A client porcess cannot communicate with the database on its own. It can perform the following tasks: 1 Parse and run sql statements 2 Execute PL/SQL code 3 Read data blocks from data files into the database buffer cache 4 Return results from the database to the application
Client Computer
computer running DBMS client software
The entity relationship diagram (ERD) represents the _____ database as viewed by the end user
conceptual
Query Optimizer
concerned with the rearrangement & possible reordering of operations, eliminating redundancies, & using efficient search algorithms during execution consults the system catolog for stats & physical info about the stored data, generates executable code that performs the necessary operations for the query, & makes calls on the runtime processor
storage virtualization
involves combining multiple network storage devices into what appears to be a single storage unit
rich client
involves processing on the mobile device using its resources. Presentation logic, business logic, and data access logic on the client side.
Redo Log Buffer
is a circular buffer that stores redo entries describing changes made to the database. These entries contain the information required to reconstruct changes made to the databases by DML or DDL operations.
SGA
is a group of shared memory structures that contain data and control information for a single oracle database instance. It is shared by all server and background processes, eg, cached data blocks and shared sql areas.
DBWN
writes modified buffers in the database buffer cache to disk: There are no free buffers Every 3 seconds During a checkpoint When there are too many dirty buffers When the instance shutdown cleanly when a table space changes status.
Simple elements
Contain data values
1NF, 2NF, and 3NF are
normalization stages
thin client
-a lightweight computer that is purpose-built for remote access to a server -a computer that depends heavily on another another to fulfill its computational roles
Three-Schema Architecture
1. Internal Level/Internal Schema - Describes the physical storage structure of the database 2. Conceptual Level/Conceptual Schema - Describes the structure of the whole database for a community of users. Hides the details of physical storage structures. 3. External/view Level - Includes a number of external schemas/user views that include only the data of interest to a particular user group
4 Main Types of Explicit Constraints
1. Key 2. Entity Integrity 3. Referential Integrity 4. Domain (every value must be from domain of its attribute, or null)
-remote procedure calls (RPCs) -message queues -object request brokers (ORBs)
3 examples of application middleware
Well-structured relation
A relation that contains minimal redundancy and allows users to insert, modify, and delete the rows in a table without errors or inconsistencies
-location transparency -replication transparency -failure transparency -concurrency transparency
4 objectives of distributed DBMS
Universal data model
A generic or template data model that can be reused as a starting point for a data modeling project (sometimes called patterns)
A(n) _____ can use a shared connection to the database instead of creating a new one every time
API
The SQL aggregate function that gives the arithmetic mean for a specific column is _____
AVG
relationships
Diamond Degree- unary/ recursive, binary, ternary Cardinality- number of instances of entity B that can be associated with each instance of entity a
Interface incompatibility
For example, one method passes a parameter of the wrong data type to another method
1NF
From an Un-normalised Database (UNF) Repeated groups are removed, so attributes only have 1 value. This is done by flattening the table and creating relations.
_____ occurs when a transaction accesses data before and after one or more other transactions finish working with such data
Inconsistent retrievals
Database operations
Insert: add new data Modify (aka Update): change existing data Delete: remove existing data
IDEF0
Integrated Computer Aided Manufacturing Definition
What Are Information Systems?
Integrated/coordinated sets of components and activities designed to accomplish: Data storage Data retrieval Data manipulation Data communication
DB/DC System
Integration of Database Management Systems and data communications systems (systems which allow for external communications with the database)
Database design in database redesign
Migrate databases to newer databases Integrate two or more databases Reverse engineer and design new databases using normalization principles and data model transformation
The initial assessment and the feasibility study are part of the Systems Development Life Cycle's (SDLC's) _____ phase
Planning
Miniworld/Universe of Discourse
The part of the actual universe which the database represents.
Configuration management
The process of ensuring that only authorized changes are made to a system.
ANSI defines four events that signal the end of a transaction. Of the following events, which is defined by ANSI as being equivalent to a ROLLBACK?
The program is abnormally terminated
The person responsible for the control of the centralized and shared database is the database _____
administrator
Three-tier Architecture
ads an immediate layer btwn the client and the database server 3 layers: user interface, application rules, data access/management
-reliability -fast response
advantages of data replication
-increased reliability and availability -local control -modular growth -lower communication costs -faster response
advantages of distributed databases
The attribute hierarchy provides a top-down data organization that is used for two main purposes: _____ and drill-down/roll-up data analysis
aggregation
Big Data Systems
aka key-value storage systems and NOSQL systems use various data models: document-based, graph-based, column-based, & key-value data models
Federated DBMS
aka- multidatabase system system where the participating DBMSs are loosely coupled and have a degree of local autonomy
A(n) _____ only returns matched records from the tables that are being joined
inner join
Multivalued attribute
An attribute that takes on more than one value for a given entity instance Attribute ellipse has a double line border Multi-valued attribute is problematic because you don't know how many there are(no mins and maxs) just know that there is more than one. Avoid in project.
Derived attribute
An attribute whose value can be computed from related attribute values.
Mapping Binary 1:1 Relationships
A binary 1:1 relationship is mapped much like a binary 1:M relationship, except the placement of the foreign key is somewhat arbitrary Ex. (can have null) PROFESSOR(ProfID, Name, Dept, SerialNum) LAPTOP(SerialNum, Model, OS) or (better/ no null) PROFESSOR(ProfID, Name, Dept) LAPTOP(SerialNum, Model, OS, ProfID)
Optional Attribute
An attribute whose value is allowed to not have a value Attribute name includes (O) Could make optional bonus non optional but input zero for bonus
Mapping Binary M:N Relationships
A binary M:N cannot be mapped directly into relations without creating an intersection relation (aka association table, junction table, etc.) Ex. STUDENT(StudentID, Name, Major, Year) COURSE(CourseID, Title, Topic) ENROLLS(StudentID, CourseID, Date) What is the primary key in enrolls? The combo Could add date to part of the composite key to fix redundancy issue
Hypertext transfer protocol (HTTP)
A communications protocol for exchanging data on the Internet.
Total Participation
A constraint applied to a Relation where each Entity must participate in the relation at least once. ( >= 1 times)
Functional dependency
A constraint between two attributes in which the value of one attribute is determined by the value of another attribute.
Project management
A controlled process of initiating, planning, executing, and closing down a project.
Recurring cost
A cost resulting from the ongoing evolution and use of a system.
Form Interaction
A highly intuitive human-computer interaction method whereby data fields are formatted in a manner similar to paper-based forms
Pop-up menu
A menu-positioning method that places a menu near the current cursor position
Third normal form (3NF)
A relation that is in second normal form and that has no functional (transitive) dependencies between two (or more) nonprimary key attributes.
What is a database?
A shared collection of logically-related, persistent data, designed to meet the needs of multiple users usually within an organization
Client/server architecture
A LAN-based computing environment in which a central database server or engine performs all database commands sent to it from client workstations, and application programs on each client concentrate on user interface functions.
Ternary Relation
A Relation between 3 Entities. Can be used instead of Aggregation
Schedule
A Sequence of operations which preserves the original order of individual Transactions
Algebraic Heuristics
A Set of rules to apply to a query when executing it
Key
A SuperKey where no proper subset of the attributes is a superkey. uniquely identifies the row in the table. Does not possess the SuperKey uniqueness.
Tangible Benefit
A benefit derived from the creation of an information system that can be measured in dollars and with certainty.
Intangible benefit
A benefit derived from the creation of an information system that cannot be easily measured in dollars or with certainty.
Project charter
A short document prepared for the customer during project initiation that describes what the project will deliver and outlines, generally at a high level, all work required to complete the project.
Optional attribute
An attribute that may not have a value for every entity (or relationship) instance with which it is associated.
Optional attribute
An attribute that may not have a value for every entity instance.
Three-tiered client/server
Advanced client/server architectures in which there are three logical and distinct applications - data management, presentation, and analysis - that are combined to create a single information system.
Multivalued attribute
An attribute that may take on more than one value for a given entity (or relationship) instance
Table names are written how?
All capital letters ex. STUDENT, COURSE, GRADE
Relationship instance
An association between (or among) entity instances where each relationship instance associates exactly one entity instance from each participating entity type
What is an Extent
An extent is a specific number of contiguous Oracle data blocks (obtained in a single allocation) that are used to store a specific type of information. Oracle data blocks in an extent are logically contiguous but can be physically spread out on disk because of RAID striping and file system implementations.
what are backup files
Are used for database recovery. You typically restore a backup file when a media failure or user error has damaged or deleted the original file.
Which of the following queries will output the table contents when the value of P_PRICE is less than or equal to 10?
SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE FROM PRODUCT WHERE P_PRICE <= 10;
In standard SQL, the optimizer hint FIRST_ROWS is generally used for _____ mode processes
interactive mode processes
How does the user interact?
interacts with the database application, which interacts with the DBMS, which controls the database
During decentralized design, after the _____ has been completed, all modules are integrated into one conceptual model
verification process
CJQ0 or Job Queue Process coordinator back
CJQ0 is automatically started and stopped as needed by Oracle Scheduler. The JOB_QUEUE_PROCESSES initialization parameter specifies the maximum number of processes that can be created for the execution of jobs. CJQ0 starts only as many job queue processes as required by the number of jobs to run and available resources.
The ANSI has defined standards that govern SQL database transactions. Transaction support is provided by two SQL statements: _____ and ROLLBACK
COMMIT
The SQL aggregate function that gives the number of rows containing non-null values for a given column is _____
COUNT
Direct installation
Changing over from the old information system to a new one by turning off the old system when the new one is turned on.
Cardinality
Consists of max (closer to entity) and min cardinality (farther)
Background processess
Consolidate functions that otherwise would require multiple oracle database programs running for each client process, background processes perform I/O and monitor other Oracle database processes.
Semi structured data
Data that may have a certain structure but not all of the info collected will have identical structure. -Some attributes may be shared among various entities others may exist only in a few entities. -additional attributes can be introduced and some of the newer data items at any time. -no predefined schema
DBMS
DataBase Management System. Software that enables the used to define, create and maintain a database and provides controller access.
In the ADO.NET framework, the _____ object is the in-memory representation of the data in the database
DataSet
To remove a row from the PRODUCT table, one must use the _____ command
DELETE
Of the following normal forms, _____ is mostly of theoretical interest
DKNF
A table can be deleted from the database by using the _____ command
DROP TABLE
Class Specialization
Defining a subclass as a set of subclasses of a superclass. 1. Predicate Defined: Specialization based on an attribute 2. Attribute Defined: Linked to an attribute (usually denoted on ER diagram with specialization label for entity connection) 3. User Defined: Defined on an entity-by-entity basis
Delete (SQL)
Delete data from a table General: delete from "tablename" where "columnname" OPERATOR "value" [and|or "column" OPERATOR "value"]; [ ] = optional ex. delete from employee; // deletes whole table! delete from employee where lastname = 'May';
System documentation
Detailed information about a system's design specifications, its internal workings and its functionality.
Business-to-consumer (B2C)
Electronic commerce between businesses and consumers
Automatic shared memory management
Enables you to exercise more control over the size of the SGA and is the default when automatic memory management is disabled. Oracle will tune the the total SGA to a supplied target size and will also tune the sizes of SGA components. There are few components that take the memory from teh SGA_TARGET parameter, leaving the memory avilable for the auomatically tuned parameters. Following SGA components need to be manually sized. LOG_BUFFER, DB_KEEP_CACHE_SIZE, DB_nk_CACHE_SIZE
E-R Diagram
Entity-Relationship Diagram. A diagram that represents the entities in a Database and the relations between them.
Referential Integrity Constraints
Every value in a foreign key must have a corresponding, valid value in the referenced primary key
How do you map an associative entity?
Exactly same manner as binary M:N relationship
Files and structures
Files associated with the database exist all the time — as long as a database is created. If you just install Oracle, no database files exist. The files show up as soon as you create a database. As with memory and process, some files are required whereas others are optional. Files contain your actual database objects: the things you create as well as the objects required to run the database. The logical structures are such things as tables, indexes, and programs.
File processing system
Focus on data processing needs of individual departments Application-driven, no overall map, plan, or model to guide the growth of applications Each application designed with its own set of data files To meet the needs of new applications, existing data files had to be restructured. Thus, existing programs had to be modified... It was often much safer to design new files for each application
Describe the Large pool
Found in the SGA Provides large memory allocations for: • Session memory for the shared server and the Oracle XA interface (used where transactions interact with multiple databases) • I/O server processes • Oracle Database backup and restore operations • Parallel Query operations • Advanced Queuing memory table storage
Join (SQL)
Joins allow you to link data from two or more tables together into a single query result--from one single SELECT statement. Forms relationships between tables ex. SELECT customer_info.firstname, customer_info.lastname, purchases.item FROM customer_info, purchases WHERE customer_info.customer_number = purchases.customer_number;
What are the four problems with concurrency
Lost update dirty read incorrect summary and unrepeatable read
is the large pool managed by LRU?
NO
_____ relates to the activities that make the database execute transactions more efficiently in terms of storage and access speed
Performance tuning
To be considered minimally relational, the DBMS must support the key relational operators _____, PROJECT, and JOIN.
SELECT
describe the PGA
Program Global Area (PGA) is a memory region that contains data and control information for each server process. An Oracle server process services a client's requests. Each server process has its own private PGA that is allocated when the server process is started. Access to the PGA is exclusive to that server process, and the PGA is read and written only by the Oracle code acting on its behalf. The PGA is divided into two major areas: stack space and the user global area (UGA).
_____, also known as RESTRICT, yields values for all rows found in a table that satisfy a given condition
SELECT
The _____ command is used to restore the database to its previous condition
ROLLBACK;
Benefits of the Database Approach
Redundancy can be reduced and thus inconsistency can be avoided Data can be integrated and shared among applications Standards can be enforced by the DBA Formats, representation, naming, documentation Security restrictions can be applied DBA: database administrator Data integrity can be maintained Conflicting requirements of different departments can be balanced by the DBA who (presumably!) acts according to "what is best for the enterprise" Data independence Ease of application development Enhanced data accessibility and responsiveness Reduced program maintenance
Typical Problems with Organizational Memory
Redundancy: same data stored in different memories Data Control: data not managed as a valuable resource Interface: difficult to access data Delays: long delays in responding to requests for data Lack of Data Integration: data dispersed across different systems; it may not be known where data are stored
_____ relationships occur when there are multiple relationship paths between related entities
Redundant
Labels/tags on directed edge
Represent schema names names of attributes object types in relationships
Which of the following queries will use the given columns and column aliases from the PRODUCT table to determine the total value of inventory held on hand and display the results in a column labeled TOTVALUE?
SELECT P_DESCRIPT, P_QOH, P_PRICE, P_QOH*P_PRICE AS TOTVALUE FROM PRODUCT;
The syntax for a left outer join is _____
SELECT column-list FROM table1 LEFT [OUTER] JOIN table2 ON join-condition
Information as a strategic resource (late 80s, 90s and beyond) Competitive advantage using SIS, KMS, BI
SIS: strategic information system KMS: knowledge management system BI: business intelligence
SOA
SOA is a style of IT architecture that utilizes a build-once/deploy-many concept. Its root definition includes webcentric services that work together to sustain business processes. SOA separates the application function from the underlying software and hardware to allow better use (or reuse) of application processing. These functions or service units are written to be flexible by design and capable of service-to-service communication.
Operational systems
Systems that are used to interact with customers and run a business in real time.
Data is stored in
Tables, which have rows and columns like a spreadsheet. A database may have multiple tables, where each table stores data about a different thing.
Drop (SQL)
The drop table command is used to delete a table and all rows in the table. drop table is different from deleting all of the records in the table. Deleting all of the records in the table leaves the table including column and constraint information. drop table "tablename"
ANSI defines four events that signal the end of a transaction. Of the following events, which is defined by ANSI as being equivalent to a COMMIT?
The end of a program is successfully reached
enterprise data modeling
The first step in database development, in which the scope and general contents of organizational databases are specified.
Memory
The memory components of Oracle (or any software, for that matter) are what inhabit the RAM on the computer. These structures only exist when the software is running. For example, they instantiate when you start an instance. Some of the structures are required for a running database; others are optional. You can also modify some to change the behavior of the database, while others are static.
The feasibility study during the planning phase of the Systems Development Life Cycle (SDLC) must address the
The technical aspects of hardware and software requirements The system cost The operational cost
CKTP or Checkpoint Process back
When a checkpoint occurs, Oracle must update the headers of all datafiles to record the details of the checkpoint. This is done by the CKPT process. The CKPT process does not write blocks to disk; DBWn always performs that work. DBWn periodically writes buffers to advance the checkpoint, which is the position in the redo thread (log) from which instance recovery begins. This log position is determined by the oldest dirty buffer in the buffer cache.
Candidate key
When a relation has more than one key
Unrepeatable read
When a transaction reads the same item twice and the item is changed between the two reads
FDBA or Flash Data Archiver Processes back
When a transaction that modifies a tracked table commits, FBDA stores the pre-image of the rows in the archive. FDBA maintains metadata on the current rows and tracks how much data has been archived. FBDA is also responsible for automatically managing the flashback data archive for space, organization (partitioning tablespaces), and retention. FBDA also keeps track of how far the archiving of tracked transactions has progressed.
ISA
When an Entity IS A subclass of another Entity, meaning its attributes are a super set of the parent's attributes. e.g. A Janitor IS A Employee, so has the same attributes and some attributes unique to Janitor
Multi valued attribute
When an attribute has more than one attribute off of it. May have lower and upper bounds to constrain the number of values allowed for each individual entity
Create table (SQL)
create table "tablename" ("column1" "data type", "column2" "data type", "column3" "data type"); ex. (see SQL data type card) create table employee (first varchar(15), last varchar(20), age number(3), address varchar(30), city varchar(20), state varchar(20)); creates table with columns: first last age address city state and defines their data types and size
Client/Server Architecture
created to deal with computing environments in which a large number of pcs, workstations, servers, electronics, etc are connected to a network
Functions of the DBMS
creates databases, tables, and supporting structures, manages database data, enforces rules and provides security.
DBMS definition
creates, processes, and administers databases
In a(n) _____ diagram, the arrows above the attributes indicate all desirable dependencies
dependency
hardware and software specification
document that describes what hardware and software are needed to support the application
replication transparency
even if data is replicated, it maybe treated as if it were a single item on a single site
-file server computing -client/server computing -network computing
examples of distributed database
A(n) _____ specifically reserves access to the transaction that locked the object
exclusive lock
Runtime Database Processor
executes the privileged commands, executable query plans, & canned transactions with runtime parameters works with the system catalog & may update it; also with the stored data manager
What is the nK Buffer Cache
found in the Database Buffer Cache in the SGA. Additional buffer caches can be configured to hold blocks of a size that is different from the default block size.
What is the Keep Pool?
found in the Database Buffer Cache in the SGA. The keep buffer pool is designed to retain buffers in memory longer than the LRU would normally retain them.
Database Utilities
functions other than those to process the software modules: loading, backup, storage reorganization, performance monitoring, etc
Object-Relational DBMS
has evolved from relational DBMSs that have taken concepts & capabilities from object databases aka- extended relational systems have been implemented in some commercial databases, but not widespread
Installing the DBMS, creating the database, and loading or converting the data are part of the _____ phase of the Database Life Cycle (DBLC)
implementation and loading
LIKE operator (SQL)
in SQL -- allows you to select only rows that are "like" what you specify. The percent sign "%" can be used as a wild card to match any possible character that might appear before or after the characters specified. select first, last, city from empinfo where first LIKE 'Er%'; // matches any name where first name begins with Er select first, last from empinfo where last LIKE '%s'; // matches any name with last name ending in s
Multiuser Systems
include the majority of DBMS support concurrent multiple users
Complex _____ requirements may dictate data transformations, and they may expand the number of entities and attributes within the design
information
In the context of developing a data administration strategy, a(n) _____ provides a framework that includes computerized, automated, and integrated tools such as a DBMS and CASE tools
information systems architecture - ISA
Application Server
middle computer that is accessed by the user that access the database server; aka- middle tier or Web server runs applications and stores business rules that are used to access data from the database server improves database security- checking the client's credentials before forwarding the request to the server
Object Data Model
new family of higher-level implementation data models that are closer to conceptual data models frequently used in the software engineering domain defines a database in terms of objects, their properties, & operations objects with the same structure/behavior belong to a class which are organized into hierarchies operations are specified in terms of methods
security requirements
produce reasonable precautions to prevent problems
Cartesian Product
produces a relation that has all of the attributes and all of the tuples in all possible combinations, of two relations, even when they are NOT union compatible
Difference
produces a relation that includes all of the tuples in one relation, which are NOT also in another relation, if they are union compatible
Intersection
produces a relation that includes all of the tuples that are in both of two relations, if they are union compatible
Client Machines
provide the user with the appropriate interfaces to utilize the servers and have enough processing power to run the applications concept can be carried over to other software packages with specialized programs can have just client programs, just servers, or both
Access Path
search structure that makes the search for particular database records efficient ex: indexing/hashing
Printer Server
server connected to various printers all print requests by the clients are forwarded to this printer
Querry/Transaction Server
server in a system where the SQL processing is stationed on the server side it provides both functionalities of queries and transactions in an RDBMS, often called a SQL server
application program interfaces (APIs)
services have standardized the way that programs or data sources communicate with each other
Data Manipulation Language (DML)
set of operations that allows for users to have means to manipulate the data- retrieving, inserting, deleting, & modifying
Downside to B-Trees
slightly slower on equality tests
microcomputer
small computer that contains a microprocessor as its central processor
A _____ schema is a type of star schema in which dimension tables can have their own dimension tables
snowflake
View Definition Language (VDL)
specifies the user views & their mappings to the conceptual schema, but in most DBMS the DDL is used for conceptual & external schemas SQL is used as a VDL to define application views as a result of predefined queries in a relational DBMS
maintainability requirements
specify the business requirements that can be anticipated
technical environment requirements
specify the type of hardware and software on which the system will work
Data Dictionary/Repository System
stores information such as design decisions, usage standards, application program descriptions, & user info along with catalog info about schemas/constraints aka: information repository info can be accessed directly by users or the DBA when needed similar to the DBMS catalog, but includes wider range of info & mainly accessed by users
Metadata
summarizes basic information about data which can make finding and working with particular instances of data easier. Having the ability to filter through that metadata makes it much easier for someone to locate a specific document.
In a database context, a(n) _____ indicates the use of different names to describe the same attribute
synonym
A _____ lock locks the entire table preventing access to any row by a transaction while another transaction is using the table
table-level lock (Chapter 10 - 467)
Unit test
tests of an individual method, class, or component before it is integrated with other software
Integration test
tests of the behavior of a group of methods, classes, or components
invertible
text encrypted with either key can be decrypted by the other
The "_____" characteristic of a primary key states that the primary key must uniquely identify each entity instance, must be able to guarantee unique values, and must not contain nulls
unique values
In the context of the Oracle Enterprise Manager, a(n) _____ is a uniquely identifiable object that allows a given person to log on to the database
user
Transitive dependency
values in a non-key column are functionally dependent on another non-key column
All join conditions are connected through a(n) _____ logical operator
AND
three-tiered architecture
uses three sets of computers
Primary identifier vs. alternate identifier
(e.g., ID vs. first, middle, and last name)
In the entity relationship diagram (ERD), cardinality is indicated using the _____ notation, where max is the maximum number of associated entities and min represents the minimum number of associated entities
(min, max)
what are database processes
- Server process: Connects to the Oracle instance and is started when a user establishes a session - Background processes: Are started when an Oracle instance is started
FLWOR
-For -Let -Where -OrderBy -Return
Problems with serial schedules
-Limit concurrency -waste time efficiency
benefits to indexing
-Speeds simple queries -speeds up join conditions
distributed data and application
-also called three-tiered or n-tiered client/server computing -data and data manipulation layer placed on one server; application logic layer has its own server -clients execute a minimum of the overall system's components
distributed data
-also known as two-tiered client/server computing -simplest form of true client/server computing -places the information system's stored data on a server and business logic and user interface on the clients
open database connectivity (ODBC)
-an open standard application programming interface (API) for accessing a database -you can access files in a number of different databases
Skills of a Successful Database/Data Analyst
-analytical -technical -managerial -interpersonal
network computing
-application server, database server, and client are separate -usually uses thin clients -internet-based
Conflict
-belongs to diff transactions -access same item -1 is a write
decentralized database
-collection of independent databases -physically located in multiple computers -not connected by networks (no sharing of data)
Natural Join
-denoted by * -combines all attributions of both relations -joins attributes with the same name
Assume you are using the UNION ALL operator to combine the results from two tables with identical structure, CUSTOMER and CUSTOMER_2. The CUSTOMER table contains 10 rows, while the CUSTOMER_2 table contains 7 rows. Customers Dunne and Olowski are included in the CUSTOMER table as well as in the CUSTOMER_2 table. How many records are returned when using the UNION ALL operator?
17
From a structural point of view, 2NF is better than _____
1NF
1st 3 Normal Forms
1NF - attributes depend on key 2NF - attributes depend on whole key 3NF - attributes depend on nothing but the key
Assume you are using the INTERSECT operator to combine the results from two tables with identical structure, CUSTOMER and CUSTOMER_2. The CUSTOMER table contains 10 rows, while the CUSTOMER_2 table contains 7 rows. Customers Dunne and Olowski are included in the CUSTOMER table as well as in the CUSTOMER_2 table. How many records are returned when using the INTERSECT operator?
2
-data replication -horizontal partitioning -vertical partitioning -combination of above
4 options for distributed databases
-Presentation -Presentation logic -Application logic -Data manipulation -Data layer
5 conceptual layers of an information system
RFP (Request for Proposal)
A document that is provided to vendors to ask them to propose hardware and system software that will meet the requirements of your new system.
Recursive foreign key
A foreign key in a relation that references the primary key values of that same relation.
Normalization
A formal method for creating well designed relational databases involves normalization, which is a process of reducing redundancy and increasing data integrity Normalization involves a series of stages referred to collectively as normal forms
Entity-relationship diagram (E-R diagram)
A graphical representation of an E-R model.
Semi-Join R ⋉ p S
A join only returning the columns from R
Natural Join R ⋈ S
A join without a condition, meaning all columns just match where columns have the same name and domain (type)
Query Language
A language that allows manipulation and retrieval of data from a Database
Internet
A large worldwide network of networks that use a common protocol to communicate with each other
Session
A logical entity in the database instance that represents the state of a current user login to a database. A session lasts until the user disconnects or exits the connecting application.
Relationship type
A meaningful association between (or among) entity types
Run-time exceptions
A method generates an error, such as "out of memory" or "file already in use," due to conflicting resource needs
Timestamping
A method of managing Database concurrency giving transactions a (relative) start time ts(T). T request read(x) - ts(T) < wrt(x) → Fails: x has change since T read it - ts(T) ≥ wrt(x) → Passes we have the latest version of x - then: rts(x) = MAX( rts(x), ts(T) ) biggest version of read T requests wrt(x) - ts(T) < rts(x) → x was read and is now wrong - ts(T) < wrt(x) → our version of x is wrong - Otherwise it passes wrt(x) = ts(T)
Data Model
A model that makes only the logical concepts visible to users, rather than the storage and implementation details
Physical file
A named set of table rows stored in a contiguous section of secondary memory.
Use case diagram
A picture showing system behavior, along with the key actors that interact with the system
Project
A planned undertaking of related activities to reach an objective that has a beginning and an end.
Second normal form (2NF)
A relation for which every nonprimary key attribute is functionally dependent on the whole primary key.
Well-structured relation
A relation that contains a minimum amount of redundancy and allows users to insert, modify, and delete the rows without errors or inconsistencies.
Third normal form (3NF)
A relation that is in second normal form and has no transitive dependencies
Binary relationship
A relationship between the incidence of two entity types - Example: PROFESSOR teaches COURSE.
Unary relationship
A relationship between the instances of just one entity type.
Total specialization rule
A rule that specifies that each entity instance of a supertype must be a member of some subtype in the relationship
Entity integrity rule
A rule that states that no primary key attribute (or component of a primary key attribute) may be null
SQL Server
A server on which all database query/transaction functionality reside
Referential integrity constraint
A statement that limits the values of the foreign key to those already existing as primary key values in the corresponding relation
Mission statement
A statement that makes it clear what business a company is in.
Project manager
A systems analyst with a diverse set of skills - management, leadership, technical, conflict management, and consumer relationship - who is responsible for initiating, planning, executing, and closing down a project.
Extend relationship
An association between two use cases where one adds new behaviors or actions to the other
Candidate key
An attribute (or combination of attributes) that uniquely identifies each instance of an entity type.
Identifier
An attribute (or combination of attributes) whose value distinguishes instances of an entity type.
Key Attribute
An attribute (which may be composite or of which there may be more than 1) for which the entity must have a unique value
Foreign key
An attribute in relation that serves as the primary key of another relation in the same database
Subtype discriminator
An attribute of a supertype whose values determine the target subtype or subtypes
Artificial/Surrogate Key
An attribute of sequential numbers created to serve as the key for a relation where no other key exits.
Primary key
An attribute or combination of attributes that uniquely identifies each row in the relation
Primary Key
An attribute or set of attributes that uniquely defines that entity. a.k.a Super Key or Candidate Key.
Foreign key
An attribute that appears as a non-primary key attribute in one relation and as a primary key attribute (or part of a primary key) in another relation.
Composite attribute
An attribute that can be further subdivided to yield additional attributes. For example, a phone number (615-898-2368) may be divided into an area code (615), an exchange number (898), and a four-digit code (2368). Compare to simple attribute.
composite attribute
An attribute that can be further subdivided to yield additional attributes. For example, a phone number (615-898-2368) may be divided into an area code (615), an exchange number (898), and a four-digit code (2368). Compare to simple attribute.
Composite Attribute
An attribute which is composed of several components
Corporate strategic planning
An ongoing process that defines the mission, objectives, and strategies of an organization.
Project workbook
An online or hard-copy repository for all project correspondence, inputs, outputs, deliverables, procedures, and standards.
_____ databases reflect the ever-growing demand for greater scope and depth in the data on which decision support systems increasingly rely
Data warehouse
Describe ARCn
Archiver Processes • Copy redo log files to a designated storage device after a log switch has occurred • Can collect transaction redo data and transmit that data to standby destinations If you anticipate a heavy workload for archiving (such as during bulk loading of data), you can increase the maximum number of Archiver processes. There can also be multiple archive log destinations. It is recommended that there be at least one Archiver process for each destination. The default is to have four Archiver processes.
Reverse engineering
Automated tools that read program source code as input and create graphical and textual representations of design-level information such as program control structures, data structures, logical flow, and data flow.
Reengineering
Automated tools that read program source code as input; perform an analysis of the program's data and logic; and then automatically, or interactively with a systems analyst, alter an existing system in an effort to improve its quality or performance.
What is ASM?
Automatic Storage Management A portable and high-performance cluster file system It manages Oracle database files Manages application files with ASM Cluster File System Spreads data across disks to balance load Mirrors data in case of failures Solves storage management challenges
_____ CASE tools provide support for the coding and implementation phases
Back-end
Background Processes
Background processes start with the database instance and perform maintenance tasks such as performing instance recovery, cleaning up processes, writing redo buffers to disk, and so on.
Describe the CKPT
Checkpoint Process Records checkpoint information in - Control file - Each data file header • Signals DBWn to write blocks to disk CKPT Checkpoint process A checkpoint is a data structure that defines a system change number (SCN) in the redo thread of a database. They are a crucial element of recovery. Oracle Database must update the control file and the headers of all data files to record the details of the checkpoint. This is done by the CKPT process.
Heuristic 2
Combine Cartesian Products (x) with subsequent selects (σ) into a join.
Application Software
Computer software designed to support organizational functions or processes.
cloud computing
Computing resources obtained as a service.
define connection
Connection: Communication between a user process and an instance connection is a communication pathway between a user process and an Oracle Database instance
CAP
Consistency, Availability, Partition Tolerance
Wnnn/SMCO or Space management Coordinator Process back
Coordinates the execution of various space management tasks. This background process coordinates the execution of various space management tasks, including proactive space allocation and space reclamation. SMCO dynamically spawns slave processes (Wnnn) to implement these tasks.
DBMS Functions
Create database Create tables Create supporting structures (ex. indexes) Modify (insert, update, or delete) database data Read database data Maintain database structures Enforce rules Control concurrency Perform backup and recovery
CRUD
Create, Read, Update, Delete
DML
Data Manipulation Language. A query language providing access to the data stored in a Database. [SQL]
Information
Data converted into a meaningful and useful context
datafiles
Data files are the largest file types in an Oracle database. They store all the actual data you put into your database, as well as the data Oracle requires to manage the database. Data files are a physical structure: They exist whether the database is open or closed. Data files are also binary in nature. You can't read them yourself without starting an instance and executing queries. The data is stored in an organized format broken up into Oracle blocks. Most of the time Oracle data files have an extension of .DBF (short for database file?). But the fact of the matter is that file extensions in Oracle don't matter. You could name it .XYZ and it will function just fine.
Clustering
Data in table re-arranged according to key field of index -easy lookup for RANGE queries -everything in sorted order
_____ exists when different versions of the same data appear in different places
Data inconsistency
Specialization Constraints
Disjoint/Overlapping - An entity must belong to only one subclass/it can belong to multiple subclasses Total/Partial - An entity must belong to one of the following subclasses/it can belong to none of the subclasses.
A(n) _____ file provides the composition of the database's logical model and defines the syntax rules for an XML document
Document Type Definition — DTD
Document centric
Documents with large amount of text - due to no structure data elements in these documents
Information for supporting management decision making (late 70s-80s) The goal was to refine the decision-making process of managers in a broad range of problem situations Such systems were referred to as EIS, ESS, DSS, GDSS
EIS: executive information system ESS: executive support system DSS: decision support system GDSS: group decision support system
EM
EM manages the databases and hosts, which are also called targets. It has a web interface that gives you a comprehensive view of each target's state. It handles all the monitoring requirements and provides other web-based tools to interact or perform maintenance with.
what are trace files
Each server and background process can write to an associated trace file. When an internal error is detected by a process, the process dumps information about the error to its trace file. Some of the information written to a trace file is intended for the database administrator, whereas other information is for Oracle Support Services.
Attributes
Ellipse May be candidate or primary identifiers, multivalued, stored or derived
Extents
Extents are like the growth rings of a tree. Whenever a segment grows, it gains a new extent. When you first create a table to store items, it gets its first extent. As you insert data into that table, that extent fills up. When the extent fills up, it grabs another extent from the tablespace. When you first create a tablespace, it's all free space. When you start creating objects, that free space gets assigned to segments in the form of extents. Your average tablespace is made up of used extents and free space.
VMS/VSAM is an example of the _____
File system data model or First Generation
A high-quality relational database schema has these features:
Flexibility or ease of implementing future data model changes Lack of redundant data
Miscellaneous issues 2
For multi-valued attributes, avoid repeating groups (e.g., columns such as Address1, Address2, and Address3) by creating a separate table/schema as you would for M:N relationships: STUDENT(StudentID) STUDENTADDRESS(Address, StudentID) No need for anything null in these treatments
Types of functional dependency
Full, partial, transitive Want to focus on weaker dependencies first. Some will create a surrogate key instead of composite key to avoid issues of dependency/ it will make it more simple.
Snnn or Shared Server Process back
Handles client requests in the shared server architecture. In the shared server architecture, clients connect to a dispatcher process, which creates a virtual circuit for each connection. When the client sends data to the server, the dispatcher receives the data into the virtual circuit and places the active circuit on the common queue to be picked up by an idle shared server. The shared server then reads the data from the virtual circuit and performs the database work necessary to complete the request. When the shared server must send data to the client, the server writes the data back into the virtual circuit and the dispatcher sends the data to the client. After the shared server completes the client request, the server releases the virtual circuit back to the dispatcher and is free to handle other clients. Several initialization parameters relate to shared servers. The principal parameters are: DISPATCHERS, SHARED_SERVERS, MAX_SHARED_SERVERS, LOCAL_LISTENER, REMOTE_LISTENER.
Types of Data Manipulation Languages
High Level/Set-at-a-time: Can be used on its own to concisely specify database operations Low Level/Record-at-a-time: Must be embedded in a general-purpose programming language, since it needs programming constructs (e.g. looping) to retrieve data
The SQL command that allows a user to insert rows into a table is
INSERT
A broader and more appropriate interpretation of Information Systems:
IS consists not only of hardware and software (i.e., the IT), but also people, procedures and data
-Server must be able to grow as more clients are added. -Gateways from server to existing mainframe or mini will be necessary for data sharing among different databases. -Capabilities must be provided for server for back-up/recovery, security, and uninterrupted power -More complex because it requires close communication between server and client for proper database processing -no standards for distributing processing between client and server -sophisticated front end often required high end macros
Issues with Client/Server
How many members are appropriate?
It depends on how paranoid you are. Two members on two disks seems to be pretty common. However, it isn't uncommon to see three members on three disks. More than that and you're just plain crazy. Well, not really. It's just that the more members you have, the more work the LGWR has to do. It can impact system performance at the same time offering very little return.
PMON
It monitors all the other background processes. When a server or dispatcher process terminates abnormally, it performs process recover. Cleans up the database buffer cache and freeing resources that were allocated for client process. It releases the resources held by failed user process, rolls back the transaction that caused the process to fail, and releases the row and table level locks held by the failed user process. PMON rolls back the transaction that caused the process to fail, the data up to the last commit before the abnormal termination is retained n the database.
One of the advantages of _____ over other middleware is that it requires no configuration on the client side
Java Database Connectivity — JDBC
Date attributes contain calendar dates stored in a special format known as the _____ date format
Julian
The special operator used to check whether an attribute value matches a given string pattern is _____
LIKE
_____ are required to prevent another transaction from reading inconsistent data
Locks
Describe the LGWR
Log Writer Process Writes the redo log buffer to a redo log file on disk - When a user process commits a transaction - When an online redo log switch occurs - When the redo log buffer is one-third full or contains 1 MB of buffered data - Before a DBWn process writes modified buffers to disk - When three seconds have passed since the last write
Relation
Mathematical concept based on the idea of sets. Looks like a table of values
PMON or Process monitor back
Monitors the other background processes and performs process recovery when a server or dispatcher process terminates abnormally PMON periodically performs cleanup of all the following: *Processes that died abnormally *Sessions that were killed *Detached transactions that have exceeded their idle *timeout *Detached network connections which have exceeded their idle timeout In addition, PMON monitors, spawns, and stops the following as needed: *Dispatcher and shared server processes *Job queue processes *Pooled server processes for database resident connection pooling *Restartable background processes PMON is also responsible for registering information about the instance and dispatcher processes with the network listener.
Server Process
Oracle creates server processes to handle the requests of user processes connected to the instance. In some situations when the application and Oracle operate on the same machine, it is possible to combine the user process and corresponding server process into a single process to reduce system overhead. However, when the application and Oracle operate on different machines, a user process always communicates with Oracle through a separate server process. Server processes (or the server portion of combined user/server processes) created on behalf of each user's application can perform one or more of the following: Parse and run SQL statements issued through the application Read necessary data blocks from datafiles on disk into the shared database buffers of the SGA, if the blocks are not already present in the SGA Return results in such a way that the application can process the information
From smallest to largest tell me the Logical database structures
Oracle data block extent segment tablespace database
Automatic Memory Management
Oracle manages the SGA and instance PGA memory automatically. This is the simplest method for managing memory and is strongly recommended by oracle. For databases created with DBCA using the basic installation option. automatic memory management is enabled by default.
_____ are especially useful when you are trying to determine what values in related tables cause referential integrity problems
Outer joins
User level (external schema)
Part of a database that a particular user group is interested in. Other parts of the database are hidden.
DIAG or Diagnostic Capture Process back
Performs diagnostic dumps A diagnostic dump captures and dumps specific diagnostic information when an incident is created (automatic) or on the request of an administrator (manual). When executed as part of incident creation, the dump is included with the set of incident diagnostics data. Examples of diagnostic dumps include a JVM thread dump, JVM class histogram dump, and DMS metric dump. DIAG performs diagnostic dumps requested by other processes and dumps triggered by process or instance termination. In Oracle RAC, DIAG performs global diagnostic dumps requested by remote instances.
GEN0 or General Task Execution Process back
Performs required tasks including SQL and DML
Join R ⋈ p S
Performs the Cross Product of R and S and returns only the rows which satisfy condition p
Recovery Backups
Periodic physical copies of the Database to offline storage
Use Case Level
Perspective from which a use case description is written, typically ranging from high level to extremely detailed
Entity Integrity/Primary Key Constraint
Primary Key Attributes can never have any null values
Prime attribute/Nonprime attribute
Prime attribute must be a member of some candidate key, nonprime attribute does not.
In Oracle, _____ make(s) it possible to merge SQL and traditional programming constructs, such as variables, conditional processing (IF-THEN-ELSE), basic loops (FOR and WHILE loops,) and error trapping
Procedural Language SQL
_____ are written instructions that describe a series of steps to be followed during the performance of a given activity
Procedures
Describe the PMON
Process Monitor Process Performs process recovery when a user process fails - Cleans up the database buffer cache - Frees resources that are used by the user process • Monitors sessions for idle session timeout cleaning up the database buffer cache and freeing resources that the user process was using. For example, it resets the status of the active transaction table, releases locks, and removes the process ID from the list of active processes. PMON periodically checks the status of dispatcher and server processes, and restarts any that have stopped running (but not any that Oracle Database has terminated intentionally). Like SMON, PMON checks regularly to see whether it is needed; it can be called if another process detects the need for it.
Mandatory background processes
Process Monitor Process (PMON) System Monitor Process (SMON) Database Writer Process (DBWn) Log Writer Process (LGWR) Checkpoint Process (CKPT) Manageability Monitor Processes (MMON and MMNL) Recoverer Process (RECO)
Theta Join
Produces all combination of tuples from two relations that satisfy the join condition (<,>,=>,=<, etc)
ACID Properties
Properties of a Transaction: Atomicity, Consistency, Isolation, Durability
VKTM or Virtual keeper of Time Process back
Provides a wall clock time and reference time for time interval measurements. VKTM acts as a time publisher for an Oracle instance. VKTM publishes two sets of time: a wall clock time using a seconds interval and a higher resolution time (which is not wall clock time) for interval measurements. The VKTM timer service centralizes time tracking and offloads multiple timer calls from other clients.
_____ cloud is a type of cloud infrastructure that is built by a third-party organization to sell cloud services to the general public
Public
RAC
RAC(Real application cluster) uses Oracle's clustering software to manage a highly available environment. If you need additional hardware resources (such as memory or CPU), or experience hardware failure, you simply add another node (server) to the grid. (Truthfully, it's more complicated than that, but you get the point.)
Diskspaces
Raw devices, - unformatted disk partitions Automatic storage management, - File System, - formatted disk partitions
Heuristic 3
Re-arrange leaf nodes so that the most selective are applied first.
Describe the RECO
Recoverer Process • Used with the distributed database configuration • Automatically connects to other databases involved in in- doubt distributed transactions • Automatically resolves all in-doubt transactions • Removes any rows that correspond to in-doubt transactions If the RECO process fails to connect with a remote server, RECO automatically tries to connect again after a timed interval. However, RECO waits an increasing amount of time (growing exponentially) before it attempts another connection.
Redo Log files
Redo log files store the information from the log buffer. They're written to by the Log Writer (LGWR). Again, you can't read these binary files without the help of the database software. Redo log files don't record select statements. If you forget what you selected, you're just going to have to remember that on your own! Redo log files contain all the information necessary to recover lost data in your database. Every SQL statement that you issue changing data can be reconstructed by the information saved in these files.
Armstrong's Axioms
Reflexivity Augmentation Transivity Decomposition Union Pseudotransivity
Relationship Type vs. Relationship Set
Relationship Type: The schema description of a relationship Relationship Set: The current set of relationship instances represented in the database.
Schedule equivalences
Result equivalence conflict equivalence
Cross Product R x S
Returns all combinations of R and S. Aka 'Cartesian Product' and 'Direct Product'.
The query to join the P_DESCRIPT and P_PRICE fields from the PRODUCT table and the V_NAME, V_AREACODE, V_PHONE, and V_CONTACT fields from the VENDOR table where the values of V_CODE match is _____
SELECT P_DESCRIPT, P_PRICE, V_NAME, V_CONTACT, V_AREACODE, V_PHONE FROM PRODUCT, VENDOR WHERE PRODUCT.V_CODE = VENDOR.V_CODE;
UPDATE tablename ***** [WHERE conditionlist]; The _____ command replaces the ***** in the syntax of the UPDATE command, shown above
SET columnname = expression
What are the mandatory Tablespaces?
SYSTEM and SYSAUX created upon creation of database • The SYSTEM tablespace is used for core functionality (for example, data dictionary tables). • The auxiliary SYSAUX tablespace is used for additional database components. • The SYSTEM and SYSAUX tablespaces should not be used for application data.
oraenv
Script Environment file that change the environment on Linux/UNIX installations: oraenv. (Windows has no such handy little script.) You simply run the script and it asks what database you want to connect to. Then, it sets the rest of your environment accordingly.
Segments
Segments are the next logical storage structure next to tablespaces. Segments are objects in the database that require physical storage and include the following: Tables Indexes Materialized views Partitions Whenever you create a segment, specify what tablespace you want it to be part of. This helps with performance.
Physical design
The part of the design phase of the SDLC in which the logical specifications of the system from logical design are transformed into technology-specific details from which all programming and system construction can be accomplished.
Partial specialization rule
Specifies that an entity instance of the supertype does not have to belong to any subtype.
Disjoint rule
Specifies that if an entity instance of the supertype is a member of one subtype, it cannot simultaneously be a member of any other subtype.
SDL
Specifies the internal Schema
Striping data
Striping is data written across multiple disks to speed up read/write access because there is less contention on an individual disk.
Program-Data Independence
Structure of Data Files must be independent from Access Programs
SQL
Structured Query Language used to communicate with a RDBMS database
Tablespaces
Tablespaces are the first level of logical organization of your physical storage. Each tablespace is responsible for organizing one or more data files. Typically, each tablespace might start attached to one data file, but as the database grows and your files become large, you may decide to add storage in the form of multiple data files. Every 11g database should have the following tablespaces: SYSTEM: Stores the core database objects that are used for running the database itself. SYSAUX: For objects that are auxiliary and not specifically tied to the core features of the database. UNDO: Stores the rollback or undo segments used for transaction recovery. TEMP: For temporary storage.
Read Uncommitted
The DBMS enforcing no concurrency on Reads
Deadlock Prevention
The Scheduler locks ahead at Transactions to stop deadlocks.
Slack time
The amount of time that an activity can be delayed without delaying the entire project.
Local area network (LAN)
The cabling, hardware, and software used to connect workstations, computers and file servers located in a confined geographical area (typically within one building or campus).
Data Abstraction
The characteristic of Database Management Systems that allow Program-Data Independence and Program-Operation Independence
Mechanism (IDEF0)
The people, machines, or systems that are required to perform the activity
Planning
The first phase of the SDLC in which an organization's total information system needs are identified, analyzed, prioritized, and arranged.
Minimum cardinality
The maximum number of instances of one entity that may be associated with each instance of another entity
Relational Model
The most common Database model, storing data in tables with rows and columns.
How many groups are appropriate?
The most common configuration we come across is three. You want enough that the first group in the list can be copied off and saved before the LGWR comes back around to use it. If it hasn't been copied off, the LGWR has to wait until that operation is complete. This can severely impact your system. Thankfully, we rarely see this happen.
Degree
The number of entity types that participate in a relationship
Degree
The number of entity types that participate in a relationship.
Cardinality
The number of instances of entity B that can (or must) be associated with each instance of entity A.
Installation
The organizational process of changing over from the current information system to a new one.
Source/sink
The origin and/or destination of data; sometimes referred to as external entities
Schedule feasibility
The process of assessing the degree to which the potential time frame and completion dates for all major activities within a project meet organizational deadlines and constraints for affecting change.
Acceptance testing
The process whereby actual users test a completed information system, the end result of which is the users' acceptance of it.
Union Compatible
The property of 2 sets where they have the same number of fields and the fields are of the same domain (type).
Cloud computing
The provision of computing resources, including applications, over the Internet so customers do not have to invest in the computing infrastructure needed to run and maintain computing resources.
Data Normalization
The storing of each logical unit of data in only one place in the database
Project execution
The third phase of the project management process in which the plans created in the prior phases are put into action.
what are alert log files
These are special trace entries. The alert log of a database is a chronological log of messages and errors. Oracle recommends that you review the alert log periodically.
Synonyms
To (or more) attributes that have different names but the same meaning.
Mapping Binary 1:M Relationships
To complete the mapping we must include the relationship by adding a foreign key: PROFESSOR(ProfID, Name, Dept) COURSE(CourseID, Topic, Title, ProfID) Notice the foreign key underline is dashed and goes on the M side of the 1:M relationship
The _____ processor is the software component found in each computer that requests data. It received and processes the application's data requests
Transaction Processor (TP) or Application Processor (AP) or Transaction Manager (TM) (chapter 12 -529)
Spurious Tuples
Tuples which are the products of the joining of neither primary nor foreign keys
What is a rule that applies to the two-phase locking protocol?
Two transactions cannot have conflicting locks
The SQL command that modifies an attribute's values in one or more table's rows is _____
UPDATE
Key Elements of Database Development
-Conceptual data modeling- ER -Logical database design- normalized relations -physical database design- specification of storage technology requirements and specification/ creation of appropriate file structures
A weak Entity type
-does not have a primary key attribute -has total participation in an identifying relationship
application middleware
-enables two programmer-written processes on two different processors to communicate with one another in whatever way suited to the overall application -corresponds to application logic framework
location transparency
-even if data is located in different sites, it can be treated as if it were all located in one site
concurrency transparency
-for concurrent transactions, each transaction appears as if it were the only activity in the system -Users and Applications should be able to access shared data or objects without interference between each other
client/server computing
-processing is performed in both client and database server -selected data is sent to client -a solution in which various IS application layers are distributed between client and server
file server computing
-single file serves as database -processing is performed in the client computer only -server is used to store and transport the DB
data replication
-store a copy of the database into two or more sites
Why transactions are needed
1. Computer Failure 2. Transaction/System Error 3. Local errors/exception conditions in Transaction 4. Concurrency Control Enforcement. 5. Disk Failure 6. Physical problems/catastrophes
Assume a designer is using the UNION operator to combine the results from two tables with identical structure, CUSTOMER and CUSTOMER_2. The CUSTOMER table contains 10 rows, while the CUSTOMER_2 table contains 7 rows. Customers Jenna and Howard are included in the CUSTOMER table as well as in the CUSTOMER_2 table. How many records are returned when using the UNION operator?
15
Partial Participation
A constraint applied to a Relation where an Entity may or may not participate in the relation. (>= 0 times)
Functional dependency
A constraint between two attributes which the value of one attribute is determined by the value of another attribute
Disjointness constraint
A constraint that addresses whether an instance of a supertype may simultaneously be a member of two (or more) subtypes
Tangible cost
A cost associated with an information system that can be easily measured in dollars and certainty.
Conceptual data model
A detailed model that captures the overall structure of organizational data and that is independent of any database management system or other implementation considerations.
Serialisation Graph
A directed graph G = (N, E) for a transaction set T where: each node is a Transactions and edges show the conflicts. i.e. if T1 writes a value and T2 reads then: T1 → T2. If there are no cycles, the Schedule is serialisable.
Content management systems (CMS)
A special type of software application for collecting and organizing, and publishing website content.
Union Type
A superclass/subclass relationship with multiple possible superclasses.
End User
A user of a DBMS who queries and updates the database through an interface.
Among client-side extensions, _____ is a Microsoft specification for writing programs that run inside the Internet Explorer browser
ActiveX
what is a password file
Allows users using the SYSDBA, SYSOPER, SYSBACKUP, SYSDG, SYSKM, and SYSASM roles to connect remotely to the instance and perform administrative tasks
Primary key
Arbitrarily chosen candidate key (generally the smallest of the candidate keys)
Archive log files
Archive log files are simply copies of redo log files. They're no different from redo log files except that they get a new name when they're created. Most archive log files have the extension .ARC, .ARCH, or .LOG. We try to use .ARC as that seems most common.
Mapping Unary M:N Relationships
Mapping this relationship requires adding an intersection relation: MECHANIC(MechID, Salary) SUPERVISES(SupervisorID, SuperviseeID) In this case, both SupervisorID and SuperviseeID are foreign keys that reference the primary key MechID. Together they create the composite primary key of SUPERVISES
Mirroring
Mirroring is maintaining multiple redundant copies of data on multiple disks so that if one disk fails, the data is still available (providing fault tolerance).
Dnnn or Dispatcher Process back
Performs network communication in the shared server architecture. In the shared server architecture, clients connect to a dispatcher process, which creates a virtual circuit for each connection. When the client sends data to the server, the dispatcher receives the data into the virtual circuit and places the active circuit on the common queue to be picked up by an idle shared server. The shared server then reads the data from the virtual circuit and performs the database work necessary to complete the request. When the shared server must send data to the client, the server writes the data back into the virtual circuit and the dispatcher sends the data to the client. After the shared server completes the client request, the server releases the virtual circuit back to the dispatcher and is free to handle other clients. Several initialization parameters relate to shared servers. The principal parameters are: DISPATCHERS, SHARED_SERVERS, MAX_SHARED_SERVERS, LOCAL_LISTENER, REMOTE_LISTENER.
Mapping Specialization/Generalization
SUPERTYPE(CommonIdent, CommonAttr1, CommonAttr2) SUBTYPE1(CommonIdent, UniqueAttr1) SUBTYPE2(CommonIdent, UniqueAttr2, UniqueAttr3) As an alternative mapping for total specialization with disjoint relations, you can may consider dropping the supertype and creating tables for each subtype: SUBTYPE1(CommonIdent, CommonAttr1, CommonAttr2, UniqueAttr1) SUBTYPE2(CommonIdent, CommonAttr1, CommonAttr2, UniqueAttr2, UniqueAttr3) SUBTYPE3(CommonIdent, CommonAttr1, CommonAttr2, UniqueAttr4) note: commonattr1 is primary in all and same ID can appear in multiple tables
Data Dictionary/Repository/Information Repository
Stores information about schemas, constraints, design decisions, usage standards, user info
_____ are the result of formatting disorganized data in order to facilitate storage, use and generation of information
Structured data
_____ activities cover all tasks directly related to the day-to-day operations of the DBMS and its applications
System support
T/F? Candidate keys can have multiple columns
TRUE
Transaction States
The states a Transaction can be in; Active, Partial Committed, Failed, Aborted, Committed
Unexpected state interactions
The states of two or more objects interact to cause complex failures, as when an OnlineCart class method operates correctly for all possible Customer object states except one
Update (SQL)
Update a row in a table. General: update "tablename" set "columnname" = "newvalue" [,"nextcolumn" = "newvalue2"...] where "columnname" OPERATOR "value" [and|or "column" OPERATOR "value"]; [] = optional ex. update phone_book set area_code = 623 where prefix = 979; update phone_book set last_name = 'Smith', prefix=555, suffix=9292 where last_name = 'Jones';
OLAP(Online Analytical Processing)
Used for analyzing data. Typically, OLAP is used for business intelligence or data mining, such as budgeting or forecasting.
OLTP: Online Transactional Processing
Used for transaction-oriented applications where the response is immediate and records are modified or inserted regularly.
Program-Operation Independence
User applications can operate on data by invoking certain operations regardless of the operations' implementation.
Alpha testing
User testing of a completed information system using simulated data.
Incorrect summary
When one transaction performs an aggregate function on an item while another transaction is updating some of the items
Dirty read
When one transaction updates an item and then fails while another transaction reads fail before it can be changed back to original value
Consistency
When a Transactions is executed it leaves the Database in a consistent state, i.e. does not break constraints
Inconsistent Analysis Problem
When a Transactions reads a value which is being updated at the same time
Dirty Read Problem
When a Transactions reads an intermediate value, i.e. is going to be updated or rolled-back
Decision support data tend to be non-normalized, _____, and pre-aggregated
duplicated
ARC 0..t or Redo Log Archive back
Writes filled redo logs to the archive log destination(s). It is known as the archiver, The archiver process (ARCn) copies online redo log files to a designated storage device after a log switch has occurred. ARCn processes are present only when the database is in ARCHIVELOG mode, and automatic archiving is enabled. An Oracle instance can have up to 10 ARCn processes (ARC0 to ARC9). The LGWR process starts a new ARCn process whenever the current number of ARCn processes is insufficient to handle the workload. The alert file keeps a record of when LGWR starts a new ARCn process. If you anticipate a heavy workload for archiving, such as during bulk loading of data, you can specify multiple archiver processes with the initialization parameter LOG_ARCHIVE_MAX_PROCESSES. The ALTER SYSTEM statement can change the value of this parameter dynamically to increase or decrease the number of ARCn processes. However, you do not need to change this parameter from its default value of 1, because the system determines how many ARCn processes are needed, and LGWR automatically starts up more ARCn processes when the database workload requires more.
User documentation
Written or other visual information about an application system, how it works, and how to use it.
The _____ is an advanced data definition language that is used to describe the structure of XML data documents
XML schema
Using _____, you can extract data from an XML document and convert it into a text file
XSLT
Output control
a control that ensures that output arrives at the proper destination and is accurate, current, and complete
Security Control
a control that protects the assets of an organization from all threats, with a primary focus on external threats
field combination control
a control that reviews combinations of data inputs to ensure that the correct data are entered
centralized database
a database that is located, stored, and maintained in a single location
Input, process, output (IPO)
a development order that implements input modules first, process modules next, and output modules last
Bottom-up development
a development order that implements low-level detailed modules first Use drivers for testing
Top-down development
a development order that implements top-level modules first and it uses stubs for testing
Document store
a document store good for arbitrary data ex. MongoDB good for fast lookups in tree or list like data bad for relational data and large (>1M entries) data
Public key encryption
a form of asymmetric key encryption that uses a public key for encryption and a private key for decryption
Test case
a formal description of a starting state, one or more events to which the software must respond, and the expected response or ending state Defined based on well understood functional and non-functional requirements Must test all normal and exception situations
Normalization
a formal technique for evaluating and improving the quality of a relational database schema
SGA(System Global Area)
a group of shared memory structures. It contains things like data and SQL. It is shared between both Oracle background processes and server processes.
Foreign key
a key in a relation referenced from another relation corresponds to a relationship in ER
Stub
a method or class developed for unit testing that simulates the behavior of a method invoked that hasn't yet been written
Functional dependency
a one-to-one association between the values of two attributes
Privileged user (part of access controls)
a person who has access to the source code, executable program, and database structure of the system
Registered user (part of access controls)
a person who is authorized to access
Unauthorized user (part of access controls)
a person who isn't allowed access to any part or functions of the system
Composite Key
a primary key that consists of 2 or more attributes that uniquely identify an entity occurrence.
remote procedure calls
a protocol that one program can use to request a service from a program located in another computer on a network without having to understand the network's details
Secure Sockets Layer (SSL)
a standard set of methods and protocols that address authentication, authorization, privacy, and integrity
Database management system
a system software component that manages and controls one or more databases
Usability test
a test to determine whether a method, class, subsystem, or system meets user requirements
Table
a two-dimensional data structure of columns and rows
Middleware Layer
accounts for the front end modules (clients) communicating with multiple back end databases (servers) can have middleware software to access autonomous preexisting databases stored under heterogeneous DBMSs
Transport Layer Security (TLS)
an Internet standard equivalent to SSL
IP Security (IPSec)
an Internet standard for secure transmission of low-level network packets
Identifiers / key
an attribute (or combination of attributes) that uniquely identifies an individual instance of an entity type
Key
an identifier in the relation corresponds to an identifier in ER
data warehouse
an integrated decision support database whose content is derived from the various operational databases. Data warehouses are typically used for reporting or data mining.
Performance test or stress test
an integration and usability test that determines whether a system or subsystem can meet time-based performance criteria
System test
an integration test of an entire system or independent subsystem
prototyping
an iterative process of systems development in which requirements are converted to a working system that is continually revised through close work between analysts and users.
database
an organized collection of logically related data that allows access, retrieval, and use of data
A table that displays data redundancies yields _____
anomalies
Asymmetric key encryption
encryption method that uses different keys to encrypt and decrypt the data
Software code area
are portions of memory used to store code that is being run or can be run.
L in FLWR
assigns variable for rest of query in XQuery
In the context of database usage monitoring, a(n) _____ is a file that automatically records a brief description of the database operations performed by all users
audit log
Data Independence
capacity to change the schema at one level of database system without having to change it for the next higher level 2 types: logical & physical
Describe the Redo Log Buffer
circular buffer in the SGA Holds information about changes made to the database Contains redo entries that have the information to redo changes made by operations such as DML and DDL
Data Model
collection of concepts that can be used to describe a database structure; provides the necessary means to achieve the abstraction; most include a set of basic operations for the database main model used is relational data & its system is SQL object data model has been implemented, but not widespread big-data, key-value storage, and NOSQL systems use varoius data models many are still based on hierarchical/network models
Data manipulation
commands and logic to store and retrieve data
agile software developement
emphasizes individuals and interactions over processes and tools, working software over comprehensive documentation, customer collaboration over contract negotiation, and response to change over following a plan.
thick or fat client
contains all or most of the application logic; most of application logic on the client side
The first step in building an entity-relationship diagram (ERD) is _____
creating a detailed narrative of the organization's description of operations
Functions of the database application inlcude
creating and processing forms, creating and transmitting queries and creating and processing reports
Physical data store
database component that stores the raw bits and bytes of data
A database is called self-describing because
it stores a description of itself
B-Trees
keeps references to our data in sorted order -self balancing -good for looking up values based on RANGE tests
In the relational model, _____ are important because they are used to ensure that each row in a table is uniquely identifiable
keys
what is LRU algorithm
least recently used (LRU) helps to ensure that the most recently used blocks tend to stay in memory to minimize disk access
Partial identifier
like copy number for a video tape that is linked to the strong identifier of movieID
user view
logical description of some portion of the database that is required by a user to perform some task
In the context of the data-information-decision cycle, decisions made by high-level managers trigger actions in an organization's
lower levels (Chapter 15 - 671)
Coordinating, monitoring, and allocating database administration resources is included in a DBA's _____ role
managerial
If one exists, a data modeler uses a _____ as the primary key of the entity being modeled
natural identifier
In the _____ model, the user perceives the database as a collection of records in 1:M relationships, where each record can have more than one parent
network
The SGA contains what data structures
shared pool, database buffer cache, redo log buffer, large pool, java pool, streams pool, fixed SGA
Representational/Implementation Data Models
provide concepts that may be easily understood by end users but is not too far from how the data is organized in the computer storage hide many details of data storage but can be implemented directly on a system represent data by using record structures; sometimes called record-based data model
At the level of middle management, the database must be able to _____
provide the data necessary for tactical decisions and planning
System Global Area (SGA)
shared pool Shared sql area, private sql area Data dictionary cache, Reserved pool Server Result cache, others Large Pool Response queue Request queue Database Buffer Cache redo log buffer Fixed SGA Java pool Steam pool
Equijoin
produces all the combination of tuples from two relations that satisfy only equality conditions
Open Database Connectivity (ODBC)
provides an application programming interface (API) with allows client-side programs to call the DBMS, as long as both client and server machines have the software necessary provided by most DBMS vendors the API can be used to connect several RDBMSs and send query/transaction requests
A server-side extension ____
provides its services to the Web server in a way that is totally transparent to the client browser
logical schema
representation of a database for a particular data management technology. Elements of the relational model which include tables, columns, rows, primary keys, foreign keys, and constraints.
The _____ table space is used for transaction-recovery purposes
rollback segment table space
middleware
said to be the "slash" in client / server
A(n) _____ lock exists when concurrent transactions are granted read access on the basis of a common lock
screen painter and report generator
The _____ component of a CASE tool produces the information system's input/output formats
screen painter and report generator
There are _____ stages in the Database Life Cycle (DBLC)
six phases: database initial study database design implementation and loading testing and evaluation operation maintenance and evolution
Initial State of Database
state of the database when the data is first populated/loaded
Valid State
state that satisfies the structure and constraints specified in the schema
A(n) _____ is a logical storage area in Oracle
tablespace