Topic 1.4 Relational Databases Week 4 Flashcards
What is a database
- 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
What is the difference between database and file based legacy
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
Advantages of databases
- 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
Logical view of data has
Are at an external level of the database. Can access it but don’t worry how it’s stored etc
Physical view data base
Internal view of database. Interested how data is stored in the system
Know what people want access to and what they want stored etc
What does the database management system do
Translates users logical view into instructions as to which data is to be retrieved from the data base
What does operating system do
Translate database management systems into machine code to physically retrieved data
What are schemas
A plan or layout. 3 types
Conceptual level - organisation wide view
External level - individuals users view
Internal level - low level view
What are the 3 different database management system languages
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
What does relationally database do
- Represents the conceptual and external schema as if that “data view” were truly stored in one table
What are primary keys
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
What is a foreign key
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
What are non key attributes
Non-key attributes which store important data about the entity
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
Rules for relational databases
- 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
What does normalisation do in relational databases
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)
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
Increase data sharing
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?
Data query language
The ________ is responsible for the database.
Database administrator
When the human resources manager wants to build a data dictionary, the manager would use which language?
Data definition language
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
Registration number
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
Reduce data redundancy
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
Data mining
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.
Subschema
The ________ contains information about the structure of the database.
Data dictionary
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.
External level schema
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
Parental relationships
The problem of changes being incorrectly recorded in a database is known as
An update anomaly
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
Referential integrity rule
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?
Data mining
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
Integration
Which is probably the most immediate and significant effect of database technology on accounting?
Quicker access to and greater use of accounting information in decision making
The ________ handles the link between the way data are physically stored and each user’s logical view of that data.
Data base management systems
Which of the following elements link rows in one table to rows in another table?
Foreign keys
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
Rare cars
A data warehouse
is primarily used for analysis rather than transaction processing.
The use of a data warehouse in strategic decision making is often referred to as
Business intelligence
Why behavioural problem occur?
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
Types of resistance
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
How to prevent behavioural problems
Management support
- why we need it, assure it will be for good
Involve users in decision making
Provide training
Keep system simple
How to gather data?
Four common methods of gathering data
Interviews
Questionnaire
Observation
System documentation
