Page 1 of 2 12 LastLast
Results 1 to 15 of 27
  1. #1
    Join Date
    Dec 2005
    Posts
    11

    Unanswered: Need help creating attributes

    Okay, can anyone help me with the following:-

    I have to create a database on theatre booking system. So far, ive identified the following entities:

    Customer(Customer ID, Title, Initials, Surname) Customer ID is PK
    Bookings(-------)
    Play(Playname, Playwright, Number Of Characters, Length(min)) Playname is PK
    Actors(-------)

    i do not know what to create as attributes for the bookings table, especially seeing as the attributes have to relate to customer and play. for the actors table i was thinking of putting in the following attributes:-

    Actor ID, Name, Age, Playname, Role (Actor ID is PK)

    but i was unsure if they worked, although i think they would, because playname from the play table would be a foreign key in the actors table. this is where i come into conflict with the bookings table, ie what could add to teh custoemr table as a primary key that would make a suitable foreign key in the bookings table, and what could be a suitable primary key in teh bookings table, and also a suitable foreign key in the play table? please help, its an urgent issue. thanks very much for your time.

    Fergal

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    For starters, Play should really have an arbitrary id. Play_id perhaps. what happens if you show the same play more than once? Or perhaps a rewritten version of a play?

    One actor could be in more than one play couldn't they? I wouldn't be surprised if one actor was in the same play more than once with different roles...

    To handle those possibilities, you may want to create a PlayActors table with play_id, actor_id and role. This would allow much greater flexibility in how you associate actors to a play.

    I'm not sure how bookings and customers come into play here. could you walk through a plain-english description of what process you need to track?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Dec 2005
    Posts
    11
    Okay, thankyou for your response, i need to do the following:

    I have to crerate a Theatre Booking System which records bookings of seats by customers for performances of plays involving various actors

    correct me if im wrong but this means the following relationships would exist here:

    Customer Makes booking
    Booking reserves seats for play
    play involves actors

    The customer and bookings are related, which means a primary key in the customer table must exist in the bookings table as a foreign key, yes? if so, what could that be? also, what attribute could act as a primary key in the bookings table but also a foreign key in the plays table. In the past, i had the following attributes for the tables:

    Customer(Customer ID, Title, Initials, Surname) Customer ID is PK
    Bookings(Seat ID, Section, Time, Date, Customer ID) Seat ID, Time And Date are PK, Customer ID is foreign key
    Play(Playname, Playwright, Number Of Characters, Length(min)) Playname is PK, dont know what to put as a foreign key
    Actors(Actor ID, Name, Age, Playname, Role) Actor ID is PK, Playname is Foreign Key

    what could be placed as a foreign key in the play table that matches the primary key in the bookings table?

    A question about composite primary keys:

    okay, if you have a primary key made of 3 attributes, does that mean that the foreign key in the corresponding table must be made of the same 3 attributes, or can it just be one of the 3 attributes?

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    It would need to be all 3 attributes, that IS the key. How can the database possibly know what you're talking about if you only give it 1/3 of the logic? That is an excellent example of why I suggest adding play_id to your play table.

    Pop play_id into your bookings table, and you'll be all set.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  5. #5
    Join Date
    Dec 2005
    Posts
    11
    so that means, the booking table should look like this:

    Bookings(Play ID, Seat ID, Section, Time, Date, Customer ID) with only Play ID as the PK?

  6. #6
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Ideally you would go with BookingID as a primary key
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  7. #7
    Join Date
    Dec 2005
    Posts
    11
    what should the overall tables look like then:-

    Customer(Customer ID, Title, Initials, Surname) Customer ID is PK
    Bookings(Seat ID, Section, Time, Date, Customer ID, Booking ID) Booking ID is PK and Customer is FK
    Play(Playname, Playwright, Number Of Characters, Length(min), Booking ID) Playname is PK, Booking ID is FK
    Actors(Actor ID, Name, Age, Playname, Role) Actor ID is PK, Playname is FK

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    can you data model support an actor who has multiple roles in a play?

    does your model support multiple or single performances.

    numer of characters equates presumably to the number of roles - if so then its derived data and therefore shouldn't be stored (unless you can possibly avoid it)

    how do you distinguish say where there may be one role but multiple actors (eg a crowd)

    how do you know what section a seat is in
    how do you propose to to identify which seats are taken
    how do you propose to handle a customer who wants to book multiple seats

    presumably the role is an attribute of the play not the actor, so I'm guessing your model should have a sub table containing roles other pertinent data, and you need some means of linking an actor to a role.

    just wondering how many tables does your assignment require?

  9. #9
    Join Date
    Dec 2005
    Posts
    11
    what should the tables to look like then?

  10. #10
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    That's kind of the point of the assignment.. you know, to see if you know what they should look like...

    Figure out how you would do it WITHOUT a database. That's always helped me in the past. How would I do it on paper? Well, I need to keep a list of all seats that I can look at when people want to know where they're sitting. I need to keep a list of all available events so I know what they want to see. I need to keep a list of all the seats that have been sold for a SPECIFIC event so I know what's left. I need to keep a list of all the actors somewhere in case someone wants to know all the plays an actor is in. I need to keep a list of all the actors in a given play too.

    I just generated most of the data elements necessary by thinking about how to do it on paper, see if you can figure out how to hook them up...
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  11. #11
    Join Date
    Dec 2005
    Posts
    11
    arrrggghhh!!11 its not working!!!!

    ive been at this past few hours and im gettin nowhere.

  12. #12
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    arrrggghhh!!11 its not working!!!!

    ive been at this past few hours and im gettin nowhere.
    And your point is?
    Its my understanding that we don't do your (or anyone else's homework). You have had some pointers and some questions to encourage you to think about what is required to meet your requirements. But in the end its your requirements not a true business model.

    Teddy's suggestion of forget the db side of it at present and think of the various entities that exist in the world your are trying to model is bang on. Then think of the attributes of those entities.

    Your first stab was in the right area - there are probably 4 or 5 entities (play, customer, bookings & actors). So I'd suggest on a piece of paper map out those entities what are relevant / related items to those entities. Having done that then think of how you would expect those entities to interrelate ie in what way or ways does say actor connect with play.

    it may well be that your model doesn't require the complexity of a real world theatre - thats fine, after all its your data model. But you need to clearly think about each separate entity, if there is confusion or doubts if your data model can support the business requirement then possibly you need to defien subcategory entities

    for example, take tblActor,
    An actor is a single person, however that actor may have mulitple roles in any one play, and may have multiple roles in multiple plays. Also its conceivable that a theatre production may have multiple actors playing the same role (either as understudy or on specific days / perfomrances [eg fred smith can do Mon- Fri, whereas John Doe does Saturday and Sunday]) An actor might be the lead actor for each play your theatre company puts on.

    So, in my view, you can't identify the single role in a single play at the actor level. You have to move the association of an actor to a role to another entity. that way round you can store more than 'just' the current information. So you probably need another table which cross refers an actor to a play. Incidentally it may make sense to call your play entity soemthing else eg production - after all the play could be a stage play, a musical, a pantomime (oh no it isn't)

    Its arguable that the role is a subtype of the play (for example you may cast a different actor in another production of the same play), so maybe you need to entities to accurately map an actor to a play (one to identify what roles are part of which play,m and another to identify what actors are playing what roles.

    If you are struggling then go ask your teacher, after all they are in a position to assist you with the lack of comprehension in this. You will get help from this forum you wont get a complete fix, you will get guidance you wont get someone to do the whole thing for you.

    HTH
    Last edited by healdem; 12-10-05 at 06:58.

  13. #13
    Join Date
    Dec 2005
    Posts
    11
    Okay, i want to start from scratch, so can someone tell me if the following is correct?

    Customer to Bookings is 1:M Because one customer is allowed to book more thanone seat, but 1 seat cant be booked by multiple customers
    Bookings to Plays is M:M because there are multiple plays, and each paly has multiple bookings
    Plays to Actors is M:M because each play had many actors and an actor can star in more than one play

    this means that 2 link tables should be created; 1 for bookings to plays and 1 for plays to actors

    is that correct?

  14. #14
    Join Date
    Nov 2002
    Posts
    272
    Quote Originally Posted by 2eXtreme
    Bookings to Plays is M:M because there are multiple plays, and each paly has multiple bookings
    I'd say a booking (date, time, seat) can not have multiple plays. Did you ever see two plays at the same time from the same seat?

  15. #15
    Join Date
    Dec 2005
    Posts
    11
    lol yeah i just realised, so i think that means that it should be

    Bookings - Plays
    1 : M

    Now for the existence rations, heres what ive got so far:

    Customer to bookings is mandatory to partial because the bookings are dependent on teh customer
    bookings to plays is partial to plays, because you cant book a seat for a play unless it exists
    plays to actors is mandatory to mandatory beacause neitehr rely on each otehr to exist

    is that right?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •