HKU Business School
IIMT3601 Database Management
Review for Written Exam (50% of the course assessment)
April 29, 2021 (Thursday) 1:45pm to 3:45pm

Chapter 1: The Database Environment
 Definition of data, metadata, and database
 Advantages and disadvantages of file processing systems
 Definition of a database management system
 Advantages and cost/risk of the database approach
 Elements of the database approach
 One-to-many/many-to-many relationships
 Components of the database environment
 Database applications

Chapter 2: The Database Development Process
 Enterprise data model
 Functional decomposition and planning matrices.
 SDLC vs Prototyping
 The use of CASE tools in database development
 Advantages of using packaged data models
 Database schema and three-schema architecture

Chapter 3: Modeling Data in the Organization
 Definition of business rules and good data names
 The three basic constructs for ER models
 How to choose good entities and attributes and how to represent relationships correctly
 Different types of attributes
 Characteristics of identifiers
 Degree of relationships: unary, binary, ternary
 Cardinality of relationships: one-to-one, one-to-many, many-to-many
 Cardinality constraints
 Multiple relationships
 Strong vs Weak entities
 Associative entities
 How to draw ER diagrams for common business situations using the notation in the handouts

Chapter 4: The Enhanced ER Model and Business Rules
 Use of supertype/subtype relationships
 Use of generalization and specialization techniques
 Specification of completeness and disjointness constraints
 Subtype discriminator
 Entity clusters
 Classification of business rules.
 Action assertions in EER diagrams.
 How to draw EER diagrams for common business situations

Chapter 5: Logical Database Design and Relational Model
 Properties of relations
 Properties of candidate keys
 Definition of primary keys, foreign keys, simple keys, and composite keys.
 Different types of integrity constraints
 Transforming EER diagrams into relations:
o Regular entities (Simple attributes, Composite attributes, Multivalued attributes)
o Weak entities
o Binary relationships
o Associate entities
o Unary relationships
o Ternary relationships
o Supertype/subtype relationships
 Goal of data normalization
 Types of anomalies
 Identify functional dependencies in relations
 Express functional dependency using arrows or diagrams
 Understand the differences between 1NF, 2NF, and 3NF and how to do normalization
 Issues to watch out in merging relations

Chapter 6: Physical Database Design and Performance
 The process of physical database design
 The notation used in usage map
 Field design and data types
 Field data integrity
 The purpose and pros/cons of denormalization
 The purpose and pros/cons of partitioning
 The principles and pros/cons of each file organization method
 General rules for using indexes
 RAID

Chapter 7: Introduction to SQL
 Benefits of a Standardized Relational Language
 The SQL environment
 DDL, DML, DCL, and the database development process
 CREATE TABLE statements
 Establish referential integrity using SQL
 ALTER TABLE and DROP TABLE statements
 CREATE INDEX statements
 INSERT, DELETE, and UPDATE statements
 MERGE statements
 SELECT statements
 Aggregate functions and Boolean operators
 Using and defining views
 Pros/cons of dynamic views

Chapter 8: Advanced SQL
 Different types of JOIN operations
 Multiple-table SQL queries
 Correlated and noncorrelated subqueries
 UNION queries
 CASE statements
 Ensuring referential integrity using SQL
 Data dictionary facilities
 SQL:1999 and SQL:2003 Enhancements/Extensions
 Routines and triggers

Format and Sample Questions (Total: 100 points)

There will be about 6 to 8 short questions (possibly with subparts) based on what we have covered so far.
This may include such tasks as drawing ER diagrams or writing SQL queries. Below are some sample
questions:

1. What are the disadvantages of traditional file processing systems?
2. Draw an ER diagram for a given situation (similar to Assignment 1 & Exercise 1)
3. Map the following ER diagram into relations (similar to Exercise 2)

4. Logical Design and Normalization (similar to Exercise 3)
Suppose a database has the following relations:
- ORDER_T (Order_ID, Order_Date, Customer_ID, Customer_Name, Customer_Address)
- PRODUCT_T (Product_ID, Product_Description, Product_Price)
- ORDER_LINE_T (Order_ID, Product_ID, Order_Quantity)

(a) Are the tables in 1NF, 2NF, or 3NF?
(b) Convert all tables into 3NF.

5. Writing SQL (similar to Exercises 4 and 5 and Assignment 2)
Suppose a database has the following relations:
- ORDER_T (Order_ID, Order_Date, Customer_ID, Customer_Name, Customer_Address)
- PRODUCT_T (Product_ID, Product_Description, Product_Price)
- ORDER_LINE_T (Order_ID, Product_ID, Order_Quantity)

(a) Write a query to create the PRODUCT_T table.
(b) Write a query to retrieve the details of all customers who have placed an order. Include the
Order_ID in the result.
(c) What is the purpose of the following query?
SELECT MAX(Product_Price) FROM PRODUCT_T

6. What are the advantages and disadvantages of sequential file organization?









































































































































学霸联盟