Results 1 to 15 of 15
  1. #1
    Join Date
    Apr 2011
    Posts
    40

    DB Desingn creating tables (1st step)

    Hi Everyone

    Can anybody check the tables I created for the fallowing scenario.
    (oracle)


    SCENARIO;

    Faraway travel offers a number of holidays. Each holiday is identified by a code which comprises the letter 'H' followed by three digits. Each holiday also has a unique name. The database should also record:-

    • The minimum and maximum number of participants that can go on each holiday. Maximum and minimum numbers are always between 5 and 30.
    • The activities available on each holiday. A holiday can offer a number of activities, the commonest of which are walking, cycling, diving, skiing and sightseeing, although some holidays offer no activities.
    Each holiday may run several times in each year. For example the holiday ‘Glorious Tuscany’ is offered starting on 4th July 2005, 7th August 2005, and 9th September 2005. Faraway calls these ‘trips’: hence the holiday ‘Glorious Tuscany’ has three trips in 2005. Each trip has a unique trip code which start with the letter 'T' followed by three digits. The database should also record the start and finish dates of the trip as well as the price of the trip.

    Faraway has a number of staff members. Staff members are assigned to trips: for example, a staff member may be assigned as ‘cook’ to one trip, and as ‘driver’ to another. Other roles include ‘leader’, ‘instructor’ and ‘guide’. A staff member can only be assigned once to a given trip, but a staff member can be assigned to many different trips. Each trip has at least one staff member assigned to it, and some have as many as five. All staff members are assigned to at least one trip. Every member of staff has a unique staff number which starts with the letter 'S' followed by three digits. The database must also record the name of each staff member (first and last name), salary and gender.

    Some staff members may act as managers who manage other staff members. A staff member will have only one designated manager but each manager manages several other staff members. Not every staff member is a manager and not every staff member is managed.

    The database must record details of the clients. Each client is given a unique client reference number which starts with the letter 'C' followed by three digits. In addition the database should store the client name (first and last) and address (street, town and postcode).

    Clients make bookings. Each booking is for one client. Faraway find that many clients make repeat bookings, so that a client may have many bookings, each for a different trip. Bookings are for trips. Each booking is for a single trip. There are generally many bookings for each trip but a trip may have no bookings. Each booking is identified by a unique reference code which starts with the letter 'B' followed by three digits. Data to be stored for each booking is the booking reference code, and the date when the booking was made.

    For each booking there are a number of payments. There are three types of payment: the deposit (usually made when the holiday is booked), the balance (the rest of the cost, which is usually paid up about one month before the trip starts), and an optional insurance payment. The database should record the type, date and amount of each payment. There is only one payment of each type per booking.
    Attached Thumbnails Attached Thumbnails tables.bmp  

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by arayici View Post
    Can anybody check the tables I created for the fallowing scenario.
    those tables still need a lot of work

    for example, the "activite" table looks like it contains activity names instead of table columns

    when's this assignment due?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Apr 2011
    Posts
    40
    Quote Originally Posted by r937 View Post
    those tables still need a lot of work

    for example, the "activite" table looks like it contains activity names instead of table columns

    when's this assignment due?

    I know they still need to a lot of work. I just wanted to be sure about my tables before I jump to the next step (relationships between tables).

    I was not sure about the "ACTIVITE" table. What can be the entities of this table or should be a table called "ACTIVITE" at all?

    assignment due on 28 April.

  4. #4
    Join Date
    May 2008
    Posts
    277
    I think what Rudy's getting at is, what would you do if you need to add another activity, such as "swimming"? Or heck, 10 more activities?

    Same thing with Job Roles.

  5. #5
    Join Date
    Apr 2011
    Posts
    40
    Quote Originally Posted by futurity View Post
    I think what Rudy's getting at is, what would you do if you need to add another activity, such as "swimming"? Or heck, 10 more activities?

    Same thing with Job Roles.
    That is the way I made separate tables for the "ACTIVITE" and "JOBROLE"

    So I can add as many as activite or jobrole i want.

    So is my table creation right or wrong??

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by arayici View Post
    So is my table creation right or wrong??
    it's wrong

    imagine if you had a table for countries

    one would expect it to have two columns, like country_code and country_name

    one would ~not~ expect it to have columns like Canada, USA, England, France ...

    that's the same error as in your activities table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Apr 2011
    Posts
    40
    Quote Originally Posted by r937 View Post
    it's wrong

    imagine if you had a table for countries

    one would expect it to have two columns, like country_code and country_name

    one would ~not~ expect it to have columns like Canada, USA, England, France ...

    that's the same error as in your activities table
    So, if my ACTIVITE table is Wrong JOBROLE Table must be wrong as well?

    So how should I create these tables then or should I create a table at all.

    any help with that

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by arayici View Post
    So how should I create these tables then or should I create a table at all.
    how should you create these tables?

    with a CREATE TABLE statement

    should you create them at all?

    i have no idea -- what does the homework assignment require?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Apr 2011
    Posts
    40
    Quote Originally Posted by r937 View Post
    what does the homework assignment require?
    First part

    • Analyse the requirements given above;
    • Identify the entities and their attributes;
    • Identify the relationships between them considering cardinality, optionality and transferability;
    • Draw an ER diagram;


    I was trying to figure out my tables first so i can Draw an ERD.

    how should I create these table in the first part of my Scenario

    Faraway travel offers a number of holidays. Each holiday is identified by a code which comprises the letter 'H' followed by three digits. Each holiday also has a unique name. The database should also record:-

    • The minimum and maximum number of participants that can go on each holiday. Maximum and minimum numbers are always between 5 and 30.
    • The activities available on each holiday. A holiday can offer a number of activities, the commonest of which are walking, cycling, diving, skiing and sightseeing, although some holidays offer no activities.

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by arayici View Post
    I was trying to figure out my tables first so i can Draw an ERD.
    you have that completely backwards

    finalize your ERD first, then creating tables will be easy

    and please stop asking for the answers -- no one is going to hand you any answers, you have to do them yourself

    however, you can ask us to critique what you have created
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Apr 2011
    Posts
    40
    Quote Originally Posted by r937 View Post
    you have that completely backwards

    finalize your ERD first, then creating tables will be easy

    and please stop asking for the answers -- no one is going to hand you any answers, you have to do them yourself

    however, you can ask us to critique what you have created
    I was not expecting the answers actually.
    I am trying to understand and make it myself but need some advice as I am only new in this.

    Some advice or some sources I could have a look, could be good help.

    I didn't think I could draw the ERD without known the tables and their attributes.

    I will try to draw one now but I still confused with my tables.
    Last edited by arayici; 04-08-11 at 15:45.

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by arayici View Post
    Some advice or some sources I could have a look, could be good help.
    - your course textbook

    - your course notes

    - your fellow students

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

  13. #13
    Join Date
    Apr 2011
    Posts
    40
    -

    -

    -

    -


    Thanks...

  14. #14
    Join Date
    May 2008
    Posts
    277
    Quote Originally Posted by arayici View Post
    So, if my ACTIVITE table is Wrong JOBROLE Table must be wrong as well?

    So how should I create these tables then or should I create a table at all.
    I honestly can't tell if you're not getting this, or there's just general confusion because of how you've drawn your diagram.

    Compare your ACTIVITE table to your STAFF table. Based on your diagram, if we were to insert some data into the STAFF table, the rows in that table would look something like this:

    Code:
    staff_num | first_name | last_name | salary | gender
    ----------+------------+-----------+--------+-------
     S001     | Joe        | Bob       | 10,000 | male
     S002     | Jane       | Smith     | 11,000 | female
    Now, show us what some rows of data in your ACTIVITE table would like.

    I didn't think I could draw the ERD without known the tables and their attributes.
    You wouldn't start building a house without a blueprint, same thing with your database. That's what the ERD is: the blueprint for your database. Answering these questions that you have -- Is my table right? Do I need these (or other) tables? -- is the whole point in creating an ERD. It forces you to understand what you're designing.

  15. #15
    Join Date
    Apr 2011
    Posts
    40
    Quote Originally Posted by futurity View Post
    [/code]
    Now, show us what some rows of data in your ACTIVITE table would like.
    I need to do this in different tasks;
    Task a

    • Analyse the requirements given above;
    • Identify the entities and their attributes;
    • Identify the relationships between them considering cardinality, optionality and transferability;
    • Draw an ER diagram;

    Task b
    • Verify that ERD is in 3rd normal form (3NF);
    • Resolve M:M relationships and redraw the ER diagram.


    for the first Task I don't need to do Normalization or M:M resolving.

    The activities available on each holiday. A holiday can offer a number of activities, the commonest of which are walking, cycling, diving, skiing and sightseeing, although some holidays offer no activities.
    as far as I can see there should be I table for Activities but no attributes are giving so I cant show any rows in the Activate table!

    I think I should include Activate in the HOLIDAY table and normalize in the task (b) so then I can make same attributes for the Activities ( am I Wright?)


    same as jobrole
    for example, a staff member may be assigned as ‘cook’ to one trip, and as ‘driver’ to another. Other roles include ‘leader’, ‘instructor’ and ‘guide’. A staff member can only be assigned once to a given trip, but a staff member can be assigned to many different trips.
    is it ok to include jobrole in the STAFFS table first and then normalize?

Posting Permissions

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