Modern Database Management
13th Edition
ISBN: 9780134792293
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
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
42. Which quality of the table demonstrates why it is not in first normal form?
A: There are only non-key attributes visible
B: There is no primary key
C: there is data redundancy
D: each attribute has multiple values
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…
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
- Explain the use of derived tables.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_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_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
- please improe this writing: There is a big difference between time it takes to perform each of the queries. After adding primary key to each table, the time to perform each of the queries was faster than what it was before. Creating a primary key help identify unique data. Primary key in a table is a crucial column used in queries, optimized for fast performance with an associated index. The primary key index is the most efficient access path for a table. They are created after a table is created, optimizing search speed. Once constructed, indexes are internal structures that the user cannot actively access. If the internal query optimization procedure concludes that using an index will increase search efficiency, then it will be employed. When SQL queries are internally ready for execution, they automatically undergo optimization. The best way to run each query is determined by the optimization process, which may or may not include using a relevant index. Read and write speeds are…arrow_forwardThe 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_forwardHelp with SQL database 2. In database lab01, create 4 tables: course, advisor, student, and department (Note: The order of tables below is not proper for creating tables. Please think about which table should be created first) course (course_id varchar(8), title varchar(50), dept_name varchar(20), credits numeric(2,0), primary key (course_id), foreign key (dept_name) references department (dept_name)) advisor (s_ID varchar(5), i_ID varchar(5), primary key (s_ID), foreign key (s_ID) references student (ID)) student (ID varchar(5), name varchar(20) not null, dept_name varchar(20), tot_cred numeric(3,0), primary key (ID), foreign key (dept_name) references department (dept_name)) department (dept_name varchar(20), building varchar(15), budget numeric(12,2), primary key (dept_name)) . Populate tables (Note: The order of populating table below is not proper. Please think about which table should be populated first) Populate the table "student" with the data below: ('23121', 'Chavez',…arrow_forward
- Objective: At the end of the exercise, the students should be able to: • Use arithmetic operators and aggregate functions. Procedure: 1. Create a table named StudentGrade with the following columns and values. Set the appropriate data type for each column. CourseCode Course Title Units Grade 1.50 COSC1003 Data Structures and Algorithms 3 GEDC1006 Readings in Philippine History 3 2.25 PHED1003 Physical Education 3 2 1.25 GEDC1014 Rizal's Life and Works 3 1.50 3 1.25 COSC1007 Human-Computer Interaction INTE1044 3 1.75 Object-Oriented Programming COSC1001 Principles of Communication COSC1008 Platform Technology 3 2.25 3 1.50 a. The number of courses/subjects b. The total units c. The number of courses with grades higher than 2.00 (lower than 2.00 in the column) The number of courses with grades lower than 2.00 (higher than 2.00 in the column) d. e. The general weighted average (Get the sum of all the products of units and grades, then divide by the total units.) Expected output: a. 8 b.…arrow_forwardAll of the following are true about relational table except Each column has a distinct name Each table must have an identifier All the values in a column can be with different data types Oc. Each row represents one instancearrow_forwardINFO 2103 Database Programming Assignment #3 For this assignment, please use the SUPPLIER and COMPUTER table. The script is available in Google Classroom. Script name: computer.sql. Note: Use Subqueries Display the name of supplier(s) that supplied the most expensive computer? 2. Display the name of supplier(s) that supplied more expensive computer than that being supplied by IBM. 3. Display the supplier that supplied the most computers. 4. Display the building location that has the most computersarrow_forward
arrow_back_ios
SEE MORE QUESTIONS
arrow_forward_ios
Recommended textbooks for you
A Guide to SQLComputer ScienceISBN:9781111527273Author:Philip J. PrattPublisher:Course Technology Ptr
Np Ms Office 365/Excel 2016 I NtermedComputer ScienceISBN:9781337508841Author:CareyPublisher:Cengage
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