1
Q
What is a data dictionary?
A
- It is something that stores metadata (a set of data that gives info about other data) (Ex of metadata: file size, author, date created) related to properties of fields in a relational database table.
2
Q
What is a database schema?
A
It is a diagram of the underlying structure of the database that shows tables, primary keys, and relationships
3
Q
What is an entity relationship diagram?
A
- It is a database schema that outlines the entities, relationships among entities, and attributes for each entity.
- ERDs do not specify the acceptable range of values for each field.
4
Q
What is SQL?
A
- Structured Query Language
- It can be used to query relational databases
- To query data stored in multiple tables, the tables must be JOINED on a shared attribute (field)
5
Q
What are the 4 types of joins in SQL?
A
- Inner Join
- Left Outer Join
- Right Outer Join
- Full Outer Join
6
Q
What is a FULL OUTER JOIN?
A
It is a join that would retrieve all records from both datasets, regardless of whether there is a match
7
Q
What is an INNER JOIN?
A
- It is a join that would retrieve only those records that match both tables
- The output would list only those who attended a workshop, for example
8
Q
What is a LEFT OUTER JOIN?
A
returns all records from the left table and matched records from the right table
9
Q
What is a RIGHT OUTER JOIN?
A
returns all records from the right table and matched records from the left table
10
Q
What is normal form?
A
The rule is that each normal form adds more rules to the one before, subdividing large tables into smaller tables that are associated with primary and foreign keys
11
Q
What is a check digit?
A
- numbers with no obvious meaning in which 1 of the digits is determined by a formula applied to the rest of the number
- Ex: Bank account number is verified to ensure that the number entered is a valid bank account number
12
Q
What are application controls?
A
They are controls that are specific to individual programs and include input, processing, and output controls
13
Q
What are the 3 types of database schemas?
A
- CONCEPTUAL: Shows the big picture of the database in initial phases of its design. Ex: Entity Relationship Diagram. It shows relationships.
- LOGICAL: Shows the attributes and primary keys that will be included for each table in the database. Does not show relationships.
- PHYSICAL: Shows the technical implementation of the logical model once the software has been determined
14
Q
What are data warehouses?
A
- They are databases designed to collect and store large amounts of historical, structured data from multiple databases
- The primary purpose is to serve as central repositories
- They must be continuously updated with current and relevant data so that decision makers can make accurate decisions
15
Q
What is a data mart?
A
- It is not for broader organization-wide purposes
- It is a smaller version of a data warehouse that is intended for a specific purpose (ex: a department)
16
Q
What are star schemas?
A
- They are schemas that require less storage space because they have fewer tables than snowflake schemas
- This improves performance, as the computer has less to search
- Used for data warehouses and data marts
- They store data in a denormalized form in central fact tables holding numerical data and in associated dimension tables, which provide descriptive context (month of sale) to the fact tables
- This configuration reduces the number of joins required to retrieve data, leading to faster queries.
17
Q
What are snowflake schemas?
A
They are schemas that are best for organizations needing to perform complex queries
18
Q
According to QC 10, A Firm’s System of Quality Control:
A
a firm should accept an engagement only when its staff are COMPETENT to perform the work
19
Q
How are data retrieved from databases?
A
By using queries
20
Q
To meet quality control standards, engagement team members must be competent in what SQL language?
A
Data query language
21
Q
What is a data lake?
A
- It is a data storage architecture that can hold massive amounts of data cheaply, no matter the format
- Data is stored in an original, raw format
- They support multidimensional analysis, like AI and machine learning
22
Q
If an employee’s department appears differently on 2+ tables, what is violated?
A
- Referential integrity
- This enforces a rule that the foreign key in 1 table can refer only to the primary key in another table
23
Q
referential integrity
A
A database design concept where a foreign key in a table points to a primary key in another table, so that any changes in 1 table affect relevant data linked to it
24
Q
What should automatically update all related tables?
A
An ATTRIBUTE CHANGE in 1 table should automatically update all related tables to keep data consistent
25
What is a field check?
- It validates whether data entered is the correct length and format
- Ex: Time card entry is verified to ensure the hours entered are numeric
26
What is a validity check?
- It compares entered data with a list of acceptable entries
- Ex: 2 letter state abbreviation will be checked against a list of acceptable choices
27
What is a reasonableness check?
It ensures that the entered data falls within predetermined limits
28
What does an asterisk do in SQL langauge?
It retrieves all fields from a table
29
What does WHERE clause do in SQL language?
It restricts the output to only those records that satisfy certain criteria
30
What does ORDER BY clause do in SQL language?
It sorts the output in ascending (small to big, or first to last) order by default OR in descending order (big to small, or last to first) by attaching the DESC keyword
31
What is application programming interface?
- It is software that connects internal and external data and systems in real time without human intervention
- Ex: Weather forecast in real time
32
What are the 4 types of constraints?
- Domain (Ex: MM/DD/YY must be entered in a certain format
- Entity
- Referential integrity
- Key
33
What are constraints?
They are PREVENTITIVE controls that restrict the type of data and range of values that can be entered into a field in a relational database table
34
What is a UPC?
- Universal product code
- These digits identify a single retail product and its manufacturer with a check digit
- UPC can be found on a barcode
35
What is electronic data interchange (EDI)?
- EDI is the asynchronous (not happening at the same time) computer to computer exchange of documents among business partners in an electronic format
- The EDI network can only be accessed by authorized organizations, so it is a secure method of transferring B2B data
36
What does the DISTINCT keyword in SQL represent?
It can remove duplicates from the output by being included in the SELECT clause
37
What are the 3 types of data structures?
- Structured Data (Ex: user ID in smartphone app)
- Semi-structured Data (Ex: Tagged post from social media)
- Unstructured Data (Ex: PDFs and video )
38
What is the DISPOSAL PHASE of the data life cycle?
- It includes archive, deletion, and destruction
- In this phase, data is archived when its no longer actively used but must remain accessible just in case
39
What is database normalization?
- It decreases data redundancy by creating tables and relationships that follow rules known as "normal forms"
40
What does AVG function do in SQL?
It calculates the average of a field in a table
41
What does HAVING do in SQL?
It applies criteria at the group level
42
What does WHERE do in SQL?
It applies criteria at the individual record level
43
The correct order of execution of SQL queries
1) From - Specifies the tables from which to extract the data
2) Where - Filters the data
3) Group by - Aggregates the data
4) Having - Filters aggregated data
5) Select - Specifies the fields to be extracted
6) Order by - Sorts the returned data
7) Limit - Restricts the rows returned
44
What is a limit test?
- AKA Reasonableness check or Limit Check
- It's a comparison of data entered with limits that are set
- Ex: Time card entry is verified to make sure that hours entered don't exceed 24 hours in a day
45
To be in the 1NF:
- Each record must have a primary key
- Repeating groups are eliminated
- Each cell must hold a single piece of data
46
To be in the 2NF:
- Database must already be in the 1NF
- Separate tables should be created for sets of values that apply to multiple records
- These tables must be related with a foreign key
47
What is a primary key?
- Think of a row of data in a table, like a house
- The house has all kinds of things in it, like a TV, couch, table, as do all the other houses on the street
- How do I know which house is mine?
- The best way to do this is to give the house a number (a special number that no one else is allowed to use on their house)
- That number is called a PRIMARY KEY
48
What is referential integrity?
- Referential integrity is a term used in database design to describe the relationship between two tables.
- It is important because it ensures that all data in a database remains consistent and up to date.
- It helps to prevent incorrect records from being added, deleted, or modified.
- It makes certain that each foreign key in a table point to a unique primary key value in another table.
- This will ensure that data is not lost, and it will also help you maintain data quality.
49
What is a domain contstraint?
- They restrict the attribute data type (character, date) and may also require the data to be entered in a specific format
- Ex: MM/DD/YYYY instead of M/D/Y
- Input fields may be programmed to automatically reformat the data or notify the user to reformat the data
50
Left JOIN + IS NULL condition =
This can be used to find records in one table that don't exist in another table
51
Union join would:
Return all records from both tables
52
WHERE Vendor ID is NULL condition does what?
It limits the output to only those records for which the VENDOR ID in the PurchaseOrder table doesn't exist as a VendorID in the Vendor table
53
What are the 5 commonly used AGGREGATE FUNCTIONS IN SQL?
COUNT: Returns the number of records in a field
AVERAGE: Computes the average of values in a field
SUM: Adds all the values in a field
MAX: Finds the largest value in a field
MIN: Finds the smallest value in a field
54
What is ROBOTIC PROCESS AUTOMATION?
It is a process that automates manual tasks (data entry) through the use of bots, which replicate the actions or clicks a human would do
55
What does EXTRACT TRANSFORM LOAD focus on?
It focuses on batch processing and consolidating data from different sources into a SINGLE REPOSITORY
56
To be in the 3NF:
All non-prime table attributes depend on the primary key
Ex: A denormalized customer table holds information about invoices. However, the only information that depends on the customer ID is the customer's name and address, so an additional table is needed to separate information that is not dependent on the primary key, achieving the 3NF. The primary key in this example is the customer ID.
57
What is a logical database schema?
- It is a type of database schema
- The logical schema must be completed before implementation because the logical schema is the basis for the physical schema
- The physical schema is then implemented in the database software once the type of database (relational) is known
- Logical schemas show the attributes (fields) and primary keys that will be included for each table in the database
ISC UWorld
flashcards
Decks in class (10)
# Cards
