Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2004
    Posts
    19

    Unhappy Unanswered: 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)
    ;

  2. #2
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    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.
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  3. #3
    Join Date
    Apr 2004
    Posts
    19

    specific example

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •