| Author |
Topic |
|
seubjoh
Starting Member
6 Posts |
 Posted - 2011-05-27 : 11:14:20
|
| So I have two tables or relationsT1(A,B) and T2(C,A)There are two Functional Dependencies for these tables,A->B and CB -> A. How do I enforce these in SQL? For the first one I simply made A the primary key of T1 but I am not sure about the other one. Help? Thanks! |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-05-27 : 11:48:03
|
| I think C needs to be referenced by a foreign key from T1.BDepends on what you mean by your notation - How have you designed the tables if you don't know how to represent the dependencies?==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
seubjoh
Starting Member
6 Posts |
Posted - 2011-05-27 : 11:54:57
|
| So I tried to simplify it out but ill just dump the whole shpeel out here now lol. Here are the tables I created. T1 is projects_m, t_2 is members_m, projectId is A, DeptName is B, and MemberId is C:create table Projects_m ( ProjectId char(5) Primary Key, DeptName char(10));create table Members_m ( MemberId char(5), ProjectId char(5));Some sample inputs:Projects:ProjectID DeptNameP1 COENP2 ELENP3 BIOEP4 COENMembers:MemberID ProjectIdM1 P1M2 P2Now if I were to insert M1 and P4 into members I want it to catch it because the FD says that MemberId,DeptName -> ProjectId. Would a trigger work? |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-05-27 : 12:05:35
|
| Don't understand.Isn't this just that MemberID ProjectId is unique in Members.There isn't anywhere else that you are holding the valid combinations ==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
seubjoh
Starting Member
6 Posts |
Posted - 2011-05-27 : 12:09:56
|
| Yes, memberId,projectId should be a unique combination but adding M1 and P4 into Members would not work because member M1 already has a project in dept COEN (P1) thus breaking the FD MemberId,DeptName -> ProjectID |
|
|
|
seubjoh
Starting Member
6 Posts |
Posted - 2011-05-27 : 12:10:24
|
| Thank you by the way for the responses, sorry If I am making it confusing. |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-05-27 : 12:12:14
|
| I seeYou need to make MemberID unique then it can only have one link to a project==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
seubjoh
Starting Member
6 Posts |
Posted - 2011-05-27 : 12:35:56
|
| but what if I want to have one member have two projects but in different departments? The only way I could think of doing this would be to make one big table with all the attributes but that sort of defeats the purpose. |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-05-27 : 12:50:49
|
| I see even more, it's been a long day.I would be tempted to add the deptname to the members table (actually I would have a department table and use the ID.Otherwise I think you will have to use a trigger.You haveprojectdepartmentproject, departmentmember, projecta member can link to multiple departments but each only once.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
seubjoh
Starting Member
6 Posts |
Posted - 2011-05-27 : 13:02:35
|
| ya the trigger has been difficult. Ive tried like everything, even throwing some states in there but it hasn't quite worked. Its frustrating cause I know what to do but can't get the computer to do it. Joys of programming i guess |
|
|
|
|
Functional Dependencies