Database design- level 1

What is the Physical Data Model Dependent on?

DB Platform

Data Manipulation Language (DML)

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

What is concatenation?

When the value for a column is functionally dependent on a combination of at least two other fields/attributes/columns

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

3NF means what?

2NF and all determinants are also candidate keys.

Update Anomaly

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

What is a candidate key?

A column (field/attribute) or collection of these that could function as the primary key

What is included in a Conceptual Data Model?

A conceptual data model includes the entities (that describe the data) and the relationships between those entities. No other information is shown through the conceptual data model.

What is a primary Key?

A field, or set of fields, in a table such that all other fields in that table are functionally dependent on that field (or fields)

Data Control Language (DCL)

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

When is a relation in First normal form (1 NF)?

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

When is a relation in Third Normal Form (3NF)?

A relation is in 3NF if each field in a table is part of the primary key ( when two fields make up the primary key) or each determinant is also a candidate key.

When is a relation in Second Normal Form (2NF)?

A relation is in second normal form if it has a single-column primary key or we can identify a field (or set of fields) upon which all remaining fields are functionally dependent, even partially

What is a Determinant?

An attribute or group of attributes on which another attribute is functionally dependent. Determinants create problems when they are neither primary keys nor are they candidate keys because then they create partial dependencies.

What is a ER Diagram?

An entity-relationship diagram which shows relationship between entities

Durability in database transactions means...

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

2NF means what?

It's in 1NF. All non-key attributes are dependent on all parts of the primary key, but determinants may not be.

What are Foreign keys?

Keys identifying the relationship between different entities by the two entities including a key they have in common

What is included in a Logical Data Model?

Logical Data Model includes 1) All entities and relationships among them. 2) All attributes for each entity are specified. 3) The primary key for each entity is specified. 4) Foreign keys (keys identifying the relationship between different entities) are specified. 5) Normalization occurs at this level.

Data Definition Language (DDL)

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

What is a One-to-Many (1:M) relationship and how common is it?

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

What is a Many-to-Many (M:M) and how common is it?

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

What are the components used in information modeling?

entities, attributes, records, relationships

What is a DBMS?

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

Insertion anomaly

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

What is 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.

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

to differentiate levels of abstraction versus detail in the model

What is functional dependency?

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

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

What are the steps for designing a Logical Data Model?

1) Specify primary keys for all entities defined in conceptual model. 2) Specify all attributes for each entity. 3) Determine functional dependencies within each entity. 4) Specify the relationships between different entities by identifying common columns. 4) Resolve many-to-many relationships. 5) Normalization.

What is a One-to-one (1:1) relationship and how common is it?

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

What are two key questions that need to be addressed when evaluating an information model?

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

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.

What is a Relationship and how is it represented on E-R diagram?

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

1NF means what?

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

How is an entity represented on E-R diagram?

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

What is database design?

Techniques used for information modeling

Atomicity in database transactions means...

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

Isolation in database transactions means...

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

Consistency in database transactions means...

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

What is a Data Model?

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

What are entities and how are they organized?

any object or event which someone chooses to collect data on, organized by tables

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 a database?

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

What are attributes and how are they organized?

characteristics of an entity, organized by columns

What are records and how are they organized?

collection of data items that have something in common with the entity, organized in rows