DCIT 55 - Reviewer

Database Administrator

Responsible for physical database design and for dealing with technical issues such as security enforcement, database performance, backup and recovery.

Technical Responsibility of DBA:

Selection of hardware and software

Installing/upgrading DBMS

Tuning database performance

Improving query processing performance

Managing data security, privacy, and integrity

Data backup and recovery

Database Performance Tuning

set of activities and procedures designed to ensure that end-user query is processed by DBMS in minimum amount of time

SQL Performance Tuning

activities on client side designed to generate SQL code that returns correct answer in least amount of time, using minimum number of resources at server end

Views

an application programs or individual user’s picture of the database, a derived table because data in it is retrieved from the base table, a table that is not really a table

Updatable Views

is when a view is derived by joining two tables on primary key of each table

Dropping a View

remove a view that is no longer needed with DROP VIEW command

Security

the prevention of unauthorized access to a database

Grant Command

main mechanism for providing access to a database

Revoke Command

remove privileges from users

Integrity Rules

a rule for the data in the database

Integrity Support

the process of specifying integrity constraints for the database

Primary Keys

unique identifier on a table.

Foreign Key

a column in one table whose value matches the primary key in another.

Data Dictionary Management

defines data elements and their relationships

Data Storage Management

stores data and related data entry forms, report definitions, etc.

Data Transformation and Presentation

translates logical requests into commands to physically locate and retrieve the requested data

Security Management

enforces user security and data privacy within database

Multi-user Access Control

creates structures that allow multiple users to access the data

Backup and Recovery Management

provides backup and data recovery utilities

Data Integrity Management

Promotes and enforces integrity rules to eliminate data integrity problems

Database Language and Application Programming Interfaces

provides data access through a query language

Database Communication Interfaces

allows database to accept end-user requests within a computer network environment

Importance of Good Design:

• Eliminates data redundancy

• Eliminates errors leading to bad decisions

Practical Approach:

Focus on principles and concepts of database design

Importance of logical design

Data Administrator

broader data management activity, more managerially oriente

Enterprise Data Model

a visual representation, or graph, of an enterprise business' data

Why do you need EDM?:

Improve Data Quality

Defining All Data

Managing Data

Data Governance

Enterprise Subject Area Model

could include Stores, Staff, Warehouses, Products, Payments, and Customers, to name a few

Enterprise Conceptual Model

could be broken down by ID and type, or Payments could be broken down by credit cards, debit cards, and cash

Enterprise Entity Model

identify the main areas of interest for each conceptual model

EDM Tools:

LucidChart

Erwin Data Model er

Diagrams.net

ER/Studio

Ab Init io

Security

refers to activities and measures to ensure the confidentiality, integrity, and availability of an information system and its main asset, dat

Confidentiality

deals with ensuring that data is protected against unauthorized access, and if the data are accessed by an authorized user, that the data are used only for an authorized purpose.

Integrity

is concerned with keeping data consistent, free of errors, or anomalies. Integrity focuses on maintaining the data free of inconsistencies and anomalies.

Availability

refers to the accessibility of data whenever required by authorized users and for authorized purposes.

Security Policy

a collection of standards, policies, and procedures created to guarantee the security of a system and ensure auditing and compliance.

Security Vulnerability

a weakness in a system component that could be exploited

Security Threat

an imminent security violation that could occur at any time due to unchecked security vulnerability.

Security Breach

occurs when a security threat is exploited to negatively affect the integrity, confidentiality, or availability of the system.

Preserved

action is required to avoid the repetition of similar security problems, but data recovery may not be necessary.

Corrupted

include database access by computer viruses and by hackers whose actions are intended to destroy or alter data.

Database Security

refers to the use of the DBMS features and other related measures to comply with the security requirements of the organization.

Authorization Management

defines procedures to protect and guarantee database security and integrity.