This assignment teaches you how to implement a database containing a hierarchy and ask queries containing aggregate operators and subqueries, starting with a specification.

You must continue the database received in S3. Do not wait for S3 to be graded. You must use your submission. Mistakes in S3 will not be graded. However, you may update your S3 if you need to.

General Instructions

You will receive a unique random database specification. You must follow these instructions for your received database specification.

You must perform the following tasks. The provided specification is different for each student. You must make the ERD the design and implement the database for the specification you received.

This is a CLOSED BOOK assignment, and you are not allowed to use any external information to solve it. Be sure that you use the URL that was assigned to you. Solving a different version of the assignment will result in a grade of zero for this assignment and may result in honor committee referral.

Database Model Conventions

You must follow these conventions when you perform your homework. Failing to follow the conventions will be penalized per each mistake and may also constitute evidence of cheating.

• Entity names: singular nouns (or noun phrase) written in CAPITAL LETTERS, using _ instead of space, must end with a two letter sufix with your first name and last name initials (e.g. if your name is John Doe, an entity name will be PERSON_JD)
• Attribute names: singular nouns (or noun phrase) written in CAPITAL LETTERS, using _ instead of space, must end with a two-letter sufix with your first name and last name initials (e.g. if your name is John Doe, an attribute name will be FIRST_NAME_JD)
• Relationship names: third person verbs (or verb phrase) written in lower case letters, using spaces. Do not use "has" or other generic names. Use a name that clarifies the meaning (even if contains entity names)
• Additional conventions might be specified by your instructor (e.g. prefix the attribute names with the entity name or abbreviation)

Your report must contain the following:

• Assignment: S4
• Name: YOUR NAME
• Section: YOUR COURSE AND SECTION
• Task 1 - ERD Model
  • Based on the specification received improve the ERD from S3 in MySQL Workbench.
  • Sign the ERD by writing your name in a text object.
  • Take a picture of your ERD.
  • Include in the report file the following:
    • A heading: Task 1 - ERD Model
    • The signed screenshot (image) of the ERD
• Task 2 - Creat database
  • Start writing the SQL script in MySQL Workbench to perform the following operations:
    • Include a comment: Task 2 Create database by ... (your name)
    • If there is a database with the provided name, delete that database.
    • Add a database with that name.
    • Mark the created database as the default database for the following operations.
    • Show all the databases, find the created one in the list
  • Execute and debug the script in MySQL Workbench until runs without error and performs the above operations.
  • Include in the report file the following:
    • A heading: Task 2 - Create database
    • A screenshot (image) of the source code for this task only (crop the image if necessary) - top of the screen
    • A screenshot (image) of the results for the performed operations - bottom of the screen
• Task 3 - Create tables
  • Continue writing the SQL script in MySQL Workbench, adding the following operations:
    • Include a comment: Task 3 - Create tables by ... (your name)
    • List all the tables in the database (no table expected).
    • Create the tables from both S3 and S4 with the required specification. Add to the tables in S3 any new table required by S4.
    • List all the tables in the database (the new tables must appear).
    • Show the structure of the created tables (both S3 and S4).
  • Execute and debug the script in MySQL Workbench until runs without error and performs the above operations.
  • Include in the report file the following:
    • A heading: Task 3 - Create tables
    • A screenshot (image) of the source code for this task only (crop the image if necessary) - top of the screen
    • A screenshot (image) of the results for the performed operations - bottom of the screen - for tables in S4 only.
    • Screenshots with the generated tables by the above commands, included in the order of the commands- middle of the screen in different tabs - for S4 only.
• Task 4 - Add data
  • Continue writing the SQL script in MySQL Workbench, adding the following operations:
    • Include a comment: Task 4 - Add data by ... (your name)
    • Show all the data in your tables (no data expected).
    • Add at least 5 rows of data in each of the tables (both from s3 and s4), start with the provided data items in the order they are provided, and imagine new data for the other ones. Add to the data for S3 tables from the previous submisssion, data for S4 tables.
    • Show all the data in your table (at least five rows expected in each table).
  • Execute and debug the script in MySQL Workbench until runs without error and performs the above operations.
  • Include in the report file the following:
    • A heading: Task 4 - Add data
    • A screenshot (image) of the source code for this task only (crop the image if necessary) - top of the screen
    • A screenshot (image) of the results for the performed operations - bottom of the screen - only for S4 tables
    • Screenshots with the generated tables by the above commands, included in the order of the commands - middle of the screen in different tabs - only for S4 tables
• Task 5 - Query the data
  • Continue writing the SQL script in MySQL Workbench, adding the following operations:
    • Include a comment: Task 5 - Query the data by ... (your name)
    • Perform the queries from your S4 specification. Do not include the queries for S3.
  • Execute and debug the script in MySQL Workbench until runs without error and performs the above operations.
  • Include in the report file the following:
    • A heading: Task 5 - Query the data
    • A screenshot (image) of the source code for this task only (crop the image if necessary) - top of the screen
    • A screenshot (image) of the results for the performed operations - bottom of the screen
    • Screenshots with the generated tables by the above commands, included in the order of the commands - middle of the screen in different tabs

What to submit

You must submit the following files:

• A PDF file containing the report for your assignment.
• A MWB file containing the database model done in MySQL Workbench
• A SQL text file containing the script to create and query the database for your assignment.

Submission instructions:

• You must use assignment submission item on Blackboard to submit your assignment.
• You must name the files:
  • S4_A1_lastname_firstname.pdf - for the report
  • S4_A1_lastname_firstname.mwb - for the source MySQL Workbench of your ERD
  • S4_A1_lastname_firstname.sql - for the script
• You must not have the files open in a program when you submit them, because they might not be submitted correctly.
• Double check the files before submission. You are not allowed to resubmit your assignment. Do not ask for resubmission because cannot be granted. Allowing one student to resubmit will imply to allow all the students to resubmit from all the sections (in order to be fair). This cannot be handled.

ZERO GRADE:

You will receive a grade of zero in ANY of the following situations:

• Not following the instructions from this semester (e.g. following instructions aspects from previous semesters, e.g. the format of your report)
• Not following your specifications (2-3 elements in your design are not based on this semester requirements but they follow previous semesters requirements or other versions of questions from this semester)
• Not submitting the files in the correct place (each file must be submitted to the corresponding question)
• Your ERD screenshot(s) are not signed, or are not signed correctly (a textbox added in MySQL Workbench with your name)
• The names of your entity do not follow the name prefix/suffix rule described below.
• The names of your attributes do not follow the name prefix/suffix rule described below.
• The code has very similar parts with another student's submission.
• You are not continuing your S3 homework but a diferent specification.

QUESTION 1

1. Requirements Specification (this is a fictional scenario)

Continue your S3 assignment for a young soccer league with the following specification.

A player plays in a single team in a given season. However, the player may change the teams from a season to the next. For a player we keep the name (we want to search based on the last name), the date of birth, the jersey number (which can change when the player changes the team or from a season to another) and the player home city.

A coach coaches one or more teams per seasons, but at most 5. A team is coached by a single coach. For a coach we keep the name (we search based on first and last name), the date of birth, the year starting coaching and home zipcode.

For the new entities in S4, as applicable, the first, last and middle name components are at most 22 characters long. We store Zipcodes-5. Cities are at most 25 characters long.

Organize the persons associated with the league in a hierarchy.

1. Database Constraints for Step 4

• Two players have the jersey number 1 and all the other players have bigger numbers.
• Two coaches started to coach in 2019, while all the others earlier.
• Have at least two players registered to a team in SPRING 2018 and two in Fall 2018.
• Have at least two coaches hired by a club in SPRING 2018 and two in FALL 2018.

1. Database Questions for Step 5

Your questions must work on any data, you must not assume anything about the data when writing the question.

• Count all the players registered for a team in Fall 2018.
• Count all the coaches hired by a club in Spring 2018.
• List the player(s) that have the smallest jersey number (the full name in a column named player).
• List the least experienced coaches (the full name in a column named coach).

Attach the PDF report as the answer to this question.

QUESTION 2

Include the design file (MWB) here.

1. Attach File

0.2 points

QUESTION 3

Include the SQL script file as the answer to this question (graded for consistency and execution)

SEASON_MA SEASON_CODE_MA VARCHAR(10) SEASON_NAME_MA VARCHAR(6) YEAR_MA YEAR(4) REG_STARTING_DATE_MA DATE REG_ENDING_DATE_MA DATE Indexes Indexes CLUB_MA CLUB_ID_MA INT CLUB_NAME_MA VARCHAR(24) >CLUB_EMAIL_MA VARCHAR(35) CLUB_PHONE_MA VARCHAR(12) H+ H+ plays for organizes DIVISION_MA DIVISION_ID_MA INT SEASON_CODE_MA VARCHAR(10) DIVISION_CODE_MA CHAR(3) AGE_GROUP_MA VARCHAR(3) KPLAYERS_SEX_MA CHAR(1) RANK MA INT Indexes EMAIL_MA VARCHAR(35) PHONE_MA VARCHAR(12) SEASON_MA_SEASON_CODE_MA VARCHAR(10) Indexes TEAM_MA TEAM_ID_MA INT TEAM_NAME_MA VARCHAR(20) CLUB_CODE_MA CHAR(5) BIRTH_YEAR_MA YEAR(4) SEX MA CHAR(1) TEAM_PHONE_MA CHAR(12) TEAM_EMAIL_MA VARCHAR(35) CLUB_MA_CLUB_ID_MA INT ++ register for H+ Maram Alhumoud opens for MEMBERSHIP_MA DIVISION_ID INT TEAM_ID_MA INT REGISTRATION_DATE_MA INT ? DIVISION_MA_DIVISION_ID_MA INT ? TEAM_MA_TEAM_ID_MA INT Indexes