Probie1 heie: https://www.ciiegg.com/homework-help/quesions- and-answers/databas

Question

Probie1 heie: https://www.ciiegg.com/homework-help/quesions- and-answers/database-systems-sql-problem-1-matrix- manipulations-optimal-use-sql-matrix-operations-impl-q23843783 Database Systems, SQL Problem 2: The Pets Database There is another way to import data into mysql. When you start mysql, you should use the-enable-local-infile option sudo mysql-enable-local-infile -u root This allows you to import individual tables into a database. Create a new database called pets. a)Create two tables: Pet(name: VARCHAR(20),owner: VARCHAR(20) species:VARCHAR(20),sex:CHAR(1),birth: DATE,death:DATE) and Event (name:VARCHAR(20),date:DATE,type:VARCHAR(15), remark:VARCHAR(255)) b)Load data into these tables using the two file provided Pet Table Data and Pet Events Table Data. You can use the following command syntax example for Pet Table Data: mysql> LOAD DATA LOCAL IN FILE-.Pet Table Data.txt" INTO TABLE pet FIELDS TERMINATED BY ‘,'; cWrite a SQL query against this database. We'd like to see all the male cats and all the female dogs. d)Write a SQL query that provides the age of each pet that has had a litter. Pet Table Data Fluffy, Harold, cat, f, 1993-02-04, N, Claws, Gwen,cat,m,1994-03-17,N Buffy, Harold, dog, f,1989-05-13,N Fang, Benny, dog ,m,1990-08-27, N, Bowser, Diane, dog,m,1979-08-31,1995-07-29, Chirpy,Gwen , bird, f, 1998-09-11, N, Whistler , Gwen,bird,N, 1997-12-09, w, Slim, Benny, snake,n, 1996-04-29,w, Pet Events Table Data Fluffy,1995-05-15,litter,4 kittens 3 female 1 male, Buffy, 1993-06-23,litter,5 puppies 2 female 3 male, Buffy, 1994-06-19,litter,3 puppies 3 female Chirpy, 1999-03-21,vet,needed beak straightened, Slim, 1997-08-03,vet, broken rib, Bowser, 1991-10-12, kennel, Fang, 1991-10-12,kennel,, Fang, 1998-08-28,birthday, Gave him a new chew toy, Claws, 1998-03-17,birthday, Gave him a new flea collar, Whistler,1998-12-09,birthday,First birthday,

Explanation / Answer

First open MySQL server then write command

sudo mysql --enable-local-infile --u root

It will allow to import tables from database.

Then create the database pet with command

create database pet;

After the creation of database write the query to create tables PET and PET_EVENT

CREATE TABLE PET(
    NAME VARCHAR(20) NOT NULL,
    OWNER VARCHAR(20),
    SPECIES VARCHAR(20),
    SEX VARCHAR(1),
    BIRTH DATE,
    DEATH DATE,
    PRIMARY KEY(NAME)
)

Here name is set as a primary key because to find age of pet we will use table join with another table PET_EVENT. For that we need NAME as a foreign key. You can set only primary key or not null key as a foriegn key.

CREATE TABLE PET_EVENT(
    NAME VARCHAR(20),
    EDATE DATE,
    TYPE VARCHAR(15),
    REMARK VARCHAR(255),
    FOREIGN KEY(NAME) REFERENCES pet(NAME)
)

After creating tables load the table data of pet table using command

LOAD DATA LOCAL INFILE "Pet Table Data.txt" INTO TABLE PET FEILDS TERMINATED BY ',';

Then load table data of pet events write command

LOAD DATA LOCAL INFILE "Pet Events Table Data.txt" INTO TABLE PET FEILDS TERMINATED BY ',';

Now database and the required tables are created and it has the required data. Now you can run the SQL query against the table.

To see all the male cat and female dog, write the query

select * from pet where (species='cat' AND sex='m') OR (species='dog' AND sex='f');

To find the age of a pet write a query

select p.name,(sysdate-p.birth) as "Age"

from PET p, PET_EVENT e

where p.name=e.name AND e.type='litter'

Related Questions

Navigate

• Browse (All)
• Browse P
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.