The following is the relation notation of the Veterinary Office List. VETERINARY_OFFICE (PetID, PetName, PetType, PetBreed,...
The following is the relation notation of the Veterinary Office List.
VETERINARY_OFFICE (PetID, PetName, PetType, PetBreed, OwnerID, OwnerLastName, OwnerFirstName, ServiceDescription, ServiceDate, ServiceCharge)
The functional dependencies are given below:
PetID -> PetName, PetType, PetBreed, OwnerID, OwnerLastName, OwnerFirstName
OwnerID -> OwnerLastName, OwnerFirstName
ServiceDescription -> ServiceCharge
PetID, ServiceDate -> ServicedDescription, ServiceCharge
Assumption: A pet belongs to only one owner, while an owner may have more than one pet. A pet receives at most one treatment on any given date.
Use the functional dependencies above to answer the following questions.
A. Show the 1NF in relational notation and indicate the primary key.
B. Show all partial dependency in the relation only (if any) and identify the 2NF. Show primary keys and foreign keys references. Use relational notation.
C. Show all transitive dependency in your answer to question b (if any) and identify 3NF (show all relations). Show primary keys and foreign keys references. Use relational notation
Homework Answers
Request Answer!
We need at least 10 more requests to produce the answer.
0 / 10 have requested this problem solution
The more requests, the faster the answer.
Add Answer to:
The following is the relation notation of the Veterinary Office
List.
VETERINARY_OFFICE (PetID, PetName, PetType, PetBreed,...
2. The following is the relation notation of a table for a veterinary office. [70 pts....
2. The following is the relation notation of a table for a veterinary office. [70 pts. total] VETERINARY_OFFICE (PetID, PetName, PetType, PetBreed, OwnerID, OwnerLastName, OwnerFirstName, ServiceDescription, ServiceDate, ServiceCharge) The functional dependencies are given below: PetID -> PetName, PetType, PetBreed, OwnerID, OwnerLastName, OwnerFirstName OwnerID -> OwnerLastName, OwnerFirstName ServiceDescription -> ServiceCharge PetID, ServiceDate > ServiceDescription, ServiceCharge Assumptions: 1)A pet belongs to only one owner, 2) an owner may have more than one pet, 3A pet receives at most one treatment on any...
write an SQL statement to list all the people in the Owner table, along with their...
write an SQL statement to list all the people in the Owner table, along with their pets' names if any. That is, for each pet an owner has, there will be a record containing the owner and the pet's name. For any owner contained in the Owner table, the output should always contain the owner's record even if no pets are associated with this owner. Hint: outer join/s may be needed. -- DROP TABLE PetAndOwner, Pet, PetType, Owner; CREATE TABLE...
1. Write SQL CREATE Table statement to create the following table with Owner ID as a...
1. Write SQL CREATE Table statement to create the following table with Owner ID as a surrogate key. Owner ID is a surrogate key (starts at 1 and increments by 1) hint: IDENTITY (1, 1) is syntax for surrogate key. PET_OWNER (OwnerID, OwnerLastName, OwnerFirstName, OwnerPhone, OwnerEmail) You can try inserting some data into your tables too! 2. Write SQL CREATE Table statement to create the following table with Pet ID as a surrogate key. Pet ID is a surrogate key...
Sample data for these tables are shown in Figures 3-26 and 3-27. For each SQL statement...
Sample data for these tables are shown in Figures 3-26 and 3-27. For each SQL statement you write, show the results based on these data. If possible, run the statements you write for the questions that follow in an actual DBMS, as appropriate, to obtain results. Use data types that are consistent with the DBMS you are using. If you are not using an actual DBMS, consistently represent data types by using either the MySQL, Microsoft SQL Server, or Oracle...
Suppose we have the following relation (R) that keeps track of car sales: R(car date_sold, salesperson#,...
Suppose we have the following relation (R) that keeps track of car sales: R(car date_sold, salesperson#, comission_rate, discount) In addition to the functional dependency implied by the primary key (car#), assume the following functional dependencies: date_sold discount salesperson commission_rate Explain why this relation is not in Boyce-Codd Normal Form (BCNF). a. b. Show how you would normalize this relation to achieve Boyce-Codd Normal Form by drawing a new relational schema. Highlight all primary keys by underlining them and use arrows...
2.1 Apply the normalization process to the Veterinary Office List–Version One relation shown in Figure 1-34...
2.1 Apply the normalization process to the Veterinary Office List–Version One relation shown in Figure 1-34 (see page 63 and below) to develop a set of normalized relations. Show the results of each of the steps in the normalization process. 1 PetName 2 King 3 Teddy 4 Fido 5 AJ 6 Cedro 7 Woolley 8 Buster 9 Jiddah PetType Dog Cat Dog Dog Cat Cat Dog Cat C P etBreed Std. Poodle Cashmier Std. Poodle Collie Mix Unknown Unknown Border...
Consider the following relation R, where {A, B} is its PK. Assume that R is in...
Consider the following relation R, where {A, B} is its PK. Assume that R is in the first normal form (INF). R (A, B, C, D, E, F) Functional dependencies: FD1: BàC FD2: AàDE FD3: ABàF Why is this table not in 2NF? Specify which FDs make R violate 2NF. Normalize the data shown in this table to second normal form (2NF). Specify the primary and foreign key (if any) in each table of your 3NF relations. Normalize the data...
Language: SQL - Normalization and Functional Dependencies Part 4 Normalization and Functional Dependencies Consider the following relation R(A, B, C, D)and functional dependencies F that hold o...
Language: SQL - Normalization and Functional
Dependencies
Part 4 Normalization and Functional Dependencies Consider the following relation R(A, B, C, D)and functional dependencies F that hold over this relation. F=D → C, A B,A-C Question 4.1 (3 Points) Determine all candidate keys of R Question 4.2 (4 Points) Compute the attribute cover of X-(C, B) according to F Question 43 (5 Points) Compute the canonical cover of F.Show each step of the generation according to the algorithm shown in class....
Normalisation Consider the following relation schema about project meetings: PMG(projID, title, type, manager, jobID, start-date, end-date,...
Normalisation Consider the following relation schema about project meetings: PMG(projID, title, type, manager, jobID, start-date, end-date, contractor, contractNo) Some notes on the semantics of attributes are as follows: • Each project has a unique project ID (projID) and also has a title, type and manager. Each manager has a specialty project type. • A project often contracts jobs to contractors with start-date and end-date. Contracts are identified by contract numbers (contractNo), but contract details are out of the scope of...
DATABASE SYSTEMS Project INDIVIDUAL WORK DELIVERABLE #: SUBMISSION DATE No Group Work Allowed Apr...
DATABASE SYSTEMS Project INDIVIDUAL WORK DELIVERABLE #: SUBMISSION DATE No Group Work Allowed April 8 Introduction to Coursework You have been approached by a University for the design and implementation of a relational database system that will provide information on the courses it offers, the academic departments that run the courses, the academic staff and the enrolled students. The system will be used mainly by the students and the academic staff. The requirement collection and analysis phase of the database...
1. Write SQL CREATE Table statement to create the following table with Owner ID as a surrogate key. Owner ID is a surrogate key (starts at 1 and increments by 1) hint: IDENTITY (1, 1) is syntax for surrogate key. PET_OWNER (OwnerID, OwnerLastName, OwnerFirstName, OwnerPhone, OwnerEmail) You can try inserting some data into your tables too! 2. Write SQL CREATE Table statement to create the following table with Pet ID as a surrogate key. Pet ID is a surrogate key...
Sample data for these tables are shown in Figures 3-26 and 3-27. For each SQL statement you write, show the results based on these data. If possible, run the statements you write for the questions that follow in an actual DBMS, as appropriate, to obtain results. Use data types that are consistent with the DBMS you are using. If you are not using an actual DBMS, consistently represent data types by using either the MySQL, Microsoft SQL Server, or Oracle...
Suppose we have the following relation (R) that keeps track of car sales: R(car date_sold, salesperson#, comission_rate, discount) In addition to the functional dependency implied by the primary key (car#), assume the following functional dependencies: date_sold discount salesperson commission_rate Explain why this relation is not in Boyce-Codd Normal Form (BCNF). a. b. Show how you would normalize this relation to achieve Boyce-Codd Normal Form by drawing a new relational schema. Highlight all primary keys by underlining them and use arrows...
2.1 Apply the normalization process to the Veterinary Office List–Version One relation shown in Figure 1-34 (see page 63 and below) to develop a set of normalized relations. Show the results of each of the steps in the normalization process. 1 PetName 2 King 3 Teddy 4 Fido 5 AJ 6 Cedro 7 Woolley 8 Buster 9 Jiddah PetType Dog Cat Dog Dog Cat Cat Dog Cat C P etBreed Std. Poodle Cashmier Std. Poodle Collie Mix Unknown Unknown Border...
Language: SQL - Normalization and Functional
Dependencies
Part 4 Normalization and Functional Dependencies Consider the following relation R(A, B, C, D)and functional dependencies F that hold over this relation. F=D → C, A B,A-C Question 4.1 (3 Points) Determine all candidate keys of R Question 4.2 (4 Points) Compute the attribute cover of X-(C, B) according to F Question 43 (5 Points) Compute the canonical cover of F.Show each step of the generation according to the algorithm shown in class....
DATABASE SYSTEMS Project INDIVIDUAL WORK DELIVERABLE #: SUBMISSION DATE No Group Work Allowed April 8 Introduction to Coursework You have been approached by a University for the design and implementation of a relational database system that will provide information on the courses it offers, the academic departments that run the courses, the academic staff and the enrolled students. The system will be used mainly by the students and the academic staff. The requirement collection and analysis phase of the database...
Most questions answered within 3 hours.
-
You have just been hired by the EPA to help with the new gas
mileage standards...
asked 44 minutes ago -
4) A firm is hit by increases in its wage rate, w.
a) How does this...
asked 59 minutes ago -
A square current loop 5.4 cm on each side carries a 510 mA
current. The loop...
asked 3 hours ago -
What are five tips in conducting knowledge assessment/literature
review research? Each of these tips needs to...
asked 3 hours ago -
Antigen presenting cells link innate and adaptive immune
responses. In the theoretical scenario where phagosomes cannot...
asked 3 hours ago -
Dealing with conflict, in my opinion, is a skill and an art. I
believe that those...
asked 4 hours ago -
6. A long straight wire with circular cross section and radius
1.5 cm carries a 1.2...
asked 4 hours ago -
Deacon Corporation has provided the following financial data
from its balance sheet and income statement:
Year...
asked 5 hours ago -
List and discuss at least three variables from everyday life for
which you expect the variable ...
asked 5 hours ago -
A high-pass filter consists of a 1.60 μF capacitor in series
with a 125 Ω resistor....
asked 5 hours ago -
A newly developed artificial intelligence has trouble
distinguishing hand-written numbers from hand-written alphabets.
There are pictures...
asked 5 hours ago -
The carbonate ion CO3 2- has multiple resonance structures.
a. Draw them and assign the hybridization...
asked 5 hours ago