Question

In: Computer Science

1. The functional dependencies for the ProAudio relation: c_id -> f_name, l_name, address, city, state, zip...

1. The functional dependencies for the ProAudio relation:

c_id -> f_name, l_name, address, city, state, zip

item_id -> title, price

ord_no -> c_id, order_date

ord_no  + item_id  -> shipped

zip -> city, state

Original ProAudio relation:

c_id f_name I_name address city state zip ord_no item_id title price order_date shipped
01 Jane Doe 123 Elm St Ely NV 11111 1-1 12-31 More Blues 8.99 12-2-00 no
02 Fred Fish 321 Oak St Ely NV 11111 2-1 21-12 Jazz Songs 9.99 11-9-00 yes
01 Jane Doe 123 Elm St Ely NV 11111 1-2 12-21 The Blues 8.99 12-2-00 yes

determining functional dependencies for ProAudio database

Normalization is a set of rules that ensures the proper design of a database. In theory, the higher the normal form, the stronger the design of the database.

Use the file for Functional Dependencies Above to answer the following questions:

Now that you are familiar with the mission statement and the entities and attributes the for ProAudio:

  1. What are types of anomalies can occur when normalization has not taken place?
  2. Define Anomalies- definitions in your own words
    • Anomalie 1
    • Anomalie 2
    • Anomalie 3

Solutions

Expert Solution

There are three types of anomalies that occur when the database is not normalized.

These are – Insertion, update and deletion anomaly.

Let us define each of these by using the file for Functional Dependencies given in the question:

1) Insertion : In the above table we have two rows for f_name "Jane" as he/she belongs to two ord_no of the ProAudio relation. If we want to update the address of Jane then we have to update the same in two rows or the data will become inconsistent. If somehow, the correct address gets updated in one ord_no but not in other then as per the database, Jane would be having two different addresses, which is not correct and would lead to inconsistent data.

2) Update : Suppose we want to add a new customer to ProAudio database, who's status of shipment is not received by the database management authority from the shipment agency, then we would not be able to insert the data into the table if shipped field doesn’t allow nulls.

3) Deletion : Suppose, if at a point of time the ProAudio agency wants to delete the order_date "11-9-00" then deleting the rows that are having order_date as "11-9-00" would also delete the information of customer "Fred" since he is assigned only to this date.

SUMMARY : If a tuple is inserted in referencing relation and referencing attribute value is not present in referenced attribute, it will not allow inserting in referencing relation. Also, if a tuple is deleted or updated from referenced relation and referenced attribute value is used by referencing attribute in referencing relation, it will not allow deleting the tuple from referenced relation.

venereology answered 23 hours ago
Next > < Previous

Related Solutions

1. The functional dependencies for the ProAudio relation: c_id -> f_name, l_name, address, city, state, zip...
1. The functional dependencies for the ProAudio relation: c_id -> f_name, l_name, address, city, state, zip item_id -> title, price ord_no -> c_id, order_date ord_no  + item_id  -> shipped zip -> city, state Original ProAudio relation: c_id f_name I_name address city state zip ord_no item_id title price order_date shipped 01 Jane Doe 123 Elm St Ely NV 11111 1-1 12-31 More Blues 8.99 12-2-00 no 02 Fred Fish 321 Oak St Ely NV 11111 2-1 21-12 Jazz Songs 9.99 11-9-00 yes 01...
Task 1. For each table on the list, identify the functional dependencies. List the functional dependencies....
Task 1. For each table on the list, identify the functional dependencies. List the functional dependencies. Normalize the relations to BCNF. Then decide whether the resulting tables should be implemented in that form. If not, explain why. For each table, write the table name and write out the names, data types, and sizes of all the data items, Identify any constraints, using the conventions of the DBMS you will use for implementation. Write and execute SQL statements to create all...
Consider a relation R (ABCDEFGH) with the following functional dependencies: ACD --> EF AG --> A...
Consider a relation R (ABCDEFGH) with the following functional dependencies: ACD --> EF AG --> A B --> CFH D --> C DF --> G F --> C F --> D Find minimal cover and identify all possible candidate keys. In order to receive full credit, please list each step taken and the rules that you applied.
Use a JSON data structure that contains student information (StudentId, FName, LName, Address, City, State, Zip,...
Use a JSON data structure that contains student information (StudentId, FName, LName, Address, City, State, Zip, E-mail, Phone, Major, AdvisorName, AdvisorEmail). Using the Foreach statement, display all the contents of the JSON data structure into a webpage. What to turn in: 1. Screenshots: Code, JSON data, Web page showing JSON data in a table - pulled using the Foreach instruction.
For the relation R(A,B,C,D,E) with the following Functional Dependencies: A → B, A → C, BC...
For the relation R(A,B,C,D,E) with the following Functional Dependencies: A → B, A → C, BC → D, AC → E, CE → A, list all non-trivial FDs following from the above.    Generate all possible keys for R. Check whether R is in 3NF. If it is in 3NF, explain the criteria you used. If it is not in 3NF, convert it into 3NF, showing the new relations and their FDs.
Normalization: Answer all 4 questions. You are given the following relation R and some functional dependencies....
Normalization: Answer all 4 questions. You are given the following relation R and some functional dependencies. R(SID, Project, Code, ListOfSupplies, Name, Initials, Abbrev) Project → ListOfSupplies SID → Name Name → Initials Project, Initials → Abbrev SID, Project → Code Code → SID Is R in 1NF? If not, normalize R into a collection of 1NF relations. Is R in 2NF? If not, normalize R (or your collection of 1NF relations) into a collection of 2NF relations. Is R in...
1. Consider the following functional dependencies: Z -> XYD, X -> Y Find the minimal cover...
1. Consider the following functional dependencies: Z -> XYD, X -> Y Find the minimal cover of the above. 2. Consider the following two sets of functional dependencies: F = {A -> C, AC -> D, E -> AD, E -> H} and G = {A -> CD, E -> AH}. Check whether they are equivalent.
Database Normalize the relations to BCNF: Member- (MemID, dateJoined, firstName, lastName, street, city, state, zip, areaCode,...
Database Normalize the relations to BCNF: Member- (MemID, dateJoined, firstName, lastName, street, city, state, zip, areaCode, phoneNumber, currentOfficeHeld) Play- (title, author, numberOfActs, setChanges) Sponsor- (sponID, name, street, city, state, zip, areaCode, phoneNumber) Subscriber- (subID, firstName, lastName, street, city, state, zip, areaCode, phoneNumber) Production- (prodyear, seasonStartDate, seasonEndDate, title) Performance – (perfyear,perfdate, time, seasonStartDate) TicketSale- (saleID, saleDate, totalAmount, perfyear, perfdate,subID) DuesPayment- (MemID, duesYear, amount, datePaid) Donation – (sponID, donationDate, donationType, donationValue, prodYear, seasonStartDate) Ticket- (saleID, seatLocation, price, type) Member - Production-(MemID, prodYear,...
=>Set of functional dependencies(F) = {A -> BC, BC -> AD, D -> E} =>Set of...
=>Set of functional dependencies(F) = {A -> BC, BC -> AD, D -> E} =>Set of functional dependencies(F) = {AB -> C, A -> DE, B -> F, F -> GH, D -> IJ} Decompose the previous R{A, B, C, D, E, F, G, H, I, J} into each higher normal form relations above its current NF. For example, if its current NF is 0NF, then you need to decompose R to 1NF relations, 2NF relations, up to 3NF relations...
Determine the Functional Dependencies that exist in the following Orders table. This table lists customer and...
Determine the Functional Dependencies that exist in the following Orders table. This table lists customer and order data. Orders (SupplierNum, SupplierName, Supp_Phone, ProductNum, Description, Product_type, QuotedPrice) . Normalize the above relation to 3 rd normal form, ensuring that the resulting relations are dependency-preserving and specify the primary keys in the normalized relations by underlining them.
ADVERTISEMENT
Subjects
ADVERTISEMENT
Latest Questions
ADVERTISEMENT