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.
  
•