INSTRUCTIONS
InstructionsThis assignment teaches you how to implement a small database and ask simple queries, starting with the design you performed in part 1.
General Instructions
Implement the design performed in part 1. Do not implement part 2. You must follow these instructions for your design.You must perform the following tasks. The provided specification is different for each student. You must update the ERD design based on the corrections and implement the database for the updated ERD.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 question 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/Tables 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)
Attributes/Columns 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: P3
Name: YOUR NAME
Section: YOUR COURSE AND SECTION
A suggestive title for your report
A short original description of the database (at least two sentences)
Relevant graphics or picture (must be a suggestive picture for the content of your report)
Task 1 – ERD Model
Based on the specification received create a ERD 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 (based on your project part 1 received specification)
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
Screenshots with the generated tables by the above commands, included in the order of the commands – middle of the screen in different tabs
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.
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
Screenshots with the generated tables by the above commands, included in the order of the commands- middle of the screen in different tabs
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
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
Screenshots with the generated tables by the above commands, included in the order of the commands – middle of the screen in different tabs
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 specification.
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
SScreenshots 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 (to their corresponding question – each question mention what file to attach):
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:
P3_A1_lastname_firstname.pdf – for the report
P3_A1_lastname_firstname.mwb – for the source MySQL Workbench of your ERD
P3_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.
The report was done based on specifications from a previous semester.
Your ERD screenshot(s) are not signed, or 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 above.
The names of your attributes do not follow the name prefix/suffix rule described above.
QUESTION 1
Requirements Specification (this is a fictional scenario)Implement the database model done as part of your project part 1 submission (not the part 2) following the instructions.
Database Questions for Step 5
List all students names (in a column named ‘Student’) in alphabetical order, with the age level (in a column named ‘Level’), color and phone (in a column named ‘Class Phone’) of the class and the name of the teacher (in a column named ‘Teacher’).
For each class level and color show all the student enrolled in alphabetical order per each class (i.e. you start with blue foxes and show all the students enrolled alphabetically, then you go to green foxes, and so on) and their date of birth. Use the following headers for the columns: Color, Level, Student Name, DOB
For each parent (caretaker) in alphabetical order (show name and home phone), list all the students enrolled (show name, class level and color)
For each teacher (show name in alphabetical order) list all the parents (show name in alphabetical order per teacher) and their students (show names in alphabetical order per parent). Use columns headers: teacher, parent, student
Attach the PDF report as the answer to this question.
QUESTION 2
Include the MWB file for the project (here graded for submission and consistency with the report). The correctness is graded in the report.
QUESTION 3
Include the SQL file for the project (here graded for submission and consistency with the report). The correctness is graded in the report
