01 Relational Database Technology

• ·         An entry in a table, consisting of values for each appropriate column. -row
• ·         The set of mandatory columns within a table that is used to enforce uniqueness of rows, and that is normally the most frequent means by which rows are accessed. – primary key
• ·         An arrangement of data in rows and columns. -Table
• ·         A column or set of columns that refers to a primary key in the same table or another table. –foreign key
• ·         Collections of objects or relations, set of operators to act on those relations, and data integrity for accuracy and consistency –relational dB
• ·         Intersection of a row and column - field
• ·         Used to modify the table data by entering, changing, or removing rows –data manipulation language
• ·         Creates, changes, and removes data structures from the data-base – data definition language
• ·         Used to manage the changes made by DML statements –transaction control language
• ·         Used to give or remove access rights to the database and the structures within it –data control language

1. The Global Fast Foods database consists of how many tables? ____ tables

SELECT  DISTINCT owner, count(table_name)

FROM all_tables

GROUP BY owner;

OWNER	COUNT(TABLE_NAME)
MDSYS	48
CTXSYS	5
OLAPSYS	2
SYSTEM	7
XDB	2
HKUMAR	35
SYS	37

But this contains tables from all the ERD’s in:

And I need for:

Specific to Global Fast Foods ERD [9] are:

·         F_ORDER_LINES

·         F_FOOD_ITEMS

·         F_PROMOTIONAL_MENUS

·         F_REGULAR_MENUS

·         F_SHIFTS

·         F_SHIFT_ASSIGNMENTS

·         F_STAFFS

·         F_CUSTOMERS

·         F_ORDERS

2. How is the F_SHIFTS table related to the F_STAFFS table?

SELECT DISTINCT  table_name from user_constraints

WHERE r_constraint_name in

(

SELECT constraint_name

FROM user_constraints

WHERE table_name in (upper('f_shifts'), upper('F_STAFFS'))

);

Above query gives an idea that, go look study tables F_ORDERS, F_SHIFT_ASSIGNMENTS

Now finally I found that, F_SHIFTS table related to the F_STAFFS – both having foreign key reference in F_SHIFT_ASSIGNMENTS.

3. What are the names of the columns in the F_CUSTOMERS table?

SELECT column_name

FROM   all_tab_cols

WHERE  table_name = 'F_CUSTOMERS'

COLUMN_NAME:

·         ID

·         FIRST_NAME

·         LAST_NAME

·         ADDRESS

·         CITY

·         STATE

·         ZIP

·         PHONE_NUMBER

4. How many rows of data have been entered in the F_PROMOTIONAL_MENUS table?

select count(*) from F_PROMOTIONAL_MENUS

Result is 2.

5. In the F_FOOD_ITEMS table, column _________ is a foreign-key column. What table and column is this key referencing?

select *

     from user_constraints

     where table_name = 'F_FOOD_ITEMS' and CONSTRAINT_TYPE = 'R';

select table_name from user_constraints

where constraint_name in

(

select r_constraint_name

     from user_constraints

     where table_name = 'F_FOOD_ITEMS' and CONSTRAINT_TYPE = 'R'

);

"REGULAR_CODE"  to "CODE" in "F_REGULAR_MENUS"

"PROMO_CODE"  to "CODE" in "F_PROMOTIONAL_MENUS"

6. List the primary key to foreign key relationships required to go from the F_SHIFTS table to the F_REGULAR_MENUS table.

·         This must be there to identify hours served. 

·         Now, ideally it should have been like this:

But seems to be, our fellow restaurant owner have separate regular menu for each shift. So ignore suggestion above.

·         Now again, values of hours served in regular menu table ['6-11am', '11-9pm']  don’t match any shift timings [8am to 12pm, 6pm to 10pm], seems to be these values on menu side are meant to be lost while this exercise J

--remove HOURS_SERVED

alter table "F_REGULAR_MENUS"  drop column "HOURS_SERVED" ;

--add SHIFT_CODE

alter table "F_REGULAR_MENUS" add  "SHIFT_CODE"  NUMBER(5, 0) ;

--give some value so that it may be set to not null later

UPDATE "F_REGULAR_MENUS"  SET "SHIFT_CODE" = 1;

--set SHIFT_CODE to not null

ALTER TABLE "F_REGULAR_MENUS" MODIFY ("SHIFT_CODE" NUMBER(5,0) CONSTRAINT "F_RMU_SHIFT_CODE_NN" NOT NULL ENABLE);

--now create the foreign key constraint

ALTER TABLE  "F_REGULAR_MENUS"  ADD CONSTRAINT "F_RMU_SHIFT_CODE_FK" FOREIGN KEY ("SHIFT_CODE")  REFERENCES  "F_SHIFTS" ("CODE")  ENABLE;

7. Which table(s) contains null values?

As per definition available at https://docs.oracle.com/cd/B19306_01/server.102/b14237/statviews_2094.htm, below mentioned query would have been sufficient to give this result:

SELECT distinct "TABLE_NAME" FROM   "USER_TAB_COLUMNS" WHERE   "NULLABLE" = 'Y' and "NUM_NULLS" > 0

Or

SELECT count(distinct "TABLE_NAME") FROM   "USER_TAB_COLUMNS" WHERE   "NULLABLE" = 'Y' and "NUM_NULLS" > 0

But,  It didn’t work as per design, due to some index issues.

So I removed the last condition in above query and manually verified each table: [10]

Table	Has some field null
EMPLOYEES	yes
LOCATIONS	no
EMP_DETAILS_VIEW	n/a	it's a view
JOBS	no
JOB_GRADES	no
REGIONS	no
COUNTRIES	no
D_CLIENTS	no
D_JOB_ASSIGNMENTS	no
D_SONGS	no
D_VENUES	no
F_ORDERS	no
F_STAFFS	yes
JOB_HISTORY	no
WF_COUNTRIES	yes
D_EVENTS	no
HTMLDB_PLAN_TABLE	yes
WF_CURRENCIES	yes
WF_SPOKEN_LANGUAGES	yes
D_PLAY_LIST_ITEMS	yes
F_PROMOTIONAL_MENUS	no
DEPARTMENTS	yes
F_FOOD_ITEMS	yes
D_PARTNERS	yes

But to verify my manual observation above, I needed something, for which I created below mentioned query:

SELECT 'SELECT  count(*) from "' ||  "TABLE_NAME"  || '" where "' ||   "COLUMN_NAME"  ||'" is null;' as query FROM   "USER_TAB_COLUMNS" WHERE   "NULLABLE" = 'Y';

This gives output like:

SELECT count(*) from "D_PARTNERS" where "EXPERTISE" is null;

(112 count)

I executed each query  generated above to verify above results.

Sample queries to verify above results (off the 112 generated):

SELECT count(*) from "DEPARTMENTS" where "MANAGER_ID" is null;

SELECT count(*) from "D_PARTNERS" where "EXPERTISE" is null;

SELECT count(*) from "D_PLAY_LIST_ITEMS" where "COMMENTS" is null;

SELECT count(*) from "EMPLOYEES" where "COMMISSION_PCT" is null;

SELECT count(*) from "F_FOOD_ITEMS" where "REGULAR_CODE" is null;

SELECT count(*) from "F_STAFFS" where "OVERTIME_RATE" is null;

SELECT count(*) from "HTMLDB_PLAN_TABLE" where "REMARKS" is null;

SELECT count(*) from "WF_COUNTRIES" where "COUNTRY_TRANSLATED_NAME" is null;

SELECT count(*) from "WF_CURRENCIES" where "COMMENTS" is null;

SELECT count(*) from "WF_SPOKEN_LANGUAGES" where "COMMENTS" is null;