EBK DATABASE CONCEPTS
7th Edition
ISBN: 9780133777840
Author: AUER
Publisher: VST
expand_more
expand_more
format_list_bulleted
Expert Solution & Answer
Chapter 5, Problem 5.18RQ
Explanation of Solution
One-to-many relationship (1:N):
- One-to-many relationship is one of the types of binary relationship that refer to the relationship between two entities which means one parent field or record can be associated with one or more child field or record.
- In a one-to-many relationship (1:N), a parent is a row on the one side of 1:N, while a child is a row on the many side of 1:N which means one parent may contains many child.
Reason for placing the key in the child table which belongs to parent table:
- The parent relation is the “one” and the child relation is “many” in 1:N relationship. An attributes in a parent relation contain only one value.
- If the key belonging to the child was placed in the parent table, then the design should not be used to represent many side of the 1:N relationship...
Expert Solution & Answer
Want to see the full answer?
Check out a sample textbook solutionStudents have asked these similar questions
Note that primary keys are in BOLD. Note also that attributes with the same name in different tables are foreign keys. For example, doctorID in the Treatment table is a foreign key that refers to the Doctor table.
Doctor(doctorId:integer, specialtyID:integer, firstName:varchar(20), lastName:varchar(20), salary:numeric(11,2))
Specialty(specialtyID:integer, specialtyName:varchar(20), specialtyCost:numeric(11,2))
Treatment(doctorId:integer, patientSSN:integer, dateTreated:date, costOfTreatment:numeric(11,2))
Patient(patientSSN:integer, firstName:varchar(20), lastName:varchar(20), street:varchar(30), city:varchar(15), state:char(2),zipCode:integer(5))
2.3) Write an SQL statement to increase the cost of all Specialty treatment by 2.5%
2.4) Write a SQL statement to delete all treatment rows that were done before 2015 and cost less than $500.
2.5) Write an SQL statement to select all Specialties where the specialtytName has a null value
Q1: Show all the information from the employee table.
SELECT * FROM emp;
Q2: Show the employees names and numbers from employee table.
SELECT empname, Empno FROM emp;
Q3: Show the employees names, salaries and their annual salaries
from the employee table, name the last field by annual salary.
SELECT empname, sal, sal*12 FROM emp;
Or we can change the attribute name using as
SELECT empname, sal, sal*12 as "Annual Salary" FROM emp;
Q4: Show the employees names, salaries, salaries plus commotion
for each employees from employee table.
SELECT empname, sal, sal+tcommotion FROM emp;
Q5: Show the employees names, salaries, annual salaries plus 100$ for
each employees from employee table.
SELECT empname, sal, (sal*12)+100 FROM emp;
Q6: Retrieve the hire date and department number of the
employee(s) whose name is “John"
WHERE
SELECT hiredate, deptno FROM
John';
%3D
emp
empname
Q7. Retrieve the name and job title of all employees who work
for the department number 10.
SELECT empname, job FROM…
1) List all the functional dependencies of the above table. (Note, this requires you to state all the functional dependencies, not just count the number).
2) What normal form(s) is the table currently in? Justify your answer.
Chapter 5 Solutions
EBK DATABASE CONCEPTS
Ch. 5 - Explain how entities are transformed into tables.Ch. 5 - Prob. 5.2RQCh. 5 - Prob. 5.3RQCh. 5 - What is denormalization?Ch. 5 - Prob. 5.5RQCh. 5 - Explain the problems that denormalized tables may...Ch. 5 - Explain how the representation of weak entities...Ch. 5 - Explain how supertype and subtype entities are...Ch. 5 - Prob. 5.9RQCh. 5 - Prob. 5.10RQ
Ch. 5 - Show two different ways to represent the 1:1...Ch. 5 - Prob. 5.12RQCh. 5 - Prob. 5.13RQCh. 5 - Prob. 5.14RQCh. 5 - Prob. 5.15RQCh. 5 - Prob. 5.16RQCh. 5 - For your answer to question 5.15, code an SQL...Ch. 5 - Prob. 5.18RQCh. 5 - Prob. 5.20RQCh. 5 - Prob. 5.21RQCh. 5 - Explain how the terms parent table and child table...Ch. 5 - For your answers to questions 5.20, 5.21, and...Ch. 5 - Prob. 5.24RQCh. 5 - Prob. 5.25RQCh. 5 - Prob. 5.26RQCh. 5 - Prob. 5.27RQCh. 5 - Prob. 5.28RQCh. 5 - Define the three types of recursive binary...Ch. 5 - Prob. 5.30RQCh. 5 - Prob. 5.31RQCh. 5 - Prob. 5.32RQCh. 5 - Prob. 5.33RQCh. 5 - Prob. 5.34RQCh. 5 - Code an SQL statement that creates a table with...
Knowledge Booster
Similar questions
- 1. The data type for Borrower_name in Borrowers Table is: A. Short text C. Currency B. Long text D. Yes/No Borrowers Field Name Borrower ID Borrower Name Phone num Membership_activation Data Type AutoNumber Short Text Short Text Yes/Noarrow_forwardIndicate dependences and their type.arrow_forwardCan an outer join be easily implemented when joining more than two tables? Why or why not?arrow_forward
- Convert the following table to third normal form (3NF). In this table, StudentNum determines StudentName, NumCredits, AdvisorNum, and AdvisorName. Advisor Num determines AdvisorName. CourseNum determines Description. The combination of StudentNum and CourseNum determines Grade. STUDENT (StudentNum, StudentName, NumCredits, AdvisorNum, AdvisorName, CourseNum, Description, Grade). Identify the primary key for the original table and the primary keys for the resulting tables. Indicate the foreign keys with italic font. Give meaning names to the resulting tables.arrow_forwardSubject: DatabaseANSWER THE FOLLOWING QUESTIONS BASED ON THE GIVEN TABLE a) write the DDL statement to create the table CUSTOMER_DETAIL with the primary key constraint. (use most appropriate data type for each attribute). b) Add NOT NULL constraint to attribute of CustomerName c) Add a column named "Country" in the CUSTOMER_DETAIL table d) Rename the table to CUSTOMERarrow_forwardDrop Student Table Refer the following schema and drop Student table. Hint: To drop parent table all associate tables need to be dropped. Student - Course Enrollement Management STUDENT COURSE Number(4) Pipnis FirstName PK PK courseid number(4) Varchar2(20) CourseName varchar2(20) LastName Varchar2(20) Duration number(2) Street Varchar2(20) Fees number(7,2) City Varchar2(20) Date REGISTRATION PK-Primary key FK-Foreign key FK CourselD number(4) FK number(4) O Pipnis Date F5 F10 A 63 70arrow_forward
- 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_forwardWrite a SQL code that will save the above changes made to the Articles table.arrow_forwardWrite SQL code for the following design schema of three tables. The tables should have the following properties: sales table has a primary key on SalesNumber and it is set to AUTO-INCREMENT.products table has a primary key on number and it is set to AUTO-INCREMENT, they is an unique index on prodidsalesdetails table has a primary key on number and it is set to AUTO-INCREMENT, they is an index on prodid. They is a one-to-many relationship between the salesdetails.SalesNumber andsales.SalesNumber fields. There is a one-to-many relationship between the products.prodid and salesdetails.prodid fields. There is to be a delete constraint on the product.prodid field a record in the product table should not be allowed to be deleted if they exist a matching prodid in the salesdetails table. Primary and foreign keys should be implemented in the appropriate tables. Referential Integrity should be enforce at the database level, where appropriate for example, if a sales record is deleted all…arrow_forward
- With SQL code, find the name of each employee with a manager along with their manager's name (use cartesian product and rename) Employee table: create table employee( ID varchar(6) not null,person_name varchar(10),street varchar(10),city varchar(10),primary key (ID)); Manages table: create table manages(ID varchar(6) not null,manager_id varchar(6),primary key (ID),foreign key (ID) references employee,foreign key (manager_id) references employee);arrow_forwardDisplay the name of customer 16 and the names of all the customers that are in the same zip code as customer 16. (Be sure this query will work for any customer.) Rewrite your answer to Problem for each customer, not just customer 16.arrow_forwardThe 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.arrow_forward
arrow_back_ios
SEE MORE QUESTIONS
arrow_forward_ios
Recommended textbooks for you
- Database Systems: Design, Implementation, & Manag...Computer ScienceISBN:9781305627482Author:Carlos Coronel, Steven MorrisPublisher:Cengage LearningDatabase Systems: Design, Implementation, & Manag...Computer ScienceISBN:9781285196145Author:Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos CoronelPublisher:Cengage Learning
Database Systems: Design, Implementation, & Manag...
Computer Science
ISBN:9781305627482
Author:Carlos Coronel, Steven Morris
Publisher:Cengage Learning
Database Systems: Design, Implementation, & Manag...
Computer Science
ISBN:9781285196145
Author:Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos Coronel
Publisher:Cengage Learning