Database Systems: Design, Implementation, & Management
11th Edition
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
expand_more
expand_more
format_list_bulleted
Concept explainers
Videos
Textbook Question
Chapter 7, Problem 65C
Write the SQL code to create the table structures for the entities shown in Figure P7.65. The structures should contain the attributes specified in the ERD. Use data types that are appropriate for the data that will need to be stored in each attribute. Enforce primary key and foreign key constraints as indicated by the ERD.
Expert Solution & Answer
Trending nowThis is a popular solution!
Students have asked these similar questions
https://sqliteonline.com/ : Program link
The first requirement: Using the SQL language, transform the following entity model into tables in the database.
Entity Manger contains:
M-name The name of the manager.
M-ID and manager number.The department entity contains:
D-name The name of the department.
D-ID and department number.Create constraint master key name of the manager when creating the Manger table.Creating the foreign key constraint The name of the manager when creating the Department table.
The second requirement: After creating the tables and specifying the primary and foreign key, do the following:
* Add a column D_loc to the Department table, specifying the type of column.* Remove the column named M_name from the Manger table* Change the name of the department table to Dep
Please::
******* ATTACH A PICTURE OF THE CODES USED AND PICTURE OF THE PROGRAM SCREEN *******
Write a Structured Query Language (SQL) statement for each of the following questions:a) Create the AppointmentDetails table with the following requirements: Appropriate data types. All keys can store between 6 to 10 characters. All the columns must have values (cannot be empty). Add on charges is set to 0.00 by default. Enforce entity and referential integrities.
b) Diplay the therapists’ particulars (include TheraName, TheraContact) as first two columns, then calculate the Total_Count_Of_Appointment for each therapist within the year 2020 as third columns. Lastly, arrange the outcome to show the list by the higher popularity of therapists first.
Consider the following ER diagram:
storcid
address
description
value
Displays
Store
Memorabilia
The above diagram represents information pertaining to stores and the memorabilia that is
displayed within the stores.
a) Write the SQL statements to create the necessary tables to represent the above diagram,
representing as many constraints as possible. Note: you do not need to worry about
specifying types for the fields, but you do need to provide suitable foreign key option(s).
Chapter 7 Solutions
Database Systems: Design, Implementation, & Management
Ch. 7 - Prob. 1RQCh. 7 - Explain why the following command would create an...Ch. 7 - Prob. 3RQCh. 7 - Explain why it might be more appropriate to...Ch. 7 - What is the difference between a column constraint...Ch. 7 - What are referential constraint actions?Ch. 7 - Rewrite the following WHERE clause without the use...Ch. 7 - Explain the difference between an ORDER BY clause...Ch. 7 - Explain why the following two commands produce...Ch. 7 - What is the difference between the COUNT aggregate...
Ch. 7 - Prob. 11RQCh. 7 - Prob. 12RQCh. 7 - Write the SQL code that will create the table...Ch. 7 - Having created the table structure in Problem 1,...Ch. 7 - Prob. 3PCh. 7 - Prob. 4PCh. 7 - Write the SQL code to change the job code to 501...Ch. 7 - Write the SQL code to delete the row for William...Ch. 7 - Prob. 7PCh. 7 - Prob. 8PCh. 7 - Write the SQL code to change the EMP_PCT value to...Ch. 7 - Prob. 10PCh. 7 - Prob. 11PCh. 7 - Write the SQL code that will change the PROJ_NUM...Ch. 7 - Prob. 13PCh. 7 - Prob. 14PCh. 7 - Prob. 15PCh. 7 - Prob. 16PCh. 7 - Write the SQL code that will produce the same...Ch. 7 - Write the SQL code to find the average bonus...Ch. 7 - Prob. 19PCh. 7 - Prob. 20PCh. 7 - Write the SQL code to calculate the ASSIGN_CHARGE...Ch. 7 - Prob. 22PCh. 7 - Prob. 23PCh. 7 - Prob. 24PCh. 7 - Prob. 25PCh. 7 - Prob. 26PCh. 7 - Prob. 27PCh. 7 - Generate a listing of all purchases made by the...Ch. 7 - Using the output shown in Figure P7.29 as your...Ch. 7 - Prob. 30PCh. 7 - Prob. 31PCh. 7 - Use a query to compute the average purchase amount...Ch. 7 - Prob. 33PCh. 7 - Prob. 34PCh. 7 - Prob. 35PCh. 7 - Prob. 36PCh. 7 - Prob. 37PCh. 7 - Using the results of the query created in Problem...Ch. 7 - Create a query to find the balance characteristics...Ch. 7 - Prob. 40PCh. 7 - Prob. 41PCh. 7 - Prob. 42PCh. 7 - Prob. 43PCh. 7 - Prob. 44PCh. 7 - Prob. 45PCh. 7 - Prob. 46PCh. 7 - Prob. 47PCh. 7 - Prob. 48PCh. 7 - Prob. 49PCh. 7 - Prob. 50PCh. 7 - Prob. 51PCh. 7 - Prob. 52PCh. 7 - Prob. 53PCh. 7 - Prob. 54PCh. 7 - Prob. 55PCh. 7 - Prob. 56PCh. 7 - Prob. 57PCh. 7 - Prob. 58PCh. 7 - Prob. 59PCh. 7 - Prob. 60PCh. 7 - Prob. 61PCh. 7 - Prob. 62PCh. 7 - Prob. 63PCh. 7 - Write the SQL code to create the table structures...Ch. 7 - The following tables provide a very small portion...Ch. 7 - Prob. 67CCh. 7 - Prob. 68CCh. 7 - Prob. 69CCh. 7 - Prob. 70CCh. 7 - Prob. 71CCh. 7 - Prob. 72CCh. 7 - Prob. 73CCh. 7 - Prob. 74CCh. 7 - Prob. 75CCh. 7 - Prob. 76CCh. 7 - Prob. 77CCh. 7 - Prob. 78CCh. 7 - Prob. 79CCh. 7 - Prob. 80CCh. 7 - Prob. 81CCh. 7 - Prob. 82CCh. 7 - Prob. 83CCh. 7 - Prob. 84CCh. 7 - Prob. 85CCh. 7 - Prob. 86CCh. 7 - Prob. 87CCh. 7 - Prob. 88CCh. 7 - Prob. 89CCh. 7 - Prob. 90CCh. 7 - Prob. 91CCh. 7 - Prob. 92CCh. 7 - Prob. 93CCh. 7 - Prob. 94CCh. 7 - Prob. 95CCh. 7 - Prob. 96CCh. 7 - Prob. 97CCh. 7 - Write a query to display the movie number, movie...
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
- Write the SQL code to calculate the ASSIGN_CHARGE values in the ASSIGNMENT table in the Ch07_ConstructCo database. (See Figure P7.1.) Note that ASSIGN_CHARGE is a derived attribute that is calculated by multiplying ASSIGN_CHG_HR by ASSIGN_HOURS.arrow_forwardReview the data for the TRIP table in Figure 1-5 in Chapter 1 and then review the data types used to create the TRIP table in Figure 3-39. Suggest alternate data types for at least two fields that currently have the CHAR data type and at least two fields that currently have the DECIMAL data type. Create but do not execute the SQL command to create the TRIP table using these alternate data types.arrow_forwardWrite the SQL code that will restore the data to its original status: that is, the table should contain the data that existed before you made the changes in Problems 5 and 6.arrow_forward
- Problem 1 and 2 are based on the following query: SELECT EMP_LNAME, EMP_FNAME, EMP_AREACODE, EMP_SEX FROM EMPLOYEE WHERE EMP_SEX = 'F' AND EMP_AREACODE = '615' ORDER BY EMP_LNAME, EMP_FNAME; What indexes should you create? Write the required SQL commands.arrow_forwardProblems 27 and 28 are based on the following query: SELECT P_CODE, P_DESCRIPT, P_QOH, P_PRICE, V_CODE FROM PRODUCT WHERE P_QOH P_MIN AND P_MIN = P_REORDER AND P_REORDER = 50 ORDER BY P_QOH; What indexes would you recommend? Write the commands to create those indexes.arrow_forwardWrite the SQL statements to create all of the necessary tables to represent the above diagram, representing as many constraints as possible. Note: you do not need to worry about specifying types for the fields, but you do need to provide suitable foreign key option(s). When playing a game, the home team name should be stored in attribute htname and the away team name should be stored in atname.arrow_forward
- Part B Create the entities, subtypes, and relationships according to the business rule below. Then map the entities identified into tables. Do these in SQL developer. BUSINESS RULE: A Seller can be categorized as Retailer or Wholesaler. All sellers have a seller id, first name, last name, contact no and maximum credit. A retailer has a RetailPrice, while a Wholesaler has a WholeSalePrice. Each seller should be categorized only to either a retailer or a wholesaler and cannot be a retailer and a wholesaler at the same time. A seller can have one or more shops and shops must be owned by a seller. A shop can contain many products and many products must be found in at least one shop or more shops. Products Shops price type manufacturer Kaddress name Copy and Paste your answers here. (Answers should come from SQL developer)arrow_forwardWrite a SQL code that will save the above changes made to the Articles table.arrow_forwardQuestion 5 You are working with a database table that contains customer data. The table includes columns about customer location such as city, state, country, and postal_code. You want to check for postal codes that are greater than 7 characters long. You write the SQL query below. Add a LENGTH function that will return any postal codes that are greater than 7 characters long. 1 2 3 4 5 SELECT * FROM customer WHERE RunReset What is the last name of the customer that appears in row 10 of your query result? 1 pointarrow_forward
- Subject: DatabaseANSWER THE FOLLOWING QUESTIONS BASED ON THE GIVEN TABLE a) write the DDL statement to create the table CUSTOMER_DETAIL with the primary key constraint. (use most appropriate data type for each attribute). b) Add NOT NULL constraint to attribute of CustomerName c) Add a column named "Country" in the CUSTOMER_DETAIL table d) Rename the table to CUSTOMERarrow_forwardThe Car Maintenance team also wants to store the actual maintenance operations in the database. The team wants to start with a table to store CAR_ID (CHAR(5)), MAINTENANCE_TYPE_ID (CHAR(5)) and MAINTENANCE_DUE (DATE) date for the operation. Create a new table named MAINTENANCES. The PRIMARY_KEY should be the combination of the three fields. The CAR_ID and MAINTENACNE_TYPE_ID should be foreign keys to their original tables. Cascade update and cascade delete the foreign keys.arrow_forwardWrite a SQL command used to enter the first four rows of the tablearrow_forward
arrow_back_ios
SEE MORE QUESTIONS
arrow_forward_ios
Recommended textbooks for you
Database 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 Learning
Database Systems: Design, Implementation, & Manag...Computer ScienceISBN:9781305627482Author:Carlos Coronel, Steven MorrisPublisher:Cengage Learning
A Guide to SQLComputer ScienceISBN:9781111527273Author:Philip J. PrattPublisher:Course Technology Ptr
Np Ms Office 365/Excel 2016 I NtermedComputer ScienceISBN:9781337508841Author:CareyPublisher:Cengage
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
Database Systems: Design, Implementation, & Manag...
Computer Science
ISBN:9781305627482
Author:Carlos Coronel, Steven Morris
Publisher:Cengage Learning
A Guide to SQL
Computer Science
ISBN:9781111527273
Author:Philip J. Pratt
Publisher:Course Technology Ptr
Np Ms Office 365/Excel 2016 I Ntermed
Computer Science
ISBN:9781337508841
Author:Carey
Publisher:Cengage
SQL Basics for Beginners | Learn SQL | SQL Tutorial for Beginners | Edureka; Author: edureka;https://www.youtube.com/watch?v=zbMHLJ0dY4w;License: Standard YouTube License, CC-BY