Database Design & Implementation - Course Details only

Introduction To Database

1. What are the roles of the Database Administrator (DBA)? [8 marks] June 2010
2. Discuss the general characteristics of advanced database applications and give an example application for each characteristic. [16 marks]        DEC2011
3. (a) What are the Database Administrator (DBA) ? List three of them with brief explanations. If we assume that the DBA is never interested in running his or her own queries, does the DBA still need to understand query optimisation? Justify your answer.

[8 marks] JUNE 2008

THE RELATIONAL MODEL

1. (b) There are several strategies defined during the logical database design process for maintaining referential integrity in relational databases. A strategy for deleting or updating a tuple is defined at the time when the table is created. Referencing SQL-92 (i.e. SQL II) standard, list these strategies and briefly explain each strategy.        [10 marks] DEC2010
2. (c) How can the primary key and foreign key constraints be enforced by a Database Management System (DBMS)? Is the enforcement technique you suggest difficult to implement? Can the constraint checks be executed in an efficient manner when updates are applied to the database? DEC2010
3. Explain why an instance of a relation (i.e. a snapshot of a table) cannot be used to prove that an attribute, or a combination of attributes, is a candidate key. [8 marks] DEC2009
4. Explain the difference between the natural join and the outer join. Illustrate your answer with an example. [8 marks] DEC2008 p 96

ENTITY RELATIONSHIP MODELLING/EERD

1. Represent the entity and relationship types shown in the ER diagram of Figure 1 below as relations. Underline the primary key and identify the foreign key (if any). [8 marks] DEC2011
2. Consider the diagram in Figure 1 below which shows a portion of an EERD for maintaining students’ registration information on Student, Course and Assessment. Map this portion of the model into a Relational Schema and identify any primary and foreign keys (e.g. identify a primary key by an underline and a foreign key by circle or a box). [16 marks] JUNE2011
3. SCENARIO….. (i) Explain why the Entity Relationship Schema below is incorrect for this application. [3 marks] DEC2009 (ii) Correct and redraw the Entity Relationship Diagram for the schema below according to the Extended Entity Relationship model. [5 marks] DEC2009

LOGICAL DATA MODEL TO RELATIONAL DATABASE/MAPPING

1. Map the above Relational Schema into one Relational table. Do not add additional attributes to the list. Underline the primary key and identify any foreign keys by drawing an arrow from each foreign key pointing to the referenced primary key. Do not add any additional attributes.[10 marks] JUNE2010
2. Reproduce the diagram above to include this scenario.[10 marks] JUNE2010
3. Map your diagram into a relational schema. Underline the primary key and identify any foreign keys by drawing an arrow from each foreign key pointing to the referenced primary key. Do not add any additional attributes. [10 marks]  JUNE2010

SECURITY

1.  Integrity is a feature of a Database Management System (DBMS). Consider the following scenario: An investment company operates out of a single office in Hong Kong. Its business is based on many years of high quality service, honesty, and reliability. The CEO (Chief Executive Officer) is concerned that the firm has become too dependent on its computer system. If some disaster should occur and the firm’s databases were lost, its reputation for reliability would disappear overnight — and so would many of its customers in the high competitive business.

Discuss what you think the firm should do to ensure database integrity. [14 marks] DEC2011/ JUNE2010

1. (c) (i) Explain how the view mechanism can be used to improve security in database systems.  [4 marks]  (ii) Is the Entity Integrity rule applicable to views? Explain your answer by giving an example. [4 marks] DEC2009

1. The security of databases is of vital importance to the organization especially in the case of sensitive or valuable information. Identify the main difference between the computer and non-computer based controls that is needed to ensure maximum information security. List the mechanisms under each category of controls. [8 marks]    JUNE2011
2. Security and recovery are two important functions of a database management system, which are also involved with database administration. Various facilities can be provided to aid both these functions. Explain what these facilities are and use examples to demonstrate their role in database administration. [15 marks] DEC 2008

TRANSACTION MANAGEMENT

1. Concurrent transactions can create several problems when they are executed without control. The scenario in figure 5 on the next page represents two transactions (T1 and T2) that encounter one of these problems. T1 is subtracting “N” from X and add it to Y. T2 is adding “M” to X. continue…… JUNE2011
2. (a)What are the final values in S1G3, Sum S1 and Avg S1 after the above interleaved sequence of operations for transactions Xat1, Xat2 and Xat3 are executed?  [10 marks]

(b) What is the main difference between this and the previous schedule in (i) above? Are the previously encountered problems (if any identified) resolved in this schedule? Comment on your results and identify any problems if any.[10 marks]

(c) Discuss how you can avoid any of the encountered problems identified in (i) and(ii) and identify any mechanisms or techniques to resolve the problems. Based on the given initial schedule given above, show how your proposed solution can be used to produce a correct schedule. What will be the final values in S1G3 and Avg S1 after executing your proposed schedule? [10 marks] DEC2010

1. SCENARIO…..What are the actual final values resulting from the above sequence when transactions A and B are executed in an interleaved manner? Show how you calculated your results. [5 marks] June 2010
2. (ii) SCENARIO…..What should be the final balance value if the transactions were performed serially? Show how you calculated your results. [5 marks] June 2010
3. SCENARIO…..Identify the type of problem in each of the two transaction schedules presented below. Justify your answer. [5 marks]+[5 marks]  June 2010
4. SCENARIO….. (i) What are the final values in ACC 1 and ACC 2 after the above sequence of operations in transactions A and B have been executed in an interleaved manner? [3 marks]
5. DEC 2009 SCENARIO….. (ii) What should be the final values in ACC 1 and ACC 2 if the transactions were performed serially? [3 marks] DEC 2009
6. DEC 2009 SCENARIO….. (iii) Comment on the results for sections (i) and (ii) above identifying any problem encountered and how this problem can be avoided (if possible)? DEC 2009
7. (c) Explain the concepts of serial, non-serial, and serializable schedules. State the rules for equivalence of schedules.[10 marks] June 2009 p 580

QUERY OPTIMASATIONS

1. State the first two of the four main phases of query processing and briefly describe what should happen in each phase identified. [8 marks]  June 2010
2. DEC 2009 SCENARIO….. (i) List the most important factors that you need to consider in distributed query optimization. [5 marks]
3. DEC 2009 SCENARIO….. (ii) Study the two query plans above and pick the one that you think is more efficient for the given scenario, and justify your answer. [5 marks]
4. Compare and contrast dynamic query optimisation versus static query optimisation.[8 marks] June 2009 p 758
5. What are the objectives of query processing. Briefly discuss the most important activity in this process.[8 marks] DEC 2008

DISTRIBUTED DATABASE

1. (b) Fragmentation, location, local mapping and naming transparencies shield users from the implementation details of distributed systems. Describe each of these distribution transparencies and give an example of how each transparency conceals details from the user.

[16 marks] DEC2011

1. In your first job you were appointed because you know all about Distributed Database Systems (DDBS). Your manager has asked you to give a brief presentation on DDBS. Specifically, he would like to know about the different business and technical issues associated with DDBS. Give a suitable example to support your discussion.[30 marks] JUNE2011

OBJECT ORIENTED DATABASE

1. Compare inheritance in the Enhanced Entity Relationship model to inheritance in the Object Oriented model. [8 marks] DEC2010
2. (a) “Object Databases are just about dealing with new data types”. Express your view on the above comment and justify your agreement or disagreement with it. Give examples whenever possible.[10 marks] DEC2011
3. Advanced database applications require analytical tools that go beyond the primitives found in the basic Entity Relationship Model. The Enhanced Entity Relationship Model (i.e. EERM), and the Class Diagram used in the Object–Oriented paradigm (i.e. OM) are two examples that cater for these new application requirements. Compare the two modelling tools, EERM vs. OM, in terms of their primitives and advantages of one over the other. [20 marks] DEC2009
4. Computer Aided Software Engineering (CASE) tools are used to provide users with facilities for capturing requirement specification, information modeling and the design and evolution of the developed system. Most existing CASE tools use relational database technologies as part of their environment to support their operations. The relational technology is limited in its support and cannot cater for new applications requirements such as dealing with complex variable large objects, versioning, composite objects, associative access with browsing ability, etc. Discuss the suitability of Object Oriented Database technology over relational technology in supporting the development of the new generation of CASE tools. [15 marks] DEC 2008 p806

WEB & DBMS

1. What is semistructured data? Discuss the difference between semistructured and structured data. Give examples to illustrate your answer.[10 marks] JUNE 2009 page 1066
2. Discuss how XML can be transferred into a database.[10 marks] JUNE 2009 page 1129

DATA WAREHOUSE

1.  You have been retained as a consultant to build a data warehouse for a large multinational company with many autonomous business units all over Europe. Produce a report explaining the purpose and the process of building a data warehouse for this company. For each stage, briefly explain the functions performed. Illustrate your answer with a diagram and suitable examples where appropriate. [30 marks] DEC2011

1. Database Views and Data Marts are alike in that they both have read-only extracts from databases. However, Data Marts and Views are different in many ways. Identify at least four of these differences. [8 marks] DEC2010

1. Dimensional modelling offers important advantages within a Data Warehousing environment. List five of these advantages and explain how each advantage may impact on the Data Warehousing environment. Use examples to illustrate your answer.

[20 marks]  DEC2009