Task 1 Identify the non-trivial FDs on the relation Abnormal_Rel. Then identify the Candidate key(s) of Abnormal_Rel. Task 2 Determine for each update anomaly whether or not the relation Abnormal_Rel is susceptible to that anomaly. Support your determination with adequate explanation and a small example. Task 3 Determine the highest normal form that the relation Abnormal_Rel is in. Then: 1. Normalize/decompose it until you get relations that are in 3NF. Use appropriate illustration to aid the understanding of your work. 2. Check if the resultant relations are in BCNF. If not, decompose them as necessary until you get all of them in BCNF. Task 4 Now (at the end of completing Task 3) you have a set of relation(s) in BCNF, derived from the relation Abnormal_Rel. 1. Create an appropriate table for each of these relations (in BCNF), keeping the key constraints in mind. Copy and paste into your .doc document the SQL code you used for this purpose. Also paste into your Assignment2Code text file the same. 2. Insert five rows of (made-up) data into each table. Make sure that the data you enter in these tables should be sufficient to return at least one row for each query in Task 5. For instance, MA should hold at least 5 bottles of Penfold Grange 2010 in some branch or other. Copy and paste into your .doc document the SQL code you used for this purpose. Also paste into your Assignment2Code text file the same. 3. Display the content of each table using a SELECT query. Copy and paste into your .doc document the result that was displayed. k 5 Write SQL queries to answer the following four questions (Q1-Q4). With respect to each, Copy and paste into your .doc document the SQL query you used for this purpose. Also paste into your Assignment2Code text file the same. Display the result of running this query. Copy and paste into your .doc document the result that was displayed. [Query 1] List the branches (ID) of MA that have in stock at least 5 bottles of Penfold Grange 2010. [Query 2] Simone Singh plans to do some last-minute Christmas shopping on 24/12/2021. List details of each beer that she will be entitled to get 20% discount on. [Query 3] Generate a list of all email addresses of members whose card will expire in the month after the coming month. Thus, for instance, if the query is run in November 2121, it will list the emails of all members whose membership will expire in January 2122. The emails should be ordered by Branch ID, then by expiry date, and then by the email address, all in ascending order. [Query 4] Determine how many times Penfold Grange 2010 has gone on sale since Covid-19 related lockdown started (assume it to be March 01, 2020).
- Assignment status: Already Solved By Our Experts
- (USA, AUS, UK & CA PhD. Writers)
- CLICK HERE TO GET A PROFESSIONAL WRITER TO WORK ON THIS PAPER AND OTHER SIMILAR PAPERS, GET A NON PLAGIARIZED PAPER FROM OUR EXPERTS
