Answer
DBMSs generally provide two types of database security: 

Answer

• Do not index small relations 
  
• In general, index the primary key of a relation if it is not a key of the file organization 
  
• Add a secondary index to a foreign key if it is frequently accessed 
  
• Add a secondary index to any attribute that is heavily used as a secondary key Add a secondary index on attributes that are frequently involved in: 
  
• (a) selection or join criteria 
• (b) ORDER BY 
• (c) GROUP BY 
• (d) other operations involving sorting (such as UNION or DISTINCT) 
• Add a secondary index on attributes involved in built-in aggregate functions, along with any attributes used for the built-in functions 
  
• Avoid indexing an attribute or relation that is frequently updated 
  
• Avoid indexing an attribute if the query will retrieve a significant proportion (for example 25%) of the tuples in the relation 
  
• Avoid indexing attributes that consist of long character strings
  

Answer

• Adding an index record to every secondary index whenever a tuple is inserted into the relation 
  
• Updating a secondary index when the corresponding tuple in the relation is updated 
  
• The increase in disk space needed to store the secondary index 
  
• Possible performance degradation during query optimization, as the query optimizer may consider all secondary indexes before selecting an optimal execution strategy
  

Answer
Primary index on the PropertyForRent relation based on the propertyNo attribute: 

 CREATE UNIQUE INDEX PropertyNoInd ON PropertyForRent(propertyNo);

 

 CREATE INDEX StaffNoInd ON PropertyForRent(staffNo) CLUSTER;

Answer

• Heap (unordered) files Records are placed on disk in no particular order. 
  
• Sequential (ordered) files Records are ordered by the value of a specified field. 
  
• Hash files Records are placed on disk according to a hash function.
  

Answer

CONSTRAINT StaffNotHandlingTooMuch

  CHECK (NOT EXISTS (SELECT staffNo

FROM PropertyForRent

GROUP BY staffNo

  HAVING COUNT(*) > 100))

Answer
The process of producing a description of the implementation of the database on secondary storage. It describes the base relations, file organizations, and indexes used to achieve efficient access to the data.