Database Managent

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 SQL CREATE TABLE statement creates a new table by specifying the table and column names.Each column is assigned a data type that indicates the format of column values. Data types can be numeric, textual, or complex. Ex:

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

Refer to the ER and table diagram. CustomerID is:

A column that refers to a key.

What is 'Category'?

Attribute - A Category, such as fiction and history, describes a Book and appears inside the Book rectangle. Therefore Category is an attribute.

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.

Which role is responsible for providing access to the database?

Database administrator

Which role is responsible for defining the detailed database design?

Database designer

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.

Which role uses an application to query a database and generate a report?

Database users

What is 'Sale'?

Entity - A sale is a thing tracked in the database, with properties unit price, quantity, and date. Entities are depicted as rectangles on ER diagrams.

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.

Is there a direct relationship between Customer and Book?

No - Customer and Book are indirectly related via Sale, but not directly related. No line appears between Customer and Book.

Relational systems were not initially designed for big data and, as a result, many non-relational systems have appeared since 2000. The newer non-relational systems are called ______, for 'not only SQL', and are optimized for big data.

NoSQL

What is 'Writes'?

Relationship - Author-Writes-Book is a relationship between the Author and Book entities. Relationships are depicted as lines on ER diagrams.

Only one of the queries does not change the database contents.

True

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.

Refer to the ER and table diagram. What element of the ER diagram does the BookAuthor table implement?

Writes

Query

a request to retrieve or change data in a database.

Query language

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++.

The growth of the internet in the 1900s generated massive volumes of online data, called ______ ______, often with poorly structured or missing information.

big data

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

catalog

In relational databases, logical and physical design affect queries differently. Logical design affects the query result. Physical design affects query processing speed but never affects the query result. The principle that physical design never affects query results is called ______ ______.

data independence

Database Designer

determines the format of each data element and the overall database structure . Database designers must balance several priorities , including storage , response time , and support for rules that govern the data . Since these priorities often conflict , database design is technically challenging .

Database Programmer

develops computer programs that utilize a database . Database programmers write applications that combine database query languages and general - purpose programming languages . Query languages and general - purpose languages have significant differences , so database programming is a specialized challenge.

Database User

is a consumer of data in a database. Database users request, update, or use stored data to generate reports or information. Database users usually access the database via applications but can also submit queries directly to the database system.

Database administrator

is responsible for securing the database system against unauthorized users. A database administrator enforces procedures for user access and database system availability.

Database Application

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.

Database System

is software that reads and writes data in a database. It ensures data is secure, internally consistent, and available at all times.

The ________ 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.

log

The query processor performs _____ __________ to ensure the most efficient instructions are executed on the data.

query optimization

The _____ ________ interprets queries, creates a plan to modify the database or retrieve data,and returns query results to the application.

query processor

The ______ ______ 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.

storage manager

The ________ ________ 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.

transaction manager