all these problem is based on these following:
Financial Company
The financial company ABC provides tax and audit services to its clients. One group of the companys employees is in performing tax services, and the other is performing audit services. Each group provides corresponding services, supports data about them, and produces various reports on them. The database of the company contains data about services (including service type: tax or audit), employees, including the type of service the employee provides, and the companys activities, including service provided, the client served, the date of the service, and the amount charged for the service.
The company has two database servers. The logical model of the database is the following:
Service (serviceCode, description, serviceType)
Employee (employeeID, name, serviceType)
Client (clientID, name, address)
Activity (clientID, employeeID, serviceCode, serviceDate,
amountCharged)
Employees of each group are allowed to read the data about services provided by their group only. Each of employees supports data about his or her activities and is not allowed to see or modify data about activities provided by other employees. In addition, each employee produces quarterly reports about clients he/she served during the quarter.
Several users of the database are in charge of support of data about clients and services; they can insert, delete, and update data about clients. These employees are also maintaining the changes when an employee is assigned to perform another service type, e.g. an employee who has been performing tax services is assigned to perform audit services.
The companys managers produce monthly reports about activities of the employees, including the number of services the employee provided during the month and the total amount charged for the services. Managers can read any data; however, they cannot modify data.
from 1 to 3 what ever you can do ?
CST3604. Project "Financial Company" 1. For the project case define your distributed solution: a. Design fragmentation of the tables (for the implementation consider two offices only: the fourth and any other) -25 points b. Suggest allocation of fragments and tables (with possible replication) - 5 points C. Provide CREATE TABLE statements (physical design, constraints suitable for your distributed solution) - 30 points d. Implement the databases (each student has to have his/her implementation) - 10 points (will be credited) e. Create a transparency tier for your distributed solution - 15 points f. If needed, implement integrity support that is not covered by the CREATE TABLE statement; explain your solution - 15 points 2. For your solution of the first task: a. Fix the problems of the first task and resubmit(together with the initial submission) - the final grade for the first assignment will be calculated as 50% of the first submission and 50% of the second submission b. Leverage transparency solution of the first task and create additional views/procedures that will support security locally and globally, in particular for the tasks performed by the users of the database - 50 points C. Create roles/users and grant the required privileges (on paper only) - 50 points 3. For your distributed solution: a. In addition to queries for the tasks mentioned in the project description, write queries for the following additional common requests -- 50 points . Retrieve data about a particular client given: 1) the client's ID or 2) name or 3) name and address. . Retrieve the type of service and the number of times a specific client X was serviced for a given week Y (for both databases). . Retrieve the ID and name of clients, and the code and description of three most recent services the client received in one of the databases). . Retrieve the ID and name of clients who received the service X and have not received the service Y (in one of the databases) within a particular month. b. Suggest the means to improve performance of these queries on your solution - 30 points C. Suggest transactions (and maybe some additions to the design) for managing the situation when an employee is transferred to another service type. Note that your approach should allow to keep the full history of the employee's work - 20 points. 4. Manage your database: write queries for different requests can in-class part of the project) CST3604. Project "Financial Company" 1. For the project case define your distributed solution: a. Design fragmentation of the tables (for the implementation consider two offices only: the fourth and any other) -25 points b. Suggest allocation of fragments and tables (with possible replication) - 5 points C. Provide CREATE TABLE statements (physical design, constraints suitable for your distributed solution) - 30 points d. Implement the databases (each student has to have his/her implementation) - 10 points (will be credited) e. Create a transparency tier for your distributed solution - 15 points f. If needed, implement integrity support that is not covered by the CREATE TABLE statement; explain your solution - 15 points 2. For your solution of the first task: a. Fix the problems of the first task and resubmit(together with the initial submission) - the final grade for the first assignment will be calculated as 50% of the first submission and 50% of the second submission b. Leverage transparency solution of the first task and create additional views/procedures that will support security locally and globally, in particular for the tasks performed by the users of the database - 50 points C. Create roles/users and grant the required privileges (on paper only) - 50 points 3. For your distributed solution: a. In addition to queries for the tasks mentioned in the project description, write queries for the following additional common requests -- 50 points . Retrieve data about a particular client given: 1) the client's ID or 2) name or 3) name and address. . Retrieve the type of service and the number of times a specific client X was serviced for a given week Y (for both databases). . Retrieve the ID and name of clients, and the code and description of three most recent services the client received in one of the databases). . Retrieve the ID and name of clients who received the service X and have not received the service Y (in one of the databases) within a particular month. b. Suggest the means to improve performance of these queries on your solution - 30 points C. Suggest transactions (and maybe some additions to the design) for managing the situation when an employee is transferred to another service type. Note that your approach should allow to keep the full history of the employee's work - 20 points. 4. Manage your database: write queries for different requests can in-class part of the project)
