DSC 450: Database Processing for Large-Scale Analytics Assignment Module 3

$30.00

Original Work ?
Category: You will Instantly receive a download link for .ZIP solution file upon Payment
Share This

Description

5/5 - (4 votes)

Part 1

You were hired to do some data analysis for a local zoo.  Below is the data table, including the necessary constraints and all the insert statements to populate the database.

 

— Drop all the tables to clean up

DROP TABLE Animal;

 

— ACategory: Animal category ‘common’, ‘rare’, ‘exotic’.  May be NULL

— TimeToFeed: Time it takes to feed the animal (hours)

CREATE TABLE Animal

(

AID       NUMBER(3, 0),

AName      VARCHAR2(30) NOT NULL,

ACategory VARCHAR2(18),

 

TimeToFeed NUMBER(4,2),

 

CONSTRAINT Animal_PK

PRIMARY KEY(AID)

);

INSERT INTO Animal VALUES(1, ‘Galapagos Penguin’, ‘exotic’, 0.5);

INSERT INTO Animal VALUES(2, ‘Emperor Penguin’, ‘rare’, 0.75);

INSERT INTO Animal VALUES(3, ‘Sri Lankan sloth bear’, ‘exotic’, 2.5);

INSERT INTO Animal VALUES(4, ‘Grizzly bear’, ‘common’, 3.0);

INSERT INTO Animal VALUES(5, ‘Giant Panda bear’, ‘exotic’, 1.5);

INSERT INTO Animal VALUES(6, ‘Florida black bear’, ‘rare’, 1.75);

INSERT INTO Animal VALUES(7, ‘Siberian tiger’, ‘rare’, 3.25);

INSERT INTO Animal VALUES(8, ‘Bengal tiger’, ‘common’, 2.75);

INSERT INTO Animal VALUES(9, ‘South China tiger’, ‘exotic’, 2.5);

INSERT INTO Animal VALUES(10, ‘Alpaca’, ‘common’, 0.25);

INSERT INTO Animal VALUES(11, ‘Llama’, NULL, 3.5);

 

Since none of the managers in the zoo know SQL, it is up to you to write the queries to answer the following list of questions.

  1. Find all the animals (their names) that take less than 2.5 hours to feed
  2. Find both the rare and exotic animals (in a single query)
  3. Return the listings for all animals whose rarity is missing (NULL) in the database
  4. Find the rarity rating of all animals that require between 1 and 2.5 hours to be fed
  5. Find the minimum and maximum feeding time amongst all the animals in the zoo (in a single SQL query)
  6. Find the average feeding time for all of the exotic animals
  7. Determine how many NULLs there are in the ACategory column using SQL
  8. Find all animals named ‘Alpaca’, ‘Llama’ or any other animals that are not listed as exotic

Part 2

 

  1. Write python code that is going to export a table from a SQLite database into a CSV file. You can use the attached SQLite_LoadAnimalTable.py to create and populate the table before you start.

Once you have created the database using attached code, your python code solution should query the rows from the Animal table in SQLite database and write the data into a new animal.txt file that is contains the comma-separated rows from the Animal table, e.g.,:

1, Galapagos Penguin, exotic, 0.5

2, Emperor Penguin, rare, 0.75

…

 

  1. Write python code that is going to load the comma-separated animal.txt file you have created in part-a into the Animal table in SQLite database. Your code must read the animal.txt file and use executemany() to load the data in python (i.e., your solution has to be a little different from the sample code from part 2-a to load the data). At the end of your code, you should verify how many rows were loaded by printing the output of

SELECT COUNT(*) FROM Animal;

 

 

 

Related products