Database Systems - Quiz 1

What is an example of a "procedural" language?

-Java -C++ -C# -Ada These languages do not allow the user to specify a "what" without a "how."

Define OLTP (Online Transaction Processing)

2D relational database; a database that is designed primarily to support a company's day-to-day operations; short term, constantly updating data

Define record

A logically connected set of one or more fields that describes a person, place, or thing. For example, the fields that constitute a record for a customer might consist of the customer's name, address, phone number, date of birth, credit limit, and unpaid balance.

Why is the distinction between logical and physical format important?

A machine can spread data out over several disks to allow a user to access several parts of a file at once without the user having to know where the data is actually stored. It adds a level of abstraction between the user and the data that increases speed, reliability, and possibly redundancy.

Define candidate key

A minimal superkey; a key that does not contain a subset of attribute that is itself a superkey

Define enterprise database

A multi-user database that is used by the entire organization and supports many users (more than 50 - usually hundreds) across many departments

Define work-group database

A multi-user database that supports a relatively small number of users (usually fewer than 50) or a specific department within an organization

Define database

A shared, integrated computer structure that stores a collection of end-user data (raw facts of interest to the end user) and metadata (data about data)

Define desktop database

A single-user database that runs on a personal computer

Define foreign key (FK)

An attribute or attributes in one table whose values must match the primary key in another table or whose values must be null

Define superkey

An attribute or attributes that uniquely identify each entity in a table

Define dependent

An attribute whose value is determined by another attribute

Define primary key (PK)

An identifier composed of one or more attributes that uniquely identifies a row; a candidate key selected as a unique entity identifier

Define determinant

Any attribute in a specific row whose value directly determines other values in that row

What is a NoSQL database? (Not Only SQL)

Generally used to describe a new generation of database management systems that is not based on the traditional relational database model "document-structured" (not organized into tables) (you don't have to define the schema before importing data)

Define physical format

How the machine stores the physical bytes within the given file

Define logical format

How the user sees the data associated with a given file

Define OLAP (Online Analytical Processing)

Multidimensional database; a set of tools that work together to provide an advanced data analysis environment for retrieving, processing, and modeling data from the data warehouse; longer term; more static data

Define distributed database

-A database that supports data distributed across several different sites -Usually requires a well-defined infrastructure to implement and operate the database

Define centralized database

-A database that supports data located at a single site -Usually requires a well-defined infrastructure to implement and operate the database

Identify 3 common problems with file-based data processing.

-Lengthy development times -Difficulty getting quick answers -Complex system administration -Lack of security and limited data sharing -Extensive programming

Advantages of cloud database

-Lower expenses (no server hardware/infrastructure) -Provides defined performance measures (data storage capacity, required throughput, and availability) for the database -Data owner does not have to know what hardware and software is being used to support their database -Performance capabilities can be renegotiated with the cloud provider as the business demands on the database change

Example of commercial single-user database

-Microsoft Access -Paradox

Example of multi-user database

-Microsoft SQL -Oracle

Disadvantages of cloud database

-No control over security or integrity if there is an outage -More tied to a vendor

If we categorize databases based on how many users they support, what are the types of databases?

-Single-user database -Desktop database -Multi-user database -Work-group database -Enterprise database

Example of metadata in database

-Whether or not a particular field is a key of some kind -ID #'s that are correlated to records but do not pertain to the concrete object the data represents -Any data type information

Define data dictionary

A DBMS component that stores metadata. It contains data definitions as well as data characteristics and relationships. It may also include data that is external to the DBMS.

Define field

A character or group of characters (alphabetic or numeric) that has specific meaning. It is used to define and store data.

Define tuple

A table row

Define structural dependence

Access to a file is dependent on its structure

Define performance tuning

Activities that make a database perform more efficiently in terms of storage and access speed; Taking something that is right and tweaking it to make it even more efficient (ex. indexing)

Example of a challenge unique to a multi-user database that the DBMS must manage.

Asynchronous edit and access: the DB can be edited by more than one person at any given time and be accessed by more than one person. The data served cannot be inconsistent due to simultaneous edits.

Why is SQL a "non-procedural" language?

It allows the user to specify commands without having to specify how said commands are to be carried out.

Does a NoSQL database use SQL?

It can

Consider the database used by OC for student enrollment. Is this primarily an OLTP or an OLAP database?

OLTP because we make frequent changes to it

Define file

A collection of related records. For example, it might contain data about the students currently enrolled at OC.

Define data

Raw facts (ex. temperature)

Define DBMS (Database Management System)

A collection of programs that manages the database structure and controls access to the data stored in the database

Define referential integrity

A condition by which a dependent table's foreign key must have either a null entry or a matching entry in the related table

Define data anomaly

A data abnormality in which inconsistent changes have been made to a database. For example, an employee moves, but the address change is not corrected in all files in the database.

Define cloud database

A database that is created and maintained using cloud data services, such as Microsoft Azure or Amazon AWS

Define secondary key

A key used strictly for data retrieval purposes. For example, customers are not likely to know their customer number (primary key), but the combination of last name, first name, middle initial, and telephone number will probably match the appropriate table row

Define composite key

A multiple-attribute key

Define metadata

Data about data, through which the end-user data is integrated and managed

Why would you need to "log in" to a database?

Databases want only those connected to the information or those that have access to the data, to be the only ones that have access.

Define schema

Definition of all the tables

Example of a rule that a DBMS might enforce to ensure data integrity.

Enforcing a relationship because you couldn't make some certain changes such as deletion of a record if another record depended on it.

Define data redundancy

Exists when the same data is stored unnecessarily at different places

What is the most common type of database structure?

Relational Database

Define information

Result of processing raw data to reveal its meaning (ex. temperature is a raw fact - is it Celsius, Fahrenheit, body temperature, machine temperature, air temperature?..)

What is the defacto standard for query language for most DBMS?

SQL (structured query language): Non-procedural

Define multi-user database

Supports multiple users at the same time

Define commercial single-user database

Supports only one user at a time

Define null

The absence of an attribute value (it's not blank)

Define functional dependence

Within a relation R, an attribute B is functionally dependent on an attribute A if and only if a given value of attribute A determines exactly one value of attribute B.

How does structural dependence impact software maintenance?

The file system application programs are affected by changes in the file structure, so all of the file system programs must be modified to conform to new file structures.

Define entity integrity

The property of a relational table that guarantees each entity has a unique value in a primary key and that the key has no null values