SECTION A: Answer ALL (30 marks) QUESTION 1 FDelivery is a shipping company with good reputation. It relies on a company-wide information system to track all shipped items. Each shipped item is sent/delivered from the retail centre. Each item can be characterised by a unique item number, weight, dimensions, insurance amount, destination and final delivery date. For retail centres, the address, unique ID and type must be recorded. Shipped items make their way to their destination via one or more standard transportation events, i.e. flight, truck or train. For example, item A was delivered via flight and truck in order to reach its destination whereas item B was delivered via truck to reach its destination. Each transportation event is characterised by a unique schedule number, date, type and delivery route. (a) Please generate TWO appropriate business rules to capture the information about FDelivery. [3 marks] (b) Create a conceptual entity relationship diagram (ERD) according to your business rules in (a) by using Chen's notation. Please indicate the ERD with primary key, cardinalities and attributes [7 marks] QUESTION 2 Figure 1. Order Form Figure 1 is an order form from a shop. Normalisation process is needed to verify the design of the database system. Please answer the following questions accordingly. (a) What is the main goal of normalisation process? [1 mark] (b) Please identify the composite keys and attributes of first normal form. [3 marks] (c) Based on Figure 1, please produce second and third normalisation form. Remember to indicate primary key and foreign keys. [6 marks] Write the appropriate Structured Query Language (SQL) command to solve the following questions by referring Figure 2. Assume the date format is YYYY-MM-DD. (a) Write a query to insert the value in Table I into table CLIENT. [2 marks] Table 1t: Information of Client (b) Display the client's name and the lawyer's name who is in charge of the case of customer with client_id 10029. [5 marks] (c) Please update the years in practice to 20 for the judge whose name is Jason Thomas George. [3 marks] (a) The following are the problems that have been identified during the testing process in the development of a new system. In which part of the life cycle do you think these problems could have originated and been identified? (i) The performance of the system is poor - failing to respond quickly enough to meet the stated user requirement when generating report. [2 marks] (ii) No backup facilities were included to meet the users' requirement of long-term archival of their data. [2 marks] (b) Assume that your DBMS is using shared/exclusive locks for concurrency control. Currently, T1 has an exclusive lock on data item A, and T2 has a shared lock on data item C. All other data items are currently unlocked. (i) Why do you think that data item A is locked with exclusive lock and data item C is locked with shared lock? [3 marks] (ii) Assume that Tl is requesting a shared lock from data item B where it is currently holding exclusive lock on data item A. T4 is requesting an exclusive lock from data item A where it is currently holding exclusive lock on data item B. What is this situation? Any solution? [3 marks] (a) In which type of situation would you consider using a distributed DBMS instead of a centralised DBMS? [2 marks] (b) A company has a distributed database located in site A, B and C. Two-phase commit protocol is used to control the concurrency of the database. Assume that an update is applied on all sites. Please describe the procedures of the protocol in controlling the concurrency of the database. [4 marks] (c) Given the following business rules. Please translate the rules into object-oriented database diagram by showing the relationship information and AT LEAST TWO attributes. Each passenger can fly to many destinations. Each destination can be reached by many passengers. [4 marks] (a) The environment of a database system is the database infrastructure of a company. There are five major parts within this environment. Please describe each major part with example. [5 marks] (b) Following are the questions that related to data model. (i) What do you understand by entity relationship data modelling? [2 marks] (ii) In your opinion, what are the reasons that cause the evolution of hierarchical and network models to relational model? [3 marks]
