2/11/2019

Specification:

LabA

Create a data model by constructing an UML class diagram to support a portion of a drastically simplified laboratory database: LabA. It only supports a very limited set of functions. Make reasonable assumptions.

Purpose of the problem No need to model yet.

LabA (only one lab, no need to have a class Lab) allows customers to take laboratory tests for medical purposes (assume all tests are for medical purpose), such as blood test, urine test, X-ray, etc. This portion of the assignment focuses on test items that have quantitative values, such as red blood cell count, creatinine (a chemical waste molecule that is generated from muscle metabolism), etc.

Potential classes: o Customero Testo test item (?): attribute or class?: likely class because it has at least two

values: Attribute: property of a class with a value. Class: properties, methods, associations, object exists by itself.

o red blood cell count, creatinine: examples of test items, names of test items

o ‘quantitative values’ property of some test items.o ‘blood test, urine test, X-ray’: examples of lab test; names.

Customers (must?) have last names, first names, date of birth (dob), and addresses. Customers may provide optional (multiplicity) phone number and email address. They have accounts and passwords to access laboratory results through the Web. (Must a customer always have an account?) The creation times of all accounts should be stored.

Attributes of customer: last names, first names, date of birth (dob), email Addresses: attributes or classes Phone number: attribute optional (multiplicity) phone number

‘They have accounts and passwords to access laboratory results through the Web. The creation times of all accounts should be stored.’

1. 2 attributes of customer: account, password.2. 3 attributes of customer: account, password, createTime3. Account class + association between account and customer.

Questions: can a customer have multiple accounts? Do all accounts have the same privilege? Can I change my account name? …

access laboratory results: class: test result

Two customers (customer objects) may have a relationship between them. For examples, customer A may be the 'husband' of customer B and the 'father' of customer C. Customer C may be a 'sister' of customer D. The database stores a list of predefined relationship kinds (such as 'father', 'mother', etc.). This includes the special kind 'other' to capture relationships that do not fall into the regular predefined kinds. For example, the relationship kind between customer C and customer E may be 'other' and the actual description stored in the database may be that C is the 'primary driver to the Lab Center' of E. LabA may store an additional note on a relationship between two customers.

predefined relationship kinds (such as 'father', 'mother', etc.): class

Every account object is associated (owned) by 1..1 customer.

Every account object is associated (owned) by 0..1 customer. An account may be owned by someone other than a customer.

N: Every X object is associated with n Y objects.

Every customer is associated (has a relationship) with n (0 or more) other customers. E>g. A has relationship with B, C and E.

Every Y object is associated with m (or more) X objects.

Customers (class) visit laboratory centers (class?) to take tests (class). Customers may take any number of test in a visit (class, because it has properties: dates, time; form association with customer). The visit dates and times are recorded, together with the nurses (attribute? Class? Class: have properties, form associations, exists by themselves) who provided the tests (such as by drawing bloods and collecting urine: just examples). A center has an unique identifier, a name, plus other information not needed to be modeled in this assignment.

Every customer can be associate with n visits? 0..*

Every visit object is associated with m customers? (A visit object is a visit by a customer to a center to conduct a number of tests?) 1..1

“A visit is a visit by a customer on a center at a specific time. A customer visiting a center twice in the same day will be considers as two separate visits.”

A visit is always handled by a nurse, an employee of LabA. Besides the usual names, dates of births and addresses recorded for all persons in the database, the social security numbers (ssn), phones, and email addresses of all employees must also be stored.

Concepts:

1. Nurse: a type of employee2. Employee: properties: social security numbers (ssn), phones, and email

addresses (class as a role)3. Person: properties: names, dates of births and addresses4. Customer (class as a role)

Person: a person in the system.

Employee: a role a person can take on with certain responsibility and privilege.

Conversions of UML to Relational Schema

Source Model: UML1. Basic structures: classes, associations, and attributes.2. Auxiliary structures: objects, multiplicity, data types, default values,

stereotypes, candidate keys, primary keys, nullability, etc. Target Model: Relational

1. Basic structures: relations, and columns (attributes).2. Auxiliary structures: rows (tuples), candidate keys, primary keys, data

types, nullability, indexes, constraints, etc.3. Different RDBMS provide different features.

C1. A class C is mapped to a relation RC.E.g. Person

ATT1. (Basic) Include all single-valued attributes (with simple data types) of a class C as the attributes of RC, the relation for the class C.E.g. PersonId, LName, FName, DoB, Address, City, State, ZipCode)

ATT2. (Basic) For each multi-valued attribute A of the class C, create a new relation RCA containing the attribute A and the primary key, RCId, of the relation RC (which implements the class C).

1. (RCId, A) is a composite candidate key.2. RCId is a foreign key referencing RC(RCId)3. A surrogate key, such as RCA_Id, may be created to serve as

a simple candidate key, to be selected as the primary key.4. The name of RCA should be meaningfully selected.

Member(MemberId, MemberScreenName, Hobbies) no good.

A1. (basic) For a many to one association between C1 (the class with the one multiplicity) and Cm, add a column C1_Id into the relation Rm (which implements Cm).

1. Assume that R1_Id is the primary key of the relation R1.2. R1_Id is a foreign key of Rm referencing R1(R1_Id).3. The name R1_Id may need to be renamed.4. R1_Id is not null in Rm If and only if 0 is not allowed (i.e. 1..1) for C1.5. Any single valued attribute of the association is mapped to a column in Rm.6. If you have composite or multi-valued attributes of the relationship, you

should consider promoting the association to an association class or a class in your UML class diagram.

Rm = VisitR1 = Center

R1_Id = CenterId

Visit(CenterId) fK referencing Center(CenterId)

Visit(CenterId) not nullable

A2. (basic) For a many to many association between classes CA and CB, create a new relation RAB(RA_Id, RB_Id).

1. (RA_Id, RB_Id) is a candidate key.2. RA_Id references RA(RA_Id) as a foreign key.3. RB_Id references RB(RB_Id) as a foreign key.4. An additional surrogate key, such as RAB_Id, can be created.

RA: VisitRB: TestGroup

RA_ID: VisitIdRB_ID: TG_Id

(RA_Id, RB_Id) (Visit_Id, TG_Id) CK

4. RAB_Id: VG_Id

2. VisitID referencing Visit(VisitId)