Data Management and Database Systems using Microsoft Excel

Flashcards covering key concepts in data management, data life cycle, processing cycles, DBMS architectures, ERDs, and database operations in Microsoft Excel.

What is the simple meaning of Data Management?

Getting the right information to the right person at the right time.

What are the seven components of Data Management?

1. Data Collection, 2. Data Organization, 3. Data Storage, 4. Data Protection, 5. Data Processing, 6. Information Output, and 7. Decision Making.

What are the three structure categories of collected data?

Structured (tables and databases), Semi-structured (XML and JSON), and Unstructured (emails, videos, images, and documents).

What are the seven data protection methods?

Authentication, Authorization, Encryption, Backup, Firewalls, Antivirus Software, and Disaster Recovery.

What are the three types of decisions based on organizational levels?

Strategic Decisions (long-term decisions made by top management), Tactical Decisions (medium-term departmental decisions), and Operational Decisions (daily routine decisions).

What are the six characteristics of high-quality data?

Accuracy, Completeness, Consistency, Timeliness, Validity, and Reliability.

What are the six steps of the Data Transformation Process?

1. Collect Data, 2. Validate Data, 3. Store Data, 4. Analyze Data, 5. Generate Reports, and 6. Business Decisions.

What are the six stages of the Data Processing Cycle (DPC)?

1. Data Collection, 2. Data Input, 3. Data Processing, 4. Data Storage, 5. Data Output, and 6. Decision Making.

What are the seven phases of the Data Life Cycle in order?

1. Create, 2. Collect, 3. Store, 4. Process, 5. Share, 6. Archive, and 7. Destroy.

What are the three methods for data destruction in the Data Life Cycle?

Secure deletion, Shredding, and Media destruction.

What four traditional filing methods are used in data organization?

Alphabetical filing, Numerical filing, Chronological filing, and Subject filing.

What are the Five Vs of Big Data?

Volume (amount of data), Velocity (speed of generation and processing), Variety (different forms and types), Veracity (accuracy and reliability), and Value (useful benefits obtained).

What are the five components of a Database System?

1. Data, 2. Hardware, 3. Software, 4. Users, and 5. Procedures.

What are the five components of a Database Management System (DBMS)?

Database Engine, Query Processor, Data Dictionary, Security Manager, and Report Generator.

How do One-Tier, Two-Tier, and Three-Tier database architectures differ?

One-Tier stores everything on one computer; Two-Tier connects clients directly to the database server; Three-Tier connects users through an application, which then connects to the database server.

What is the difference between a Strong Entity and a Weak Entity in an Entity-Relationship Diagram (ERD)?

A Strong Entity has its own unique identifier (Primary Key) and can exist independently, whereas a Weak Entity depends on another entity and cannot exist independently.

What are the five types of attributes in an ERD?

Simple Attribute, Composite Attribute, Single-Valued Attribute, Multi-Valued Attribute, and Derived Attribute.

What ERD symbols correspond to Entity, Attribute, Relationship, and Primary Key?

Rectangle represents an Entity, Oval represents an Attribute, Diamond represents a Relationship, and an Underlined Attribute represents a Primary Key.

What is the function of a Primary Key compared to a Foreign Key?

A Primary Key (PK) uniquely identifies a record, while a Foreign Key (FK) connects one table to another.

How do database concepts map to Excel database terminology?

Database = Workbook, Table = Excel Table, Record = Row, Field = Column, Attribute = Column Heading, Primary Key = Unique Identifier Column.

What steps are involved in removing duplicate records in Microsoft Excel?

1. Select the table, 2. Go to Data, 3. Click Remove Duplicates, 4. Select the columns to check, 5. Click OK.

What Excel formula counts records in range $D2:D50$ that match the department "HR"?

$$=\text{COUNTIF}(D2:D50, \text{"HR"})$$

What Excel formula sums values in range $F2:F50$ for records where range $D2:D50$ equals "Accounting"?

$$=\text{SUMIF}(D2:D50, \text{"Accounting"}, F2:F50)$$

What Excel formula syntax tests if cell $F2$ is greater than or equal to $50000$, returning "High Salary" or "Regular Salary"?

$$=\text{IF}(F2 \ge 50000, \text{"High Salary"}, \text{"Regular Salary"})$$

What formula in Excel retrieves matching department information from another sheet using cell $A2$?

$$=\text{XLOOKUP}(A2, \text{Department!A:A}, \text{Department!B:B})$$