Sunday, March 25, 2012

Do I need more integrity on my DB?

I am not sure I need more integrity on my DB. My DDL are down below.
thanks

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

create table person(
personId int identity(1,1) primary key,
fName varchar(25) not null,
mI char(1) null,
lName varchar(25) not null
);

create table student(
studentId char(4) not null primary key,
personId int not null
);

alter table student
add constraint fk_person_student
foreign key (personId)
references person (personId)
;

create table instructor(
instructorId char(4) not null primary key,
instructorQual varchar(100) not null,
personId int not null
);

alter table instructor
add constraint fk_person_instructor
foreign key (personId)
references person (personId)
;

create table contract(
contractNum int identity(1,1) primary key,
contractDate smalldatetime not null,
tuition money not null,
studentId char(4) not null foreign key references student (studentId),
contactId int not null foreign key references contact (contactId)
);

create table contact(
contactId int not null primary key,
fName varchar(25) not null,
mI char(1) null,
lName varchar(25) not null,
street varchar(50) not null,
city varchar(25) not null,
state char(2) not null,
zip char(5) not null,
relationship varchar(25) not null,
phNum char(12) not null,
emailAdd varchar(50) null,
);

create table class(
classNum char(4) not null primary key,
className varchar(25) not null,
classDay char(3) not null,
classTime char(8) not null,
testNum char(5) not null
);

alter table class
add constraint fk_class_testnum
foreign key (testNum)
references test (testNum)
;

create table discount(
discountNum char(3) primary key,
discountDesc varchar(100) not null,
discountPer decimal(3,2) not null
);

create table test(
testNum char(5) primary key,
testName varchar(50) not null,
testDate smalldatetime not null,
testFee money not null,
);

create table studentClass(
studentId char(4) not null,
classNum char(4) not null,
pass char(1) not null
);

alter table studentClass
add constraint pk_studentclass primary key clustered (studentId, classNum)
;

alter table studentClass
add constraint fk_studentclass_studenttid
foreign key (studentId)
references student(studentId)
;

alter table studentClass
add constraint fk_studentclass_classnum
foreign key (classNum)
references class(classNum)
;

create table contractDiscount(
contractNum int not null,
discountNum char(3) not null
);

alter table contractDiscount
add constraint pk_contractdiscount primary key clustered (contractNum, discountNum)
;

alter table contractDiscount
add constraint fk_contractdiscount_contractnum
foreign key (contractNum)
references contract(contractNum)
;

alter table contractDiscount
add constraint fk_contractdiscount_discountnum
foreign key (discountNum)
references discount(discountNum)
;

create table instructorClass(
instructorId char(4) not null,
classNum char(4) not null,
);

alter table instructorClass
add constraint pk_instructorclass primary key clustered (instructorId, classNum)
;

alter table instructorClass
add constraint fk_instructorclass_instructorid
foreign key (instructorId)
references instructor(instructorId)
;

alter table instructorClass
add constraint fk_instructorclass_classnum
foreign key (classnum)
references class(classnum)
;Without knowing more about your business and the applications using the database, it's very hard to say if you need more. It's possible. This looks good though just from looking at it.|||as a specific example.

I have a contract table which is connected to student, contact and contractDiscount table with studentId and contactId as a FK. contractDiscount table is a intersection table.

IF I try to delete a contract record it won't let me, unlease I delete the discount relation on contractDiscount table. when I delete the relation with that certain row in discount on contractDiscount table then I am able to delete the contract.
However in that case, my student table and contact table record will be there without the existing contract.

My first question is do i even need to delete those records in student and contact table for intefrity of DB, when I delete the contract table?

second question is If I need to delete the record how I do that because without deleting contractDiscout record, I am not even able to delete the contract record.

No comments:

Post a Comment