Analysis Analyse and identity from the case scenario: 1. II. III. all key stakeholders (entities) and their attributes. all business rules that define the relationship(s) between them. all business constraints/validation lists that the data model may include. Requirement: You must use ONLY the prescribed set notations in your analysis please refer to the appendix for the guideline and examples. You must include at least eight key stakeholders (goes not include tables of constraints) in your data model. Assuming you have identified most of the three parts listed above correctly but with some errors, you can achieve a pass mark or up to a maximum mark equivalent of a Credit. To earn a Distinction, you must identify two additional key stakeholders and relevant attributes. You must establish relevance for including them. All relevant attributes and relationships must be identified correctly. Any M.M relationships must also be translated and resolved correctly (in both the business rules and shown in your ER-D) To eam a High Distinction, you must include another two key stakeholders and relevant attributes. Again, you must establish relevance for including them. All relevant relationships must also be identified. All M:M relationships must also be resolved correctly. Note: Any deviation from the prescribed convention will result in loss of marks 02. Design Based on your completed analysis (01), design an E-R diagram to represent a data model of the case scenario. Components to consider and include: Entities and attributes Primary keys Foreign keys Cardinalities Consistency and completeness of the diagram using only one of the suggested tools. Recommended took: https://app.diagrams.net (draw.io); alternate tool: Microsoft Visio Requirement: Use Crow's foot notation ONLY. Please refer to the appendix for an example. The guideline will be discussed in class and tutorial sessions. It is important you attend all sessions. Some relevant discussions will be recorded and made available for your reference as well. Note: Any deviation from the prescribed norm will result in loss of marks Any additional entities/attributes/relationships you have identified must be correctly represented in the ER-D to gain the higher marks. Where possible, you want to avoid criss-crossing the line connectors. All line connectors between entities should be correctly attached to the entities, identifying also the common keys shared between them. The criteria weights are as follow: 30 marks | Appropriate table structures are created. 20 marks | Proper use of constraints. 20 marks | Normalised database design (with the intuitive methodology). 20 marks | Clear and concise conventions and notations. Case scenario A new local coffee shop, called Muffin & Java, wants to keep track of its front inventory and customer orders. You and your team were asked to consider the front logistics of the business. Then, design a new data solution that is capable of tracking customer orders made online or directly in the store. MUFFIN & JAVA STORE TCH On the outset, your team can immediately identify a few key entities: Muffin_Types, Coffee Types, Inventory and Customer Sales. Your team will need to identify and fill in the related attributes as well in the analysis stage. For example, Muffin Types (muffin_ID, muffin_name, price) The database needs to be capable of tracking data about the product availability, the staff, the sales, sales items, and other related information. Your team must evaluate and propose at least another four entities to track (not mentioned in the scenario) to ensure the smooth running of the coffee store. Muffin & Java staff comprises of a crew of seven and they are all paid the hourly wage. Some available positions include the manager, barista, baker, kitchen helper, and general crew member. The employee data that should be tracked include staff ID, first name and last name, date of birth, email, contact number, position, and starting date. If any customers want to order via the website or mobile app, they must complete an online registration. The customer information stored should include the name, e-mail, contact number, password, security question, date of registration (default is the current date), account status (default is ACTIVE), and a customer ID, which is uniquely generated and assigned when the account is first created. The customer is also given the option to store payment information for faster checkout. The customer can also opt for paperless invoice for online orders. Whenever a customer completed an order, a sales order is created. When the order is paid, an invoice is generated and issued. If the customer has opted for paperless, the invoice will be emailed to the customer. . 10 marks | Proper use of primary and foreign keys Appendix. A Guide for recording your work for submission Q1. An example on how to approach and prepare the report of your analysis Note: Clearly outline your analysis using set notations only-other format will NOT be accepted in this assignment. Main Stakeholders (Entities) and their related attributes -- Minimally, you should identify 8 entities; use the following template: Entity_name(comma-separated list of attributes) Add {name_of_constraint) to a field to indicate the table of constraint/validation list. Use the naming convention prescribed in class. For example, Movie (mediaID, title, releasedDate, category, rating, copiesAvailable, genre) MovieCopy feataloqueNo, mediaID, formati: tbl format), rentalRate, state, availability) Role (roleID, mediain, actarID, character, description} Actor (actorID, firstName, lastName, dob, country! Business Rules (define the relationships between the entities) -- Minimally, you must identify 7 pairs of business rules: Each pair of business rules describe one relationship Any M:M relationships must be translated into 1:M's for a relational database E.g. Movie: MovieCopy (1:M) A movie has one or more copies A movie copy is associated with one and only one movie E.g. this is how a M:M relationship should be documented Movie:Actor (M:M) A movie is made up of one or many performing actors An actor can also act in one or many movies { } Movie:Role (1:M) A movie has one or more character roles A character role is associated with one movie Actor Role (1:M) An actor can play in one or more roles A role is associated with one actor Business Constraints (limits the types of values) Consider only field constraint - one or more values that limit a field. E.g. tbl Format (SD, HD, 3D, 2K) format description. SD HD 3D 2K Standard definition title High definition 3-Dimensions Q2. An example how to approach and prepare your report of your ER-D E.g. 2xHigh Definitions PK medialD genre Movie releasedDate category rating copiesAvailable PK rollD FK medialD FK actor D Role character description PK FK MovieCopy catalogueNo medialD format rentalRate state availability Actor PK actor D firstName lastName dob country