ISM 4212 Exam 1
Relational Database Management System
A DBMS that manages data as a collection of tables in which all relationships are represented by common values in related tables
Simple Primary Keys
A single attribute
Structured Query Language (SQL)
A special-purpose programming language designed for managing data in RDBMS
INSERT Statement
Add a full row to a table OR Add a partial row to a table
CREATE Statement
Add a table to a database
Which of the following is NOT a problem with keeping data?
Automation
Prototyping
Brief attempt at conceptual data modeling Define database during development of initial prototype Repeat implementation and maintenance activities with new prototype versions
Components of the Environment
CASE tools Repository DBMS Database Application User Interface Database Administrations System Developers End Users
Databases Provide:
Central repository of shared data Data stored in a standardized, convenient form
Repository
Centralized storehouse of metadata
UPDATE Statement
Change the value of a field of data
A good business rule is all of the following EXCEPT: Precise Checked Distinct Declarative
Checked
Data Control Language (DCL)
Commands that control a database, including administering privileges and committing data
Data Definition Language (DDL)
Commands that define a database, including creating, altering, and dropping tables and establishing constraints
Data Manipulation Language (DML)
Commands that maintain and query a database
CASE Tools
Computer aided software engineering Automates applications
Relationships
Connections between entities EX: customer places an order Used to retrieve desired information from multiple entities
Referential Integrity
Constraint that states that foreign key values of a table must match primary key values of a related table For referential integrity to hold, any field in a table that is declared a foreign key can contain only values from a parent table's primary key or candidate key
The DIKW triangle includes which of the following?
Data Knowledge Information Wisdom
Types of Structured Query Language
Data Definition Language (DDL) Data Manipulation Language (DML) Data Control Language (DCL)
Data Integrity Problem
Data changes in one file could cause inconsistencies in other files
Information
Data processed to increase knowledge in the user Data in context
Metadata
Data that describes the properties and context of user data Data about data EX: datatype
SELECT Statement
Data you want to be returned from the query OR More specific data you want to be returned from the query
Relational Databases
Database technology involving tables that represent entities and primary/foreign keys that represent relationships Saves data in table formats Uses SQL as standard language What we are working with** Most widely used
DROP Statement
Delete a table from a database
DELETE Statement
Delete all rows from a table OR Delete certain rows from a table
Attributes
Descriptions of the entity EX: for a customer: CustomerID, Address, Phone #
When querying a dimensional database, a user went from summarized data to its underlying details. The function that served this purpose is
Drill down
Why use a database?
Duplicate Data problem Data Integrity problem
The three schema architecture includes
External schema, conceptual schema, and internal schema An approach to building information systems and systems information management that originated in the 1970s
Just having data is a good enough reason to need a database.
False
T/F Computerized support is only used for organizational decisions that are responses to external pressures, not for taking advantage of opportunities
False
T/F Successful BI is a tool for the information systems department, but is not exposed to the larger organization
False
T/F: The data names assigned to entities, attributes and relationships do not really matter.
False
T/f Decision support system (DSS) and management information system (MIS) have precise definitions agreed to by practitioners
False
Data Models
Graphical system capturing nature and relationship of data
How does the use of cloud computing affect the scalability of data warehouse?
Hardware resources are dynamically allocated as use increases.
Foreign Keys
Identifiers that enable one table to refer to another table Primary key from one table becomes the foreign key of the other
Unstructured Data
Images, video, documents
Knowledge
Information put into action What we know Declarative (what we know), Procedural (how to do something), Tacit (difficult to transfer to another person), Explicit (readily articulated, codified, accessed and verbalized)
OLTP (Online Transaction Processing) systems are designed/optimized for
Inputs like inserts, updates, deletes in the database
Data Warehouse
Integrated decision support system derived from various operational databases
Key performance indicators (KPIs) are metrics typically used to measure
Internal results
A database schema can be described by all of the following EXCEPT: It's the structure that contains descriptions of the objects created by the user. It's a special purpose programming language. It's the organization of data. It's stored in the data dictionary.
It's a special purpose programming language.
Database system development methods
Life cycle Prototyping Agile
Within a database, an entity will have which of the following: Many instances Output Consensus Multiple attributes
Many instances Multiple Attributes
Agile Development
Methods based on iterative, incremental development Requirements and solutions evolve through collaboration Encourages rapid and flexible response to change
Composite Primary Keys
More than one attribute
Select all of the examples of bad table names listed below: Student Name 4DJB22 Payroll_Report
Name 4DJB22 Payroll_Report
Category: Boolean
Name: Boolean Description: Stores truth values (true, false) EX: Boolean
Category: Text
Name: Char(n) Description: stores string/text characters; fixed length EX: Char(2) Name: Varchar(n) Description: stores string/text characters; fixed length EX: Varchar(255)
Category: Temporal
Name: Date Description: Stores year, month, and day values EX: Date
Category: Number
Name: Integer Description: stores string/text characters; variable length EX: Integer Name: Decimal (p,s) Description: Stores exact numbers; p= precision, s= scale EX: decimal(5,2) 432.10
Structured Data
Numbers, text, dates
Types of Relationships
One to one One to many One to none
Database
Organized collection of logically related data
End Users
People who use applications / data
Database Applications
Personal Two Tier Three Tier Enterprise Resource Planning Data Warehousing
System Developers
Personnel responsible for implementing databases
Database Administrators
Personnel responsible for managing database
SDLC
Phases: Planning, Analysis, Logical Design, Physical Design, Implementation and Maintenance Time consumer, but comprehensive
Application
Programs- software using the data
A constraint that states that the foreign key values of a table must match the primary key values of a related table is called:
Referential Integrity
Database Development Approaches
SDLC- Systems Development Life Cycle Prototyping Agile Development
Basic Select Statement:
Select ---> Column(s) From ---> Table Where ---> Row
Entities
Singular noun describing a person, place, object, event, or concept EX: Student, customer, order, part Composed of attributes
Why we need a database?
Size of data Ease of updating Accuracy Security Redundancy Importance
DBMS
Software for managing database
Database Management System (DBMS)
Software packages that are used to manipulate a database Used to create, maintain, and provide controlled access to user databases Manages data resources like an operating system manages hardware resources
Steps in Table Creation
Starting from a relation: 1. Identify columns that are unique (candidate keys) 2. Select the primary key 3. Identify data types for attributes 4. Identify columns that can/cannot be null 5. Identify primary key key- foreign key mates 6. Create the table
Data
Stored representations of meaningful objects and events Facts and Figures
Database
Storehouse of data
User Interface
Text/graphical displays to users
Wisdom
The knowledge and experience needed to make sensible decisions
What caused the growth of the demand for instant, on-demand access to dispersed information?
The more pressing need to close the gap between the operational data and strategic objectives
Constraints
The number of instanced of one entity that can or must be associated with each instance of another entity
A database management system is a software system that is used to create, maintain, and provide controlled access to user databases
True
T/F During the early days of analytics, data was often obtained from the domain experts using manual processes to build mathematical or knowledge-based models
True
T/F In the 2000s, the DW-driven DSSs began to be called BI systems
True
T/F Interval data are variables that can be measured on interval scales
True
T/F Managing data warehouses requires special methods, including parallel computing and/or Hadoop/Spark
True
T/F Traditional BI systems use a large volume of static data that has been extracted, cleansed, and loaded into a data warehouse to produce reports and analyses
True
T/F in SQL, the UPDATE statement modifies data in an existing row(s)
True
T/F: A data definition is a type of business rule that provides a description of the data
True
T/F: A database management system is a software system that is used to create, maintain, and provide controlled access to user databases
True
T/F: An attribute corresponds to a field in a table
True
T/F: An attribute is a property or characteristic of an entity
True
T/F: An entity instance is a single example of an entity
True
T/F: In an SQL statement, you use the AND, OR and NOT operators for customizing conditions in the WHERE clause.
True
T/F: Relationship cardinality specifies how many of each entity type is allowed in a relationship
True
T/F: The CREATE TABLE SQL statement defines a new table and its columns.
True
The reason we need a database is to allow the data to grow and still be manageable.
True
Personal Database Application
Typical # of Users: 1 Typical Size of Database: Megabytes
Three-Tier Database Application
Typical # of Users: 100-1,000 Typical Size of Database: Gigabytes
Two-Tier Database Application
Typical # of Users: 5-100 Typical Size of Database: Megabytes-Gigabytes
Enterprise Resource Planning
Typical # of Users: >100 Typical Size of Database: Gigabytes-Terabytes
Data Warehousing
Typical # of Users: >100 Typical Size of Database: Terabytes-Petabytes
Primary Keys
Unique identifiers of the table How we can guarantee that all instances are unique No primary key attribute can be null. All primary key fields must have data.
Duplicate Data Problem
Waste of space housing same information in multiple locations
Why is a performance management system superior to a performance measurement system?
because measurement alone has little use without action
A relational database management system involves:
entities and relationships between those entities
Data warehouses provide direct and indirect benefits to organizations. Which of the following is an indirect benefit of data warehouses?
improved customer service
information put into action; what we know (Declarative, Procedural, Tacit, Explicit)
knowledge
database
organized collection of logically related data. software packages that are used to manipulate a database
wisdom
the knowledge and experience needed to make sensible decisions -values, virtues, vision
Big Data often involves a form of distributed storage and processing using Hadoop and MapReduce. One reason for this is
the processing power needed for the centralized model would overload a single computer.
Benefits of the latest visual analytics tools, such as SAS Visual Analytics, include all of the following EXCEPT
they explore massive amounts of data in hours, not days