"Peter Chen (E/R Modeling, 1976) used the term "weak" entity to describe one which could not meaningfully have an independent existence. An example might be an Order which requires a customer (and a Product or set of products). A weak entity need not be a composite, as in your OrderItem example. The central issue here is dependency of one entity [of one] type on another [of another type]. Furthermore, there could be more that one such dependency. In your example, the OrderItem would be dependent on BOTH Order and Item.

It is important to note that this dependency will NOT be enforced by a simple RDBMS unless you also define referential integrity on both parts of the composite key, notwithstanding that some RDBMS's (e.g., SQL Server) couple the definition of the relationship with a referential integrity constraint. In other words, you cannot have a defined relationship without enforcing referential integrity.

This is not always desirable. Consider the task of bulk loading some records which have a relationship with some other entity type(s). The only practical way to do this is to first turn off referential integrity enforcement which means deleting the definition of the relationship entirely. After completing the loading of data, if you reinstate the defined relationship, will the system automatically ensure that referential integrity is not violated? Good question." --Gordon Everest

"In Oracle you can instruct the DBMS to log violating rows into an 'exceptions' table while enabling a constraint (be it, a primary key, unique key, foreign key, or plain check constraint). This fits the bill better, particularly if it's possible to INSERT...SELECT them after corrections." --Toon Koppelaars

"SQL Server will check on re-enabling the constraint that it is not violated by the new state of the database. If it is, it only reports that an error occurred. It has a real time violation catalog to easily map a constraint name to a specific violation and transaction id. In other words, doing bulk batch constraint validation is part of the product." --William Sisson and Racim Boudjakdji

"How to implement“existence dependence” ... between two entities ...is a design decision and is dependent upon the architecture of the implementation technology. [Chen's E/R models] ... were completely technology agnostic and had no “relational” flavor at all. Why? With the rapid emergence and popularity of the NoSQL, NewSQL, Graph Databases, etc. no longer can relational implementation and FK be assumed and we again need to separate the “conceptual data model” from the “logical data model”. Whether this is evolution of de-evolution is a whole other subject (for those of us that remember WHY relational technology emerged in the 1980s--and as a warning to those who would forget their history--IDMS, IMS, etc.)"

The relational model has been so dominant for so long, we have forgotten our roots and the reason that there were originally three layers of data model, not just two. The rise of the post-relational technologies has again required their creation and reminds us of their utility. --David Tryon

• A logical model is implemented in specific hardware and DBMS software, a conceptual model is formalized as a logical model by means of a data model (e.g., the RDM). Using 'implementation' for both induces and reinforces the confusion.
• Emergence, or popularity are not grounds for technology adoption: a superior generality-to-simplicity ratio (G2SR), soundness and flexibility are. 

The query framework, as it exists in modern day MongoDB, supports the following functionality:

• Filtering. The rough equivalent of the WHERE clause in SQL.
• Paging. The rough equivalent of LIMIT and OFFSET in SQL.
• Sorting. The rough equivalent of ORDER BY in SQL.

--John De Goes, MongoDB: The Frankenstein Monster of NoSQL Databases, LinkedIn.com

• First Normal Form (1NF) in Theory and Practice, Part 1
• First Normal Form (1NF) in Theory and Practice, Part 2
• First Normal Form (1NF) in Theory and Practice, Part 3

09/19/23: For the latest on this subject see: FIRST NORMAL FORM - A DEFINITIVE GUIDE

Note: This is a 11/23/17 revision of Part 3 of a three-part series that replaced all of my previous posts on the subject (pages of which redirect here), in order to further tighten integration with the formalization and interpretation [1] of McGoveran's formalization and interpretation [1] of Codd's true RDM.

(Continued from Part 2)

 
"Is this table in 1NF?" is a common question in database practice. On the other hand, "What problems are solved by splitting street addresses into individual columns?", or  What's the best way to store an array in a relational database does not seem to evoke associations with 1NF. This reveals poor foundation knowledge.

Part 1 introduced the poor understanding of 1NF and Part 2 provided a correct definition and explanation. Part 3 explains how 1NF can be enforced by the data sublanguage, which SQL does not.

1. What's wrong with this picture?

NoSQL database management systems give us the opportunity to store our data according to more than one data storage model, but our entity-relationship data modeling notations are stuck in SQL land. Is there any need to model schema-less databases, and is it even possible? --Theodore Hills, The Hybrid Data Model, Dataversity.net

09/19/23: For the latest on this subject see: FIRST NORMAL FORM - A DEFINITIVE GUIDE

Note: This is a 11/23/17 revision of Part 2 of a three-part series that replaced all of my previous posts on the subject (pages of which redirect here), in order to further tighten integration with the McGoveran formalization and interpretation [1] of Codd's true RDM.
(Cont'd from Part 1)
 
Part 1 raised the issue of poor understanding of Codd's concept of a simple domain with atomic values underlying 1NF. In Part 2 I clarify Codd's definition of 1NF and its correct interpretation.

Q: I am trying to remove duplicate rows from a database containing 4446 tables and when finished, merge data from that database to an existing database with the same structure. Is there an easy way to remove duplicate rows from all those 4446 tables?

A1: Please tell us "what is a 4446 table?" And, please give examples of duplicate records.

A2: Recreate your database with unique primary keys. 2) Copy records from original database to recreated database, one table at a time. The duplicate records will drop on the floor. 3) Fix the thousands of bugs in your application code that created the duplicate rows in the first place.

A3: I'm creating temporary tables + I add unique index to that table and insert data into and then I rename it to the original one.

--stackoverflow.com

[With] a declarative language and you have no real control over the execution plan. Heck, Oracle 12c can use multiple different execution plans for the same query depending on bind parameters, statistics not matching reality, and the phase of the moon.

No declarative language will ever be a first-class programming citizen in my eyes. Too much magic. Too non-linear. Too hard to debug for those reasons. They are great for when you have to do really simple things but once you step off the well worn path, you are in the thicket.
--reddit.com

09/19/23: For the latest on this subject see: FIRST NORMAL FORM - A DEFINITIVE GUIDE

Note: This is a 11/23/17 revision of Part 1 of a three-part series that replaced all of my previous posts on the subject (pages of which redirect here), in order to further tighten integration with the McGoveran formalization and interpretation [1] of Codd's true RDM.

On the one hand:

"... there is no generally accepted definition of 1NF ... the word that you see most often is 'atomic'. It is common to say that a relation is in 1NF if all its attributes [sic] are atomic ... Does 1NF equate to “atomic attribute [values]”? ... what [do] people have in mind [when they claim] atomicity? ... the [meaning] behind definitions is that you should rarely need to extract information from a value of an attribute ... But that explains why one cannot decide, depending on theory only, whether a relation is in 1NF ... it is a habitual use of data that makes attributes atomic, not theory. No wonder, there is so much mess in theory about what 1NF should be."

Karen Lopez: To bring others up to speed, Fabian is using an academic taxonomy for data modeling terms. It's valid. It's popular in research. It's not used in any major data modeling tool, nor in many practitioner resources. I have been using the industry vernaculars in my posts and here. Part of the debate that Fabian is having is because he does not tolerate the industry terms, so he chooses to attack others who use the mainstream terms. He believes they are "wrong" instead of "alternative". So that's part of the pain we have in debating his positions. I'm bilingual, but I choose to use just one set in my writings.

Fabian Pascal: Karen, bollocks. See, I can use non-academic terms too.

NULL values can be very useful, especially on indexes, as an indication of "index is not set" or "no index here", or "default inherited index applies".

I use Null values extensively (in huge database systems) with not only no problems whatsoever, but measurably signifcant advantages. People who try to tell you that "Nulls are the work of the devil", or :the sky will fall down if you allow nulls", or some such unsubstantiated childish delusion are exclusively ignorant of the correct ways to handle them. (Or too lazy/ineducable to learn their correct implementation and/or benefits.)

Fact, just plain indisputable fact. --bytes.com

I don't know that I would say that the RM is dead per se, or even holding us back. But I have to ask... why the relational model? ...A Hierarchical Model would work. In fact if we look at these NoSQL databases ... Hierarchical Models work better than relational models. The point is that some of the factors which cause one to think in terms of relationships have changed. Disk is cheap. There are definitely problems where having a strongly typed and structured model make sense. Then there are problems where not having a rigid model make sense. Structure on read seems to make sense these days. PS... Relational models don't scale. Especially on MPP.

--LinkedIn.com

See:

SEMANTICS, DATABASE RELATIONS, AND TABLES

Table (n.) – a collection of information (data?) describing a population of entities which possess some common characteristics, called attributes. Tables are the building block of relational databases.  Tables must generally be “normalized,” at least to 1NF.  That may be an appropriate way to think of databases when implemented in a modern day DBMS.  However, it is not the way the world thinks logically. People have no problem with commonly occurring phenomena such as:
* A multi-valued attribute, e.g., an Employee possesses multiple Skills.
* Many-to-many (M:N) relationships, e.g., as between Employees and Projects
* A relationship with attributes
--Gordon Everest, Recognizing and Treating Tableitis

JN: It's an unfortunate bit of propaganda. Some truth mixed in with distractions and irrelevant comparison. I've met the DataStax team. They're smart people with a solid understanding of their space. I'm disappointed to see them mix good and bad information into something that looks like objective truth.

According to Wikipedia, Amazon's Redshift is a modified version of Postgres.
Maybe its speed redshifted data integrity into a bloody mess.

It has no primary keys, foreign keys or unique constraints. It just has optimizer hints in the DDL that *maybe* the data behaves that way. If they want to put hints in the DDL, OK, but don't call those hint PRIMARY KEY, FOREIGN KEY or UNIQUE.
--Unsupported PostgreSQL Features - Amazon Redshift

I don't think any version of Postgres lets you say Create Table Foo (Bar Int PRIMARY KEY) and then let you do

INSERT INTO Foo (BAR) Values (42)
INSERT INTO Foo (BAR) Values (42)
INSERT INTO Foo (BAR) Values (42)

That might be fine for a one time only static data warehouse, but an ongoing data update system is going to break the integrity rules, it is just a matter of time. That would make for some surprises when someone decides to migrate their data and lots of application code from a relational DBMS to Redshift.
--Jeff Winchell

1. Quote of the Week

The entire Information Technology industry is still stuck in 1971 with Dr. Codd's 3rd Normal form ... when 3 more exist and now a 7th or (N) Normal Form ... Dr. Codd, created, devised, extrapolated 6 forms of Data NORMALIZATION, and after 45 years (1971), every single Database system or information management system to date has not exceeded the 3rd normal Form... except AtomicDB, (N) Normal Form improving on Dr. Codd's work by 4 levels(dimensions)at the very least ... There was and is a method to his theory, that with each level of Normalization brings a geometric increase in "Efficiency" and scalability, however, no one has even attempted the restrictions of the 4th normal form (no duplicates) let alone the 5th or 6th, and now with Dr. Everett’s (N)th Normal form, we can do anything the human mind can devise on a computer and in nearly real time. --Jean Michel LeTennier, CTO, Atomic Database Corp.

One other intriguing benefit of NoSQL that I started to unwitting benefit from recently is the ability to push data scheme concerns entirely to the application layer. In this scenario, the applications use a NoSQL database predominantly as a storage service, lightly structured by a few indexed key fields. The object structured data document within the payload becomes transparent to the database. The applications then assume the role of enforcing and understanding the data scheme.

This approach allows the application architect to encode the data structures and meaning directly in the code that creates and consumes the data. So data structure changes required for functional updates can be implemented, tested, and deployed in the application code base with no updates to the database layer at all. (Of course, a conversion of existing NoSQL data documents may be required in situations.)

In this NoSQL approach, the removed translation of object data scheme to a relational structure and, and then back to an object structure again is a very welcome relief as well. --use-the-index-luke.com

• Needing A Database Design Book
• The database curmudgeon speaks again

RAQUEL

After attending NoSQL conference I am really hoping that companies think through this 'big data' implementation! No one there was interested in Data model ... and said so ... forget the data model ... even 'standards' were looked at 'its 'too early' for this new technology ... and no one could tell us anything about 'meta-data'...!!!!! --LinkedIn.com

The Principle of Orthogonal Database Design Part I

The ER notation consists a set of constructs, such as, a rectangle to represent an entity types, an ellipse to represent an attribute a diamond to represent a relationship type, and so on. The RDS is a set of linear relation schemas. A relation schema has a name and is a sequence of attributes of text separated by comas and arranged horizontal. I have also developed an ER-to relational transformation algorithm to transform an ER schema to its corresponding RDS. I wish to implement this project as a CASE tool. --LinkedIn.com

The First Normal Form in Theory and Practice, Part  I

• Why is it so hard to get SQL performance right the first time
• Why Are There No Relational DBMS's
• SchemeUnit and SchemeQL: Two Little Languages 

"The real definition of Big Data?? Simple: Whatever does not fit in Excel!"

Me: What is--precisely, pls!--the threshold from small to big data? And how do the structural, manipulative and integrity aspects change over the threshold?

HM: Big data := the smallest set of data for which the sensitivity of your transfer function is minimal, and such that the cardinality of this set is too large to implement said transfer function on a single physical machine.Transfer function := though not strictly a function, as in the Lambda calculus, it is more of an operator which ingests data of any size and produces a monetizeable product or service. Sensitivity := the degree to which a perturbation on the input into a transfer function affects the result produced by said function.

Me: Ugh! I'm sure that's exactly what all the overnight data scientists, including those who invented Big Data, had in mind.--LinkedIn.com

C: As a non-CS grad coming fresh to databases, I found both the entity-relationship, and the object-oriented models confusing. Then I read Date [1] and Codd's [2] books and papers on the relational model, the one from the 1970s that is basically set and type theory applied to data, and found that to be a lot clearer and a more powerful abstraction to deal with your data model. As a non-"full time developer" it amazes me the number of "experienced" developers who are not aware of the relational model and who do not know what a foreign key is, or why referential integrity might be important.

For example, your Relational Model introduction has a discussion of various data types. But arguably, whether your integer is implemented as BIGINT or TINYINT is an implementation decision which should be separate from the model discussion (dixit Date). In other words, that attribute has a type of integer and how that integer is stored is a separate issue, and your RDBMS ought to abstract it away (as, I think, Postgres is pretty good with, and MySQL quite annoying). The beauty of the latest RDBMS developments, particularly in PostgreSQL world, is that the implementation has gotten so good that you don't need to really worry about it like you used to just a decade ago, at least in 95% of use cases.

I think one can teach SQL (and the relational model) to a non-developer in about 2 hours, because it is so declarative and intuitive. One day I'll go write that tutorial, as many clients need it sorely.

• Find the Supplier number for those suppliers who supply every part
• Why is it so hard to get SQL performance right the first time

... the challenges inherent in the SQL RDBMS [sic] approach ... the constrained schema (or schema-first) approach of SQL RDBMS engines imposes semantic infidelity rather than fidelity on all applications and services that depend on this RDBMS type, solely ... SQL RDBMS engines (as per what I've outlined above) do impose a "one size fits all" constraint on DBMS driven apps and services that manifests as the "data variety issue" outlined by the "Big Data" meme.
--LinkedIn.com

Understanding Codd's 12 Rules for RDBMS

The Real Data Science: Tables--So What?

You see, in Cassandra 1.x, the data model is centered around what Cassandra calls “column families”. A column family contains rows, which are identified by a row key. The row key is what you need to fetch the data from the row. The row can then have one or more columns, each of which has a name, value, and timestamp. (A value is also called a “cell”). Cassandra’s data model flexibility comes from the following facts:
* column names are defined per-row
* rows can be “wide” — that is, have hundreds, thousands, or even millions of columns
* columns can be sorted, and ranges of ordered columns can be selected efficiently using “slices”.
--http://blog.parsely.com/post/1928/cass/

• Database Design is Easy — Or Is It?
• 4 Reasons Why the Database Is Your #1 Bottleneck to Agile Development
• Basic Rules of Cassandra Data Modeling

• Is There a Closed Model Of the World Premise?
• The Folk Model is Real - The Universal Model is the Fiction

• 10 Common Design Mistakes
• Entity Relationship Problem

The First Normal Form in Theory and Practice Part I

The Real Data Science: Tables--So What?

[Do] formalized languages need the definition of data types? Up to now I have not read strong arguments against my statement that for interpretation and operation on data the use of character strings is sufficient when

• All data are expressed as character strings that are explicitly based in language communities, whereas the character strings denote concepts that are represented by UID's;
• The denoted concepts are defined by their supertype concepts (among others);
• Collections of allowed qualitative concepts (that are denoted by string values or value ranges) are defined to enable the specification of constraints; --LinkedIn.com

• There is no Such Thing as Object-Relational Impedance Mismatch
• SQL for Beginners in a Nutshell [Infographic]

• Orthodoxy versus the Data Management Reformation
• Back to Basics
• How to enforce a constraint over multiple tables

• Query Optimization
• Relational Algebra
• LEAP RDBMS
• Relational
• Relational Algebra Translator
• System for Translating Relational Algebra Scripts into Microsoft SQL Server SQL Scripts
• System for Translating Relational Algebra Scripts into Oracle SQL Scripts

• The Case For Teaching Ignorance
• Tech nightmares that keep Turing Award winners up at night 
• New @The PostWest

The Real Data Science: Tables -- So What? 

• The Real Data Science
• Relations and databases
• 5NF R-tables
• "Table arithmetic"
• RDM and SQL