1 Problem Domain 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 (eg. 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%). (name Branch_id address iteme-on-sale involves in *Branch Provide Sales-campaign open days open_hours discount End-date Campaign_id) (Start-date Work id On Salary Staff.id Staff (Role Employment_type Membership-id Reporting to Current_stock *Pod K Product_info Type (Expiry-date Product_id Platinum (Gold Silver) item. m-type (Brand Price) Packaging 1 Problem Domain 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 (eg. 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%). (name Branch_id address iteme-on-sale involves in *Branch Provide Sales-campaign open days open_hours discount End-date Campaign_id) (Start-date Work id On Salary Staff.id Staff (Role Employment_type Membership-id Reporting to Current_stock *Pod K Product_info Type (Expiry-date Product_id Platinum (Gold Silver) item. m-type (Brand Price) Packaging