Your company sells toys around the world. You are developing an EC system supported by an RDBMS and write the following SQL code to create a sales order:
01 Begin Transaction
02 Insert Orders(Id, CustomerId, OrderDate) Values(1, 1, ‘2020/08/15’);
03 Insert OrderItems(Id, OrderId, ProductId, Quantity, Price) Values(1, 1, 1, 1, 9.9);
04 Commit Transaction
Which of the following best describes the primary purpose of the transaction depicted in the SQL code? (Source: Wentz QOTD)
A. To avoid orphan records
B. To enforce semantic integrity
C. To implement the third normal form (3NF) of the data model
D. To create a foreign key constraint to enforce referential integrity
Kindly be reminded that the suggested answer is for your reference only. It doesn’t matter whether you have the right or wrong answer. What really matters is your reasoning process and justifications.
My suggested answer is A. To avoid orphan records.
There are three types of integrity in a relational database:
- Entity integrity means each record stored in a table shall be uniquely identified by the primary key, a unique field, or attribute. Entity integrity can be enforced through the assignment of a primary key when designing the database. That is, creating the primary key constraint.
- Semantic integrity means each value of a cell (field/attribute) should be meaningful to people. The data type and the range/domain of an attribute are typical concerns of semantic integrity.
- Referential integrity means the value of the foreign key in a detail or child table shall exist in the primary key in the master or parent table. If it doesn’t, the record in the child table is an orphan record, a typical breach of referential integrity. Referential integrity can be enforced by creating the foreign key constraint at design time and employing transactions at the runtime.
Database integrity can be enforced by creating constraints as part of the database schema at the design time. However, it can also be enforced at the runtime, e.g., input validation and transactions.
- Database normalization is a “design work” that depends on a set of principles. It’s not mandatory. So is creating constraints. The SQL code is executed to process data at runtime. It doesn’t implement the design work of normalization or doesn’t create foreign key constraints at runtime (this is typically a design work).
- A database without normalization can be a poor design; an over-normalized database may hinder performance.
- The SQL code doesn’t check data types or domain values. It just inserts records to tables enclosed in a transaction, which prevents orphan records.
The database schema of a database is its structure described in a formal language supported by the database management system (DBMS). The term “schema” refers to the organization of data as a blueprint of how the database is constructed (divided into database tables in the case of relational databases). The formal definition of a database schema is a set of formulas (sentences) called integrity constraints imposed on a database. These integrity constraints ensure compatibility between parts of the schema.
In computer science, ACID (atomicity, consistency, isolation, durability) is a set of properties of database transactions intended to guarantee data validity despite errors, power failures, and other mishaps.
Database normalization is the process of structuring a relational database [clarification needed] in accordance with a series of so-called normal forms in order to reduce data redundancy and improve data integrity. It was first proposed by Edgar F. Codd as part of his relational model.
Normalization entails organizing the columns (attributes) and tables (relations) of a database to ensure that their dependencies are properly enforced by database integrity constraints. It is accomplished by applying some formal rules either by a process of synthesis (creating a new database design) or decomposition (improving an existing database design).
Third Normal Form (3NF)
Third normal form (3NF) is a database schema design approach for relational databases which uses normalizing principles to reduce the duplication of data, avoid data anomalies, ensure referential integrity, and simplify data management. It was defined in 1971 by Edgar F. Codd, an English computer scientist who invented the relational model for database management.
A database relation (e.g. a database table) is said to meet third normal form standards if all the attributes (e.g. database columns) are functionally dependent on solely the primary key. Codd defined this as a relation in second normal form where all non-prime attributes depend only on the candidate keys and do not have a transitive dependency on another key.
- Database schema
- Chapter 9 Integrity Rules and Constraints
- Database normalization
- Third normal form
- Orphan Records
- What is an Orphaned Record?
Insert Orders(Id, CustomerId, OrderDate) Values(1, 1, ‘2020/08/15’);
Insert OrderItems(Id, OrderId, ProductId, Quantity, Price) Values(1, 1, 1, 1, 9.9);
以下哪一項最能描述您開發這段SQL交易代碼的目的？ （來源：Wentz QOTD）
A. 避免孤立記錄 (orphan records)
B. 強化語義完整性 (semantic integrity)
A BLUEPRINT FOR YOUR SUCCESS IN CISSP
My new book, The Effective CISSP: Security and Risk Management, helps CISSP aspirants build a solid conceptual security model. It is not only a tutorial for information security but also a study guide for the CISSP exam and informative reference for security professionals.