Database Design!

What are the three types of relationships in information modeling?

- Association = instance connection (1:1, 1:m, n;m) - Aggregation = part-sub part correspondence between things - Gen/Spec. = similarity among things

What is a conceptual data model?

- Shows data requirements of an organization at a high level of abstraction - Does NOT have implementation details - Includes objects, relationships, constraints

What is a logical data model?

- derived from conceptual data model with implementation included (aka Implementation Data Model) - includes more details - uses commercial DBMS software (Oracle, MySQL)

What is a physical data model?

- uses selected DBMS language to translate logical data model into physical representation within the DBMS - internal storage structure and file organizations

candidate key

1. Least likely to have its value changed 2. Least likely to lose its uniqueness 3. Contains the fewest characters 4. Easiest for the user to use

Testing a Database

1. Should be preformed in order to locate and fix errors 2. Should follow a carefully designed strategy

Database Requirement Document

1. Well defined Mission statement. 2. Operational scenarios

Conceptual Design Phase

1.identifying entities 2. identifying attributes and attribute domains for entities 3. identifying relationships 4. identifying candidate and primary keys for entities 5. Create an entity-relationship (ER) diagram.

3NF

2NF and no attributes that are not part of the primary key are transitively dependent on the primary key.

Update Anomaly

A circumstance in which redundant data in a relation may not be properly updated.

data mode

A formal expression of data, data relationships, and constraints on the data. Expresses these data relationships to a DBMS

Data Control Language (DCL)

A language used to control access to data in a database. Define privileges for users of the database

Deadlock

A situation in which two or more transactions are simultaneously waiting for another transaction to release a lock on a data item.

Entities

A uniquely identifiable element about which data is stored in a database. Can be any element about which data needs to be stored. Need not be tangible objects

Determinant

An attribute or group of attributes on which another attribute is functionally dependent.

What are the relational DB technologies?

Anomalies Normalization 1NF 2NF 3NF

Three Tier

Client: User Interface Application Server: Business Logic Data Processing Database Server: Data Validation Database Access

Two-tier

Client: User Interface + Business & Data Processing Logic Server: Server-side Validation database Access

Durability

Committed transactions are permanent and should not be lost owing to any ensuing malfunction.

What is the construct of a relationships? What are they dependent on?

Connection between things Business rules

ER Model

Contains rectangles with diamonds intermediates. Conceptual high level diagram of the structure of a database. Provides basic framework and and a user's view of data.

Integrity Controls

Controls prevent invalid data from being entered into the database. Invalid entries can lead to misleading or incorrect data.

database strategy

Creating a corporate data model is a common activity during the planning.

Backup and Repair

Creating copies of their database to ensure data access and security. Copying log files to help restore a database.

Transactions criterion

Criteria refer to: backup and recovery capabilities, logging systems, concurrency control, rollback and commit support, and support for stored procedures.

Utilities criterion

Criteria refer to: performance monitoring, use monitoring, support for database administration, and database tuning capabilities

Encryption

Database protection technique that encodes data and requires a specific key to decode it.

Operational Scenario

Describes one or more beginning to end transactions involving the database system and it's environment.

In order to have a complete and correct Information Model:

Does your model: Capture all the relevant objects and their relationships? Satisfy all the requirements? Satisfy all the requirements efficiently?

Commit

Final step in a successful completion of a database transaction in which the database attains a new consistent state.

Granting Privileges

GRANT (Options: SELECT, UPDATE, INSERT,etc) ON Employee TO emp_00004;

All Privileges

GRANT ALL PRIVILEGES ON Employee TO Emp_Users; or GRANT ALL ON Employee TO Emp_Users;

With Grant Options

GRANT SELECT ON Employee TO emp_00004 WITH GRANT OPTION;

Transitive dependency

In a functional dependency, if B is functionally dependent on A, and C is functionally dependent on B, C is then said to be transitively dependent on A.

partial functional dependency

In a functional dependency, when B is functionally dependent on A, and an attribute can be removed from A and the dependency still exists, B is said to be partially dependent on A.

2NF

It's in 1NF. All non-key attributes are dependent on all parts of the primary key.

Union-compatibility

Means that they must have the same number of attributes with matching domains

Views

Mechanism is a powerful security tool that can be used to hide many parts of the database from unauthorized users.

Authorization Controls

Mechanisms built into database requiring user IDs and passwords.

1NF

No repeating groups. All data values are atomic. Still subject to insertion, deletion and update anomalies.

System R

Project that led to the Commercial Database (IBM's DB2 and Oracle's SQL/DS) development.

Data Dictionary

Provides: 1. Each attribute's domain 2. Any default values (if present and permitted) for each attribute 3. Attribute's value, if it can be null.

Optimistic Concurrency Control

Read, Validate, Write

Data Definition Language (DDL)

SQL s subset is referred to as the schema definition language. Used to create relations, domains, views and access privileges in the database.

Data Manipulation Language (DML)

SQL subset is used to operate on the data, including retrieval, update, delete and insertion operations

Diamond

Shape used in a Chen ER model to indicate a relationship between entities

RESTRICT

Synonymous with NO ACTION.

Degree

The number of attributes (columns) in a relation

Decomposition

The process of creating new relations from existing relations based on functional dependencies within the original relation.

Denormalization

The rejoining of relations that were decomposed during normalization.

Atomicity

The transaction is a complete unit, and is executed in its entirety or not at all.

Isolation

The transaction is independent from other transactions. An incomplete transaction should not be visible to other transactions.

Consistency

The transaction must change the database from one consistent state to another consistent state.

Privileges

Type of action a user can take regarding a specific database object.

Rollback

Undoing a partially completed databases transaction, in which a database is returned to the consistent state it was before the transaction

SQL INSERT

Used to add records to a database. Ex: INSERT INTO Employee VALUES (00005, 'Jill', 'Jackson', '01-10-80', 's_rep', '1003', '28000');

What is a Relational Data Model?

Uses concept of normalization; collection of related relations

Lost updates

When simultaneous updates occur to a relation, one update may override another.

Database Prototype

Working model of both database and the application that accesses the database

Data Model

a formal expression of data, data relationships, and constraints on the data.

One of the main goals in information modeling is to....?

capture the objects and their relationships from the real world that are important to the business under study

What is an Information Model?

describes the structure of objects in a system--identity, attributes, relationships to other objects and their operations

data definition functionality criterion

functionality refers to: enforcement of primary keys, foreign key support, support for data types, domain support, data integrity mechanisms, support for views, data dictionary, data model type, and schema support

timestamp

is a distinct identifier that the DBMS assigns to a transaction.Methods prevent conflicts by rolling back any transactions involved in a conflict and restarting them.

Granting TO PUBLIC

keyword allows the DBA to grant privileges to all users in one statement. Ex: GRANT SELECT ON Employee TO PUBLIC;

Concurrent transactions only read data

no conflict exists and the order of execution is not significant.

Concurrent transactions read or write entirely different data structures

no conflict exists and the order of execution is not significant.

Insertion anomaly

occurs when certain attributes cannot be inserted into the database without the presence of other attributes.

Unrepeatable query results:

problems can occur if a database query accesses only partially updated data. AKA Dirty Read

Uncommitted updates

problems can occur when one transaction is allowed to see the intermediate results of another transaction.

Normalization

process of organizing and refining relations within a relational database usually has the effect of reducing the duplication of data items within the database at times reducing the amount of storage space needed for the base tables of the database. Addresses insertion, deletion and update anomalies. Creation of additional tables to achieve these goals.

Integrity

refers to the fact that data that is not kept secure cannot be guaranteed to be accurate. Corrupt or invalid data is of little use to an enterprise.

Availability

refers to the fact that the enterprise must have access to its data at all times. Loss of data can have far-reaching, detrimental effects on the enterprise.

Confidentiality

refers to the need of an enterprise to maintain control over sensitive data. Security breakdowns of this type can lead to loss of competitiveness, loss of revenues, or any other of many detrimental consequences.

Serializability

refers to whether a set of transactions must be executed individually, or whether they can be executed concurrently with one another.

Concurrent transactions read or write the same data structures

the order of execution is significant.

Why are the terms conceptual, logical, and physical used in data modeling?

to differentiate levels of abstraction versus detail in the model

Relationships

usually expressed using verbal expressions within the requirements document

The ____ relational type is the "relational model ideal."

1:M

One-to-one (1:1):

A strict matching, very rare. If you think one exists in your diagram, you are probably wrong.

_____ can serve as a communication tool between the users and designers.

Business rules

Examples of Conceptual Data Model?

ERD, Class Diagram (OOD)

____ yields only the rows that appear in both tables.

INTERSECT

____ data exist in the format in which they were collected.

Unstructured

What are Attributes/Record/Keys?

characteristic of an entity collection of data items that have something in common with the entity a data item(s) in a record that is used to identify a record

What does IT do?

coordinate firm activities

The ____ data model is said to be a semantic data model.

object-oriented

Network substrate hardware concerns

-Bandwith (too little) -Response -Stability -Security

Servers support

-Common systems -Common data -Anything large scale -Give info to clients when requested

3 IT Conflicts

1. Priority 2. Strategy Conflict 3. Technical Goals

What are the steps to Database Design? What are the outputs of each?

1. Requirements Analysis = DB Requirement Specification 2. Conceptual Database Design = Conceptual Data Model 3. Logical Database Design = Logical Data Model 4. Physical Database Design = Physical Data Model

What are the steps in the normalization process?

1. remove repeating groups (1NF) 2. make all attributes functionally dependent on the primary key (2NF) 3. all non-key attributes are fully functionally dependent on the primary key AND there are no transitive (non-key) dependencies

The hierarchical data model was developed in the ____.

1960s

The relational data model was developed in the ____.

1970s

The object-oriented data model was developed in the ____.

1980s

The ____ relationship should be rare in any relational database design.

1:1

Data standard

A common method of defining, structuring, and describing data. Avoids confusion from different names for the same thing and different things with the same name. Compatible data standards provide semantic interoperability.

First normal form (1 NF):

A database is in 1NF if each cell contains a single atomic value.

Third Normal Form (3NF):

A database is in 3NF if each field in a table is either: -Part of the primary key. -Fully functionally dependent on the primary key.

Second Normal Form (2NF):

A database is in second normal form if we can identify a field (or set of fields) that all remaining fields are functionally dependent upon.

Primary Key

A field, or set offends, in a table such that all other fields are functionally dependent

Communication Protocol

A set of rules that governs the communications between computers on a network. Problems come when we need to span networks. Compatible communication protocols provide syntactic interoperability.

A(n) ____ might be written by a programmer or it might be created through a DBMS utility program.

Application program

Hardware and software

Configuration and maintenance is heavily impacted by corporate governance and value chain configuration.

What is the Physical Data Model Dependent on?

DB Platform

Attribute:

Important information describing a given object or, on occasion, a relationship (oval)

Relationship:

Interactions among entities, typically describe an activity that is taking place. (diamond)

The body of information and facts about a specific subject.

Knowledge

Codd's Rule of ____ states: Application programs and ad hoc facilities are logically unaffected when changes are made to the table structures that preserve the original table values (changing order of columns or inserting columns).

Logical Data Independence

What are the 4 types of files?

Master = records for a group of entities Table = data used to calculate more data Transaction = used to enter changes Report = print reports of the data

Codd's Rule of ____ states: If the system supports low-level access to the data, users must not be allowed to bypass the integrity rules of the database

Nonsubersion

Entity:

Objects of interest, could be people, places, or things (like a transaction) (rectangle)

____ is a set of tools that work together to provide an advanced data analysis environment for retrieving, processing, and modeling data from the data warehouse.

Online analytical processing

____ yields a vertical subset of a table.

PROJECT

The response of the DBMS to a query is the ____.

Query result set

End-user data

Raw facts of interest to the end-user

Examples of Logical Data Model?

Relational Data Model, Network Data Model

To be considered minimally relational, the DBMS must support the key relational operators ____, PROJECT, and JOIN.

SELECT

____, also known as RESTRICT, yields values for all rows found in a table that satisfy a given condition.

SELECT

What is a ER Diagram?

Shows relationship between entities

Enterprise system

Support and integrate functional units

What is the Logical Data Model Dependent on?

The DB Tech. that will be used

One-to-Many (1:M):

Very common, describes a setting in which an instance of one entity can be associated with multiple instances of another entity.

Many-to-Many (M:M):

Very common, describes a setting in which multiple instances of a given entity can be associated with multiple instances of another entity.

What is the main problem in Database Design?

What are the domain needs?

Oracle 11g is an example of a(n) ____.

XML/Hybrid data model

What are Entities/Relationships

any object or event which someone chooses to collect data on associations between entities

A(n) ____ is the equivalent of a field in a file system.

attribute

A ____ key can be described as a superkey without unnecessary attributes, that is, a minimal superkey.

candidate

What is a database?

central source of data meant to be shared by many users for a variety of applications

A(n) ____ model represents a global view of the database as viewed by the entire organization.

conceptual

A(n) ____ is a restriction placed on the data.

constraint

____ are important because they help to ensure data integrity.

constraints

____ are normally expressed in the form of rules.

constraints

A(n) ____ enables a database administrator to define schema components.

data definition language (DDL)

A ____ contains at least all of the attribute names and characteristics for each table in the system.

data dictionary

The phrase ____ refers to an organization of components that define and regulate the collection, storage, management and use of data within a database environment.

database system

In the context of a database table, the statement "A ____ B" indicates that if you know the value of attribute A, you can look up the value of attribute B.

determines

A CUSTOMER table's primary key is CUS_CODE. The CUSTOMER primary key column has no null entries, and all entries are unique. This is an example of ____ integrity.

entity

A noun in a business rule translates to a(n) ____ in the data model.

entity

A(n) ____ is anything about which data are to be collected and stored.

entity

A(n) ____ represents a particular type of object in the real world.

entity

The ____ model uses the term connectivity to label the relationship types.

entity relationship

The ____ model is the end users' view of the data environment.

external

A ____ is a character or group of characters that has a specific meaning.

field

A ____ is a collection of related records.

file

VMS/VSAM is an example of a(n) ____.

file system data model

The attribute B is ____ the attribute A if each value in column A determines one and only one value in column B.

functionally dependent on

What is a DBMS?

heart of the database that allows the creation, modification, and updating of the data

In the ____ model, each parent can have many children, but each child has only one parent.

hierarchical

In the ____ model, the basic logical structure is represented as an upside-down tree.

hierarchical

One of the limitations of the ____ model is that there is a lack of standards.

hierarchical

In a database context, the word ____ indicates the use of the same attribute name to label different attributes.

homonym

Functional dependency

if field A assumes a value of X: Field B must assume a value of Y

A(n) ____ is an ordered arrangement of keys and pointers.

index

What is metadata?

information that describes data

A(n) ____ join only returns matched records from the tables that are being joined.

inner

The organization of the data within the folders in a manual file system was determined by ____.

its expected use

In the relational model, ____ are important because they are used to ensure that each row in a table is uniquely identifiable.

keys

The relational database model enables you to view data ____ rather than ____.

logically, physically

Students and classes have a ____ relationship.

many-to-many

A(n) ____ join links tables by selecting only the rows with common values in their common attribute(s).

natural

In the ____ model, the user perceives the database as a collection of records in 1:M relationships, where each record can have more than one parent.

network

In an outer join, the matched pairs would be retained and any unmatched values in the other table would be left ____.

null

Most decision-support data are based on historical data obtained from ____.

operational databases

____ relates to the activities that make the database perform more efficiently in terms of storage and access speed.

performance tuning

Controlled ____ makes a relational database work.

redundancy

MySQL is an example of a(n) ____.

relational data model

A verb associating two nouns in a business rule translates to a(n) ____ in the data model.

relationship

A(n) ____ is bidirectional.

relationship

A ____ key is defined as a key that is used strictly for data retrieval purposes.

secondary

Most data you encounter is best classified as ____.

semistructured

XML data is ___.

semistructured

In a database context, a(n) ____ indicates the use of different names to describe the same attribute.

synonym

The ____ is actually a system-created database whose tables store the user/designer-created database characteristics and contents.

system catalog

The relational model's creator, E. F. Codd, used the term relation as a synonym for ____.

table

When you define a table's primary key, the DBMS automatically creates a(n) ____ index on the primary key column(s) you declared.

unique