Simplified Web-Based Laboratory Test Report System (LTRS)

This is a sample design based on the class diagram LTRS.asta (or LTRS.pdf)

The relation schema:

1 / Member(MemberId, LastName, Firstname, Email)
Candidate Keys / (1) MemeberId
Foreign Keys
Nullable Attributes
Non-nullable Attributes / MemberId, LastName, Firstname, Email
Notes / (1) Since there is a 1..1 to 1..1 association between the classes Member and Account, it is possible to use one relation to implement both classes. If this approach is selected, data for the Account class should be captured by the Member relation too. That is, the two relations Member and Account will be merged.
2 / Phone(PhoneId, Phone, Type)
Candidate Keys / (1) PhoneId, (2) Phone
Foreign Keys
Nullable Attributes / Type
Non-nullable Attributes / PhoneId, Phone
Notes / (1) A surrogate key, PhoneId, is created as the primary key.
3 / MemberPhone(MP_Id, MemberId, PhoneId, Description, SetTime)
Candidate Keys / (1) MP_Id, (2) MemberId, PhoneId
Foreign Keys / (1)MemberId references Member(MemberId), (2) PhoneId references Phone(PhoneId)
Nullable Attributes
Non-nullable Attributes / MP_Id, MemberId, PhoneId, Description, SetTime
Notes / (1) A surrogate key, MP_Id, is created as the primary key. (2) In this design, the constraint that a member must have at least one phone may not be captured in the ‘create table’ level. An alternate design may add {MainPhoneId, MainPhoneDescription, MainPhoneSetTime} as non-nullable columns in the Member relation and change the MemberPhone relation to AdditionalMemberPhone to store only phones other than the main one.
4 / Account(AccountId, AccountName, AltEMail, CurrentPassword, SetTime, MemberId, SecurityPhoneId)
Candidate Keys / (1) AccountId, (2) AccountName
Foreign Keys / (1) MemberId references Member(MemberId), (2) SecurityPhoneId references Phone(PhoneId)
Nullable Attributes / AltEmail, SecurityPhoneId
Non-nullable Attributes / AccountId, AccountName, CurrentPassword, SetTime, MemberId
Notes / (1) A surrogate key, AccountId, is created as the primary key. Doing so also allows the possibility of account name changes.
5 / PreviousPassword(PP_Id, Password, AccountId, SetTime)
Candidate Keys / (1) PP_Id
Foreign Keys / (1) AccountId references to Account(AccountId)
Nullable Attributes
Non-nullable Attributes / PP_Id, Password, AccountId, SetTime
Notes / (1) A surrogate key, PP_Id, is created as the primary key.
6 / PredefinedSecurityQuestion(QId, Question)
Candidate Keys / (1) QId, (2) Question
Foreign Keys
Nullable Attributes
Non-nullable Attributes / QId, Question
Notes
7 / PSQ_Answer(PSQA_Id, Answer, SetTime, AccountId, QId)
Candidate Keys / (1) PSQA_id, (2) AccountId, QId
Foreign Keys / (1) AccountId references Account(AccountId), QId references PredefinedSecurityQuestion(QId)
Nullable Attributes
Non-nullable Attributes / PSQA_Id, Answer, SetTime, AccountId, QId
Notes / (1) A surrogate key, PSQA_Id, is created as the primary key. (2) In this design, the constraint that every account has exactly three predefined security question may not be captured in the ‘create table’ level. An alternate design is merging PSQ_Answer into the table Account by adding three groups of three non-nullable columns for the three security questions: {QId_<x>, Answer_<x>, SetTime_<x>}, where <x> can be 1 to 3.
8 / UserDefinedSecurityQuestion(UDSQ_Id, Question, Answer, SetTime, AccountId)
Candidate Keys / (1) UDSQ_Id
Foreign Keys / (1) AccountId references Account(AccountId)
Nullable Attributes
Non-nullable Attributes / UDSQ_Id, Question, Answer, SetTime, AccountId
Notes / (1) A surrogate key, UDSQ_Id, is created as the primary key.
9 / LabTest(LabTestId, TestTime, MemberId)
Candidate Keys / (1) Lab_TestId, (2) MemberId, TestTime
Foreign Keys / (1) MemberId references Member(MemberId)
Nullable Attributes
Non-nullable Attributes / LabTestId, TestTime, MemberId
Notes / (1) (MemberId, TestTime) is assumed to be unique as it can be used to check for double entries of the same lab test.
10 / TestGroup(TestGroupId, TestGroupName)
Candidate Keys / (1) TestGroupId, (2) TestGroupName
Foreign Keys
Nullable Attributes
Non-nullable Attributes / TestGroupId, TestGroupName
Notes
11 / TestItem(TestItemId, TestItemName, Unit, LowerRange, UpperRange)
Candidate Keys / (1) TestItemId, (2) TestItemName
Foreign Keys
Nullable Attributes / LowerRange, UpperRange
Non-nullable Attributes / TestItemId, TestItemName, Unit
Notes
12 / TestGroupDefinition(TGD_Id,TestGroupId, TestItemId)
Candidate Keys / [1] TGD_Id; [2] TestGroupId, TestItemId
Foreign Keys / (1) TestGroupId references TestGroup(TestGroupId), (2) TestItemId referencesTestItem(TestItemId)
Nullable Attributes
Non-nullable Attributes / TGD_Id, TestGroupId, TestItemId
Notes / (1) A surrogate key TGD_Id is created as the primary key.
13 / LabTestGroup(LTG_Id, LabTestId, TestGroupId)
Candidate Keys / (1) LTG_Id, (2) LabTestId, TestGroupId
Foreign Keys / (1) LabTestId references LabTest(LabTestId), (2) TestGroupIdreferences TestGroup(TestGroupId)
Nullable Attributes
Non-nullable Attributes / LTG_Id, LabTestId, TestGroupId
Notes / (1) A surrogate key LTG_Id is created as the primary key.
14 / TestGroupResult(TGR_Id, ResultTime, Description, LabTestId)
Candidate Keys / (1) TGR_Id
Foreign Keys / (1) LabTestId references LabTest(LabTestId)
Nullable Attributes / Description
Non-nullable Attributes / TGR_Id, ResultTime, LabTestId
Notes / (1) A surrogate key TGR_Id is created as the primary key.
15 / TestItemResult(TIR_Id, Result, Description, TestItemId, TGR_Id)
Candidate Keys / (1) TIR_Id, (2) TestItemId, TGR_Id
Foreign Keys / (1) TestItemId references TestItem(TestItemId), (2) TGR_Id references TestGroupResult(TGR_Id)
Nullable Attributes / Description
Non-nullable Attributes / TIR_Id, Result, TestItemId, TGR_Id
Notes / (1) A surrogate key TIR_Id is created as the primary key.
16 / Comment(CommentId, comment, SetTime, TGR_Id, TIR_Id)
Candidate Keys / (1) CommentId
Foreign Keys / (1) TGR_Id references TestGroupResult(TGR_Id), (2) TIR_Id references TestItemResult(TIR_Id)
Nullable Attributes / TGR_Id, TIR_Id
Non-nullable Attributes / CommentId, comment, SetTime
Notes / (1) A surrogate key CommentId is created as the primary key.