- Case Scenario A Local Store Reese Comp Which Specialises In Selling Computing Products And Services Has Been Operatin 1 (110.94 KiB) Viewed 19 times
scenario:
I.all key stakeholders(entities) and their attributes;
II.all business rules that define the relationship(s) between
them;
III.all business constraints that the data model must abide by
in the daily business operations.
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 field
constraint.
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
Business Constraints (limits the types of
values)
Consider only field constraint-one or more values that limit a
field
2. Based on your completed analysis(Q1), 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 and cardinalities
•Consistency and completeness of the diagram using only one of
the suggested tool.
3. Creating the implementation view • Map
your E-R model (Q2) into a Relational model.
(Requirement: You must determine and specify using relevant
Oracle native data types of each attribute at this point.)
4. Implement ALL the main tables and populate each with
sample data in Oracle SSID
Recommended: 7 or more records per table
Case scenario A local store, Reese Comp, which specialises in selling computing products and services, has been operating in several Melbourne suburbs for the last fifteen years. Two years ago, due to the global pandemic and lockdowns that ensued, it decided to move its business online. Reese Comp had changed its name to RComp. As things began to settle into the new year, RComp decided to re-open two physical stores in Richmond and Camberwell whilst continuing with its online presence. You and your team were asked to analyse the backend logistics; and then, design a new data model to facilitate the frontend logistics of RComp's new hybrid business model. The database should store data about their stores, products, employees, order and shipping information. Your team should also evaluate and propose any additional data to track (not mentioned in the scenario) to ensure the smooth operations of RComp business. Whilst RComp continues with its online presence, it must also maintain some information about its physical stores. Due to the hybrid business model, RComp is well positioned to double its current full-time employees of five and they are all paid a biweekly wage. The wage is based on the employee class: managerial, technical, services marketing or sales. Two employees work in the backend to provide technical support whilst the remaining staff members, including the manager, who work in the frontend to support the logistics of customer service and care, process and/or ship customer orders, and maintain a product inventory. The employee data that should be kept comprise, but are not limited to: employee ID, first name and last name, email, contact number, job title, and hire date. RComp maintains a modest collection of over 17,000 computing products in its warehouse and stores. Initially, your team may need to research online to determine the relevant information needed to track a computing product, e.g. the product brand, model, classification, colour, form factor, speed, capacity, supplier, the cost and list prices, description, product ratings (from customers), customer reviews, and the number of items available, just to name a few. A customer who shops with RComp can complete a registration online. The customer information stored should include the name, mailing address, e-mail, contact number, date of registration (default is the current date), date of closing the account (default is NULL). Online customers must register their username, password, security question. The customer ID is automatically generated. The customers are given the option to store or not store payment information. Whenever a customer requests a purchase order, a sales order is added to the database with a pending status. When RComp processes and ships the order, the order status is changed to shipped. If a customer cancels an order, the order status is changed to cancelled. If a product is purchased directly from the store, a status of paid-in-store is logged.