UP TO 15 % DISCOUNT

Get Your Assignment Completed At Lower Prices

Plagiarism Free Solutions
100% Original Work
24*7 Online Assistance
Native PhD Experts
Hire a Writer Now
CSIT115: Data Management and Security Assignment, UOW, Singapore A departmental store issues in-store-credit-card to facilitate and encourage customers to make more purchases in the store
University University of Wollongong (UOW)
Subject CSIT115: Data Management and Security Assignment
Posted on: 24th May 2023

CSIT115: Data Management and Security Assignment, UOW, Singapore A departmental store issues in-store-credit-card to facilitate and encourage customers to make more purchases in the store

Task 1

Background

A departmental store issues in-store-credit-card to facilitate and encourage customers to make more purchases in the store. The in-store-credit-card is available to public members who wish to apply. Card holder is given a credit limit (based on card holder’s income) for making purchases in the store. At the end of the month, card holder will be billed for the amount spent. Premium
card holder can earn bonus points when making purchases, and the bonus points can be used to redeem merchandise.

Requirements

A) Transform the conceptual schema to a set of relational schemas. You are not allowed to add or modify attributes in the conceptual schema.

B) Based on the relational schemas, provide SQL statements to create the tables. You must choose an appropriate data type and length for each column. You must also include appropriate check constraints and foreign key constraints to ensure data integrity

C) To prepare for possible changes in the near future, the store owner requests for a set of ALTER Table statements to alter the table structure without dropping and re-creating the tables. The changes are stated below.

– When a credit card (premium credit card) is deleted, the related “child records” will be retained and the foreign key values will be changed to NULL.

– Add a column called discount to the Card table to indicate the discount given to the card owner on each purchase. The default value is 0, and the valid values are between 0 and 30%.

– Add a column called payment_type to the Bill table. The possible values are: ‘Full’, ‘Deferred’ or ‘Installment’. The column cannot accept NULL value.

Task 2

Background

A major multi-national corporation has employed you to research and implement SQL statements to support their data management operations. The company consists of several departments located in the cities all over the world. The company’s database also contains information about the present and past jobs of its employees and about the present managerial structure. A
conceptual schema of the database is given below

Please use the files: A2dbcreate.sql, A2dbload.sql and A2dbdrop.sql to create the tables and load the records. You should study the statements in the script files as they hold important clues to guide you in your subsequent

implementations of SQL statements.

Requirement

For each of the queries (A, B, C) below, use a single SELECT statement (join, subquery, etc.) to retrieve the records. You must not create temporary (intermediate) table or view. Use limit 5 to show the first 5 rows in your answers.

A) Retrieve employees who belong to departments located in China or India, and have worked for 20 years or more. For each record, display the following columns:
– employee_id
– first_name
– last_name
– job_title
– hire_date
– number of years worked.

B) Retrieve the job history of each employee which includes the current job title and job titles held in the past by the employee. For each record, display the following columns in descending order of start_date (i.e. current job title first);
– employee_id
– last_name
– job_title
– email
– start_date of the job_title

C) Retrieve employees who are supervisors and supervising 8 or more employees. For each record, display the following columns:
– employee_id
– first_name
– last_name
– hire_date
– salary
– number of employees supervised

For each of the scenarios (D, E) below, provide a sequence of INSERT, UPDATE, DELETE statements to make the changes. You are not allowed to drop, re-create or alter (disable/enable) consistency constraints during the modifications.

D) A supervisor (employee_id 114) has resigned from the company, and today is his last day of work. With immediate effect, the department he manages, and all those employees he supervises will be taken over by his supervisor (i.e. employee 114’s ‘boss’ will now supervise all those employees that used to work under him). Provide the SQL statements to effect the “transfer of supervisor responsibilities” described above, as well as removing all information about this employee from the database.

E) A new employee Alex Fong has been hired on 10/10/2022 to manage the new Training department. Alex reports to the president: Steven King (100). Below are the detailed records. Provide the SQL statements to add the new records.

Buy Custom Answer of This Assessment & Raise Your Grades

Get Help By Expert

Attention students at the University of Wollongong (UOW) in Singapore! Need assistance with your CSIT115: Data Management and Security assignments? Look no further! Our expert team is here to provide you with comprehensive support. We offer a range of services tailored to meet your needs, including Formative assessments, solutions for summative assignments, Online Exam Help, and the convenience of paying for assignments online. If you're facing challenges with the CSIT115: Data Management and Security Assignment on a departmental store issuing in-store credit cards, we have the expertise to help. Don't let the pressure get to you; instead, pay our experts to take the load off your shoulders. Trust us to deliver top-quality assistance and guidance, allowing you to excel in your course. Join the ranks of satisfied Singapore students who have benefited from our reliable services.
 

Categories:-
Tags:- CSIT115 UOW MANAGEMENT ASSIGNMENT
Answer
Recent Solved Questions
No Need To Pay Extra
  • Turnitin Report

    $10.00
  • Proofreading and Editing

    $9.00
    Per Page
  • Consultation with Expert

    $35.00
    Per Hour
  • Live Session 1-on-1

    $40.00
    Per 30 min.
  • Quality Check

    $25.00
  • Total
    Free

New Special Offer

Get 30% Off

UP TO 15 % DISCOUNT

Get Your Assignment Completed At Lower Prices

Plagiarism Free Solutions
100% Original Work
24*7 Online Assistance
Native PhD Experts
Hire a Writer Now
My Assignment Help SG Services
My Assignment Help SG

Rated 4.9/5 Based on 22945 Singaporean Students