University | University of Wollongong (UOW) |
Subject | CSIT115: Data Management and Security Assignment |
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.
Hire a Professional Essay & Assignment Writer for completing your Academic Assessments
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
Looking for expert assistance with your CSIT115 Data Management and Security Assignment at UOW, Singapore? Assignment Help Singapore provides top-notch Dissertation Writing Services tailored to meet your academic needs. Our team of experts is dedicated to helping Singapore students excel in their courses.
Tags:-
- HFS351 ECA (End-of-Course Assessment) SUSS : July Semester 2024 – Safety Management and Audit
- HFSY217 ECA (End-of-Course Assessment) SUSS : July Semester 2024 – Emergency Preparedness and Response Planning, Singapore
- NSG3EPN Assignment Two instructions rubric – Contemporary nursing practice :Engagement in Professional Nursing, LTU Singapore
- HFS201 GBA (Group-based Assignment) SUSS: July 2024 – Workplace Evaluation and Design
- Business Accounting & Finance – (VM) – A3 Assignment, UOM, Singapore
- HRM3010S: Managing People At Work, Assignment, UCD, Singapore
- HFS351: Safety Management and Audit, End-of-Course Assessment, SUSS, Singapore
- HFSY217: Emergency Preparedness and Response Planning, TMA (Tutor-Marked Assignment), SUSS
- CMM315: TMA (Tutor-Marked Assignment) – Peacebuilding and Security, SUSS, Singapore
- Harley Davidson utilized different entry mode strategies in pursuit of internationalization: Sustainable, Assignment, UoN, Singapore
UP TO 15 % DISCOUNT