Assignment 02

docx

School

Wayne State University *

*We aren’t endorsed by this school

Course

5800

Subject

Statistics

Date

Feb 20, 2024

Type

docx

Pages

Uploaded by MagistrateAtomFox29

Assignment 02 Terry Paul Hd9796 Part 1. Keys Use the database shown below to answer the following questions: Question 1.1 For each table, identify the primary key and the foreign key(s). If a table does not have a foreign key, write None in the space provided. Primary Foreign Truck: TRUCK_NUM BASE_CODE, TYPE_CODE BASE: BASE_CODE NONE TYPE: TYPE_CODE NONE Question 1.2 Do the tables exhibit entity integrity? Answer yes or no. YES ALL PRIMARY KETS ONLY APPEAR ONCE

Question 1.3 Do the tables exhibit referential integrity? Answer yes or no. Write NA (Not Applicable) if the table does not have a foreign key. YES FOR TRUCK table that has a foreign key has a value associated with it. Na FOR THE REST AS THEY HAVE NO FOREIGN KEYS TO USE AS REFEENCE Question 1.4 Identify the TRUCK table’s candidate key(s). TRUCK_NUM, TRUCK_SERIAL_NUM Question 1.5 For each table, identify a superkey. TRUCK: TRUCK_NUM WITH OR WITHOUT ANY OTHER ATTRIBUTE BASE: BASE_CODE WITH OR WITHOUT ANY OTHER ATTRIBUTE TYPE: TYPE CODE WITH OR WITHOUT ANY OTHER ATTRIBUTE Question 1.6 Create the relational diagram for this database.

Part 2. Operators The subject table below is an adaptation of the Pima Indians Diabetes Database. The subject_id and city_id columns were added to the original dataset (table). Also, subjects_old, subjects_temp, cities, and values tables were also added so we can apply some operations to them. To know the meaning of each column in the original dataset, please see number 7 in the Pima Indians Diabetes Database documentation . subjects subject_id city_id preg plas pres skin insu mass pedi age class 1 2 6 148 72 35 0 33.6 0.627 50 tested_positive 2 3 1 85 66 29 0 26.6 0.351 31 tested_negative 3 2 8 183 64 0 0 23.3 0.672 32 tested_positive 4 NULL 1 89 66 23 94 28.1 0.167 21 tested_negative 5 3 0 137 40 35 168 43.1 2.288 33 tested_positive 6 2 5 116 74 0 0 25.6 0.201 30 tested_negative 7 NULL 3 78 50 32 88 31 0.248 26 tested_positive 8 3 10 115 0 0 0 35.3 0.134 29 tested_negative 9 1 2 197 70 45 543 30.5 0.158 53 tested_positive 10 1 8 125 96 0 0 0 0.232 54 tested_positive 11 0 4 110 92 0 0 37.6 0.191 30 tested_negative 12 3 10 168 74 0 0 38 0.537 34 tested_positive 13 2 10 139 80 0 0 27.1 1.441 57 tested_negative 14 NULL 1 189 60 23 846 30.1 0.398 59 tested_positive 15 2 5 166 72 19 175 25.8 0.587 51 tested_positive subjects_old subject_id city_id preg plas pres skin insu mass pedi age class 16 2 7 100 0 0 0 30 0.484 32 tested_positive 17 0 0 118 84 47 230 45.8 0.551 31 tested_positive 18 1 7 107 74 0 0 29.6 0.254 31 tested_positive 19 NULL 1 103 30 38 83 43.3 0.183 33 tested_negative 20 3 1 115 70 30 96 34.6 0.529 32 tested_positive

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

subjects_temp subject_id city_id preg plas pres skin insu mass pedi age class 5 3 0 137 40 35 168 43.1 2.288 33 tested_positive 6 2 5 116 74 0 0 25.6 0.201 30 tested_negative 17 0 0 118 84 47 230 45.8 0.551 31 tested_positive 18 1 7 107 74 0 0 29.6 0.254 31 tested_positive 11 0 4 110 92 0 0 37.6 0.191 30 tested_negative cities city_id name 0 Phoenix 1 Tucson 2 Mesa 3 Tempe 4 Glendale 5 Chandler values value_id boolean 0 false 1 true Use the tables above to execute the following operations: Question 2.1 Create the table that would result from applying the SELECT operator where the diastolic blood pressure is less than 65 in the subjects table. subjects subject_id city_id preg plas pres skin insu mass pedi age class 3 2 8 183 64 0 0 23.3 0.672 32 tested_positive

5 3 0 137 40 35 168 43.1 2.288 33 tested_positive 7 NULL 3 78 50 32 88 31 0.248 26 tested_positive 8 3 10 115 0 0 0 35.3 0.134 29 tested_negative 14 NULL 1 189 60 23 846 30.1 0.398 59 tested_positive Question 2.2 Create the table that would result from applying the PROJECT operator to return the attributes of the diabetes pedigree function and class variable in the subjects table. Pedi Class 0.627 Tested_postive .351 Tested_negtive .672 Tested_postive .167 Tested_negtive 2.288 Tested_postive .201 Tested_negtive .248 Tested_postive .134 Tested_negtive .158 Tested_postive .232 Tested_postive .191 Tested_negtive .537 Tested_postive 1.441 Tested_negtive .398 Tested_postive .587 Tested_postive Question 2.3 Create the table that would result from applying the DIFFERENCE operator in subjects_old - subjects_temp. subject_id city_id preg plas pres skin insu mass pedi age class 16 2 7 100 0 0 0 30 0.484 32 Tested_postive 19 Null 1 103 30 38 83 43.3 0.183 33 Tested_negative 20 3 1 115 70 30 96 34.6 0.529 32 Tested_postvie

Question 2.4 Create the table that would result from applying the PRODUCT operator in values * cities. CITY_ID NAME VALUE_ID BOOLEAN 0 Phoenix 0 false 0 Phoenix 1 True 1 Tucson 0 false 1 Tucson 1 True 2 Mesa 0 false 2 Mesa 1 True 3 Tempe 0 false 3 Tempe 1 True 4 Glendale 0 false 4 Glendale 1 false 5 Chandler 0 false 5 Chandler 1 false Question 2.5 Create the table that would result from applying the NATURAL JOIN operator to subjects and cities. subjects subject_id city_id preg plas pres skin insu mass pedi age class name 1 2 6 148 72 35 0 33.6 0.627 50 tested_positive mesa 2 3 1 85 66 29 0 26.6 0.351 31 tested_negative Tempe 3 2 8 183 64 0 0 23.3 0.672 32 tested_positive mesa 5 3 0 137 40 35 168 43.1 2.288 33 tested_positive Tempe 6 2 5 116 74 0 0 25.6 0.201 30 tested_negative Mesa 8 3 10 115 0 0 0 35.3 0.134 29 tested_negative Tempe 9 1 2 197 70 45 543 30.5 0.158 53 tested_positive Tucson 10 1 8 125 96 0 0 0 0.232 54 tested_positive Tucson 11 0 4 110 92 0 0 37.6 0.191 30 tested_negative Phoenix 12 3 10 168 74 0 0 38 0.537 34 tested_positive Tempe 13 2 10 139 80 0 0 27.1 1.441 57 tested_negative Mesa 15 2 5 166 72 19 175 25.8 0.587 51 tested_positive Mesa

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

Question 2.6 Create the table that would result from applying the OUTER RIGHT JOIN operator to subjects and cities. subject_id city_id preg plas pres skin insu mass pedi age class name 1 2 6 148 72 35 0 33.6 0.627 50 tested_positive mesa 2 3 1 85 66 29 0 26.6 0.351 31 tested_negative Tempe 3 2 8 183 64 0 0 23.3 0.672 32 tested_positive mesa 5 3 0 137 40 35 168 43.1 2.288 33 tested_positive Tempe 6 2 5 116 74 0 0 25.6 0.201 30 tested_negative Mesa 8 3 10 115 0 0 0 35.3 0.134 29 tested_negative Tempe 9 1 2 197 70 45 543 30.5 0.158 53 tested_positive Tucson 10 1 8 125 96 0 0 0 0.232 54 tested_positive Tucson 11 0 4 110 92 0 0 37.6 0.191 30 tested_negative Phoenix 12 3 10 168 74 0 0 38 0.537 34 tested_positive Tempe 13 2 10 139 80 0 0 27.1 1.441 57 tested_negative Mesa 15 2 5 166 72 19 175 25.8 0.587 51 tested_positive Mesa NUll 5 NUll NUll NUll NUll NUll NUll NUll NUll NUll Chadler