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

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

$30.00

Category: You will Instantly receive a download link for .zip solution file upon Payment || To Order Original Work Click Custom Order?

Description

5/5 - (5 votes)

Part 1

  1. Using the extended Zoo database (ZooDatabase_extended.sql), write the following queries in SQL:

 

  1. Find all the rare animals and sort the query output by feeding time (from small to large)
  2. Find the animal names and categories for animals related to a bear (hint: use the LIKE operator)
  3. Find the names of the animals that are related to the tiger and are not common
  4. Find the names of the animals that are not related to the tiger

 

  1. List the animals (animal names) and the ID of the zoo keeper assigned to them.
  2. Now repeat the previous query and make sure that the animals without an assigned handler also appear in the answer.
  3. Report, for every zoo keeper name, the average number of hours they spend feeding all animals in their care.
  4. Report every handling assignment (as a list of assignment date, zoo keeper name and animal name).  Sort the result of the query by the assignment date in an ascending order.

 

 

  1. Repeat the following queries using python (i.e., by reading data from animal.txt, without using a database)

 

  1. Find the names of the animals that are related to the tiger and are not common
  2. Find the names of the animals that are not related to the tiger

 

 

Part 2

 

  1. You are given a following schema in 1NF:

(First, Last, Address, Job, Salary, Assistant) and the following set of functional dependencies:

 

First, Last à Address

Job à Salary, Assistant

 

Decompose the schema to make sure it is in Third Normal Form (3NF).

 

  1. Write the necessary SQL DDL statements (CREATE TABLE) to define these the tables you created

 

  1. Write a python script that is going to create your tables and populate them with data automatically from data_module4_part2.txt (file attached). You do not have to use executemany, your python code can load data row-by-row. Make sure that you are inserting a proper NULL into the database. HINT: You can use INSERT OR IGNORE statement (instead of a regular INSERT statement) in SQLite to skip over duplicate primary key inserts without throwing an error.

 

For example:

cursor.execute(“INSERT OR IGNORE INTO Animal VALUES(?,?,?,?)”, [11, ‘Llama’, None, 3.5]);

would automatically ignore the insert if animal with ID 11 already exists in the database and insert a NULL into the third column. If you use ‘NULL’ value instead, animal category would be set to the 4-character string ‘NULL’

 

  1. Verify that your NULLS are loaded correctly, by finding all jobs with no salary specified using Salary IS NULL

 

 

Related products