Topic 1.4 Relational Databases Week 4

Question 1

What is a database

Answer 1

• efficiently and centrally coordinates information for a related group of files (database)
  File - a group of related records
  Record - a group of related fields
  A field is a specific attribute of interest for the entity

Question 2

What is the difference between database and file based legacy

Answer 2

File based legacy - had multiple master file databases that had facts

Database system - keeps facts that used to be in master files in a database so they can be accessed whenever. Not stored somewhere else

Question 3

Advantages of databases

Answer 3

• data is integrated
• data sharing
• minimises data redundancy and inconsistencies
• data is independent of the programs that use the data
• data is easily accessed for reporting and cross functional analysis

Question 4

Logical view of data has

Answer 4

Are at an external level of the database. Can access it but don’t worry how it’s stored etc

Question 5

Physical view data base

Answer 5

Internal view of database. Interested how data is stored in the system
Know what people want access to and what they want stored etc

Question 6

What does the database management system do

Answer 6

Translates users logical view into instructions as to which data is to be retrieved from the data base

Question 7

What does operating system do

Answer 7

Translate database management systems into machine code to physically retrieved data

Question 8

What are schemas

Answer 8

A plan or layout. 3 types

Conceptual level - organisation wide view

External level - individuals users view

Internal level - low level view

Question 9

What are the 3 different database management system languages

Answer 9

Data definition language (DDL)

• builds the data dictionary
• creates the database
• describes the logical views for each user
• specifies record or field security constraints

Data manipulation language (DML)
- changes the content in the database
Creates, updates, insertions, and deletions

Data Query Language (DQL)
- enables users to retrieve, sort, and display specific data from the database

Question 10

What does relationally database do

Answer 10

• Represents the conceptual and external schema as if that “data view” were truly stored in one table

Question 11

What are primary keys

Answer 11

The key that identifies the record in a table e.s in a sales order it’s the sales number

An attribute or combination of attributes that can be used to uniquely identify a specific row (record) in a table

Question 12

What is a foreign key

Answer 12

Is a key that links two tables together. The foreign key in one table is the primary key in another table

An attribute in one table that is a primary key on another table
- used to link two tables

Question 13

What are non key attributes

Answer 13

Non-key attributes which store important data about the entity

Question 14

Why have a set of related tables?

Answer 14

Data stored in one large table can be redundant and inefficient causing the following problems:

• update anomaly
• insert anomaly
• delete anomaly

Question 15

Rules for relational databases

Answer 15

• every column in a row must have a single value
• primary key cannot be null (empty) also known as entity integrity
• 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)
• all other attributes in the table must describe characteristics of the object identified by the primary key

Following these rules allows databases to be normalised and solves the update, insert and delete anomalies

Question 16

What does normalisation do in relational databases

Answer 16

Initially, one table is used for all the data in a database
The table is decomposed into multiple smaller tables related by primary and foreign keys
This is called 3rd normal form (3NF)

Question 17

Emma Kolb is a system analyst at a manufacturing company located in the Midwest. She has been asked to analyze the company’s accounting information system and to recommend cost-effective improvements. After noting that the several key managers do not have access to the production and sales information, she recommends that they should be uploaded to the company’s internal network and be made available for search. Implementation of her recommendation would benefit the company by contributing to

Answer 17

Increase data sharing

Question 18

When the human resources manager wants to gather data about salary and start date by employees and by departments, the manager would use which language?

Answer 18

Data query language

Question 19

The ________ is responsible for the database.

Answer 19

Database administrator

Question 20

When the human resources manager wants to build a data dictionary, the manager would use which language?

Answer 20

Data definition language

Question 21

Tom Wilson is chairman of the Rare Car Club, where he maintains a database of registered rare cars and their history. One table will store the model, make, vehicle identification number, and other characteristics of all of the registered rare cars. Each rare car is uniquely identified by a registration number. A second table will contain data that link each rare car to its most recent estimated fair market value by means of their registration numbers. The primary key in the first table is

Answer 21

Registration number

Question 22

Emma Kolb is a system analyst at a manufacturing company located in the Midwest. She has been asked to analyze the company’s accounting information system and to recommend cost-effective improvements. After noting that the same production files have been saved and stored independently on several databases, she recommends that they be stored only once. Implementation of her recommendation would benefit the company by contributing to

Answer 22

Reduce data redundancy

Question 23

Merlin Frodo has been doing custom piercings and tattoos for over thirty years. His home and place of business is a garage in the harbor district of Seattle, Washington. He keeps meticulous records of every job he has ever completed. These have been entered into a computerized accounting information system that his accountant refers to as a “data warehouse.” Merlin is considering adding scarification to his service offerings and has asked his accountant to identify past customers who might be likely candidates for this service. Merlin wants his accountant to engage in

Answer 23

Data mining

Question 24

Mary Lamb is an insurance sales representative. Her company has provided her with a laptop computer that uses wireless connectivity to access the accounting information system from virtually anywhere in the country. She, and other sales reps, have access to view customer and insurance product information. All sales reps also have access that allows them to enter and cancel customer orders. The permissions for Mary define a ________ in the company’s database management system.

Answer 24

Subschema

Question 25

The ________ contains information about the structure of the database.

Answer 25

Data dictionary

Question 26

Mary Lamb is an insurance sales representative. Her company has provided her with a laptop computer that uses wireless connectivity to access the accounting information system from virtually anywhere in the country. She has access to view customer and insurance product information. She also has access that allows her to enter and cancel customer orders. The permissions for Mary (and other sales reps) define a ________ in the company’s database management system.

Answer 26

External level schema

Question 27

Pat Owen is chair of the National Grizzly Society, which maintains a database of all grizzly bears within the United States. One table will store the name, birth date, and other characteristics of all of the grizzly bears that have been registered. Each grizzly bear is uniquely identified by a registration number. A second table will contain data that link each grizzly bear to its male and female parents by means of their registration numbers. The entities described by the second table are

Answer 27

Parental relationships

Question 28

The problem of changes being incorrectly recorded in a database is known as

Answer 28

An update anomaly

Question 29

The database requirement that foreign keys must be null or have a value corresponding to the value of a primary key in another table is formally called the

Answer 29

Referential integrity rule

Question 30

Mara Kay is a system analyst for a national department store. She was tasked to identify previously unknown relationships in the company’s sales data that can be used in future promotions. What technique would Mara most likely use?

Answer 30

Data mining

Question 31

Emma Kolb is a system analyst at a manufacturing company located in the Midwest. She has been asked to analyze the company’s accounting information system and to recommend cost-effective improvements. After noting that the production and sales departments use database systems that are entirely separated, she recommends that they be combined. Implementation of her recommendation would benefit the company by contributing to data

Answer 31

Integration

Question 32

Which is probably the most immediate and significant effect of database technology on accounting?

Answer 32

Quicker access to and greater use of accounting information in decision making

Question 33

The ________ handles the link between the way data are physically stored and each user’s logical view of that data.

Answer 33

Data base management systems

Question 34

Which of the following elements link rows in one table to rows in another table?

Answer 34

Foreign keys

Question 35

Tom Wilson is chairman of the Rare Car Club, where he maintains a database of registered rare cars and their history. One table will store the model, make, vehicle identification number, and other characteristics of all of the registered rare cars. Each rare car is uniquely identified by a registration number. A second table will contain data that link each rare car to its most recent estimated fair market value by means of their registration numbers. The entities described by the first table are

Answer 35

Rare cars

Question 36

A data warehouse

Answer 36

is primarily used for analysis rather than transaction processing.

Question 37

The use of a data warehouse in strategic decision making is often referred to as

Answer 37

Business intelligence

Question 38

Why behavioural problem occur?

Answer 38

Fear
-of failure, the unknown, losing status

Lack of top management support
- if top management is not supportive why should the employee change

Bad prior experience
-bad experience with prior IS changes

Poor communication
- employees need to understand why change is necessary

Disruption

Manner change is introduced

Biases and emotions

Personal characteristics and background

- age
- open to technology and comfortable with it

Question 39

Types of resistance

Answer 39

Aggression
- purposely weakening system or making errors. Sabotage

Projection
- start blaming new system for everything that goes wrong

Avoidance
- ignoring a new system in hope that it will go away

Question 40

How to prevent behavioural problems

Answer 40

Management support
- why we need it, assure it will be for good

Involve users in decision making

Provide training

Keep system simple

Question 41

How to gather data?

Answer 41

Four common methods of gathering data

Interviews
Questionnaire
Observation
System documentation