Advanced Database Systems - Pre-final Exam

Data Administrator (DA)

management-oriented role that concerns controlling the overall corporate data resources, both computerized and manual

Database Administrator (DBA)

a person who maintains a successful database environment by directing or performing all related activities to keep the data secure.

Data Administrator (DA)

to determine the contents and logical boundaries of each database

Database Administrators (DBA)

are responsible for the design, implementation, maintenance, and security of physical structures (databases).

Data Administrator (DA)

Builds logical design

Data Administrator (DA)

Has a managerial orientation

Data Administrator (DA)

Analyze and perform business data requirements

Data Administrator (DA)

Define policies and standards (definition, naming, abbreviation)

Database Administrator (DBA)

Facilitates the development and use of the database

Database Administrator (DBA)

Has a technical orientation

Database Administrator (DBA)

Analyze data volumes and space requirements in DBMS

Database Administrator (DBA)

Enforces policies and programming standards

Database security

refers to DBMS features and other related measures that comply with the organization’s security requirements.

SQL security model

provides a basic syntax used to specify security restrictions.

Users

This represents people or programs performing actions on objects in the database.

Users

grants users an ID for authentication and privileges to perform specific actions on specific tables/rows.

Objects

These are the things defined by SQL standards in the database that users can manipulate

Objects

This includes rows, columns, tables, indexes, and views.

Privileges

This refers to the rights of users to manipulate objects.

Privileges

start with SELECT, INSERT, DELETE, and UPDATE, ALTER, INDEX, AND REFERENCES for database objects.

Roles

is a named collection of database access privileges that authorize a user to connect to the database and use its system resources.

Authentication and Authorization

Individual users, groups, or processes granted access to the SQL server instance either at server level or database level

Serverlevel

includes logins and server roles.

Database-level

include users and database roles.

Login

allows you to connect to the SQL Server service (also called an instance), and permissions inside the database are granted to the database users.

GRANT

is a command used to provide access or privileges on the database objects to the users

REVOKE

is a command used to remove privileges from a specific user or role or from all users to perform actions on database objects

role

is a collection of privileges that can be granted to one or more users or other roles.

fixed-database roles

are predefined in the database

user-defined database roles

can create on your own based on your preferences.

db_owner

Members of the __ fixed database role can perform all configuration and maintenance activities on the database and can also drop the database in SQL Server.

db_securityadmin

Members of the ___ fixed database role can modify role membership for custom roles only and manage permissions. Members of this role can potentially elevate their privileges

db_accessadmin

Members of the __ fixed database role can add or remove access to the database for Windows logins, Windows groups, and SQL Server logins.

db_backupoperator

Members of the __ fixed database role can back up the database.

db_ddladmin

Members of the __ fixed database role can run any Data Definition Language (DDL) command in a database

db_datawriter

Members of the __ fixed database role can add, delete, or change data in all user tables.

db_datareader

Members of the __ fixed database role can read all data from all user tables.

db_denydatawriter

Members of the __ fixed database role cannot add, modify, or delete any data in the user tables within a database

db_denydatareader

Members of the __ fixed database role cannot read any data in the user tables within a database.

backup

is a copy of data from your database that can be used to reconstruct that data.

Backups

can be divided into physical backups and logical backups.

Physical backups

are backups of the physical files used in storing and recovering your database, such as data files, control files, and archived redo logs.

Logical backups

contain logical data (for example, tables or stored procedures) exported from a database management system (DBMS) and stored in a binary file for later re-importing into a database using the corresponding DBMS restore/import utility.

checkpoint

is a mechanism where all the previous logs are removed from the system and stored permanently in a storage disk.

Database recovery

restores a database from a given state (usually inconsistent) to a previous consistent state

Big Data

is used to label large volumes of data that push the limits of conventional software.

Volume

the quantity of data to be stored, is a key characteristic of big data.

Velocity

another key characteristic of big data.

Velocity

This refers to the rate at which new data enters the system as well as the rate at which the data must be processed.

Variety

it refers to the vast array of formats and structures in which the data may be captured

NoSQL database management systems (NoSQL DBMS

A new generation of database management systems that is not based on the traditional relational database model (SQL)

Key-value (KV) database

It is the simplest of the NoSQL data models

Key-value (KV) database

It stores data as a collection of key-value pairs.

Column-oriented database

can refer to traditional, relational database technologies that use column-centric storage instead of row-centric storage

Graph databases

are based on graph theory and represent data through nodes, edges, and properties.

Document-oriented databases

are conceptually similar to key-value databases, and they can almost be considered a subtype of KV databases

MongoDB

is a cross-platform, open-source, document-oriented database that provides high performance, high availability, automatic, and easy scalability.

Database

SQL: Database ; MongoDB: __

Collections

SQL: Tables; MongoDB: __

Fields

SQL: Columns; MongoDB: __

Documents

SQL: Rows; MongoDB: __

Collections

can be thought of as a table with a dynamic schema.

Document

is the basic unit of data for MongoDB, roughly equivalent to a row in a relational database management system