Study sets, textbooks, questions
Upgrade to remove ads
AIS Exam 2
Terms in this set (62)
Normalization involves identifying and removing structural dependencies from tables being modeled. True or False?
What is a fully normalized database called?
third normal form (3NF)
An REA diagram must link every event to at least one___and two__.
Which elements link rows in one table to rows in another table?
Which of the following is an example of a DML statement?
What is the first step to implementing an REA diagram in a relational database?
-Create table for each distinct entity and each M:M relationship
-Assign attributes to appropriate tables
-Use foreign keys to implement one-to-one and one-to-many relationships
Create table for each distinct entity and each M:M relationship
A table is in first normal form when it is
-free of repeating group data
-free of transitive dependencies
-free of partial dependencies
-free of update anomalies
free of repeating group data
The logical view of a database system refers to
-how master files store data values used by > one application program
-how the DBMS accesses data for a certain application program
-how a user or programmer conceptually organizes and understands the data
-how and where the data are physically arranged and stored
how a user or programmer conceptually organizes and understands the data
At least two REA agents participate in each economic event. True or False?
A primary key does not have to be unique for each record. True or False?
Which duty is not the responsibility of the database administrator?
-to develop and maintain the data dictionary
-to design application programs
-to design the subschema
-to implement security controls
to design application programs
An excel file can be a database. True or False?
What acts as an interface between the database and the various application programs?
-Database management system
Database management system
The formal name for a row in a database table is
Identify the attribute below that would not make a good primary key
-a unique number automatically generated by the system
-Social security number
-Cell phone number
The Database Approach
Pools data into a common database shared by all programs/users, which resolves problems.
No data redundancy as each data element is only stored once.
(minimizes data redundancy and inconsistencies)
Changes any user makes updates values for all programs/users.
(minimizes data redundancy and inconsistencies)
The data is separated from the program. (Users have access to all firm data, subject to authorizations)
In DB terms, is called normalization. Needed for understanding redundant, inconsistent, or incomplete data. Impacts the audit trails!
A related group of files is called what?
What is a related group of records called?
What is an instance of an entity?
A record. Contains all the attributes of that single entity. Often called rows or tuples
Something in which you are collecting data about
Conceptual level schema
Organization wide view. To design a DB, you need to have a conceptual view of the entire DB.
External level schema
Individual user's view (how they see what they have access to)
Internal level schema
low level view (physical arrangement of DB records). How everything is stored on the hard drive or computer.
The Database Administrator (DBA)
Responsible for managing database resources:
-May be an entire department in large organizations.
-Database planning, design, implementation, operation and maintenance, change and growth.
-User requests go to the DBA who evaluates them to determine user needs and grants access authority.
-Creates and maintains the data dictionary
"blueprint" of the DB
-Describes every data element, field type, etc.
Database Management System (DBMS)
Special software system that controls access to the database
-Microsoft SQL Server
Data Definition Language (DDL)
-Creates the DB and builds the structure of the tables and relationships
-Defines logical views for each user
Data manipulation language (DML)
-Changes the content in the DB
-Updates, inserts and deletes
Data query language (DQL)
-Retrieves data from the DB
-Attributes of an entity
-Often called columns
Actions taken when a record meets certain criteria. Ex. If an inventory level gets below a fixed amount, an automatic purchase is executed on this inventory level to make sure we never run out.
Common DB actions
formal name for a row in the table.
-Represents an instance of the entity
data elements that define an entity
the unique attribute or combination of attributes that can uniquely identify a specific record.
-Other non-key attributes need not be unique
-This is a REQUIRED value for the record; CANNOT be null (blank)
primary key from another table that relates entities
-This is the basis for the relationships in a relational DB
-The timing to be replicated to the warehouse and/or retained in the DB varies
-Accuracy more of a factor than speed
-Typically run reports against the data warehouse so you don't impact the speed of the DB
Aimed at eliminating anomalies
-Goal is to be at 3NF
result of data redundancy in an unnormalized table. Ex. Somebody moves and have to update multiple places and miss one in the system and it messes the system up.
occurs when data cannot be added to a table because of its dependence on something else
unintentional deletion of data from a table. Ex. You accidentally delete a customer record and don't realize it then you have no record that the customer ever existed.
First normal form (1NF)
-eliminates repeating groups
-should be able to create a primary key
Second normal form (2NF)
-In 1NF AND
-Eliminates partial dependency
Third normal form (3NF)
-In 1NF and 2NF AND
-Eliminates transitive dependencies
Steps in the Normalization Process
1. Remove all repeating groups to create the 1NF and select the primary key
2. Remove partial dependencies (attributes dependent on part of the primary key) to create the 2NF. *Only applies when the 1NF has a concatenated key (2 or more attributes for the primary key)
3. Remove all transitive dependencies (non primary key attributes dependent on non primary key attributes)
Process of defining a database so that it faithfully represents all aspects of the organization, including interactions with the external environment (System analysis and conceptual design)
-Typically done with Entity-relationship (E-R) diagrams
-REA data model invented for AIS
anything about which the organization wants to collect and store data about
Things that have economic value to the organization
Various business activities that management wants to collect information on
People and organizations that participate in events, both internal and external
1. Each event is linked to at least one resource that it effects
2. Each event is linked to at least one other event (duality)
3. Each event is linked to at least two participating agents (usually one internal and external)
4. Diagram has 3 columns
-Resources on the left
-Events in the middle
-Agents on the right
5. Drawn top to bottom corresponding to the sequence in which they occur
Steps to develop an REA diagram
Step 1. Identify relevant events
Step 2. Identify resources and agents
Step 3. Determine cardinalities of relationships
Nature of the relationships indicating the number of occurrences of one entity that may be associated with a single occurrence of the other entity
Using an REA diagram to build a relational database
1. Create tables for each entity AND M:M relationship
2. Assign attributes to each table
-Identify primary keys
-Concatenated keys for M:M relationship table
3. Use foreign keys to implement 1:1 and 1:M relationships
Query event tables
Query resource tables
Query resources and events using multiple queries
Query financial and non financial information
Recommended textbook explanations
John Buck, William Hayt
Introduction to Algorithms
Charles E. Leiserson, Clifford Stein, Ronald L. Rivest, Thomas H. Cormen
Operating System Concepts
Abraham Silberschatz, Greg Gagne, Peter B. Galvin
Database Systems: The Complete Book
Hector Garcia-Molina, Jeffrey D Ullman, Jennifer Widom
Sets with similar terms
AIS Relational Databases
ACC 409- Chapter 4
ACC 409- Chapter 4
Other sets by this creator
ENT 311 Midterm
AIS Exam #1. Chapters 1-3, 5, 16
BIA Exam 3
Other Quizlet sets
Module 2 micro for test
Contexte 4: La tolérance, Lecture 4.1
Intro to HCP Chapter 21
Chem - Ch 9 (aqueous solutions)