Problem Set 2 – Identifying Entities and Attributes

In an earlier article on data modeling we promised to give you a set of exercises to practice finding entities and attributes. Here is the second installment of our problem set. Enjoy.

Problem 1: Countries

Description:

Find the right entities and their attributes to represent all the countries in the world, their interior regions (which can be called states, provinces, or regions) and their cities. We want to represent each country’s name, continent, date of independence, type of government and population. For each region (or province, state, etc.) we wish to store the capital city, the name of the governor, and the population. Finally, for each city we want to have the name, founding date, population, and the number of schools per inhabitant. We would also like to represent what every country calls its interior regions.

Solution:

From the domain problem description, we can clearly identify 3 entities: Country, Region and City.

For the Country entity we find the following attributes: name, governmentType, population and independenceDay.

For the Region entity, we discover the attributes name, governorName, population and capitalCity.

For City, we have name, foundationDate, population and schoolsPerHabitant.

Data modelling is done in stages called iterations. At this point, we iterate. We go back to the Country entity and add a new attribute. The last sentence of the description asked us to represent each country’s name for its interior regions. That name should be at the country level, so we will add a new attribute called categoryRegion to the Country entity.

Question:

The population is represented at the Country, Region and City levels. Do you think this is correct? Is there duplicate information being stored? How do you allow for this?

↑ Click the logo to preview the model in your browser | Download the model as a png file

Problem 2: Aircraft

Description:

A new budget airline wants to enter the market and needs a simple system to manage its assets. To help us build the right system, we asked a few people to define key information for any new airline. Based on the comments below, suggest a few entities with attributes for an airplane management system.

An experienced pilot:

I’ve worked at quite a few airlines and I’ve spent thousands of hours in the air. They always ask for the same information when I change employers. First, they want to know my name, my birthday – many airlines only employ pilots within a certain age range. And they always need to check my certification – I have a special license number which helps them do that. The number of hours flown is also very important; it tells them a lot about the pilot. Lucky me that I’m so experienced! And, of course, I’m always given an employee number – I don’t know why, but they refer to me using the number instead of my surname.

An aircraft manufacturer’s representative:

Every airline needs aircraft. The whole description of any airplane is very complex and I could go on describing my products for ages, but the white-collar guys from airlines are usually only interested in the basic info. Of course, they want to know how many passengers can fly a in an airplane – helps them to calculate the costs, benefits etc. And they always ask about the cruising range so they know how far each plane can fly. Of course, they need the manufacturer name and the model name to put in their books. Oh, and from what I heard, they always give special internal numbers to any aircraft they purchase.

An air traffic controller:

There are some basic facts that we keep about each flight at our airport. The flight must have a certain number for identification reasons, (like FG 432). We need to know the departure and arrival airports. And time is very important, too. We store not only the scheduled departure and arrival time, but also the real times – airplanes can be late or can even arrive ahead of schedule.

Solution:

In our description, we clearly identify 3 entities: Aircraft, Pilot and Flight. Then we find the attributes of each entity.

For the Aircraft entity we have manufacturer, model, passengerCapacity, cruisingRangeMiles and internalNumber.

For the Pilot entity we discover the following attributes: employeeNumber, firstName, lastName, birthDate, licenseNumber and flownHours.

Finally, for Flight we identify the flightNumber, departureAirport, destinationAirport, scheduledDepartureTime, scheduledArrivalTime, realDepartureTime, and realArrivalTime.

To simplify this data model, we make the assumption that all flights are scheduled on all days of the week.

↑ Click the logo to preview the model in your browser | Download the model as a png file

In some cases, our domain description includes attributes that we must ignore. For example, we decided to exclude flight duration from this data model because we can calculate it from the real arrival and real departure times.

Problem 3: Restaurant Guide

Description:

Samuel wants to create an online restaurant guide. There are many such websites already, but he wants to focus on the particular dishes available rather than on the restaurant themselves. He’s really enthusiastic about his idea and this is how he described it to us:

I want to describe restaurants in detail on my site, so I need the basic stuff like their names and addresses. The address must be precise: not only the street and the number, but also the city, state and country. Yes, country; I want to go international! Besides, I want each of them to be given a particular style, like, you know, Chinese, Italian, or something like that. Each of them will be ranked with a certain number of stars.

More importantly, I want to focus on the food! Restaurants serve thousands of meals, and for each of them, I need the dish’s name and type – appetizer, main course, or dessert. There are various appetizers in various countries, so I need to store information about appetizer origins, too. And for main courses… well, I think it will be nice to provide the number of calories for people on diets. Desserts should also contain this sort of information.

And I want EACH dish to be shown, together with its current price! Oh, that reminds me: let’s put beverages on there too. The name, the price… and maybe the level of alcohol, come to think of it.

Based on the above description, suggest a few entities and their attributes for Samuel’s online restaurant guide.

Solution:

The first entity we have is Restaurant with the attributes of name, addressStreet, addressNumber, city, state and country. Other attributes in Restaurant are: stars and style.

Our next idea could be to create an entity called Meal and give it the attributes name, type and price. However, if we read the complete problem description, we will find specific attributes for desserts, main courses and appetizers. So we decide to scrap Meal and go with 3 entities: Main_course, Appetizer and Dessert.

For MainCourse, we will have the following attributes: name, category and price.

For the Appetizer entity, we have attributes called name, country and price.

For Dessert we find the attributes name, calories and price.

Finally, the Beverage entity with has the attributes name, alcoholLevel and price.

↑ Click the logo to preview the model in your browser | Download the model as a png file

Problem 4: Music Bands

Description:

A music production company wants to model the world of music bands. We met with one of its representatives and asked him a few questions. Read the interview below and find the right entities and their attributes for a model of music bands.

Vertabelo: What kind of people are there in the world of music?

Representative: Many, but I think we just need a few. Bands consist of singers and musicians. And, of course, their managers. For all of them, we want their first and last names in the system. Singers and musicians usually have a nickname, too. Musicians play a certain instrument and singers have a particular voice type, like soprano or tenor.

V: What about managers? How do you keep in touch with them?

R: It depends. Some of them prefer cell phones for quick communication, others like to be sent emails so they can think everything through. I think we need both kinds of information here.

V: And all of these people…

R:…form music bands, yes. Each band has a name, of course. They usually play various kinds of music, but we always assign them to only one style, like rock or metal. That’s important. We need to know how long they’ve played together, because young bands have a tendency to come up and disappear very quickly. We usually want to know when they played their most recent concert and what the ticket price was.

V: Do you need anything else?

R: We need to store information about songs. Wow, songs are complicated. They have specific kinds of lyrics, a certain key, a number of instruments involved... really complicated stuff.

V: And is all of this important for you?

R: Well, yes, but we actually have a system for songs already, so here we can… well, I think we’ll be good with just the song name and duration. And maybe the creation date.

Question:

Given a song, can we use this data model to match it to its band? Or is something missing?

Solution:

The first entity we find is MusicBand with the attributes name, mainStyle, foundationDate, lastShowDate and lastShowPlace.

The next entity is Musician, where we have the following attributes: firstName, lastName, nickName and instrument.

The Singer entity pursues a similar pattern: we have as attributes the singer’s firstName, lastName, nickName, and voiceLevel.

Our next entity is Song, which has the following attributes: name, duration and creationDate.

Finally, the last entity we identified is Manager; it has the attributes of firstName, lastName, emailAddress and cellPhone.

↑ Click the logo to preview the model in your browser | Download the model as a png file