Table name: EMPLOYEEEMP_NUM EMP_LNAME215 Smith216 Romero217 Randall218 AardenTable name: EMP_EDUCEMP_NUM EDU_CODE EE_YEAR215 842002215 HS1991215 JC1994216 B41989216 CDP2004216 CNP2002216 HS1985217 HS1992217 JC2000218 B41990218 CDP2000218 CNP2005218 HS1984218 M21995TABLEEMPLOYEEEDUCATIONTable name: EDUCATIONEDU_CODEEDU_DESCRIPTIONa) For each table in the database, identify the primary key and the foreign key(s). If a table does nothave a foreign key, write None in the space provided.EMP_EDUC34CDPCNPCPADRHSJCM2Bachelor's degreeCertified data ProcessingCertified Network ProfessionalCertified Public AccountantEarned Doctorate degreeHighschool diplomaPRIMARY KEY(s)Junior College degreeMaster's degreeFOREIGN KEY(s)

below is the primary key and forgain key for all 3 tables.

Table name: EMPLOYEE EMP_NUM EMP_LNAME 215 Smith 216 Romero 217 Randall 218 Aarden Table name: EMP_EDUC EMP_NUM EDU_CODE EE_YEAR 215 84 2002 215 HS 1991 215 JC 1994 216 B4 1989 216 CDP 2004 216 CNP 2002 216 HS 1985 217 HS 1992 217 JC 2000 218 B4 1990 218 CDP 2000 218 CNP 2005 218 HS 1984 218 M2 1995 Table name: EDUCATION EDU_CODE EDU_DESCRIPTION 34 CDP CNP CPA DR HS JC M2 Bachelor's degree Certified data Processing Certified Network Professional Certified Public Accountant Earned Doctorate degree Highschool diploma Junior College degree Master's degree a) For each table in the database, identify the primary key and the foreign key(s). If a table does not have a foreign key, write None in the space provided.

Table name: EMPLOYEE EMP_NUM EMP_LNAME 215 Smith 216 Romero 217 Randall 218 Aarden Table name: EMP_EDUC EMP_NUM EDU_CODE EE_YEAR 215 84 2002 215 HS 1991 215 JC 1994 216 B4 1989 216 CDP 2004 216 CNP 2002 216 HS 1985 217 HS 1992 217 JC 2000 218 B4 1990 218 CDP 2000 218 CNP 2005 218 HS 1984 218 M2 1995 Table name: EDUCATION EDU_CODE EDU_DESCRIPTION 34 CDP CNP CPA DR HS JC M2 Bachelor's degree Certified data Processing Certified Network Professional Certified Public Accountant Earned Doctorate degree Highschool diploma Junior College degree Master's degree a) For each table in the database, identify the primary key and the foreign key(s). If a table does not have a foreign key, write None in the space provided.

Database Systems: Design, Implementation, & Management

12th Edition

ISBN:9781305627482

Author:Carlos Coronel, Steven Morris

Publisher:Carlos Coronel, Steven Morris

Chapter8: Advanced Sql

Section: Chapter Questions

Problem 69C: Update the DETAILRENTAL table to set the values in DETAIL_RETURNDATE to include a time component....

See similar textbooks

Similar questions

Table name: EMPLOYEE EMP_NUM EMP_LNAME 215 Smith 216 Romero 217 Randall 218 Aarden Table name: EMP_EDUC EMP_NUM EDU_CODE EE YEAR 2002 1991 1994 1989 2004 2002 1985 1992 2000 1990 2000 2005 1984 1995 215 84 215 HS 215 JC 216 B4 216 CDP 216 CNP 216 HS 217 HS 217 JC 218 B4 218 CDP 218 CNP 218 HS 218 M2 Table name: EDUCATION EDU_CODE EDU_DESCRIPTION 34 CDP CNP CPA DR HS JC M2 Bachelor's degree Certified data Processing Certified Network Professional Certified Public Accountant Earned Doctorate degree Highschool diploma Junior College degree Master's degree
SUPPLIER PRODUCT ProductCode ProductnName unitPrice SupCode supCode (Number, 3) SupName (text, 20) АВC LLC (Number, 10) [Char(10)] (>0) 1111111 Calculator 10 111 111 2222222 Notebook 2.5 777 222 ХҮX LLC 3333333 Book-AI 20 222 3333 PQR LLC 4444444 Pen -2.0 222 222 AXL LLC 5555555 Pencil .500 111 Eraser .500 222
PK Cus CODE PK INV NUMBER PK,FK1 INV NUMBER LINE NUMBER HEDenerates contains PK CUS LNAME CUs FNAME CUs INITIAL CUS AREACODE CUS PHONE CUS BALANCE FK1 CUS CODE INV DATE P CODE LINE UNITS LINE PRICE FK2 is found in VENDOR PRODUCT PK V CODE PK P CODE V NAME v CONTACT V AREACODE V PHONE V_STATE V ORDER P DESCRIPT P INDATE P_QOH P MIN P PRICE DISCOUNT FK1 V CODE upplies Write a SQL query to list the product code, vendor code, and vendor name for all products and includes those vendors with no matching products. Y CODE- PRODUCTY CODE
Table name: ADVISORS ADVISORID ADVISORNAME A102 Sami A103 Alice A104 Suad A105 Tamil A101 Arun Table name: DEPARTMENTS DEPTID DEPTNAME ELC D102 D101 IT D103 BSD D104 ENG Table name: STUDENTS STUDENTID STUDENTNAME DOB GENDER ALLOWANCE DEPTID ADVISORID STUDENTLEVEL AGE 6/1/1999 Male 12/30/1994 Female 6/1/1999 Male 5/6/1995 Male 10/15/1992 Male S103 Hisham 90 D102 A102 Level 1 22 S102 Sara 45 D103 A104 Diploma 27 S101 Salim 45 D103 A104 Diploma 22 S107 Khalil 45 D101 A101 BTech 26 S109 Said 45 D104 A101 Advanced Diploma 29 s100 Ahmad 5/1/1995 Male 45 D101 A103 BTech 26 S104 Faisal 4/5/1998 Male 90 D104 A101 Advanced Diploma 23 S105 Fatima 2/23/1992 Female 90 D102 A102 Level 2 29 9/12/1999 Female 8/13/1991 Female S106 Arwa 45 D102 A102 Level 3 22 S108 Ibtisam 90 D103 A104 Diploma 30 Write the SQL statement for the following: 1. Using the ON clause, display the advisor name and student name for all students in the BTech level
Member ID 1582 2587 3549 9536 2567 2154 3265 8765 3259 6540 First Name Mara Philip Kathleen Sau Shanice Chase Tommie Dorcas Angel Weston BookingID 1 2 3 4 5 6 7 8 Table 1: Member Last Name Gender Age Female 25 Male 36 Female 25 United States Female 25 United States Hashimoto Gent Hanner Pfau Mccrystal Karner Jia Darity Sanor Martina Room Room 15 Room 25 Room 1 Room 15 Room 5 Room 21 Room 3 Room 8 Date 29-Dec-2020 9-Dec-2021 3-Mar-2019 4-Sep-2021 13-Feb-2021 6-May-2021 10-Nov-2020 4-Apr-2022 Table 2: Booking Days 2 4 6 5 10 6 5 Female 36 Male 37 Male 26 Female 37 Male 24 Male 23 4 Table 3: Make MemberID 3549 9536 6540 2154 2567 2154 3265 1582 $150.00 $80.00 $120.00 $300.00 Country Uganda France 3 4 China Australia United States United States Cost per day $200.00 $80.00 $80.00 $150.00 5 6 7 8 France China BookingID 1 2 Joined Date 16-Aug-2020 21-May-2015 12-Dec-2017 16-Aug-2019 16-Aug-2020 1-Jan-2021 29-Dec-2018 21-May-2019 4-Mar-2019 1-Feb-2022 Status Paid Paid Pending Paid Pending Paid…
List the department IDs for those departments that have less than 5 employees: Employee Did Salarycode Ename Eid Ephone Samer 15 23 2345 70092485 Sarah 13 12 5088 71092246 Ahmad 4 26 7712 03099348 Jalal 15 22 9664 76093148 Elie 8 23 3054 70092729 Fadi 24 2976 03091945 Layal 9 21 4490 71099380 Select one: a. SELECT Did FROM Employee GROUP BY Did HAVING SUM(Eid) < 5; O b. SELECT Did, COUNT(Eid) FROM Employee GROUP BY Did; O c. None of these d. SELECT Did FROM Employee GROUP BY Did HAVING COUNT(Eid) < 5;
Table name: ADVISORS ADVISORID ADVISORNAME A102 Sami A103 Alice A104 Suad A105 Tamil A101 Arun Table name: DEPARTMENTS DEPTID DEPTNAME D102 ELC D101 IT D103 BSD D104 ENG Table name: STUDENTS STUDENTID STUDENTNAME DOB GENDER ALLOWANCE DEPTID ADVISORID STUDENTLEVEL AGE Hisham 6/1/1999 Male 90 D102 S103 A102 Level 1 22 12/30/1994 Female 45 D103 S102 Sara A104 Diploma 27 S101 Salim 6/1/1999 Male 45 | D103 A104 Diploma 22 5107 Khalil 5/6/1995 Male 45 D101 A101 Blech 26 45 | D104 Advanced Diploma S109 Said 10/15/1992 Male A101 29 5/1/1995 Male 45 D101 90 D104 5100 Ahmad BTech Advanced Diploma A103 26 4/5/1998 Male 2/23/1992 Female 9/12/1999 Female S104 Faisal A101 23 0 D102 45 D102 S105 Fatima 90 A102 Level 2 29 S106 Arwa A102 Level 3 22 S108 Ibtisam 8/13/1991 Female 90 D103 A104 Diploma 30 Write the SQL statement for the following: 1. Using the ON clause, display the advisor name and student name for all students in the BTech level 2. Display the student name, gender and department name of…
5. EMP table has records as follows: EMP_NUM EMP_TITLE EMP_LNAME EMP_FNAME EMP_INITIAL Kolmvcz George Lewis Rhonda Rhett Vandam 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 Mr. Ms. Mr. Ms. Mr. Mr. Mrs. Mr. Mr. Mr. Mrs. Mr. Mr. Ms. Ms. Mrs. Mr. Jones Lande Williams Smith Diante Employee Rhonda Lewis Rhett Vandam Jeanine Jorae Wiesenbach Paul Smith Genkazi Washington Johnson Smythe Brandon Saranda Smith Anne John Robert Anne Jones John Lange Jeanine Smith Jorge Diante George Smith Leiahla Genkazi Rupert Washington Edward Johnson Melanie Smythe Marie Brandon Hermine Saranda George Smith George Leighla Rupert Edward Melanie Marie Hermine George D G Manager George Kolmvcz George Kolmvcz George Kolmvcz Robert Williams Robert Williams Robert Williams Paul Wiesenbach Paul Wiesenbach Robert Williams George Kolmvcz Robert Williams Paul Wiesenbach Robert Williams Paul Wiesenbach ZAOXO xx wwa. M P D K D R K W E E P G R EMP_DOB 1942-06-15 00:00:00 1965-03-19 00:00:00…
Attribute Name Sample Value Sample Value Sample Value Sample Value Sample Value INV_NUM 211347 211347 211347 211348 211349 PROD NUM SALE_DATE PROD LABEL VEND_CODE VEND NAME AA-E3422QW QD-300932X 15-Jan-2018 15-Jan-2018 Rotary sander 211 QUANT_SOLD PROD PRICE $49.95 1 NeverFail, Inc. NeverFail, Inc. BeGood, Inc. RU-995748G 15-Jan-2018 0.25-in. drill Band saw 211 309 8 $3.45 1 $39.99 AA-E3422QW 15-Jan-2018 Rotary sander 211 Power drill 157 NeverFail, Inc. ToughGo, Inc. 2 GH-778345P 16-Jan-2018 $49.95 1 $87.75 b) Remove all partial dependencies, write the relational schema, and draw the new dependency diagrams. Identify the normal forms for each table structure you created.
1. Write a database description for each of the relations shown, using SQL DDL (shorten, abbreviate, or change any data names, as needed for your SQL version). Assume the following attribute data types: StudentID (integer, primary key) StudentName (25 characters) FacultylD (integer, primary key) FacultyName (25 characters) CourselD (8 characters, primary key)
Q3/Consider the following table is used to truck information: Truck_num Base_code Base_city Base_state Type_code Truck_serial_num 1001 501 Dalton GA 1 AA-332-W11 1002 502 Lexington KY 1 AC-342-023 1003 501 Dalton GA 2 AC-445-799 1004 503 Murfree TN 1 WQ-112-T34 1005 503 Murfree TN 2 FR-998-W12 The table is considered as not normalized, explain why? And what is the INF of the table?
Q3/Consider the following table is used to truck information: Truck num Base code Base_city Base_state Type code Truck serial num 1001 501 Dalton GA AA-332-W11 1002 502 Lexington KY AC-342-Q23 1003 501 Dalton GA AC-445-Z99 1004 503 Murfree TN WQ-112-T34 1005 503 Murfree TN FR-998-W12 The table is considered as not normalized, explain why? And what is the INF of the table?