A 1:1 relationship typically indicates that you have partitioned a larger entity for some reason. Often it is because of performance reasons in the physical schema, but it can happen in the logic side as well if a large chunk of the data is expected to be “unknown” at the same time (in which case … Read more
Your pizza can have exactly three topping types: one type of cheese one type of meat one type of vegetable So we order two pizzas and choose the following toppings: Pizza Topping Topping Type ——– ———- ————- 1 mozzarella cheese 1 pepperoni meat 1 olives vegetable 2 mozzarella meat 2 sausage cheese 2 peppers vegetable … Read more
Another common Name for this model is the Supertype Model, where one has a base set of attributes that can be expanded via joining to another entity. In Oracle books, it is taught both as a logical model and physical implementation. The model without the relations would allow data to grow into invalid state and … Read more
You can use a lookup table with a VARCHAR primary key, and your main data table uses a FOREIGN KEY on its column, with cascading updates. CREATE TABLE ColorLookup ( color VARCHAR(20) PRIMARY KEY ); CREATE TABLE ItemsWithColors ( …other columns…, color VARCHAR(20), FOREIGN KEY (color) REFERENCES ColorLookup(color) ON UPDATE CASCADE ON DELETE SET NULL … Read more
Re “atomic” In Codd’s original 1969 and 1970 papers he defined relations as having a value for every attribute in a row. The value could be anything, including a relation. This used no notion of “atomic”. He explained that “atomic” meant not relation-valued (ie not table-valued): So far, we have discussed examples of relations which … Read more
In most cases . . . “Staff” always describes people. Some customers are people. Some customers are businesses (organizations). “Suppliers” are usually (always?) organizations. Staff can also be customers. Suppliers can also be customers. There are serious problems with having separate tables of staff phone numbers, supplier phone numbers, and customer phone numbers. Staff can … Read more
The term “repeating group” originally meant the concept in CODASYL and COBOL based languages where a single field could contain an array of repeating values. When E.F.Codd described his First Normal Form that was what he meant by a repeating group. The concept does not exist in any modern relational or SQL-based DBMS. The term … Read more
1NF is the most basic of normal forms – each cell in a table must contain only one piece of information, and there can be no duplicate rows. 2NF and 3NF are all about being dependent on the primary key. Recall that a primary key can be made up of multiple columns. As Chris said … Read more
SQL treats NULL specially per its version of 3VL (3-valued logic). Normalization & other relational theory does not. However, we can translate SQL designs into relational designs and back. (Assume no duplicate rows here.) Normalization happens to relations and is defined in terms of operators that don’t treat NULL specially. The term “normalization” has two … Read more
I try to attempt to explain normalization in layman terms here. First off, it is something that applies to relational database (Oracle, Access, MySQL) so it is not only for MySQL. Normalisation is about making sure each table has the only minimal fields and to get rid of dependencies. Imagine you have an employee record, … Read more