Design virtual Insurance Agent's DB with SQL Developer Data Modeler

 You are a small database consulting company specializing in developing databases for the medical industry. You have just been awarded the contract to develop a data model for a database application system for a mid-size health insurance company to keep track of health claims including patient information, provider (doctor) information, and information about patient visits to their doctor as well as prescription drugs prescribed to patients.

Information such as patient name, address, phone, email etc. are needed as well as who each patient’s primary care doctor is, their insurance ID number and insurance company name. We also want information on each doctor such as their specialty and what hospitals they are affiliated with as well as their phone, address etc. Regarding the hospitals themselves we will need to know where they are located and how to contact them.

The prescriptions given to each patient by a health-care provider also need to be tracked in this particular database at this time to determine claim eligibility including some basic information on the drug being prescribed to make sure there are no conflicts with a patient’s other prescriptions. We need to know each drug’s name, purpose/use and possible side effects.

If you are getting difficulty understanding below conventions, follow step by step:
https://drive.google.com/open?id=1DRCaFVvwh431AiA9_LHo4bVgz6jxXu-CEfht9P4qoyo

ERD:

Custom.pdf:

Layout1.pdf

Layout2.pdf

Layout3.pdf

layout4.pdf

General Comments:

1)      In Health Insurance domain, there are health services organizations e.g. Cigna. And they provide plans through organizations e.g.  starmark, unitedhealth and corbenser [one of these could be virtual client for me, say starmark].

2)      “INSURANCE PROVIDER” here is organization like ‘Cigna’. It may offer “MEDICAL PLAN” like “Cigna PPO S”.

3)      Generally, companies like starmark, for whom I am designing this db, talk to Pharmacy Services (“INSURANCE PROVIDER”) e.g. ‘LDI Integrated’ to back up their offering with a “DRUG PL AN” for drug prescriptions. It doesn’t mean “MEMBER” can buy medicine only from ‘LDI’.

There are “PROVIDER” of “PROVIDER TYPE” pharmacy e.g. ‘CVS’, which are bounded under “COVERAGE TYPE” in-network to “INSURANCE PROVIDER” e.g. ‘LDI’. Such a relation is stored in “SERVICE AVL COVERAGE CATEGORY”.

4)       Sometimes “MEDICAL PLAN” e.g.  ‘Cigna PPO S’ does not cover vision. Companies like starmark, for whom I am designing this db, talk to “INSURANCE PROVIDER” e.g. ‘VSP’ to back up their members with a separate vision plan.

5)      Sometimes “MEDICAL PLAN” e.g. ‘Cigna PPO S’ does not cover dental. Companies like starmark, for whom I am designing this db, talk to “INSURANCE PROVIDER” e.g.  ‘UNUM Concorida’ to back up their members with a separate dental plan.

6)      There are some “MEMBER” who take multiple “MEDICAL PLAN”  to backup unfortunate events, they are afraid of copay’s, deductibles, out of pocket etc. so much that, to cover such things they take one more “MEDICAL PLAN”  !!

So, a single “MEMBER” may end up having multiple “INSURANCE PLAN” which may be “MEDICAL PLAN”, another “MEDICAL PLAN”, a “DRUG PLAN”, a “VISION PLAN” and a “DENTAL PLAN”.  The structure of entity “MEDICAL PLAN”, “DENTAL PLAN” and “VISION PLAN” is exactly same. So, I need not identify them separately – those may be identified by “SERVICE COVERAGE BY PLAN”. Only “DRUG PLAN” behaves differently. So, I create separate entity for “DRUG PLAN”.

7)      In the discussion above, “SERVICE COVERAGE BY PLAN” is an open end till now. Let’s take it up further. A “MEDICAL PLAN” e.g. ‘Cigna PPO S’ may cover a “SERVICE” like ‘knee rod installation’ 100% in network, but only 70% out of network.  The same plan may not cover “SERVICE” like ‘lasik eye surgery’ at all, so no instance in “SERVICE COVERAGE BY PLAN” for “SERVICE” ‘lasik eye surgery’ and “MEDICAL PLAN”  ‘Cigna PPO S’. This signifies importance of entity “SERVICE COVERAGE BY PLAN”. 

8)      “SERVICE” contains master data of all services all over the globe. [Even a slight difference in how service is provided, creates a new instance, yes that is how health insurance domain works, same treatment, one “INSURANCE PROVIDER” does under one code, but another “INSURANCE PROVIDER” uses another code. They may also use same code. I myself experienced this with a dental claim for my dependent, which was fixed later on and I got reimbursements. This is dirty and we keep this information out of our so called ideal ERD world here.]

9)      “INSURANCE PROVIDER” offer “PLAN” grouped together under “INSURANCE GROUP”.  This  “INSURANCE GROUP” has different significance in different scenarios, for example in one case it could be used to group together all the offerings done to a company say AT&T, in other case it may be used to give some discount to a backward community.

10)  Let’s come back to “MEMBER” entity now. It stores all the information related to plan user, multiple instances of “MEMBER” may have been availing insurance under a main insured “MEMBER”.

11)  A “MEMBER” may be availing multiple “PLAN” by virtue of main insured “MEMBER”. The “PLAN” instances used by a primary “MEMBER” instance are stored in “MEMBER PLAN MAP” entity.

12)  “PROVIDER” may be a hospital, laboratory, doctor, Emergency care Unit and so on. This is identified by “PROVIDER TYPE”. So, it is quite possible that, under a ‘hospital’[ “PROVIDER” of  “PROVIDER TYPE” hospital ] , multiple ‘doctor’[ “PROVIDER” of  “PROVIDER TYPE”  doctor] are servicing.

13)  “PROVIDER” gives “SERVICE” is tracked by “SERVICE AVAILABLE”. This also tracks if “PROVIDER” has specialty in that “SERVICE”.

14)  For a “SERVICE”, an instance of “INSURANCE PROVIDER” does not go above allowedlimt in any case; rest extra must be taken care by “MEMBER”. This data stored in “INSURANCE SERVICE LIMIT”. This is irrespective of what “PLAN” member has.

15)   “TRACK PRIMARY” is used to record “PROVIDER” of “PROVIDER TYPE”  ‘doctor’ for a “MEMBER” with time tracking.

16)  “DRUG LABEL” is generated from “PRESCRIPTION”. To disburse the medicine to the “MEMBER”.  A “PRESCRIPTION” may generate multiple “DRUG LABEL”.

17)  A “SERVICE” is provided to “MEMBER” either with a “VISIT” or without it. Say, I got a “DRUG LABEL”, without a need of “VISIT” the “PROVIDER” of “PROVIDER TYPE” pharmacy. “SERVICE” availed by a “MEMBER” is recorded in “SERVICE PROVIDED”. Getting “DRUG” is also a service and has a  “DRUG LABEL” generated. One “VISIT” involves giving one or more “SERVICE” to “MEMBER”. “SERVICE PROVIDED” track all the “SERVICE” provided. 

18)  When a “MEMBER”  “VISIT” a “PROVIDER” of “PROVIDER TYPE” ‘doctor’, he may get one instance of “PRESCRIPTION” or multiple.  To get a “PRESCRIPTION”, “VISIT” is not mandatory.

19)  “PRESCRIPTION” is given for a “MEMBER”, includes a specific “DRUG” given by a specific “PROVIDER” of type doctor and these mentioned relationships are non-transferable. Subtype “RE-FILLABLE” of suptertype  “PRESCRIPTION” will have PillsCountPerRefill, NoOfRefillsAvailable as extra attributes. Rest “PRESCRIPTION” fall under “NON-REFILLABLE”. But, problem specifically mentions to use arc for this instead of super type-subtype.

20)  Some “MEMBER”’s “VISIT” are of subtype “NEW ILLNESS”, some are subtype “FOLLOWUP”, some are of subtype “ROUTINECHECKUP”. There is a subtype “OTHER” to track other trackable visits. We would like to be able to track which type of visit each instance is so we can keep specific information regarding the visit.

21)  A single “CLAIM”  must  have one or more “SERVICE CLAIMED”. A “CLAIM” must have all the “SERVICE CLAIMED” by same “PROVIDER”. Say I visited a hospital. Three doctors worked on my surgery giving tons of “SERVICE PROVIDED”. No one stops “SERVICE PROVIDED” being claimed under separate “CLAIM” instances, even if they are by same “PROVIDER”.  Every “CLAIM” has one “CHECK” issued which can be given only to one “PROVIDER”.  It could be a doctor name, a hospital name or else. In practice getting check issued at the end is mandatory, it includes a check no with zero amount if nothing is paid to “PROVIDER”, but ERD will keep CLAIM side of this relationship optional, because during the central program engine is processing the claim which takes sometimes many weeks, there is no check available.  

SERVICE AVL COVERAGE CATEGORY (SAY)
Key Type (pk, fk, uk)	Optionality ( "*", "○")	Column Name
pk, fk1	*	ins comp id
pk, fk2	*	service code
pk, fk2	*	provider number
	○	discount agreed
fk3	*	category code
Description/Assumptions for SAY:
Each services offered/available by Provider needs to be categorized as in-network or out-network [ this is at insurance provider level e.g cigna]. I once went to an Urgent Care, all the services treated as primary care were covered by my insurance provider as in-network but the urgent care services were out of network. It gives an idea that, for a PROVIDER, each services should be listed as in-network or out-network in DB.
SERVICE AVAILABLE (SAE)
Key Type (pk, fk, uk)	Optionality ( "*", "○")	Column Name
pk, fk1	*	provider number
pk, fk2	*	service code
	*	is speciality
Description/Assumptions for SAE:
Out of many services in the world, some services a provider gives, other he won't, weather it is covered by my insurance or not that is another story.
COVERAGE TYPE (CTE)
Key Type (pk, fk, uk)	Optionality ( "*", "○")	Column Name
pk	*	category code
	*	name
Description/Assumptions for CTE:
This entity has instances of 'out of network' and 'in network'
SERVICE CLAIMED (SCD)
Key Type (pk, fk, uk)	Optionality ( "*", "○")	Column Name
pk	*	scd number
fk1	○	ineligibility code
fk2	*	memeber id
fk3	*	claim number
fk4, uk	*	spd id
Description/Assumptions for SCD:
1. Each SERVICE PROVIDED is finally claimed as one SERVICE CLAIMED, and CLAIM must contain one or more SERVICE CLAIMED. a provided service once claimed under a SERVICE CLAIMED is non transferable. 2. If a SERVICE CLAIMED instance is calculated as an invalid/not covered, a proper ineligibility code is used here for the reason. 3. Instance must be created for a MEMBER. It is non-transferable. 4. It must be raised under a CLAIM and is non-transferable. 5. A reference to SERVICE PROVIDED is mandatory to approve/reject the claim.  Some of the validation applied using this reference in programming : a) if it is a DRUG LABEL, was the drug covered under drug plan, is the user being given this medicine too often to alarm that MEMBER is selling this medicine to black market, what kind of medicine it is - generic, preferred or non-preferred and so on. b) if it is a service given during a doctor visit, is the service start date and end date matches with the visit/admission dates. is it covered under any of the plans available to the member, to which extent and so on 6. One SERVICE CLAIMED may use one or more insurance plans to the member. For example, if no plan covers this service provided , plan applied will be nothing. for same service one plan is treated as major and other plan covers members responsibilities under first plan (COB). And so on 7. Calculated by program, based on plans applied and what member has to pay, member's ACCUMULATOR for current year change. Note: it doesn't matter MEMBER pay the PROVIDER his due part or not, that is added to ACCUMULATOR. It's PROVIDER responsibility to collect his dues, not of INSURANCE PROVIDER
CLAIM (CLM)
Key Type (pk, fk, uk)	Optionality ( "*", "○")	Column Name
pk	*	claim number
	○	adjustment
	○	adjustment description
	*	claim date
Description/Assumptions for CLM:
A CLAIM instance must be generated for one or more SERVICES CLAIMED to be be provided and a SERVICE once claimed under a claim, it will continue till end - non transferable.  Since one claim generates only one claim, programmatically it is ensured that, all SERVICE CLAIMED will pay to same provider. claim date is re cored since insurance companies are bound to SLA's to give decision. Sometimes a claim is so special that it needs special adjustments, for that amount is recorded with clear description from agent.
CHECK (CHK)
Key Type (pk, fk, uk)	Optionality ( "*", "○")	Column Name
pk	*	check id
	*	issue date
	*	amount
	*	receiver name
	*	cancelled
fk	*	claim number
Description/Assumptions for CHK:
1. A check is generally identified by issuer bank, account no and check no, but I am identifying here with checkid only 2. No need to store receiver name here, since one claim is generated for only one Provider entity (via  DRUG LABEL or SERVICE IN VISIT) even if provider changes name, check won't change  'To' field. This one time programmatic extraction occurs from PROVIDER 'payment name' attribute. 3. Check issued is nontransferrable. 4. A second check for a claim is issued only if earlier check is cancelled or lost for some reason. Default value of cancelled is false
PLAN APPLIED (PAD)
Key Type (pk, fk, uk)	Optionality ( "*", "○")	Column Name
pk, fk1	*	scd number
pk, fk2	*	ins comp id
pk,fk2	*	memeber id
pk, fk2	*	plan code
pk, fk2	*	groupid
	*	cob
Description/Assumptions for PAD:
1. For this entity cob is true if the plan used is covering member's responsibilities of a claim where primary insurance applied didn't covered it fully. This is calculated programmatically. 2. Here the member id is coming from MEMBER MEDICAL PLAN. This must also checked to match with member id in SERVICE CLAIMED 3. Plan applied is the best options identified programmatically based on relation b/w provider and insurances involved. The best options identified here means the ACCUMLATORS will be increamented minimum due to a SERVICE CLAIMED for that MEMBER. 4. It is quite possible that SERVICE CLAIMED gets ineligible in absence of any MEMBER PLAN MAP qualified to be applied. 5. The relations are kept transferrable here, because MEMBER is allowed to challenge a SERVICE CLAIMED to reduce his responsibility part.
MEMBER PLAN MAP (MPP)
Key Type (pk, fk, uk)	Optionality ( "*", "○")	Column Name
pk, fk1	*	memeber id
	*	premium
	*	member since
	○	termination date
pk, fk2	*	plan code
pk, fk2	*	groupid
pk, fk2	*	ins comp id
Description/Assumptions for MPP:
frequency of premium is in $ per month for all plans, it is some of both employer share and member share- no matter who pays it. termination date is optional here to add currently active plan applied.
ACCUMULATOR (ACR)
Key Type (pk, fk, uk)	Optionality ( "*", "○")	Column Name
pk, fk	*	plan code
pk, fk	*	memeber id
pk, fk	*	groupid
pk, fk	*	ins comp id
pk	*	year
	*	type
	○	in ntwrk deduct max indvidual
	○	in ntwrk out of pckt max indv
	○	out ntwrk deduct max indvidual
	○	out ntwrk out f pckt max indv
	○	deductible
Description/Assumptions for ACR:
1. type column defines if this amount is accumulated as DRUG PLAN ACCUMULATOR or MEDICAL PLAN ACCUMULATOR. 2. There is no need to save below mentioned coming from MEDICAL PLAN ACCUMULATOR: .out ntwrk out f pckt max fam .out ntwrk deduct max family .in ntwrk out of pckt max fam .in ntwrk deduct max family If family configuration changes during an year, these figures will change. Like divorces, getting new job etc. 3. deductible is coming from DRUG PLAN ACCUMULATOR 4. check constraints are used to verify that each instance belong to one and only one sub type. The values in this ACCUMULATOR don't have a tracking done  i.e which SERVICE CLAIM changed the entry. If that kind of tracking is needed, better is save the amounts in SERVICE CLAIM table and drop ACCUMULATOR all together. And calculate ACCUMULATOR at run time programatically.
INELIGIBILITY EXPLANATION (IEN)
Key Type (pk, fk, uk)	Optionality ( "*", "○")	Column Name
pk	*	ineligibility code
	*	title
	○	description
Description/Assumptions for IEN:
Programatically, ineligibility code for a SERVICE CLAIM is chosen and referenced in SERVICE CLAIM, if need be. This is master table to store reasons.
BILLING CATEGORY (BCY)
Key Type (pk, fk, uk)	Optionality ( "*", "○")	Column Name
pk	*	billing cat code
	*	name
Description/Assumptions for BCY:
A visit to same doctor could be billed as urgent care and I pay elevated copay, same doctor in emergency room - I give highest copay. So how a visit is being billed must be tagged. This may also affect the service codes programatically chosen for the action done in that visit.
VISIT (VIT)
Key Type (pk, fk, uk)	Optionality ( "*", "○")	Column Name
pk	*	visit id
uk1	*	start time
	○	end time
fk1, uk1	*	memeber id
fk2, uk1	*	provider number
fk3	*	billing cat code
	*	type
	○	blood pressure systolic
	○	blood pressure diastolic
	○	height
	○	weight
	○	initial diagnosis
	○	current patient condition
Description/Assumptions for VIT:
1. subtype OTHER covers everything else than specifically identified here. 2. FOLLOWUP , NEW ILLNESS and  ROUTINE CHECKUP lost there mandatory feature. 3. A doctor once visited by patient is visited, non-transferable 4. end time is kept open end if I am in hospital when claims are being processed.Visit not yet ended. But if end time is not null, it should be greater than start time of visit. 5. check contraint is used to mandate blood pressure systolic,diastolic , height,weight in case type is routine checkup - in this case initial diagnosis and current patient condition must be null as verified by check. In this check similarly, integrity of other sub type is verified. 6. Every VISIT has at least one SERVICE IN VISIT. In-fact in bills, even if doctor don't even touch you or prescribe anything, he charges big amounts to insurance as visiting fee.
SERVICE PROVIDED (SPD)
Key Type (pk, fk, uk)	Optionality ( "*", "○")	Column Name
pk	*	spd id
fk1	*	service code
fk1	*	provider number
	*	service end date
	*	service start date
	*	type
fk2	○	visit id
fk3	○	prescription number
	○	label number
	○	medicine expiration date
Description/Assumptions for SPD:
1. Primary key is spd id and used further in SERVICE CLAIMED. It is possible that SERVICE PROVIDED is not claimed at all. But one SPD goes only to one instance of SERVICE CLAIMED 2. service code and provider number make one composite foreign key.  In case of 3. service must be completed before coming here. start time must be less than end time. 4. Type is mandatory to identify if it is drug label service or service provided in a visit. 5. check constraint is used to verify integrity as a suptype, each row must belong to either SERVICE IN VISIT or DRUG LABEL. a) visit id is mandatory for SERVICE IN VISIT but stored hare as non-mandatory. b)label number is  mandatory for a label but stored here as non-mandatory and non-unique. c) prescription number mandatory for LABEL but stored hare as non-mandatory. d) medicine expiration date  is mandatory for a label but stored here as non-mandatory 6.  Programmatic check on label number is required to verify that it is unique for the LABEL entity.
PROVIDER TYPE (PTE)
Key Type (pk, fk, uk)	Optionality ( "*", "○")	Column Name
pk	*	type code
	*	name
Description/Assumptions for PTE:
A provider could be of type doctor, hospital, pharmacy, lab etc
PROVIDER (PVR)
Key Type (pk, fk, uk)	Optionality ( "*", "○")	Column Name
pk	*	provider number
	*	name
	*	payment name
fk1	*	ctt id
fk2	*	type code
AFFILIATION (AFE)
Key Type (pk, fk, uk)	Optionality ( "*", "○")	Column Name
pk, fk1	*	hospital
pk, fk2	*	doctor
Description/Assumptions for AFE:
1. A doctor must be affiliated with at least one hospital, if he wish he may get affiliated from multiple hospitals. But if Provider is not a doctor, he need not any affiliation, that is why relation show as optional on PROVIDER side in ERD. Additional programming required here to handle optionality of a doctor here. 2. Hospital must have at least one doctor affiliated and working there, but doctor won't affiliate another doctor, that is why additional programming here too.
SERVICE (SVE)
Key Type (pk, fk, uk)	Optionality ( "*", "○")	Column Name
pk	*	service code
	*	name
	*	description
MEMBER (MBR)
Key Type (pk, fk, uk)	Optionality ( "*", "○")	Column Name
pk	*	memeber id
uk	*	social secuirty number
	*	date of birth
	*	gender
	*	first name
	*	last name
fk1	○	parent member
fk2	*	ctt id
Description/Assumptions for MBR:
1. parent member column is optional because, for the head of family himself who bought this insurance, this is blank
TRACK PRIMARY DOCTOR (TPR)
Key Type (pk, fk, uk)	Optionality ( "*", "○")	Column Name
pk, fk1	*	memeber id
pk, fk2	*	provider number
	*	start date
	○	end date
Description/Assumptions for TPR:
1. MEMBER who never had been a patient till now can't have a PRIMARY doctor selected till now. 2. end date can be blank only for one record for a MEMBER and that identify current primary doctor.
SERVICE COVERAGE BY PLAN (SCN)
Key Type (pk, fk, uk)	Optionality ( "*", "○")	Column Name
pk, fk1	*	ins comp id
pk, fk1	*	groupid
pk, fk1	*	plan code
pk, fk2	*	service code
	*	in network covered share
	*	out network covered share
Description/Assumptions for SCN:
1 SERVICE AVL COVERAGE CATEGORY was to see if a provider is in-network or out-network for an INSURANCE PROVIDER  for a SERVICE. 2. Now every plan give different % of coverage for a service.  It could say this plan will cover 70% of the cost if this particular service is given out of network, if it is given in-network, insurance will cover 90%. for another service in-network could be 80% and out-network could be 20% and so on. 3. It is quite possible that for a service an insurance company has not yet made such a decision for every plan.
INSURANCE SERVICE LIMIT (IST)
Key Type (pk, fk, uk)	Optionality ( "*", "○")	Column Name
pk, fk1	*	ins comp id
pk, fk2	*	service code
	*	allowed amount
Description/Assumptions for IST:
Sometimes a SERVICE is so much overcharged by PROVIDERs every time that, the INSURANCE PROVIDER as a whole, come up with allowed amount per SERVICE basis and enforce it on all PROVIDERS in claim procedure. Anything above allowed amount is considered patient's responsibility and settled b/w patient and provider personally.
INSURANCE PROVIDER (IPR)
Key Type (pk, fk, uk)	Optionality ( "*", "○")	Column Name
pk	*	ins comp id
	*	name
fk	*	ctt id
Description/Assumptions for IPR:
1. e.g. cigna 2. ctt id defines its address and contact details
INSURANCE GROUP (IGP)
Key Type (pk, fk, uk)	Optionality ( "*", "○")	Column Name
pk	*	ins comp id
pk	*	groupid
	○	medical group discount
	○	drug group discount
Description/Assumptions for IGP:
1. drug group discount also contain the universal discount offered on medicines to everyone everywhere, in addition to special discount on drugs negotiated by the group.
PLAN (PLN)
Key Type (pk, fk, uk)	Optionality ( "*", "○")	Column Name
pk	*	ins comp id
pk	*	groupid
pk	*	plan code
	*	precondition addendum
	*	type
	○	max deductible on drug
	○	deductible applied on generic
	○	deduct applied on preferred
	○	deduct  on non preferred
	○	copay primary
	○	copay specialist
	○	copay urgent care
	○	copay emergency
	○	in ntwrk deduct max indvidual
	○	in ntwrk out of pckt max indv
	○	out ntwrk deduct max indvidual
	○	out ntwrk out f pckt max indv
	○	in ntwrk deduct max family
	○	in ntwrk out of pckt max fam
	○	out ntwrk deduct max family
	○	out ntwrk out f pckt max fam
Description/Assumptions for PLN:
1. type identifies if it is DRUG PLAN or MEDICAL PLAN 2. check constraint is required to implement subtypes here: a) For DRUG PLAN attributes marked in yellow are mandatory b) for MEDICAL PLAN attributes marked in green are mandatory 3. Drug plan do not consider family. they work on individual. 'max deductible on drug' identify maximum money member will pay before medicine getting free for him. rest of the three properties in DRUG PLAN in yellow identify if deductible is applied before making that kind of medicine fully included. generally I have seen 'deductible applied on generic' as false - means generic are fully free since beginning. rest of the two are generally true, means first finish paying the deductible then get it for free. 4. for MEDICAL PLAN attributes in green, attributes/columns  ending with fam/family [ four such attributes in MEDICAL PLAN] defining maximum per family, and the one ending with indv/individual  [ four such attributes in MEDICAL PLAN] define maximum per family member. 5. Co-payment/copay [ three columns/attributes in MEDICAL PLAN]: A fixed amount (for example, $15) you pay for a covered health care service, usually when you receive the service. The amount can vary by the type of covered health care service. 6.  Deductible max: The amount you owe for health care services your health insurance or plan covers before your health insurance or plan begins to pay. For example, if your deductible is $1000, your plan won’t pay anything until you’ve met your $1000 deductible for covered health care services subject to the deductible. The deductible may not apply to all services. [ one such amount in DRUG PLAN] [ eight such attributes in MEDICAL PLAN] 7. out-of-pocket limit: Example [ also see table named ACCUMULATOR to understand this] a)Jane hasn’t reached her $1,500 deductible yet. Her plan doesn’t pay any of the costs. Office visit costs: $125 Jane pays: $125 Her plan pays: $0 b)Jane reaches her $1,500 deductible, co-insurance begins. Jane has seen a doctor several times and paid $1,500 in total. Her plan pays some of the costs for her next visit. Office visit costs: $75 Jane pays: 20% of $75 = $15 Her plan pays: 80% of $75 = $60 c)Jane reaches her $5,000 out-of-pocket limit. Jane has seen the doctor often and paid $5,000 in total. Her plan pays the full cost of her covered health care services for the rest of the year. Office visit costs: $200 Jane pays: $0 Her plan pays: $200 [two such attributes in MEDICAL PLAN above, one for whole family, one for individual]
DRUG CATEGORY (DCY)
Key Type (pk, fk, uk)	Optionality ( "*", "○")	Column Name
pk	*	drug category code
	*	category name
Description/Assumptions for DCY:
e.g.  generic , preffered, non-preffered
DRUG (DRG)
Key Type (pk, fk, uk)	Optionality ( "*", "○")	Column Name
pk	*	drug code
	*	drug name
	*	cost per unit
	*	unit of measurement
	*	purpose
	*	side effect
fk	*	drug category code
Description/Assumptions for DRG:
generally there are multiple purposes and side effects of same drug based on salts/content. I don't go so deep here . Store everything in single column as text, one for purpose, one for side effect. Under real life implementation, salt and content of drug is also tracked, with purpose and side effects of each ingredients with possible reaction with other salts/content.
PRESCRIPTION (PSN)
Key Type (pk, fk, uk)	Optionality ( "*", "○")	Column Name
pk	*	prescription number
	*	date issued
fk1	*	drug code
	*	dosage description
	○	duration in days
	○	followup date
fk2	*	provider number
fk3	*	memeber id
fk4	○	visit id
fk5	○	non refillable detail id
fk6	○	refillable detail id
Description/Assumptions for PSN:
1. PRESCRIPTION may be issued during a VISIT. Or over the phone. A VISIT may issue zero or more PRESCRIPTION. -non transferable - fk4 2.  PRESCRIPTION must be issued for one MEMBER and a MEMBER may have sero or more PRESCRIPTION - non transferable - fk3 3. PRESCRIPTION must be issue by one PROVIDER of type doctor [ type check is programmatic] and a doctor may issued zero or more PRESCRIPTION - non transferable - fk2 4. PRESCRIPTION must have one DRUG and a DRUG may be present in zero or more PRESCRIPTION - non transferable - fk1 5. fk5 and fk6 are in arc- exclusive or, so optional here. 6. check constraint is required for arc exclusive or. Both can not be null, one and only one must be non-null of both in a row
NON REFILLABLE (NRE)
Key Type (pk, fk, uk)	Optionality ( "*", "○")	Column Name
pk	*	non refillable detail id
	*	unit count given
fk	*	prescription number
Description/Assumptions for NRE:
an instance must map to one valid PRESCRIPTION instance - non transferable
REFILLABLE (RFE)
Key Type (pk, fk, uk)	Optionality ( "*", "○")	Column Name
pk	*	refillable detail id
	*	no of refills available
	*	unit count per refill
fk	*	prescription number
Description/Assumptions for RFE:
an instance must map to one valid PRESCRIPTION instance - non transferable Programming is required to reduce no of refills available with each LABEL generation and it can not go below zero
COUNTRY (CTY)
Key Type (pk, fk, uk)	Optionality ( "*", "○")	Column Name
pk	*	country id
	*	name
Description/Assumptions for CTY:
e.g. Unite Sates
ZIP (ZIP)
Key Type (pk, fk, uk)	Optionality ( "*", "○")	Column Name
pk, fk	*	country id
pk	*	zip id
	*	zip code
Description/Assumptions for ZIP:
e.g. 30348
CONTACT (CTT)
Key Type (pk, fk, uk)	Optionality ( "*", "○")	Column Name
pk	*	ctt id
fk1	*	country id
fk1	*	zip id
	*	communication name
	*	address line 1
	○	address line 2
	*	phone
	○	fax
	*	email
fk2	○	ins comp id
fk3	○	provider number
fk4	○	memeber id
fk5	○	pme id
Description/Assumptions for CTT:
1. fk2, fk3, fk4 are in arc, one of these must be non-null,  But not more than one. For this check constraint is used. 2. Hospitals must mention their preferred mode of communication, for other its optional - additional programming required.
PREFERRED MODE (PME)
Key Type (pk, fk, uk)	Optionality ( "*", "○")	Column Name
pk	*	pme id
	*	pme name
Description/Assumptions for PME:
This defines preferred mode of communication for the contact details saved in table CONTACT - which in turn is used by provider/insurance/member

Subtype Mapping

2. Transform the STAFF supertype in the Global Fast Foods model, using the subtype or two-table implementation.

MANAGERS (MAR)
Key Type	Optionality	Column Name
pk	*	id
	*	fname
	*	lname
	*	dob
	*	salary
	*	budget
	*	tgt_rev

ORDER_TAKERS (OTR)
Key Type	Optionality	Column Name
pk	*	id
	*	fname
	*	lname
	*	dob
	*	salary
	*	ovt_rate

COOKS (COK)
Key Type	Optionality	Column Name
pk	*	id
	*	fname
	*	lname
	*	dob
	*	salary
	*	training

OTHERS (OER)
Key Type	Optionality	Column Name
pk	*	id
	*	fname
	*	lname
	*	dob
	*	salary

3. Identify the database rules for each part of the database implementations below.

Supertype implementations

• •         Table

o   Single table for super type and all subtype entities, no matter how many subtypes I have.

• •         Column

o   Supertype columns accommodated as is, no change in optionality.

o   Subtype columns accommodated as optional

o   One additional column appears in addition to the ones coming from super and sub entities. This column identifies which subtype this row belongs to. Possible values in this additional column may be taken from short names of subtypes. This column may be named as “maintableshortname_type”.

• •         Identifiers

o   Unique identifiers transform to primary and unique keys.

• •         Relationship

o   Relationships at supertype level mapped as usual, but the sub type level relationship is mapped as optional foreign key, even if it was meant to be mandatory in ERD for that subtype.

• •         Integrity constraint rules

o   A check constraint is needed to ensure that for each particular subtype, all columns that come from mandatory attributes are not null. Check constraint is needed to ensure business rules present in ERD.

Subtype implementations

• •         Table

o   One table per first level subtype.

• •         Column

o   All the attributes from supertype and current subtype are accommodated without losing optionality.

• •         Identifiers

o   Primary UID as primary key and secondary UIDs as unique keys come from supertype as is.

o   If sub type has a column which is unique for that subtype instances, this is also taken in as unique key, since every subtype has separate table.

• •         Relationship

o   Foreign key to represent relationship at super type level are accommodated as is without losing optionality. For the relations at sub type level, foreign key is implemented only in the table of that sub class with original optionality.

• •         Integrity constraint rules

o   Check constraint as in Supertype implementation is not needed here.

In case of Subtype implementation, if primary key need to have unique values across all the sub type tables, it requires additional programming.

Arc implementations

•         Table

o   Supertype and subtype entities generate one table for each entity.

•         Column

o   Each column gets all the attributes from entity of which it is mapped from, with original optionality.

•         Identifiers

o   Primary UID of the supertype level creates a primary key for  each of the tables.

o   All other unique identifiers mapped as unique keys in respective tables.

•         Relationship

o   Relationships mentioned at respective level are maintained as foreign keys without losing optionality.

•         Integrity constraint rules

o   “n” count additional columns are created in super type level table where “n” is no of subtypes. These are optional foreign key columns pointing to each subtype level table. These foreign key columns are optional because these 1:1 relationship b/w super type and subtypes is present in arc.

Additional check constraint is required to implement the arc.

  if we are implementing this, then the ‘primary key of subtype’ is already a foreign key to ‘primary key of super table’.

This part is done to implement exclusive or (arc), means no record in super class can skip to be of any subclass.

1.      My stand here, If, I am doing this, then why doing this should be bothered?

2.      Second thing, if I am doing this, then, I should have observed barred relationship in ERD in study material, I didn’ see that anywhere in study material. (Below mentioned)

See Below two diagrams:

Hierarchies and Recursive Relationships

• ·         A relationship between an entity and itself. –Recursive Relationship
• ·         A series of relationships that reflect entities organized into successive levels. –Hierarchal relationship

1. Develop two ER diagrams to represent the following situation. Develop one using a hierarchical structure and one using a recursive structure.

“Our company sells products throughout the United States. So we’ve divided the U.S. into four major sales regions: the Northern, Eastern, Southern, and Western regions. Each sales region has a unique region code. Each sales region is then divided into sales districts. For example, the Western region is divided into the Rocky Mountain, Northwest, Pacific Coast, and Pacific districts. Each district has a unique district code. Each district is made up of sales territories. The Rocky Mountain district is composed of three territories: Wyoming-Montana, Colorado, and Utah-New Mexico. The North-west district is made up of two territories: the Washington and Oregon-Idaho territories. The Pacific Coast district is composed of two territories: the California and Nevada territories. The Pacific District includes the Hawaii territory and the Alaska territory. Each territory has a unique territory code.

Then each sales territory is broken down into sales areas. For example, Colorado is made up of two sales areas: the Front Range and the Western Slope sales areas. Each sales area has a unique sales-area code.

Each salesperson is responsible for one or more sales areas and has a specific sales quota. We also have sales managers who are responsible for one or more sales districts and sales directors who are responsible for one or more sales regions. Each sales manager is responsible for the territories with his/her districts. We don’t overlap our employees’ responsibilities. Each sales area is always the responsibility of a single salesperson, and our managers' and directors' responsibilities don’t overlap. Some-times our salespersons, managers, and directors will have special assignments and will not be responsible for sales. We identify all our sales personnel by their employee IDs.”

Hierarchical: Assume that every region must have at least one district and so on up to areas.

Recursive:

• ·         To cover top and bottom entities in chain make optional on both end of pigs ear.
• ·         Type attribute may take values e.g. area, territory, district region etc.
• ·         To cover entities of type territory, relation b/w unit and employee on unit side becomes optional. (Additional information to be recorded with this : Each sales manager is responsible for the territories with his/her districts. But this does not mean I will store duplicate information with territories) 
•