Terms in this set (45)
data is stored in one giant table. Typically different functions have separate flat files that do not link to each other.
data is shared between functions. Three types of databases 1) Hierarchical, 2) Network, and 3) Relational
Fixing data so you don't have issues
Process of defining a data base so that it faithfully represents all aspects of the organization.
anything about which the organization wishes to store data.
Also called fields - what you want to know about the entity
All the fields containing data about one entity (e.g., one employee)
Each table has a primary key, Each field in the table should tie back to the primary key, The primary key has to be unique; Uniquely identifies a record, Entity integrity - cannot be null
used to link table, Foreign keys are primary keys from other tables.
The REA model separates a business process into three types of entities:
Resources - things of value an organization acquires and uses (e.g., cash, inventory, equipment)
Events - business activities, [e.g., sales order (business event), sales event (economic event)]
Agents - people and organizations (e.g., employee, customer)
The entities are represented in an entity-relationship diagram (ERD).
Creating an ERD
Identify events (Economic, Business)
Identify resources and agents (What and Who)
Determine the linkages between entities.
Determine the cardinalities.
one-to-one relationship (1:1)
exists when the maximum cardinality for each entity in the relationship is 1.
one-to-many (1:N) relationship
exists when the maximum cardinality on one side is 1 and the maximum on the other side is many.
many-to-many (M:N) relationship
exists when the maximum on both sides is many.
Designing A Database Using An ERD
Create a table for each entity and for each many-to-many relationship.
Identify attributes, including primary keys (A relationship table contains the primary keys from the two entities along with attributes, The primary key is called a "concatenated" key.)
Assign foreign keys for each relationship that is not many-to-many.
Every line in an ERD should result in a foreign key or a relationship table
Person responsible for maintaining the database
Describes each field in the database
Data integrity controls
ensure accuracy of data
ensure transactions are performed accurately
prevent simultaneous access to a table by multiple users and ensure that transactions are performed sequentially
Database Management Systems (DBMS)
set of computer programs that enable users to create, modify and utilize database information.
Data Definition Language (DDL)
the language used to create and format the fields in a database.
Examples of DDL functions
Determine field size (number of characters), Assign field name, Set data type (text, numeric, date, etc), Identify primary key, Establish default value
Data Manipulation Language (DML)
Language used for data maintenance (Updating (change data), Inserting (add data), Deleting (remove data))
Refers only to data names
the entire database
a subset of the database (called a view)
In addition to extracting data, queries can also be used to change or delete a record.
Query answer can be used to update database (using DML)
Online Analytical Processing (OLAP)
extract data, allows users to summarize data by different dimensions, also has "drill down" capability, which allows users to click on data and see one less level of aggregation.
Structured Query Language (SQL)
standardized language used to query relational data bases.
used to identify which columns will be displayed.
If you want to see a column in your output, the column must be included in the SELECT statement.
used to identify the tables that will be queried.
The tables needed to access the columns used in the SELECT and the WHERE statements must be included in the FROM statement.
used to select which rows to retrieve. Conditions are defined using statements such as, =, <=, >=, or BETWEEN.
Several conditions can be linked using an 'AND' statement
When more than one table is included in the query, the columns on which the tables are to be linked must be identified in the WHERE statement as follows:
Table name.column name = table name.column name
the total for a column
the lowest amount in a column
the highest amount in a column
the number of observations in a column
the average for a column
used to identify which field is subject to a mathematical operation (subtotals)
used to sort the answer in ascending or descending order (sort output)
cannot enter some of the data in the system because you don't have some of the information you need.
You delete some of the info you don't mean to by deleting something else.
You run the risk of having different information where it should be the same (data inconsistency).
Open Invoice System
when you get an invoice you pay it. No account. The relationship between sales and cash receipts in 1:1.
Installment Sale Agreement
One invoice with multiple payments. Relationship is 1:N.
Revolving Credit Arrangement
the vendor gives a credit limit and you can spend up to that limit. You get the bill, you pay what you can when you want to. A M:N relationship. (Ex: credit card).