Download SQL Queries and Database Concepts and more Exams Advanced Education in PDF only on Docsity!
MIST 4610 - EXAM 1 (Multiple Choice
Study Guide)
Data Management - Answer- Data management is the development and execution of architectures, policies, practices and procedures in order to manage the information lifecycle needs of an enterprise in an effective manner. (Google definition) Challenges posed with the data deluge - Answer- 1. Protecting Privacy
- Providing Security
- Saving Energy
- Extracting Value 2 types of data management - Answer- Individual and Organizational what is high frequency trading? - Answer- computerized trading that makes data trades in pretty much everything at a very fast rate External memory vs internal memory - Answer- Internal is small, fast, and convenient External is larger, slower, and not as convenient 4 Common features to all data management systems (both individual and organizational) - Answer- 1. there is a storage medium
- there is a structure for storing data
- the storage device is organized for rapid data entry and retrieval
- the selection of data management system frequently requires a tradeoff decision what is a table? - Answer- a common structure for storing data what is the key goal to nearly all data management systems? - Answer- rapid data access, but it always comes at a price what is TPS? - Answer- a transaction processing system that handles common business tasks such as accounting, inventory, purchasing, and sales NAME THAT ACRONYM
- BI - Answer- - Transaction Processing System
- Management Information Systems
- Decision Support System
- Executive Information Systems
- Online Analytical Processing
- Business Intelligence what is DSS? - Answer- a decision support system that supports managerial decision making by providing models for processing and analyzing data what is EIS? - Answer- an executive information system that provides senior management with information necessary to monitor organizational performance and develop and implement strategies what is OLAP? - Answer- an online analytical process that presents a multidimensional logical view of data what is data mining? - Answer- use statistical analysis and artificial intelligence techniques to identify hidden relationships in data what is BI? - Answer- business intelligence used to gather, store, and analyze data to improve decision making The information systems cycle - Answer- 1. the organization is process by TPSs
- data collected by TPSs are stored in databases
- these data are converted into information by analysts using a variety of software
- these technologies are used by the organization to prepare for the future
- the business systems created to prepare for the future determine the transactions the company will process and the data will be collected
- the process continues... 6 Desirable Attributes of Data - Answer- 1. Sharable (readily accessed by more than one person at a time)
- Transportable (easily moved to a decision maker)
- Secure (protected from destruction and unauthorized use)
- Accurate (reliable, precise records)
- Timely (current and up-to-date)
- Relevant (appropriate to the decision) 6 Components of Organizational Memory read details in book - Answer- 1. People/conversations
- Tables/Documents 3.Videos/Images/Graphics/Multimedia
- Models
- Knowledge/Decisions
- Specialized Memories
- Class Information Hardness - Answer- the subjective measure of the accuracy and reliability of an item of information (think of the mineral scale of hardness as a comparison) Information Richness - Answer- describes the concept that information can be rich or lean depending n the information delivery medium
- richest when delivered face to face; conversation permits immediate feedback, body language, tone, natural language
- leanest would be a numeric document (no opportunity for questions, etc) Information Class - Answer- groups types of information based on their key features 4 Types of Information Classes - Answer- 1. Content (quantity, location, and types of items)
- Form (shape and composition of an object)
- Simulation of a physical object)
- Action (creation of action) 3 parts to organizational change - Answer- 1. Goal setting (planning, benchmarking)
- Gap information (problem identification, scorekeeping)
- Change (problem solution) 3 Ways information is a means of change - Answer- 1. Marketing
- Customer Service
- Empowerment 2 ways demand can vary read more about this, Ch2 - Answer- 1. with hardness of information
- with responsibilities 2 Types of Knowledge - Answer- 1. Explicit (codified and transferable)
- Tacit (personal, experience judgment; difficult to codify and transfer) what is information integration - Answer- - consolidation of disparate systems
- comprehensive view for managers
- integrated interface to organizations data warehouse Codd's Relational Model - Answer- - is analogous to a table or a set
- a collection of related files is a "database"
- files contain "records"
- each record contains the "data" for one instance of data the file stores
- records have "fields" that store the fine details of each instance
- fields are composed of "characters"
- characters have "bytes" as a unite of storage sufficient to store a single letter or digit
- each byte of a "bit" what is a data model? - Answer- a graphical description of the components of a database what is DDL? - Answer- Data Definition Language used to describe a database what is DML? - Answer- Data Manipulation Language used to maintain and query a database what is a relation? - Answer- a two dimensional table arranged in rows and columns what a relational database? - Answer- a collection of relations lol what is a primary key? - Answer- the way each row must be uniquely identified, in a relational database what is SQL? - Answer- Structured Query Language what is an entity? - Answer- - something in the environment we want to keep track of
- represented by a rectangle
- an instance is a particular occurrence of an entity what is an attribute? - Answer- - a discrete data element that describers an entity
- must be unique with the data model
- names must be meaningful what is an identifier? - Answer- - what uniquely identifies an instance of an entity
- can be an attribute or a collection of attributes
- can be created if there is no obvious attribute
- denoted by a leading * Using the "CREATE" command to establish a table and its primary key - Answer- CREATE TABLE share ( shrcode CHAR( 3), shrfirm VARCHAR( 20) NOT NULL, shrprice DECIMAL( 6,2), shrqty DECIMAL( 8), shrdiv DECIMAL( 5,2), shrpe DECIMAL( 2), PRIMARY KEY (shrcode)); Watson, Richard (2013-12-27). Data Management (Kindle Locations 1474-1479). eGreen Press. Kindle Edition.
"list the name, price quantity, and dividend of each firm where the number of shares held is at least 100,000" - Answer- - choosing rows and columns SELECT shrfirm, shrprice, shrqty, shrdiv FROM share WHERE shrqty > = 100000; Watson, Richard (2013-12-27). Data Management (Kindle Locations 1777-1778). eGreen Press. Kindle Edition. Primary Key Retrieval "display the firm whose code is AR" "report firms with a dividend of 2.50" - Answer- - a query using the primary key return at most one row "display the firm whose code is AR" SELECT * FROM share WHERE shrcode = 'AR'; Watson, Richard (2013-12-27). Data Management (Kindle Locations 1808-1809). eGreen Press. Kindle Edition. "report firms with a dividend of 2.50" SELECT * FROM share WHERE shrdiv = 2.5; Watson, Richard (2013-12-27). Data Management (Kindle Location 1819). eGreen Press. Kindle Edition. the WHERE clause "find all firms where the PE is 12 or higher and the share holding is less than 100,000" - Answer- - can contain AND or OR SELECT * FROM share WHERE shrpe > = 12 AND shrqty < 10000;
Watson, Richard (2013-12-27). Data Management (Kindle Locations 1792-1793). eGreen Press. Kindle Edition. using the IN command "report data on firms with codes FC, AR, SLG" - Answer- - used with a list to specify a set of values SELECT * FROM share WHERE shrcode IN (' FC',' AR',' SLG'); ^ is EQUIVALENT to > SELECT * FROM share WHERE shrcode = 'FC' or shrcode = 'AR' or shrcode = 'SLG'; Watson, Richard (2013-12-27). Data Management (Kindle Location 1834). eGreen Press. Kindle Edition. using the NOT IN command "report all firms other than those with the code CS or PT" - Answer- - used to report instances that do not match any of the values SELECT * FROM share WHERE shrcode NOT IN (' CS',' PT'); ^ is EQUIVALENT to > SELECT * FROM share WHERE shrcode < > 'CS' AND shrcode < > 'PT'; Watson, Richard (2013-12-27). Data Management (Kindle Location 1853). eGreen Press. Kindle Edition. using the ORDER BY clause "List all firms where PE is at least 12. Order the report in descending PE. Where PE ratios are identical, list firms in alphabetical order" - Answer- - specifies the row order in a report
Watson, Richard (2013-12-27). Data Management (Kindle Location 1991). eGreen Press. Kindle Edition. Built in SUM, MIN, and MAX functions - Answer- - differ in statistic but are used similarly to AVG
- null values in a column are not included Subqueries "report all firms with a PE ratio grater than the average for the portfolio" - Answer- - a query within a query SELECT shrfirm, shrpe FROM share WHERE shrpe > (SELECT AVG( shrpe) FROM share); Watson, Richard (2013-12-27). Data Management (Kindle Locations 2012-2013). eGreen Press. Kindle Edition. using the LIKE command "list all firms with a name starting with 'F'" "list all firms containing 'Ruby' in their name" "find firms with 't' as the third letter of their name" "find firms not containing an 's' in their name" - Answer- "list all firms with a name starting with 'F'" SELECT shrfirm FROM shr WHERE shrfirm LIKE 'F%'; "list all firms containing 'Ruby' in their name" SELECT shrfirm FROM shr WHERE shrfirm LIKE '%Ruby%'; "find firms with 't' as the third letter of their name" SELECT shrfirm FROM shr
WHERE shrfirm LIKE '_ _t%'; "find firms not containing an 's' in their name" SELECT shrifrm FROM shr WHERE shrfirm NOT LIKE '%S%' AND shrfirm NOT LIKE '%s%'; using the DISTINCT command "report the different values of the PE ratio" "find the number of different PE rows" - Answer- - eliminates duplicate rows
- when used with a column function, it ignores duplicate values
- when used before a column name, it prevents the selection of duplicate rows "report the different values of the PE ratio" SELECT DISTINCT shrpe FROM share; Watson, Richard (2013-12-27). Data Management (Kindle Location 2060). eGreen Press. Kindle Edition. "find the number of different PE rows" SELECT COUNT( DISTINCT shrpe) as 'Different PEs' FROM share; Watson, Richard (2013-12-27). Data Management (Kindle Location 2067). eGreen Press. Kindle Edition. Using the BETWEEN command "list the firm name and share PE for those firms whose PE is at least 10 and as much as 20" - Answer- - specifying a range (range includes the value listed) SELECT shrfirm, shrpe FROM shr WHERE shrpe BETWEEN 10 and 20; using the DELETE command
"- a firm can have many divisions, a division belongs to one firm
- a devision can have many departments, a department belongs to one division
- a department can have many sections, but a section belongs to one department" 3 Anomalies you want to avoid slides 4-5 Module 3 - Answer- Insert
- cannot insert a fact about a nation's exchange rate without buying a stock listed with that nation
- separate tables solve this problem Delete
- if delete data about a particular stock, may lose exchange rate data Update
- increase number of updates required, and possibility of error Mapping to a relational database - Answer- - each entity becomes a table
- the entity name becomes the table name
- each attribute becomes a column
- add a column to the table at the many end of a 1:m relationship
- put the identifier of the one end in the added column what is a foreign key? - Answer- a column (or a combo of columns) in one table whose values must match the primary key in the same table, or the primary key in another table, to be null
- the primary key on the ONE side of a relationship should be the foreign key on the MANY side of the relationship
- must be the same data type and length as the primary key what does it mean to qualify attributes? - Answer- - to distinguish between columns with identical names example. NATION.NATCODE and STOCK.NATCODE what is the referential integrity constraint? - Answer- - for every value of a foreign key, there is a primary key with that value
- for every value of 'natcode' in STOCK, there is a value of 'natcode' in NATION
- a primary key must exist before the foreign key can be defined Creating the 1:m table (using Nation and Stock) - Answer- CREATE TABLE nation ( natcode CHAR( 3), natname VARCHAR( 20), exchrate DECIMAL( 9,5),
PRIMARY KEY( natcode)); CREATE TABLE stock ( stkcode CHAR( 3), stkfirm VARCHAR( 20), stkprice DECIMAL( 6,2), stkqty DECIMAL( 8), stkdiv DECIMAL( 5,2), stkpe DECIMAL( 5), natcode CHAR( 3), PRIMARY KEY( stkcode), CONSTRAINT fk_has_nation FOREIGN KEY( natcode) REFERENCES nation( natcode) ON DELETE RESTRICT); Watson, Richard (2013-12-27). Data Management (Kindle Locations 2562-2573). eGreen Press. Kindle Edition. what does the CONSTRAINT clause do? - Answer- - define the column or columns in the table being created that constitute the foreign key what does the ON DELATE clause do? - Answer- - specifies what processing should occur if an attempt is made to delete a row in NATION with a primary key that is a foreign key in STOCK using the JOIN command - Answer- - creates a new table from two existing tables by matching on a common column here 'natcode' is the primary key in Nation and the foreign key in Stock SELECT * FROM stock, nation WHERE stock.natcode = nation.natcode; Watson, Richard (2013-12-27). Data Management (Kindle Locations 2608-2609). eGreen Press. Kindle Edition. what if you join without specifying the relationship between the tables? - Answer- for example. SELECT * FROM stock, nation;
- all columns in the stock and nation table are displayed
- one big clusterf**k Combing join with other SQL commands
- similar to the WHERE clause, except that it applies to rows in the result set that are grouped according to the GROUP by clause SELECT natname, SUM( stkprice* stkqty* exchrate) AS stkvalue FROM stock, nation WHERE stock.natcode = nation.natcode GROUP BY natname HAVING COUNT(*) > = 2; Watson, Richard (2013-12-27). Data Management (Kindle Locations 2869-2871). eGreen Press. Kindle Edition. Subquery vs. Correlated subquery - Answer- - in a "regular" subquery the inner query is excited once SELECT shrfirm,shrpe FROM shr WHERE shape > (SELECT AVG(shrpe) FROM shr);
- in a "correlated" subquery, the inner query is executed many times SELECT natname, stkfirm, stkqty FROM stock, nation WHERE stock.natcode = nation.natode AND stkqty > (SELECT AVG(stkqty) FROM stock WHERE stock.natcode = nation.natcode); Correlated Subquery "find those stocks (by natname, stkfirm, stkqty) where the quantity is greater than the average for that country" - Answer- SELECT natname, stkfirm, stkqty FROM stock, nation WHERE stock.natcode = nation.natcode AND stkqty > (SELECT avg( stkqty) FROM stock WHERE stock.natcode = nation.natcode); Watson, Richard (2013-12-27). Data Management (Kindle Locations 2963-2966). eGreen Press. Kindle Edition. using the VIEW command - Answer- - the ideal situation of not having to run the JOIN query multiple times, but instead running it once and storing it
- only the definition of the view is stored, not the result
- has NO actual data, is just an imaginary table CREATE VIEW stkvalue (nation, firm, price, qty, exchrate, value)
AS SELECT natname, stkfirm, stkprice, stkqty, exchrate, stkprice* stkqty* exchrate FROM stock, nation WHERE stock.natcode = nation.natcode; Watson, Richard (2013-12-27). Data Management (Kindle Locations 2997-3000). eGreen Press. Kindle Edition. many to many relationship - Answer- - cannot be directly represented in a data model
- must create a third entity to map the m:m relationship (called a weak/associative entity)
- put a + by the "crows feet"
- the associative entity will have two foreign keys (the primary keys of the two main entities) what is an identifying relationship? - Answer- used when the entity at the many end of the relationship is a weak entity and needs the identifier of the one end of the relationship creating a m:m rational database - Answer- CREATE TABLE sale ( saleno INTEGER, saledate DATE NOT NULL, saletext VARCHAR( 50), PRIMARY KEY( saleno)); CREATE TABLE item ( itemno INTEGER, itemname VARCHAR( 30) NOT NULL, itemtype CHAR( 1) NOT NULL, itemcolor VARCHAR( 10), PRIMARY KEY( itemno)); CREATE TABLE lineitem ( lineno INTEGER, lineqty INTEGER NOT NULL, lineprice DECIMAL( 7,2) NOT NULL, saleno INTEGER, itemno INTEGER, PRIMARY KEY( lineno, saleno), CONSTRAINT fk_has_sale FOREIGN KEY( saleno) REFERENCES sale( saleno), CONSTRAINT fk_has_item FOREIGN KEY( itemno) REFERENCES item( itemno));
Watson, Richard (2013-12-27). Data Management (Kindle Locations 3569-3572). eGreen Press. Kindle Edition. using the NOT EXISTS command "report all clothing items (type "C") that have not been sold" - Answer- - reports true of the subquery returns no rows SELECT itemname, itemcolor FROM item WHERE itemtype = 'C' AND NOT EXISTS (SELECT * FROM lineitem WHERE item.itemno = lineitem.itemno); ^ is EQUIVALENT to > SELECT itemname, itemcolor FROM item WHERE item type = 'C' AND itemno NOT IN (SELECT itemno FROM lineitem); Watson, Richard (2013-12-27). Data Management (Kindle Locations 3596-3599). eGreen Press. Kindle Edition. using the UNION command "list items that were sold on Jan 16, 2011 or were brown" - Answer- - similiar to OR SELECT itemname FROM item, lineitem, sale WHERE item.itemno = lineitem.itemno AND lineitem.saleno = sale.saleno AND saledate = '2011-01-16' UNION SELECT itemname FROM item WHERE itemcolor = 'Brown'; Watson, Richard (2013-12-27). Data Management (Kindle Locations 3682-3685). eGreen Press. Kindle Edition. using the INTERSECT command "list items that were sold on Jan 16, 2011 and were brown" - Answer- - equivalent to AND SELECT itemname
FROM item, lineitem, sale WHERE item.itemno = lineitem.itemno AND lineitem.saleno = sale.saleno AND saledate = '2011-01-16' INTERSECT SELECT itemname FROM item WHERE itemcolor = 'Brown'; Watson, Richard (2013-12-27). Data Management (Kindle Locations 3696-3700). eGreen Press. Kindle Edition. a one to one relationship, example - Answer- - a dept has one or more employees
- an employee belongs to one department
- a department has one boss
- a person is a boss of only one department
- BOSS has a 1:1 relationship between DEPT and EMP what is a relationship descriptor? - Answer- the label that is associated with a 1: relationship as to avoid confusion where to put the foreign key for a 1:1 relationship? - Answer- put the foreign key in the table that results in the fewest null values
