Index

Citation

Zygiaris, S. (2018), "Index", Database Management Systems, Emerald Publishing Limited, Leeds, pp. 291-299. https://doi.org/10.1108/978-1-78756-695-820181017

Publisher

:

Emerald Publishing Limited

Copyright © 2018 Emerald Publishing Limited

---

INDEX

Access rights

, 206

Ad-hoc queries

, 242

Aggregate functions

, 110–118

AVG

, 115–116

COUNT

, 112–114

MAX

, 117–118

MIN

, 116–117

SUM

, 114–115

ALL PRIVILEGES

, 210

ALL subqueries

, 166

ALTER TABLE

, 84

Analytical data processing

, 15

ANY subqueries

, 166

Applications

, 6

Arithmetic attributes in queries, managing

, 182–183

Arithmetic operations, query formation using

, 105–107

Artificial intelligence

, 243

Ascending order

, 109

Associative table

, 33

Atomicity

, 275

Attributes

, 7, 25, 28, 93–95

Audit trails

, 209

weak

, 206

AVG aggregate function

, 115–116

Backup

, 196, 204–205

full

, 204

incremental

, 204

Base

, 7

Big data, data mining in

, 250–252

Binary search

, 202

Built-in functions

, 188

Business intelligence (BI)

, 15

data and predictive analytics

, 252–253

data mining in big data

, 250–252

data warehouse

, 244–245

framework for

, 242–244

OLAP extensions

, 247–250

OLAP operations

, 245–247

OLAP services

, 244–245

Business processes

, 253

Business requirements

, 22–23

Business rules

, 23

Business world, databases in

, 241–263

business intelligence

, 242–253

enterprise-wide reporting

, 253–259

Calculated attributes

, 26

Candidate key

, 30

Cardinality

, 26, 27

CASCADE

, 78

Centralized database

, 15, 265–266

CHECK constraint

, 52

Cloud, defined

, 258

Cloud Enterprise Computing

, 258–259

COBOL (Common Business Oriented Language)

, 7

Codd, E. F.

, 28

Columns

, 7

COMMIT

, 274

Composite primary key

, 33

CONCAT()

, 184, 185

Conceptual design

, 22, 24–28

entities, designing

, 25–26

relationships, designing

, 26–28

Concurrency control

with lost updates

, 277–278

with scheduler

, 279–280

transaction management in SQL

, 274–276

two-phase commit protocol in distributed databases

, 280–282

with uncommitted data

, 278–279

Conditional operators

, 73–74

query formation using

, 98–103

Consecutive views

, 176

Cookies

, 230

COUNT aggregate function

, 112–114

CREATE FUNCTION

, 188

Cross join, joining tables with

, 122–124

CRUD (Create, Read, Update, and Delete) transactions

, 4, 13, 17

CUBE

, 244

Customer Relationship Management (CRM)

, 13

Cybercriminals, security attacks from

, 206–207

Data

, 3

Data analytics

, 252–253

Database administration

, 195–205

backup

, 204–205

indexes

, 201–203

metadata administration

, 197–199

optimization of database

, 199–200

partitioning of database

, 200–201

recovery

, 204–205

Database Administrator (DBA)

certification

, 196

responsibilities of

, 195–196

Database analysis

, 21–22, 23–24

Database applications

defined

, 219–221

GET operation

, 222

multiuser applications

, 223

POST operation

, 221–222

single user applications

, 223

structure of

, 221

web. See Web database applications, building

Database creation

, 49–50

Database design

conceptual design

, 24–28

development process

, 21–24

logical design using relational model

, 28–36

normalization

, 36–41

Database development process

, 21–24

Database environment

, 11–14

database management

, 12–14

Database life cycle

, 21

Database management

, 12–14, 22

Database Management System (DBMS)

defined

, 14

types of

, 14–17

Database optimization

, 196, 199–200

Database partitioning

, 200–201

Database privileges

, 205–206

Database rootkits

, 207

Databases

approach

, 9–11

file systems

, 7–9

structure

, 7

Database security

, 195

defined

, 208

threats. See Database security threats

Database security threats

, 205

excessive privileges to users

, 206

legitimate database privileges, abuse of

, 205–206

mitigation of. See Mitigation of database security threats

security attacks from cybercriminals

, 206–207

storage media exposure

, 206

weak audit trails

, 206

Database table creation

, 50–67

in MariaDB

, 53–59

in MS-Access

, 64–67

in ORACLE

, 59–64

Data constraints

, 51–53

Data dependency

, 8, 14

Data dictionary

in MariaDB

, 198

in MS-Access

, 198–199

in ORACLE APEX4.2

, 197

Data extraction

, 95–96

Data Integration

, 253–254

Data integrity

, 9

enforcement of

, 11

Data marts

, 242

Data mining

, 15, 242

in big data

, 250–252

Data processing

, 4–6

Data Processor (DP)

, 272

Data redundancy

, 8–9, 11

Data selection

, 95–96

Data sorting, in SQL queries

, 108–110

Data source stage

, 242

Data storage, dispersion of

, 15

Data transfer

, 4

Data updating through views

, 177–178

Data warehouse

, 15, 242, 244–245

Date data types

MariaDB

, 58

ORACLE

, 64

Date managing functions, in queries

, 186–188

Date searching, query formation using

, 105

DAY()

, 188

DEFAULT constraint

, 52

DELETE command, revised with subqueries

, 168–169

DELETE FROM

, 72

Deleting data

, 72–75

Denial of Service (DoS)

, 207, 208

Denormalization

, 41

Descending order

, 109

DICE

, 246, 247

Digital data

, 3

Disaster Recovery Plan (DRP)

, 204

DISTINCT clause

grouping data using

, 150

query formation using

, 107–108

Distributed database

, 265–266

client’s distribution transparency

, 270–274

design

, 267–270

fragmentation

, 268–270

two-phase commit protocol in

, 280–282

Distributed databases

, 15

Distributed DBMS (DDBMS)

, 266

Distributed request

, 273

Distributed transaction

, 273

Distribution transparency

, 270–274

DO-UNDO-REDO

, 282

DRILL-DOWN

, 245–246

DROP TABLE

, 75

DUAL()

, 186

ELT tool

, 260

Enterprise DBMS

, 14–15

Enterprise Resource Planning (ERP)

, 13, 254–257

databases in

, 257–258

Enterprise-wide information into business knowledge, transforming

, 241

Enterprise-wide reporting

, 253–259

databases in ERP

, 257–258

Enterprise Resource Planning

, 254–257

integration in business information systems, need for

, 253–254

Entity(ies)

, 25

designing

, 25–26

M:M relationship

, 26–228

1:M relationship

, 26–28

1:1 relationship

, 26–28

into tables, covering

, 28–30

Entity–Relationship (E–R) diagrams

, 22, 25, 51

Epicor ERP

, 258, 259

Integrating Business Processes

, 256

Excessive privileges to users

, 206

Fields

, 7, 28

repeating

, 9

File systems

, 7–9

First in, first out (FIFO)

, 275

First normal form (1NF), database conversion to

, 36–38

FLOOR()

, 183

Foreign key

, 33

constraint

, 53

Fragmentation

, 267–268

horizontal

, 268

transparency

, 271–272

vertical

, 268

Fragmented piecemeal

, 12, 253, 254

FROM subqueries

, 167–168

Front-end stage

, 242

Full backup

, 204

FULL JOIN

, 128

Functional dependency

, 36–38

Functions of SQL

, 181–189

GET operation

, 222

Global economy, business challenges in

, 241–242

Granting privileges

, 209–214

GROUP BY clause, grouping data using

, 146–149

GROUP BY CUBE clause

, 249–250

GROUP BY ROLLUP clause

, 248

GROUP BY SQL clause

, 247

GROUPING clause

, 250

Grouping data

, 145–156

with calculated attributes

, 152–153

DISTINCT clause

, 150

GROUP BY clause

, 146–149

HAVING clause

, 153–156

with limitations using WHERE clause

, 150–152

rationale of

, 145–146

HANA SAP Eclipse platform

, 259

Hardware

, 7

HAVING clause, grouping data using

, 153–156

HAVING subqueries

, 162–163

HOLAP

, 245

Holistic participation

, 26

Horizontal fragmentation

, 268

HTML

, 230

HTTP protocol

, 230

Incremental backup

, 204

Indexes

, 201–203

Infor

, 258, 259

Information

, 6

Information systems

, 6

Infrastructure as a Service (IaaS)

, 259

Inner join, joining tables with

, 124–127

common error in

, 125–126

Inner query

, 158

IN operator

, 98

INSERT command, revised with subqueries

, 168–169

Inserting data into database

, 67–70

common mistakes in

, 70

INSERT INTO

, 67

IN subqueries

, 163–165

INTERSECT set operator

, 138–139

Isolation

, 275

Joining tables

, 121–126

with cross join

, 122–124

with inner join

, 124–126

rationale of

, 121

JOIN operator

, 126–137

inner join

, 126–127

necessary conditions

, 129–135

outer join

, 127–128

recursive JOIN

, 135–137

using alias in

, 128–129

Knowledge creation

, 6

LEFT JOIN

, 127

Legacy systems

, 7

Legitimate database privileges, abuse of

, 205–206

LENGTH()

, 185–186

LIKE

, 104

Linear search

, 202

Local area network (LAN)

, 265

Local-mapping transparency

, 271

Location transparency

, 271

Locking

, 279–280, 281

Logical design

, 22, 24

using relational model

, 28–36

Logical operators

, 73–74

query formation using

, 98–103

Lost updates, concurrency control with

, 277–278

LOWER()

, 185

MariaDB

creating tables in

, 53–59

database backup

, 204

data dictionary in

, 198

data insertion

, 71

date data types

, 58

numeric data types

, 56

time data types

, 58

web database applications

, 230–235

MAX aggregate function

, 117–118

Metadata

, 244

administration

, 195, 197–199

Microsoft Azure

, 259

Microsoft Certified Database Administrator (MCDBA)

, 196

Microsoft Dynamics

, 258, 259

MIN aggregate function

, 116–117

MINUS set operator

, 139–140

Mitigation of database security threats

database audits

, 209

monitoring and blocking

, 208–209

scan for vulnerability

, 208

sensitive data identification

, 208

user access rights management

, 208

user tracking

, 208

M:M entity relationship

, 26–28

implementation of

, 33–36

MOLAP

, 245

MONTH()

, 187

MS-Access

creating tables in

, 64–67

database backup

, 205

data dictionary in

, 198–199

data insertion

, 72

data types

, 66

web database applications

, 228–230

Multi-user applications

, 223

Multi-user DBMS

, 14

Multivalued attributes

, 26

Niche markets, identification of

, 241–242

Nondistributed transaction

, 272

Normalization

, 36–41

NOT IN operator

, 98

NOT NULL constraint

, 52

Numeric data types

MariaDB

, 56

ORACLE

, 60–61

ON DELETE

, 52, 76–84

1:M entity relationship

, 26–28

implementation of

, 30–33

1:1 entity relationship

, 26–25

Online Analytical Processing (OLAP)

, 15, 242

extensions

, 247–250

operations

, 245–247

servers, types of

, 245

services

, 244–245

ON UPDATE

, 52, 76–84

Open Vista

, 258, 259

Operational DBMS

, 15

Optimal decision-making support environment, creation of

, 241

ORACLE

, 258

creating tables in

, 59–64

data insertion

, 71

Data Mining Tool

, 251

date data types

, 64

ERP’s Enterprise-wide Reporting

, 257

numeric data types

, 60–61

text data types

, 62–63

time data types

, 64

ORACLE APEX4.2

data dictionary in

, 197

web database applications

, 223–228

Oracle Certified Professional (OCP)

, 196

Oracle Cloud solution

, 259

Oracle University

, 196

ORDER BY

, 109

Orphan child

, 78

Outer join, joining tables with

, 127–128

Outer query

, 158

Partial dependency

, 38–39

Partial participation

, 26

PCI

, 206

PHP

, 230

with SQL engine, connecting

, 233

PIVOT

, 246, 247

Platform as a Service (PaaS)

, 259

Porter’s five forces competitive model

, 241

POST operation

, 221–222

Predictive analytics

, 242–243, 252–253

Premature commit effect

, 281

Primary key

, 10, 25, 28

composite

, 33

constraint

, 53

surrogate

, 25

Privilege abuse

, 205–206

Procedural SQL (PL/SQL)

, 179

Query execution

, 96–97

Query formation, in SQL

, 98–110

using arithmetic operations

, 105–107

using conditional and logical operators

, 98–103

data sorting

, 108–110

using date searching

, 105

using DISTICT clause

, 107–108

using text searching

, 103–104

RANDOM()

, 183–184

Random values in queries, selection of

, 183–184

Rational joined table

, 124

Records

, 7

Recovery

, 196, 204–205

Recovery Manager (RMAN)

, 204

Recursive JOIN

, 135–137

Referential integrity constraints, deleting and updating

, 76–84

Relational model, logical design using

, 28–36

Relational set operators

, 137–140

INTERSECT

, 138–139

MINUS

, 139–140

UNION

, 137–138

Relationship

, 25

designing

, 26–28

Replication

, 267–268

RESTRICT

, 78, 83

REVOKE

, 211

RIGHT JOIN

, 127

ROLAP

, 245

ROLLBACK

, 274

ROLL-UP

, 245

ROUND()

, 182–183

Rows

, 7, 28

selection

, 93–95

Salesforce

, 259

SAP

, 259

ERP framework

, 257, 258

Scan for vulnerability

, 208

Scheduler, concurrency control with

, 279–280

Script

, 230

Second normal form (2NF), database conversion to

, 38–39

SELECT

, 93

subqueries

, 167

Sensitive data identification

, 208

Serialization

, 275, 280

SET DEFAULT

, 78

SET NULL

, 78

Single-user applications

, 223

Single-user DBMS

, 14

Single-valued attributes

, 26

SLICE

, 246

Software as a Service (SaaS)

, 258–259

SOX

, 206

Spanner

, 15

SQL injection

, 206–207

SQL ROLLUP clause

, 247–250

SQRT()

, 182

Stack

, 231

Storage media exposure

, 206

Stored attributes

, 26

Structured Query Language (SQL)

, 49–92

aggregate functions

, 110–118

attribute and row selection

, 93–95

basics

, 49

constraints

, 51–53

database creation

, 49–50

database table creation

, 50–67

data extraction

, 95–96

data selection

, 95–96

data types

, 51

deleting and updating referential integrity constraints

, 76–84

deleting data

, 72–75

functions

, 181–189

grouping data

, 145–156

inserting data into database

, 67–70

JOIN operator

, 126–137

joining tables

, 121–126

query basics

, 93

query execution

, 96–97

query formation

, 98–110

relational set operators

, 137–140

subqueries

, 156–169

transaction management in

, 274–276

triggers

, 178–181

updating data

, 75–76

updating table structure

, 84–85

views

, 173–178

Subqueries

, 156–169

ALL

, 166

ANY

, 166

DELETE

, 168–169

FROM

, 167–168

HAVING

, 162–163

IN

, 163–165

INSERT

, 168–169

rationale of

, 156–159

SELECT

, 167

UPDATE

, 168–169

WHERE

, 159–162

SUBSTR()

, 184–185

SUM aggregate function

, 114–115

Supply Chain Management

, 13

Surrogate primary key

, 25

SYSDATE()

, 186–187

Table

, 9

alias

, 128–129

associative

, 33

creation. See Table creation

data integrity, enforcement of

, 11

data redundancy, elimination of

, 11

entities into, covering

, 28–30

related

, 11

repeating field entries

, 11

single business entity

, 9–10

unique rows

, 10–11

Table creation

, 50–67

in MariaDB

, 53–59

in MS-Access

, 64–67

in ORACLE

, 59–64

Tablespaces

, 201

Text attributes, concatenating and managing

, 184–186

Text data types

MariaDB

, 56

ORACLE

, 62–63

Text searching, query formation using

, 103–104

Third normal form (3NF), database conversion to

, 40–41

Time data types

MariaDB

, 58

ORACLE

, 64

Transaction

, 271

CRUD

, 4, 13, 17

log. See Transaction log

management in SQL

, 274–276

transparency

, 271–272

Transaction log

, 274–276

atomicity

, 275

backward state

, 275

consistency

, 275

forward state

, 275

isolation

, 275

serialization

, 275, 280

Transaction Processor (TP)

, 272

Transitive dependency

, 40–41

Transparency

distribution

, 270–274

fragmentation

, 271–272

local-mapping

, 271

location

, 271

Triggers

, 178–181

creation of

, 179–181

rationale of

, 178–179

Two-phase commit protocol, in distributed databases

, 280–282

Uncommitted data, concurrency control with

, 278–279

UNION ALL

, 138

UNION set operator

, 137–138

UNIQUE constraint

, 52

Universal User Tracking (UUT)

, 208

Updatable views

, 178

UPDATE command, revised with subqueries

, 168–169

Updating data

, 75–76

Updating table structure

, 84–85

UPPER()

, 185

User access rights management

, 208

User-defined functions, creating

, 188–189

Users, database privileges for

, 209–214

VALUES clause

, 68

Vertical fragmentation

, 268

Views

, 173–178

consecutive

, 176

data updating through

, 177–178

updatable

, 178

virtual tables as, creating

, 173–177

Virtual tables as views, creating

, 173–177

Visualization

, 244

Weak audit trails

, 206

Web Clients

, 230

Web database applications, building

, 219–239

in MariaDB

, 230–235

in MS-Access

, 228–230

in ORACLE APEX 4.2

, 223–228

Web forms

, 234

Web reports

, 219

Web Servers

, 230

WHERE

, 105

clause, grouping with limitations using

, 150–152

subqueries

, 159–162

Workgroup DBMS

, 14

XML

, 15

XML DBMS

, 15

YEAR()

, 187