d426 1

data

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

• scope:

• format

• access

database

collection of data in a structured format

dbms

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.

query

a request to retrieve or change data in a database

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.

can access data but do not store data.

information management system

is a software application that manages corporate data for a specific business function.

includes a database system as well as other components, such as a user interface, business logic, and interfaces to other systems.

admin

is responsible for securing the database system against unauthorized users.

enforces procedures for user access and database system availability.

designer

determines the format of each data element and the overall database structure.

balance several priorities, including storage, response time, and support for rules that govern the data.

programmer

develops computer programs that utilize a database

write applications that combine database query languages and general-purpose programming languages.

user

a consumer of data in a database.

request, update, or use stored data to generate reports or information. usually access the database via applications but can also submit queries directly to the database system.

transaction

is a group of queries that must be either completed or rejected as a whole.

Prevent conflicts between concurrent transactions.

Ensure transaction results are never lost.

architecture

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

query processor

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

storage manager

translates the query processor instructions into low-level file-system commands that modify or retrieve data.

uses indexes to quickly locate data.

transaction manager

ensures transactions are properly executed.

prevents conflicts between concurrent transactions.

restores the database to a consistent state in the event of a transaction or system failure.

log

a file containing a complete record of all inserts, updates, and deletes processed by the database. The transaction manager writes it

catalog

also known as a data dictionary, is a directory of tables, columns, indexes, and other database objects. Other components use _______ information to process and execute queries.

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.

sql

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

statement

sqlt database commands.

perform a broad range of database operations, such as retrieving data, editing data, creating a database, defining transactions, and authorizing users to access data.

common ones: delete, insert, update, select

create table

statement creates a new table by specifying the table and column names. creates a table with no rows. An INSERT statement is necessary to insert rows into a table.

Each column is assigned a data type that indicates the format of column values. Data types can be numeric, textual, or complex.

• INT stores integer values.

• DECIMAL stores fractional numeric values.

• VARCHAR stores textual values.

• DATE stores year, month, and day.

database design

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

1. Conceptual design

2. Logical design

3. Physical design

conceptual design

phase specifies database requirements without regard to a specific database system. Requirements are represented as entities, relationships, and attributes. A

er diagram

• Rectangles with round corners represent entities. Entity names appear at the top of rectangles.

• Lines between rectangles represent relationships.

• Text inside rectangles and below entity names represent attributes.

logical design

phase implements database requirements in a specific database system. converts entities, relationships, and attributes into tables, keys, and columns.

table diagram

show logical design

• Rectangles with square corners represent tables. Table names appear at the top of rectangles.

• Text within rectangles and below table names represents columns.

• Bullets (●) indicate key columns.

• Arrows between tables indicate columns that refer to keys. The tail of the arrow is aligned with the column and the arrow points to the table containing the key.

key

is a column used to identify individual rows of a table

schema

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

physical design

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

specified with SQL statements such as CREATE INDEX and, like logical design, is specific to a database system.

diagrams are not commonly used.

data independence.

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

allows database designers to tune query performance without changes to application programs.

API

It is a library of functions or classes that lets a programming language communicate with a database. The program uses these functions to connect to the database, run queries, and get the results back.

mysql

leading relational database system, sponsored by Oracle

• community: free. university courses and training

• enterprise: paid version for managing commercial databases

• heatwave: commercial woth additional analytics and ML

database model

• Data structures that prescribe how data is organized.

• Operations that manipulate data structures.

• Rules that govern valid data.

relational model

is a database model based on a tabular data structure. The model was published in 1970 by E. F. Codd of IBM and released in commercial products around 1980.

big data

characterized by unprecedented data volumes and rapidly changing data structures. caused by rise of internet

set

an unordered collection of elements enclosed in braces. Ex: {a, b, c} and {c, b, a} are the same,

tuple

an ordered collection of elements enclosed in parentheses. Ex: (a, b, c) and (c, b, a) are different

table data structure

• A table has a name, a fixed tuple of columns, and a varying set of rows.

• A column has a name and a data type.

• A row is an unnamed tuple of values. Each value corresponds to a column and belongs to the column's data type.

• A data type is a named set of values, from which column values are drawn.

relational algebra

• Select selects a subset of (or all) rows of a table.

• Project selects one or more columns of a table.

• Product lists all combinations of rows of two tables.

• Join combines two tables by comparing related columns.

• Union selects all rows of two tables.

• Intersect selects rows common to two tables.

• Difference selects rows that appear in one table but not another.

• Rename changes a table name.

• Aggregate computes functions over multiple table rows, such as sum and count.

the result of relational operations is always a table, the result of an SQL query is also a table.

relational rules

are part of the relational model and govern data in every relational database. Ex:

• Unique primary key. All tables have a primary key column, or group of columns, in which values may not repeat.

• Unique column names. Different columns of the same table have different names.

• No duplicate rows. No two rows of the same table have identical values in all columns.

business rule

are based on business policy and specific to a particular database. Ex: All rows of the Employee table must have a valid entry in the DeptCode column.