I need help identifying the primary key and foreign key for the BENEFIT table. I've had people say it is EMP_CODE+PLAN_CODE and people say there isn't one, but a table cannot exist without one. This then brings up the question if the BENEFIT table exhibits entity integrity, and I am not sure if it does because there are duplicate entries in both columns. EMP CODETable name: EMPLOYEEEMP_LNAME JOB_CODETable name: BENEFITEMP CODEPLAN_CODE14 Rudell215215 McDade115316 Ruellardo116117 Smith317120 Smith2173174203Database name: Ch03_BeneCoTable name: JOBJOB CODE JOB DESCRIPTION1 Clerical2 Technical3 ManagerialTable name: PLANPLAN_CODEPLAN_DESCRIPTION1 Term life2 Stock purchase3 Long-term disability4 Dental

BENEFIT Table StructureThe BENEFIT table has two columns:EMP_CODE: Represents an employee code,…

EMP CODE Table name: EMPLOYEE EMP_LNAME JOB_CODE Table name: BENEFIT EMP CODE PLAN_CODE 14 Rudell 2 15 2 15 McDade 1 15 3 16 Ruellardo 1 16 1 17 Smith 3 17 1 20 Smith 2 17 3 17 4 20 3 Database name: Ch03_BeneCo Table name: JOB JOB CODE JOB DESCRIPTION 1 Clerical 2 Technical 3 Managerial Table name: PLAN PLAN_CODE PLAN_DESCRIPTION 1 Term life 2 Stock purchase 3 Long-term disability 4 Dental

EMP CODE Table name: EMPLOYEE EMP_LNAME JOB_CODE Table name: BENEFIT EMP CODE PLAN_CODE 14 Rudell 2 15 2 15 McDade 1 15 3 16 Ruellardo 1 16 1 17 Smith 3 17 1 20 Smith 2 17 3 17 4 20 3 Database name: Ch03_BeneCo Table name: JOB JOB CODE JOB DESCRIPTION 1 Clerical 2 Technical 3 Managerial Table name: PLAN PLAN_CODE PLAN_DESCRIPTION 1 Term life 2 Stock purchase 3 Long-term disability 4 Dental

Database System Concepts

7th Edition

ISBN:9780078022159

Author:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Publisher:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Chapter1: Introduction

Section: Chapter Questions

Problem 1PE

See similar textbooks

Similar questions

use draw.io to model any FUNCTIONAL PERSPECTIVE part of the registration process and include both exclusive join; exclusive split and parallel split and parallel join.
Open the Missing Addresses query in Design view. Add a new column to determine if a customer does not have an address on file. If the customer’s Address is null, it should display Missing. If not, it should display nothing. Name the column AddressPresent. Add criteria of Missing to the column you just created, so only the customers missing an address display. Move the AddressPresent field so it appears between PhoneNumber and Address. Run the query. Ensure only customers with null Address fields display. Save and close the query.
Define the term referential integrity constraint. Give an example of a referential integrity constraint for the tables you created for question 1.8.
!I am doing all this from cengage! Task 10: List all the tables contained within the system catalog, but only display the first 10 records with a TABLE_TYPE of SYSTEM VIEW. I did with: SELECT * FROM (SELECT * from KimTay) WHERE ROWNUM <= 11; and with: SELECT * FROM (SELECT TABLE_NAME FROM ALL_TABLES ORDER BY TABLE_NAME ) WHERE ROWNUM <= 10; and with: SELECT * FROM SYSTABLES but nothing worked and some of them gave me an error as (ERROR 1248 (42000) at line 1: Every derived table must have its own alias) and I need answers for these too: Task 11: List all the columns contained within the system catalog, but only display the first 11 records that are in the KimTay TABLE_SCHEMA. Task 12: List all the views contained within the system catalog, but only display the first 12 records. Task 13: Write the command to display only tables within the system catalog that have the KimTay TABLE_SCHEMA and are of the type BASE TABLE. Display only the following columns:…
You are working with a database table that contains invoice data. The table includes columns for invoice_id and billing_state. You want to remove duplicate entries for billing state and sort the results by invoice ID. You write the SQL query below. Add a DISTINCT clause that will remove duplicate entries from the billing_state column. NOTE: The three dots (...) indicate where to add the clause. SELECT ... FROM invoice ORDER BY invoice_id What billing state appears in row 17 of your query result? 1 point 1-CA 2-NV 3-WI 4-AZ
Your task is to break this up into a list of "normalized" tables that you describe using the text-based "relational notation". You will need to break this example into as many separate tables as you feel is appropriate to remove all modification anomalies. Please submit your relational notations in an ordinary word document along with the sample data in a "grid". I'm expecting to see both PK and FK notations in your submission. Note: I'm not asking for a database... I'm asking for a list of tables in relational notation plus the sample data in rows/columns. Part One: Use text-based relational notation to describe each table/field Note: I am not yet requiring you to solve the "multi-column" problem Show PK and FK (use Microsoft Word's underline and italics tools) Part Two: Create a "grid" for each table with the all of the rows/columns of data for that table after normalization (to include PKs/FKs) Make sure you don't have any duplicate rows in your "grid" Note: You can just do…
What you should draw your attention to are the COST values. While these are arbitrary, they are not meaningless. Find the section of the query where this grows the largest, that is the slowest part of the query in need of optimization. (Save yourself some time as search for other_publisher_views) As this query had been previously reviewed for proper indexing, all queries should be indexed so we need to find another optimization. Since this is already indexed, draw your attention to the ROWS returned. If you look at the next query which makes use of these results, you'll notice the ROWS is much smaller. So our problem is we are looking at way more data than we need to. We are pulling over 100,000 rows when we are trying to find 4 of them. Now let's extract the critical section to work with, along with simplified versions of its dependencies: WITHlisting_info_raw AS ( SELECT '' AS property_id_sha, '' AS mc_export_property_id_sha, '(Rental)' AS pretty_address, '…
Using the World database, two updates are needed for one row in the City table. The city of Giumri in Armenia (country code ‘ARM’) is spelled incorrectly in the table. It should be spelled ‘Gyumri’. Also, the district is currently ‘irak’ in the table and should be ‘Irak’. Make these changes in one construct.
Determine the functional dependencies that exist in the following table. After determining the functional dependencies, convert this table to an equivalent collection of tables that are in third normal form. ITEM(ITEM_ID, DESCRIPTION, ON_HAND, CATEGORY, LOCAITON, PRICE,(INVOICE_NUM, INVOICE_DATE, CUST_ID, FIRST_NAME, LAST_NAME, QUANTITY, QUOTED_PRICE))
Write the MySql Queries. Proving yourself worthy of being able to handle bigger tasks, the tech lead has decided to give you a challenging job. However, this time, the data that you would be handling is very sensitive and no one wants this data to be leaked. Therefore, instead of getting the entire table, the tech lead has given you the list of attributes that the table contains and the table name. The information given is as follows: You are tasked with building the MySql queries to retrieve the following information: 1. Find the first_name, last_name, email, phone_number, hire_date, and department_id of all the employees with the latest hire_date. 2. Find the first_name, last_name, employee_id, phone_number, salary and department_id of all the employees with the lowest salary in each department. 3. Find the first_name, last_name, employee_id, commission_pct and department_id of all the employees in the department 'DPT007' who have a lower commission_pct than all of the…