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