Deadlock Victum Error Message

Hi all
I keep getting and error message that says that I am a Deadlock Victim.
Is there anything that I can do about this.
What is causing this.
I have a VB6 application using ADO to connect to a MS SQL Server 2000
Database
This always happens when accessing the same table.
I am not sure but it seems to happen on updates and inserts.
My record set details are this
rsMain.CursorLocation = adUseClient
rsMain.Open sSQL, dbMain, adOpenForwardOnly, adLockOptimistic
The record set remains open for the life of the form
On this table I do have a triggers for Insert, Update and Delete.
In my table I have and order column that is used to make sure that the
records load into a list in the correct order.
When ever a change is made if it is something that changes the order then
the trigger runs an update statement to make sure that the numbers are all
hole numbers and in sync.
So
1
2
4.5
6
Will become
1
2
3
4
This is managed by a group ID and no 2 people can edit the same group at the
same time. I have coded this in to the application by disabling the save
button if another user has the group of records open.
But One person can be editing and another viewing.
I tried doing the reordering of numbers on the client side but it was fare
to slow.
Is the problem that the second person to open the group of records the
recorded set should be opened read only as well as disabling the Save
button. This is what I will try next.
Many thanks
Ian
"Ian" <ian@.NoWhere.com>'s wild thoughts were released on
Tue, 14 Sep 2004 11:25:46 +0100 bearing the following fruit:

>Hi all
>I keep getting and error message that says that I am a Deadlock Victim.
>Is there anything that I can do about this.
>What is causing this.
http://www.sql-server-performance.com/deadlocks.asp
J

>I have a VB6 application using ADO to connect to a MS SQL Server 2000
>Database
>This always happens when accessing the same table.
>I am not sure but it seems to happen on updates and inserts.
>My record set details are this
>rsMain.CursorLocation = adUseClient
>rsMain.Open sSQL, dbMain, adOpenForwardOnly, adLockOptimistic
>The record set remains open for the life of the form
>On this table I do have a triggers for Insert, Update and Delete.
>In my table I have and order column that is used to make sure that the
>records load into a list in the correct order.
>When ever a change is made if it is something that changes the order then
>the trigger runs an update statement to make sure that the numbers are all
>hole numbers and in sync.
>So
>1
>2
>4.5
>6
>Will become
>1
>2
>3
>4
>This is managed by a group ID and no 2 people can edit the same group at the
>same time. I have coded this in to the application by disabling the save
>button if another user has the group of records open.
>But One person can be editing and another viewing.
>I tried doing the reordering of numbers on the client side but it was fare
>to slow.
>Is the problem that the second person to open the group of records the
>recorded set should be opened read only as well as disabling the Save
>button. This is what I will try next.
>
>Many thanks
>Ian
>
Jan Hyde (VB MVP)
Did you hear about the guy who blamed arithmetic for his divorce?
His wife put two and two together.
(Art. Moger)
[Abolish the TV License - http://www.tvlicensing.biz/]
|||Ian wrote:
> Hi all
> I keep getting and error message that says that I am a Deadlock
> Victim.
> Is there anything that I can do about this.
> What is causing this.
>
> I have a VB6 application using ADO to connect to a MS SQL Server 2000
> Database
> This always happens when accessing the same table.
> I am not sure but it seems to happen on updates and inserts.
> My record set details are this
> rsMain.CursorLocation = adUseClient
> rsMain.Open sSQL, dbMain, adOpenForwardOnly, adLockOptimistic
> The record set remains open for the life of the form
> On this table I do have a triggers for Insert, Update and Delete.
> In my table I have and order column that is used to make sure that the
> records load into a list in the correct order.
> When ever a change is made if it is something that changes the order
> then the trigger runs an update statement to make sure that the
> numbers are all hole numbers and in sync.
> So
> 1
> 2
> 4.5
> 6
> Will become
> 1
> 2
> 3
> 4
> This is managed by a group ID and no 2 people can edit the same group
> at the same time. I have coded this in to the application by
> disabling the save button if another user has the group of records
> open.
> But One person can be editing and another viewing.
> I tried doing the reordering of numbers on the client side but it was
> fare to slow.
> Is the problem that the second person to open the group of records the
> recorded set should be opened read only as well as disabling the Save
> button. This is what I will try next.
>
> Many thanks
> Ian
Keep you transactions as short as possible.
Always access tables in the same order from all stored procedures.
Determine a predefined order and try to abide by these rules in all
procedures.
Use the NOLOCK hint on SELECT statements where it's OK to potentially
read dirty data.
Use Profiler and track the SQL:StmtStarting, SP:StmtStarting events,
plus all the Deadlock events and wait for the problem to occur again. If
you can force the problem quickly, that would be ideal. See what other
transactions are involved int hedeadlock with you and see if their
queries need to be tuned.
You said: "The record set remains open for the life of the form" using
the LockOptimistic option. This is probably the root cause of the
problem. What lock level are you using when users can only read the
data? You shouls use adLockReadOnly for those transactions.
The other issue with a LockOptimistic is you are likely locking pages on
the server, which can potentially block other users.
David G.
|||Thanks to all of you
That is great
"Ian" <ian@.NoWhere.com> wrote in message
news:eYrRNVkmEHA.3328@.TK2MSFTNGP10.phx.gbl...
> Hi all
> I keep getting and error message that says that I am a Deadlock Victim.
> Is there anything that I can do about this.
> What is causing this.
>
> I have a VB6 application using ADO to connect to a MS SQL Server 2000
> Database
> This always happens when accessing the same table.
> I am not sure but it seems to happen on updates and inserts.
> My record set details are this
> rsMain.CursorLocation = adUseClient
> rsMain.Open sSQL, dbMain, adOpenForwardOnly, adLockOptimistic
> The record set remains open for the life of the form
> On this table I do have a triggers for Insert, Update and Delete.
> In my table I have and order column that is used to make sure that the
> records load into a list in the correct order.
> When ever a change is made if it is something that changes the order then
> the trigger runs an update statement to make sure that the numbers are all
> hole numbers and in sync.
> So
> 1
> 2
> 4.5
> 6
> Will become
> 1
> 2
> 3
> 4
> This is managed by a group ID and no 2 people can edit the same group at
the
> same time. I have coded this in to the application by disabling the save
> button if another user has the group of records open.
> But One person can be editing and another viewing.
> I tried doing the reordering of numbers on the client side but it was fare
> to slow.
> Is the problem that the second person to open the group of records the
> recorded set should be opened read only as well as disabling the Save
> button. This is what I will try next.
>
> Many thanks
> Ian
>

Deadlock Victum Error Message

Hi all
I keep getting and error message that says that I am a Deadlock Victim.
Is there anything that I can do about this.
What is causing this.
I have a VB6 application using ADO to connect to a MS SQL Server 2000
Database
This always happens when accessing the same table.
I am not sure but it seems to happen on updates and inserts.
My record set details are this
rsMain.CursorLocation = adUseClient
rsMain.Open sSQL, dbMain, adOpenForwardOnly, adLockOptimistic
The record set remains open for the life of the form
On this table I do have a triggers for Insert, Update and Delete.
In my table I have and order column that is used to make sure that the
records load into a list in the correct order.
When ever a change is made if it is something that changes the order then
the trigger runs an update statement to make sure that the numbers are all
hole numbers and in sync.
So
1
2
4.5
6
Will become
1
2
3
4
This is managed by a group ID and no 2 people can edit the same group at the
same time. I have coded this in to the application by disabling the save
button if another user has the group of records open.
But One person can be editing and another viewing.
I tried doing the reordering of numbers on the client side but it was fare
to slow.
Is the problem that the second person to open the group of records the
recorded set should be opened read only as well as disabling the Save
button. This is what I will try next.
Many thanks
Ian
"Ian" <ian@.NoWhere.com>'s wild thoughts were released on
Tue, 14 Sep 2004 11:25:46 +0100 bearing the following fruit:

>Hi all
>I keep getting and error message that says that I am a Deadlock Victim.
>Is there anything that I can do about this.
>What is causing this.
http://www.sql-server-performance.com/deadlocks.asp
J

>I have a VB6 application using ADO to connect to a MS SQL Server 2000
>Database
>This always happens when accessing the same table.
>I am not sure but it seems to happen on updates and inserts.
>My record set details are this
>rsMain.CursorLocation = adUseClient
>rsMain.Open sSQL, dbMain, adOpenForwardOnly, adLockOptimistic
>The record set remains open for the life of the form
>On this table I do have a triggers for Insert, Update and Delete.
>In my table I have and order column that is used to make sure that the
>records load into a list in the correct order.
>When ever a change is made if it is something that changes the order then
>the trigger runs an update statement to make sure that the numbers are all
>hole numbers and in sync.
>So
>1
>2
>4.5
>6
>Will become
>1
>2
>3
>4
>This is managed by a group ID and no 2 people can edit the same group at the
>same time. I have coded this in to the application by disabling the save
>button if another user has the group of records open.
>But One person can be editing and another viewing.
>I tried doing the reordering of numbers on the client side but it was fare
>to slow.
>Is the problem that the second person to open the group of records the
>recorded set should be opened read only as well as disabling the Save
>button. This is what I will try next.
>
>Many thanks
>Ian
>
Jan Hyde (VB MVP)
Did you hear about the guy who blamed arithmetic for his divorce?
His wife put two and two together.
(Art. Moger)
[Abolish the TV License - http://www.tvlicensing.biz/]
|||as well as J's posted url, i would suggest switching on -T1204 (You can do
this if you are the DBA)
and investigate the output in sql error log doing this will help identify
what the other process(spid) in the deadlock was. and what both processes
were doing to result in a deadlock.
U might have to address the order in which these processes run and/or
re-tune your code.
"Jan Hyde" wrote:

> "Ian" <ian@.NoWhere.com>'s wild thoughts were released on
> Tue, 14 Sep 2004 11:25:46 +0100 bearing the following fruit:
>
> http://www.sql-server-performance.com/deadlocks.asp
> J
>
> Jan Hyde (VB MVP)
> --
> Did you hear about the guy who blamed arithmetic for his divorce?
> His wife put two and two together.
> (Art. Moger)
> [Abolish the TV License - http://www.tvlicensing.biz/]
>
|||Ian wrote:
> Hi all
> I keep getting and error message that says that I am a Deadlock
> Victim.
> Is there anything that I can do about this.
> What is causing this.
>
> I have a VB6 application using ADO to connect to a MS SQL Server 2000
> Database
> This always happens when accessing the same table.
> I am not sure but it seems to happen on updates and inserts.
> My record set details are this
> rsMain.CursorLocation = adUseClient
> rsMain.Open sSQL, dbMain, adOpenForwardOnly, adLockOptimistic
> The record set remains open for the life of the form
> On this table I do have a triggers for Insert, Update and Delete.
> In my table I have and order column that is used to make sure that the
> records load into a list in the correct order.
> When ever a change is made if it is something that changes the order
> then the trigger runs an update statement to make sure that the
> numbers are all hole numbers and in sync.
> So
> 1
> 2
> 4.5
> 6
> Will become
> 1
> 2
> 3
> 4
> This is managed by a group ID and no 2 people can edit the same group
> at the same time. I have coded this in to the application by
> disabling the save button if another user has the group of records
> open.
> But One person can be editing and another viewing.
> I tried doing the reordering of numbers on the client side but it was
> fare to slow.
> Is the problem that the second person to open the group of records the
> recorded set should be opened read only as well as disabling the Save
> button. This is what I will try next.
>
> Many thanks
> Ian
Keep you transactions as short as possible.
Always access tables in the same order from all stored procedures.
Determine a predefined order and try to abide by these rules in all
procedures.
Use the NOLOCK hint on SELECT statements where it's OK to potentially
read dirty data.
Use Profiler and track the SQL:StmtStarting, SP:StmtStarting events,
plus all the Deadlock events and wait for the problem to occur again. If
you can force the problem quickly, that would be ideal. See what other
transactions are involved int hedeadlock with you and see if their
queries need to be tuned.
You said: "The record set remains open for the life of the form" using
the LockOptimistic option. This is probably the root cause of the
problem. What lock level are you using when users can only read the
data? You shouls use adLockReadOnly for those transactions.
The other issue with a LockOptimistic is you are likely locking pages on
the server, which can potentially block other users.
David G.
sql

dbo.dtproperties information

Hi All

I understand the dbo.dtproperties tables is a system table, but it appears
under "User Tables" in my Query Analyzer.

Is this normal , or does it signify a problem ? I have never seen it there
before.

In the sysobjects table, the xtype field and type field for dtproperties
table, have been set to 'U', which I thought meant User.

Perhaps I should just set them back to 'S' ?

All sugestions welcomed

Cheers

RayRay Watson (rwatson@.oztell.com) writes:
> I understand the dbo.dtproperties tables is a system table, but it appears
> under "User Tables" in my Query Analyzer.
> Is this normal , or does it signify a problem ? I have never seen it there
> before.
> In the sysobjects table, the xtype field and type field for dtproperties
> table, have been set to 'U', which I thought meant User.
> Perhaps I should just set them back to 'S' ?

No. That's the one table that has type = U, but Objectproperty(id,
'IsMSShipped') = 1.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

DBNMPNTW Access Denied Problem

Hi all

I have a program that accesses SQL 2000 that is running on a win2003 machine. The program works on my XP machine but does not work on my win98 machine, I have even opened up a port on the server so the database can be accessed externally (This seems to work fine as well, a friend of mine tested it for me).

The error message returned on the win98 machine is "[DBNMPNTW] Access Denied". Please help, thank you.

James :(I don't suppose you're on a domain are you? If not, try adding the win98 user computer\user to SQL Server as a user.|||Hi

I have managed to fix the problem. Yes i am on a domain, and my win98 machine can log onto AD (i installed AD client).

I simply added an extra field in my connection string and it worked, it's something to do with network, found it on the net somewhere.

James :)

Dbf To Ms Sql Server 2000

:eek: HI ALL
I m having my data in DBF file format. now i want to use my .Dbf files Data in to SQL Server 2000. so i can use SQL query on that and extract data from that. i want to use my DBF data in SQL. tell me how it is possible. plz give me soloution ...
Thanx
Manu VermaDo you still have a database server that will read the dbf files? If so, you can connect to it from SQL Server using DTS and import the data.|||Do you still have a database server that will read the dbf files? If so, you can connect to it from SQL Server using DTS and import the data.

we can read DBF files even in Excel or in VFoxpro. tell me the way to Import the data. i tried in SQL Server 2000. I select the Visual Foxpro ODBC Driver it gave the error. How can i import data via using DTS?|||we can read DBF files even in Excel or in VFoxpro. tell me the way to Import the data. i tried in SQL Server 2000. I select the Visual Foxpro ODBC Driver it gave the error. How can i import data via using DTS?

Follow this...
Open EM>Right click on Database>All Tasks>Import data>Select Microsoft Visual Foxpro Driver as data source>Select User/SystemDSN>New>System data source>Visual foxpro driver>Give data source name>Browse the database>Select the created DSN> and you are through...:cool:|||I select the Visual Foxpro ODBC Driver it gave the error.The error? Believe it or not, there are actually several errors you can get. Care to post the message you received?|||Hi,

You might need to download the latest drivers for Foxpro/Dbf.
Thanks

RN|||When i import the data from Foxpro .dbf file to sql, there is error:

Insert error, Column 47 ('mrlvbd',DBType_DBTimeStamp),status 6: Data Overflow.

Then i found out that it might be because of the date field of the data is blank. What can i do to solve this?

Thanks in advance|||I have found the solution.

1 ) In the Select Source Dialog Box,select the Transform Button.

2) Change the Destination Datatype of the related field from SmallDateTime to DateTime

Thanks

DBDesign Q2:

Hi All!

I like employee and department scenario from DBDesgin Q.
It looks more intuitive than my previous sample.

************************************************** *********************
Business rule:

Each employee works only in one department.
Department is managed by only one of employee who works in this
department.
One employee cannot work for two different departments and one
employee cannot manage two different departments

************************************************** ************************

*********
SolutionA(not good. Two entity referent each other)

Department (DepartmentID PK, Name, eEmployeeID FK)
Employee (EmployeeID PK , Name, , dDepartmetnID FK)
To add records in solutionA to Department and Employee tables:
Add a record to Department with eEmployeeID= NULL
Add Employee records
Set eEmployeeID to value in Department table

*********
Solution B: (I think it is cleaner than A, but there is a whole)
Department (DepartmentID PK, Name)
Employee (EmployeeID PK , Name, , dDepartmetnID FK)
(the next is subtyping of employee)
DepartmentMngr(EmployeeID PK/FK, dDepartmentID FK/U1)

It will satisfy bus. rule, but there is a whole.
Employee who is a manager can reference one department in Employee
table and the other department in DepartmentMngr.

*********
Solution C.(Problem: How buss. rule can be forced that only one
employee from department is a manager?)
Department (DepartmentID PK, Name)
Employee (EmployeeID PK , Name, , dDepartmetnID FK, eEmployeeID FK)

Q. How buss. Rule can be forced that only one employee from
department is a manager?

Are there any suggestions about implementing the above business rule?
Which of implementations are you in favor?

Thank you in advance,
Andy.The following seems to match your business rules. Treat Department Manager
as an attribute of the Department rather than the Employee.

CREATE SCHEMA AUTHORIZATION dbo

CREATE TABLE Employees (empno INTEGER PRIMARY KEY, deptno INTEGER NOT NULL
REFERENCES Departments (deptno), UNIQUE (deptno, empno))

CREATE TABLE Departments (deptno INTEGER PRIMARY KEY, deptname VARCHAR(20)
NOT NULL UNIQUE, deptmanager_empno INTEGER NOT NULL, FOREIGN KEY (deptno,
deptmanager_empno) REFERENCES Employees (deptno, empno))

The usual caveat about design questions applies: it's very difficult to give
design advice online without the opportunity to research a particular
situation in detail.

--
David Portas
----
Please reply only to the newsgroup
--|||net__space@.hotmail.com (Andy) wrote in message news:<edb90340.0311262009.33976f1a@.posting.google.com>...
> Hi All!
> I like employee and department scenario from DBDesgin Q.
> It looks more intuitive than my previous sample.
> ************************************************** *********************
> Business rule:
> Each employee works only in one department.
> Department is managed by only one of employee who works in this
> department.
> One employee cannot work for two different departments and one
> employee cannot manage two different departments
> ************************************************** ************************
> *********
> SolutionA(not good. Two entity referent each other)
> Department (DepartmentID PK, Name, eEmployeeID FK)
> Employee (EmployeeID PK , Name, ?, dDepartmetnID FK)
> To add records in solutionA to Department and Employee tables:
> Add a record to Department with eEmployeeID= NULL
> Add Employee records
> Set eEmployeeID to value in Department table

I don't find this objectionable. It may pose a technical challenge,
but that can be overcome. For example, Oracle allows constraint
checking to be deferred until the end of the transaction, so you can
do this:

insert into department( departmentid, eemployeeid ) values
('D1','E1');
insert into employee (employeeid, name, ddepartmentid) values
('E1','Smith','D1');
commit;

To enforce the rule that the manager of the department must also be an
employee in the department would require an additional database
constraint (or "assertion"), which would also have to be checked at
the end of the transaction. Not all DBMS products support this though
(Oracle doesn't).

> Solution B: (I think it is cleaner than A, but there is a whole)
> Department (DepartmentID PK, Name)
> Employee (EmployeeID PK , Name, ?, dDepartmetnID FK)
> (the next is subtyping of employee)
> DepartmentMngr(EmployeeID PK/FK, dDepartmentID FK/U1)
> It will satisfy bus. rule, but there is a whole.
> Employee who is a manager can reference one department in Employee
> table and the other department in DepartmentMngr.

Another hole is that a department can be set up with no manager at
all. Again, database constraints deferred until the end of the
transaction would be required to enforce these rules (if the DBMS
supports such constraints).

> Solution C.(Problem: How buss. rule can be forced that only one
> employee from department is a manager?)
> Department (DepartmentID PK, Name)
> Employee (EmployeeID PK , Name, ?, dDepartmetnID FK, eEmployeeID FK)
> Q. How buss. Rule can be forced that only one employee from
> department is a manager?

I don't understand this one. What is employee.eemployeeid?

> Are there any suggestions about implementing the above business rule?
> Which of implementations are you in favor?

Out of those, I'd stick with solution A. Another possibility you
didn't mention is this:

Department (DepartmentID PK, Name)
Employee (EmployeeID PK , Name, ?, dDepartmetnID FK, manager_flag)

The "manager_flag" column is a boolean or yes/no type value that
specifies whether the employee is manager of the department he/she
belongs to. That enforces the rule that an employee can only manage
his own department, but does not enforce the rule that a department
must have one and only one manager - again, a deferred database
constraint is required.

Whichever solution you choose requires the addition of database
(inter-table) constraints that are deferred until the end of the
transaction. Combinations of primary, unique and foreign keys alone
cannot enforce all the rules. With most (all?) existing DBMS
products, this means that the business rules simply cannot all be
enforced in the database; some must be enforced in the application, or
by forcing use of stored procedures to maintain the data, or not
enforced at all (except by exception reporting and manual
intervention).|||well,

i just can't image how these two can be used! can you continue your sample
and try to populate the tables with some sample data.

</wqw
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:Bp6dna5zxsvBf1iiRVn-hQ@.giganews.com...
> The following seems to match your business rules. Treat Department Manager
> as an attribute of the Department rather than the Employee.
> CREATE SCHEMA AUTHORIZATION dbo
> CREATE TABLE Employees (empno INTEGER PRIMARY KEY, deptno INTEGER NOT NULL
> REFERENCES Departments (deptno), UNIQUE (deptno, empno))
> CREATE TABLE Departments (deptno INTEGER PRIMARY KEY, deptname VARCHAR(20)
> NOT NULL UNIQUE, deptmanager_empno INTEGER NOT NULL, FOREIGN KEY (deptno,
> deptmanager_empno) REFERENCES Employees (deptno, empno))
> The usual caveat about design questions applies: it's very difficult to
give
> design advice online without the opportunity to research a particular
> situation in detail.
> --
> David Portas
> ----
> Please reply only to the newsgroup
> --|||> Solution B: (I think it is cleaner than A, but there is a whole)
> Department (DepartmentID PK, Name)
> Employee (EmployeeID PK , Name, ., dDepartmetnID FK)
> (the next is subtyping of employee)
> DepartmentMngr(EmployeeID PK/FK, dDepartmentID FK/U1)
> It will satisfy bus. rule, but there is a whole.
> Employee who is a manager can reference one department in Employee
> table and the other department in DepartmentMngr.

it's not clear what's the FK on DepartmentMngr refers to. try composite one
like this:

FOREIGN KEY(EmployeeID, dDepartmentID )
REFERENCES Employee (EmployeeID, dDepartmentID)

> Solution C.(Problem: How buss. rule can be forced that only one
> employee from department is a manager?)
> Department (DepartmentID PK, Name)
> Employee (EmployeeID PK , Name, ., dDepartmetnID FK, eEmployeeID FK)
> Q. How buss. Rule can be forced that only one employee from
> department is a manager?

if you go with the "manager_flag" to enforce "only one employee from a
department is its manager" you can use an indexed view. something like this:

SELECT dDepartmetnID
FROM Employee
WHERE manager_flag=1

and then create the PK on dDepartmetnID -- this will ensure no two employees
are manager of the same department.

cheers,
</wqw|||Ah! you spotted my deliberate mistake ;-).

Make Deptmanager_empno NULLable. Obviously that's a compromise to Andy's
requirements. Possibly you could make it non-nullable once you've populated
both tables though that could make for some interesting logical conundrums
if you need to carry out a management reshuffle between departments!

Possibly it might be acceptable to leave Deptmanager_empno nullable but add
a UNIQUE constraint so that no more than one department can be without a
manager at any one time.

--
David Portas
----
Please reply only to the newsgroup
--|||> > *********
> > SolutionA(not good. Two entity referent each other)
> > Department (DepartmentID PK, Name, eEmployeeID FK)
> > Employee (EmployeeID PK , Name, ?, dDepartmetnID FK)
> > To add records in solutionA to Department and Employee tables:
> > Add a record to Department with eEmployeeID= NULL
> > Add Employee records
> > Set eEmployeeID to value in Department table
> I don't find this objectionable. It may pose a technical challenge,
> but that can be overcome. For example, Oracle allows constraint
> checking to be deferred until the end of the transaction, so you can
> do this:
> insert into department( departmentid, eemployeeid ) values
> ('D1','E1');
> insert into employee (employeeid, name, ddepartmentid) values
> ('E1','Smith','D1');
> commit;
> To enforce the rule that the manager of the department must also be an
> employee in the department would require an additional database
> constraint (or "assertion"), which would also have to be checked at
> the end of the transaction. Not all DBMS products support this though
> (Oracle doesn't).

Are you ok with the style that employee references department table
and
department references employee table?

> > Solution C.(Problem: How buss. rule can be forced that only one
> > employee from department is a manager?)
> > Department (DepartmentID PK, Name)
> > Employee (EmployeeID PK , Name, ?, dDepartmetnID FK, eEmployeeID FK)
> > Q. How buss. Rule can be forced that only one employee from
> > department is a manager?
> I don't understand this one. What is employee.eemployeeid?

Employee.eEmployeeID(FK) references Employee.EmployeeID(PK)

> Out of those, I'd stick with solution A. Another possibility you
> didn't mention is this:
> Department (DepartmentID PK, Name)
> Employee (EmployeeID PK , Name, ?, dDepartmetnID FK, manager_flag)
> The "manager_flag" column is a boolean or yes/no type value that
> specifies whether the employee is manager of the department he/she
> belongs to. That enforces the rule that an employee can only manage
> his own department, but does not enforce the rule that a department
> must have one and only one manager - again, a deferred database
> constraint is required.
I am personally in favour of the solution with the flag. The problem
is more than one employee can be a managers or no managers at all if
all flag are false, but this can be fixed with a trigger.

Solution A. The problem is that the manager (Department.eEmployeeID)
can point to employee ((Employee.EmployeeID) from different department

Between the solution A and the proposed solution with flag which one
are you in favour of?|||"Vlad Vissoultchev" <wqweto@.nospam.myrealbox.com> wrote in message news:<uCXMLGStDHA.2060@.TK2MSFTNGP10.phx.gbl>...
> > Solution B: (I think it is cleaner than A, but there is a whole)
> > Department (DepartmentID PK, Name)
> > Employee (EmployeeID PK , Name, ., dDepartmetnID FK)
> > (the next is subtyping of employee)
> > DepartmentMngr(EmployeeID PK/FK, dDepartmentID FK/U1)
> > It will satisfy bus. rule, but there is a whole.
> > Employee who is a manager can reference one department in Employee
> > table and the other department in DepartmentMngr.
> it's not clear what's the FK on DepartmentMngr refers to.

DepartmentMngr.dDepartmentID refers Department.DepartmentID|||net__space@.hotmail.com (Andy) wrote in message news:<edb90340.0311272005.3500e39d@.posting.google.com>...
> > > *********
> > > SolutionA(not good. Two entity referent each other)
> > > > Department (DepartmentID PK, Name, eEmployeeID FK)
> > > Employee (EmployeeID PK , Name, ?, dDepartmetnID FK)
> > > To add records in solutionA to Department and Employee tables:
> > > Add a record to Department with eEmployeeID= NULL
> > > Add Employee records
> > > Set eEmployeeID to value in Department table
> > I don't find this objectionable. It may pose a technical challenge,
> > but that can be overcome. For example, Oracle allows constraint
> > checking to be deferred until the end of the transaction, so you can
> > do this:
> > insert into department( departmentid, eemployeeid ) values
> > ('D1','E1');
> > insert into employee (employeeid, name, ddepartmentid) values
> > ('E1','Smith','D1');
> > commit;
> > To enforce the rule that the manager of the department must also be an
> > employee in the department would require an additional database
> > constraint (or "assertion"), which would also have to be checked at
> > the end of the transaction. Not all DBMS products support this though
> > (Oracle doesn't).
> Are you ok with the style that employee references department table
> and
> department references employee table?
Yes, like I just said: "I don't find this objectionable." In reality,
I would probably allow a department to exist without a manager
assigned - a company might want to define its new department structure
first, and assign managers later. But if the rule really were that
every department MUST have a manager and every employee MUST be in a
department, then I would use deferred constraints to handle that.

> > > Solution C.(Problem: How buss. rule can be forced that only one
> > > employee from department is a manager?)
> > > Department (DepartmentID PK, Name)
> > > Employee (EmployeeID PK , Name, ?, dDepartmetnID FK, eEmployeeID FK)
> > > > Q. How buss. Rule can be forced that only one employee from
> > > department is a manager?
> > I don't understand this one. What is employee.eemployeeid?
> Employee.eEmployeeID(FK) references Employee.EmployeeID(PK)

OK, so this is a diferrent set up altogether: now it is employees who
are managed by other employees, and departments are not managed at
all. May be valid, but it isn't the same thing.

> > Out of those, I'd stick with solution A. Another possibility you
> > didn't mention is this:
> > Department (DepartmentID PK, Name)
> > Employee (EmployeeID PK , Name, ?, dDepartmetnID FK, manager_flag)
> > The "manager_flag" column is a boolean or yes/no type value that
> > specifies whether the employee is manager of the department he/she
> > belongs to. That enforces the rule that an employee can only manage
> > his own department, but does not enforce the rule that a department
> > must have one and only one manager - again, a deferred database
> > constraint is required.
> I am personally in favour of the solution with the flag. The problem
> is more than one employee can be a managers or no managers at all if
> all flag are false, but this can be fixed with a trigger.

I don't think it's that simple actually. Suppose you have a
department D1 with 2 employees E1 and E2, and that E1 is flagged as
being the manager. If a department must always have exactly 1
manager, how do you change the manager from E1 to E2? You can't
"unflag" E1 first, because then there would be no manager, and you
can't flag E2 first, because then there would be 2 managers.
This is a check that must be done at the transaction level (after both
updates), whereas triggers fire at the statement level.

Maybe you could get around that using a fancy update statement like:

update employee
set manager_flag = case when manager_flag='Y' then 'N' else 'Y' end
where employee_id in (E1, E2);

Another solution might be to use a trigger on employee to maintain a
new column "number_of_managers" on department. We can then add a
check constraint on this column: check(number_of_managers = 1). As
long as we can DEFER this check until the end of the transaction, we
are OK.

> Solution A. The problem is that the manager (Department.eEmployeeID)
> can point to employee ((Employee.EmployeeID) from different department
> Between the solution A and the proposed solution with flag which one
> are you in favour of?

Generally, I'd go for A because if every department is managed by 1
employee, that sounds like a foreign key from department to employee
to me.