Entity Relationship modeling and Relational database

Task 1: Designing an Entity-Relationship Model Food Saver Case Study Food Saver (FS) is a supermarket that sells a variety of food products in Australia. The following are the requirements for managing data about staff, products and customers for FS. Stores FS has stores throughout Australia. Each store is allocated several staff members. One staff member manages the operations of the store. Each staff member works at a specific store only. The data describing a store includes a unique store number, an address (i.e., street, suburb, and postcode) and several contact types (e.g., email, fax, phone). FS keeps track of which staff members work at each store, including the manager (and the date each staff member starts his/her position at the store he/she is working at). Staff It is important to distinguish between two types of FA staff members (i.e., supervisors and workers) as supervisors have additional responsibilities. Supervisors are responsible for the day-to-day activities of a team of workers. Each supervisor is responsible for all staff at a particular store. The data for each staff member includes a unique staff number, name, address, position, and salary. Products FS has a range of products that it sells to customers in Australia. At any given time at a store, a particular product has a specific quantity in stock which must be tracked. Other data for each product includes a unique product number, a name, description and sale price. Customers When customers register on the FS website, the system records their unique email address, their name, delivery address (i.e., street, suburb, and postcode) and payment details (Credit Card number, expiry date and security code). Orders Once registered, customers can order products from the website, selecting the product and quantity to be added to their order. Each order has a unique order number and order date. Based on the given description, model the given business rules, and present your model as an Entity-Relationship (ER) diagram. Carefully state any assumptions that you make. In your ER diagram, you must properly denote all applicable concepts, including weak or strong entities, keys, composite or multi-valued attributes, relationships and their cardinality and participation constraints. If you cannot represent any of this information in the ER model, clearly explain what limitations in the ER model restrict you from representing your model. You must use UML notation and the diagramming tool Lucidchart to draw your diagram. Your diagram must be drawn to a high standard with minimal clutter. You are not required to map the ER model to relational model. A special note: This is an open-ended question with many different models that can be derived. Your model is assessed based on how accurately it represents business rules described above. Task 2: Designing an Entity-Relationship Model Part A: Initial Design Sunny Holidays Case Study Sunny Holidays is an Australian travel company that allows customers to book holiday packages. You are asked to design a database for managing customer bookings. Requirements for the database are as follows: • Customers book holiday packages recording the date the booking was made. Each customer has a firstname, lastname, date of birth, and passport number. • Holiday packages have a package no, and have a set fee (depending on the departure date). They consist of several stop overs, each in a city within a country. Each country could have several stop over cities. • Each stop over has a sequence number (eg stop number 1, 2 etc). • The system stores a complete list of cities and countries, some which may not necessarily have an associated holiday stop over (yet). • All holiday packages have at least one stopover. Based on the given description, model the business rules of Sunny Holidays, and present your model as an Entity- Relationship (ER) diagram. Carefully state any assumptions that you make. In your ER diagram, you must properly denote all applicable concepts, including weak or strong entities, keys, composite or multi-valued attributes, relationships and their cardinality and participation constraints. If you cannot represent any of this information in the ER model, clearly explain what limitations in the ER model restrict you from representing your model. You must use UML notation and the diagramming tool Lucidchart to draw your diagram. Your diagram must be drawn to a high standard with minimal clutter. You are not required to map the ER model to relational model. A special note: This is an open-ended question with many different models that can be derived. Your model is assessed based on how accurately it represents business rules described above. Part B: Client Adjustments After presenting your ER model to Sunny Holidays management, you are asked if it can be used to perform the following additional tasks. • Customers arrive at and depart from each stop over on a flight at a specific datetime. • Flights have a unique code and also a duration. • Depending on the duration of the stop over, there may be a single nominated accommodation. • Accommodation has an identifying address and has a phone number. • The check in and check out datetime is recorded for each accommodation stay. For each one of the tasks specified above, explain how your ER diagram is supporting it. If it is not possible to achieve any of the tasks above given your current design, state why, modify the model, provide the modified ER diagram