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