A Guide to SQL
9th Edition
ISBN: 9781111527273
Author: Philip J. Pratt
Publisher: Course Technology Ptr
expand_more
expand_more
format_list_bulleted
Concept explainers
Expert Solution & Answer
Chapter 2, Problem 5TD
Explanation of Solution
Tables in the TAL Distributors
The TAL Distributors database consists of the following tables:
- REP(REP_NUM, LAST_NAME, FIRST_NAME, STREET, CITY, STATE, POSTAL_CODE, COMMISSION, RATE)
- CUSTOMER (CUSTOMER_NUM, CUSTOMER_NAME,STREET, CITY, STATE, POSTAL_CODE, BALANCE, CREDIT_LIMIT)
- ORDERS (ORDER_NUM, ORDER_DATE, CUSTOMER_NUM, REP_NUM)
- ORDER_LINE (ITEM_NUM, ORDER_NUM, NUM_ORDERED, QUOTED_PRICE)
- ITEM (ITEM_NUM, DESCRIPTION, ON_HAND, CATEGORY, STOREHOUSE, PRICE)
Here, there exist mainly five tables namely the “REP”, “CUSTOMER”, “ITEM”, “ORDERS”, and “ORDER_LINE” with the respective data. The primary keys for the table “ORDER_LINE” are been listed or use “ITEM_NUM”, “ORDER_NUM” as per requirements...
Expert Solution & Answer
Trending nowThis is a popular solution!
Students have asked these similar questions
use Valentina Studio to build a form that will allow users to enter information into our Sakila database without having to utilize and SQL. For this assignment, we are only implementing single table forms.
This assignment will take some time as there are several steps to follow, so please start early to allow for time to complete the activity.
To develop a form, we must begin by creating a new “Project” within the database that will allow for us to create the form. The following steps illustrate how to create the Project.
The 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.
Indicate the changes you need to make to the KimTay Pet Supplies database to support the following additional requirement. Each location has a manager who is identified by a manager ID, a manager first name, and a manager last name.
Chapter 2 Solutions
A Guide to SQL
Ch. 2 - Prob. 1RQCh. 2 - What is an attribute?Ch. 2 - What is a relationship? What is a one-to-many...Ch. 2 - Prob. 4RQCh. 2 - What is a relation?Ch. 2 - Prob. 6RQCh. 2 - Prob. 7RQCh. 2 - How do you qualify the name of a field, and when...Ch. 2 - Prob. 9RQCh. 2 - What is a primary key? What is the primary key for...
Ch. 2 - Prob. 11RQCh. 2 - Prob. 12RQCh. 2 - Define second normal form. What types of problems...Ch. 2 - Define third normal form. What types of problems...Ch. 2 - Prob. 15RQCh. 2 - Prob. 16RQCh. 2 - List the changes you would need to make to your...Ch. 2 - Prob. 1TDCh. 2 - Prob. 2TDCh. 2 - Prob. 3TDCh. 2 - Prob. 4TDCh. 2 - Prob. 5TDCh. 2 - Prob. 1CATCh. 2 - Identify the functional dependencies in the...Ch. 2 - Prob. 3CATCh. 2 - Determine the functional dependencies that exist...Ch. 2 - Prob. 2SCGCh. 2 - Prob. 3SCG
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
- Topic: Database Design Provide an example of a 3 table OUTER join. Explain how it works and what the order of of the joins is.arrow_forwardThe marketing team wants to celebrate the success of StayWell with a party. The team wants a table with the names of all residents and owners combined into single column named PARTICIPANT. You need to combine this information from the tables and send it back to the team. You do not need to create a new table in the database schema. Task Return a list of all the residents and ownersarrow_forwardThe manager of a consulting firm has asked you to evaluate a database that contains the table structure shown in the table below.Table: Sample CLIENT records Attribute name Sample value Sample value Sample value CLIENT_NUM 298 289 289 CLIENT_NAME Marianne R. Brown James D. Smith James D. Smith CLIENT_REGION Midwest Southeast Southeast CONTRACT_DATE 10-Feb-2018 15-Feb-2018 12-Mar-2018 CONTRACT_NUMBER 5841 5842 5843 CONTRACT_AMOUNT R22,985,00.00 R1,670,300.00 R11,250,000.00 CONSULT_CLASS_1 Database administration Internet services Database design CONSULT_CLASS_2 Web applications Database administration CONSULT_CLASS_3 Network installation CONSULT_CLASS_4 CONSULTANT_NUM_1 29 34 25 CONSULTANT_NAME_1 Rachel G. Carson Gerald K. Ricardo Angela M. Jamison CONSULTANT_REGION_1 Midwest Southeast Southeast CONSULTANT_NUM_2 56 38 34 CONSULTANT_NAME_2 Karl M. Spenser Anne T. Zuma Gerald K. Jele CONSULTANT_REGION_2 Midwest Southeast Southeast CONSULTANT_NUM_3 22 45 CONSULTANT_NAME_3 Julian H.…arrow_forward
- Based on the ERD below, write the SQL statement to change the job description of “Database Designer” to “Database Developer”. After completing this task examine the results with a select statement. (Hint: Use an Update Statement.)arrow_forwardThe Solmaris database does not include a field for service fees. In which table would you place the information for service fees? Why?arrow_forwardWhat table might the database need to help determine the balance field in the Patient table? Would you want to record when a bill is paid and the amount? How would the insurance part of the bill be recorded?arrow_forward
- What data types would you use for the attributes in this entity? How would you code a CREATE TABLE statement to implement this entity as a table in the database?arrow_forwardWhat are the differences between the JOIN … USING and JOIN … ON approaches for joining tables?arrow_forwardCreate access table relationships as shown in the ERD above.arrow_forward
- StayWell also rents out properties on a weekly basis to students attending summer school in the Seattle area. Design a database to meet the following requirements, using the shorthand representation and a diagram of your choice. For each student renter, list his or her number, first name, middle initial, last name, address, city, state, postal code, telephone number, and e-mail address. For each property, list the office number, property address, city, state, postal code, square footage, number of bedrooms, number of floors, maximum number of persons that can sleep in the unit, and the base weekly rate. For each rental agreement, list the renter number, first name, middle initial, last name, address, city, state, postal code, telephone number, start date of the rental, end date of the rental, and the weekly rental amount. The rental period is one or more weeks.arrow_forwardQuery design by referring the following scenario. company has a huge database to handle their employees details.Most of the details regarding the employee activities and department details are stored in Employeestable and Departments table. Write the appropriate query to support the manager to complete thefollowingarrow_forwardI'm wondering how to start this assignment? Instructions In SQL Server, create a new database called University. Given the ERD in this document, write the CREATE TABLE statements to construct the tables for this database. Be sure to use reasonable data types and create all tables, attributes, primary keys, foreign keys, and “not null” constraints in your statements where appropriate. To receive full credit for this assignment, your CREATE TABLE statements must be executed in SQL Server. In a new Word document, copy the ERD and type your CREATE TABLE statements below the ERD. Then, in SQL Server, after you execute each CREATE TABLE statement, issue these two statements: SELECT * FROM [table name] exec sp_help [table name]. Note: exec sp_help [table name] is a built-in stored procedure that you run to easily see your table structure, including fieldnames, data types, primary keys, foreign key constraints, etc. It’s a very useful tool. Take a screen shot of your queries and their…arrow_forward
arrow_back_ios
SEE MORE QUESTIONS
arrow_forward_ios
Recommended textbooks for you