Modern Database Management (12th Edition)
12th Edition
ISBN: 9780133544619
Author: Jeffrey A. Hoffer, Ramesh Venkataraman, Heikki Topi
Publisher: PEARSON
expand_more
expand_more
format_list_bulleted
Concept explainers
Question
Chapter 4, Problem 4.56PAE
Program Plan Intro
a. Identify functional dependency between the attributes of given relation
Program Plan Intro
b. Identify Normal form of given relation
Program Plan Intro
c. Normalize the relation to 3NF
Expert Solution & Answer
Want to see the full answer?
Check out a sample textbook solutionStudents have asked these similar questions
Use the following DBDL that contains 4 entities in 3NF to create a Crow's Foot ERD (use the name of entities and attributes mentioned in DBDL) .
titleAuthor [ titleCode , authorID, sequence ]
FK titleCode -> Title
FK authorID -> Author
Author [ authorID, authorFName, authorLName ]
Title [ pubCode, titleCode, titleName, price ]
FK pubCode -> Publisher
Publisher [ pubCode, pubCompany]
Given the Bank database that contains the following tables (primary keys are underlined and foreign keys are preceded with #):Agency(agencyNumber, agencyName, city, assets)Client (clientNumber, firstName, lastName, clientCity)Account(accountNumber, #agencyNumber, #clientNumber, balance)Loan(loanNumber, #agencyNumber, #clientNumber, amount )
c) Find the average balance of customer accounts, for each agency whose average balance is greater than 10,000. The table result should have the columns agencyNumber and Balance_Average
What exactly is a derived attribute? Please provide an example. What are the benefits and drawbacks of keeping or not storing a derived attribute?
Chapter 4 Solutions
Modern Database Management (12th Edition)
Ch. 4 - Prob. 4.1RQCh. 4 - Prob. 4.2RQCh. 4 - Prob. 4.3RQCh. 4 - Describe the primary differences between the...Ch. 4 - Summarize six important properties of relations.Ch. 4 - Describe two properties that each candidate key...Ch. 4 - Describe the three types of anomalies that can...Ch. 4 - Demonstrate each of the anomaly types with an...Ch. 4 - Fill in the blanks in each of the following...Ch. 4 - What is a well-structured relation? Why are...
Ch. 4 - Prob. 4.11RQCh. 4 - Describe how the following components of an E-R...Ch. 4 - Prob. 4.13RQCh. 4 - Prob. 4.14RQCh. 4 - Briefly describe four typical problems that often...Ch. 4 - Prob. 4.16RQCh. 4 - Explain how each of the following types of...Ch. 4 - Prob. 4.18RQCh. 4 - Prob. 4.19RQCh. 4 - Prob. 4.20RQCh. 4 - Prob. 4.21RQCh. 4 - What is the relationship between the primary key...Ch. 4 - Prob. 4.23RQCh. 4 - Explain what can be done with primary keys to...Ch. 4 - Prob. 4.25RQCh. 4 - Explain three conditions that suggest a surrogate...Ch. 4 - Prob. 4.27RQCh. 4 - For each of the following E-R diagrams from...Ch. 4 - Prob. 4.29PAECh. 4 - Prob. 4.30PAECh. 4 - For your answers to the following Problems and...Ch. 4 - Figure 4-3212 shows a class list for Millennium...Ch. 4 - Prob. 4.33PAECh. 4 - Prob. 4.34PAECh. 4 - Prob. 4.35PAECh. 4 - Prob. 4.36PAECh. 4 - Prob. 4.37PAECh. 4 - Prob. 4.38PAECh. 4 - For your answers to the following Problems and...Ch. 4 - Transform Figure 2-15a, attribute version, to 3NF...Ch. 4 - Prob. 4.41PAECh. 4 - Prob. 4.42PAECh. 4 - Prob. 4.43PAECh. 4 - Prob. 4.44PAECh. 4 - For your answers to Problem and Exercise 3-33 from...Ch. 4 - Prob. 4.46PAECh. 4 - Prob. 4.47PAECh. 4 - Figure 4-38 includes an EER diagram for a...Ch. 4 - Prob. 4.49PAECh. 4 - Prob. 4.50PAECh. 4 - Prob. 4.51PAECh. 4 - Prob. 4.52PAECh. 4 - Figure 4-40 shows an EER diagram for a university...Ch. 4 - Explore the data included in Table 4-9. Assume...Ch. 4 - Prob. 4.55PAECh. 4 - Prob. 4.56PAECh. 4 - Prob. 4.57PAECh. 4 - Prob. 4.58PAE
Knowledge Booster
Learn more about
Need a deep-dive on the concept behind this application? Look no further. Learn more about this topic, computer-science and related others by exploring similar questions and additional content below.Similar questions
- Using the instance of the Airport database, formulate the following queries in the relationalalgebra: 1)Display the number of all carriers and the average of their revenues. 2)Display the minimum, the average, and the maximum distances of the flights that arescheduled on October 17th, 2017? 3)Display, for each carrierID, the number of flights and the average of their distances. Example: List all airport names. π name Airport Uploaded more examples on how to do it below:arrow_forwardCreate the following table as part of a Hilton Hotel Business database: a. Hotel (hotelNo, hotelName, City) where hotel contains hotel details and hotelNo is the primary key. b. Room (roomNo, hotelNo, type, price) Room contains room details for each hotel and (roomNo, hotelNo) forms the primary key. c. Booking (hotelNo, guestNo, dateFrom, dateTo, roomNo) Booking contains details of bookings and (hotelNo, guestNo, dateFrom) forms the primary key. d. Guest (guestNo, guestName, guestAddress) Guest contains guest details and guestNo is the primary key. Create a relationship and diagram of a database created as given above. ps - picture not relatedarrow_forwardThe table STUDENT has the attributes STUDENT_ID, NAME, SCHOOL_ID, and ADDR. The table SCHOOL has the attributes SCHOOL_ID, NAME, and STATE_CODE. Assume that there is a 1:N relation between SCHOOL and STUDENT. Which of the following creates a table that shows the STUDENT attributes and the corresponding SCHOOL attributes? a. SELECT * SCHOOL_ID FROM STUDENT JOIN SCHOOL b. SELECT * SCHOOL_ID FROM STUDENT JOIN SCHOOL USING(SCHOOL_ID) c. SELECT * SCHOOL_ID FROM STUDENT NATURAL JOIN SCHOOL d. SELECT * SCHOOL_ID FROM STUDENT, SCHOOL WHERE SCHOOL_ID = SCHOOL_IDarrow_forward
- Assume that the student table in the database of a university contains indexes on StudentID, which serves as the primary key, as well as Major, Age, MaritalStatus, and HomeZipCode (all secondary keys). Assume that the university needed a list of students who were over the age of 25, married, and from the 45462 zip code to major in MIS or computer science OR students who were majoring in computer engineering and were single and from the 45462 zip code to major in computer engineering. How can indexes be used to make sure that only entries that fulfill these requirements be accessible when they are needed?arrow_forwardSQL Queries: Given the following schema. Primary keys are underlined and highlighted in yellow and foreign keys are bold. The meaning of the relations is straightforward. Medical_Staff(MS_Id, MS_First, MS_Last, MS_PhoneNumber, MS_Occupation, Salary) Blood_Bank(Bank_Id, Bank_Name, Bank_Street, Bank_City, Bank_St, Bank_PhoneNumber) Donor(Donor_Id, Donor_First, Donor_Last, Donor_BloodType, Donor_City, Donor_St, DOB, TotalNumberOfDonations) Donation_Record(Donor_Id, Donation_Date, Donation_Amount) Employee(MS_Id, Bank_Id) Storage(Donor_Id, Donation_Date, Bank_Id) Write SQL queries to answer the following questions: a.Create a view to list of all employees for any Blood Bank that is in a city named St. Paul. (i.e., view output includes MS_Last, MS_First, and Bank_Id). Sort by Bank_Id, MS_Last, MS_First. b.Find full names of all donors who donated in 2021. (Hint: use IN) c.Create a stored procedure that takes two inputs: a Medical Staff identifier (MS_Id) and new salary. The…arrow_forwardSQL Queries: Given the following schema. Primary keys are underlined and highlighted in yellow and foreign keys are bold. The meaning of the relations is straightforward. Medical_Staff(MS_Id, MS_First, MS_Last, MS_PhoneNumber, MS_Occupation, Salary) Blood_Bank(Bank_Id, Bank_Name, Bank_Street, Bank_City, Bank_St, Bank_PhoneNumber) Donor(Donor_Id, Donor_First, Donor_Last, Donor_BloodType, Donor_City, Donor_St, DOB, TotalNumberOfDonations) Donation_Record(Donor_Id, Donation_Date, Donation_Amount) Employee(MS_Id, Bank_Id) Storage(Donor_Id, Donation_Date, Bank_Id) Write SQL queries to answer the following questions: a.Find the average donation amount for Sue Thompson (Donor Id = 3329) for the year 2018. b.Sum all donations by Bank for each State (i.e. the State the blood bank is located in, not the Donor). c.Find the name of the youngest donor and all the donations made in by that donor in 2020.arrow_forward
- Using the instance of the Airport database, formulate the following queries in the relationalalgebra: 1)Display the number of all carriers and the average of their revenues. 2)Display the minimum, the average, and the maximum distances of the flights that arescheduled on October 17th, 2017? 3)Display, for each carrierID, the number of flights and the average of their distances. Examples of how to do it below:arrow_forwardWhich of the following defines a relationship in which data can have multiple occurrences in each entity? one-to-one one-to-many many-to-many none of the abovearrow_forward
arrow_back_ios
arrow_forward_ios
Recommended textbooks for you
- A Guide to SQLComputer ScienceISBN:9781111527273Author:Philip J. PrattPublisher:Course Technology PtrNp Ms Office 365/Excel 2016 I NtermedComputer ScienceISBN:9781337508841Author:CareyPublisher:Cengage
- 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
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
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