Database Systems: Design, Implementation, & Management
13th Edition
ISBN: 9781337627900
Author: Carlos Coronel, Steven Morris
Publisher: Cengage Learning
expand_more
expand_more
format_list_bulleted
Concept explainers
Textbook Question
Chapter 8, Problem 37P
Modify the CHARTER table to add the attributes shown in the following table.
TABLE P8.37 ATTRIBUTES AND VALUES FOR PROBLEM 37
ATTRIBUTE NAME | ATTRIBUTE DESCRIPTION | ATTRIBUTE TYPE |
CHAR_WAIT_CHG | Waiting charge for each model (copied from the MODEL table) | Numeric |
CHAR_FLT_CHG_HR | Flight charge per mile for each model (copied from the MODEL table using the MOD_CHG_MILE attribute) | Numeric |
CHAR_FLT_CHG | Flight charge (calculated by CHAR_HOURS_FLOWN × CHAR_FLT_CHG_HR) | Numeric |
CHAR_TAX_CHG | CHAR_FLT_CHG × tax rate (8%) | Numeric |
CHAR_TOT_CHG | CHAR_FLT_CHG + CHAR_TAX_CHG | Numeric |
CHAR_PYMT | Amount paid by customer | Numeric |
CHAR_BALANCE | Balance remaining after payment | Numeric |
Expert Solution & Answer
Trending nowThis is a popular solution!
Students have asked these similar questions
Modify the PILOT table to add the attribute shown in the following table, with a precision and scale of (8,2).
Create a dataset that contains 3 objects and 4 attributes by filling the following table.
Type of attribute
Attribute 1 (Nominal
Attribute 2 (Ordinal)
Attribute 3 (Interval)
Attribute 4 (Ratio)
AttributeName
Object 1
Object 2
Object 3
Modify the MODEL table to add the attribute shown in the following table. MOD_WAIT_CHG should support up to 5 digits to the left of the decimal place and 2 digits to the right of the decimal place..
Chapter 8 Solutions
Database Systems: Design, Implementation, & Management
Ch. 8 - Prob. 1RQCh. 8 - Explain why it might be more appropriate to...Ch. 8 - What is the difference between a column constraint...Ch. 8 - What are referential constraint actions?Ch. 8 - What is the purpose of a CHECK constraint?Ch. 8 - Explain when an ALTER TABLE command might be...Ch. 8 - What is the difference between an INSERT command...Ch. 8 - What is the difference between using a subquery...Ch. 8 - What is the difference between a view and a...Ch. 8 - Prob. 10RQ
Ch. 8 - Prob. 11RQCh. 8 - Prob. 12RQCh. 8 - Write the SQL code that will create only the table...Ch. 8 - Having created the table structure in Problem 1,...Ch. 8 - Prob. 3PCh. 8 - Write the SQL code that will save the changes made...Ch. 8 - Write the SQL code to change the job code to 501...Ch. 8 - Write the SQL code to delete the row for William...Ch. 8 - Write the SQL code to create a copy of EMP_1,...Ch. 8 - Using the EMP_2 table, write the SQL code that...Ch. 8 - Using the EMP_2 table, write the SQL code to...Ch. 8 - Prob. 10PCh. 8 - Prob. 11PCh. 8 - Prob. 12PCh. 8 - Prob. 13PCh. 8 - Prob. 14PCh. 8 - Prob. 15PCh. 8 - Create the CUSTOMER table structure illustrated in...Ch. 8 - Create the INVOICE table structure illustrated in...Ch. 8 - Prob. 18PCh. 8 - Prob. 19PCh. 8 - Create an Oracle sequence named CUST_NUM_SEQ to...Ch. 8 - Create an Oracle sequence named INV_NUM_SEQ to...Ch. 8 - Prob. 22PCh. 8 - Modify the CUSTOMER table to include the customers...Ch. 8 - Prob. 24PCh. 8 - Prob. 25PCh. 8 - Create a trigger named trg_updatecustbalance to...Ch. 8 - Prob. 27PCh. 8 - Prob. 28PCh. 8 - Write a trigger to update the customer balance...Ch. 8 - Prob. 30PCh. 8 - Create a trigger named trg_line_total to write the...Ch. 8 - Create a trigger named trg_line_prod that...Ch. 8 - Create a stored procedure named prc_inv_amounts to...Ch. 8 - Create a procedure named prc_cus_balance_update...Ch. 8 - Modify the MODEL table to add the attribute and...Ch. 8 - Prob. 36PCh. 8 - Modify the CHARTER table to add the attributes...Ch. 8 - Write the sequence of commands required to update...Ch. 8 - Write the sequence of commands required to update...Ch. 8 - Write the command required to update the...Ch. 8 - Write the command required to update the...Ch. 8 - Write the command required to update the...Ch. 8 - Prob. 43PCh. 8 - Create a trigger named trg_char_hours that...Ch. 8 - Create a trigger named trg_pic_hours that...Ch. 8 - Create a trigger named trg_cust_balance that...Ch. 8 - Write the SQL code to create the table structures...Ch. 8 - The following tables provide a very small portion...Ch. 8 - For Questions 49-63, use the tables that were...Ch. 8 - Prob. 50PCh. 8 - Write a single SQL command to increase all price...Ch. 8 - Alter the DETAILRENTAL table to include a derived...Ch. 8 - Update the DETAILRENTAL table to set the values in...Ch. 8 - Alter the VIDEO table to include an attribute...Ch. 8 - Update the VID_STATUS attribute of the VIDEO table...Ch. 8 - Alter the PRICE table to include an attribute...Ch. 8 - Prob. 57PCh. 8 - Prob. 60P
Knowledge Booster
Learn more about
Need a deep-dive on the concept behind this application? Look no further. Learn more about this topic, computer-science and related others by exploring similar questions and additional content below.Similar questions
- Delete the OWNER_INDEX 3 index from the OWNER table.arrow_forwardUsing the output shown in Figure P7.12 as your guide, generate a list of customer purchases, including the subtotals for each of the invoice line numbers. The subtotal is a derived attribute calculated by multiplying LINE_UNITS by LINE_PRICE. Sort the output by customer code, invoice number, and product description. Be certain to use the columnaliases as shown in the figure. FIGURE P7.12 SUMMARY OF CUSTOMER PURCHASES WITH SUBTOTALSarrow_forwardProblem 22 Insert the following customer into the CUST_MYSQL table, allowing the AUTO_INCREMENT attribute set up in Problem 20.a and Problem 20.b to generate the customer number automatically: CUST_LNAME CUST_FNAME CUST_BALANCE Powers Ruth 500 This is what problem 20.a said, and got right: Using MySQL, create a table named CUST_MYSQL with the same fields as in Problem 16, except, use the AUTO_INCREMENT feature for the CUST_NUM field. This is what problem 20.b said, and got right: Using MySQL, alter the table to populate the CUST_NUM field, beginning the increment with 2000.arrow_forward
- Answer questions 2.1 to 2.4 based on the following schema. Note that primary keys are in BOLD. Note also that attributes with the same name in different tables are foreign keys. For example, doctorID in the Treatment table is a foreign key that refers to the Doctor table. Doctor(doctorId:integer, specialtyID:integer, firstName:varchar(20), lastName:varchar(20), salary:numeric(11,2)) Specialty(specialtyID:integer, specialtyName:varchar(20), specialtyCost:numeric(11,2)) Treatment(doctorId:integer, patientSSN:integer, dateTreated:date, costOfTreatment:numeric(11,2)) Patient(patientSSN:integer, firstName:varchar(20), lastName:varchar(20), street:varchar(30), city:varchar(15), state:char(2),zipCode:integer(5)) 1. Write an SQL statement to increase the cost of all Specialty treatment by 2.5% 2. Write a SQL statement to delete all treatment rows that were done before 2015 and cost less than $500.arrow_forwardAnswer questions 2.1 to 2.4 based on the following schema. Note that primary keys are in BOLD. Note also that attributes with the same name in different tables are foreign keys. For example, doctorID in the Treatment table is a foreign key that refers to the Doctor table. Doctor(doctorId:integer, specialtyID:integer, firstName:varchar(20), lastName:varchar(20), salary:numeric(11,2)) Specialty(specialtyID:integer, specialtyName:varchar(20), specialtyCost:numeric(11,2)) Treatment(doctorId:integer, patientSSN:integer, dateTreated:date, costOfTreatment:numeric(11,2)) Patient(patientSSN:integer, firstName:varchar(20), lastName:varchar(20), street:varchar(30), city:varchar(15), state:char(2),zipCode:integer(5)) 2.3) Write an SQL statement to increase the cost of all Specialty treatment by 2.5% 2.4) Write a SQL statement to delete all treatment rows that were done before 2015 and cost less than $500. 2.5) Write an SQL statement to select all Specialties where the specialtytName has a null…arrow_forwardUsing the STUDENT table structure shown, do the following: Attribute Name Sample Value Sample Value Sample Value Sample Value Sample Value INV_NUM 211347 211347 211347 211348 211349 PROD_NUM AA-E3422QW QD-300932X RU-995748G AA-E3422QW GH-778345P SALE_DATE 15-Jan-2018 15-Jan-2018 15-Jan-2018 15-Jan-2018 16-Jan-2018 PROD_LABEL Rotary sander 0.25-in. drill bit Band saw Rotary sander Power drill VEND_CODE 211 211 309 211 157 VEND_NAME NeverFail, Inc. NeverFail, Inc. BeGood, Inc. NeverFail, Inc. ToughGo, Inc. QUANT_SOLD 1 8 1 2 1 PROD_PRICE $49.95 $3.45 $39.99 $49.95 $87.75 A. Write the relational schema, draw its dependency diagram and identify all dependencies, including all partial and transitive dependencies. You can assume that the table does not contain repeating groups and that any invoice number may reference more than one product. (Hint: This table…arrow_forward
- Note that primary keys are in BOLD. Note also that attributes with the same name in different tables are foreign keys. For example, doctorID in the Treatment table is a foreign key that refers to the Doctor table. Doctor(doctorId:integer, specialtyID:integer, firstName:varchar(20), lastName:varchar(20), salary:numeric(11,2)) Specialty(specialtyID:integer, specialtyName:varchar(20), specialtyCost:numeric(11,2)) Treatment(doctorId:integer, patientSSN:integer, dateTreated:date, costOfTreatment:numeric(11,2)) Patient(patientSSN:integer, firstName:varchar(20), lastName:varchar(20), street:varchar(30), city:varchar(15), state:char(2),zipCode:integer(5)) 2.3) Write an SQL statement to increase the cost of all Specialty treatment by 2.5% 2.4) Write a SQL statement to delete all treatment rows that were done before 2015 and cost less than $500. 2.5) Write an SQL statement to select all Specialties where the specialtytName has a null valuearrow_forwardAssume you have a vehicle table with a surrogate key vehicle_id column and VIN (Vehicle Identification Number) column and some other columns. You want to designate a unique index on the vehicle_id and VIN columns because together they filter on the joining column with the highest cardinality (or degree of uniqueness). Write the syntax to add a unique index on the vehicle_id and VIN columns triggered by joins on the surrogate key column?arrow_forward
arrow_back_ios
arrow_forward_ios
Recommended textbooks for you
- Database Systems: Design, Implementation, & Manag...Computer ScienceISBN:9781305627482Author:Carlos Coronel, Steven MorrisPublisher:Cengage LearningDatabase Systems: Design, Implementation, & Manag...Computer ScienceISBN:9781285196145Author:Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos CoronelPublisher:Cengage LearningNp Ms Office 365/Excel 2016 I NtermedComputer ScienceISBN:9781337508841Author:CareyPublisher:Cengage
- A Guide to SQLComputer ScienceISBN:9781111527273Author:Philip J. PrattPublisher:Course Technology Ptr
Database Systems: Design, Implementation, & Manag...
Computer Science
ISBN:9781305627482
Author:Carlos Coronel, Steven Morris
Publisher:Cengage Learning
Database Systems: Design, Implementation, & Manag...
Computer Science
ISBN:9781285196145
Author:Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos Coronel
Publisher:Cengage Learning
Np Ms Office 365/Excel 2016 I Ntermed
Computer Science
ISBN:9781337508841
Author:Carey
Publisher:Cengage
A Guide to SQL
Computer Science
ISBN:9781111527273
Author:Philip J. Pratt
Publisher:Course Technology Ptr
dml in sql with examples; Author: Education 4u;https://www.youtube.com/watch?v=WvOseanUdk4;License: Standard YouTube License, CC-BY