Itech1006 Database Management Systems-Australian Premier Assessment Answers
All attributes containing metrics will be sourced from club databases and are accumulated values in this database. i.e. player attributes like “number of tackles” etc., will be read from the club database and then added to the current value in this database.
Using the following business rules, design a database that will allow the new Australian Premier League to track their soccer clubs:
The Australian Premier League needs to store the name, city, state and email of all clubs. Each club also needs a unique ID to identify them.
Each club may have one or more sponsors to help finance them during the course of the year. It is also possible that a sponsor may sponsor more than one club.
The league needs to keep a record of a sponsor’s name, email address, and the type of sponsorship and funding amount for sponsorships given to clubs. Each sponsor also needs a unique ID.
A club may also have many members who belong to it. However, a member can only belong to one club. A member would need to have a separate member ID to belong to another club. The league has set this rule to better gauge how many members a club actually has.
The league needs to store the member's ID, first and last name, address, city and post code, and their email address.
Each club stocks a variety of merchandise that they can sell. All clubs have the same types of items, i.e. “Scarf”, “Beanie”, “Jacket”, “T shirt” etc., the only difference between them is the club logo and club colours/patterns.
The club can only sell the merchandise with their branding on it.
The Merchandise needs an ID that shows that it distinctly belongs to the respective club, and what type it is. The selling price and amount sold also need to be stored.
Each club has only one stadium, and stadiums are not shared amongst clubs. If a stadium is unable to be used by the home club team(s), then the game will be played at the other club’s stadium.
Answer:
- One Player can belong to only one Club
- One Player can play under many Divisions
- One Player can be either a Field Player or Goal Keeper and rarely both
- A Field Player can belong to only one of Fielder Type
- A Item can be merchandised by only one Club
- A Club can merchandise multiple items
- A Stadium is to only one Club
- A Club can withhold only one Stadium
- A member can belong to only one Club
- A person want to become the member of multiple club wants to obtain member id for each of the club
- Each coach is supervised by a head coach who is not supervised by others
- A coach can belong to only one club division
- A Club can hold many coaches
- A Club can contain many sponsor
- A Sponsor can provide sponsorship to multiple clubs
Normalization
FIELDER TYPE (FielderTypeId, FielderType)
DIVISION(DivisionName, Description)
POSTCODE INFO (PostCode, City)
Club (ClubId, Name, City, State, Email)
Player (PlayerId, ClubId, FirstName, LastName, DOB, PlayedGamesCount, CurrentPackage)
Field Player Performance (PlayerId, FielderTypeId, NoOfShotsOnTarget, NoOfAssists, NoOfPasses, NoOfTackles, NoOfPenalties)
Goal Keeper Performance (PlayerId, NoOfFreeKicksSaved, NoOfGoalKicks, NoOfNormalSaves, NoOfGoalsConceded)
MERCHANDISE ITEM (ItemId, ClubId, Name, Type, SellingPrice, AmountSold)
STADIUM (StadiumId, ClubId, Name, NoOfAvailableSeats, NoOfExcecutiveSeats, CumulativePercentAttendence)
Games Played (PlayerId, DivisionName, NoOfGamesPlayed)
Sponsor (SponsorId, Name, EmailAddress, SponsorType, FundingAmount)
Club-Sponsor List (ClubId,SponsorId)
Coach (CoachId, ClubId, HeadCoachId, DivisionName, FirstName, LastName, NoOfCoachedGames)
Member (MemberId, ClubId, FirstName, LastName, Address, PostCode, EmailAddress)
All the relations above are in 3NF
Conversion of E-R diagram to relational schema
Table Field Type Description
Fielder Type FielderTypeId INTEGER Primary Key
FielderType VARCHAR(25)
Division DivisionName VARCHAR(20) Primary Key
Description VARCHAR(255)
PostCode Info PostCode INTEGER Primary Key
City VARCHAR(25)
Club ClubId INTEGER Primary Key
Name VARCHAR(20)
City VARCHAR(20)
State VARCHAR(20)
Email VARCHAR(20)
Player PlayerId INTEGER Primary Key
ClubId VARCHAR(20) Foreign Key Reference Club (ClubId)
FirstName VARCHAR(20)
LastName VARCHAR(20)
DOB DATE
PlayedGamesCount INTEGER
CurrentPackage CURRENCY
Goal Keeper Performance PlayerId INTEGER PRIMARY KEY, Foreign Key Reference Club (ClubId) NoOfFreeKicksSaved INTEGER NoOfGoalKicks INTEGER NoOfNormalSaves INTEGER NoOfGoalsConceded INTEGER
FieldPlayerPerformance PlayerId INTEGER PRIMARY KEY, Foreign Key Reference Club (ClubId) FielderTypeId INTEGER Foreign Key Reference FielderType (FielderTypeId) NoOfShotsOnTarget INTEGER NoOfAssists INTEGER NoOfPasses INTEGER NoOfTackles INTEGER NoOfPenalties INTEGER
Merchandise Item ItemId INTEGER Primary Key ClubId INTEGER Foreign Key Reference Club (ClubId) Name VARCHAR (20) Type VARCHAR (20) SellingPrice CURRENCY AmountSold CURRENCY
Stadium StadiumId INTEGER Primary Key ClubId INTEGER Foreign Key Reference Club (ClubId) Name VARCHAR(25) NoOfAvailableSeats INTEGER NoOfExcecutiveSeats INTEGER CumulativePercentAttendence FLOAT
Games Played PlayerId INTEGER Primary Key
DivisionName VARCHAR(20)
NoOfGamesPlayed INTEGER
Sponsor SponsorId INTEGER Primary Key
Name VARCHAR(20)
EmailAddress VARCHAR(20)
SponsorType VARCHAR(20)
Funding Amount CURRENCY
Club-Sponsor List ClubId INTEGER Primary Key, Foreign Key Reference Club (ClubId)
SponsorId INTEGER Primary Key, Foreign Key Reference Sponsor(SponsorId)
Coach CoachId INTEGER Primary Key
ClubId INTEGER Foreign Key Reference Club (ClubId)
HeadCoachId INTEGER
DivisionName VARCHAR (25)
FirstName VARCHAR (25)
LastName VARCHAR (25)
NoOfCoachedGames INTEGER
Member MemberId INTEGER Primary Key
ClubId INTEGER Foreign Key Reference Club (ClubId)
FirstName VARCHAR(20)
LastName VARCHAR(20)
Address VARCHAR(20)
PostCode INTEGER
EmailAddress VARCHAR(20)
Reference:
Avi Silberschatz, Henry F. Korth, and S. Sudarshan (1986). Database System Concepts. McGraw-Hill Education 6th Edition.
Hector Garcia-Molina, Jeffrey D. Ullman, Jennifer Widom (2011). Database Systems: The Complete Book. Pearson Education.
Buy Itech1006 Database Management Systems-Australian Premier Assessment Answers Online
Talk to our expert to get the help with Itech1006 Database Management Systems-Australian Premier Assessment Answers to complete your assessment on time and boost your grades now
The main aim/motive of the management assignment help services is to get connect with a greater number of students, and effectively help, and support them in getting completing their assignments the students also get find this a wonderful opportunity where they could effectively learn more about their topics, as the experts also have the best team members with them in which all the members effectively support each other to get complete their diploma assignments. They complete the assessments of the students in an appropriate manner and deliver them back to the students before the due date of the assignment so that the students could timely submit this, and can score higher marks. The experts of the assignment help services at urgenthomework.com are so much skilled, capable, talented, and experienced in their field of programming homework help writing assignments, so, for this, they can effectively write the best economics assignment help services.
