Please answer Tasks 1 and 2 please!
COMP2350/6350 2021 Assignment 2 Background knowledge for this assignment is given in the textbook(s), lectures, any other components of the unit, in the prerequisite units COMP1350 (or ISYS114 ), and in the readings provided on ilearn. However, some parts of the assignments may not be answered without prior independent research and/or searching for other sources of information. This assignment concerns database Database Design and Manipulation. It will be marked out of 100 and will contribute 10% towards your final grade. It consists of normalising some given relation schemas to an appropriate level, implementing those resultant relations employing MySQL, and carrying out some simple data manipulation using those relations. 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 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 .pdffile 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 Assignment2 Code.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
