QUESTION 1 45 MARKS
Cold Chain Company (CCC) is an Australian Fast-Moving Consumer Goods (FMCG)
company and was formed through acquisitions of other smaller FMCG companies of
various sizes in the last ten years. Thus, the offices of these acquired companies are
spread across the country. These acquired companies have their own in-house
systems. Some of the databases used in these systems are outdated such as Microsoft
Access 2000, and databases running on Singer mainframe. The Chief Information
Officer (CIO) has decided to have one consolidated database running on Oracle
servers.
You are hired as a consultant to develop a prototype database. You can assume the
Information Technology (IT) staff will develop the applications using the prototype
database. Below are some of the specifications you collected from the initial meeting
with the business units. You will develop a prototype database based on your initial
meeting, and you acknowledge that not all attributes are included.
Below are the business rules you have been given by the CCC:
1. There are several departments in the CCC (e.g., Finance, Human Resources,
Marketing, Warehouse, Logistics, Sales, etc.). All departments are identified by
Department ID and other attributes, such as Department Name, Department
Description, Warehouse ID, and Manager ID.
2. Every employee of the company has a unique Employee ID. Other attributes
associated with the employee include first name, last name, gender, address,
telephone (landline and/or mobile), personal address, date of birth, and the
person an employee reports to, i.e. the manager (attribute is manager ID).
Please note that the manager is also an employee of the company, and the
Chief Executive Officer (CEO) of the company does not report to a manager.
3. Each employee is assigned to a job class (e.g., Manager, Sales, Administrative,
IT Staff, etc.). Each of the job classes has a different salary. For IT Staff, it is
further divided into an IT Project Manager, an IT Developer, and an IT Support.
An IT Project Manager has an additional attribute called Project Manager
Certification, and the date the certificated is obtained. An IT Developer has an
additional attribute called University Degree, and the year when it is obtained.
An IT Support has an additional attribute called Support Certification, and the
date the certificate is issued. You can assume that the company has to record
this extra information only if the employee is in that job class.
4. CCC has a number of warehouses across the country. Warehouses can be
identified by Warehouse ID. It also has other attributes called warehouse
location name (e.g., Warehouse Kensington), address, and warehouse
capacity.
PAGE 2 OF 3
5. For each of the warehouses, CCC has different types of room. A room can be
defined as an office room, a meeting room, or a storage building. If it is an office
room, it has an additional attribute called Room Telephone (e.g.,
+610481000000). If it is a meeting room, it has attributes of Layout and
Occupancy. Each warehouse has one storage building only which has an
attribute called Number of Pallets, i.e. the maximum number of pallets can be
stored in the warehouse (or better known as the warehouse capacity).
Required:
With reference to the above scenario, answer the following three questions:
(a) Using Chen’s notation to create an Entity Relationship Diagram (ERD) that
provides a suitable model based on the above business rules. You can state your
assumptions, but they must be reasonable.
(30 marks)
(b) When a business rule changes, it might change one or more of the entities in the
ERD in order to accommodate the changes. Based on the above scenario, give two
examples on how a change to the business rule will have an impact on the ERD,
and with an aid of a diagram(s) how would you modify your entity(ies) to
accommodate the changes.
[300 words limit] (10 marks)
(c) When a business rule changes, it might change one or more of the entities in the
ERD in order to accommodate the changes. Based on the above scenario, give
one example on how a change to the business rule will impact on the ERD, and
with an aid of a diagram(s) how would you modify your entity(ies) to accommodate
the changes.
[150 words limit] (5 marks)
PAGE 3 OF 3
QUESTION 2 35 MARKS
Scenario: The following Orders table is extracted from an Excel Spreadsheet.
Metadata for the above Orders table structure:
Required:
With reference to the above scenario, you now have to normalise the data. Answer the
following two questions:
a) Using the above table structure, draw the functional dependency diagram.
Identify, label and explain the selection of Primary Key(s), all transitive and/or
partial dependencies. (15 marks)
b) Create 1NF, 2NF, 3NF and BCNF (if necessary) showing all intermediate
steps in the Normalisation process. Write the relational schemas and show
all primary keys with solid lines and foreign keys with dotted lines.
(20 marks)
INV_NUM
PROD_
NUM
INV_DATE
CUST_
CODE
CUST_NAME PROD_DESC
VEND
_CODE
VEND_
NAME
QTY_
SOLD
PROD_
PRICE
TOT_AMT
208760 123 17-Oct-20 9485 Harry Jones Cadbury Chocolate 2kg 672 Cadbury 1 $ 60.44 $ 60.44
208760 456 17-Oct-20 9485 Harry Jones Old Gold Chocolate 100g 672 Cadbury 8 $ 4.17 $ 33.36
208760 789 17-Oct-20 9485 Harry Jones Rowntree Cholcolate 125g 868 Rownie 1 $ 48.40 $ 48.40
208761 123 17-Oct-20 9500 Stephen Smith Cadbury Chocolate 2kg 672 Cadbury 2 $ 60.44 $ 120.88
208762 567 17-Oct-20 9600 Ivan Zhang Lindt Chocolate 1kg 564 Lindt 1 $ 99.99 $ 99.99
Column Column Description
INV_NUM Invoice Number
PROD_NUM Product Number
INV_DATE Invoice Date
CUST_CODE Customer Code
CUST_NAME Customer Number
PROD_DESC Product Description
VEND_CODE Vendor Code
VEND_NAME Vendor Name
QTY_SOLD Quantity Sold
PROD_PRICE Product Price
TOT_AMT Total Amount = Quantity Sold * Product Price