COMP2350 Database Programming And Implementation- Develop a Database System

COMP2350 Database Programming And Implementation- Develop a Database System

COMP2350 Database Programming and Implementation

Task

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%. 

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.

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. _report.pdf.

2. _programs.sql 

3. _testscript.sql. 

You will submit the files in two stages. In the first stage, you must submit 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 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. 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%)

COMP2350 Database Programming and Implementation

Answer in Detail


Solved by qualified expert

Get Access to This Answer

Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Hac habitasse platea dictumst vestibulum rhoncus est pellentesque. Amet dictum sit amet justo donec enim diam vulputate ut. Neque convallis a cras semper auctor neque vitae. Elit at imperdiet dui accumsan. Nisl condimentum id venenatis a condimentum vitae sapien pellentesque. Imperdiet massa tincidunt nunc pulvinar sapien et ligula. Malesuada fames ac turpis egestas maecenas pharetra convallis posuere. Et ultrices neque ornare aenean euismod. Suscipit tellus mauris a diam maecenas sed enim. Potenti nullam ac tortor vitae purus faucibus ornare. Morbi tristique senectus et netus et malesuada. Morbi tristique senectus et netus et malesuada. Tellus pellentesque eu tincidunt tortor aliquam. Sit amet purus gravida quis blandit. Nec feugiat in fermentum posuere urna. Vel orci porta non pulvinar neque laoreet suspendisse interdum. Ultricies tristique nulla aliquet enim tortor at auctor urna. Orci sagittis eu volutpat odio facilisis mauris sit amet.

Tellus molestie nunc non blandit massa enim nec dui. Tellus molestie nunc non blandit massa enim nec dui. Ac tortor vitae purus faucibus ornare suspendisse sed nisi. Pharetra et ultrices neque ornare aenean euismod. Pretium viverra suspendisse potenti nullam ac tortor vitae. Morbi quis commodo odio aenean sed. At consectetur lorem donec massa sapien faucibus et. Nisi quis eleifend quam adipiscing vitae proin sagittis nisl rhoncus. Duis at tellus at urna condimentum mattis pellentesque. Vivamus at augue eget arcu dictum varius duis at. Justo donec enim diam vulputate ut. Blandit libero volutpat sed cras ornare arcu. Ac felis donec et odio pellentesque diam volutpat commodo. Convallis a cras semper auctor neque. Tempus iaculis urna id volutpat lacus. Tortor consequat id porta nibh.

Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Hac habitasse platea dictumst vestibulum rhoncus est pellentesque. Amet dictum sit amet justo donec enim diam vulputate ut. Neque convallis a cras semper auctor neque vitae. Elit at imperdiet dui accumsan. Nisl condimentum id venenatis a condimentum vitae sapien pellentesque. Imperdiet massa tincidunt nunc pulvinar sapien et ligula. Malesuada fames ac turpis egestas maecenas pharetra convallis posuere. Et ultrices neque ornare aenean euismod. Suscipit tellus mauris a diam maecenas sed enim. Potenti nullam ac tortor vitae purus faucibus ornare. Morbi tristique senectus et netus et malesuada. Morbi tristique senectus et netus et malesuada. Tellus pellentesque eu tincidunt tortor aliquam. Sit amet purus gravida quis blandit. Nec feugiat in fermentum posuere urna. Vel orci porta non pulvinar neque laoreet suspendisse interdum. Ultricies tristique nulla aliquet enim tortor at auctor urna. Orci sagittis eu volutpat odio facilisis mauris sit amet.

Tellus molestie nunc non blandit massa enim nec dui. Tellus molestie nunc non blandit massa enim nec dui. Ac tortor vitae purus faucibus ornare suspendisse sed nisi. Pharetra et ultrices neque ornare aenean euismod. Pretium viverra suspendisse potenti nullam ac tortor vitae. Morbi quis commodo odio aenean sed. At consectetur lorem donec massa sapien faucibus et. Nisi quis eleifend quam adipiscing vitae proin sagittis nisl rhoncus. Duis at tellus at urna condimentum mattis pellentesque. Vivamus at augue eget arcu dictum varius duis at. Justo donec enim diam vulputate ut. Blandit libero volutpat sed cras ornare arcu. Ac felis donec et odio pellentesque diam volutpat commodo. Convallis a cras semper auctor neque. Tempus iaculis urna id volutpat lacus. Tortor consequat id porta nibh.

Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Hac habitasse platea dictumst vestibulum rhoncus est pellentesque. Amet dictum sit amet justo donec enim diam vulputate ut. Neque convallis a cras semper auctor neque vitae. Elit at imperdiet dui accumsan. Nisl condimentum id venenatis a condimentum vitae sapien pellentesque. Imperdiet massa tincidunt nunc pulvinar sapien et ligula. Malesuada fames ac turpis egestas maecenas pharetra convallis posuere. Et ultrices neque ornare aenean euismod. Suscipit tellus mauris a diam maecenas sed enim. Potenti nullam ac tortor vitae purus faucibus ornare. Morbi tristique senectus et netus et malesuada. Morbi tristique senectus et netus et malesuada. Tellus pellentesque eu tincidunt tortor aliquam. Sit amet purus gravida quis blandit. Nec feugiat in fermentum posuere urna. Vel orci porta non pulvinar neque laoreet suspendisse interdum. Ultricies tristique nulla aliquet enim tortor at auctor urna. Orci sagittis eu volutpat odio facilisis mauris sit amet.

Tellus molestie nunc non blandit massa enim nec dui. Tellus molestie nunc non blandit massa enim nec dui. Ac tortor vitae purus faucibus ornare suspendisse sed nisi. Pharetra et ultrices neque ornare aenean euismod. Pretium viverra suspendisse potenti nullam ac tortor vitae. Morbi quis commodo odio aenean sed. At consectetur lorem donec massa sapien faucibus et. Nisi quis eleifend quam adipiscing vitae proin sagittis nisl rhoncus. Duis at tellus at urna condimentum mattis pellentesque. Vivamus at augue eget arcu dictum varius duis at. Justo donec enim diam vulputate ut. Blandit libero volutpat sed cras ornare arcu. Ac felis donec et odio pellentesque diam volutpat commodo. Convallis a cras semper auctor neque. Tempus iaculis urna id volutpat lacus. Tortor consequat id porta nibh.

MyAssignmenthelp.com is the only platform in Australia that offers dissertation writing help at the cheapest rates. Part of our services also includes the provision of dissertation methodology help. Students who search the internet with phrases such as

QUALITY: 100% ORIGINAL PAPER – NO PLAGIARISM – CUSTOM PAPER