UTA INSY 3304 - Exam 2 - Chapter 7&9

Data definition language

DDL

Data manipulation language

DML

CREAT SCHEMA AUTHORIZATION

Creates a database schema

CREATE TABLE

Creates a new table in the user's databse schema

NOT NULL

Ensures that a column will not have null values

UNIQUE

Ensures that a column will not have duplicate values

PRIMARY KEY

Defines a primary key for a table

FOREIGN KEY

Defines a foreign key for a table

DEFAULT

Defines a default value for a column ( when no value is give)

CHECK

validates data in an attribute

CREATE INDEX

Creates an index for a table

CREATE VIEW

Creates a dynamic subset of rows and column from on or more tables

ALTER TABLE

Modifies a table's definition (adds, modifies, or deletes attributes or constraints)

CREATE TABLE AS

Creates a new table based on a query in the user's database schema

DROP INDEX

Permanently deletes an index

DROP VIEW

Permanently deletes a view

Authentications

the process DBMS uses to verify that only registered users access the database

Numeric

Number( L, D) or Numeric(L, D)

Character

Char (L)

Date (DATE)

Stores dates in the Julian format

iNSERT: Command to insert data into table

INSERT INTO tablename VALUES();

COMMIT Syntax

COMMIT [WORK];

SELECT Syntax

Command to list the content

SELECT columnlist FROM tablename;

UPDATE

Command to modify data

Syntax - UPDATE tablename SET columnname = expression [, columnname = expression] [WHERE conditionlist];

ROLLBACK

Syntax -ROLLBACK;

Undoes the changes since last COMMIT

command

DELETE

Command to delete

Syntax - DELETE FROM tablename

Subquery

Query embedded/nested inside another query

=

Equal to

<

Less than

<=

less than or equal to

>

greater than

>=

Greater than or equal to

<> or !=

Not equal to

Alias

Alternate name given to a column or table in any SQL statement to improve the readability

The Rule of Precedence

Establish the order in which computations are completed

Performed in this order:

1. Operations within parentheses

2. Power operations

3.Multiplications and divisions

4.Additions and subtractions

OR and AND

Used to link multiple conditional expressions in a WHERE or HAVING clause

OR

requires only one of the conditional expressions to be true

AND

requires all of the conditional expressions to be true

NOT

is used to negate the result of a conditional expression

Boolean algebra

is dedicated to the use to logical operations

Between

Checks whether attribute value is within a range

IS NULL

Checks whether attribute value is null

LIKE

Checks whether attribute value matches given string pattern

IN

Checks whether attribute value matches any value within a value list

EXISTS

Checks if subquery returns any rows

ALTER TABLE command

To make changes in the table structure

Use _______________ command to ADD primary and foreign keys

ALTER TABLE

DROP TABLE

Deletes table from database

Syntax - DROP TABLE tablename;

ORDER BY

clause is useful when listing order is important

Syntax - SELECT columnlist

FROM tablelist

[WHERE conditionlist]

[ORDER BY columnlist [ASC | DESC]];

Cascading order sequence

Multilevel ordered sequence

DISTINCT clause

Produces list of values that are unique

Syntax- SELECT DISTINCT columnlistFROM tablelist;

Recursive query

Table is joined to itself using alias

Systems analysis

Process that establishes need for and extent of information system

Systems development

Process of creating information system

Database development

Process of database design and its implementation

Purpose of Database Initial Study

Analyze company situation

Define problems and constraints

Define objectives

Define scope and boundaries

Database Design

Supports company's operations and objectives

Most critical phase

Ensures final product meets user and system requirements

Virtualization

Creates logical representations of computing resources independent of underlying physical computing resources

Create the databases

Requires the creation of special storage-related constructs to house the end-user tables

Load or convert the data

Requires aggregating data from multiple sources

Full backup/dump

All database objects are backed up in their entirety

Differential backup

Only modified/updated objects since last full backup are backed up

Transaction log backup

Only the transaction log operations that are not reflected in a previous backup are backed up

Software

Software-induced failures may be traceable to the operating system, the DBMS software, application program, or viruses and other malware

Hardware

Hardware-induced failures may include memory chip errors, disk crashes, bad disk sectors, and disk-full errors.

Programming exemptions

Application program or end users may roll back transactions when certain conditions are defined. Programming exemptions can also be caused by malicious or improperly tested code that can be exploited by hackers.

Transactions

The system detects deadlocks and aborts one of the transactions

External factors

Backups are especially important when a system suffers completely destruction from fire, earthquake, flood, or other natural disaster

Minimum data rule

All that is needed is there, and all that is there is needed

Conceptual Design

Designs a database independent of database software and physical details

Conceptual data model

Describes main data entities, attributes, relationships, and constrains

Conceptual Design Steps

1. Data analysis and requirements

2. Entity relationship modeling and normalization

3. Data model verification

4. Distributed database design

Data Analysis and Requirements

"Discover data element characteristics

Obtains characteristics from different sources

Requires thorough understanding of the company's data types and their extent and uses

Take into account business rules

Derived from description of operations

"

Description of Operations

Provides precise, up-to-date, and reviewed description of activities defining an organization's operating environment

Developing the conceptual model using ER diagrams

1. Identify, analyze, and refine the business rules.

2. Identify the main entities, using the results of Step 1.

3. Define the relationships among the entities, using the result of Step 1 and 2.

4. Define the attributes, primary keys, and foreign keys for each of the entities.

5. Normalize the entities.

6. Complete the initial ER diagram.

7. Validate the ER model against the end users' information and processing requirements.

8. Modify the ER model, using the results of Step 7.

Module

Information system component that handles specific business function

Data Model Verification

Verified against proposed system processes Revision of original design Careful reevaluation of entities Detailed examination of attributes describing entities

The ER Model Verification process

1. Identify the ER model's central entity

2. Identify each module and its components.

3. Identify each modules transaction requirements

4. Verify all processes against system requirements.

5. Make all necessary changes suggested in Step 4.

6. Repeat steps 2-5 for all modules

Cohesivity

Strength of the relationships among the module's entities

Module coupling

Extent to which modules are independent to one another

Database fragment

Subset of a database stored at a given location

Distributed Database Design

Portions of database may reside in different physical locations, Ensures database integrity, security, and performance

DBMS Software Selection

Cost

DBMS features and tools

Underlying model

Portability

DBMS hardware requirements

Logical design

Designs an enterprise-wide database that is based on a specific data model but independent of physical-level details

Physical design

Process of data storage organization and data access characteristics of the database

Logical Design Steps

1. Map the conceptual model to logical model components.

2. Validate the logical model using normalization.

3. Validate the logical model integrity constraints.

4. Validate the logical model against user requirements.

Mapping the conceptual model to the relational model

1. Map strong entities.

2. Map supertype/subtype relationships

3. Map weak entities.

4. Map binary relationships.

5. Map higher-degree relationships.

Physical Design Steps

1. Define data storage organization

2. Define integrity and security measures

3. Determine performance measurements

Clustered Tables

Technique that stores related rows from two related tables in adjacent data blocks on disk

Database Role

Set of database privileges that could be assigned as a unit to a user or group