Comprehensive Database Management System and Data Modeling Concepts

1. Know the disadvantages and advantages of DBMS.

Advantages: centralized integrated view; reduced redundancy/anomalies; improved sharing/concurrency; centralized security, integrity, backup/recovery; metadata and ad‑hoc SQL queries. Disadvantages: higher cost; administrative complexity; need for skilled DBAs; vendor dependence; possible performance overhead.

1-3a Role and Advantages of DBMS

Role: intermediary that manages database structures, access, transactions, integrity, security, and metadata; provides query/transaction services and an integrated view of data.

2. What is the column's range of permissible values called?

Domain.

3. What is the row's range of permissible values called?

A row (tuple) must conform to each column's domains and the table's integrity constraints; no separate global name beyond tuple validity by attribute domains.

4. What is the practical significance of taking the logical view of a database?

It abstracts physical storage so designers/users work with logical tables/relationships, simplifying design and separating logical requirements from physical implementation.

5. What is a data warehouse (definition, abilities, structure use, etc.)?

A centralized repository for integrated, historical data from many sources optimized for decision support/OLAP and large‑scale analytics; structures favor query/report performance (may use normalized or denormalized designs).

6. What are the advantages and disadvantages of database systems compared to its predecessor?

Advantages vs file systems: centralized control, reduced redundancy, stronger integrity/security, easier sharing, ad‑hoc queries, metadata. Disadvantages: higher cost, complexity, need for DBAs, migration issues, potential join/performance overhead.

7. What is a field?

A named data element (character or group of characters) that stores a single piece of information in a record (maps to a column).

8. What are standards in terms of database administration?

Documented conventions, naming rules, policies, procedures, and controls ensuring consistent design, security, performance, and operations across the DB environment.

9. What is normalization?

Process of decomposing table structures to minimize redundancy and eliminate insertion/update/deletion anomalies by applying normal forms (1NF, 2NF, 3NF, BCNF, 4NF).

10. What is an issue that arises if different versions of the same data appears in different places?

Data inconsistency causing integrity problems and update/insert/delete anomalies.

11. What is a DBMS and its functions?

DBMS: software that creates/manages databases. Functions: storage/retrieval, transaction/concurrency control, integrity enforcement, security/authorization, backup/recovery, indexing/performance tuning, query processing (SQL), metadata management.

12. What is Metadata?

Data about data: descriptions of elements, structures, domains, relationships, constraints, indexes, and access paths stored in the data dictionary/system catalog.

13. What is End-user data?

Business data (raw facts) of interest to users—transactions, names, measurements—distinct from metadata.

14. If the entity has a relationship with itself it is known as what kind of relationship?

Recursive relationship (a unary relationship).

15. What is a desktop database?

A single‑user database on a personal computer that supports one user at a time (e.g., small MS Access files).

16. Know the different kinds of models (entity relationship, relational data, network, hierarchical, and object-oriented).

Hierarchical: upside‑down tree (1:M). Network: graph with multiple parents. Relational: tables/relations, keys, SQL. ER: conceptual graphical entities/attributes/relationships. Object‑oriented: objects with attributes and methods, classes/inheritance (UML).

17. Know what kind of data an XML database supports (Specifically XML data).

Semistructured hierarchical textual data encoded as XML documents (tagged elements/attributes).

18. What is a surrogate key and what type of data is commonly used with it?

System‑generated primary key (commonly numeric, auto‑increment) used when no suitable natural key exists or to simplify joins/indexing.

19. What is the extended entity relationship model?

EER: ER extended with supertypes/subtypes, specialization/generalization, inheritance, subtype discriminators, disjoint/overlap and completeness constraints, and entity clusters.

20. What is a system administrator and what is their responsibilities?

Platform/OS administrator who manages servers, networks, storage, OS patches, backups and ensures infrastructure supporting the DBMS is available and performant; coordinates with DBAs.

21. What is a data administrator and what is their responsibilities?

DA/IRM: strategic role setting data policies/standards, metadata governance, and long‑term planning; controls corporate data resources (computerized and manual) and reports to senior management.

22. What is a database administrator and what is their responsibilities?

DBA: technical specialist managing DBMS installation/configuration, database design/implementation, security, backup/recovery, performance tuning, user management, monitoring, and daily DB operations.

23. Know the different types of keys (foreign, candidate, primary, superkey, etc.).

Superkey: attributes uniquely identifying rows. Candidate key: minimal superkey. Primary key: chosen candidate (unique, non‑null). Composite key: multiple attributes. Foreign key: references PK in another table. Secondary key: for retrieval/indexing.

24. What is a specialization hierarchy?

Arrangement of a supertype and its subtypes ("is‑a" hierarchy) where subtypes inherit supertype attributes/PK; supports multi‑level specialization and constraints (disjoint/overlap, completeness).

25. What is a constraint?

Rule restricting allowed data values or relationships (domain, NOT NULL, UNIQUE, referential integrity, cardinality, disjoint/overlap, completeness).

26. What is an attribute (definition and being able to identify the different types)?

Characteristic of an entity (maps to a column). Types: simple vs composite; single‑valued vs multivalued; stored vs derived; required vs optional; identifier/key attribute.

27. In terms of when developing a data administration strategy, what is an access plan, information systems architecture, and security policy?

Access plan: who can access which data and how (privileges, roles, views). ISA: blueprint aligning strategic goals with data/apps/infrastructure. Security policy: standards/procedures to ensure confidentiality, integrity, availability, auditing, compliance.

28. Know the difference between a many-to-many, one-to-many, many-to-one, and one-to-one relationships.

1:M: one instance of A relates to many of B. M:1: reverse perspective. M:N: multiple instances on both sides—implemented via associative entity (two 1:M). 1:1: each A relates to at most one B and vice versa.

29. Define method in terms of object-oriented.

Method: an operation/behavior defined for an object/class that acts on the object's data (attributes).

30. What is a data anomaly?

Problems caused by redundancy: update anomaly (inconsistent updates), insertion anomaly (can't add data without other data), deletion anomaly (deleting causes unintended loss).

31. Know the levels of database normalization (1NF, 2NF, 3NF, 4NF, and BCNF).

1NF: table format, PK, no repeating groups. 2NF: 1NF + no partial dependencies. 3NF: 2NF + no transitive dependencies. BCNF: every determinant is a candidate key. 4NF: 3NF + no independent multivalued dependencies.

32. Know what the DBA's managerial role is.

Enforce policies/standards, provide user support, plan/test backups and disaster recovery, ensure security/privacy/integrity, control data distribution, audit compliance, coordinate governance.

33. What is an entity in terms of data models?

Person, place, thing, or event about which data are collected; maps to a table (entity set) with instances as rows.

34. Know how rows are related in the SQL-based relational database.

Rows relate by common attribute values (foreign keys referencing primary keys) and are combined via joins in queries.

35. Define file, schema, and column.

File: collection of related records (analogous to a table). Schema: formal definition of DB structure (tables, columns, constraints, relationships). Column: table attribute representing a property (maps to a field).

36. How do columns relate to attributes in tables?

Each table column represents an attribute; columns are named and constrained by an attribute domain.

37. How is data organization determined within folders in a manual file system?

By expected use of the data—how users will retrieve and process records.

38. Know what an entity-relationship diagram is and the steps to build it.

ERD: graphical model of entities, attributes, relationships. Steps: create narrative; identify business rules; extract entities/relationships; draft ERD; add attributes/PKs; specify cardinality/participation; validate and refine.

39. Know what Crow's Foot notation ERDs are.

Notation using a three‑pronged "crow's foot" symbol to show the "many" side of a relationship; commonly used to express cardinality.

40. In terms of decision-making support types, know what operational, tactical, and strategic is.

Operational: daily transactions/short‑term operations. Tactical: middle‑management planning and monitoring. Strategic: top‑management long‑term planning and competitive decisions (BI/data warehouse).

41. What is a single-valued attribute and multivalued attribute?

Single‑valued: one value per instance (e.g., serial number). Multivalued: multiple values possible for one instance (e.g., certifications); model as separate entity/attributes.

42. What is a required attribute and an optional attribute?

Required: must have a value (NOT NULL). Optional: may be left empty (nullable).