Results 1 to 9 of 9
  1. #1
    Join Date
    Jul 2003
    Location
    vietnam
    Posts
    7

    Admission management for a university database help

    Does anyone tell me how to design this? give me some suggestions about that! I need Normalization, Relationship, functional dependency...etc

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171

    Re: Admission management for a university database help

    Originally posted by leeyoung
    Does anyone tell me how to design this? give me some suggestions about that! I need Normalization, Relationship, functional dependency...etc
    Have you come up with ANYTHING yourself yet?

  3. #3
    Join Date
    Jul 2003
    Location
    vietnam
    Posts
    7

    need database help

    I have designed this, now I want to normalize my table and create domain specialization business rule and contraint for my database
    I enclose with the file below! Get it!
    Attached Files Attached Files

  4. #4
    Join Date
    Jan 2003
    Posts
    67

    Re: need database help

    Originally posted by leeyoung
    I have designed this, now I want to normalize my table and create domain specialization business rule and contraint for my database
    I enclose with the file below! Get it!
    All of this needs to be figured out before building the actual tables. You should identify the tables, fields, primary and foreign keys. Business rules are identified by the business and are based on the requirements you gatered, ie "students must be 18years old or older".

    I think you need to come to the table with more then this. Even if we wanted to design this for you (not likley) we would need far more information.

    Good luck.

  5. #5
    Join Date
    Jul 2003
    Location
    vietnam
    Posts
    7

    help Normalization and functional dependency

    Relations:
    TblProgram(programID, progName, fees, duration)
    TblCollege(collegeID, name, address)
    TblPreRequisite(prID, programID(FK), prProgramID)
    TblFacility(facilityNo, facilityName)
    TblActivity(activityID, activityName)
    TblActivityInstance(activityInstanceID, activityID (FK), startDate, endDate)
    TblProgFacility(pfID, facilityNo(FK), programID(FK))
    TblCollegeProgramme(collProgID, collegeID(FK), programID(FK), strength, vacantSeats)
    TblCollFacility(cfID, facilityNo(FK), collegeID(FK))
    TblCollProgSchedule(scheduleID, collProgID(FK), activityInstanceID(FK))
    TblApplication(applicationID, studentID(FK), activityInstanceID(FK), place, fees, date)
    TblApplicant(studentID, name, address)
    TblApplicantQual(entryID, studentID (FK), programID (FK))
    TblAppMarks(studentID(FK), activityInstanceID(FK), marks, marksEntry)
    TblPreference(prefID, applicationID(FK), collProgInstID, prefNo)
    TblFeeReceipt(feeReceiptNo, applicationID(FK), activityInstanceID(FK), fees, modeOfPayment, date, seatAllocation)

    Note: FK: foreign key

    Functional Dependancy:
    programID --> progName, fees, duration
    studentID, collegeID --> name, address
    prID --> prProgramID
    facilityNo --> facilityName
    facilityNo, programID --> pfID
    facilityNo, collegeID --> cfID
    collProgID, activityInstanceID --> scheduleID
    collProgID, collegeID, programID --> strength, vacantSeats
    activityID --> activityName
    activityID, activityInstanceID --> startDate, endDate
    applicationID, studentID, activityInstanceID --> place, fees, date
    studentID, programID --> entryID
    studentID, activityInstanceID --> marksEntry, marks
    prefID, applicationID, collProgInstID --> prefNo
    feeReceiptNo, activityInstanceID, applicationID --> fees, modeOfPayment, date, seatAllocation


    I have design this more, but it seems wrong when I design it
    I really dont know what wrong . Help me with this problem
    I need to normailization into 1NF, 2NF, 3NF...any ideas about this Give me some solutions
    and I also attachs in Document relationship any thing wrong with it!
    Attached Files Attached Files

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171

    Re: help Normalization and functional dependency

    Is applicationID a primary/unique key for Application as its name implies? If so, that suggests:

    applicationID --> studentID, activityInstanceID, place, fees, date

    If that's true, you don't need activityInstanceID in FeeReceipt.

    (Incidentally, WHY does almost everyone put redundant "tbl" prefixes on their tables these days? Is this some horrible new "standard" I'm not aware of?)

    Some of your tables have surrogate keys that seem superfluous. For example, TblCollFacility(cfID, facilityNo(FK), collegeID(FK)). Why not just TblCollFacility(facilityNo(FK), collegeID(FK)), with both columns in the primary key? Composite keys are allowed!

  7. #7
    Join Date
    Jul 2003
    Location
    vietnam
    Posts
    7

    Normalization for this database

    programID in TblPreRequisite must exist in programID in TblProgram
    activityID in TblActivityInstance must exist in activityID in TblActivity
    facilityNo in TblProgFacility must exist in facilityNo in TblFacility
    programID in TblProgFacility must exist in programID in TblProgram
    facilityNo in TblCollFacility must exist in facilityNo in TblFacility
    collegeID in TblCollFacility must exist in collegeID in TblCollege
    collegeID in TblCollegeProgram must exist in collegeID in TblCollege
    programID in TblCollegeProgram must exist in programID in TblProgram
    collProgID in TblCollProgSchedule must exist in collProgID in TblCollegeProgram
    activityInstanceID in TblCollProgSchedule must exist in activityInstanceID in TblActivityInstance
    studentID in TblApplication must exist in studentID in TblApplicant
    activityInstanceID in TblApplication must exist in activityInstanceID in TblActivityInstance
    studentID in TblApplicantQual must exist in studentID in TblApplicant
    programID in TblApplicantQual must exist in programID in TblProgram
    studentID in TblAppMarks must exist in studentID in TblApplicant
    activityInstanceID in TblAppMarks must exist in activityInstanceID in TblActivityInstance

    just I mention above , Now I want to receive your suggestions for this
    you can give me normalization of this data! you can base on functional dependency above to normalize them!

  8. #8
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171

    Re: Normalization for this database

    Originally posted by leeyoung
    programID in TblPreRequisite must exist in programID in TblProgram
    activityID in TblActivityInstance must exist in activityID in TblActivity
    facilityNo in TblProgFacility must exist in facilityNo in TblFacility
    programID in TblProgFacility must exist in programID in TblProgram
    facilityNo in TblCollFacility must exist in facilityNo in TblFacility
    collegeID in TblCollFacility must exist in collegeID in TblCollege
    collegeID in TblCollegeProgram must exist in collegeID in TblCollege
    programID in TblCollegeProgram must exist in programID in TblProgram
    collProgID in TblCollProgSchedule must exist in collProgID in TblCollegeProgram
    activityInstanceID in TblCollProgSchedule must exist in activityInstanceID in TblActivityInstance
    studentID in TblApplication must exist in studentID in TblApplicant
    activityInstanceID in TblApplication must exist in activityInstanceID in TblActivityInstance
    studentID in TblApplicantQual must exist in studentID in TblApplicant
    programID in TblApplicantQual must exist in programID in TblProgram
    studentID in TblAppMarks must exist in studentID in TblApplicant
    activityInstanceID in TblAppMarks must exist in activityInstanceID in TblActivityInstance

    just I mention above , Now I want to receive your suggestions for this
    you can give me normalization of this data! you can base on functional dependency above to normalize them!
    I can't speak for others, but I don't have time to do others' homework for them. If you don't know how to normalize, you need to learn by trying yourself, by reading books, and by getting guidance from your teacher. I have made some suggestions in my previous post, have you considered those?

  9. #9
    Join Date
    Jul 2003
    Location
    vietnam
    Posts
    7

    Help Normalization

    Functional Dependancy:
    programID --> progName, progFees, duration
    studentID, collegeID --> name, address
    prID --> prProgramID
    facilityNo --> facilityName
    collProgID, activityInstanceID --> scheduleID
    collProgID, collegeID, programID --> strength, vacantSeats
    activityID --> activityName
    activityID, activityInstanceID --> startDate, endDate
    applicationID --> studentID, activityInstanceID, place, applicationFees, applicationDate
    studentID, programID --> entryID
    studentID, activityInstanceID --> marksEntry, marks
    prefID, applicationID, collProgInstID --> prefNo
    feeReceiptNo, applicationID --> receiptFees, modeOfPayment, receiptDate, seatAllocation

    The table below is in 1NF (no repeating groups)

    Admission(programID, progName, progFees, duration, collegeID, collegeName, collegeAddress, prID, prProgramID, facilityNo, facilityName, activityID, activityName, activityInstanceID, startDate, endDate, collProgID, strength, vacantSeats, scheduleID,
    studentID, studentName, studentAddress, applicationID, place, applicationFees, applicationDate, entryID, marks, marksEntry, prefID, collProgInstID, prefNo, feeReceiptNo, receiptFees, modeOfPayment, receiptDate, seatAllocation)

    The tables below are in 2 NF (no partial dependencies)

    TblProgram(programID, progName, progFees, duration, facilityNo, prID, prProgramID)
    TblCollege(collegeID, collegeName, collegeAddress, facilityNo)
    TblFacility(facilityNo, facilityName)
    TblActivity(activityID, activityName)
    TblActivityInstance(activityInstanceID, activityID, startDate, endDate)
    TblCollegeProgramme(collProgID, collegeID, programID, strength, vacantSeats, scheduleID, activityInstanceID)
    TblApplicant(studentID, studentName, studentAddress, entryID, activityInstanceID, marks, marksEntry)
    TblApplication(applicationID, studentID, activityInstanceID, place, applicationFees, applicationDate, prefID, collProgInstID, prefNo, feeReceiptNo, ReceiptFees, modeOfPayment, receiptDate, seatAllocation)

    The tables below are in 3NF (no transitive dependencies)

    TblProgram(programID, progName, progFees, duration)
    TblCollege(collegeID, collegeName, collegeAddress)
    TblPreRequisite(prID, programID, prProgramID)
    TblFacility(facilityNo, facilityName)
    TblActivity(activityID, activityName)
    TblActivityInstance(activityInstanceID, activityID, startDate, endDate)
    TblProgFacility(facilityNo, programID)
    TblCollFacility(facilityNo, collegeID)
    TblCollegeProgramme(collProgID, collegeID, programID, strength, vacantSeats)
    TblCollProgSchedule(scheduleID, collProgID, activityInstanceID)
    TblApplicant(studentID, studentName, studentAddress)
    TblApplication(applicationID, studentID, activityInstanceID, place, applicationFees, applicationDate)
    TblApplicantQual(entryID, studentID (FK), programID (FK))
    TblAppMarks(studentID, activityInstanceID, marks, marksEntry)
    TblPreference(prefID, applicationID, collProgInstID, prefNo)
    TblFeeReceipt(feeReceiptNo, applicationID, receiptFees, modeOfPayment, receiptDate seatAllocation)

    That is what I have done now! but I am still got stuck when I design them! That means I make some mistakes ~ Anything correct to you! What wrong with that! You can point them out for me! and show me your solutions~ Pls, help me with that!
    Thanks!

Posting Permissions

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