代写program、代写Java,python程序

日期：2022-08-15 09:29

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