Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Join Date
    Oct 2010
    Posts
    10

    Help Me!! Database Design!!

    Plz some one help me to design database for following scenario:


    Specification:
    The aim of this assignment is to develop a database system to hold records for basketball teams and
    their matches. The system will be used for analysing the players’ performance during the season as
    well as to select players of the month based on the data entered into the system. The system also is
    mainly used for producing statistical information for managers, coaches and clubs (where the team
    plays) owners.
    The system will maintain personal information of players such as: first and surnames, date of birth
    (players must be above 16 years old), height and weight, position (each player can play in various
    positions) and experience (measured by the number of national and international matches’ played).
    Information on when the player has starting in a specific position is required. If he used to play in a
    specific position before but not anymore, the date when he stopped playing in that position is
    required.
    A player can be associated with only one team at any time. However, records of previous
    associations (i.e. teams previously played for) would be very useful but not essential. The team has
    a fixed address which usually the address of the club where the basketball court is physically
    located (Home court). A player is either a professional or an amateur but cannot be both.
    Professional players get a high salary from the club. Information on their contracts should be kept
    in the system such as salary, date of start and date when the contract finishes plus any additional
    binding conditions.
    Amateur players must be either in full time education or in full time employment. The occupation
    must be recorded in the system (e.g. education or employment) plus the name of the institute (e.g.
    company or university name). We also keep information about other hobbies for amateurs. An
    amateur player does not get any payments when playing in matched but all expenses incurred for
    playing away from home club will be covered by the team.
    Each season, a record of matched that each team plays is kept. A game is between two teams of
    which one of them must win (i.e. no draw results in basketball games). The date and place of the
    match are kept in the system. A record of the scored points by each team is used to determine
    which team is the winner.

    For each match we need to keep information of the performance of each player. This include the
    number of: 2 points attempts, 2 points made, 3 points attempts, 3 points made, free throws attempts,
    free throws made, minutes played, fouls made, rebounds (i.e. A rebound in basketball is the act of
    successfully gaining possession of the basketball after a missed field goal or free throw).
    Although the match is between two teams, it is common that many players are named in the team
    but they do not actually participate in that match when it takes place. These are reserved players
    sitting on the bench ready to be called any minute by the coach to join the match. The team consists
    of 10 named players but only 5 players can play at any one time. The team consists of professional
    and amateur players. The coach can exchange players at any time, unlimited number of times.
    Quite often players do not actually play in the match despite they are named in the team initially for
    that match. Therefore, information as to which player actively played in a specific match is
    important.
    The developed system should be capable of keeping track of players and allow the user to interact
    flexibly and efficiently with the database. Although users are accessing the DB via a web page, it is
    not part of the requirements for this cswk
    .

    I need to implement following queries:

    A1. A list of all players who played more than 20 games (national or international).
    The user should be prompted to enter the player name at run-time. Produce the
    SQL code only (i.e. no form or report).

    A2. For a given match, produce the total number of fouls made by all players (from
    both teams who actually played) in that match. Produce the SQL code only (i.e.
    no form or report).

    A3. For any given player, give the number of all matched that he did not actually
    play but listed in the team. The player full name or ID should be input at run
    time from the SQL prompt. Produce the SQL code only (i.e. no form or report).

    A4. Choose a player of the match (i.e. the player who scored highest points in one
    match). Produce the SQL code only (i.e. no form or report).

    A5. For any given match, produce the names of all named players in the two teams,
    the name of the winning team, the court postcode where the match took place
    and the date of the match. The user should be prompted to pick the target
    match from a pull down menu that shows (the pull down menu) the date of the
    match and names of the two teams. Produce the output on a report.

    A6. Produce a list of statistics for any given professional player. The name of the
    player should be captured at run-time from a drop-down list (of all available
    professional players) by the user. For each player, and for each match he
    played, list the match date, the team he is paying for, the total points scored
    (i.e. 2 & 3 points goals) in that match and the number of fouls he made. The
    output is to be produced in a master/detail form.
    Last edited by maruf12; 10-21-10 at 05:53.

  2. #2
    Join Date
    Oct 2010
    Posts
    10
    I need the table names with their field and queries for A1-A6

    thanks
    Last edited by maruf12; 10-21-10 at 15:45.

  3. #3
    Join Date
    Oct 2010
    Posts
    10
    deleted post
    Last edited by maruf12; 10-21-10 at 06:31.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Uh huh.
    And you deserve to pass if you post your assignment on the internet asking people to complete it for you?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Oct 2010
    Posts
    10
    Quote Originally Posted by pootle flump View Post
    Uh huh.
    And you deserve to pass if you post your assignment on the internet asking people to complete it for you?
    brother i badly need it

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    You should have studied hard and started your assignment in good time.

    You will not be helped to cheat here.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Oct 2010
    Posts
    10
    okay give me some concept about the Design......as i dont know about the rules of basketball game......give me some instruction.

    thanks

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Quote Originally Posted by maruf12 View Post
    okay give me some concept about the Design......as i dont know about the rules of basketball game......give me some instruction.

    thanks
    OK
    data in a relational database is stored in tables, which represent specific entities. so you should have no repeating groups of information is a table.
    so first off I'd suggest you identify the entities you need, think what you need to store to meet the specific requirements of your assignment

    go look at the sources/references on table design, normalisation and so on, assuming you were in class when these were pointed out.

    if not there's some good references here
    Fundamentals of Relational Database Design -- r937.com
    and / or
    The Relational Data Model, Normalisation and effective Database Design

    when you've done that and made an effort by all means come back and ask questions as required
    another goof source of information are you fellow students, and the person lecturing/teaching you.
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Quote Originally Posted by maruf12 View Post
    i dont know about the rules of basketball game
    You don't need to sweat about what the basketball stats you are asked to track actually mean. That does not matter. Just consider them as a collection of measurements associated with each player and game.
    Then, design the DB as if you were designing it for soccer, or some other sport with which you are familiar.
    Players, Teams, Games, Points, Stats....all sports have these components. So just design a generic DB for tracking any sport.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  10. #10
    Join Date
    Oct 2010
    Posts
    10
    thanks "blindman" and "healdem" for your kind reply.

    @ healdem: i have knowledge about entities/relation, Primary key, Foreign Key, Cardinality(1:1, 1:N), normalisation, ERD, SQL. main problem is, i am totally out of study for 7-8 months due to my job, thats why i am confused. i want to find out the perfect entities for this scenario.

    @ blindman: I want to take following entities for my assignment:

    1. team
    2. player
    3. player_details
    4. match
    5. match_performance
    players - player_detail (1:N)
    players - team (1:N)
    team - match (M:N)
    match - match_performance (1:N)


    Will it be perfect DB for my assignment?? or i need to add more entities/tables?
    Last edited by maruf12; 10-21-10 at 16:15.

  11. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    What are you planning to store in player_detail and match_performance?

    Can a player be on more than one team? Perhaps in different seasons, or even play on two teams in the same season?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  12. #12
    Join Date
    Oct 2010
    Posts
    10
    Plz see my proposed relation:


    http://img80.imageshack.us/img80/751/20316170.jpg



    is it correct??
    Last edited by maruf12; 10-21-10 at 17:55.

  13. #13
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    player_type should be related to Player, not player_stats.
    I'm not sure what role "reserve" is playing, off their on the side. What attributes will it have?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  14. #14
    Join Date
    Oct 2010
    Posts
    10
    Quote Originally Posted by blindman View Post
    player_type should be related to Player, not player_stats.
    I'm not sure what role "reserve" is playing, off their on the side. What attributes will it have?
    ok i will relate player_type with player:

    http://img263.imageshack.us/img263/8689/dd2ux.jpg

    i think reserve is needed for A3
    A3. For any given player, give the number of all matched that he did not actually
    play but listed in the team. The player full name or ID should be input at run
    time from the SQL prompt. Produce the SQL code only (i.e. no form or report)


    reserve (reserve_id, team_staff_id)

    please correct me, if i am wrong

    thanks
    Last edited by maruf12; 10-22-10 at 14:01.

  15. #15
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    You don't need a "reserve" table for that. Just run query to find all of the team members who did not participate in the game.
    Or, you could have an column in the "participant_staff_in_game" table that indicates whether they had actual playing time. Heck, if you are tracking playing time as one of the stats, then you just find all the players where their playing time was zero minutes.
    My point is, "reserve" is derivable data, and should not need to be stored in its own table.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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