Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss
Database Modeling help???
36 views
Skip to first unread message
BadnBull_Options
Jun 1, 2012, 5:35:40 AM6/1/12
to
Sorry for being a newb, for whatever reason I just can't wrap my head
around this. I need some help in figuring out the entities and their
relationships here, I already sent in a couple of submissions, and I
get the impression my professor is getting a bit ticked off.
Film Showings
The Cinema Guild Theatre Group wants the following collection of 21
data items to be implemented in a database to manage its member
theatres in several towns. Provide the 3NF LDM that satisfies these
requirements.
Your answer must be the minimum required for a valid logical model
solution.
No extraneous entities or relationships.
Do not add *any* data items to the 21 items shown.
You must label with a name each entity and each relationship (between
entities).
Check carefully that you have satisfied all constraints A through K.
Actor
Address of Theatre
Adult Count
Adult Price
Child Count
Child Price
Date
Director
Movie Name
Movie Rating
Movie Review
Number of Screens
Screen Number
Senior Count
Senior Price
Student Count
Student Price
Theatre Name
Theatre Phone Number
Time of Showing
Town
The following describe key business rules, definitions, requirements
and relationships between data items:
A: Theatres can show a "double bill" (2 films for 1 admission price)
or a "triple bill" (3 films) or a "festival" (a larger number of films
for one price).
B: The film distributor requires each theatre to report the count of
each type of ticket sold by showing. A showing is one start of a movie
on a particular day at a specific time. Movies shown together (double
bill, etc.)
have one start time.
C: A given movie may be showing at more than one theatre at the same
time.
D: The movie review is a short paragraph quoted from a national
magazine. The movie's rating is also based on non-local information.
E: The database needs to track all principal actors in a movie, e.g.
co-stars Bette Middler and Carrie Fisher.
F: Theatres sometimes run film festivals, where they show several
films starring the same actor. (Don't handle the festival separately,
just be sure that your design for individual movies handles it.)
G: Theatres have four classes of admission with separate prices:
adults, students, children, and senior citizens.
H: All prices are determined by the time of day and the day of the
week (i.e. the date). Therefore, weekend and holiday prices can be
different than weekday prices. Prices can be different in each
theatres showing the same film at the same time.
I: A theatre is in only one town, but a town can have more than one
theatre.
J: Most of the theatres in the theatre group have become multi-screen
centers, as specified by the "Number of Screens" data item for each
theatre. For each showing, the screen number must be recorded to
identify the actual room in which the screening will occur. Date and
time alone do not uniquely identify a showing.
K: Assume that a movie has only one director, but that director film
festivals can be held (e.g. showing five Alfred Hitchcock or Woody
Allen movies).
He's very specific about the format of the model, should be described
in text format describing the model, relationship and verb which
describes the relationship. For instance, the first entity which is
"theatre" should be as follows:
Model
Theatre (Theatre Name(PK), Address of Theatre, Town, Theatre phone
number, # of Screens)
Relationship
Shows Theatre 1:M Movie
If you guys could help me out this would be a huge help, so I can move
on to other assignments and get this database class behind me, plus I
don't wanna tick him off anymore than I have to.
Many thanks!
around this. I need some help in figuring out the entities and their
relationships here, I already sent in a couple of submissions, and I
get the impression my professor is getting a bit ticked off.
Film Showings
The Cinema Guild Theatre Group wants the following collection of 21
data items to be implemented in a database to manage its member
theatres in several towns. Provide the 3NF LDM that satisfies these
requirements.
Your answer must be the minimum required for a valid logical model
solution.
No extraneous entities or relationships.
Do not add *any* data items to the 21 items shown.
You must label with a name each entity and each relationship (between
entities).
Check carefully that you have satisfied all constraints A through K.
Actor
Address of Theatre
Adult Count
Adult Price
Child Count
Child Price
Date
Director
Movie Name
Movie Rating
Movie Review
Number of Screens
Screen Number
Senior Count
Senior Price
Student Count
Student Price
Theatre Name
Theatre Phone Number
Time of Showing
Town
The following describe key business rules, definitions, requirements
and relationships between data items:
A: Theatres can show a "double bill" (2 films for 1 admission price)
or a "triple bill" (3 films) or a "festival" (a larger number of films
for one price).
B: The film distributor requires each theatre to report the count of
each type of ticket sold by showing. A showing is one start of a movie
on a particular day at a specific time. Movies shown together (double
bill, etc.)
have one start time.
C: A given movie may be showing at more than one theatre at the same
time.
D: The movie review is a short paragraph quoted from a national
magazine. The movie's rating is also based on non-local information.
E: The database needs to track all principal actors in a movie, e.g.
co-stars Bette Middler and Carrie Fisher.
F: Theatres sometimes run film festivals, where they show several
films starring the same actor. (Don't handle the festival separately,
just be sure that your design for individual movies handles it.)
G: Theatres have four classes of admission with separate prices:
adults, students, children, and senior citizens.
H: All prices are determined by the time of day and the day of the
week (i.e. the date). Therefore, weekend and holiday prices can be
different than weekday prices. Prices can be different in each
theatres showing the same film at the same time.
I: A theatre is in only one town, but a town can have more than one
theatre.
J: Most of the theatres in the theatre group have become multi-screen
centers, as specified by the "Number of Screens" data item for each
theatre. For each showing, the screen number must be recorded to
identify the actual room in which the screening will occur. Date and
time alone do not uniquely identify a showing.
K: Assume that a movie has only one director, but that director film
festivals can be held (e.g. showing five Alfred Hitchcock or Woody
Allen movies).
He's very specific about the format of the model, should be described
in text format describing the model, relationship and verb which
describes the relationship. For instance, the first entity which is
"theatre" should be as follows:
Model
Theatre (Theatre Name(PK), Address of Theatre, Town, Theatre phone
number, # of Screens)
Relationship
Shows Theatre 1:M Movie
If you guys could help me out this would be a huge help, so I can move
on to other assignments and get this database class behind me, plus I
don't wanna tick him off anymore than I have to.
Many thanks!
Ron Weiner
Jun 1, 2012, 6:28:06 AM6/1/12
to
It happens that BadnBull_Options formulated :
And when you get a real job in the real world would you like us to do
that work too, just so the boss dosen't fire you?
As for me, No thank you!
The whole purpose of this exercise is to learn something, not to get a
passing grade. Shame on you.
Rdub
that work too, just so the boss dosen't fire you?
As for me, No thank you!
The whole purpose of this exercise is to learn something, not to get a
passing grade. Shame on you.
Rdub
Bob Barrows
Jun 1, 2012, 8:14:29 AM6/1/12
to
BadnBull_Options wrote:
> Sorry for being a newb, for whatever reason I just can't wrap my head
> around this. I need some help in figuring out the entities and their
> relationships here, I already sent in a couple of submissions, and I
> get the impression my professor is getting a bit ticked off.
>
<snip>
> Sorry for being a newb, for whatever reason I just can't wrap my head
> around this. I need some help in figuring out the entities and their
> relationships here, I already sent in a couple of submissions, and I
> get the impression my professor is getting a bit ticked off.
>
> If you guys could help me out this would be a huge help, so I can move
> on to other assignments and get this database class behind me, plus I
> don't wanna tick him off anymore than I have to.
>
I must admit to having the same reaction as Ron's. You'll be more likely to
> on to other assignments and get this database class behind me, plus I
> don't wanna tick him off anymore than I have to.
>
get help if you tell us about your approach to the problem, the reasons the
professor gave for rejecting your initial submissions, what, if anything,
you learned from those failed submissions ... something to indicate that
you're making an effort and just need a pointer or two to get you going in
the right direction, rather than trying to get us to do your work for you.
Frankly, everything you need to give an answer is provided in the problem
statement. Show us what you've tried.
TotalNewb
Jun 2, 2012, 9:29:35 PM6/2/12
to
Here's my third submission, apparently it's still has some issues, for instance restricting the rating of a particular movie to only one rating, the Actor entity restricts an actor to only one film in his entire career, also the first 3 relationships are incorrect. Also, I'm a little puzzled as how to approach the Count and Price relations. Any feedback would be great guys?
Please see below:
MODEL
Theatre (Theatre Name(K), Address of Theatre, Town, Theatre Phone Number, # of Screens)
Showing ((Time of Showing, Date, Screen #)(K), Theatre Name, Movie Name
Movie (Movie Name(K), Movie Review)
Rating (Movie Rating(K), Movie Name)
Actor (Actor(K), Movie Name)
Director (Director(K), Movie Name)
Count ((Date, Theatre Name, Time of Showing) (K), Adult Count, Child Count, Student Count, Senior Count)
Admission ((Date, Theatre Name, Time of Showing)(K), Adult price, Child price, Student Price, Senior price)
RELATIONSHIPS
Shows: Theater M:M Showings ( A theatre shows many showings, and many showings can be shown by more than 1 theatre.)
Has: Town 1:M Theatres (A town has more than one theatre, many theatres can exist in 1 town)
Runs: Showing 1:M Films (1 showing can run more than one film, many films can run in one single showing)
Includes – Admission 1:M Showings (A single admission price can include more than one showing, therefore many showings can be included in a single admission price)
Directs - Director 1:M Movies (One director can direct more than one movie, many movies can be directed by a single director)
David Hare-Scott
Jun 3, 2012, 12:15:46 AM6/3/12
to
different times by different Directors and movies can be released under
various names in different places. There is no solution in the real world
if you are forced to use MovieName as a PK and unless you have left out some
vital information from the question (such as *assume* that each MovieName is
unique) there is no solution in the tidy world of academia either. Perhaps
some discourse on this weakness of his question will get you back in the
professor's good books. But maybe not.
David
Bob Barrows
Jun 3, 2012, 8:40:28 AM6/3/12
to
David Hare-Scott wrote:
> TotalNewb wrote:
>> On Friday, June 1, 2012 8:14:29 AM UTC-4, Bob Barrows wrote:
>>> BadnBull_Options wrote:
>>>> Sorry for being a newb, for whatever reason I just can't wrap my
>>>> head around this. I need some help in figuring out the entities and
>>>> their relationships here, I already sent in a couple of
>>>> submissions, and I get the impression my professor is getting a bit
>>>> ticked off.
>>>>
>>
> TotalNewb wrote:
>> On Friday, June 1, 2012 8:14:29 AM UTC-4, Bob Barrows wrote:
>>> BadnBull_Options wrote:
>>>> Sorry for being a newb, for whatever reason I just can't wrap my
>>>> head around this. I need some help in figuring out the entities and
>>>> their relationships here, I already sent in a couple of
>>>> submissions, and I get the impression my professor is getting a bit
>>>> ticked off.
>>>>
>>
>> Here's my third submission, apparently it's still has some issues,
>> for instance restricting the rating of a particular movie to only one
>> rating, the Actor entity restricts an actor to only one film in his
>> entire career,
Do you see why? What do you think you would need to do to correct it? Think
>> for instance restricting the rating of a particular movie to only one
>> rating, the Actor entity restricts an actor to only one film in his
>> entire career,
about what a key does: it uniquely identifies each row in a table. Look at
the key you chose for the Actor entity. How many rocords is it allowing for
each actor?
In the real world, I would have two tables: one for Actors, keyed by actor
(one record per actor) so I could include all the other interesting details
about the actor (DOB, etc.), and the other would be called something like
ActorsMovies, which is keyed by the keys for both actors an movies. It would
have two foreign key relationships: one to Actors, and the other to Movies.
> also the first 3 relationships are incorrect. Also,
>> I'm a little puzzled as how to approach the Count and Price
>> relations. Any feedback would be great guys?
shown." does seem to make this insoluble ... unless that horribly named
"Date" data element actually refers to the movie's release date rather than
the date that it is shown in a theatre. Hmmm, yes, the "Time of Showing"
element could store both the date and time of showing, so "Date" could be
used to indicate the release date of the movie. This would allow a natural
primary key of
MovieName, Date, Director
To the OP:
I would modify the movie key as above, as well as the relationships and the
keys of the other tables where you have used Date to indicate the date of a
movie's screening.
A similar objection applies to the key you have chose for theatre - I can
think of several Showcase Cinemas located within 25 miles of my location.You
need to include at least Town to uniquely identify a theatre.
In the real world, it is possible for there to be two actors with the same
name - for example, two actors named Colin Farrell are currently working.
The elder one appeared in "A Bridge Too Far". The younger one of course is
famous for his roles in "Minority Report", "Alexander", "Daredevil",
etc.Other examples include Peter Graves and Richard Denning.
So the problem definition has failed to include enough data elements to
properly identify the actors - be sure to point out this flaw to the
professor, but don't let that stop you from providing a theoretical
solution - you can use parenthesized numerals as part of the actors' names
to differentiate actors with the same name (similar to what IMDB does). It's
not an ideal solution but it will make the problem soluble.
Think about the key you've chosen for Showings. Don't theatres routinely use
sequential numbers to identify their screens? Screen 1, screen 2, etc. Have
you selected the proper key for that table?
I am going to stop here, I think you have enough direction now to revisit
each of your enitities in your model and make sure you provide proper keys
for each. Once you have done that, show us what you have.
David Hare-Scott
Jun 3, 2012, 10:27:23 PM6/3/12
to
a data item? I would say no but the Prof may well say yes.
Newb do you see the difference between making the key of Movie a compound
key (MovieName, Date, Director) and a counter with no meaning in the real
world (cntMovieID)? There are implications for both solutions.
D
0 new messages