normalizing data warehouse

Here’s a story. I’m in a warehouse and I’m to normalize 8M records
The employee dimesion is composed of the following fields
Employee_key, employee_no, center_id, date_hired and other fields
What I want is to
1. Select disctint employee_no, centerid, datehired
Plus
2. the “top 1 employee_key” per group if grouped by
(employee_no,centerid,datehired)
3. no cursor pls.
the resultset
employee_no, centerid, datehired, employee_key --<--top 1
thank you, thank you…
thanks,
Jose de Jesus Jr. Mcp,Mcdba
Data Architect
Sykes Asia (Manila philippines)
MCP #2324787does this help? You could consider using min() function instead...
select
max(employee_key) as EmpKey,
employee_no,
centerid,
datehired
from
table
group by
employee_no, centerid, datehired
MC
"Jose G. de Jesus Jr MCP, MCDBA" <Email me> wrote in message
news:AF491592-2324-42A0-B397-FBA301F3DD6F@.microsoft.com...
> Here's a story. I'm in a warehouse and I'm to normalize 8M records
> The employee dimesion is composed of the following fields
> Employee_key, employee_no, center_id, date_hired and other fields
> What I want is to
> 1. Select disctint employee_no, centerid, datehired
> Plus
> 2. the "top 1 employee_key" per group if grouped by
> (employee_no,centerid,datehired)
> 3. no cursor pls.
> the resultset
> employee_no, centerid, datehired, employee_key --<--top 1
> thank you, thank you.
>
> --
> thanks,
> --
> Jose de Jesus Jr. Mcp,Mcdba
> Data Architect
> Sykes Asia (Manila philippines)
> MCP #2324787

Normalization question

Hello,

I'm building an OLTP employee roster application, and I have the data model normalized, but I'm finding it challenging to update the data in all of the underlying tables via a web form and stored procedures. I'm getting the direction to keep things normalized, but I'm struggling with table updates.

Is it sound OLTP database practice to write records to a "roster_data" table that is a foreign key repository for all of the underlying tables (almost like a view without the update restrictions)? It would be much easier to manage this table of foreign keys (fact table) instead of writing several stored procedures to keep all of the related tables (dimensions) updated. (almost like a star schema in OLAP)

I know I'm being vague, but I would like to bounce off some OLTP experts.

Thanks,

Jim

What type of data is being changed?

Is it key data?

Would CASCADE UPDATE work?

More information, table DDL, etc., would be useful.

|||

Not that you are a beginner in normalization topic, but the following links should get you the idea in thsi regard:

http://www.informit.com/articles/article.asp?p=27785&rl=1 & http://www.sqlmag.com/Articles/Index.cfm?ArticleID=4887

|||

Everyone,

My basic issue was keeping my underlying normalized data model updated via one VIEW, and I was running into the "can't updated more than one base table issue." After some research, I found the INSTEAD OF triggers -- this functionality will enable me to update all of my underyling tables using one VIEW.

Thanks everyone and have a great holiday!

Jim

No way out Please help second post

I am trying to write a query which when run can tell what courses a
department require and what courses were taken by the employee
/*
create table #temp(people_id int,course_taken varchar(50),
date_taken datetime,full_name varchar(50),
assign_id int,dept_taken varchar(50),jobcode_taken varchar(50))
insert into #temp
values(3223,'Access','1/20/1996','Doney,Vicke',6738,'3','7')
insert into #temp values(3223,'VB','11/20/1996','Doney,Vicke',6738,'3','7')
insert into #temp
values(3223,'FoxPro','11/20/1998','Doney,Vicke',6738,'3','7')
insert into #temp values(3223,'SQL
DBA','11/20/1999','Doney,Vicke',6738,'3','7')
insert into #temp values(3224,'SQL DBA','11/20/1999','Ricky Yo',6790,'4','8'
)
insert into #temp values(3224,'VB','11/20/1999','Ricky Yo',6790,'4','8')
create table #temp1(deptid varchar(50),coursecode varchar(50),jobcode_id
varchar(50))
insert into #temp1 values(3,'SQL DBA','7')
insert into #temp1 values(3,'FoxPro','7')
insert into #temp1 values(4,'FoxPro','8')
select * from #temp
select * from #temp1
*/
select coalesce (people_id,null),
coalesce(course_taken,coursecode) as coursecode,
coalesce(dept_taken,deptid) as deptid,
Case When coursecode is null
Then 0 Else 1 End Required,
Case When course_taken is null
Then 0 Else 1 End Taken
from
(
select people_id,coursecode,course_taken,deptid
,dept_taken from #temp t
right join #temp1 t1
on t.dept_taken=t1.deptid
and t.course_taken=t1.coursecode
--group by people_id,dept_taken,coursecode,course_t
aken,deptid
union
select people_id,coursecode,course_taken,deptid
,dept_taken from #temp t
left outer join #temp1 t1
on t1.deptid=t.dept_taken
and t1.coursecode=t.course_taken
) apple
This is the result set I am getting
How I can show peole id 3224in the first lin so it can show me which
courses was
required and were not taken or courses were taken which were not required.
peopleid Course Dept Required Taken
NULL FoxPro 4 1 0
3223 Access 3 0 1
3223 VB 3 0 1
3223 FoxPro 3 1 1
3223 SQL DBA 3 1 1
3224 SQL DBA 4 0 1
3224 VB 4 0 1
I like to see the outpurt come as this
peopleid Course Dept Required Taken
3224 FoxPro 4 1 0
3223 Access 3 0 1
3223 VB 3 0 1
3223 FoxPro 3 1 1
3223 SQL DBA 3 1 1
3224 SQL DBA 4 0 1
3224 VB 4 0 1
Thanks
TanweeHi
Thanks for posting DDL. But actaully your tables don't have primary key and
store redundant data, you are really need to normalize your database
I'm not certain that understand your requirements. It seems you don't need
to use UNION instead FULL join is giving you the data.
select isnull((select max(people_id) from #temp where
deptid=4),people_id),
coalesce(course_taken,coursecode) as coursecode,
coalesce(dept_taken,deptid) as deptid,
Case When coursecode is null
Then 0 Else 1 End Required,
Case When course_taken is null
Then 0 Else 1 End Taken
from
(
select people_id,coursecode,course_taken,deptid
,dept_taken from #temp t
FULL join #temp1 t1
on t.dept_taken=t1.deptid
and t.course_taken=t1.coursecode
) AS APPLE
"Tanweer" <Tanweer@.discussions.microsoft.com> wrote in message
news:AD8B48EE-F0F0-4A31-824F-CDCBA60FA3FF@.microsoft.com...
> I am trying to write a query which when run can tell what courses a
> department require and what courses were taken by the employee
>
> /*
> create table #temp(people_id int,course_taken varchar(50),
> date_taken datetime,full_name varchar(50),
> assign_id int,dept_taken varchar(50),jobcode_taken varchar(50))
> insert into #temp
> values(3223,'Access','1/20/1996','Doney,Vicke',6738,'3','7')
> insert into #temp
values(3223,'VB','11/20/ 1996','Doney,Vicke',6738,'3','7')darkred">
> insert into #temp
> values(3223,'FoxPro','11/20/1998','Doney,Vicke',6738,'3','7')
> insert into #temp values(3223,'SQL
> DBA','11/20/1999','Doney,Vicke',6738,'3','7')
> insert into #temp values(3224,'SQL DBA','11/20/1999','Ricky
Yo',6790,'4','8')
> insert into #temp values(3224,'VB','11/20/1999','Ricky Yo',6790,'4','8')
> create table #temp1(deptid varchar(50),coursecode varchar(50),jobcode_id
> varchar(50))
> insert into #temp1 values(3,'SQL DBA','7')
> insert into #temp1 values(3,'FoxPro','7')
> insert into #temp1 values(4,'FoxPro','8')
>
> select * from #temp
> select * from #temp1
> */
> select coalesce (people_id,null),
> coalesce(course_taken,coursecode) as coursecode,
> coalesce(dept_taken,deptid) as deptid,
> Case When coursecode is null
> Then 0 Else 1 End Required,
> Case When course_taken is null
> Then 0 Else 1 End Taken
> from
> (
> select people_id,coursecode,course_taken,deptid
,dept_taken from #temp t
> right join #temp1 t1
> on t.dept_taken=t1.deptid
> and t.course_taken=t1.coursecode
> --group by people_id,dept_taken,coursecode,course_t
aken,deptid
> union
> select people_id,coursecode,course_taken,deptid
,dept_taken from #temp t
> left outer join #temp1 t1
> on t1.deptid=t.dept_taken
> and t1.coursecode=t.course_taken
> ) apple
>
> This is the result set I am getting
> How I can show peole id 3224in the first lin so it can show me which
> courses was
> required and were not taken or courses were taken which were not required.
>
> peopleid Course Dept Required Taken
> NULL FoxPro 4 1 0
> 3223 Access 3 0 1
> 3223 VB 3 0 1
> 3223 FoxPro 3 1 1
> 3223 SQL DBA 3 1 1
> 3224 SQL DBA 4 0 1
> 3224 VB 4 0 1
>
> I like to see the outpurt come as this
> peopleid Course Dept Required Taken
> 3224 FoxPro 4 1 0
> 3223 Access 3 0 1
> 3223 VB 3 0 1
> 3223 FoxPro 3 1 1
> 3223 SQL DBA 3 1 1
> 3224 SQL DBA 4 0 1
> 3224 VB 4 0 1
>
> Thanks
> Tanwee
>|||This work great as long as another employee don't have the same department.
By adding this statement it will through the previous query
insert into #temp
values(3229,'Access','1/20/2004','Doney,Vicke',6739,'3','7')
"Uri Dimant" wrote:

> Hi
> Thanks for posting DDL. But actaully your tables don't have primary key an
d
> store redundant data, you are really need to normalize your database
> I'm not certain that understand your requirements. It seems you don't need
> to use UNION instead FULL join is giving you the data.
>
> select isnull((select max(people_id) from #temp where
> deptid=4),people_id),
> coalesce(course_taken,coursecode) as coursecode,
> coalesce(dept_taken,deptid) as deptid,
> Case When coursecode is null
> Then 0 Else 1 End Required,
> Case When course_taken is null
> Then 0 Else 1 End Taken
> from
> (
> select people_id,coursecode,course_taken,deptid
,dept_taken from #temp t
> FULL join #temp1 t1
> on t.dept_taken=t1.deptid
> and t.course_taken=t1.coursecode
> ) AS APPLE
>
>
> "Tanweer" <Tanweer@.discussions.microsoft.com> wrote in message
> news:AD8B48EE-F0F0-4A31-824F-CDCBA60FA3FF@.microsoft.com...
> values(3223,'VB','11/20/1996','Doney,Vicke',6738,'3','7')
> Yo',6790,'4','8')
>
>|||On Mon, 21 Mar 2005 16:31:08 -0800, Tanweer wrote:

>This work great as long as another employee don't have the same department.
>By adding this statement it will through the previous query
> insert into #temp
>values(3229,'Access','1/20/2004','Doney,Vicke',6739,'3','7')
Hi Tanweer,
Upon inspection of your data, I've come to the conclusion that your
problems are caused by lack of normalization. Unless I misunderstand
your data, you should split the #temp table in two parts:
Part 1: people_id, fullname, dept_taken (peoople_id is PK)
Part 2: people_id, course_taken, date_taken (people_id is FK to part 1;
people_id + course_taken is PK)
I didn't want to change your data, so I renamed the temp tables to
permanent tables and created views to mimic how it would look with
normalized data:
create table temp1(people_id int,course_taken varchar(50),
date_taken datetime,full_name varchar(50),
assign_id int,dept_taken varchar(50),jobcode_taken varchar(50))
insert into temp1
values(3223,'Access','1/20/1996','Doney,Vicke',6738,'3','7')
insert into temp1
values(3223,'VB','11/20/1996','Doney,Vicke',6738,'3','7')
insert into temp1
values(3223,'FoxPro','11/20/1998','Doney,Vicke',6738,'3','7')
insert into temp1 values(3223,'SQL
DBA','11/20/1999','Doney,Vicke',6738,'3','7')
insert into temp1 values(3224,'SQL DBA','11/20/1999','Ricky
Yo',6790,'4','8')
insert into temp1 values(3224,'VB','11/20/1999','Ricky
Yo',6790,'4','8')
-- Extra insert statement - uncomment for seecond test case
-- insert into temp1
--values(3229,'Access','1/20/2004','Doney,Vicke',6739,'3','7')
create table temp2(deptid varchar(50),coursecode varchar(50),jobcode_id
varchar(50))
insert into temp2 values(3,'SQL DBA','7')
insert into temp2 values(3,'FoxPro','7')
insert into temp2 values(4,'FoxPro','8')
go
-- Create two views as placeholders for normalized tables
create view people as
select distinct people_id, full_name, assign_id, dept_taken
from temp1
go
create view coursetaken as
select people_id, course_taken, date_taken
from temp1
go
-- Show normalized data
select * from people
select * from coursetaken
go
-- And here's the query
select coalesce(p.people_id, ct.people_id) as people_id,
coalesce(c.coursecode, ct.course_taken) as coursecode,
coalesce(p.dept_taken, (select dept_taken from people where
people_id = coalesce(p.people_id, ct.people_id))) as deptid,
case when c.deptid is null then 0 else 1 end as required,
case when ct.people_id is null then 0 else 1 end as taken
from people as p
inner join temp2 as c
on c.deptid = p.dept_taken
full join coursetaken as ct
on ct.people_id = p.people_id
and ct.course_taken = c.coursecode
go
drop view people
drop view coursetaken
drop table temp1
drop table temp2
go
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)