Friday, September 16, 2016

Understanding CRUD Requirements

  • ·         One who gives expert or professional advice - consultant
  • ·         The practice of checking a data model for create, retrieve, update and delete functions that the business requires – CRUD analysis
  • ·         Used to perform calculations on data, modify individual data items, manipulate output for groups of rows, format dates and numbers for display, convert column datatypes. - functions
  • ·         No longer in use - obsolete

1. Identify the part of the CRUD analysis that best suits the task in the table.
• Create
• Retrieve
• Update
• Delete
Bring up
Look up

2. Relate CRUD analysis to a school enrollment environment. Consider the data or information used in a school and identify at least one example for each CRUD function.
a. Create:
Student enrolls in a subject, and enrollment details are saved in DB.
b. Retrieve:
Student logs in blackboard on first day of session, and he can see all the courses he enrolled to on home page.
c. Update:
Student goes to myPortal and gives a new mobile number since he took a new mobile with new number.
d. Delete:
Student no longer wants his parents to see his scores are report card. So, he goes to myPortal and removes the authorization.

Resolving Many-to-Many Relationships

  • ·         A relationship that participates in an entity's unique identifier. – barred relationship
  • ·         The product of the resolution of a many to many relationship. – intersection entity

1.      Resolve the M:M between TEACHER and CLASS as well as INTERPRETER and LANGUAGE. 
For each intersection entity, think of additional attributes like a UID.

For intersection entity InterpreterLanguageMap, interpreterId from teacher entity and languageId from class entity make unique identifier.

Relationship Types

  • ·         A relationship where each record in Table A can be related to one, and only one, record in Table B, and each record in Table B relates to one, and only one, record in Table A. 1:1
  • ·         A relationship where a single record in Table A can be related to one or more records in Table B, but a single record in Table B can only be related to one record in Table A. 1:M
  • ·         A relationship in which many records in one table match many records in another table. M:M
  • ·         Unnecessarily repetitive. - redundant

1. Identify the relationship types of the statements below
A snowboard instructor may instruct one or more snowboarders. Not mentioned: All snowboarders are not bound to use same instructor
A bicycle may be owned by a child. Not mentioned:  One bicycle will have only one owner, but a child can own multiple bicycles.
Classroom crayons may be used by students in a classroom
A passport belongs to a person. Not mentioned: ignore expired passports
A female elephant gives birth to an elephant. No mentioned: an elephant has one and only mother, but female elephant can give birth to multiple elephants.

2. Provide two examples for each relationship type.
Relationship Type
 I have only one SSN  XXXXXXXXX and XXXXXXXXX belongs only to me
  I have a unique valid driver license number, this number cannot be assigned to anyone else.
A bicycle may be owned by a child, child may own multiple bicycles.
 I have three passports, two are expired, and the current one is valid. But the passport, whether it is valid or not, must be of one person.
 Classroom crayons may be used by students in a classroom
 In my house there are 10 laptops and 4 family members, anyone can use any laptop and any laptop can be used by any family member.

3. Draw an entity relationship diagram to represent the following:
a. Each CLUB must be assigned to one and only one DEPARTMENT
b. Each DEPARTMENT may be responsible for one or more CLUBs
c. Each STUDENT may join one or more CLUBs
d. Each CLUB may be composed of one or more STUDENTs

Relationship Transferability

  • ·         Description of a relationship where an instance of A is related to an instance of B, and the association can be moved to another instance of B. – Transferable
  • ·         Description of a relationship where an instance of A is related to an instance of B, and the association cannot be moved to another instance of B - nontransferable

1. Draw ERDs for each of the following. Draw softboxes, relationship lines, and labels for each relationship in both directions. Indicate non-transferability when appropriate.
a. Each town may be the birthplace of many people. Each person must be born in one and only one town.
assuming world has no villages, only towns:
b. Each room may house one or more guests. Each guest may stay in one and only one room.

c. Each employee must work for one and only one department. Each department may have one or more employees.

d. Each hotel may be the host of one or more guests. Each guest may be hosted in one or more hotels. This one is tricky, generally when a guest is hosted in a hotel, he stays there, if he change hotel, previous hotel booking goes away, but here, problem says guest may be  hosted in multiple hotels

e. Each message must be addressed to one or more persons. Each person may be the addressee of one or more messages. A message is addressed here, not sent, so it is still transferrable.

f. Each garment must have one and only one price. Each price may be for one or more garments.

g. Each airline coupon must be used for one and only one destination. Each destination may be visited with one or more coupons. A coupon is generated one destination, if generated by mistake, new is reissued, so nontransferable here.

h. Each automobile must use one and only one tire size. Each tire size may be used by one or more automobiles. Tire size of an automobile is defined by design,   if the tire size need to be changed, means the automobile will be recalled. And a new unit will be given. So it is nontransferable.

i. Each child must have one and only one biological mother. Each mother must be the parent of one or more children.

j. Each person must be of one and only one blood type. Each blood type may classify one or more persons.

k. A person may be on one or more junk-mail lists. Each junk list may contain one or more persons.

l. Each student may learn from one or more teachers. Each teacher may educate one or more students.

m. Each school may be attended by one or more honor students. Each honor student must attend one and only one school.

n. Each fingerprint must belong to one and only one person. Each person must have one and only one fingerprint.

Documenting Business Rules

  • ·         A type of business rule that indicates the types of information to be stored and how the information elements interrelate. – structural business rule
  • ·         A formalized statement of the usual, customary, or generalized course of action or behavior for a business. – business rule
  • ·         A type of business rule that is workflow or business process related. (e.g., A has to happen before B, and then C has to happen at the same time as D.) This is also called a process business rule. –procedural business rule

1. Members of your design team have been working with the local hospital to develop a data model for their need to store information about patients, the patient's room number, the patient's doctor, drug prescriptions given, and specific drug information.
However, they all went on vacation and left you to figure out the model. They also failed to give you any of their documentation other than the entities and attributes illustrated here. Instead of going back to the hospital, which could reflect poorly on your company, you’re going to have to think about everything you know about hospitals!
Your task is to generate a list of business rules you think were used to arrive at the information shown here. Use your imagination. List 10 structural rules, 5 procedural rules, and 2 programmatic rules (rules to be addressed by computer applications in the future). State each rule as a single sentence.
Based on your set of business rules, draw the ERD.

·         Each floor in all buildings has a VIP room which should be allocated to normal patient only when all other normal rooms are full. – procedural business rule, need programming
·         Patient, who has past dues, will not be admitted, if there is a balance from past admissions – procedural business rule, needs programming
·         When a Physician goes on leave, all his patients are directed to one of the junior doctor in his team, available on the day of admission. – procedural business rule, need programming
·         If a prescription is raised for a drug, while giving medicine, if generic version in stock, it will be issued to the patient, unless doctor mandated use of branded drug in follow-up call by pharmacist. – procedural business rule, need programming
·         If the drug being issues to patient is going to expire in next 6 months, drug won’t be issued and drug label won’t be generated. – procedural business rule, need programming
·         When a DrugLabel is generated, means patient is issued a batch of dose, no of available refill reduce by one – procedural business rule, need programming
o   A room is identified by room number and building number, in same building there can be no rooms with same number – structural business rule
o   A room can be VIP room or ordinary room – structural business rule
o   Each room has the capacity of one patient – structural business rule
o   Patient may be issued a room, or not. – structural business rule
o   Patient must be issued a unique patient number at the time of registration. - structural business rule
o   A patient must be assigned a unique physician while admission – structural business rule
o   A Physician has unique physician number – structural business rule
o   Physician must have a valid license number to be employee of this hospital.- structural business rule
o   A Physician may have a senior Physician under whose supervision he works – structural business rule 
o   A prescription has unique prescription number – structural business rule
o   If a doctor prescribes multiple drugs, for each drug he will raise separate prescription – structural business rule
o   A prescription is bound to a unique patient – structural business rule
o   A drug must mention, whether it is generic or not – structural business rule.
o   Drug label must mention the expiration date of pills included in the container. - structural business rule
o   A drug label must be issued with prescription – structural business rule
o   No drug label may not generated for a prescription, if patient condition improves before the drug label is generated, but doctor don’t cancel such prescription, just in case symptoms come back soon. – structural business rule.
Even though DrugLabel (if seen as a print out) generated contains a lot of information from Prescription, I won’t repeat information, since it has prescriptionId in the database.

Supertypes and Subtypes

  • ·         All subtypes are listed without omission. - Exhaustive
  • ·         A means of classifying an entity that has subtypes. - Supertype
  • ·         Something an entity may be split into based on common attributes and/or relationships. - Subtype
  • ·         Each instance of a supertype is an instance of only one possible subtype. – Mutually Exclusive

1. Identify which item off of the following list is the supertype entity and which items are the subtypes of that entity.
a.       Amputation = ______Subtype of d_______________
b.      Visual Impairment = _____Subtype of d________________
c.       Hearing Impairment = ______Subtype of d_______________
d.      Disability = ___SuperType of the rest__________________
e.       Paralysis = ____Subtype of d_________________

2. For each rule, indicate whether the rule is applicable to supertypes or subtypes.
__Subtype________ They share common attributes
__SubType________ They inherit all attributes and relationships of the entity
____SubType______ It never exists alone
____SuperType______ It contains the attributes held in common by all instances

3. Name three things you consider when modeling supertypes and subtypes.
·         Is this subtype a kind of supertype?
·         Have I covered all possible cases? (Exhaustive)
·         Does each instance fit into one and only one subtype? (mutually exclusive)

4. Find the incorrect subtypes in the illustration. Explain why you think the subtype is incorrect. Adjust the model to improve it.

·         Is this subtype a kind of supertype – a vehicle may also be non-automobile
·         Have I covered all possible cases? – in both BUILDING and AUTOMOBILE all possible cases are not covered
·         Does each instance fit into one and only one subtype – a sedan is an enclosed automobile body having two or four doors and seating four or more persons on two full-width seats. Means “4-DOOR VEHICLE” and ‘SEDAN” are not mutually exclusive.
To improve AUTOMOBILE, 4-door may be removed and a new SubType OTHER may be added.
Same in BUILDING, to accommodate anything else than HOUSE, OTHER may be added.

5. Read the following scenario and construct an ERD that contains at least two subtypes of the entity PRODUCT. Show clearly which attributes belong to the entity supertype, and which belong to the subtypes. Identify a UID for the entity.
“Our shops sell several kinds of women’s clothing, including dresses, skirts and blouses. Of course each product has a name, a description, and a price. Oh, and sizes too: all products have a waist size. Dresses and skirts have a hem length but blouses don’t. Dresses and blouses have a chest size, but skirts don’t.”

Dresses have generally two parts that is why they have hemlength and chestsize.

Matrix Diagrams

  • A grid-like drawing that can be used to discover and record relationships between entities in an entity- relationship model - matrix diagram

1.      Read the business scenario and review the ERD. Using the matrix diagram, make up two or more possible relationships between PHOTOGRAPH and the other entities that make sense for the business.

“I’m an amateur photographer. I own several cameras and am always taking pictures of different subjects. I’m trying to keep track of which camera and type of film perform best under certain conditions—indoor light, outdoor light, etc.—so when I have my films developed, I note down which camera I used. When the pictures come back, I note the subject and conditions. Each picture always features one subject. A subject could be a view, a person or group of persons, or an object or group of objects.”
Two possible relationships are:


 Used to take with

 Taken with


 Featured in

2. Complete the matrix diagram below, and construct an ERD from it.

City For Race
Race Type
Running Event

City For Race
 visited by

Race Type
 Chosen by

 Contained in
Running Event
 Attended by
 Happens in

1.      City of race must be visited by many runners, otherwise there is no race and hence, it’s not a city of race. Runner may visit multiple city of race since he is very enthusiastic.
2.      RaceType must be chosen by many runners otherwise, racetype will be dropped from the event. Runner may wish to pick up multiple RaceTypes ,  he may be versatile.
3.      Event must be attended by multiple runners or there is no event. Runner may choose to attend multiple events.
4.      City of race must host running event/events or else it’s not a city of race. An event must happen in a city.
5.      RUNNING event must have one or more race types, runner won’t pay baseball in running event, will they run small or big distances. A racetype may be present in multiple events happening around.