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.