Just uploaded the slideshow these posts will be based on. It is from an exercise I use in my college database classes. See link below...
Database Normalization on SlideShare
--Dan
Pages
Labels
1st normal form
Books
calculated fields
case
cloud
concatenation
cube
database design
database diagram
database scripts
downloads
erd
except
first normal form
intersect
median
mode
MySQL
normal forms
normalization
outer join
pos410
PowerPoints
rollup
sql scripts
sql201w
sql202
sql206
sql212
sql302
text schemas
Total lines
union
visio
Welcome
Wednesday, December 6, 2017
Thursday, November 30, 2017
1st normal form violation
The table violates first normal form's rule that all cell values must be unique. See below.
You cannot have two values in one cell. This would introduce numerous problems. You would not be able to do joins on this column. Nor would grouping on the column work correctly. Sorting would not work and so on.
To resolve this issue you need to update the table to correct this entry. below is the corrected table.
This table now has no issues that violate first normal form but it WILL violate second. We will discuss that in our next post.
---Dan D'Urso
You cannot have two values in one cell. This would introduce numerous problems. You would not be able to do joins on this column. Nor would grouping on the column work correctly. Sorting would not work and so on.
To resolve this issue you need to update the table to correct this entry. below is the corrected table.
This table now has no issues that violate first normal form but it WILL violate second. We will discuss that in our next post.
---Dan D'Urso
Saturday, November 25, 2017
I am going to put up a short series of posts on normalization. It is based on an exercise I found in a textbook, long since forgotten. But it is an interesting and clear example nonetheless. The goal will be to take an Excel spreadsheet and convert it to 3rd normal form. Later I may extend it to Boyce-Codd, which I consider a variation on 3rd normal, and 4th normal.
When designing a database from scratch one would, of course, aim for a properly designed database free from anomalies. Nevertheless, it is very helpful to understand the basics of normalization so that problems can be avoided. Plus, in today's environment much data exists in Excel spreadsheets. These must be normalized as they are imported into a relational database.
Business case
Tracking of large industrial equipment such as boilers, feed heaters, etc. in chemical plants in the South. Each piece of equipment has a composite primary key consisting of the plant name and equipment type. In other words something like styrene feed heater.
Normal forms
There are five normal forms, three of which we will consider in this series. They are cumulative in the sense that a table that satisfies 2nd normal form also satisfies 1st and so on. I will cover 1st in this post. 2nd and 3rd in future posts.
1st normal form
To be in 1st normal form a table must meet the following conditions:
All rows are unique
All cells are atomic
There are no repeating groups
The order of rows and columns does not matter
Original spreadsheet
Below is the original spreadsheet. Plant name and eqpt name form a composite identifier for each row. This spreadsheet contains a violation of the 1st normal form rules.
I will cover what the problem is in my next post. A succeeding post will then introduce 2nd normal form.
Regards,
Dan D'Urso
Laguna Niguel, CA
Thursday, November 2, 2017
We recently created a short course in using Visio 2016 to create ERD's. Visio 2016 does not have a lot of traditional database design tool capabilities such as forward engineering to create scripts but it is simple and rather elegant. And it is already widely installed. Below is the slide deck for the class.
Sunday, September 9, 2012
CASE expressions
There are times when you want to pick one of several alternatives for a field in your select list. The examples we are going to use are from our bookstore database (click to download at box.com). In our first example we want to look at the order_filled column and depending on its value display a message. To do this we can use the CASE expression. In our second example we will look at the customer table and show the referrer, if any, for each customer. There are two types of CASE expressions: simple or unsearched and searched. Our examples demonstrate both.
Simple Syntax
CASE selector when expr1 then value1 when expr2 then value2 ... when exprN then valueN else value 3
The else clause is optional. The alternative is selected based on the value of the selector.
Searched Syntax
This one has no selector. The alternative expressions are evaluated and if true that choice is used.
CASE when expr1 then value1 when expr2 then value2 ... when exprN then valueN else value 3
Simple Example
Here is an example of this syntax. It expands the order_filled yes/no field.
use bookstore; select order_numb, order_date, case order_filled
when 'Yes' then 'Order status: Filled' when 'No' then 'Order status: Not filled' else 'Errorneous order status detected' end 'Order_Filled'from orders;
Simple Results
Searched CASE example
This example below shows who referred a customer. If the customer was not referred by another customer we print "self". In this case we cannot use a selector since it might evaluate to a null which leads to no defined choice. Instead, this example uses a searched case which separately evaluates each condition. You have to do it this way if the field you might use for a selector can contain nulls.
use bookstore;
select c.customer_first_name
, c.customer_last_name
, case when c.referred_by is null then 'Self' else r.customer_last_name
end as 'Referred By'from customers c left join customers r on c.referred_by = r.customer_numb;
Searched CASE reuslts
Simple Syntax
CASE selector when expr1 then value1 when expr2 then value2 ... when exprN then valueN else value 3
The else clause is optional. The alternative is selected based on the value of the selector.
Searched Syntax
This one has no selector. The alternative expressions are evaluated and if true that choice is used.
CASE when expr1 then value1 when expr2 then value2 ... when exprN then valueN else value 3
Simple Example
Here is an example of this syntax. It expands the order_filled yes/no field.
use bookstore; select order_numb, order_date, case order_filled
when 'Yes' then 'Order status: Filled' when 'No' then 'Order status: Not filled' else 'Errorneous order status detected' end 'Order_Filled'from orders;
Simple Results
Searched CASE example
This example below shows who referred a customer. If the customer was not referred by another customer we print "self". In this case we cannot use a selector since it might evaluate to a null which leads to no defined choice. Instead, this example uses a searched case which separately evaluates each condition. You have to do it this way if the field you might use for a selector can contain nulls.
use bookstore;
select c.customer_first_name
, c.customer_last_name
, case when c.referred_by is null then 'Self' else r.customer_last_name
end as 'Referred By'from customers c left join customers r on c.referred_by = r.customer_numb;
Searched CASE reuslts
Conclusion
The CASE expression can be very helpful when you need to choose between several alternatives. I am sure you will find it useful. Note there is another similar keyword - DECODE - used in Oracle.
Tuesday, June 26, 2012
There is a way to represent the schema of a database in text. Of course a diagram is very helpful in visualing the relationships, but you can also write out the schema if you have to. Suppose we have the following from the Access Relationship Designer. It is a diagram of one of the simple databases we use in class. The purpose of the database is to record hours worked by employees in a factory on various work orders.
We can represent this in text with a few conventions: bold for a required field, italic for foreign key and underline for primary key. List the table name and then the field names in parentheses.This yields the text schema below.
Employees(empno, fname, lname, rate, mgrno)
Work_Orders(wono,descr,std,accum)
Labor(empno, wono, start, end, hours)
Note the composite primary key in the labor table. Each column is also a foreign key.
You might find this technique useful if dashing off a quick Email or you are without your favorite drawing tools.
We can represent this in text with a few conventions: bold for a required field, italic for foreign key and underline for primary key. List the table name and then the field names in parentheses.This yields the text schema below.
Employees(empno, fname, lname, rate, mgrno)
Work_Orders(wono,descr,std,accum)
Labor(empno, wono, start, end, hours)
Note the composite primary key in the labor table. Each column is also a foreign key.
You might find this technique useful if dashing off a quick Email or you are without your favorite drawing tools.
Thursday, June 14, 2012
Left Outer Join with Partial Match
There are some subleties to outer joins involving partial matches. We have looked at joins where we want to show all the rows from the one side of a relationship. Or conversely the rows from the one side that have no matches on the many side. But what if we want to see all the rows from the one side and only a subset from the many side? For eaxmple show all my customers along with their orders for last year. Our first pass at this might be something like the query below.
But this will not work since the filter for 1999 orders effectively masks out the effect of the outer join. We don't want to filter out customers, just orders. The missing values should be replaced by nulls as is normal for outer joins.
Solution
The solution to the above is to prefilter the orders and then join the results of that to the customers using an outer join. We can prefilter the orders in a derived table or inline view using the following syntax.
Syntax
select columns
from table1
left join
(select statement) derived table alias
on table1 key = derived table key
Sample Code
Here is the actual code using the bookstore database (download from Box.net).
Results
Here is an extract of the results of running the above query, Emily Jones and Mary Collins did not place any orders in 1999 but were still listed.
Follow Up
We will expand on this concept in future additions to this post. Hopefully this post will have shown you how to work with outer joins that partially match. It also provided a look at inline views or derived tables which we will cover in more detail in other posts.
There are some subleties to outer joins involving partial matches. We have looked at joins where we want to show all the rows from the one side of a relationship. Or conversely the rows from the one side that have no matches on the many side. But what if we want to see all the rows from the one side and only a subset from the many side? For eaxmple show all my customers along with their orders for last year. Our first pass at this might be something like the query below.
Solution
The solution to the above is to prefilter the orders and then join the results of that to the customers using an outer join. We can prefilter the orders in a derived table or inline view using the following syntax.
Syntax
select columns
from table1
left join
(select statement) derived table alias
on table1 key = derived table key
Sample Code
Here is the actual code using the bookstore database (download from Box.net).
Here is an extract of the results of running the above query, Emily Jones and Mary Collins did not place any orders in 1999 but were still listed.
Follow Up
We will expand on this concept in future additions to this post. Hopefully this post will have shown you how to work with outer joins that partially match. It also provided a look at inline views or derived tables which we will cover in more detail in other posts.
Subscribe to:
Posts (Atom)