QUESTION 1 25 MARKS
Bike sharing has become increasingly popular across the globe due to climate change. The principle of bike sharing is simple. Individuals use bicycles on an asneeded basis without the costs and responsibilities of bike ownership. It is a shortterm bicycle access, which provides its users with an environmentally friendly form of public transportation. This flexible short-term use scheme targets daily mobility and allows users to access public bicycles at unattended bike stations. Bicycle reservations, pickup, and drop-off are self-service.
A renter has to download a smartphone app before the renter can hire a bike. The smartphone app is used to unlock and locate nearby bicycles, report problems with the bicycles, ask for help, and pay for the ride. A casual renter can use the smartphone app to pay for the ride. As for frequent and regular renters, they can sign up, again using the app, for discount payments such as monthly or yearly subscriptions. The smartphone app is tracked using GPS so a bike company knows where the riders start and finish their journey, and where the bike is finally located.
Bike sharing companies need to maintain their company enterprise systems (such as human resources system, customer service system, invoicing system, and accounting system) for their daily operation. Moreover, some of the activities such as choosing a subscription and payment are done through the smartphone app.
In addition, a rider can use the smartphone app to view the time and distance they have travelled, and any historical journeys they have taken. The bike sharing companies capture live streaming data to track where the riders and their bicycles. Over time, the amount of data generated from riders can be constituted as a big data.
A new start-up company in Sydney has decided to start a bike sharing business but focuses on renting electric bicycles and electrical motorbikes. The company has decided to hire you as a consultant. You will be working with the IT development team.
Answer the following question:
Explain how you would design the company’s new infrastructure network and ecosystems to handle the data generated from various systems. The data is to be accessed and shared by all the departments for their data analysis. You can aid with a diagram to support your design. [800 words limit]
Note: you cannot use or copy diagrams from other sources.
QUESTION 2 40 MARKS
A sample report:
|Item Description||Quantity||Price per Item||Gross
|12345||Stan Smith||19750||19751||95410||Egg||2||$ 4.50||$ 9.00||$ 9.00||1 Kent St, Perth|
|12345||Stan Smith||19750||19751||96722||Toilet Paper||2||$ 7.00||$ 14.00||$ 14.00||1 Kent St, Perth|
|12346||Grace Jones||19753||19754||94013||Milk 3 litre||5||$ 4.25||$ 21.25||$ 4.25||CV||$ 17.00||367 Lamb St, Perth|
|12346||Grace Jones||19753||19755||96722||Toilet Paper||6||$ 7.00||$ 42.00||$ 8.40||CV||$ 33.60||376 Lamb St, Perth|
|12347||Liz Clark||19756||19757||95410||Egg||4||$ 4.50||$ 18.00||$ 2.00||CQ||$ 16.00||367 King St, Perth|
|12347||Liz Clark||19756||19757||95950||Chocolate||3||$ 5.00||$ 15.00||$ 1.50||CQ||$ 13.50||367 King St, Perth|
|12349||John Xu||19759||19760||94013||Milk 3 litre||2||$ 4.25||$ 8.50||$ 8.50||15 Berry St, Perth|
|12349||John Xu||19759||19760||95410||Egg||2||$ 4.50||$ 9.00||$ 9.00||15 Berry St, Perth|
Metadata for the above table structure:
|Customer No||:||Customer Unique Id|
|Customer Name||:||Customer Name|
|Order No||:||Order Unique Id|
|Delivery No||:||Delivery Unique Id|
|Item No||:||Item Unique Id|
|Item Description||:||Item Description|
|Price per item||:||Recommended Retail Price per item|
|Gross Amount||:||Gross Amount = Quantity * Price per Item|
|Discount Amount||:||(i) Discount Amount based on Quantity(ies) purchased or|
|(ii) Discount Amount based on a % of the Gross Amount|
|Discount Code||:||CQ: Quantity Discount based on quantity(ies) purchased by a customer|
|CV: Value Discount based on a % on Gross Amount purchased by a customer|
|Net Amount||:||Gross Amount – Discount Amount|
|Delivery Address||:||The address the items to be delivered to|
- You can assume dates will not influence the relationships between attributes and/or tables.
- You can assume delivery address is the same for the same customer, but an order can be broken down and delivered to the customer separately.
- You can assume all deliveries are free.
- You can add new attributes/columns to a table when it is necessary.
With reference to the above scenario, you now have to normalise the data. Answer the following three questions:
- 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. (10 marks)
- 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)
- Assuming your relational schemas in Question 2 (b) are now converted into tables. Write an ANSI SQL query (that is, you cannot use SAS enhancement functions such as “calculated”) using SQL Join clauses on these tables to create the sample report as shown on previous page. (10 marks)
QUESTION 3 35 MARKS
Cold Chain Company (CCC) is a conglomerate 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, DB2 running on MVS mainframe, VAX Rdb/VMS running on VAX VMS, and databases running on Singer mainframe. The CIO (Chief Information Officer) of Cold Chain 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 given by the CCC:
- There are several departments in the CCC (e.g., Finance, Human Resources, Marketing, Warehouse, Logistics, Sales, IT etc.). All departments are identified by Department ID and other attributes, such as Department Name, Department Description, Warehouse ID, and Manager ID.
- Every employee of the company has a unique Employee ID. Other attributes associate 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 CEO (Chief Executive Officer) of the company does not report to a manager.
- If an employee is currently married to another employee of the CCC, the date of marriage and who is married to whom must be stored in the database; however, no record of marriage is required if an employee’s spouse is not an employee.
- Each employee is assigned to a job class (e.g., Manager, Sales, Administrative, IT Staff, etc.). Each of 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 date the certificated is obtained. An IT Developer has an additional attribute called University Degree, and year when it is obtained. An IT Support has an additional attribute called Support Certification, and the date the staff is graduated. You can assume that the company only has to record this extra information if the employee is in that job class.
- 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 (including state and postcode), and warehouse capacity.
- 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 only has one storage building 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).
- CCC has customers, products and warehouses. Customers consist of a unique Customer ID, address (including state and postcode), and contact person. A customer might have more than one warehouse (Customer Warehouse ID). For instance, a customer might have five warehouses in five different locations (Sydney, Melbourne, Brisbane, Perth and Adelaide). Thus, CCC might deliver from Warehouse Kensington to Customer’s Warehouse Melbourne.
- Products consist of a unique Product ID, description, and weight. Each product has a new price at the beginning of the year, i.e. the same price is used throughout that year. You can assume all the products are stored in all the warehouses.
- There are monthly specials. Every calendar month of a year, some products are listed as promotion to attract more customers to purchase. The monthly promotion attributes include Product ID (i.e. the product to be promoted), promotion month, and discount per unit.
With reference to the above scenario, answer the following two questions:
- Using Crow’s Foot 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. (25 marks)
- 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 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)