Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: THEend8_
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.