Entities, Instances, Attributes and Identifiers

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