AIS Exam 3

3 types of cardinalities

1-to-1 1-to-many many-to-many

Basic REA Rules

1. Each EVENT is linked to at least 1 RESOURCE that it affects 2. Each EVENT is linked to at least 1 other EVENT 3. Each EVENT is linked to at least 2 participating AGENTS

Basic Requirements of a Relational Database

1. Every column in a row must be single valued 2. Primary keys cannot be null 3. Foreign keys (if not null, must have values that correspond to the value of a primary key in another table) 4. Any nonkey attributes in a table must describe a characteristic of the object identified by the primary key

2NF Steps

1. Remove Repeating Groups 2. Remove the partial dependencies 3. Remove Transitive Dependenicies

REA data model

A data model used to design AIS databases. It contains information about 3 fundamental types of entities: resources, events, and agents

Entity integrity rule

A non-null primary key ensure that every row in a table represents something and that it can be identified

Events

Business activities about which management wants to collect information for planning and controlling purposes

Minimal data redundancy and data inconsistencies

Data items are usually stored only once

Data modeling

Defining a database so that it faithfully represents all key components of an organization's environment

Cardinalities

Describe the nature of a database relationship indicating the number of occurrences of one entity that may be associated with a single occurrence of the other entity.

When a row cannot be uniquely identified, what rule had been violated?

Entity integrity rule

Normalization

Following relational database creation rules to design a relational database that is free from delete, insert, and update anomalies

Referential integrity rule

Foreign keys which link rows in one table to rows in another table must have values that correspond to the value of a primary key in another table

Insert anomaly

Improper database organization that results in the inability to add records to a database

Update anomaly

Improper database organization where a non-primary key item is stored multiple times; updating the item in one location and not the others cause data inconsistencies

Delete anomaly

Improper organization of a database that results in the loss of all information about an entity when a row is deleted

Data sharing

Integrated data are more easily shared with authorized users

Which of the following is NOT an advantage to using a relational database?

Introduces data anomalies

Data integration

Master files are combined into large "pools" of data that many application programs access

Database System

The database, DBMS, and the application programs that access the database through DBMS

Maximum cardinality

The maximum number of instances that an entity can be linked to the other relationship 1 & many

Agents

The people and organizations who participate in events and about who information is desired

Database Administration

The person responsible for coordinating, controlling, and managing the database

DBMS (database management system)

The program that manages and controls the data and the interfaces between the data and the application programs that uses the data stored in the database.

Resources

Those things that have economic value

T/F: The purpose of the primary key is to uniquely identify each record in a table.

True

Semantic data modeling

Using knowledge of business processes and information needs to create a diagram that shows what to included in a fully normalized database (3NF)

Schmaltz' Sandwich Shop keeps records of all its purchases of food products for its sandwiches. In their Purchase Order table, which of the following would most likely be a foreign key?

Vendor #

one-to-many relationship

a relationship between 2 entities where the maximum cardinality for one of the entities is 1 but the other entity has a maximum cardinality of many

many-to-many relationship

a relationship between 2 entities where the maximum cardinality of both entities is many

Tuple

a row in a table that contains data about a specific item in a database table

"Relational" database

a set of interrelated centrally coordinated data files that are stored with as little data redundancy as possible - the tables are connected

Data model

an abrstract representation of database contents

Foreign key

an attribute in a table that is also a primary key in another table; used to link the two tables

Business intelligence

analyzing large amounts of data for strategic decision making

entity

anything about which an organization wants to collect & store information

Cross-functional analysis

associations can be explicitly defined and used

Data independence

data & programs are independent of each other and can be changed without changing the other

Primary key

database attribute or combination of attributes that uniquely identifies each row in a table

Entity-relationship (E-R) diagram

graphical depiction of a database's contents showing the various entities being modeled and the important relationships among them

A foreign key must first be at _____ in another table, and its primary purpose is to link _____.

primary two tables

Minimum cardinality

the minimum number of instances that an entity can be linked to the other entity in the relationship 0 & 1

3 anomalies

update delete insert

Online analytical processing (OLAP)

using queries to investigate hypothesized relationship among data

Data mining

using sophisticated statistical analysis to "discover" unhypothesized relationship in the data

Data warehouse

very large databases containing detailed and summarized data for number of years that are used for analysis rather than transaction processing