Systems Exam 3

What are the standard SQL commands?

1. "Select" 2. "Insert' 3. "Update" 4. "Delete" 5. "Create" 6. "Drop"

What is Data Definition Language (DDL)?

1. Builds the data dictionary 2. Creates the database 3. Describes logical views for each user 4. Specifies record or field security constraints

What is Data Manipulation Language (DML)?

1. Changes the content in the database --Creates, updates, insertions, and deletions

How can you use an REA diagram to build a relational database?

1. Create tables for each distinct entity and M:N relationship 2. Assign attributes to each table --Identify primary keys --Concatenated keys for M:N relationship table 3. Use foreign keys to implement 1:1 and 1:N relationships

List the four DBMS "languages" and describe who uses each and for what purpose.

1. Data definition language (DDL) is used by designers, the purpose of using DDL is to create the database, describes logical view for each user, and specifies record or field security constrains. 2. Data manipulation language (DML) is used by programmers, the purpose of using it is to change database content, including data element creations, updates, insertions, and deletions. 3. Data query language (DQL) is used by IT users and decision makers, the purpose of using it is to integrate database, contains powerful, easy-to-use commands that enables users to retrieve, sort, order, and display data. 4. Report writer is used by IT programmers, its purpose is to facilitate report generation, and simplifiers report creation.

What are the advantages of databases?

1. Data is integrated and easy to share 2. Minimize data redundancy 3. Data is independent of the programs that use the data 4. Data is easily accessed for reporting and cross-functional analysis

Describe the three basic rules that apply to the REA model pattern.

1. Each event is linked to at least one resource that it affects. 2. Each event is linked to at least one other event. 3. Each event is linked to at least two participating agents.

What is Data Query Language (DQL)?

1. Enables users to retrieve, sort, and display specific data from the database

What are the relational database design rules?

1. Every column in a row must be single valued 2. Primary key cannot be null (empty) also known as entity integrity 3. IF a foreign key is not null, it must have a value that corresponds to the value of a primary key in another table (referential integrity) 4. All other attributes in the table must describe characteristics of the object identified by the primary key

Explain the five rules for drawing integrated REA diagrams.

1. Every event must be liked to at least one resource. 2. Every event must be linked to two agents who participate in that event. 3. Every event that involves the disposition of a resource must be linked to an event that involves the acquisition of a resource. (This reflects the economic duality underlying "give-to-get" economic exchanges). 4. Every resource must be linked to at least one event that increments that resource and to at least one event that decrements that resource. 5. If event A can be linked to more than one other event, but cannot be linked simultaneously to all of those other events, then. The REA diagram should show that event A is linked to a minimum of 0 of each of those other events.

Describe the steps in developing an REA diagram.

1. Identify the events about which management wants to collect information. 2. Identify the resources affected by each event and the agents who participate in those events 3. Determine the cardinalities of each relationship

Explain the difference between file-oriented transaction processing systems and relational.

1. In file-oriented approach , many files managed by many users. This results in a significant increase in number of master files stored by an organization. 2. In the database approach , data is an organizational resource that is used by and managed for the entire organization. The program that manages and controls the data and the interfaces between data and application programs is called the database management system (DBMS).

Explain the types of attributes that tables possess in a relational database.

1. Primary key - the attribute, or combination of attributes, that uniquely identify a specific row in a table. 2. Foreign key - an attribute appearing in one table that is a primary key in another table. 3. Nonkey attributes found in tables - For example, an inventory table may contain information about the description, quantity on hand, and list price of each item a company sells.

What is a relational database?

1. Represents the conceptual and external schema as if that "data view" were truly stored in one table. 2. Although the conceptual view appears to the user that this information is in one big table, it really is a set of tables that relate to one another

What are the most common data types?

1. char(size)- Fixed-length character string. Size is specified in parenthesis. Max 255 bytes 2. varchar(size)- Variable-length character string. Max size is specified in parenthesis. 3. number(size)- Number value with a max number of column digits specified in parenthesis. 4. date- Date value 5. number(size,d)- Number value with a maximum number of digits of "size" total, with a maximum number of "d" digits to the right of the decimal.

Explain the two advantages semantic data modeling has over normalization when designing a relational database.

1. knowledge of business processes facilitates the efficient design of transaction processing databases 2. the graphical model explicitly represents the organization's business processes and policies and, by facilitating communication with system users, helps ensure that the new system meets users' actual needs

Define minimum and maximum cardinalities.

1. minimum: the least number of instances an entity can participate in an instance of a relationship 2. maximum: the most number of instances an entity can participate in an instance of a relationship

Discuss the steps for designing and implementing a database system.

1: Systems analysis-initial planning to determine the need for and feasibility of developing a new system. 2: Conceptual Design-developing different schemas of new system at conceptual, external, and internal levels. 3: Physical Design-translating internal-level schema into actual database structures that will be implemented in the new system. 4: Implementation and Conversion-All activities associated with transferring data from existing systems to new database AIS, testing, and training. 5: Using and maintaining the new system.

Discuss the ways in which a well-designed DBMS will facilitate the three basic functions of creating, changing, and querying data.

A DBMS will use data definition, data manipulation, and data query languages in order to perform the three basic, essential data functions. 1. Data definition is achieved using DDL (data definition language) 2. data manipulation is achieved using DML (data manipulation language) which includes operations such as updating, inserting, and deleting portions of the database. 3. DQL (data query language) is used to retrieve, sort, order, and present subsets of data in response to user queries. A DBMS will probably also include a report writer, which is a language that simplifies report creation.

Describe the different schemas involved in a database structure. What is the role of accountants in development of schemas?

A schema describes the logical structure of a database. There are three levels of schemas: 1. the conceptual-level schema- an organization-wide view of the entire database listing all data elements and relationships between them. 2. external-level schema- a set of individual user views of portions of the database, each of which is referred to as a subschema. 3. internal-level schema provides a low-level view of the database includes descriptions about pointers, indexes, record lengths, etc. Accountants are primarily involved in the development of conceptual- and external-level schemas; however, database knowledgeable accountants may participate in developing an internal-level schema.

Explain specifically what is meant by the following statement: "Accountants can and should participate in all stages of the database design process."

Accountants are in a unique position within a business organization. They are intimately acquainted with the many business transactions that occur in an organization and they are knowledgeable about the policies and practices of the business itself as well as the environment within which it operates. The knowledge base and skill sets of the accountant should be put to good use in the development of database design to the fullest extent possible (although some accountants may not possess AIS coding and development skill sets). Consider that during the planning stage accountants provide some of the information used to evaluate the feasibility of the proposed project and they participate in making the decision itself. Accountants can identify user information needs and develop logical schema during the requirement analysis and design stages. Accountants can also help test the accuracy of the new database and application programs during the implementation stage of development. Accountants can also act as "subject matter experts" since they are knowledgeable users of the new system. They can also serve as managers of the system once it is up and running.

Describe a data dictionary.

a collection of descriptions of the data objects or items in a data model for the benefit of programmers and others who need to refer to them

What is the first step in implementing an REA diagram in a relational database?

Create a table for each distinct entity in the diagram and for each many-to-many relationship

Describe an REA data model

Data model used to design AIS databases, containing information about resources, events, and agents.

Why have a set of related tables?

Data stored in one large table can be redundant and inefficient causing the following problems: --Update anomaly --Insert anomaly --Delete anomaly

Describe a major advantage of database systems over file-oriented transaction processing systems.

Database systems separate logical and physical views. This separation is referred to as program-data independence. Such separation facilitates developing new applications because programmers can concentrate on coding the application logic (what the program will do) and do not need to focus on how and where the various data items are stored or accessed. In the file-oriented transaction systems, programmers need to know physical location and layout of records which adds another layer of complexity to programming.

Define cardinality.

Describe nature of relationship between two entities by indicating how many instances of one entity can be linked to each specific instance of another entity.

What are logical views?

Different users of the database information are at an external level of the database.

What is a database?

Efficiently and centrally coordinates information for a related group of files -A file is a related group of records -A record is a related group of fields -A field is a specific attribute of interest for the entity (record)

Discuss redundancy as it applies to database design.

In the case where multiple systems exist, numerous problems and inefficiencies become problematic. Often the same data must be captured and stored by more than one system, which not only results in redundancy across systems but can also lead to discrepancies if data are changed in one system but not in others.

What are agents?

People and organizations that participate in events (both internal (e.g., employees) and external (e.g., customers/vendors) to the organization)

What is data modeling?

Process of defining a database so that it faithfully represents all aspects of the organization, including its interactions with the external environment. --Entity-relationship (E-R) diagrams --REA data model

What is the difference in logical view and physical view?

Relational database tables are logical views of data that are physically stored in the database. The logical database view is how the data appear to be stored when the user accesses the database; that is, it is the way the user sees the database. The logical view provides a structure that the user can use to extract data from the database. The physical view is a representation of the way in which the data are stored on the disk or other storage medium used by the database management system. The physical view of the data often has little relation to the logical view.

Which is a true statement about the REA data model?

The REA data model classifies entities into 3 distinct categories

What are resources?

Things that have economic value to the organization (e.g., inventory, cash)

What are events?

Various business activities that management wants to collect information on

In SQL, columns are _______ and rows are __________.

attributes; data

When using an REA diagram to develop a database, a many-to-many relationship must be

broken into two, one-to-many relationships

The primary key for a Purchase-Inventory table that consists of the Purchase Order Number and the Inventory Item Number, is known as a

concatenated key

What is an example of the "create table" statement?

create table employee (first varchar(15), last varchar(20), age number(3), address varchar(30), city varchar(20), state varchar(20));

Example of "delete" statement?

delete from employee where lastname = 'May';

Describe the information that is contained in the data dictionary.

describes what kind of data is carried within a database

Explain a completeness check.

determines if all required data have been items have been entered

A(n) _________ diagram graphically depicts a database's contents by showing entities and relationships

entity-relationships

In which stage(s) of the database design process does data modeling occur?

in both the systems analysis and design stages

Example of "insert" statement?

insert into employee (first, last, age, address, city, state) values ('Luke', 'Duke', 45, '2130 Boars Nest', 'Hazard Co', 'Georgia');

What is the data dictionary?

is a "blueprint" of the structure of the database and includes data elements, field types, programs that use the data element, outputs, and so on

Where in the REA model you can find the information normally found in a journal.

it is contained in the tables used to record data about events

Explain where in the REA model you can find the information normally found in a ledger.

it is stored in resource tables in an REA-based relational database

What does the "select" statement do?

it is used to query the database and retrieve selected data that match the criteria that you specify. Here is the format of a simple select statement: select "column1" [,"column2",etc] from "tablename" [where "condition"]; [] = optional

Every person in the world has a birthdate, but no person has more than one birthdate. Thus, the cardinality that exists between birthdate and people is

one-to-many

Explain how an AIS system can be viewed as a set of "give-to-get" exchanges.

organizations typically engage in activities that use up resources in the hopes of acquiring some other resource in exchange

The first step in developing an REA diagram for a specific transaction cycle begins with identifying __________.

relevant events

What does the "like" operator allow you to do?

select only rows that are "like" what you specify select first, last, city from empinfo where first LIKE 'Er%';

The major benefits of using the REA model as the basis for designing an AIS include all of the following except

the REA data model reduces the need for accountants to understand the underlying accounting journals and ledgers

What does the conceptual view illustrate?

the different files and relationships between the files

Which of the following statements is true about the development of an REA model?

the objective is to model basic value-chain activities

What is the "drop table" command used for?

to delete a table and all rows in the table

Example of "update" statement?

update phone_book set area_code = 623 where prefix = 979;

What does the "where" clause specify?

which data values or rows will be returned or displayed, based on the criteria described after the keyword "where"

What are physical views?

At an internal level of the database is the physical view of the data which is how the data is actually physically stored in the system.