Database Design!

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

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

1:M

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.

One-to-one (1:1):

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

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

Application program

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

Business rules

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

Examples of Conceptual Data Model?

ERD, Class Diagram (OOD)

____ yields only the rows that appear in both tables.

INTERSECT

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

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

Unstructured

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

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

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

What does IT do?

coordinate firm activities

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

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

object-oriented

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