Friday, September 23, 2016

UIDs and Normalization

  • ·         Unique identifier - UID
  • ·         A UID that is a single attribute. -  Simple UID
  • ·         A UID that is a combination of attributes. – Composite UID
  • ·         A UID that does not occur in the natural world but is created for identification purposes in a system. – Artificial UID
  • ·         One of several UIDs that could identify something. – Candidate UID
  • ·         A candidate UID that is the primary identifier of something. Primary UID
  • ·         A candidate UID that also identifies something, but is not the primary UID. – Secondary UID

1. Match the type of Unique Identifier to the entity.
a. Simple UID





b. Composite UID
c. Composite UID comprising an attribute and a relationship

d. Primary Key & Secondary Key

2. Consider an entity for Library Member. Sometimes an entity can have more than one candidate UID. Describe a scenario where you would want more than one candidate UID for a Library Member – why would you want to find the member in the database using alternative information?
In libraries here, we are issued a library card with unique member ID on it, but it is quite possible that, I lose my library card. But when we open library card, they took our address, name, email address and phone no.  With same address there could be multiple family members joining the same library system. Two members may have same name. But email address or mobile no should be sufficient to identify a person, so two secondary UID’s [candidate UID which is not marked primary] are Mobile no, email address.

3. Describe how you would identify the entities listed below, making up any attributes and relationships you consider appropriate. For example, for the Student entity you may say that the UID is comprised of the first name, last name, and address. Or you might suggest that the Student entity contains an artificial UID called student number.

a. A city
#City
#State
#Country
In final design, we may observe Hierarchal relationship, rather than city and country names stored in the same Entity. Like, #CityId and a barred relationship from State Entity making a composite UID

b. A contact person for a customer
#emailaddress
(#)directphonenumber
*name

c. A train
#TrainId
It’s artificial UID. I am not using factors like routes, start point, end point here, because I may want to run different trains on same routes on different days and timings. So schedule entity may use route and train from two entities.

d. A road
#RoadName
#City
#Sate
#Country
I never saw two roads in the same city which have same name.

e. A financial transaction (for example: transfer funds)
#SenderAccount
#SenderBank
#ReceiverAccount
#ReceiverBank
#Amount
In final design, there may be an artificial UID to identify transaction and nontransferable relationships from sender bank account and receiver bank account Entities, with amount marked mandatory. Sender and receiver bank accounts Entities will have barred relationships with bank entity.

f. An award (for example: movies)
#AwardID
*Name
○Description
AwardId is artificial UID here.
AwardId from AWARD Entity,  PersonId from PERSON Entity and year make UID for DISTRIBUTION Entity.

g. A painting
#PaintingName
#AuthorId from Author Entity as non-transferrable barred relationship.


4. Identify one of the issues with respect to a national identification system. Provide your point of view on the issue.
They use our SSN as UID. Its artificial UID, what if you know my SSN, think what all things you could do!!! It’s not profiling of my speech, not my fingerprint and not my eyes.

5. You have previously reviewed the video store business requirements stated below:
“I’m the owner of a small movie hire store. We have over 3,000 DVDs that we need to keep track of.
“Each of our DVDs has a disk number. For each movie, we need to know its title and category (e.g., comedy, suspense, drama, action, war, or sci-fi). Yes, we do have multiple copies of many of our movies. We give each movie a specific ID, and then we track which movie a disk contains. A disk may be either High Definition (HD) or BlueRay format. We always have at least one disk for each movie we track, and each disk is always a copy of a single, specific movie. Our disks are very big, although we don’t have any movies that require multiple disks.”
For this activity read the new business requirements below and resolve the M:M relationship. Identify UIDs for all the entities that require multiple disks.
“We are frequently asked for movies starring specific actors. John Wayne and Kathe-rine Hepburn are always popular. So we’d like to keep track of the star actors appearing in each movie. Not all of our movies have star actors. Customers like to know each actor’s “real” birth name and date of birth. We track only actors who appear in the movies in our inventory.
“We have lots of customers. We only rent DVDs to people who have joined our DVD club. To belong to our club, they must have good credit. For each club member, we’d like to keep the first and last name, current phone number, and current address. And, of course, each club member has a membership number.
“Then we need to keep track of what DVDs each customer currently has checked out. A customer may check out multiple DVDs at any given time. We just track current rentals. We don’t keep track of any rental histories.
Assumption:
  • ·         we’d like to:: I consider this not mandatory, real name may be different than popular name, but unknown. DOB of actor may be unknown.
  • ·         we’d like : I consider this must, because I am giving my property to the person. So while giving club membership, I will take all these details.
  • ·         Actors who don’t appear in a movie coming up in future/already in stock: We don’t track ( so solid line in purple ‘Present  in’ in diagram). Also, there may be a low budget  movie which don’t have any star actor which we want to track ( so dotted line in purple ‘performed by’ in diagram).
  • ·         I may have a movie, for which I don’t have a media available, but it is coming in stock, say tomorrow. If you want to reject this assumption, please consider pink dotted line in below diagram as solid.
  • ·         I assume that a movie belongs to only one category and must be under one category; for special movies, others is one instance of category Entity.
  • ·         Under IssueLog, I may have put a composite UID on mediaInventoryNumber, ClubmemberId and IssueTime. But, I have chosen artificial UID here a separate IssueLogId.
  • ·         Even though problem says, ‘Issue time’, ‘issued till’ and ‘is returned’ is not required here, simply delete the row in issue log on return, but it doesn’t make sense in real world, if you want to stick to what problem says, I don’t even need ISSUELOG Entity, Simply create a  relationship b/w MEDIA and Clubmember will be sufficient, optional on Media side (dotted on media side because my all movies are not out at all times J), many on media side ( crowfoot on media side since a customer may take multiple movies), optional on clubmember side ( dotted on clubmemberside, may take ‘not must’), single tow on clubmemberside ( a media instance cannot be taken by multiple customer instances at same time).
  • ·         Here we don’t track disk number but InventoryNumber,  an inventory may have multiple disks. But all media under same inventory number have same format, so no worries. And it won’t be rented partiallyJ.






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
Alter
 Update
Bring up
 Retrieve
Change
 Update
Discard
 Delete
Enter
 Create
Find
 Retrieve
Import
 Create
Input
 Create
Load
 Create
Look up
 Retrieve
Modify
 Update
Print
 Retrieve
Purge
 Delete
Read
 Retrieve
Record
 Create
Remove
 Delete
Report
 Retrieve
Trash
 Delete
View
 Retrieve

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
Type
Statement
 M:M
A snowboard instructor may instruct one or more snowboarders. Not mentioned: All snowboarders are not bound to use same instructor
1:M
A bicycle may be owned by a child. Not mentioned:  One bicycle will have only one owner, but a child can own multiple bicycles.
 M:M
Classroom crayons may be used by students in a classroom
 1:1
A passport belongs to a person. Not mentioned: ignore expired passports
 1:M
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
Example
One-to-one
 I have only one SSN  XXXXXXXXX and XXXXXXXXX belongs only to me
One-to-one
  I have a unique valid driver license number, this number cannot be assigned to anyone else.
One-to-many
A bicycle may be owned by a child, child may own multiple bicycles.
One-to-many
 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.
Many-to-many
 Classroom crayons may be used by students in a classroom
Many-to-many
 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.