Answer to Question #214283 in Databases | SQL | Oracle | MS Access for Hassam

Question

Given is the schema of a table and fragments created. Write down which of the correctness rules of fragmentation are violated (also write reason) for each part and also rewrite new fragments (at-least 3) for each part such that all correctness rules must be satisfied.

1-     Employee (Eid, EName, eAge, eDesignation, eSalary)[ Salary must be more than 14000]

F1: eSalary > 14000 and eSalary < 20000

F2: eSalary >= 20000 and eSalary <=50000

F3: eSalary >=50000 and eSalary >= 100000

2-     Book(Book_ISBN, bTitle, bPrice, bCategory) [bCategory must not be ‘CS’ or ‘IT’]

F1: bCategory = ‘Science’

F2: bCategory = ‘Novels’

F3: bCategory != ‘Science’

F4: bCategory != ‘CS’ or bCategory != ‘IT’

Answer 1

2. 
F3: bCategory != ‘Science’
Because the statement will accept all other bCategories except 'Science'. This will allow other categories shuch as
'IT' and 'CS' which are not allowed in this case.


F4: bCategory != ‘CS’ or bCategory != ‘IT’
This will allow either 'CS' or 'IT' which is prohibited.