- · Required – Mandatory
- · Incapable of being perceived by the senses - Intangible
- · Any combination of attributes and/or relationships that serves, in all cases, to uniquely identify an instance of an entity - Identifiers
- · An occurrence or example of an entity - Instance
- · A characteristic; something that describes, quantifies, or specifies an entity - Attribute
- · Not required - Optional
- · A named thing or category of things that is significant to the business and about which data must be known - Entity
- · A value that is unavailable, unassigned, unknown, or empty; however, it is neither a zero nor a space - Null
- · Highly changeable - Volatile
- · Perceptible to the senses, especially the sense of touch - Tangible
- · A classification identifying one of various types of data, stating the possible values for that type, the operations that can be done on that type, and the way the values of that type are stored – Data Type
- · Can only have one value at any point for each instance in the entity – Single Valued
1.
Consider the entity STUDENT. You are all
students in a class. However, each one of you is a unique instance of that
entity. Your teacher will lead you through an exercise to demonstrate this.
Our attribute values are
different, say first name, last name etc.
2.
Think about the last time you went to get a
haircut, permanent, manicure, or other service from a barbershop or hair salon.
Discuss the business of a barbershop or hair salon. Name the
entities that are the “main things” about this business. Give examples of
instances for each entity.
a)
BARBER: name,
specialty(haircut/threading/massage/other), time of working
b)
COUNTER: Type (main/ individual of barber’s/hair
washing counter/Sample Product sales/others), color
c)
HAIR CUTTING DEVICE: Type (seasor/electric
machine/other)
3. The goal of this activity is to learn to make a
distinction between an entity, an attribute, and an instance of an entity.
List which of the following concepts you think is an:
• entity
• attribute
• instance.
If you determine that one is an entity, then give an example
of an instance. If you deter-mine that one is an attribute or an instance, give
a sample entity name. For the last three rows, find a concept that fits.
Concept
|
Entity? Attribute? Instance?
|
Example instance or Entity
|
Vehicle
|
Entity
|
corolla, school bus, civic
|
Diet Cola
|
Instance
|
Drink
|
Price
|
Attribute
|
Toy,Drink,Vehicle
|
Customer number
|
Attribute
|
customer
|
Green
|
Attribute
|
car, train, grass
|
4. The goal of this practice is to recognize attributes for
an entity.
The three entities that play a role in a DJ business: SONG,
EVENT, and CUSTOMER are listed as the first three column headings of the table
below. The fourth column contains a collection of attributes. Use a check mark
to indicate if the attribute listed could be an at-tribute for the entities
listed. (For example, could Title be an attribute for Song, for Event, and for
Customer?)
SONG
|
EVENT
|
CUSTOMER
|
|
Yes
|
Yes
|
Title
|
|
Yes
|
Yes
|
Description
|
|
Yes
|
Venue
|
||
Yes
|
First Name
|
||
Yes
|
Phone Number
|
||
Yes
|
Release date
|
||
Yes
|
Last Name
|
||
Yes
|
Yes
|
Type
|
|
Yes
|
Email address
|
5. From the list of attributes provided, identify which
attribute could be the unique identifier of each entity.
Entity: STUDENT
Attributes: student id, first name, last name,
address
Entity: MOVIE
Attributes: title, date released, producer, director
Entity: LOCKER
Attributes: size, location, number
6. Read the given business scenario and walk through the
steps below.
“I’m the owner of a small movie rental store. We have over
3,000 movies that we need to keep track of.
“Each of our movies has a DVD or VHS tape 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 track which DVD
or VHS contains the movie. A movie can be either DVD or VHS format.
“We always have at least one DVD or VHS tape for each movie
we track, and each DVD or VHS tape is always a copy of a single, specific
movie.
“Our DVDs and VHS tapes are very long. We don’t have any
movies that require multi-ple DVDs or VHS tapes.”
a.
Write a list of nouns used in the scenario.
MOVIE, DVD, VHS
b.
Name each entity.
MOVIE, CATEGORY,
MEDIA, MEDIAFORMAT
c.
Is each instance of the entity uniquely
identifiable? Which attribute or attributes could serve as the unique attribute
to identify the entity?
MOVIE (Attributes: #Id,*
Title, * CategoryId, *Year ) -
generally with same Title two movies may not be released in same year, but what
if? So, Id is primary key. I won’t even make a composite unique key on Title
and year. CategoryId is foreign key to CATEGORY (Id)
CATEGORY (Attributes:
#Id, #Name). Even though Name of
category is uniquely identifiable, but in physical model, I will make ID as a
serial and primary key. On Name will put unique constraint.
MEDIA (Attributes: #Id, *FormatId, *MovieId), Id is the unique serial primary key. FormatId
is the foreign key to MEDIAFORMAT.
MEDIAFORMAT(
Attributes : #Id, #Format) FORMAT column
will be unique and sufficient , under physical model, it may have three
instances of MEDIAFORMAT (DVD, VHS, Others). But I will also create an Id
column as primary key (+ Serial) to refer as foreign key in MEDIA.
7. Identify the business rules contained in the following
scenario.
“We are frequently asked for movies starring specific
actors. John Wayne and Julia Roberts 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.”
This adds two more
entities here one actor and one intersection entity, we need intersection
entity, because we cannot stop one actor to work in multiple movies, and almost
always a movie has multiple actors.
ACTOR(Attributes:
#Id, *Name, ◦RealName, ◦DOB) it is quite possible that some stars are not
revealing DOB and real name anywhere. Name (screen name) may be same for two
actors
ACTORMOVIEMAP (
Attributes: #ActorID, #MovieId,
*IsMainCastingActor) We got barred relationships here, so combination of
ActorId and MovieId will make composite primary key, IsMainCastingActor is required to show main
names on the movie posters, but in full casting list of movie other actor names
may also be shown.
8. Identify the business rules contained in the following
scenario.
“We have lots of customers. We rent videos only to people
who have joined our ‘video club.’ To belong to our club, they must have good
credit. For each club member, we’d like to keep their first and last name,
current phone number, and current address. And, of course, each club member has
a membership number.
CUSTOMER( Attributes: #clubMemberID, *firstName, *lastName, *phone,
*address), since if customer is not member of club, we don’t rent him, and club
membered is always unique, it is right candidate to be primary key here. “Must have good credit” need to be
programmatically handled by monthly / quarterly jobs running on server. If not
good credit found, send notice to customer and throw him out.
“Then we need to keep track of what movie each customer
currently has checked out. A customer may check out multiple video tapes at any
given time. We just track current rentals. We don’t keep track of any rental
histories.”
This makes us to add
an optional attribute to MEDIA entity called ◦RenterId which is a foreign key
to customer table. It is optional because at a given point of time, all the
media in store will not be rented out, if it does I will become billionaire
soon. J. RenterID can not be kept unique because a
customer can rent out multiple media if he wish to.
9. Age is an example of what type of attribute?
Volatile
10.Birth date is an example of what type of attribute?
nonvolatile
11.Read the business scenario of a fast-food restaurant
below:
We are a small fast food business. Our menu features food
items that can be ordered by a customer. A customer places an order at the
counter and indicates what food items he/she would like on that order.
Lately we've noticed that we have some regular customers, so
we started asking them for information -- such as name and address, so we can
mail them coupons when we have specials.
a.
Using the restaurant entities ORDER, FOOD ITEM,
AND CUSTOMER, identify the attributes for each entity.
ORDER ( #Id, ◦CustId,
*DateTimeOfOrder): some customers may not be present in CUSTOMER, so CustId is
optional. CustId is foreign key to Customer, it is not unique because it can be
Null or present in multiple orders. Id is UId and right candidate for primary key here.
CUSTOMER( #Id, ◦Name,
◦Address). Current scenario says a customer comes into Customer collection only
if he gives personal details: Name and Address which means, these two may be
marked as mandatory. Just to cater future scenarios, I have kept name and
address as optional. Id is UId and right candidate for primary key here.
FOODITEM ( #Id,
*Name, *Price) price is not optional, for free food items, give value as zero.
A food item cannot be without a name. .
Id is UId and right candidate for primary key here.
ORDERFOODMAP (
#OrderId,#ItemId, *Count) we have composite primary key ( and unique
identifier) here on order and item Ids. One order can have multiple fooditems ,
even multiple instances of same food item.
Rather than repetition of items saved as separate rows, I will prefer to
have mandatory count column.
b.
Where possible, indicate whether the attribute
is mandatory or optional.
See above
c.
Where possible, pick out the UIDs for each
entity
See above
