Abalone is a common name for any group of small to very large marine gastropod mollusks in the family Haliotidae. The flesh of abalones is widely considered to be a desirable food and is consumed raw or cooked by a variety of cultures.
Requirements:
CIS 2334 Semester Project
Part 1
Background Information
Abalone is a common name for any group of small to very large marine gastropod mollusks in the family Haliotidae. The flesh of abalones is widely considered to be a desirable food and is consumed raw or cooked by a variety of cultures. The haliotid family has a worldwide distribution. Most abalone species are found in cold waters, such as off the coasts of New Zealand, South Africa, Australia, Western North America, and Japan.
In February 2021, a group of marine biologists (collectors) wanted to study the diversity of the abalone in the US and monitor the species’ biological statistics. Funded by different universities, 15 marine biologists have teamed up and formed an investigation squad. They have traveled across the country and collected thousands of abalone samples from different coastal area in the US.
After an abalone sample was collected, the marine biologist would measure a group of biological statistics and document the collector’s information, as well as the water region information where the abalone had been collected.
The Abalone biological statistics the marine biologists measured include:
Gender (text) – abalone’s gender: M (male), F(female), I(infant)
Length (continuous) – Longest shell measurement
Diameter (continuous) – Perpendicular to length
Height (continuous) – abalone’s height with meat in shell
Whole weight (continuous) – abalone’s total weight
Shucked weight (continuous) – weight of meat
Viscera weight (continuous) – abalone’s gut weight
Shell weight (continuous) – abalone’s dry shell weight
Rings (integer) – the number of rings on abalone
The collector information includes collector first and last names and the collector organization. Water region information includes the name of the region where the abalone has been collected and the water temperature in degree centigrade (degree Celsius) when the abalone was collected.
As data collection work came to an end, the research team realized that they have collected a large amount of data, however, none of them are experts in data management. The research team is using a merged csv file to store the information in the cloud. Soon they noticed that they were facing many problems, such as how to store the data safely; how to handle the data input simultaneously from different team members; how to avoid human errors while data entering; how to search efficiently in the data; etc…
For the above reasons, the research team has contacted you to get some professional help. They would like you to create a database for them to handle their valuable (expensive and useful) data. They would like to see a prototype, and they already have made a few simple requests for the prototype database.
Project Tasks
To satisfy the research team’s requirements, you must use your database and SQL knowledge and you must demonstrate that your database is fulfilling their requirements. For this assignment, you must first ensure that you have installed your own copy of MySQL on your own computer. These activities will be done within that development environment. Please perform the following tasks:
Create the database:
Import the csv file into a MySQL database. (You can use the Import Wizard once you have created the database name with MySQL.)
Validation: you need to use a SQL command to check the import results and verify that the data has been successfully imported into the database. The SQL command to use will show the tables in the database.
File submission: Export the database (dump the database out of the server). You need to submit the dumped file (.sql). File name: Firstname_ Lastname_FullDB.sql
(with FirstName and LastName being your own first name and last name).
Report submission:
Create an MS Word document
Title: CIS 2334 Semester Project – Part 1.
Include your Last Name, your First Name, and your Cougar ID.
Use the subtitle “Question 1”.
Paste all the SQL commands you have used for this step.
Paste Screen shots of all the results.
Name your word file: Firstname_Lastname_ProjectPart1.doc(x).
Conduct a query and find all the abalones collected in Florida.
File submission: Export the results to a csv file.
Name your csv file Firstname_ Lastname_Florida.csv
You need to use a SQL command to find the all the desired results.
Report submission:
Use the same MS Word document.
Use the subtitle “Question 2”.
Paste all the SQL commands you have used for this step.
Paste Screen shots of all the results.
Conduct a query and find all the abalones that are infants.
File submission: Export the results in a csv file.
Name your csv file Firstname_ Lastname_Infant.csv.
You need to use a SQL command to find the all the desired results.
Report submission:
Use the same MS Word document.
Use the subtitle “Question 3”.
Paste all the SQL commands you have used for this step.
Paste Screen shots of all the results.
Conduct a query and find all the abalones that were collected in water with a temperature warmer than x degrees Celsius, x being the last 2 digits of your student ID divided by 5.
For example, if your student id is 12345678, x is 78/5 = 15.6. You will need to find all the abalones that are collected from water that is warmer than 15.6 degrees. If your last two digits are “00”, then use “20” instead which will give you an X value of 4.
File submission: Export the results into a csv file.
Name your csv file Firstname_Lastname_Warmer.csv
You need to use a SQL command to find the all the desired results.
Report submission:
Use the same MS Word document.
Use the subtitle “Question 4”.
Paste all the SQL commands you have used for this step.
Paste Screen shots of all the results.
Insert the following new data into the database
Collector: Jan Odom, University of Houston
Water info: Florida Atlantic Coast; 71.8 degrees Fahrenheit.
File submission: Export the results in a csv file.
Name your csv file Firstname_Lastname_New.csv.
You need to use a SQL command to find the all the desired results.
Report submission:
Use the same MS Word document.
Use the subtitle “Question 5”.
Paste all the SQL commands you have used for this step.
Paste Screen shots of all the results.
Delete the data for which the “Whole weight” is larger than y,
y being the last digit of your Student Id divided by 4.
For example, if your student id is 12345678, y is 8/4 = 2. You will need to delete all the data for which the “Whole_weight” is larger than 2. If the last digit of your ID is “0”, use “5” instead.
File submission: Export the results after the requested deletion in a csv file.
Name your csv file Firstname_Lastname_Weight.csv.
You need to use a SQL command to find the all the desired results.
Report submission:
Use the same MS Word document.
Use the subtitle “Question 6”.
Paste all the SQL commands you have used for this step.
Paste Screen shots of all the results.
Extra Credit
Extra Credit (Possible 8 points).
In this database, we first consider that the only Entity is abalone. Define the Primary Key for that Entity.
Find the shorthand representation of the corresponding table. Find all the functional dependences. (Possible 1 point)
Is the table in the first NF? If not, explain why not and then perform the first normalization. (Possible 1 point)
Is the table in the second NF? If not, explain why not and then perform the second normalization. (Possible 1 point)
Is the table in the third NF? If not, explain why not and then perform the third normalization. (Possible 1 point)
Create a CSV file for each table that has been created by the normalizations.
(Possible 3 points)
File submission: Name your csv files:
Firstname_Lastname_Abalone.csv,
Firstname_Lastname_Collectors.csv
Firstname_Lastname_Water.csv
Draw by hand an Entity-Relationship Diagram using the Crow’s foot representation. Take a picture of it. Alternative Option – You can also do the diagram in Vizio and submit a screenshot of your drawing. (Possible 1 point)
Report submission:
Use the same MS Word document.
Use the subtitle “Question 7, Extra Credit”.
Insert all the tables and Entity-Relationship Diagram.
Submission of Results
Export the word document into a PDF file.
Name your PDF file: Firstname_Lastname_ProjectPart1.pdf.
Submit all 7 files (.sql, .pdf and .csv files) to Canvas.
(10 files if you choose to also do the Extra Credit questions).
Collepals.com Plagiarism Free Papers
Are you looking for custom essay writing service or even dissertation writing services? Just request for our write my paper service, and we'll match you with the best essay writer in your subject! With an exceptional team of professional academic experts in a wide range of subjects, we can guarantee you an unrivaled quality of custom-written papers.
Get ZERO PLAGIARISM, HUMAN WRITTEN ESSAYS
Why Hire Collepals.com writers to do your paper?
Quality- We are experienced and have access to ample research materials.
We write plagiarism Free Content
Confidential- We never share or sell your personal information to third parties.
Support-Chat with us today! We are always waiting to answer all your questions.
