Database Concepts (7th Edition)
7th Edition
ISBN: 9780133544626
Author: David M. Kroenke, David J. Auer
Publisher: PEARSON
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 solution
Students 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
Indicate dependences and their type.
You have the following tables:
APARTMENTS (ADDRESS, CITY, STATE, RENTER_ID, RENTER_LAST_NAME)
RENTERS (RENTER_ID, FIRST_NAME, LAST_NAME)
What is the primary key for the APARTMENTS table? (It may be a composite key involving 2 or more fields) What are the foreign keys, if any?
What is the primary key for the RENTERS table? What are the foreign keys, if any?
What field in RENTERS can be eliminated so that the RENTERS table is normalized?
Chapter 5 Solutions
Database Concepts (7th Edition)
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
- 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…arrow_forward1. 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_forwardClosely 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_forward
- Drop 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_forwardConvert 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_forwardCan an outer join be easily implemented when joining more than two tables? Why or why not?arrow_forward
- Write the SQL statements to create all of the necessary tables to represent the above diagram, representing as many constraints as possible. Note: you do not need to worry about specifying types for the fields, but you do need to provide suitable foreign key option(s). When playing a game, the home team name should be stored in attribute htname and the away team name should be stored in atname.arrow_forwardImplement a new independent entity phone in the Sakila database. Attributes and relationships are shown in the diagram below The diagram uses Sakila naming conventions. Follow the Sakila conventions for your table and column names: All lower case Underscore separator between root and suffix Foreign keys have the same name as referenced primary key Write CREATE TABLE and ALTER TABLE statements that: Implement the entity as a new phone table. Implement the has relationships as foreign keys in the Sakila customer, staff, and store tables. Remove the existing phone column from the Sakila address table. Step 2 requires adding a foreign key constraint to an existing table. Ex: ALTER TABLE customer ADD FOREIGN KEY (phone_id) REFERENCES phone(phone_id) ON DELETE SET NULL ON UPDATE CASCADE; Specify data types as follows: phone_id, phone_number, and country_code have data type INT. phone_type has date type VARCHAR(12) and contains strings like 'Home', 'Mobile', and 'Other'. Apply these…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
- The 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_forwardWhen converting the ERD to a table design, how should you handle the AssignedTo relationship? (Be sure to include a discussion of primary keys, foreign keys, and tables in your answer.)arrow_forwardIn relational model terminology, a table is considered as Select one: a. Tuple b. Range c. Relation d. Domainarrow_forward
arrow_back_ios
SEE MORE QUESTIONS
arrow_forward_ios
Recommended textbooks for you
Database 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 ScienceISBN:9781305627482Author:Carlos Coronel, Steven MorrisPublisher: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
Database Systems: Design, Implementation, & Manag...
Computer Science
ISBN:9781305627482
Author:Carlos Coronel, Steven Morris
Publisher:Cengage Learning