Data Management Foundations- C175

a file having no internal hierarchy

A file that has been encrypted for security purposes.

An unsorted set of records.

The transformation of raw data into useful facts.

A card that is perforated and can hold commands or data.

Information with a high degree of organization.

Information that does not have structure (such as text)

A relationship between two entity types.

An associate occurrence of an entity type with other occurrences of the same entity type.

The maximum number of entities that can be involved in a particular relationship.

Diagram of entities together with their attributes and the relationship among them.

It is data that describes a many-to-many relationship.

It is a minimum number of entity occurrences that can be involved in a relationship.

It means that a single occurrence of one entity type can be associated with a single occurrence of the other entity type and vice versa.

Involves three different entity types.

It is used to uniquely identify each record in a database table.

A property, characteristic, or fact that we know about an entity.

One-to-one binary relationship

One-to-many binary relationship

Many-to-many binary relationship

Cardinality is the outer symbol; represents the maximum.

Modality is the inner symbol; represents the minimum.

One-to-one unary relationship

One-to-many unary relationship

Many-to-many unary relationship

This revolves around the circumstance of trying to refer to data in one relation in the database, based on values in another relation.

If the delete rule between two relations is RESTRICT and an attempt is made to delete a record on the "one side" of the one-to-many relationship, the system will forbid the delete to take place if there are any matching foreign key values in the relation on the "many side".

If the delete rule between two relations is CASCADE and an attempt is made to delete a record on the "one side" of the relationship, not only will the record be deleted but all of the records on the "many side" of the relationship that have a matching foreign key value will also be deleted.

In other words, the delete will "cascade" from one relation to the other.

If the delete rule between the two relations is SET-TO-NULL and an attempt is made to delete a record on the "one side" of the one-to-many relationship, that record will be deleted and the matching foreign key values in the records on the "many side" of the relationship will be set to null.

Essentially, it's exactly like the CASCADE delete option, but instead of completely deleting all possible values, the values are set to NULL instead.

Associative Entity

Weak Entity

This is when a relation has more than one attribute or minimum group of attributes that represents a way of uniquely identifying the entity.

When two or more users are trying to update the same record simultaneously.

Combines two or more tables based on a column that is common to the tables.

Example: Joining Client and Salesman tables that both contain the SalesmanID column which have the exact same values.

When an attribute or group of attributes serves as the primary key of one relation and also appears in another relation.

Matches each row in a table against each row in another table based on common values found in columns sharing a common name and data type.

Rows/records are referred to as tuples when talking about relations. They serve the exact same function, it just has a different name in the context of relations.

1. The creation of a datacentric environment that is a significant company resource, which can be shared inside and outside the company.

2. The ability to achieve data integration while storing data in a non-redundant fashion.

3. The ability to store data representing entities involved in multiple relationships w/o introducing data redundancy.

4. Managing data control issues such as data security, backup and recovery, and concurrency control.

5. High degree of data independence.

• Hierarchical

• Network

• Relational

• Object-oriented

• The columns of a relation can be arranged in any order w/o affecting the meaning of the data. That is not true of a file.

• Similarly, the rows of a relation can be arranged in any order, which is not true of a file.

• Every row/column position, sometimes referred to as a "cell", can have only a single value, which is not necessarily true in a file.

• No two rows of a relation are identical, which is not necessarily true in a file.

• It indicates that all attributes of the selected row are to be retrieved

• It displays a record if more than one condition is true

• It returns the average value of a numeric column.

• It allows you to specify a range of numeric values in a search.

• It is used to eliminate duplicate rows in a query result.

• It allows you to specify a list of character strings to be included in a search

• It is used to combine rows from more than one table, based on a common field between them. Sometimes it is done by using the '=' symbol.

• It allows you to specify partial character strings in a "wildcard" sense.

• It displays a record it either the first condition OR the second condition is true.

• It simply takes the result of a SQL query and orders them by one or more specified attributes.

• Data retrieval in SQL is accomplished with the SELECT command.

• When on SELECT statement is "nested" within another in a format, it is known as subquery. This is shown when there is a second SELECT phrase within a set of parenthesis.

• DROP

• ALTER

• RENAME

• CREATE

• TRUNCATE

• UPDATE

• DELETE

• INSERT

• MERGE

• SELECT

SELECT COMMPERCT, YEARHIRE FROM SALESPERSON WHERE SPNUM=186;

SELECT * FROM SALESPERSON WHERE SPNUM=186;

SELECT SPNUM, SPNAME FROM SALESPERSON WHERE COMMPERCT=10;

SELECT SPNUM, SPNAME, COMMPERCT FROM SALESPERSON WHERE COMMPERCT<12;

SELECT CUSTNUM, HQCITY FROM CUSTOMER WHERE CUSTNUM>=1700;

SELECT CUSTNUM, CUSTNAME, HQCITY FROM CUSTOMER WHERE HQCITY='New York' AND CUSTNUM>1500;

SELECT CUSTNUM, CUSTNAME, HQCITY FROM CUSTOMER WHERE HQCITY='New York' OR CUSTNUM>1500;

SELECT CUSTNUM, CUSTNAME, HQCITY FROM CUSTOMER WHERE HQCITY='New York' OR (CUSTNUM>1500 AND HQCITY='Atlanta');

SELECT * FROM CUSTOMER WHERE CUSTNAME LIKE 'A%';

SELECT CUSTNUM, CUSTNAME, HQCITY FROM CUSTOMER WHERE CUSTNUM>1000 ORDER BY HQCITY, CUSTNAME;

SELECT AVG(QUANTITY) FROM SALES WHERE SPNUM=137;

SELECT SUM(QUANTITY) FROM SALES WHERE SPNUM=137;

SELECT SPNAME FROM SALESPERSON, CUSTOMER WHERE SALESPERSON.SPNUM=CUSTOMER.SPNUM AND CUSTNUM=1525;

SELECT PRODNAME FROM SALESPERSON, PRODUCT, SALES WHERE SALESPERSON.SPNUM=SALES.SPNUM AND SALES.PRODNUM=PRODUCT.PRODNUM AND SPNAME='Adams' AND QUANTITY>2000;

The command that creates base tables and tells the system what attributes will be in them.

Specifies the base tables on which the view is to be based and the attributes and rows of the table that are to be included in the view.

Specify which row(s) of a table are to be deleted based on data values within those rows.

Discards an entire table from a database.

Discards views.

The process of organizing the fields and tables of a relational database to minimize redundancy (duplication) and dependency.

All non-key attributes must be functionally dependent on the entire key of that table.

Non-key attributes are not allowed to define other non-key attributes.

1. It is completely free of redundancy

2. All foreign keys appear where needed to logically tie together related tables.

3. It is the same structure that would have been derived from a properly drawn entity-relationship diagram of the same business environment.

INSERT INTO SALESPERSON VALUES ('489','Quinlan',15,'2011','59');

*Hint, this is DML, so remember that INSERT is one of the keywords for DML.

DELETE FROM SALESPERSON WHERE SPNUM = '186';

INSERT INTO table_name VALUES (value1,value2,value3,...):

CREATE VIEW view_name AS SELECT column_name(s) FROM table_name WHERE condition

Data normalization

Second form

CREATE TABLE table_name ( column_name data_type(size), );

UPDATE table_name SET column1=value1,column2=value2,... WHERE some_column=some_value;

Association rules specify a relation between attributes that appears more frequently than expected if the attributes were independent.

The processes, technologies, and tools needed to turn data into information, information into knowledge, and knowledge into plans that drive profitable business action.

Classification involves examining the attributes of a particular object and assigning it to a defined class.

Clustering is the task of taking a large collection of objects and dividing them into smaller groups of objects that exhibit some similarity.

Affinity Grouping is a process of evaluating relationships or associations between data elements that demonstrate some kind of affinity between objects.

• Financial value associated w/ increased profitability.

• Productivity value associated with increased throughput.

• Trust value (customer, employee, supplier satisfaction) as well as increased confidence in forecasting.

• Risk value - decreased risk associated with decision making

• Simplicity.

• Lack of bias.

• Extensibility.

• Centralized repository of information.

• Organized around relevant subject areas.

• Provides platform for queries.

• Used for analysis and not transactional processing.

• Data is nonvolatile.

• Target location for integrating data from multiple sources.

1. Get the data

2. Map the data to staging area

3. Validate and clean the data

4. Apply necessary transformations

5. Map data to loading model

6. Move data to repository

7. Load data to warehouse

Performance

Aggregation

Estimation