Given the following two tables (Employees and Departments) below and their sample values, explain three techniques available in a DBMS to maintain the Referential Integrity rule of a database. That is, explain what will happen if a user will try to delete the row with department_id 90 in the departments table. [33marks] TABLE: EMPLOYEES TABLE: DEPARTMENTS A specialist travel company wishes to computerize its operations. Each staff member services enquiries made by many customers with customers possibly making enquiries with different staff members at different times. Details of customer servicing are recorded even if booking does not result. An individual staff member may create a number of bookings with an individual booking being created by one staff member. Each customer who decides on a holiday makes a booking, with each booking being made by only one customer. The booking is associated with only one holiday type, and more than one booking may be associated with each holiday type. (i.e. a booking is created by a staff member for a customer to go on a holiday type). Each holiday type has a single destination, with each destination having only one holiday type, except for an occasional destination for which no holiday type is offered during a particular year. Staff details held include staff number, name, salary and grade. Customer details recorded include customer number, name, address, phone number, date of enquiry and enquiry type. Booking details include booking number and booking date. Holiday type details include holiday code, description, hotel name and duration. Destination details include destination name, country and continent. (a) Produce an Entity Relationship Diagram (Logical Data Structure) for the above scenario. For each entity, identify the primary key attribute and label each relationship with the appropriate role and cardinality. [15 marks] (b) Use the mapping rules to derive a suitable relational schema (Physical Data Model) for the above ERD produced. QUESTION 3 [20 Marks] (a) Describe the three types of anomalies which can arise in a table if it is not normalized. Support your answer with an appropriate example using the table structure given below. [23marks] (b) Using the table structure shown below, write the relational schema, draw its dependency diagram and identify all dependencies (including all partial, transitive and fully functional dependencies). The columns description is given below: ISBN: The ISBN of the book published. TITLE: The title of the book. YEAR_PUBLISHED: The year the book was published. PUB_ID: The Publisher ID. Pub_Name: The Publisher name. STREET, CITY, STATE and ZIP: The address of the Publisher. AU_ID: The Author ID. AU_LNAME: The Last Name of the Author. AU_FNAME: The First Name of the Author. YEAR_BORN: The Year the Author was born. [8 marks] (c) Draw the ERD from the relational schema obtained in the above Normalisation Process
