INSTRUCTIONS
InstructionsThis assignment teaches you how to implement a more complex database and ask queries containing groups, starting with a specification.You must continue the database received in S3 and S4.
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 create 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 start 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 start 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: S5
Name: YOUR NAME
Section: YOUR COURSE AND SECTION
Task 1 – ERD Model
Based on the specification received improve the ERD from S3 and S4 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 – Create 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 with the required specification. Add to the tables in S3 and S4 any new table required by S5
List all the tables in the database (again – the new table must appear).
Show the structure of the created tables.
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 S5 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, 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 and S4 tables from the previous submisssion, data for S5 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 S5 specification. Do not include the queries for 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 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:
S5_A1_lastname_firstname.pdf – for the report
S5_A1_lastname_firstname.mwb – for the source MySQL Workbench of your ERD
S5_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:
The report was done based on instructions from a previous semester.
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 different specification
QUESTION 1
Requirements Specification (this is a fictional scenario)Continue your S3 and S4 assignment for a young soccer league with the following specification. Do not include the previous queries from Task.A team will play some of the other teams in the same division once per season. For a scheduled game we will keep a unique integer code, the date, time and final score.
Database Questions for Step 4
Define a current season with the same year as the current year and the same semester as the current semester (fall, spring, summer).
Be sure you have at least 2 divisions in the current season, they must have at least 3 teams each, and they must play one game to each other in the current season. The teams must have at least 2 players and a coach.
Database Questions for Step 5
For each date (chronologically) compute the number of games.
For each club (in alphabetic order) compute the total number of teams playing in the current season.
For each division compute the total number of teams enrolled. Sort chronologically.
For each coach (in alphabetic order) compute the total numbers of wins.
Attach the PDF report as the answer to this question.
QUESTION 2:INCLUDE THE DESIGN FILE (MWB) HERE.
QUESTION 3
Include the SQL script file as the answer to this question (graded for consistency and execution) Attach File
