BCS 260: Module 1: zyBooks Introductory Basicsflogff

When processing transactions, database systems must:

- Ensure transactions are processed completely or not at all. A computer or application might fail while processing a transaction. When failing to process a transaction, the database system must reverse partial results and restore the database to the values prior to the transaction. - Prevent conflicts between concurrent transactions. When multiple transactions access the same data at the same time, a conflict may occur. Ex: Sam selects a seat on a flight. Maria purchases the same seat in a separate transaction before Sam completes his transaction. When Sam clicks the 'purchase' button, his seat is suddenly unavailable - Ensure transaction results are never lost. Once a transaction completes, transaction results must always be saved on storage media, regardless of application or computer failures. The above requirements are supported in sophisticated transaction management subsystems of most database systems.

Entities, relationships, and attributes are depicted in ER diagrams

- Rectangles represent entities. Entity names appear at the top of rectangles. - Lines between rectangles represent relationships. - Text inside rectangles and below entity names represent attributes. ER diagrams are usually supplemented by textual descriptions of entities, relationships, and attributes

The logical design is depicted in a table diagram. Table diagrams are similar to ER diagrams but more detailed:

- Rectangles represent tables. Table names appear at the top of rectangles. - Text within rectangles and below table names represents columns. - Solid bullets (●) indicate key columns. - Empty bullets (○) and arrows indicate columns that refer to keys.

What is a database?

- a collection of data in a structured format - In principle, databases can be stored on paper or even clay tablets. In practice, however, modern databases are invariably stored on computers - The database structure ensures that similar data is stored in a standardized manner.

1. The database designer establishes the structure of the database and determines the data to be collected and stored. 2. The database administrator ensures the database is available and secure. 3. A database programmer uses query languages and programming languages to develop applications for database users. 4. Database users are the primary consumers of database data through applications or with query languages. Questions: 1) Which role is responsible for providing access to the database? 2) Which role is responsible for defining the detailed database design? 3) Which role uses an application to query a database and generate a report?

1) Database administrator (Database administrators establish procedures for database security and user access.) 2) Database designer (Database designers are responsible for the database design, deciding how data should be organized for quick access.) 3) Database users (Database users typically access the database via an application. Advanced users may use a query language instead.)

A banks database: 1) Banking data is stored in a database and is managed by a database system. 2) A bank customer uses an application to perform bank transactions. 3) Banking transactions cause the database system to modify the bank's database and update the user's account. 1) Where would a bank customer's account data be directly stored? 2) Which of the following manages the Bank database? 3) Which of the following would prevent unauthorized access to the Bank database? 4) How would a bank customer access their bank data?

1) In the bank database (A customer's banking data is stored in a database) 2) The banking database system (A database system provides support for database management, security, and access.) 3) The banking database system (Database systems ensure data is secure and safe.) 4) Through the customer banking application (Applications interact with a database system to access data that is stored in databases)

1. A list of bank transactions is stored in the text file bookkeeping.txt. Each persons' transactions include a date, type, and the amount of money paid or received. 2. Two programs access the text file. One adds new transactions, and the other calculates account balances. 3. When program A writes transactions quickly to the file, Program B misses the $4000 deposit and calculates Raul's balance incorrectly. 4. Program A may write an erroneous transaction. 5. A lack of adequate security could allow unauthorized users to access the file. 1) If program A writes to bookkeeping.txt the same time that program B reads from bookkeeping.txt, what can potentially go wrong? 2) Why was an unauthorized user permitted to perform the transaction that added $999999 to bookkeeping.txt? 3) What is wrong with the transaction posted on 13/31 for the amount +6z@yy?

1) Program B may only be reading partial data (Program B may miss a recent update made to the file by Program A. A database can manage reading and writing to shared data to avoid such problems.) 2) The user had write access to the text file (An operating system can limit read or write access to a file, providing some amount of security. But a database provides additional layers of security and access control. Ex: One user can be permitted to read account balances while another user is permitted to change account data.) 3) The data in the transaction contains invalid pieces of data (Text files do not restrict invalid or garbled data from being entered, so files can be corrupted. A database ensures that data is consistent with structural and business rules.)

For small, simple databases, the database design process can be informal and unstructured. For large, complex databases, the process has three phases:

Analysis Logical design Physical design

Database software is organized in layers

Applications interact with a query language on one layer, and a query language interacts with a database system on another layer. Other software layers, such as the operating system, are beyond the scope of this material.

The _______ statement creates a table with no rows. An _______ statement is necessary to insert rows into a table.

CREATE INSERT

The SQL __________ statement creates a new table by specifying the table and column names.

CREATE TABLE

Example: 1. The CREATE TABLE statement names the new table "Employee". 2. The column names and data types are separated by commas.

CREATE TABLE Employee ( ID INT, NAME VARCHAR(60), BIRTHDATE DATE, SALARY DECIMAL(7,2) );

__________ database systems, like Oracle Database and Microsoft SQL Server, are sponsored by for-profit companies and licensed for a fee.

Commercial

Recovery

Computers, database systems, and individual transactions occasionally fail. Database systems must recover from failures and restore the database to a consistent state without loss of data.

The four common queries are sometimes referred to as CRUD operations, an acronym for

Create, Read, Update, and Delete data

The _______ data type is for a date only.

DATE

The _______ data type stores a date and a time.

DATETIME

What is Format?

Data may be produced as numbers, text, image, audio, or video. Ex: A phone's proximity sensor generates raw numbers, and a satellite captures images.

Security

Database systems ensure authorized users only access permissible data. Database systems also protect against hackers by encrypting data and restricting access.

Rules

Database systems ensure data is consistent with structural and business rules. Ex: When multiple copies of data are stored in different locations, copies must be synchronized as data is updated. Ex: When a course number appears in a student registration record, the course must exist in the course catalog.

Database systems have different capabilities, and component details vary significantly.

Ex: Some database systems do not support transactions and therefore have no transaction manager. Ex: The storage manager implementation depends on the physical structure of data on storage media.

Some data types are followed by one or two numbers in parentheses, indicating the size of the data type.

Example: VARCHAR(10) indicates ten characters. DECIMAL(10, 3) indicates ten significant digits, including three after the decimal point.

Data types can be numeric, textual, or complex.

Examples: - INT stores integer values. - DECIMAL stores fractional numeric values. - VARCHAR stores textual values. - DATE stores year, month, and day.

T / F The query processor has direct access to the database data on storage media.

False All access to the database data must go through the storage manager.

T / F Every database query must be logged by the transaction manager to recover the database in the event of a system failure.

False All inserts, updates, and deletes must be logged in order to correctly restore database changes. Reads are not needed to restore database changes, however, and thus are not logged.

T / F The insert, select, update, and delete queries are the only types of commands necessary to interact with a database system.

False Other database commands are necessary to create and delete databases, tables, columns, and other database objects. However, the insert, select, update, and delete queries are the most common database commands

T / F Without query optimization, the storage manager cannot retrieve the database data.

False Query optimization ensures the most efficient instructions are sent to the storage manager, but the storage manager can retrieve the data with efficient or inefficient instructions.

T / F The catalog allows the storage manager to quickly locate the requested data.

False The catalog just describes the database. The index helps locate data quickly by providing information about where on the storage media certain pieces of data can be found.

Physical design can be depicted in diagrams.

However, logical design is more important for database users and programmers, so physical design diagrams are not commonly used.

In relational databases, logical and physical design affect queries differently. How?

Logical design affects the query result. Physical design affects query processing speed but never affects the query result.

Authorization

Many database users should have limited access to specific tables, columns, or rows of a database. Database systems authorize individual users to access specific data.

The newer non-relational systems are called _____, for 'not only SQL', and are optimized for big data.

NoSQL

_______ systems were developed in response to the growth of the internet, which generates large volumes of unstructured data.

NoSQL

__________ software is licensed at no cost and can be inspected and modified by the public. The sponsor of the software must review and adopt any software changes to the original project's source code.

Open source

Large, complex databases that are shared by many users have special requirements:

Performance Authorization Security Rules Recovery

_______ databases like MySQL have a tabular structure and support the SQL query language.

Relational

All relational database systems support the ___ query language

SQL

An _______ is a database command, such as a query that inserts, selects, updates, or deletes data

SQL statement

The SQL standard is sponsored by the American National Standards Institute (ANSI) and the International Organization for Standardization (ISO).

SQL was first developed at IBM in the 1970s as an experimental query language for a prototype relational database. At the time, IBM was the dominant computer company, so SQL became the dominant relational query language. Today, all relational database systems support SQL.

Data can vary in several important ways. Name the three:

Scope Format Access

What is Access?

Some data sources are private while others are made publicly available. Ex: A retail company may use private customer data to discover purchasing behavior patterns, but a government may be required by law to share certain data sets.

SQL stands for and what does it include?

Structured Query Language it includes statements that read and write data, create and delete tables, and administer the database system.

_______ is the standard query language of relational database systems.

Structured Query Language or SQL

What is Scope?

The amount of data produced and collected can vary. Ex: A small business might track an inventory of a few thousand items, but a large commerce website might track billions of items

T / F Given the data in the Bank database, a select query for accounts with negative balances would return nothing.

True No accounts have a negative balance. While most select queries return something, a select query can also return nothing.

T / F An update query cannot update data that isn't in the database.

True Data must be in the database for an update query to make an update.

Performance

When many users and applications simultaneously access large databases, query response time degrades rapidly. Database systems maintain fast response times by structuring data properly on storage media and processing queries efficiently.

The shift from ______ to _______ data facilitated the rise of large computer databases.

analog to digital

The _______ phase specifies database requirements without regard to a specific database system

analysis Requirements are represented as entities, relationships, and attributes.

An _______ is a descriptive property of an entity.

attribute

massive volumes of online data, called ___ ____, often with poorly structured or missing information

big data

Each column is assigned a _______ that indicates the format of column values.

data type

A ________ is a specification of database objects such as tables, columns, data types, and indexes.

database design Database design also refers to the process used to develop the specification.

A _______ query removes data from the database.

delete

DELETE

deletes rows from a table example: DELETE FROM Account WHERE ID = 572;

What is the architecture of a database system?

describes the internal components and the relationships between components. At a high level, the components of most database systems are similar:

Historically, data was mostly analog,

encoded as continuous variations on various physical media. Ex: Audio was recorded as vibrations impressed on vinyl disks. Images were recorded as chemicals on celluloid tapes.

Today, data is mostly digital,

encoded as zeros and ones on electronic and magnetic media.

Transaction manager

ensures transactions are properly executed. The transaction manager prevents conflicts between concurrent transactions. The transaction manager also restores the database to a consistent state in the event of a transaction or system failure.

An ______ is a person, place, activity, or thing.

entity

The term database sometimes refers to a database system rather than the data managed by the system.

he meaning is usually clear from context.

An _______ query inserts new data into the database.

insert

INSERT

inserts rows into a table example: INSERT INTO Account VALUES (290, 'Ethan Carr', 5000);

Query processor

interprets queries, creates a plan to modify the database or retrieve data, and returns query results to the application. The query processor performs query optimization to ensure the most efficient instructions are executed on the data.

Catalog, also known as a data dictionary

is a directory of tables, columns, indexes, and other database objects. Other components use catalog information to process and execute queries.

Log

is a file containing a complete record of all inserts, updates, and deletes processed by the database. The transaction manager writes log records before applying changes to the database. In the event of a failure, the transaction manager uses log records to restore the database.

Open source software

is software that anyone can inspect, copy, and modify with no licensing fee.

What is a transaction?

it is a group of queries that must be either completed or rejected as a whole. Execution of some, but not all, queries results in inconsistent or incorrect data. Ex: A debit-credit transaction transfers funds from one bank account to another. The first query removes $100 from one account and the second query deposits $100 in another account. If the first query succeeds but the second fails, $100 is mysteriously lost. The transaction must process either both queries or neither query.

What is a query?

it is a request to retrieve or change data in a database

What is a query language?

it is a specialized programming language, designed specifically for database systems. Query languages read and write data efficiently, and differ significantly from general-purpose languages such as Python, Java, and C++.

What is a database application?

it is software that helps business users interact with database systems. Many databases are complex, and most users are not familiar with query languages. Consequently, direct database access is usually not feasible. Instead, programmers write applications to simplify the user experience and ensure data access is efficient and secure.

What is a database system (also known as a database management or DBMS)?

it is software that reads and writes data in a database. Database systems ensure data is secure, internally consistent, and available at all times. These functions are challenging for large databases with many users, so database systems are complex.

A _______ is a column used to identify individual rows of a table. Tables, keys, and columns are specified in SQL with CREATE TABLE statements.

key

Relationships are depicted as _______ on ER diagrams.

lines

The _______ phase implements database requirements in a specific database system.

logical design

UPDATE

modifies data in a table example: UPDATE Account SET Balance = 4500 WHERE ID = 831;

What is data

numeric, textual, visual, or audio information that describes real-world systems.

The ________ phase adds indexes and specifies how tables are organized on storage media.

physical design

A database system responds to _______ written in a query language.

queries

A _______ is a command for a database that typically inserts new data, retrieves data, updates data, or deletes data from a database.

query

A _______ is a computer programming language for writing database queries.

query language

Entities are depicted as _______ on ER diagrams.

rectangles

Most leading database systems are

relational

A _______ is a link between entities

relationship

SELECT

retrieves data from a table. example: SELECT Name FROM Account WHERE Balance > 3000;

The logical design, as specified in SQL and depicted in a table diagram, is called a database

schema

A _______ query retrieves information from the database.

select

Insert, update, and delete all change the database. Only the _______ query does not change the database.

select

What is a relational database?

stores data in tables, columns, and rows, similar to a spreadsheet. All data in a column has the same format. All data in a row represents a single object, such as a person, place, product, or activity.

Small databases that are shared by one or two users can be managed in a

text file or spreadsheet Text files and spreadsheets are inadequate, however, as databases grow in size, complexity, and use.

Why is data collected and processed?

to aid in a variety of tasks, such as forecasting weather, analyzing financial investments, and tracking the global spread of pandemics.

Storage manager

translates the query processor instructions into low-level file-system commands that modify or retrieve data. Database sizes range from megabytes to many terabytes, so the storage manager uses indexes to quickly locate data.

An _______ query changes existing data in the database.

update