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
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.
Problem 2: Aircraft
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.
Problem 3: Restaurant Guide
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.
Problem 4: Music Bands
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.
Given a song, can we use this data model to match it to its band? Or is something missing?