New Semester
Started
Get
50% OFF
Study Help!
--h --m --s
Claim Now
Question Answers
Textbooks
Find textbooks, questions and answers
Oops, something went wrong!
Change your search query and then try again
S
Books
FREE
Study Help
Expert Questions
Accounting
General Management
Mathematics
Finance
Organizational Behaviour
Law
Physics
Operating System
Management Leadership
Sociology
Programming
Marketing
Database
Computer Network
Economics
Textbooks Solutions
Accounting
Managerial Accounting
Management Leadership
Cost Accounting
Statistics
Business Law
Corporate Finance
Finance
Economics
Auditing
Tutors
Online Tutors
Find a Tutor
Hire a Tutor
Become a Tutor
AI Tutor
AI Study Planner
NEW
Sell Books
Search
Search
Sign In
Register
study help
computer sciences
databases
Database Systems Design Implementation And Management 11th Edition Carlos Coronel, Steven Morris - Solutions
Automata Inc. produces specialty vehicles by contract. The company operates several departments, each of which builds a particular vehicle, such as a limousine, a truck, a van, or an RV.Before a new vehicle is built, the department places an order with the purchasing department to request specific
United Helpers is a nonprofit organization that provides aid to people after natural disasters. Based on the following brief description of operations, create the appropriate fully labeled Crow’s Foot ERD.• Individuals volunteer their time to carry out the tasks of the organization. For each
Using the Crow’s Foot methodology, create an ERD that can be implemented for a medical clinic, using at least the following business rules:a. A patient can make many appointments with one or more doctors in the clinic, and a doctor can accept appointments with many patients. However, each
The administrators of Tiny College are so pleased with your design and implementation of their student registration/tracking system that they want you to expand the design to include the database for their motor vehicle pool. A brief description of operations follows: A brief description of
During peak periods, Temporary Employment Corporation (TEC) places temporary workers in companies. TEC’s manager gives you the following description of the business:• TEC has a file of candidates who are willing to work.• If the candidate has worked before, that candidate has a specific job
What is a specialization hierarchy?
What is an overlapping subtype? Give an example.
What is the difference between partial completeness and total completeness?
According to the data model, is it required that every entity instance in the PRODUCT table be associated with an entity instance in the CD table? Why or why not?
What is an entity cluster, and what advantages are derived from its use?
What primary key characteristics are considered desirable? Explain why each characteristic is considered desirable.
Under what circumstances are composite primary keys appropriate?
What is a surrogate primary key, and when would you use one?
When implementing a 1:1 relationship, where should you place the foreign key if one side is mandatory and one side is optional? Should the foreign key be mandatory or optional?
What is the most common design trap, and how does it occur?
What is an entity supertype, and why is it used?
What kinds of data would you store in an entity subtype?
What is a subtype discriminator? Given an example of its use.
List all of the attributes of a movie.
What are time-variant data, and how would you deal with such data from a database design point of view?
Given the following business scenario, create a Crow’s Foot ERD using a specialization hierarchy if appropriate.Two-Bit Drilling Company keeps information on employees and their insurance dependents. Each employee has an employee number, name, date of hire, and title. If an employee is an
Given the following business scenario, create a Crow’s Foot ERD using a specialization hierarchy if appropriate.Tiny Hospital keeps information on patients and hospital rooms. The system assigns each patient a patient ID number. In addition, the patient’s name and date of birth are recorded.
Given the following business scenario, create a Crow’s Foot ERD using a specialization hierarchy if appropriate.Granite Sales Company keeps information on employees and the departments that they work in. For each department, the department name, internal mail box number, and office phone
In Chapter 4, you saw the creation of the Tiny College database design. That design reflected such business rules as “a professor may advise many students” and “a professor may chair one department.” Modify the design shown in Figure 4.36 to include these business rules:An employee could be
Tiny College wants to keep track of the history of all administrative appointments (date of appointment and date of termination). The Tiny College chancellor may want to know how many deans worked in the College of Business between January 1, 1960 and January 1, 2010 or who the dean of the College
Some Tiny College staff employees are information technology (IT) personnel. Some IT personnel provide technology support for academic programs. Some IT personnel provide technology infrastructure support. Some IT personnel provide technology support for academic programs and technology
The FlyRight Aircraft Maintenance (FRAM) division of the FlyRight Company (FRC) performs all maintenance for FRC’s aircraft. Produce a data model segment that reflects the following business rules:All mechanics are FRC employees. Not all employees are mechanics.Some mechanics are specialized in
“Martial Arts R Us” (MARU) needs a database. MARU is a martial arts school with hundreds of students. It is necessary to keep track of all the different classes that are being offered, who is assigned to teach each class, and which students attend each class. Also, it is important to track
The Journal of E-commerce Research Knowledge is a prestigious information systems research journal. It uses a peer-review process to select manuscripts for publication. Only about 10 percent of the manuscripts submitted to the journal are accepted for publication. A new issue of the journal is
Global Computer Solutions (GCS) is an information technology consulting company with many offices located throughout the United States. The company’s success is based on its ability to maximize its resources—that is, its ability to match highly skilled employees with projects according to
What is normalization?
When is a table in 1NF?
When is a table in 2NF?
What three data anomalies are likely to be the result of data redundancy? How can such anomalies be eliminated?
What is a surrogate key, and when should you use one?
Suppose that someone tells you that an attribute that is part of a composite primary key is also a candidate key. How would you respond to that statement?
When is a table in BCNF?
Given the dependency diagram shown in Figure Q6.6, answer items 6a-6c:a. Identify and discuss each of the indicated dependencies.b. Create a database whose tables are at least in 2NF, showing the dependency diagrams for each table.c. Create a database whose tables are at least in 3NF, showing the
The dependency diagram in Figure indicates that authors are paid royalties for each book that they write for a publisher. The amount of the royalty can vary by author, by book, and by edition of the book.a. Based on the dependency diagram, create a database whose tables are at least in 2NF, showing
The dependency diagram in Figure Q6.8 indicates that a patient can receive many prescriptions for one or more medicines over time. Based on the dependency diagram, create a database whose tables are in at least 2NF, showing the dependency diagram for each table.
Define and discuss the concept of transitive dependency.
Using the descriptions of the attributes given in the figure, convert the ERD shown in Figure P6.1 into a dependency diagram that is in at least 3NF.
Using the descriptions of the attributes given in the figure, convert the ERD shown in Figure into a dependency diagram that is in at least 3NF.
Using the INVOICE table structure shown in Table P6.3, do the following: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
Using the STUDENT table structure shown in Table P6.4, do the following:a. Write the relational schema, draw its dependency diagram, and identify all dependencies, including all transitive dependencies.b. Write the relational schema and draw the dependency diagram to meet the 3NF requirements to
To keep track of office furniture, computers, printers, and so on, the FOUNDIT Company uses the table structure shown in Table P6.5.a. Given that information, write the relational schema and draw the dependency diagram. Make sure that you label the transitive and/or partial dependencies.b. Write
The table structure shown in Table P6.6 contains many unsatisfactory components and characteristics. For example, there are several multivalued attributes, naming conventions are violated, and some attributes are not atomic.a. Given the structure shown in Table P6.6, write the relational schema and
Suppose you are given the following business rules to form the basis for a database design. The database must enable the manager of a company dinner club to mail invitations to the club's members, to plan the meals, to keep track of who attends the dinners, and so on.Each dinner serves many
Use the dependency diagram shown in Figure 6.8 to work the following problems.a. Break up the dependency diagram in Figure 6.8 to create two new dependency diagrams, one in 3NF and one in 2NF.b. Modify the dependency diagrams you created in Problem 8a to produce a collection of dependency diagrams
Suppose that you have been given the table structure and data shown in Table 6.9, which was imported from an Excel spreadsheet. The data reflect that a professor can have multiple advisees, can serve on multiple committees, and can edit more than one journal.a. Draw the dependency diagram.b.
The manager of a consulting firm has asked you to evaluate a database that contains the table structure shown in Table P6.10.Table P6.10 was created to enable the manager to match clients with consultants. The objective is to match a client within a given region with a consultant in that region,
Given the sample records in the CHARTER table shown in Table P6.11, do the following:a. Write the relational schema and draw the dependency diagram for the table structure. Make sure that you label all dependencies. CHAR_PAX indicates the number of passengers carried. The CHAR_MILES entry is based
In a SELECT query, what is the difference between a WHERE clause and a HAVING clause?
Explain why the following command would create an error, and what changes could be made to fix the error.SELECT V_CODE, SUM(P_QOH) FROM PRODUCT;
Explain why it might be more appropriate to declare an attribute that contains only digits as a character data type instead of a numeric data type.
What are “referential constraint actions”?
Explain why the two following commands produce different results.SELECT DISTINCT COUNT (V_CODE) FROM PRODUCT;SELECT COUNT (DISTINCT V_CODE) FROM PRODUCT;
Explain the difference between an ORDER BY clause and a GROUP BY clause.
Explain why it would be preferable to use a DATE data type to store date data instead of a character data type.
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 would be appropriate for the data that will need to be stored in each attribute. Enforce primary key and foreign key
The following tables provide a very small portion of the data that will be kept in the database. This data needs to be inserted into the database for testing purposes. Write the INSERT commands necessary to place the following data in the tables that were created in problem65.
Write a query to display the minimum balance, maximum balance, and average balance for memberships that have a rental (result shown in FigureP7.92).
Write a query to display the rental number, rental date, video number, movie title, due date, and return date for all videos that were returned after the due date. Sort the results by rental number and movie title (result shown inFigure).
Write a query to display the rental number, rental date, video number, movie title, due date, return date, detail fee, and number of days past the due date that the video was returned for each video that was returned after the due date. Sort the results by rental number and movie title. (Result
Write a query to display the rental number, rental date, video number, movie title, due date, return date, detail fee, and number of days past the due date that the video was returned for each video that was returned after the due date. Sort the results by rental number and movietitle.
Write a query to display the rental number, rental date, movie title, and detail fee for each movie that was returned on or before the due date (result shown in Figure).
Write a query to display the membership number, last name, and total rental fees earned from that membership (result shown in Figure.). The total rental fee is the sum of all of the detail fees (without the late fees) from all movies that the membership has rented.
Write a query to display the movie number, movie genre, average movie cost of movies in that genre, movie cost of that individual movie, and the percentage difference between the average movie cost and the individual movie cost (result shown in Figure). The percentage difference is calculated as
Write the SQL code that will create the table structure for a table named EMP_1. This table is a subset of the EMPLOYEE table. The basic EMP_1 table structure is summarized in the table below.
Write the SQL code to change the job code to 501 for the person whose employee number (EMP_NUM) is 107. After you have completed the task, examine the results, and then reset the job code to its original value.
Write the SQL code to create a copy of EMP_1, naming the copy EMP_2. Then write the SQL code that will add the attributes EMP_PCT and PROJ_NUM to its structure. The EMP_PCT is the bonus percentage to be paid to each employee. The new attribute characteristics are:EMP_PCTNUMBER (4,2)PROJ_NUMCHAR (3)
Write the SQL code to change the EMP_PCT value to 3.85 for the person whose employee number (EMP_NUM) is 103. Next, write the SQL command sequences to change the EMP_PCT values as shown in FigureP7.9.
Write the two SQL command sequences required to:a. Create a temporary table named TEMP_1 whose structure is composed of the EMP_2 attributes EMP_NUM and EMP_PCT.b. Copy the matching EMP_2 values into the TEMP_1 table.
Write the SQL code that will produce a virtual table named REP_1. The virtual table should contain the same information that was shown in Problem.
Using the data in the ASSIGNMENT table, write the SQL code that will yield the total number of hours worked for each employee and the total charges stemming from those hours worked. The results of running that query are shown inFigure.
Write the SQL code to generate the total hours worked and the total charges made by all employees. The results are shown in Figure.
Write the SQL code to generate the total hours worked and the total charges made to all projects. The results should be the same as those shown in Figure.
Generate a listing of all purchases made by the customers, using the output shown in Figure P7.28 as your guide.
Using the output shown in Figure as your guide, generate the listing of customer purchases, including the subtotals for each of the invoice line numbers.
Modify the query used in Problem 29 to produce the summary shown inFigure.
Modify the query in Problem 30 to include the number of individual product purchases made by each customer. (In other words, if the customers invoice is based on three products, one per LINE_NUMBER, you would count three product purchases. If you examine the original invoice data, you
Use a query to compute the average purchase amount per product made by each customer.
Write a query to produce the number of invoices and the total purchase amounts by customer, using the output shown in Figure P7.35 as your guide.
List the balance characteristics of the customers who have made purchases during the current invoice cyclethat is, for the customers who appear in the INVOICE table. The results of this query are shown inFigure.
Using the results of the query created in Problem provide a summary of customer balance characteristics as shown inFigure.
Find the customer balance summary for all customers who have not made purchases during the current invoicing period. The results are shown inFigure.
Write a query to display the first name, last name, street, city, state, and zip code of any customer who purchased a Foresters Best brand top coat between July 15, 2013, and July 31, 2013. If a customer purchased more than one such product, display the customers information only once
Write a query to display the employee number, last name, first name, and sum of invoice totals for all employees who completed an invoice. Sort the output by employee last name and then by firstname.
Write a query to display the largest average product price of any brand.
Write a query to display the brand ID, brand name, brand type, and average price of products for the brand that has the largest average productprice.
Write a query to display the manager name, department name, department phone number, employee name, customer name, invoice date, and invoice total for the department manager of the employee who made a sale to a customer whose last name is Hagan on May 18, 2011. For all person names, concatenate the
Write the SQL code to delete the row for the person named William Smithfield, who was hired on June 22, 2004, and whose job code classification is 500.
Using the query results in Problem 35 as your basis, write a query to generate the total number of invoices, the invoice total for all of the invoices, the smallest invoice amount, the largest invoice amount, and the average of all of the invoices.
Write a query to display the employee number, last name, first name, salary from date, salary end date, and salary amount for employees 83731, 83745, and 84039. Sort the output by employee number and salary fromdate.
Write a query to display the employee number, last name, e-mail address, title, and department name of each employee whose job title ends in the word ASSOCIATE. Sort the output by department name and employeetitle.
Write a query to display a brand name and the number of products of that brand that are in the database. Sort the output by the brandname.
Write a query to display the brand ID, brand name, and average price of products of each brand. Sort the output by brand name. (Results are shown with the average price rounded to two decimalplaces.)
Write a query to display the employee number, first name, last name, and largest salary amount for each employee in department 200. Sort the output by largest salary in descendingorder.
Write a query to display the customer code, first name, last name, and sum of all invoice totals for customers with cumulative invoice totals greater than $1,500. Sort the output by the sum of invoice totals in descendingorder.
Write a query to display the department number, department name, department phone number, employee number, and last name of each department manager. Sort the output by departmentname.
Showing 600 - 700
of 3228
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
Last
Step by Step Answers