COMP2350: Database Design & Manipulation - IT Computer Science Assignment Help

Assignment Task:

Task:

1 Problem Context

The context of this Assignment is the same as for Assignment 1, namely The Magic Ale (MA). This has been reproduced as is in the Appendix for your convenience. We pretend that your firm had an agreement with MA to design and implement the Database, and the job fell on your lap. You in turn assigned that task to an intern you are mentoring. The intern has gone through the specification and designed some relational schemas for this purpose. You had a quick look at them, and one of them particularly stood out since it had quite a number of attributes that you suspected should best belong to different relations. The suspect relation schema in question is: Abnormal_Rel( ProductID, BranchID, campaignID, MemberID, ProductType, PackageType, YearProduced, Price, Brand, StockLevel, CampaignStartDate, CampaignEndDate, FirstName, LastName, eMail, MembershipLevel, MemberExpDate, Discount ) Upon discussion with the intern you gathered that different branches can have the same product (ProductID) in different quantities. Also, in different campaigns, the same product may be given different discount rates for the same membership level. You took upon yourself to explain to the intern the issue at hand, and how the issue should be addressed. Complete the following tasks in that context. COMP2350/6350 2021 Assignment 2 3 2 Task Specifications You will submit two files on iLearn:

1. Assignment2.pdf. You will first put all your answers to the tasks below (including copying and pasting any SQL code) into a file Assignment2.doc (carefully following the instructions provided), then generate a .pdf file called Assignment2.pdf.

2. Assignment2Code.sql. [If you already know how to extract an .sql file, you need not follow the relatively convoluted process described below. This description is meant for students who do not know how to generate an executable file of SQL codes.] First open a new file called Assignment2Code.txt employing a text editor such as Notepad or vi. Then copy and paste as text the required SQL codes into it. When completed, save it, exit, and replace the suffix .txt by the suffix .sql.

Task 1 (20 marks) Identify the non-trivial FDs on the relation Abnormal_Rel. Then identify the Candidate key(s) of Abnormal_Rel.

Task 2 (20 marks) 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 (20 marks) 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 (20 marks) 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

The above IT Assignment has been solved by our  IT Assignment  Experts at My Uni Paper. Our Assignment Writing Experts are efficient to provide a fresh solution to this question. We are serving more than 10000+ Students in Australia, UK & US by helping them to score HD in their academics. Our experts are well trained to follow all marking rubrics & referencing style.

Be it a used or new solution, the quality of the work submitted by our assignment experts remains unhampered. You may continue to expect the same or even better quality with the used and new assignment solution files respectively. There’s one thing to be noticed that you could choose one between the two and acquire considered worthy of the highest distinction.