Question Details

[solution] » Database Processing for Large-Scale Analytics Assignment 2 Part

Brief item decscription

Step-by-step solution file


Item details:

Database Processing for Large-Scale Analytics Assignment 2 Part
More:

Hi hch88,

I need help for assignment #2 of my Database class. I've attached the document. Like previous times please provide help according to the specs in the document. 
Database Processing for Large-Scale Analytics

 

Assignment 2 Part 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).

 

Write SQL DDL and SQL INSERT statements to create the 3NF tables and load the data provided in the

 

text file data_hw2.txt (posted together with this assignment). Make sure that NULL in the data file is

 

loaded as database NULL and not as ?NULL? string. You can use your python code from Assignment 1

 

to generate these INSERT statements. Part 2

 

Write a python script that is going to create your tables from Part 1 and populate them with data

 

automatically. Use sqlite3 database as shown in class and make the necessary data type changes in your

 

DDL from part1 (e.g., NUMBER(5,0)INTEGER, NUMBER(5,2)REAL).

 

Your python code must successfully load data into all tables (however many tables you have created in

 

Part-1). Note that you are loading data into SQLite, not into Oracle (the process is the same, but

 

installing cx_Oracle for python is somewhat complicated). Your python file must read the input file

 

data_hw2.txt directly and automatically load the data, do not simply copy-paste INSERT statements

 

from Part 1. Part 3

 

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.5);

 

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

 

INSERT INTO Animal VALUES(9, 'South China tiger', 'exotic', 2.25);

 

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 1.5 hours to feed.

 

2. Find all the rare animals and sort the query output by feeding time (from small to large)

 

3. Find the animal names and categories for animals related to a bear (hint: use the LIKE operator)

 

4. Return the listings for all animals whose rarity is missing in the database

 

5. Find the rarity rating of all animals that require between 1 and 2.5 hours to be fed

 

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

 

7. Find the names of the animals that are not related to the tiger

 

8. Find the minimum and maximum feeding time amongst all the animals in the zoo

 

9. Find the average feeding time for all of the rare animals

 







About this question:
STATUS
Answered
QUALITY
Approved
ANSWER RATING

This question was answered on: Feb 21, 2020

PRICE: $24

Solution~00066555096.zip (18.37 KB)

Buy this answer for only: $24

This attachment is locked

We have a ready expert answer for this paper which you can use for in-depth understanding, research editing or paraphrasing. You can buy it or order for a fresh, original and plagiarism-free copy (Deadline assured. Flexible pricing. TurnItIn Report provided)

Pay using PayPal (No PayPal account Required) or your credit card. All your purchases are securely protected by PayPal.
SiteLock

Need a similar solution fast, written anew from scratch? Place your own custom order

We have top-notch tutors who can help you with your essay at a reasonable cost and then you can simply use that essay as a template to build your own arguments. This we believe is a better way of understanding a problem and makes use of the efficiency of time of the student. New solution orders are original solutions and precise to your writing instruction requirements. Place a New Order using the button below.

Order Now