CREATE TABLE DOG_OWNER (OWNER_ID INTEGER, OWNER_FIRST_NAME VARCHAR2(30) NOT NULL

Question

CREATE TABLE DOG_OWNER
(OWNER_ID INTEGER,
OWNER_FIRST_NAME VARCHAR2(30) NOT NULL,
OWNER_LAST_NAME VARCHAR2(30) NOT NULL,
OWNER_PHONE VARCHAR2(15) NOT NULL,
EMP_ID INTEGER NOT NULL,
PRIMARY KEY (OWNER_ID),
FOREIGN KEY (EMP_ID) REFERENCES DOG_OWNER (OWNER_ID));
CREATE TABLE DOG
(DOG_NAME VARCHAR2(30) NOT NULL,
DOG_BREED_ID INTEGER NOT NULL,
DOG_DOB DATE NOT NULL,
DOG_SEX VARCHAR2(6),
OWNER_ID INTEGER,
PRIMARY KEY (DOG_NAME, DOG_DOB),
FOREIGN KEY (DOG_BREED_ID) REFERENCES DOG_BREED (DOG_BREED_ID),
FOREIGN KEY (OWNER_ID) REFERENCES DOG_OWNER (OWNER_ID));
CREATE TABLE EMPLOYEE
(EMP_ID INTEGER NOT NULL,
EMP_FIRST_NAME VARCHAR2(30),
EMP_LAST_NAME VARCHAR2(30),
EMP_DOB DATE,
EMP_STREET_NUM INTEGER,
EMP_STREET_NAME VARCHAR2(30),
EMP_CITY VARCHAR2(30),
EMP_STATE VARCHAR2(30),
EMP_ZIP VARCHAR2(30),
PRIMARY KEY (EMP_ID));
CREATE TABLE DEPENDENT
(DEPENDENT_FIRST_NAME VARCHAR2(30) NOT NULL,
DEPENDENT_MIDDLE_NAME VARCHAR2(30) NOT NULL,
DEPENDENT_LAST_NAME VARCHAR2(30) NOT NULL,
DEPENDENT_DOB DATE,
DEPENDENT_SEX VARCHAR2(1),
EMP_ID INTEGER,
PRIMARY KEY (DEPENDENT_DOB, EMP_ID),
FOREIGN KEY (EMP_ID) REFERENCES EMPLOYEE (EMP_ID));
CREATE TABLE WALKS
(EMP_ID INTEGER NOT NULL,
DOG_NAME VARCHAR2(30),
DOG_DOB DATE,
START_DATE_TIME DATE,
END_DATE_TIME DATE,
PARK_NAME VARCHAR2(50),
PRIMARY KEY (EMP_ID, DOG_NAME, DOG_DOB, START_DATE_TIME),
FOREIGN KEY (EMP_ID) REFERENCES EMPLOYEE (EMP_ID),
FOREIGN KEY (DOG_NAME, DOG_DOB) REFERENCES DOG (DOG_NAME, DOG_DOB));
1,Using your own words and NOT SQL syntax, what does line 45 ensure will happen?

2, A correlated sub-query executes once for each row of the outer query; how can you tell it’s “correlated”?

3,Write a SQL query with proper syntax to retrieve the phone number of the dog owner named “Paul Beckman”.

4,Without using ANY SQL or database terms, what is line 16 supposed to do? Will it work? Why or why not?

Explanation / Answer

Solution

Part-1)-PRIMARY KEY (EMP_ID, DOG_NAME, DOG_DOB, START_DATE_TIME),-à

This is example of Composite key, or composite primary key, in this case we consider more than one column is used to specify the primary key of a table

Example CREATE TABLE TABLE_NAME
(COLUMN_1 DATA_TYPE_1,
COLUMN_2 DATA_TYPE_2,
…
PRIMARY KEY (COLUMN_1, COLUMN_2, …));

Here 4 column (EMP_ID, DOG_NAME, DOG_DOB, START_DATE_TIME) are used as primary key

Part-2)- A correlated sub-query executes once for each row of the outer query; how can you tell it’s “correlated”

Correlated subquery- we know that a correlated subquery special type of subquery that depends on the outer query.

In correlated query we use data of outer query in where clause

The main difference between a correlated subquery and a simple subquery is that correlated subqueries reference columns from the outer table

Syntax of Correlated Query

SELECT Column[s] FROM <table name> <table reference>

WHERE Condition (SELECT Column[s]

                   FROM <same table name> <table reference>

                   WHERE Clause)

Example Query to find all employees with a salary higher than their average departmental salary

SELECT  lastname,  firstname, salary FROM employee e1

    WHERE e1.salary > (SELECT avg(salary)

                   FROM employee e2

                   WHERE e2.dept_id = e1.dept_id)

In the above example, e1.dept_id is a reference to the outer subquery table.

There is no shortcut for identifying the correlated query but to identify a correlated query, we look for these kinds of references in the query.

Part-3)- Write a SQL query with proper syntax to retrieve the phone number of the dog owner named “Paul Beckman

Select OWNER_PHONE from DOG_OWNER where OWNER_FIRST_NAME =’ Paul’ and    OWNER_LAST_NAME =’ Beckman’

Part-4) FOREIGN KEY (DOG_BREED_ID) REFERENCES DOG_BREED (DOG_BREED_ID)

This is the syntax of foreign key

FOREIGN KEY is a key used to link two tables together .FOREIGN KEY is a field in one table that refers to the PRIMARY KEY in another table.

Here DOG_BREED_ID is the FOREIGN KEY field of DOG and it’s refer to the primary key of DOG_BREED_ID of DOG_BREED table.

But it will not work because in this question there is no DOG_BREED table for mapping with DOG_BREED_ID,

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

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