Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Raghavendra.

Asked: July 02, 2012 - 9:27 am UTC

Last updated: November 25, 2020 - 5:40 pm UTC

Version: 11.2.0.1.0

Viewed 1000+ times

You Asked

Hi Tom,

Just wanted to know , if oracle database supports all the normal forms we know i.e
1st,2nd,3rd.BCNF,4th,5th.

http://psoug.org/reference/normalization.html

Is scott schema we use , is it fully normalized/or can be normalized till 5th normal form?

In general, till which normal form it is sufficient to conclude that the created database is in
agreement with the standards.

Thanks,
Raghavendra B


and we said...

Since those are all just designs for schemas, yes, we'd support them all.

You could 'normalize' the scott schema further, but it wouldn't really benefit you. It is fine the way it is.

Your goal is not to get to the 99th normal form - your goal is to have an effective, efficient data model that supports your needs. 1st and 2nd are typically more than sufficient.

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Easy as 1,2,3 -- plus 4 & 5 can be.

Duke Ganote, July 03, 2012 - 5:29 pm UTC

IMHO, one can't beat Bill Kent's exposition of the normal forms.
http://www.bkent.net/Doc/simple5.htm

According to Bill [Kent], the 3NF mantra is "a non-key field must provide a fact about the key, the whole key, and nothing but the key"

4th & 5th normal forms aren't intimidating. I think of them as basically "don't cheat by putting every field into the key".

I think of EAV data models as a variant of that kind of cheating.

Am not sure, but I think the emp table is not in 3NF

AJ, November 25, 2020 - 11:55 am UTC

Hi
As far as I understand, the emp table is not in 3NF as (Deptno, Job) determine the Mgr. Empno determines (Deptno, Job). So Empno -> (Deptno, Job) ->Mgr

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

So I would remove Mgr from this table and create a new one with Deptno, Job,mgr

Chris Saxon
November 25, 2020 - 5:40 pm UTC

That would be true if MGR was the department manager. But it's the employee's manager - this is an attribute of the person. EMPNO determines MGR. So it is in 3NF.