Assignment 1
Objectives
• To develop an ER diagram from a provided scenario
• To create normalised relations of the data
• To create a Database Schema
Timelines and Expectations
• Percentage Value of Task: 20%
• Due: Friday, May 5th 2017, 5.00pm. (Week 7)
• Minimum time expectation: 12 hours
• The entities and relationships are in third normal form, unless valid reasons given
• Assumptions must be given if business rules are ambiguous or unclear
Learning Outcomes Assessed
The following course learning outcomes are assessed by completing this assessment:
• K4. design a relational database for a provided scenario utilising tools and techniques including ER diagrams, relation models and normalisation
• K5. describe relational algebra and its relationship to Structured Query Language (SQL);
• A1. design and implement a relational database using a database management system;
Project Specifications - Australian Premier League (APL)
The following are the requirements for a design of a database for a new premier soccer league in Australia. Representatives of current Australian state soccer clubs have come together to form the new Australian Premier League (APL). They have set up a Management Board to oversee the creation of this new league, and have commissioned you to design the database.
For this first stage, APL are only interested in maintaining data at the club level, to ensure that the clubs are tracking well for future success. Therefore, the APL needs information regarding the running and maintenance of the clubs, including their players and coaches, stadiums, sponsors and sponsor contributions, members, and all saleable merchandise. Any other club financial requirements, including club assets and running inventory are kept on separate financial databases, and are not part of this database project.
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.
? (Assumption): At least one of the clubs who has team(s) playing a game at the stadium will have their own stadium available.
? The league needs the stadium name, seating capacity, cumulative percent attendance and number of executive suites. A unique ID is also needed.
? Clubs have many coaches, at least one per the three divisions, but also clubs can have multiple coaches who assist. All coaches, however, can only belong to one club.
? The league keeps a record of the coach's first and last name and number of games they have coached. A coach will also need a unique ID. Head coaches may supervise other coaches, but are not supervised themselves. A coach can only be supervised by one head coach.
? A club also has many players, but players can only play for one club.
? The league keeps a record of a player’s first and last name, date of birth, career number of games for all games played and their current salary (in dollars). Also each player needs a unique ID.
? A player can be either a field player or a goal keeper, but are rarely both. The league needs to keep player’s performance tallys, in order to get an idea of how the players have been performing over time. (For example, they may do a query for a player's skill count divided by the player's number of games).
? The league would like to separate the two types of players, to save on the number of NULLS for which the player is not.
? For a field player the league wants to have a running total on: Number of shots on Target, Number of assists, Number of passes, Number of tackles and Number of penalties. Also a field type attribute stating if the player is an “Attacker”, “Midfield” or a “defender”.
? For a Goal keeper the league wants to have a running total on: Number of free kicks saved, Number of goal kicks, Number of normal saves and Number of goals conceded.
? There are three possible divisions that a coach can coach in, namely: “Senior”, “Youth” and “U18s”. A division can have many coaches, but a coach can only coach in one division.
? Players can play in more than one division, depending on their form and any injuries. For example a “Youth” may move up into the “Senior” division, if a “Senior” player has an injury. Also divisions contain many players.
? The league would also like to keep a tally of the number of games that a player has played in each division.
? For a division, the league would like to have the division name used as the unique ID, also an attribute giving a long description of the division would be useful.
Submission
Your submission should include:
• A completed SEIT submission coversheet signed. (A digital signature is fine)
• Your own front cover, showing a title; your name, your student number and acknowledgement of all students you have spoken to.
• A table of contents, showing all sections and page numbers.
• An ER Diagram with all entity names, attribute names, primary and foreign keys, relationships, cardinality and participation indicated. All many to many relationships should be resolved, and any self referential or weak entities, or super and subtypes should be correctly shown. Refer to the lecture notes for the expected course diagram notation. e.g.
? Entity NAMES in capitals, and singular
? Attributes as proper nouns
? Primary keys underlined
? Foreign keys in italics
? Correct cardinality and participation (optional/mandatory) symbols given
• Assumptions made, e.g. how you arrived at the cardinality/participation for those business rules not mentioned or unclear.
• A list of relations that translates your E-R diagram which includes:
? All table names, attributes, primary and foreign keys indicated. Again, as per the conventions given in the lecture slides and listed above.
? A discussion of normalisation, including:
? the normal form that each entity is in and why that is optimal
? also include how normalisation was achieved and reasons to why any relations are not in 3rd normal
• A relational database schema indicating the type and purpose of all attributes. In a tabular format, as shown in the template.
• The assignment is to be submitted via the Assignment 1 submission box in Moodle. This can be found in the Assessments section of the course Moodle shell.
Marking Criteria/Rubric
Assessment Criteria and Marking Overview Tasks Marks
1. Presentation 10
• Cover page indicating student name and number and tutor name. 2
• Page numbers included in report 2
• Index giving page numbers of various sections 2
• Overall presentation of the report 2
• Full APA referencing of all materials used and full disclosure of assistance from all sources including tutors and other students. 2
2. E-R diagram 50
• Completeness of diagram 8
• Correct notation and convention used 8
• All assumptions clearly noted 8 • Primary and foreign keys 10
• Resolution of many to many relationships 8
• Implementation of any weak or self referencing entities 4
• Implementation of any super and sub types 4
3. Normalisation 20
• All entities and relationship in appropriate normal form 10 • Discussion of normalisation for all entities and relationships 5
• Appropriate interpretation of each normal form, arguments for leaving the schema in the normal form you consider optimal. 5
4. Conversion of E-R diagram to relational schema 20
• Correct standards, conventions and notation used 2
• Primary keys used 2
• Foreign keys correctly identified including parent entity 6
• Schema is a correct translation of the E-R diagram submitted with appropriate tables, columns, primary keys, and foreign keys. 6
• Types and restrictions on attributes given 4
Total 100
Feedback
Feedback will be provided via the marked rubric with comments handed out in the tutorial 2 weeks after the submission date. The marks will also be available on FDL Marks at this time.
Plagiarism:
Plagiarism is the presentation of the expressed thought or work of another person as though it is one's own without properly acknowledging that person. You must not allow other students to copy your work and must take care to safeguard against this happening. More information about the plagiarism policy and procedure for the university can be found at http://federation.edu.au/students/learning-andstudy/online-help-with/plagiarism.