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/
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.
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: yourLastname_yourFirstname _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 yourLastname_yourFirstname _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 yourLastname_yourFirstname _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 yourLastname_yourFirstname _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.
4 Your Submission
You will submit three files:
1. yourLastname_yourFirstname _report.pdf.
2. yourLastname_yourFirstname _programs.sql
3. yourLastname_yourFirstname _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) yourLastname_yourFirstname _programs.sql including either the trigger
CHECK_MEMBERSHIP_UPDATE or the procedure BrandNameCampaign in it, and b) yourLastname_yourFirstname _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 special permission is granted for a delay of more than three days, the student’s mark for this assignment will be calculated based on their overall performance in the Final Exam. Please see the Unit Guide for details.
Appendix: Problem Context from Assignments 1 & 2
This assignment concerns a liquor shop chain in Sydney, called The Magic Ale (MA). The objective of this assignment is to develop a database system that will be used to centrally store and manage all relevant information for the branches of MA.
The information to be stored include information on different branches of MA (Bankstown,
Hornsby, etc.), types of drinks they sell (beers, wines, cedars, etc.), staff they employ (Retail
Assistants, Shelving Assistants, etc.), Magic Members (MA Loyalty Card holders), and Sales Campaigns (discounts on specific products over a limited period). The basic requirements gathered from the stake holders is presented in the following five points. As typically the case, these requirements are often underspecified. Use your judgment in interpreting them when required, and keep a note of the assumptions you made.
1. Branch Information: The MA System shall keep information on each branch including its name and address, and the number of employees who work there. The system shall also contain information on which days (Mon-Sun) the branch is open, and opening hours. It will also keep information on opening hours (e.g., Mon-Fri 10:00AM-5:30PM; Sat 9:00AM-9:00PM; Sun Closed).
2. Product Information: The system shall contain relevant information on products of different types at the “item level”, such as: type (wine/beer/spirit/…), packaging info (can/bottle/…), volume (e.g., 375ml X 6 pack), price, and brand (e.g. Tooheys Old Dark Ale), as well as current stock level.
3. Staff Information: The system shall record information on staff members who work at different branches of MA. This will include their roles, type of employment (e.g. permanent, casual), salary (annual or hourly depending on permanent or casual), as well as who they report to.
4. Membership Information: The system shall record information on magic members, including type of membership (Platinum/Gold/Silver), and when the membership will expire. 5. Sales Campaign Information. The system shall keep information on sales campaign. Assume that these campaigns are global (same across all branches of MA). It will have information of the form: campaign start date and campaign end date, what items are on sale, and the discount for customers based on their membership (e.g., nonmembers 10%, Silver 15%, and Platinum/Gold 20%)
5. Sales Campaign Information: The system shall keep information on sales campaign. Assume that these campaigns are global (same across all branches of MA). It will have information of the form: campaign start date and campaign end date, what items are on sale, and the discount for customers based on their membership (e.g., nonmembers 10%, Silver 15%, and Platinum/Gold 20%)