Examples of DB Modeling in UML
by K. Yue
1. The Problem: Simplified Web-Based Laboratory Test Report System (LTRS)
This is a drastically simplified system that does not support all functions. Make reasonable[y1] assumptions.
The basic idea is to support members to log on[y2] a Web-based applicationto[y3] view the results of laboratory tests conducted.
A[y4]membershould have anaccount, which is unique to the member[y5]. The[y6] last name and first name of the member should be stored as well as a mandatory email address. A member should have a uniqueid as its primary key.
A member must have one or morephones. Members have many phones?A[y7] phone contains aphone number and an optionaltype (e.g. mobile, land line, etc.) [y8]When a phone of a member is recorded, there may be an optional description (e.g. "work phone good from 9 to 5."). A phone can be used by multiple members with different descriptions for different members. The time when a phone is added to a member should be recorded. For example,
Phone A may be 713-000-9999 and 'landline'. It may be added to member X on "2016/1/6 13:22:27" as "primary phone" and to member Y on "2016/1/2 11:20:45" as "Home phone from 5:00pm to 8:00pm".
An account contains an unique account name as a primary key. The current password and a number of previous passwords should be stored with the time the passwords were set. This is useful when a member sets a new password, the system can check a number of previous passwords to ensure that they are not reused as the new password.
If a member forgets his password, reset instructions will be sent to the recorded primary email of the member. The system also stores an optional alternate email in the account for extra protection in case the primary email does not work. Furthermore, a member can record a phone as a security phone so that a security code can be sent as SMS to the phone to be used for resetting password.
The system stores a set of predefined security questions (e.g. "What is your mother's maiden name?" There should be a unique Id to serve as the primary key of these questions. Each member must select three of these predefined security questions. Their answers should be recorded with the setting times. A member can also select a number of user defined security questions in which the member can specify both the question and the answer. Again, the setting time should be recorded.
A member may take any number of laboratory tests. Each lab test has an unique id as its primary key. The test time of the laboratory should be stored. A laboratory contains one or more predefined test groups. A test group contains one or more test items. An test item may belongs to many test groups. Each test group and test item has an unique id as its primary key.
A test item contains an unique name. It has a unit (e.g. c.c., gm. ml., etc.) for its value. It may have an optional lower range and an optional upper range for normal values. For example, the test item with id 1007 may be "RED BLOOD CELL (RBC) COUNT". Its unit may be "10E6/uL". Its lower and upper range for normal values may be "4.14" and "5.80". It may be an item for the test groups with ids 1 and 3. Different members taking this test item may have different values in different tests. Member A may have a value of "4.33" (normal) in Test X and a value of "4.66" (also normal) in Test Y. Member C may have a value of "2.25" (abnormal) in Test Z.
Test results return in groups so the result time of each test group may be different for the same laboratory test. There is an optional description of the result of each test group and each test item. Further more, there any be any number of comments on each test group and each test item. It is necessary to record these comments and when they are made. Currently, it is not necessary to store who makes the comment.
[y1]What do you mean by reasonable?
[y2]Need account to log on.
[y3]Are there many difference Web applications? Do we need to keep application data? …
Since this is a prototype limited section, I assume that the answer to answer (2) is no.
[y4]We assume that a member can have only one account. Also, a member must have an account.
[y5]Can a member have multiple account? What information do we need to store in an account? Does the system need to save past passwords? Can an account exist without a member? Maybe it is possible that an administrator, who is not a member, has an account.
[y6]Does an account always belong to a member? Are there other kinds of users who may have accounts?
[y7]Can a member has no phone?
[y8]These are values of phone type. Do we have a predefined set of phone types?