Title: Theory / Important / CS / Algo / Database / Database System Concept 6 - McGraw Hill+27{ / 1 Introduction / Concept
Tags: fromMath mathToDef examplesSwitchExample1 hasSeenConjDef mathToSee FromInstruction automatedNewLineAfterCommaSpace computer_to_instruction hasNotNewEffectReturnsMeaning seen_description

Name: Database-management system
Definition: Collection of interrelated data and a set of programs to access those data.
Page: 2
Chapter: 1
Abbreviation: DBMS

Name: Data inconsistency
Definition: The fact that various copies of the same data which no longer agree.
Page: 4
Chapter: 1
Section: 2

Name: Data redudancy
Definition: Data copied in multiple places.
Page: 4
Chapter: 1
Section: 2

Name: Physical level of a database
Definition: How the data are actually stored.
Page: 6
Chapter: 1
Section: 3.1

Name: Logical level of a database
Definition: What data are stored in the database, and what relationships exist among those data.
Page: 6
Chapter: 1
Section: 3.1

Name: A view level of a database
Definition: What some user may see from the database.
Page: 6
Chapter: 1
Section: 3.1

Name: An instance of \(D\)
Definition: The collection of information stored in the database at a particular moment.
Page: 8
Chapter: 1
Section: 3.2
Variables: \(D\) a database

Name: The schema of \(D\)
Definition: The overall design of the database.
Page: 8
Chapter: 1
Section: 3.2
Variables: \(D\) a database

Name: A data-definition language
Definition: A language to specify the database schema.
Page: 9
Chapter: 1
Section: 4
Example: The part of the language with
create table name (arg_1 type_1, ..., arg_n type_n)
Abbreviation: DDL
Applied to: sql

Name: A data-manipulation language
Definition: A language to express database queries and updates.
Page: 9
Chapter: 1
Section: 4
Example: the part of the language with
SELECT * from table;
Abbreviation: DML
Applied to: sql

Name: Procedural DML
Definition: A DML which requires a user to specify what data are needed and how to get those data.
Page: 10
Chapter: 1
Section: 4.1
Example: Relational algebra

Name: Declarative DML
Definition: A DML which requires a user to specify what data are needed without specifying how to get those data.
Page: 10, 48
Chapter: 1, 2
Section: 4.1, 5
Example: Tuple relational calculus,
Name2: nonprocedural DML
Example2: domain relational calculus.

Name: A query
Definition: A statement requesting the retrieval of information.
Page: 10
Chapter: 1
Section: 4.1

Name: A query language
Definition: The portion of a DML that involves information retrieval.
Page: 10
Chapter: 1
Section: 4.1

Name: Domain constraint
Definition: A constraint of a value that an attribute can take.
Page: 11
Chapter: 1
Section: 4.1

Name: Referential Integrity constraints
Definition: A constraint stating that a value that appears in one relation for a given set of attributes also appears in a certain set of attributes in another relation.
Page: 11, 131
Chapter: 1, 4
Section: 4.1, 4.5
Example: foreign key constraint
Name2: subset dependencies

Name: Assertion
Definition: Any condition that the database must always satisfy.
Page: 11, 135
Chapter: 1, 4
Section: 4.1, 4
Context: Database
Example: Referential integrity
Example2: Domain constraint

Name: Authorizations (in a DDL)
Definition: Stating which user(s) can read/write some values.
Page: 11
Chapter: 1
Section: 4.1

Name: A table
Definition: Multiple columns, each with unique name.
Page: 12
Chapter: 1
Section: 5.1

Name: Entity-relationship data model
Definition: a collection of basic objects, called entities
Page: 17, 240
Chapter: 1, 7
Section: 6.3, 3.3
Kind: exemple
Definition2: relationships among these objects.
Example: instructor and departements are entities which instructor works in which departement is a relationship
Conjdef: A data model using
Abbreviation: E-R

Name: An entity
Definition: A “thing” or “object” in the real world that is distinguishable from other objects.
Page: 17
Chapter: 1
Section: 6.3

Name: A relationship
Definition: An association among several entities.
Page: 17
Chapter: 1
Section: 6.3
Variables: D an E-R data model
Example: instructor and department, to state which teach where

Name: Unified Modeling Language
Page: 17
Chapter: 1
Section: 6.3
Abbreviation: UML

Question: Why to avoid repetition in a DB
Answer: Lost of space
Page: 19
Chapter: 1
Section: 6.4
Answer2: Updating the information require to do many update

Name: File manager
Definition: Manages the allocation of space on disk storage and the data structures used to represent information stored on disk.
Page: 21
Chapter: 1
Section: 7.1
Variables: In a storage manager

Name: Buffer manager
Definition: Responsible for fetching data from disk storage into main memory, and deciding what data to cache in main memory.
Page: 21
Chapter: 1
Section: 7.1
Variables: In a storage manager

Name: Transaction manager
Definition: Ensures that the database remains in a consistent (correct) state despite system failures, and that concurrent transaction executions proceed without conflicting.
Page: 21
Chapter: 1
Section: 7.1
Variables: In a storage manager

Name: Integrity manager
Definition: The part which tests for integrity constraints.
Page: 21
Chapter: 1
Section: 7.1
Variables: In a storage manager

Name: Authorization manager
Definition: Checks the authority of users to access data.
Page: 21
Chapter: 1
Section: 7.1
Context: In a storage manager

Name: Query processor
Definition: DDL interpreter
Page: 21
Chapter: 1
Section: 7.2
Definition2: DML compiler
Conjdef: It contains
Definition3: Query evaluation engine

Name: DDL interpreter
Definition: Interprets DDL statements and records the definitions in the data dictionary.
Page: 21
Chapter: 1
Section: 7.2

Name: DML compiler
Definition: Translates DML statements in a query language into an (optimized) evaluation plan consisting of low-level instructions that the query evaluation engine understands.
Page: 21
Chapter: 1
Section: 7.2

Name: Query evaluation engine
Definition: Executes low-level instructions generated by the DML compiler.
Page: 21
Chapter: 1
Section: 7.2

Name: Atomicity
Definition: The fact that a transaction is either entirely executed, or not executed at all.
Page: 22
Chapter: 1
Section: 8

Name: Consistency
Definition: A requirement that some property of the DB is not changed after the execution of a transaction.
Page: 22
Chapter: 1
Section: 8

Name: Durability
Definition: The result of a transaction persists even if the DB has a failure.
Page: 22
Chapter: 1
Section: 8

Name: Transaction
Definition: A collection of operations that performs a single logical function in a database application.
Page: 22, 127
Chapter: 1, 4
Section: 8, 3
Definition2: Ended either by Commit work or Rollback work

Name: Concurrency-control manager
Definition: Control the interaction among the concurrent transactions, to ensure the consistency of the database.
Page: 23
Chapter: 1
Section: 8

Name: Recovery manager
Definition: A program which ensures the atomicity and durability properties.
Page: 22
Chapter: 1
Section: 8

Name: Transaction manager
Definition: The concurrency-control manager and the recovery manager.
Page: 23
Chapter: 1
Section: 8

Name: Client machine
Definition: Machine on which remote database users work.
Page: 23
Chapter: 1
Section: 9

Name: Server machine
Definition: Machine on which the database system runs.
Page: 23
Chapter: 1
Section: 9

Name: Two tier architecture
Definition: The application, on the client machine, query directly the database on the server machine.
Page: 25
Chapter: 1
Section: 9

Name: Three-tier architecture
Definition: The application client, on the client machine, query the application server, on the server machine, which queries the database.
Page: 25
Chapter: 1
Section: 9

Name: Business logic of an application
Definition: What actions to carry out under what conditions.
Page: 25
Chapter: 1
Section: 9

Name: Data mining
Definition: The process of semiautomatically analyzing large databases to find useful patterns.
Page: 25
Chapter: 1
Section: 10

Name: Database administrators
Definition: The people who have central control of both the data and the programs that access those data.
Page: 28
Chapter: 1
Section: 12.2
Abbreviation: DBA

Name: Integrity constraints
Definition: primary key
Page: 61, 128
Chapter: 3, 4
Section: 2.2, 4
Definition2: foreign key
Conjdef: A predicate, in general of the form
Definition3: not null
Intuition: ensure that changes made to the database by authorized users do not result in a loss of data consistency

Name: Storage manager
Definition: Authorization manager
Page: 20
Chapter: 1
Section: 7.1
Definition2: integrity manager,
Conjdef: The component of a database system that provides the interface between the low-level data stored in the database and the application programs and queries submitted to the system.
It is composed of
Definition3: Transaction manager,
Definition4: File manager,
Definition5: Buffer manager

Name: Commit work
Definition: makes the updates performed by the transaction become permanent in the database
Page: 127
Chapter: 4
Section: 3
Context: Database transaction
Intuition: Saving in a program

Name: Rollback work
Definition: undoes all the updates performed by the SQL statements in the transaction.
Page: 127
Chapter: 4
Section: 3
Context: Database transaction
Intuition: Quitting a program without saving

Name: structured data type
Definition: nested record structures, arrays, multiset
Page: 138
Chapter: 4
Section: 5.5

Name: distinct type
Definition: a user defined type used as a synonim for another type
Page: 139
Chapter: 4
Section: 5.5
Context: sql

Name: The hierarchy for naming relations in a modern database
Definition: catalog/database
Page: 142
Chapter: 4
Section: 5.7
Context: sql
Definition2: schemas
Conjdef: in this order
Definition3: objects (relation/views)

Name: A catalog
Definition: Top level of database system
Page: 142
Chapter: 4
Section: 5.7
Context: sql
Name2: A database
Typ: A set of schemas.

Name: role
Definition: A set of database user
Page: 144
Chapter: 4
Section: 6.2
Context: database privilege
Example: instructior, teaching, assistant, student, dean
Typ: privilege

Instruction: create role foo;
Page: 146
Effect: add a role named foo to the database
Language: sql
Context: database privilege
Chapter: 4
Section: 6.2

Name: privilege
Definition: An authorization for user/role to do things
Page: 143, 146
Chapter: 4
Section: 6, 6.2
Context: database
Example: read, insert, update, delete
Example2: role

Question: Default privilege on views
Answer: the user creating a view obtain all privilege which is already owned on the underlying relation
Page: 147
Chapter: 4
Section: 6

Name: authorization graph
Definition: roots: the privilege
Page: 148
Chapter: 4
Section: 6.5
Context: sql privilege
Definition2: nodes: the user
Conjdef: A digraph with
Definition3: edge: from a user to another one, labelled by a privilege from root to user, the basic privilege
Intuition: An user u has permission to do p if there is a path from root p to u with all edges labelled by u

Name: cascading revokation
Definition: The fact that when an user lose a privilege, some other people may also lose it
Page: 148
Chapter: 4
Section: 6.5
Context: sql privilege

Question: Why should programming language must be used and not only sql
Answer: to do non-database related action, such as printing
Page: 157
Chapter: 4
Section: 6.5
Context: sql privilege
Answer2: because sql is not turing complete

Name: Dynamic SQL
Definition: Allowing a program to create query as string, send it to database, and have the values in its variable
Page: 157
Chapter: 4
Section: 6.5
Context: sql privilege

Name: Embedded SQL
Definition: SQL statements are identified at compile time using a preprocessor. Preprocessor submits the SQL statements to the database system for precompilation and optimization; then it replaces the SQL statements in the application program with appropriate code and function calls before invoking the programming-language compiler
Page: 157
Chapter: 4
Section: 6.5
Context: sql privilege

Name: sql injection
Definition: hacking where sql code is maliciously introduced in the database
Page: 163
Chapter: 5
Section: 1.1.4
Context: sql

Name: Open Database Connectivity
Definition: an API that applications can use to open a connection with a database, send queries and updates, and get back results
Page: 166
Chapter: 5
Section: 1.2
Context: sql
Abbreviation: ODBC

Name: Conformance level
Definition: A subset of the functionality defined by the standard
Page: 166
Chapter: 5
Section: 1.2
Context: Open Database Connectivity

Name: Host language
Definition: A language in which queries are created
Page: 169
Chapter: 5
Section: 1.3
Context: SQL

Name: embedded sql
Definition: SQL query occuring directly in the code of a programming language(not as string but as standard code)
Page: 169
Chapter: 5
Section: 1.3
Context: SQL

Left: Commit
Right: Saving
Left2: Rollback
Right2: Quitting without saving
Page: 127
Chapter: 4
Section: 3
Context: Database

Question: How to possibly end a transaction
Answer: Commiting work
Page: 127
Chapter: 4
Section: 3
Answer2: Rollbacking work

Name: meta data
Definition: Information about the database that is not database content
Page: 164
Context: db
Example: schema
Example2: database version and kind

Name: sqlj
Definition: java embedding of sql
Page: 164
Context: db

Name: A table function
Definition: A function whose return type is a table
Page: 174
Context: db
Intuition: Parameterized view

Name: A measure attribute
Definition: An attribute that can be aggregated on.
Page: 199
Chapter: 5
Section: 6
Context: sql
Example: number of sale
Example2: price
Intuition: Something that can be measured, where it makes sens to sum, average, take the max
Counterexample: street number/address

Name: A dimension attribute
Definition: A description of the row content
Page: 199
Chapter: 5
Section: 6
Context: database
Example: name
Example2: color

Name: Multidimensional data
Definition: Data that can be modeled as dimension attributes and measure attributes
Page: 199
Chapter: 5
Section: 6
Context: database
Example: Sales

Name: cross-tabulation of R
Definition: A table whose headers are the attributes of R
Page: 199
Chapter: 5
Section: 6
Variables: R a relation
Context: database
Name2: pivot-table of R
Abbreviation: cross-tab of R

Name: The data cube of \(R\)
Definition: The \(d\)-dimensional cube, with coordinate \((elt_1,\dots,elt_d)\) with \(elt_i\) either a value of dimension \(D_i\) or all, and label  \(A\) groupe by \(elt_1,\dots,elt_d\)
Page: 199
Chapter: 5
Section: 6
Variables: \(R\) a relation with dimension \(D_1,\dots,D_d\)
\(A\) an aggregation function over \(R\)'s measure
Context: database

Name: A slicing of \(C\)
Definition: Restricting \(C\) over some dimensions. Either fixing some values or aggregating over all of them
Page: 201
Chapter: 5
Section: 6
Variables: \(C\) a data cube
Context: database

Name: A dicing of \(C\)
Definition: A slicing of \(C\) for mulitple dimensions
Page: 201
Chapter: 5
Section: 6
Variables: \(C\) a data cube
Context: database

Name: A drill down of \(C\)
Definition: Adding an extra dimension to \(C\)
Page: 201
Chapter: 5
Section: 6
Variables: \(C\) a data cube
Context: database

Name: A hierarchy on \(T\)
Definition: A set of equivalence relation over \(T\), ordered from trivial (\(T\)) to arbitrary coarser
Page: 201
Chapter: 5
Section: 6
Variables: \(T\) a type
Context: database
Example2: Clothes, meanswear, smoking
Applied to: time
Applied to3: Location
Applied to2: sale

Name: A rollup of \(C\)
Definition: Removing dimension of \(C\) by applying some aggregate function
Page: 201
Chapter: 5
Section: 6
Variables: \(C\) a data cube
Context: database
Applied to: time

Name: Multidimensional OLAP
Definition: OLAP that store data cubes in multidimensional arrays
Page: 204
Chapter: 5
Section: 6
Context: database
Abbreviation: MOLAP
Applied to: time

Name: Relational OLAP
Definition: OLAP that store data cubes in relational system
Page: 204
Chapter: 5
Section: 6
Context: database
Abbreviation: ROLAP

Name: Hybrid OLAP
Definition: A OLAP storing some summaries in memories and base data and other summaries in relational database
Page: 204
Chapter: 5
Section: 6
Context: database
Abbreviation: HOLAP

Instruction: table_name pivot (
  aggr(aggregated_column)
  for pivoted_column in (value_1, ..., value_n)
)
Page: 205
Language: sql
Context: database
Chapter: 5
Section: 6.3
Returns: A table whose column are
column_1, ..., column_m, value_1, ..., value_m
For each tuple
input_1, ..., input_n, ?, ? of table_name, there is a row r starting with input_1, ..., input_n
its value under value_i is the result of aggregation function over the input rows with the same input values and where pivoted_column value is value_i
Variables: table_name of schema
column_1,
  ...,
  column_n,
  aggregated_colum_n,
  pivoted_column

Name: A role
Definition: A group of users to which some privileges are associated
Chapter: 4
Section: 6.2
Context: Database