Modern Database Management
13th Edition
ISBN: 9780134773650
Author: Hoffer
Publisher: PEARSON
expand_more
expand_more
format_list_bulleted
Question
Chapter 9, Problem 9.35PAE
Program Plan Intro
To draw:
A single table format that will contain the non-redundant data from the given three tables.
Expert Solution & Answer
Want to see the full answer?
Check out a sample textbook solution
Students have asked these similar questions
Question 3 refers to two data tables giving the details of a company's employees and
company's job titles, called EMP and JOB respectively, which have the following format:
Table EMP
Column type
Integer
Up to 10
characters
Column name
Example entry
Description
Employee ID number
Employee surname
EMPNO
7369
ENAME
SMITH
Integer
Integer
Employee's job title No
ID no. of employee's
JOBNO
10
MGR
7566
manager
Date employment started
Monthly salary
HIREDATE
Date
17/12/1980
SAL
Integer
1600
Table JOB
Column type
Integer
Up to 15
characters
Column name
Example entry
Description
job title No
Employee's job title
JOBNO
10
JOB TITLE
SALESMAN
b) Using EMP and JOB tables, give an example of the JOIN SQL statement which
uses "WHERE", "GROUP BY", “HAVING" and “ORDER BY" clauses. Explain the
result table of the SQL statement. (Maximum 300 words).
48
Refer to the given tables.
Salesperson Salesperson Commission Year of Department Manager
Number
Percentage
Product
Number
SALESPERSON table
Salesperson
Number
PRODUCT table
QUANTITY table
Frdig
Quantity
Which feature of the tables makes it clear that they are in second normal form?
Each non-key attribute is fully functionally dependent on the key of that table.
O The tables are completely free of data redundancy.
Each non-key attribute functions completely independently of the key of that table.
O The tables are dependent on one another in descending order.
Jh
Within this database, you will be using tables throughout entire model, and pick the appropriate tables for each question for this assignment. Based on assignment readings and slides, perform each of the following queries from the fil table.
Guided Queries: For the 5 following questions, your submission will be the query that you would write that would return the needed result
Write a query that returns the Staff ID for the staff member who is involved in the most rentals.
Write a query that returns all the distinct list of film category names.
Write a query that counts the rentals of films, by category, and returns the category name and count of rentals.
Write a query that returns the First_Name and Last_Name of each actor, and the count of the number of times their films have been rented.
Write a query that returns the First_Name and Last_Name of the actor, and the count of the number of time their film has been rented, of the actor with the most rentals
Chapter 9 Solutions
Modern Database Management
Ch. 9 - Prob. 9.1RQCh. 9 - Prob. 9.2RQCh. 9 - Prob. 9.3RQCh. 9 - List the five major trends that necessitate data...Ch. 9 - Prob. 9.5RQCh. 9 - Prob. 9.6RQCh. 9 - Prob. 9.7RQCh. 9 - Prob. 9.8RQCh. 9 - Prob. 9.9RQCh. 9 - Prob. 9.10RQ
Ch. 9 - List four objectives of derived data.Ch. 9 - Prob. 9.12RQCh. 9 - Prob. 9.13RQCh. 9 - Explain the pros and cons of logical data marts.Ch. 9 - Prob. 9.15RQCh. 9 - What is a helper table, and why is it often used...Ch. 9 - Describe the characteristics of a surrogate key as...Ch. 9 - Prob. 9.18RQCh. 9 - What is the purpose of conformed dimensions for...Ch. 9 - Prob. 9.20RQCh. 9 - Prob. 9.21RQCh. 9 - Prob. 9.22RQCh. 9 - Prob. 9.23RQCh. 9 - Prob. 9.24RQCh. 9 - Prob. 9.25RQCh. 9 - Describe the current key trends in data...Ch. 9 - Discuss the benefits of offering data warehousing...Ch. 9 - Prob. 9.28RQCh. 9 - List six typical characteristics of reconciled...Ch. 9 - Prob. 9.30RQCh. 9 - Prob. 9.31RQCh. 9 - Prob. 9.32RQCh. 9 - Prob. 9.33RQCh. 9 - Describe some field-level and record-level data...Ch. 9 - Prob. 9.35PAECh. 9 - Prob. 9.36PAECh. 9 - Prob. 9.37PAECh. 9 - Prob. 9.38PAECh. 9 - You are to construct a star schema for Simplified...Ch. 9 - Simplified Automobile Insurance Company would like...Ch. 9 - Prob. 9.41PAECh. 9 - Prob. 9.42PAECh. 9 - Prob. 9.43PAECh. 9 - Prob. 9.45PAECh. 9 - Prob. 9.46PAECh. 9 - Prob. 9.47PAECh. 9 - Prob. 9.48PAECh. 9 - Prob. 9.49PAECh. 9 - Prob. 9.50PAECh. 9 - Prob. 9.51PAECh. 9 - Prob. 9.52PAECh. 9 - Prob. 9.53PAECh. 9 - Prob. 9.54PAE
Knowledge Booster
Similar questions
- Closely examine the following tables and use it to answer the questions that follow: department course student_type student_category Student registration registration_type Suppose that the following are true: - all id column are primary keys in whichever table they are - the dept_id in course table is foreign key that is referencing the id column in the department table - the foreign keys stud_type_id, stud_cat_id in the student table are referencing the id columns in the student_type and student_category tables respectively. - the coursecode and stud_id columns in the registration table are foreign keys in the course and student tables respectively. 1. Design an Entity Relationship Diagram for the system above. 2. Write query that would return the names and contacts of all Local students. 3. Write a query that would return names, contact and locations of all departments that has a location text with Ave 3 as part of it. [hint: use the LIKE clause with “_” or “%”] 4. What would…arrow_forwardWhen doing away with rows, columns, and tables, what is the most critical consideration to bear in mind?arrow_forwarddraw ERD for these tabels The system must contain tables such as category(catNo,catogeryName) ,Items(itemNo,ItemName,Price,total_quantity, catNo),Items_stor_entry(entry_no number primary key,itemNo,entry_date ,quantity), Employee (employeeNo,employeeName,job) ,branch(No,Name), Customer(customerNo,costomerName,gender) , ,Invoice (employeeNo,customerNo,total price ,invoiceNo,InvoiceDate,branchNo ) InvoiceDeatail contain List Of Purchase Items contain detail information related to the invoice (invoiceNo,ItemNo, paidPrice, quantity)arrow_forward
- Consider the Sales Order and table used to store its data below. Sales_Order (SalesOrderNo, Date, CustomerNo, CustomerName, CustomerAddress, ClerkNo, ClerkName, ItemNo, Description, Quantity, UnitPrice, OrderTotal) Please normalize this table to 3NF.arrow_forward3. The QR table records the workshop attendance of students. The BB table is a master table that holds student’s data in INFO151. The ZOOM table automatically records students who enter the workshop session with zoom. The requirements for each of the tables are listed in the requirement table below. (a) Write the DDL to create the tables with the suggested attributes, data types and constraints. (b) What is a composite key? Is the primary key in the BB table a composite key or a surrogate key? Must the last name be unique for this type of primary key? (c) Discuss the limitation of using FName and LName as the primary key. (d) Is the relationship between BB and QR tables a one-to-one? If it is not, state the correct relationship.arrow_forwardExactly how many global descriptor tables are there?arrow_forward
- Database Foundations: Design a database to produce the following reports. Do not use any surrogate keys in your design. For each guide, list the guide number, guide last name, guide first name, address, city, state, postal code, telephone number, and date hired. For each trip, list the trip ID number, the trip name, the location from which the trip starts, the state in which the trip originates, the trip distance, the maximum group size, the type of trip (hiking, biking, or paddling), the season in which the trip occurs, and the guide number, first name, and last name of each guide. A guide may lead many trips and a trip may be led by many different guides. For each client, list the client number, client last name, client first name, address, city, state, postal code, and telephone numberarrow_forwardCreate a table “PRODUCTS” with the below mentioned structure:Product ID NUMBER(11)Supplier ID NUMBER(11)Category ID NUMBER(11)Quantity Per Unit VARCHAR2(20)Unit Price NUMBER(11)Units In Stock NUMBER(11)Units On Order NUMBER(11)Product ID should be the Primary Key.2) Consider the following tables:WORKS(Pname,Cname,Salary)LIVES(Pname,Street,City)LOCATED_IN(Cname,City)MANAGER(Pname,Mgername)Where Pname=Person name, Cname= Company name and Mgrname = Manager name.Write the SQL for the following:List the names of the people who work for the company Wipro along with the cities they live in.arrow_forwardRefer to the dimension tables and EmployeeFact table below. Job Date Location EmployeeFact •JobID •DatelD •LocationID • ( OEmployeelD Title FullDate StreetAddress oJobID JobCode DayNumber City PostalCode OLocationID LevelCode MonthNumber oHireDatelD OBirthDatelD ) SalaryAmount BonusAmount MonthName State Year CountryCode EducationYears PerformanceRating The following query reports the employee with the highest salary amount per postal code for employees with level code 8. Complete the missing values. SELECT CountryCode, EmployeeID, MAX(_ _(A)_ FROM (B). Location, Job WHERE EmployeeFact.LocationID = Location.LocationID AND EmployeeFact.JobID = (C). AND = 8 GROUP (A) Ex: Identifier (B) (C)arrow_forward
- The one-to-many relationship between two tables is made by including the primary key from one table into the other, thus making it a: (SELECT ONLY ONE ANSWER) A)Compound key B) Foreign key C) Important key D)Primary keyarrow_forwardEmployees table—the data Open the Employees table in Datasheet View. You should now see the data currently in the Employees table, as in Figure 19.3. Scan the data to become familiar with the kind of data being stored. In this view, you would be able to add, delete, or modify data in the table. Click the small + sign beside the employee with ID 149. What do you see? Why do you see it? (Hint: think about the relationships between the tables.) Would you expect to see + signs in either of the other two tables?arrow_forwardThe term attribute refers to a of a table. Your answer When we talk about the table in more accurate details, for example this field, what is its data type and what is the domain of it like integer or text, this type is called view of data. Your answerarrow_forward
arrow_back_ios
SEE MORE QUESTIONS
arrow_forward_ios
Recommended textbooks for you
Np Ms Office 365/Excel 2016 I NtermedComputer ScienceISBN:9781337508841Author:CareyPublisher:Cengage
Np Ms Office 365/Excel 2016 I Ntermed
Computer Science
ISBN:9781337508841
Author:Carey
Publisher:Cengage