Day 4 - Subject 1 Wrap-Up

📌 Key Takeaways

• Subject 1 is 10 questions worth 20 points, and scoring below 8 points (4 questions) is a subject failure even if the total exceeds 60 points.
• The modeling stages are conceptual, logical, physical, and the three-level schema is external, conceptual, internal. Logical independence withstands changes to the conceptual schema, and physical independence withstands changes to the internal schema.
• By tangibility, entities are divided into tangible, conceptual, event, and by time of occurrence into fundamental, main, transaction. The two axes do not correspond to each other.
• Normalization checks ask in this order: "Is it an atomic value → Does it depend on part of the key → Do ordinary attributes depend on each other → Is there a determinant that is not a candidate key?"
• Denormalization follows the order investigate → consider other methods → apply, and a surrogate identifier simplifies the key at the cost of duplicates and an extra index.

How Subject 1 Decides Pass or Fail

In SQLD, Subject 1 is 10 of the 50 questions (20 points). Its weight is small, but scoring below 40% in any subject is a failure, so if you cannot get 8 points (4 questions) in Subject 1, you fail regardless of your Subject 2 score (source: the exam information in this track's README, confirmed at dataq.or.kr on 2026-11-03).

• The scope of Subject 1 is the ten notes of W1 (modeling, entities, attributes, relationships) and W2 (identifiers, normalization, denormalization, joins and transactions, NULL, surrogate identifiers).
• Below, rather than listing that content again, we organize it by the pairs that are easily swapped in answer choices and by the order of judgment.

The Nature of Modeling: Four Lists Grouped in Threes

W1 Day 1 has four lists that are grouped into "three things." Answer choices that mix up the group name and the elements are wrong.

• The three elements of a data model are entities (Things), attributes (Attributes), and relationships (Relationships).
• The names for an individual unit are also asked as pairs. The individual of an entity is an instance (occurrence), the individual of an attribute is an attribute value, and the individual of a relationship is a pairing.

The Three Modeling Stages and the Three-Level Schema

Both concepts are three boxes going "from abstract to concrete," so they work well together in one question. They differ from the start in what they divide.

Modeling stages                     Three-level schema
Conceptual  core entities,          External  per-user/application
            relationships, EA                 views (several)
  ↓                                   │  external/conceptual mapping → logical independence
Logical     keys, attributes,       Conceptual  organization-wide
            relationships fixed,                integrated structure (one)
            normalization             │  conceptual/internal mapping → physical independence
  ↓
Physical    tables, types,          Internal  storage structure,
            indexes, performance              indexes (one)

• "Logical schema" and "external modeling" are phrases made by mixing the two lists. The modeling stages have no external, and the schema has no logical.
• Normalization is the work of logical modeling, while performance, storage methods, and denormalization review are the work of physical modeling.

Independence is judged by "what changes, and what is protected."

⚠️ Pitfall: An answer choice that begins "Logical independence = even if the external schema changes…" is wrong. What changes in logical independence is the conceptual schema, and the external schema is the side that is protected.

Entities: Six Conditions and Two Classification Axes

Conditions for Being an Entity

Flipping the six characteristics of W1 Day 3 into "if even one is missing, it is not an entity" makes judging fast.

• Exceptions to the relationship condition are statistical or code-like entities and internal entities for system processing (such as transaction logs). The relationships are sometimes omitted from the drawing.
• An entity that no process uses is read as a sign that processes were left out of the business analysis.

Two Classification Axes

It is often shortened to "tangible/intangible," but this track (W1 Day 3) divides into three, tangible, conceptual, event, as in the textbook. What falls under intangible is conceptual and event.

• One entity gets a name on each of the two axes. Order item is an event and also a transaction entity, and membership grade is conceptual and also fundamental.
• An answer choice that chains the axes, such as "event entity = main entity," is wrong.
• Transaction entities often reveal themselves at the detailed design stage or at the stage of interaction modeling with processes.
• Names should use business terms, no abbreviations, and a singular noun, and be unique across the whole model.

Attributes: Three Classification Axes and Domains

An attribute is the smallest unit that cannot be divided further in meaning, and it holds only one attribute value per instance (W1 Day 4).

• "Single attribute" and "single-valued attribute" are on different axes. The former asks whether it can be divided, and the latter asks how many values it has.
• A multivalued attribute is separated into its own entity. This judgment leads to 1NF.
• A derived attribute gets out of sync if it is not updated together when its source changes. So define as few as possible.
• The same customer number is a PK in the customer entity and an FK attribute in the order entity. Which entity you view it from decides the classification.

A domain is the range of values an attribute can have, and it contains the data type, size, and constraints together. For example, order date, delivery date, and payment date share one "date" domain, and in the physical model it is implemented with the column type and CHECK and NOT NULL.

Relationships: Types, Notation Elements, and Two Notations

• The three notation elements are the relationship name (Membership), the relationship cardinality (Cardinality), and the relationship optionality (Optionality).
• Relationship names avoid vague verbs ("is related") and are written in the present tense. One is attached at each end.
• M:N cannot be implemented directly in a relational DB, so it is resolved into two 1:M relationships with an intersection entity (order item).
• The reading order is the reference entity ("each") → the counterpart count → optionality ("always/sometimes") → the relationship name.

IE      Customer ┼──────────O< Order    The optionality symbols (O, |) are attached to the opposite end
Barker  Customer - - - -─────< Order    Optionality is a solid (mandatory) or dashed (optional) line on one's own half

• Even for the same relationship, the circle O in IE is at the target-side end, while the dashed line in Barker is on the reference-side half.
• Barker puts # (identifier), * (mandatory), and o (optional) in front of attributes.

Identifiers: Four Classification Criteria and Primary Identifier Properties

Using the EMP example from W2 Day 1 as is, EMP.EMPNO is a primary, internal, single, natural identifier, and EMP.DEPTNO is an external identifier brought in from DEPT. The national ID number in the employee entity is a secondary identifier that distinguishes rows but is not used for references.

The four properties of a primary identifier are easy to use for separating answer choices if you remember them through violation examples.

• Minimality does not mean "one attribute." For order item, (order number, product number) is the minimum with two.
• When choosing, pick attributes heavily used in lookups, and avoid name attributes such as names and descriptions.

Identifying and Non-Identifying Relationships

• If the parent PK becomes all of the child PK, it is a 1:1 relationship.
• If the relationship is weak, the child key can be made independently, or the child can arise before the parent, consider a non-identifying relationship.

Normalization Judgment Order

Normal forms are decided by the shape of functional dependencies (X → Y, where X is the determinant) (W2 Day 2). Ask the questions below from the top, and the first place a "yes" appears is the violated normal form.

① Does one cell hold several values (multivalued, repeating group)?   Yes → violates 1NF
② Does a regular attribute depend on only "part" of a composite primary identifier?   Yes → violates 2NF (partial functional dependency)
③ Does a regular attribute depend on another regular attribute?   Yes → violates 3NF (transitive functional dependency)
④ Is there a determinant that is not a candidate key?   Yes → violates BCNF
   All no → satisfies BCNF (for 4NF multivalued dependency and 5NF join dependency, only the names and targets)

• If the primary identifier is a single attribute, ② cannot hold. A 2NF violation arises only with a composite primary identifier.
• The BCNF example is solved by splitting into (professor, course) and (student_id, professor).
• Without normalization, insertion, update, and deletion anomalies arise. You cannot insert a product without an order (insertion), fixing only one row makes the names diverge (update), and deleting an order makes the product information disappear (deletion).
• After normalization, insert, update, and delete get better, but queries may get slower because joins increase, or faster because tables get smaller. "Queries necessarily get slower" is an incorrect statement.

Denormalization: Order and Techniques

Denormalization is deliberately introducing duplication into a model that has finished normalization for the sake of query performance (W2 Day 3). Its price is the risk to integrity.

• The two tables produced by a vertical split are in a 1:1 relationship sharing the same primary identifier. A horizontal split is often implemented with partitioning.
• The alternatives in ② create no duplication in the model. That is why we look at them before applying.

Data Model and SQL: Relationships, Transactions, NULL

• A hierarchical data model is a superior-subordinate relationship within one entity. EMP's MGR → EMPNO is an example, and the MGR of the top-level KING is NULL.
• Joins work even without a relationship. EMP and SALGRADE are connected by the salary range (non-equi).
• Entities that are meaningless without each other, like order and order item, are drawn with a mandatory relationship. If wrongly drawn as optional, data with only an order may remain.

NULL is "not determined," so it differs from 0 and blank. The behaviors asked about in Subject 1 are summarized in five.

• Oracle treats NULL as the largest value in sorting and places it last in ascending order.

Natural Identifier vs Surrogate Identifier

• In W2 Day 5, the table with only the ITEM_ID PK accepted the same (1001, 'P1') row twice, while the table with the natural identifier as PK rejected it with ORA-00001.
• To sum up, use the natural identifier as is if it is not complicated, and consider a surrogate identifier when there are too many attributes.

Tomorrow we will organize Subject 2, SQL Basics and Applications, in the same way.

📖 Glossary

• Subject failure : Failing regardless of the total because a subject score is below 40% of its allotted points. For Subject 1, it is below 8 points.
• Data independence : The property that when the schema of one layer changes, the other layers are not affected. There are two kinds, logical and physical.
• Pairing : The connection of one pair of instances participating in a relationship. It is the individual unit of a relationship.
• Transaction entity : An entity that arises from two or more parent entities. Order item is an example.
• Domain : The range of values an attribute can have. It includes type, size, and constraints.
• Determinant : The side X that determines the value in a functional dependency X → Y. BCNF requires every determinant to be a candidate key.
• Vertical split / horizontal split : Denormalization techniques that split a table by column / by row.
• Surrogate identifier : An identifier that does not exist in the business but was created to simplify the key. The natural columns need a separate constraint.