Answer Happy

1. Analyse and identify from the case
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(below image is an
example)
• Alter the employee table to add an emergency
contact name and number for all employees. Then update the sample
data for all existing employee records with their emergency contact
information.
• Update the manager with an increment of 1.25% to
the biweekly wage. Likewise, give the other employees a 1.15%
raise.
• List all computing models of a product brand. This
query must be designed to prompt the user for a product name at
runtime.
• Create a view/query that can be used to print a
list of returning customers. (Hint: customers who have two or more
purchases.) Note: the information must be processed through a
query; not hard-coded into the table(s).
• Create a query of your own, which must involve
using a nested query or a compound statement. You must indicate
what the query is designed for, i.e. what information you are
processing. Higher mark is awarded to the more innovative and
usefulness of the query.
please provide a reasonable description of each query is
intended to achieve. and also the screenshot of each step
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.
An example of the prescribed format of a relational model. Student (studentNo:VARCHAR2(9), surname:VARCHAR2(50), given:VARCHAR2(50), sex:CHAR(1), address:VARCHAR2(50), postcode:CHAR(4), programmeCode:CHAR(7), programmeLoad:CHAR(1)) Programme (programmeCode:CHAR(7), pName:VARCHAR2(30), deptNo:CHAR(5)) Department (deptNo:CHAR(5), deptName:VARCHAR2(50)) Course (courseNo:CHAR(6), cName:VARCHAR2(50), programmeCode:CHAR(7)) Section (sectionNo:CHAR(1), courseNo:CHAR(6), semester:NUMBER(1), year:DATE) Enrolment (enrolNo:VARCHAR2(2), studentNo:VARCHAR2(9), courseNo:CHAR(6), section No:CHAR(1), grade:VARCHAR2(2))
02-7 An example on how to submit your answers Note: Please include a clear screenshot of each executing commands in oracle SQL E.g. To create an employee table: (show a successful execution of each create statement) CREATE TABLE EMPLOYEE Ssn char (9) NOT NULL, Given varchar2 (40) NOT NULL, Surname varchar2 (40) NOT NULL, DOB date, DNO CHAR (1) NOT NULL, CONSTRAINT PK_EMP PRIMARY KEY (Ssn), CONSTRAINT FK_EMP FOREIGN KEY (DNO) REFERENCES Persons (DNO) SQL> CREATE TABLE EMPLOYEE Ssn char(9) NOT NULL, Given varchar(40) NOT NULL, Surname varchar(40) NOT NULL, DOB date, DNO NUMBER (1) NOT NULL 2 3 4 5 6 7); Table created. . Then, print the structure of each table, e.g.: SQL> desc Employee; Name Null? Type SSN GIVEN SURNAME DOB DNO NOT NULL CHAR (9) NOT NULL VARCHAR2 (40) NOT NULL VARCHAR2 (40) DATE NOT NULL NUMBER (1) To insert a record into the Employee table: (show only a sample insertion into each table) INSERT INTO EMPLOYEE (Ssn, Given, Surname, DOB, Dno) VALUES ('123456789', 'John', 'Smith', '09-Jan-1965', '5'); SQL> INSERT INTO EMPLOYEE (Ssn, Given, Surname, DOB, Dno) VALUES ('123456789', 'John', 'Smith', '09-Jan-1965', '5'); 2 1 row created. SQL> INSERT INTO EMPLOYEE (Ssn, Given, Surname, DOB, Dno) VALUES ('123456788', 'Russul', 'Alanni', '11-Nov-1980', '1'); 2 i row created. Then print the table to show that the new records have been added: (show each completed table) SQL> SELECT * FROM EMPLOYEE; SSN GIVEN SURNAME DOB DNO 123456789 John 123456789 Russul Smith Alanni Smith 09-JAN-65 18-NOV-80 5 1 122411700 SS ng TCC