Consider the following dimensions, dimension attributes, and dimension sizes for Farm Coop-Insurance.
x Member(member_ID, Name, Address). On average, there are two members for each policy and item covered by the policy.
x InsuredItem(Item_ID, Description, Coverage_Type). There is an average of ten covered items per policy.
x CoopOffice(Office_ID, Address, Manager_name). Each policy is registered and managed by only one office of the cooperative.
x Policy(Policy_ID, Type). The company has approximately one million policies at the present time.
Approximately five percent of these policies experience some change each month.
x Period(Date_Key, Fiscal_Period). The length of the fiscal period is one month. The decision system reports are supposed to be based on five years of data.
x Claim(Claim_ID, Claim_Description, Claim_Type).
The facts to be recorded for each combination of these dimensions are: Policy_Premium, Deductible, and Monthly_Claim_Total.
a. Design a star schema for this problem.
b. Using the assumptions stated above, estimate the number of rows in the fact table.
c. Estimate the total size of the fact table (in bytes), assuming an average of 5 bytes per field.
Suggest an appropriate recovery technique that a database administrator could use to resolve each of the following situations.
a. A network disconnection occurs while a user is entering a transaction at an ATM bank machine.
b. A disk drive fails during regular operations while a clerk is entering data about newly registered students.
c. The registration office at a university entered an incorrect amount for a student tuition payment. The error was discovered by the financial services department several weeks later.
d. The database administrator of a financial institution performed a full database backup, but forgot to activate the journalizing facility. Afterwards, data entry clerks at the financial institution entered transactions for two hours before the database became corrupt. It is discovered that the journalizing facility of the database has not been activated since the backup was made.
Suggest the most appropriate security measures for each of the situations described below.
a. The Western Union bank uses an electronic funds transfer (EFT) system to transmit sensitive financial data between its branches all over the world.
b. A fighter jet simulation company has set up an off-site computer-based training centre for the F35. The company wishes to restrict access to the site to authorized employees. Since each employee's use of the centre is occasional, it does not wish to provide the employees with keys to access the centre.
c. A golf club uses a simple password system to protect its database. The club has created a new Web site to allow both its members and its employees to use the new Web-based system to access and update information. The club finds that it needs a more comprehensive security system to grant different privileges (such as read-only versus create or update) to different users.
d. A training centre at the Northern Technical University has experienced considerable difficulty with unauthorized users who access files and databases by appropriating passwords from legitimate users.
The UBS broker company has a database server with three disks. Both the accounting and stock exchange applications share the same disk, and they are experiencing performance problems. Discuss potential reasons for the performance problem, and suggest how to reduce I/O contention.
Consider the concurrent execution of the following transactions.
Read A Read B
Read B Write A
Write C Read C
Write A Write B
List two problems that may occur from the concurrent execution of these two transactions.
The Edmonton International Airport would like to implement a database that will be used to keep track of airplanes, their owners, airport employees, and pilots. From the requirements for this database, the following information was collected.
x Each airplane has a registration number, is of a particular plane type, and is stored in a particular hangar. x Each hangar is managed by an employee who supervises the maintenance services performed in that hangar.
x Each plane type has a model number, a capacity, and a weight. x Each hangar has a number, a capacity, and a location.
x The database keeps track of the owner of each plane, and the employees who have maintained each plane.
x The database keeps track of each airplane’s purchase date.
x Each maintenance service record is identified by a work code, and includes the employee who performed the service, the date and time of the service, and the number of hours the maintenance service required.
x Each plane undergoes service many times, and all its service records are kept. x An owner can be either a person or a corporation. x A person can be an owner, a pilot, or an employee of the airport. x Each pilot has specific attributes, including license number and restrictions. x Each employee has specific attributes, including salary and shift worked.
x The database stores social insurance number, name, address, and telephone number for all person entities.
x The database stores name, address, and telephone number for all corporation entities.
x The database also keeps track of the types of plane each pilot is authorized to fly, and the types of plane each employee is qualified to service.
Draw an object-oriented diagram for the Edmonton International Airport database.
This material may consist of step-by-step explanations on how to solve a problem or examples of proper writing, including the use of citations, references, bibliographies, and formatting. This material is made available for the sole purpose of studying and learning - misuse is strictly forbidden.