Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Mar 2010
    Posts
    12

    database scenerio

    Titanic Leisure Centre is a medium sized Leisure Centre in the London Borough of Folgate. It contains a gym, a large sports hall suitable for indoor sports such as 5-a-side football, badminton and basketball, a smaller sports hall used for martial arts and two swimming pools (one large and one small). Titanic Leisure Centre is run by Mr. Radagast Imperio and employs various other staff such as gym instructors, receptionists and assistants. It also employs various instructors for particular sessions such as Judo and Karate. Customers of Titanic Leisure can either be full members who pay a monthly fee by direct debit, associate members who pay a fixed yearly fee and get a reduction on whatever activity they want to do or guest members who pay full price for activities as they do them. As well as individual customers Titanic Leisure also puts on various special sessions for particular groups in the community such as a Jewish Men‟s Swimming session, a Women Only Swimming Session and a Over 60s‟ gym session. These special sessions are blocked booked for particular times every week in the name of a particular group e.g. the Folgate Women Swimmers Club. Depending on the room particular sessions might be booked by individuals, set aside for a community activity, or they might be open to all. So, for example, badminton sessions must be booked by an individual customer. Where an activity is organised by Titanic themselves then it is marked as being booked to „TITANIC‟. An example of this is a Karate or Judo session where an instructor is provided.

    entities i have pulled out are
    customer
    groups
    session
    payments
    members
    staff

    hello guys this is my scenerio for a school project can some one please help me come up with a normalization for this scenerio or idea thanks my email is
    fergentius@hotmail.com

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by dman11304133 View Post
    can some one please help me come up with a normalization for this scenerio
    sure, we'll help, but you have to do the work

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2010
    Posts
    12

    ummm

    should i show you my erd atm

  4. #4
    Join Date
    Mar 2010
    Posts
    12

    erd

    this is a copy of my erd so far
    Attached Thumbnails Attached Thumbnails Untitled.gif  

  5. #5
    Join Date
    Mar 2010
    Posts
    12

    these are my current tables that i thinking to put

    customertable
    customerid PK
    first_name
    last_name
    address
    dob
    membership_type fk
    customer_type fk

    customer_type
    customer_type_name pk

    membership table
    membership name pk

    paymenttable
    customer_id pk
    payment_date pk
    payment_time
    amount paid

    roomtable
    room name pk

    equipment table
    equipement_name pk

    activitytable
    activity_name pk

    stafftable
    staff id pk
    staff name
    staff position

    sessionstaff table
    sessioncode PK
    staff id PK

    session table
    session code pk
    session date
    session time
    room namefk
    activiy namefk
    equipment namefk
    booking_no fk

    booking table
    booking_id
    customer_id
    booking_date
    booking_time

    this is the table i created but i think it off a bit i got these tables from the erd i constructed but it seem wrong i dont no what to do again i been working on this for 3 weeks now im frustrated

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by dman11304133 View Post
    ... but i think it off a bit
    ... but it seem wrong
    what makes you think it's "off" or "wrong"?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Mar 2010
    Posts
    12

    hmm

    cause when i get to thinking about booking and session it comes like the same thing and it driving me nuts cause i have to do it in oracle so i decided to do it in access first.. but when i get to booking and session i just get confuse. can you give me a lil wisdom on any error or abonormities.. against i decided to make customer type in teh customer table cause the customer can be groups or can be individual. do them entities look right

  8. #8
    Join Date
    Mar 2010
    Posts
    12

    hmmm

    i no staff is a recursive relationship because a staff assign a next staff to the activity i dont no how to implement that into the database design to show which staff assigned a next staff this scenerio is driving me nuts

  9. #9
    Join Date
    Mar 2010
    Posts
    12

    hmm

    can anyone help me

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by dman11304133 View Post
    i no staff is a recursive relationship because a staff assign a next staff to the activity
    staff itself isn't recursive, if it were it would look like the typical manager-employee relationship that you see in all the sql tutorials

    in fact it's probably sessionstaff that's recursive, as it depends on each different activity who the successor is

    it'll probably look something like this when you declare the table --
    Code:
    CREATE TABLE sessionstaff 
    ( sessioncode     INTEGER NOT NULL
    , FOREIGN KEY ( sessioncode ) REFERENCES session ( sessioncode )
    , staffid         INTEGER NOT NULL
    , FOREIGN KEY ( staffid ) REFERENCES staff ( staffid )
    , PRIMARY KEY PK ( sessioncode, staffid )
    , successor       INTEGER NULL -- i.e. optional
    , FOREIGN KEY ( sessioncode, successor )
        REFERENCES sessionstaff ( sessioncode, staffid )
    );
    Last edited by r937; 03-27-10 at 10:15.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Mar 2010
    Posts
    12

    data structure

    so by looking at my structure do you see any anomialies in it... where in the tables structure would need fixes.. um can you help me with a normalization from that table

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by dman11304133 View Post
    um can you help me with a normalization from that table
    sure, but like i said, you're going to have to do the work

    which tables do you want to normalize? do you know what normalization means?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Mar 2010
    Posts
    12

    hmm

    can you explain something how would you look at booking and session in this scenerio. that what killing me. i dont no how to normalize the booking and the session table would need the most normalize. cause in my customer td everything depends back to the primary key..
    do you like the idea how i put customer type entity to distinguish between group and individual customer?

  14. #14
    Join Date
    Mar 2010
    Posts
    12

    hmmm

    well while i looked at my table it all looked normalized perfectly because all the attributes look dependent on the primary key.. but my problem is when i start to create the table is the booking and session table... how would you recon those would be related

  15. #15
    Join Date
    Mar 2010
    Posts
    12

    hmmmm

     Session-Staff Schedule Form For any scheduled session, show all the staff assigned to it. This form should facilitate the allocation of existing staff to an existing session.
    [10 Marks]
     Activity-Equipment Schedule Form This form should facilitate the allocation of existing activity to existing equipment.
    [10 Marks]
     Session Form This form should allow the setting up of sessions for rooms. It should look something like Figure 3.
    [10 Marks]
     Block Booking Form For a group customer show the block bookings they have made and which sessions these block bookings belong to. This form should, therefore, link existing groups with existing sessions via a block booking.


    these are the different form which my database would need so do you think that my current design can achieve that all you could say is yes or no then i no if i going in the right direction and bdw would your book assist me in this scenerio

Posting Permissions

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