Section 6 - Data

What does the Selection Operation do?

takes rows from one table and creates a new table

What is Database Research?

the act of analyzing and converting data into information that can be used in decision-making

What is the UNION operation?

UNION operation combines data from different tables that have the same data types or attributes. Used after the SELECT command

What is the output of the following SQL code: SELECT* FROM Patient CROSS JOIN Nurse;

selects all records from Patient and Nurse tables, explicitly stated but same result

What types of responsibilities fall into a Database Administrators job functions

- Database Security: only authorized users can access - Database Tuning: optimizing database performance - High Availability: making and maintaining replicas/copies of data in case of outage - Business Continuity: core business function unaffected even with disruption - Backup & Recovery: planning and executing backup+rec procedures - Reporting: writing queries + generating reports - Designing: developing database apps

What is a Database?

A database is a collection of records

List the 2 types of DBMS layers

Application Layer & Database Management Layer

Which database can increase resources to accommodate for demands on the system while maintaining high availability even in the case of local or regional outages?

Cloud database

How do Relational Databases work?

Contain database tables that can be joined together in logical ways, each record has a unique identifier called a primary key. 2 primary keys on separate tables can match logically to create a foreign key or relational link.

Which data management tool deals with analyzing specific categories of historical information and making decisions based on that information?

Data and business intelligence

How do Hierarchical Databases work?

Data is organized in a tree structure like an organizational chart, one parent for each record with optional nodes forming sub-tress called segments

Which factors should Mei consider when choosing a file organization system?

Data retrieval speed, Scalability, Security. Data retrieval speed impacts the experience of the user when interacting with the database. Scalability allows the organization to expand the system's capacity as needed. Security focuses on the confidentiality, integrity, and accessibility of the data kept in the database.

What is the collection of names, addresses, and account balances of all the patients of Mei's office more technically known as?

Database. A database is a collection of records. The name, address, and account balance of a patient form the patient's record.

What are the most common types of NoSQL databases?

Document (stores data in document-like structures) Graph (stores data in nodes to accelerate querying) Key-Value (simple and uses unique keys that matches w/ associated values) Wide-Column Stores (table-style databases, store data across tables that can have large numbers of columns

List the 3 types of Database Models

Flat-File, Relational, Hierarchical

What is a tree-like structure of records in a database referred to as?

Hierarchical database structure. In hierarchical databases, data is organized in a tree structure, much like an organizational chart.

List the 5 forms of data used in databases

Integers, Floating-Point Numbers, Characters, Character Strings, Boolean Values

Mei's database administration function includes

Managing user access to resources. In an organization, it is typically the responsibility of the database administrator to manage the access rights of users to various data resources.

What are OO Databases?

Object-oriented databases stores & relates data directly without using inter-table relations. Different from Relational databases

What are Cloud Databases?

Optimized to work in a cloud environment practical benefits include the ability to increase resources to accommodate for demands on the system, to be highly available even in the case of local or regional outages, and to support SaaS (software as a service) cloud deployments

Which of the following systems are examples of database applications in Mei's medical office?

Patient Scheduling System, Billing System, Equipment Inventory System, Accounting System

What are the name, address, and account balance of each individual patient in Mei's office known as?

Record. The name, address, and account balance of each individual patient of the office forms a record of the patient. A collection of records forms a database.

What is the output of the following SQL code: SELECT PatientID, LastName FROM Patient UNION SELECT NurseID, LastName FROM Nurse;

SELECT command takes PatientID, LastName from the Patient tables and UNION unifies it with the NurseID, LastName from Nurse file record. Types are matched by the same data type; NurseID=PatientID etc

What is the output of the following SQL code: SELECT* FROM Patient, Nurse;

Selects all records from the Patient and Nurse tables, implicit

What is a NoSQL Database?

Stands for Not Only SQL. More dynamic then traditional & pre-organized Relational databases. Accommodates a variety of data models and is very useful in organizing large sets of distributed data

What is SQL and how does it work?

Structured query language (SQL) code includes queries to identify what data should be retrieved or how the data should be manipulated DBMS complements the SQL code by providing a mechanism to send the code to and from the database server

How does SELECT define the user-requested criteria?

Tests use attribute names, constants, and relational operators, such as AND and OR, which define the criteria

What is the expected output for the following SQL query? SELECT * FROM Patient WHERE LastName = 'Moore';

The SELECT * FROM Patient statement identifies that all records from the table Patient have been queried. The WHERE LastName = 'Moore'; statement identifies the specific patient's last name being requested.

How does the SELECT operation work?

The operation tests the selection criteria against the user specified table. Each row is tested and once a condition is met the result is output in the result table.

What is the output of the following SQL: SELECT * FROM Patient WHERE PatientID = '223344';

The output would be the complete Patient Record for ID 223344. The SELECT statement identifies the records that are being requested, and the asterisk (*) means everything from that table. The parameter after FROM identifies the table name (Patient). The next keyword, WHERE, is the condition the query is requesting.

What is a Data Lake?

a system of data stored in raw format. It is usually a single store of all enterprise data as well as information derived from data during reporting, transactions, or other activities. Data lakes can include both structured data, such as tables, and unstructured data, such as audio and video recordings.

What is the JOIN operation?

combines two tables, but records are only appended when a matching criterion is met. Can be either implicitly or explicitly joined

What is Distributed Database?

consists of data residing on different machines. For instance, an international corporation might store and maintain local employer records at local sites, yet link those records via a network to create a single, distributed database.

What is the PRODUCT operation?

creates a results table that includes all attributes for the 2 tables 1:1 ratio of rows matching.

What does DBMS stand for?

database management system

How does a Flat-File Database generally work?

flat-file database stores data in a plain text file, each line of the text file holds one record. Fields are separated by delimiters like spaces or commas