This link has been bookmarked by 34 people . It was first bookmarked on 18 Apr 2008, by Douglas Atlas.
-
22 Nov 15
-
21 Aug 15
-
Each table should represent only one thing
-
-
13 Apr 15
-
24 Jan 15
-
It is important that both foreign keys and the primary keys that are used to reference share a common meaning and draw their values from the same domain. Domains are simply pools of values from which columns are drawn. For example, CustomerId is of the domain of valid customer ID #'s, which in this case might be Long Integers ranging between 1 and 50,000. Similarly, a column named Sex might be based on a one-letter domain equaling 'M' or 'F'. Domains can be thought of as user-defined column types whose definition impli
-
-
31 Aug 12
-
First Normal Form
-
atomic
-
Second Normal Form
-
table is said to be in Third Normal Form (3NF), if it is in 2NF and if all non-key columns are mutually independent.
-
-
16 Apr 12
-
primary key—a
-
column that contains unique values for a table
-
A foreign key is a column in a table used to reference a primary key in another table.
-
There can be many detail lines for each order in the pizza delivery business
-
For example, a patient may be covered by multiple insurance plans and a given insurance company covers multiple patients
-
simplifying the design of a database so that it achieves the optimum structure
-
The Normal Forms are based on relations rather than tables. A relation is a special type of table that has the following attributes:
-
First Normal Form (1NF) says that all column values must be atomic.
-
meaning indivisible
-
If lists of values are stored in a single column, there is no simple way to manipulate those values.
-
1NF also prohibits the presence of repeating groups, even if they are stored in composite (multiple) columns.
-
A better, but still flawed, version of the Orders table, tblOrder2. The repeating groups of information violate First Normal Form.
-
Tables in 1NF do not have the problems of tables containing repeating groups
-
he primary key of this table is a composite key made up of OrderId and OrderItem#.
-
A table is said to be in Second Normal Form (2NF), if it is in 1NF and every non-key column is fully dependent on the (entire) primary key.
-
Given the OrderId, you know the customer and date of the order, without having to know the OrderItem#.
-
The process of breaking a non-normalized table into its normalized parts is called decomposition.
-
You decompose the tables in such a way as to allow them to be put back together again using queries.
-
A table is said to be in Third Normal Form (3NF), if it is in 2NF and if all non-key columns are mutually independent.
-
For example, if a table contains the columns Quantity and PerItemCost, you could opt to calculate and store in that same table a TotalCost column (which would be equal to Quantity*PerItemCost), but this table wouldn't be 3NF.
-
given an OderID and an OrderItem#, you know the values of Quantity, ProductId and ProductDescription.
-
tblOrderDetail also contains a dependency among two if its non-key columns, ProductId and ProductDescription.
-
-
02 Nov 11
-
22 Aug 11
-
09 Jun 10
-
08 Jun 10
-
In fact, Codd (and other relational database theorists) use the terms relations, attributes and tuples where most of us use the more common terms tables, columns and rows, respectively (or the more physical—and thus less preferable for discussions of database design theory—files, fields and records).
-
-
13 Mar 10
-
unordered tables
-
operations
-
basic idea behind the relational model is that a database consists of a series of unordered tables (or relations) that can be manipulated using non-procedural operations that return tables.
-
unordered tables
-
The basic idea behind the relational model is that a database consists of a series of
-
he relational mode
-
basic idea behind the relational model is that a database consists of a series of unordered tables (or relations) that can be manipulated using non-procedural operations that return tables.
-
unordered tables (or relations)
-
that can be manipulated using non-procedural operations that return tables
-
The table in Codd's writings was actually referred to as a relation (a related set of information).
-
relations, attributes and tuples
-
tables, columns and rows
-
you have to make decisions regarding how best to take some system in the real world and model it in a database.
-
Each table should represent only one thing.
-
Tables in the relational model are used to represent "things" in the real world
-
a real-world object might be a customer, an inventory item, or an invoice.
-
each row in a table be unique.
-
All columns (or combination of columns) in a table with unique values are referred to as candidate keys
-
primary key must be drawn
-
A simple key is a key made up of one column, whereas a composite key is made up of two or more columns.
-
Fabian Pascal
-
based upon the principles of minimality
-
and simplicity/familiarity
-
stability
-
most developers favor numeric primary keys because names do sometimes change and because searches and sorts of numeric columns are more efficient than of text
-
Counter columns in Microsoft Access make good primary keys
-
arbitrary static whole number
-
primary key
-
one-to-one
-
for every row in the first table, there is at most one row in the second table
-
may be necessary in a database when you have to split a table into two or more tables because of security or performance concerns or because of the limit of 255 columns per table.
-
for every row in the first table, there can be zero, one, or many rows in the second table, but for every row in the second table there is exactly one row in the first table.
-
one-to-many
-
many-to-many
-
for every row in the first table, there can be many rows in the second table, and for every row in the second table, there can be many rows in the first table.
-
Many-to-many relationships can't be directly modeled in relational database programs
-
must be broken into multiple one-to-many relationships.
-
They describe one entity.
They have no duplicate rows; hence there is always a primary key.
The columns are unordered.
The rows are unordered.
The Normal Forms are based on relations rather than tables. A relation is a special type of table that has the following attributes:
-
First Normal Form (1NF) says that all column values must be atomic.
-
for every row-by-column position in a given table, there exists only one value, not an array or list of values.
-
1NF also prohibits the presence of repeating groups, even if they are stored in composite (multiple) columns.
-
A table is said to be in Second Normal Form (2NF), if it is in 1NF and every non-key column is fully dependent on the (entire) primary key.
-
tables should only store data relating to one "thing" (or entity) and that entity should be described by its primary key.
-
A table is said to be in Third Normal Form (3NF), if it is in 2NF and if all non-key columns are mutually independent.
-
if a table contains the columns Quantity and PerItemCost, you could opt to calculate and store in that same table a TotalCost column (which would be equal to Quantity*PerItemCost), but this table wouldn't be 3NF. It's better to leave this column out of the table and make the calculation in a query or on a form or a report instead
-
referential integrity
-
entity integrity
-
primary keys cannot contain null (missing) data.
-
You can't uniquely identify or reference a row in a table, if the primary key of that table can be null.
-
A row may not be added to a table with a foreign key unless the referenced value exists in the referenced table.
If the value in a table that's referenced by a foreign key is changed (or the entire row is deleted), the rows in the table with the foreign key must not be "orphaned."
-
you still have to come up with a design that reflects the business you are trying to model.
-
it helps to understand the business (or other scenario) you are trying to model.
-
20-step approach to sound database design:
-
write out a basic mission statement for the system.
-
Start to rough out (on paper) the data entry forms.
-
Based on the forms, you created in step 3, rough out your tables on paper.
-
Look at your existing paper or computerized reports. (If you're starting from scratch, rough out the types of reports you'd like to see on paper.)
-
Take the roughed-out reports from step 5 and make sure that the tables from step 4 include this data.
-
add several rows to each roughed-out table.
-
identify candidate keys for every table and using the candidates, choose the primary key.
-
Every table must have a primary key!
-
Draw relationships between the tables, noting if they are one-to-one or one-to-many.
-
Does each table describe a single entity?
-
does the primary key imply all of the other columns in each table?
-
Are there any computed columns? Are there any mutually dependent non-key columns? Remove computed columns. Eliminate mutual dependent columns by breaking out lookup tables.
-
Create the tables using Microsoft Access
-
Refine the design as needed.
-
Create prototype queries, forms, and reports.
-
Bring the users back in. Have them evaluate your forms and reports.
-
add business rules.
-
Create the final forms, reports, and queries.
-
Have the users test the system.
-
Deliver the final system.
-
Break the rules deliberately; have a good reason for denormalizing.
Be fully aware of the tradeoffs this decision entails.
Thoroughly document this decision.
Create the necessary application adjustments to avoid anomalies.
If you do break the rules, however, and decide to denormalize you database, it's important that you follow these guidelines:
-
-
14 Nov 08
-
28 Aug 08
-
27 Aug 08
-
29 Aug 06
-
05 Dec 05
-
28 Jun 05
Douglas Atlaspotentially useful, with lots of basics, and good coverage of general approach
Would you like to comment?
Join Diigo for a free account, or sign in if you are already a member.