Hi there, can you please give me a cote?
A3createDB.sql /* ********************************************************************* Name: MySQL Magic Ale Database ********************************************************************* */ /* CREATE SCHEMA if not exists magicale; USE magicale; */ DROP TABLE IF EXISTS DiscountDetails,Branch,Product,Campaign,Membership; create table Product ( ProductID int not null, ProductType varchar (20) not null, PackageType varchar(20) not null, YearProduced int not null, Price float not null, Brand varchar(255) not null, PRIMARY KEY (ProductID) ) engine InnoDB default charset=latin1; create table Campaign ( CampaignID int not null, CampaignStartDate date not null, CampaignEndDate date, PRIMARY KEY (CampaignID) ) engine InnoDB default charset=latin1; create table Membership ( MembershipID int not null, FirstName varchar(20) not null, LastName varchar(20) not null, eMail varchar(255), MembershipLevel varchar(20) not null, MemberExpDate date, PRIMARY KEY (MembershipID) ) engine InnoDB default charset=latin1; create table DiscountDetails ( ProductID int not null, CampaignID int not null, MembershipLevel varchar(20) not null, Discount int not null, primary key (ProductID, CampaignID, MembershipLevel), foreign key (ProductID) references Product (ProductID), foreign key (CampaignID) references Campaign (CampaignID) ) engine InnoDB default charset=latin1; create table Branch ( BranchID varchar(45) not null, ProductID int not null, StockLevel int not null, primary key (BranchID, ProductID), foreign key (ProductID) references Product (ProductID) ) engine InnoDB default charset=latin1; A3populateDB.sql /* ********************************************************************* Name: MySQL Magic Ale Database ********************************************************************* */ insert into `Product`(`ProductID`,`ProductType`,`PackageType`,`YearProduced`,`Price`, `Brand`) values (1,'Wine', 'Bottle', '1990', 1000, 'Penfold'), (2,'Wine','Bottle', 2000, 400, 'Penfold'), (3,'Wine','Bottle', 2010, 200, 'Penfold'), (4,'Wine','Bottle', 2020, 50, 'Penfold'), (5,'Beer','Can', 2020, 10, 'Blonde'); insert into `Campaign`(`CampaignID`,`CampaignStartDate`,`CampaignEndDate`) values (1,'2021-01-02', '2021-01-30'), (2,'2021-02-02', '2021-02-28'), (3,'2021-03-02', '2021-03-30'), (4,'2021-08-02', '2021-08-30'), (5,'2021-09-02', '2021-09-30'); insert into `Membership`(`MembershipID`,`FirstName`,`LastName`, `eMail`, `MembershipLevel`, `MemberExpDate`) values (1, 'John', 'Smith', '
[email protected]','Gold', '2021-12-31'), (2, 'Peter', 'Cole', '
[email protected]','Silver', '2021-10-15'), (3, 'Sarah', 'Goodman', '
[email protected]','Platinum', '2021-10-31'), (4, 'Christ', 'Gordon', '
[email protected]','Gold', '2021-11-28'), (5, 'Emma', 'Spring', '
[email protected]','Silver', '2021-12-31'); insert into `DiscountDetails`(`ProductID`,`CampaignID`,`MembershipLevel`, `Discount`) values (1, 1, 'Gold', 21), (1, 1, 'Platinum', 32), (2, 1, 'Gold', 13), (2, 2, 'Silver', 50), (3, 3, 'Platinum', 5); insert into `Branch`(`BranchID`,`ProductID`,`StockLevel`) values ('MA', 1, 1000), ('AB', 2, 10000), ('CD', 3, 10000), ('EF', 5, 100000), ('HH', 4, 20000); select * from DiscountDetails; select * from Membership; COMP2350_2021_a3.pdf COMP2350/COMP6350 2021-Assignment 3 1 DEPARTMENT OF COMPUTING COMP2350/6350 2021 S2 – ASSIGNMENT THREE (10%) Draft Due: Tuesday, October 26, 2021, 11:55 PM (Week 12) Final Submisisons Due: Friday, October 29, 2021, 11:55 PM (Week 12) Database Programming and Implementation Please Print Clearly In CAPITALS Surname First Name Student ID Signature Student Code of Conduct Macquarie University students have a responsibility to be familiar with the Student Code of Conduct: https://policies.mq.edu.au/document/view.php?id=233 Student Support Macquarie University provides a range of support services for students. For details, visit http://students.mq.edu.au/support/ COMP2350/COMP6350 2021-Assignment 3 2 The background knowledge for the assignments is given in the textbook(s), lectures, any other components of the unit, in the prerequisite units ISYS114 or COMP1350, 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 programming and implementation. It will be marked out of 100 and will contribute 10% towards your final grade. It consists of developing procedures and triggers in MySQL, creating and populating the database tables, and running test scripts against the tables. The description of the Problem domain is given below. 1 Problem Domain The context of this Assignment is the same as for Assignment 2, namely the Magic Ale (MA). This has been reproduced as is in the Appendix for your convenience. A DDL script (A3createDB.sql) for creating the corresponding database, and a DML script (A3populateDB.sql) for populating this database with some sample data are being provided in the Assignment 3 folder. 2 Task Specifications Task 1 (10 marks) Create the tables in the Magic Ale database by running the DDL script provided in the 'Assignment 3' folder. Then insert some sample records into the tables by running the provided DML script. Verify that the tables are created and populated as intended. Task 2 (30 marks) The membership records in the Membership table can be updated using an UPDATE statement. Such a statement can update any non-PK column value including the membership levels, but the Magic Ale has certain rules about membership level upgrades: Only those members with a non-expired membership can receive an upgrade. Only the SILVER members can be upgraded to the GOLD level. Only the GOLD members can be upgraded to the PLATINUM level. There is no further upgrade for the PLATINUM members. You will write a BEFORE UPDATE trigger called CHECK_MEMBERSHIP_UPDATE which fires when a record is attempted to be updated in the Membership table. The trigger has to check the conditions above to make sure that they are satisfied by the update request. If the above conditions are satisfied, then the UPDATE statement is allowed to proceed. Otherwise, a meaningful message needs to be displayed to the user. Note that a membership level can also be downgraded in a similar fashion but you are not responsible for checking the downgrading rules. COMP2350/COMP6350 2021-Assignment 3 3 Task 3 (30 marks) In this task, you will write a procedure called BrandNameCampaign which takes a brand name as input and creates a new campaign with the top 5 most expensive products with that brand name. The campaign will have a 4 week duration and will start after exactly two weeks of its creation. For the campaign, the SILVER level members will receive a 10% discount, the GOLD level members 20% and the PLATINUM level members 30%. If there are five or fewer products with that brand name, all those products will be included in the campaign. Task 4 (30 marks) This task involves testing the code developed in Tasks 2 & 3. Part (a) (10 marks) First you are required to test the programs you wrote against the sample data provided as part of Task 1 to see if they work. These data constitute a minimal test against a very small number of records and are unlikely to demonstrate the full functionality of your programs. Part (b) (20 marks) Next you carry out a more extensive test by testing the programs against a larger set of records that are designed by you to easily expose any flaws in your programs. You do that by deleting records, adding records, or modifying the records in other ways, and then calling different procedures and/or firing the trigger. 3 Report Specification You will also prepare and submit a report (in the PDF format). A word file template for this purpose will be provided which you will complete, convert to pdf, and submit. The file you submit will be named:
_report.pdf. Your report should have the following sections: 1. The initial State of the database as created in Task 1: Paste to the word file the screen shots showing the provided sample data in the tables. Do not change any of the table or column names given in the provided DDL script. 2. Stored Programs: Paste into this section the programs you wrote (the contents of the SQL file _programs.sql that you prepared for Tasks 2 & 3). 3. Required Testing against the sample dataset as required in Task 4 Part (a): Paste into this section your SQL statements for the initial tests you ran (one by one) and then the corresponding results as screenshots. Also place your SQL statements into a file called_testscript.sql 4. More Extensive Testing as required in Task 4 Part (b): Explain what sort of changes you are going to make to which tables, what tests you are going to run, and why. Paste into this section your SQL statements for the extensive tests you ran (one by one) and then the corresponding results as screenshots. Also place all of your SQL statements into _testscript.sql. 5. Notes (optional): In this section, you might wish to note anything, such as whether you faced any particular difficulty in completing any of these tasks, the nature and extent of any help you received from anyone, and why. Remember to convert the report Word file to pdf and submit only the pdf file. COMP2350/COMP6350 2021-Assignment 3 4 4 Your Submission You will submit three files: 1. _report.pdf. 2. _programs.sql 3. _testscript.sql. You will submit the files in two stages. In the first stage, as a minimum, you must submit the following two draft files by Tuesday, October 26, 2021, 11:55 PM (Week 12): a) _programs.sql including either the trigger CHECK_MEMBERSHIP_UPDATE or the procedure BrandNameCampaign in it, and b) _report.pdf, with complete Section (1) and partially complete sections (2) and (3). You can modify these files while preparing your final version. The final version of these three files must be submitted by Friday, October 29, 2021, 11:55 PM. Note Regarding Draft Submission. You are strongly suggested to submit a draft of your work by the “Draft Submission Due Date”. Students who have not submitted a draft will not qualify for special consideration should they not be able to submit by the deadline due to technical issues such as failure to connect to the Database Server. Late Submission Policy. No extensions on assignments will be granted without an approved application for Special Consideration. Late submissions will be accepted up to three days after the deadline, but there will be a deduction of 10% mark for each day (whole or part) of delay, unless you have received special consideration. If