Final Report ITS232 PDF

Preview

Summary

ITS232: INTRODUCTION TODATABASE MANAGEMENT SYSTEMSSemester September 2019 – January 2020Final Report: Production of Dairy Product in FarmFreshPrepared by:Name of the Group Members Student ID1. AKMAL HISHAM BIN NORKHAIRUL AZHA20184336642. FIKRI FIRAS BIN TAJUL ARIFIN 20182118143. FAIZ ELMIE SHAH BIN ...

Description

CS1103D

ITS232: INTRODUCTION TO DATABASE MANAGEMENT SYSTEMS Semester September 2019 – January 2020 Final Report: Production of Dairy Product in Farm Fresh

Prepared by: Name of the Group Members 1. AKMAL HISHAM BIN NOR KHAIRUL AZHA 2. FIKRI FIRAS BIN TAJUL ARIFIN 3. FAIZ ELMIE SHAH BIN IZAHAR SHAH 4. AHMAD FITRI BIN HAMZAH

Student ID 2018433664 2018211814 2018226852 2017339739

Prepared for: MADAM NIK RUSLAWATI NIK MUSTPA (Leave this part blank. For lecturer’s comments only)

Remarks:

TABLE OF CONTENTS

No

Content

Page

. 1. 2. 3. 4. 5. 6. 7.

Members’ Profile Company Background Current System Descriptions Problem Statements Suggested Solution Database Objectives Database Design 7.1. Entity Relationship Diagram (ERD)

8. 9.

7.2. 3NF Relational Schema Database Implementation References

2-3 4-6 7-9 10 11 12 13-14 15-33 34

1.0 MEMBER’S PROFILE

Name

: Akmal Hisham bin Nor Khairul Azha

1|Page

Matrix Number

: 2018433664

Phone Number

: 017-6073663

Email

: [email protected]

IC No

: 000307050695

Address

: No.21 , Lorong Seri Damak 26b, Taman Sri Andalas, 41200 Klang, Selangor

Name

: Fikri Firas bin Tajul Arifin

Matrix Number

: 2018211814

Phone Number

: 0173022089

Email

: [email protected]

IC No

: 000703080271

Address

: PT3383 Kg Tersusun Simpang 4 , 32000 Sitiawan, Perak

Name

: Faiz Elmie Shah Bin Izahar Shah

Matrix Number

: 2018226852

Phone Number

: 0125380672

Email

: [email protected]

IC No

: 961122115395

Address

: No.21 , Pers. Bakar Bata 2 , Tmn Sungai Kinta Tanjong Rambutan , 31250 , Perak

Name

: Ahmad Fitri bin Hamzah

Matrix Number

: 2017339739

Phone Number

: 0197641981

2|Page

Email

: [email protected]

IC No

: 940315145483

Address

: G-0-3, Vista Tasik, Jalan Sri Permaisuri, Bandar Sri Permaisuri, 56000, Cheras, Kuala Lumpur

2.0 COMPANY BACKGROUND

3|Page

COMPANY NAME: Farm Fresh Milk Sdn. Bhd (Malaysia)

COMPANY ADDRESS: No. 11-1, Jalan Petaling, Kawasan Perindustrian Larkin, 80350, Johor Bahru, Johor

COMPANY OWNER: Mr. Loi Tuan Ee

COMPANY STORY: Ever since growing up in a small town in Perak, Loi Tuan Ee has held a deep respect for agriculture and a love of nature. Noticing that many Malaysian brands were selling products made from reconstituted or powdered milk, he decided to purchase a small plot of land in Johor and import 60 Holstein Jersey cows from Australia, setting up The Holstein Milk Company.

At first, he found buyers hard to come by. After all, who would trust a Malaysian dairy brand? But one day, a major retailer took a chance on Farm Fresh. Loi’s dream was becoming a reality. Farm Fresh was growing faster than Loi could have ever imagined.

To help expand his operations, he enlisted the help of his old friend Azmi Zainal as partner and brought in Khazanah National, the strategic investment arm of the Government of Malaysia, as an investor in the company. These collaborations laid the groundwork for the company’s continued success.

The word about Farm Fresh’s natural products soon spread, and before long our fresh milk and yoghurt products became available at supermarkets and hypermarkets all across Malaysia.

4|Page

MOTTO: To produce fresh and pure dairy just as nature intended.

VISION: “It’s all about quality produce, ensuring we have healthy and happy cows, preserving the environment and giving our customers what they truly deserve.”

“We believe in a simple approach to dairy farming and that when we do these things well, people can taste the difference.”

5|Page

ORGANISATION CHART

Loi Tuan Ee

6|Page

3.0 CURRENT SYSTEM DESCRIPTION

This company produces dairy products such as milk and yogurt for its costumer. The company has five identities which are EMPLOYEE, BARN, FODDER, COW, PRODUCT and PRODUCTION. Manager want to keep track on the number of cows they have in each barn, which employee that given fodder task or barn task and the production of product by each cow.

3.1 Current System Processes

An employee will be given or assign to their task, which by the database their employee ID is required to be foreign key in the barn or fodder which refers to table employee. So, 1 employee can be assigned to 1 or more fodder task. And each of fodder task is assign to one and only one employee. For barn and employee, 1 and only 1 employee is assigned to 1 or more barn task. And each of barn task is assign to one and only one employee. In table fodder will be stored amount of food remaining for the cow. Every barn have different quantity of food. It relates by knowing which fodder is assign to which barn, hence they can get the amount of food stored for each barn. So, 1 and only 1 barn can be assigned to 1 or more fodder , and each fodder is assigned to one and only one barn. Each barn can contain a different number of cows, it depends on how many cow was assigned to each barn. So, one barn can contain one or more cow , while each cow can be contained in one and only one barn. In table cow, each cow had been set with their own identification number which can be use to refer their age and which barn they were assigned to. From table cow they can get which product was produced by which cow. So, one cow can produce 1 or more product, and each product can only be produced by 1 and only 1 cow.

7|Page

3.2 CURRENT SYSTEM ERD

EMPLOYEE (EMPLOYEE_ID, EMPLOYEE_NAME, EMPLOYEE_JOB) FODDER (BARN_NO*, EMPLOYEE_ID*, FOOD_EXPR) BARN (BARN_NO, EMPLOYEE_ID*, BARN_SIZE) COW (COW_ID, COW_AGE, BARN_NO*) PRODUCT (PRODUCT_CODE, COW_ID*, PRODUCT_DATE)

8|Page

3.3 BUSINESS RULES

Each EMPLOYEE is assigned to one or many FODDER Each FODDER has one and only one EMPLOYEE Each EMPLOYEE is assigned to one and only one BARN Each BARN has one or many EMPLOYEE Each EMPLOYEE is assigned to one or many COW Each cow is handled by one and only one EMPLOYEE Each FODDER feed one or many COW Each COW is fed by one and only one FODDER Each BARN contains one or many COW Each COW lives in one and only one BARN Each COW produce one or many PRODUCT Each PRODUCT produces by one and only one COW

3.4 FLOWCHART

9|Page

4.0 PROBLEM STATEMENT

To what extent does the efficiency of data management currently being practice in the Fresh Farm company?

The company is facing a problem in handling a massive addition of cows breeding in a short period of time and when the number of cows increase the number of employees needed to keep track of each cow also increase which they fail to update from time to time.

The company find it hard to get information about which employee was in charge of production of each product because their database is lack of information about their employee.

The manager also wants to have detailed information about employee with task as fodder and employee that is in charge to barn with task as milker, the output of how much milk produced can’t be track.

5.0 SUGGESTED SOLUTION

10 | P a g e

We suggested to create a database which can help the company to keep track on their production more efficiently.

First, we suggest that we use bridge entity between barn and employee tables that is named as production. This table will store information about production of the product before it is produced. Hence, the company can keep track on which employee is responsible for which product.

Second, we suggest that for each of employee task or job was assigned in different table. We create an overlap table between each job so that we can know that an employee may work or perform both task and can give different output every time.

Third, in every task table will store detail information about the task before or after the task been done. For example, in table milk will store information on how much milk had been produced by every milker. In table fodder, will store information such as feeding schedule and etc.

6.0 DATABASE OBJECTIVES

11 | P a g e

To ensure this database is efficient and easy to use by the company. Therefore, by creating the database, this company can now focus on a larger scale of production. For example, each cow can now be track based on their health status, how much milk they can produce by every milker, and they can get the output (volume of milk) more accurately. Which can even be done with a larger number of cows.

To ensure that every data about production is to be stored in their table. The manufacture date and expire date can be track to get by connecting with table product so the information about the long lasting of the product can be retrieve. Manager can also see which of the product would last longer.

To ensure every worker is assigned to their job so every output from the employee task can be seen by the manager. Manager can now see the performance of every of his employee in performing task given. For example, for milker, manager can see which employee collect the highest volume of milk, following with their barn and date the task was performed.

7.0 DATABASE DESIGN 7.1 Final Entity Relationship Diagram

12 | P a g e

7.2 3NF RELATIONAL SCHEMA

13 | P a g e

PRODUCT(PRODUCT_CODE, PRODUCT_QUANTITY, PRODUCT_DETAIL, EXP_DATE) PRODUCTION (BARN_NO*, PRODUCT_CODE*, EMPLOYEE_ID*, MANUFACTURE_DATE) BARN(BARN_NO, BARN_DESC*, BARN_LASTCLEAN) COW(COW_ID, BARN_NO*, EMPLOYEE_ID*, DATE_LASTMILK, COW_STATUS, AGE) EMPLOYEE(EMPLOYEE_ID, EMP_HIREDATE, NAME, EMP_AGE) FOODER(EMPLOYEE_ID*, FOOD_DATE, BARN_NO*, END_SCHEDULE) MILKER(EMPLOYEE_ID*, VOLUME_MILK, MILK_TIME, BARN_NO*) BARN_INFO(BARN_DESC, BARN_CAPACITY)

8.0 DATABASE IMPLEMENTATION 8.1 DATA DEFINITION LANGUAGE DDL

14 | P a g e

CREATE TABLE 1. COMMAND : CREATE TABLE PRODUCT(PRODUCT_CODE VARCHAR(4) NOT NULL UNIQUE, PRODUCT_QUANTITY INTEGER, PRODUCT_DETAIL VARCHAR(20), EXP_DATE DATE, PRIMARY KEY (PRODUCT_CODE))

2. COMMAND : CREATE TABLE BARN (BARN_NO VARCHAR (3) NOT NULL UNIQUE, BARN_DESC VARCHAR (20), BARN_CAPACITY INTEGER, BARN_LASTCLEAN DATE, PRIMARY KEY(BARN_NO))

3. COMMAND : CREATE TABLE EMPLOYEE (EMPLOYEE_ID VARCHAR (5) NOT NULL UNIQUE, EMP_HIREDATE DATE, NAME VARCHAR(30), EMP_AGE INTEGER, PRIMARY KEY(EMPLOYEE_ID))

15 | P a g e

4. COMMAND : CREATE TABLE PRODUCTION(BARN_NO VARCHAR(3) NOT NULL, EMPLOYEE_ID VARCHAR(5) NOT NULL, PRODUCT_CODE VARCHAR(4) NOT NULL, MANUFACTURE_DATE DATE, REJ_QUANTITY INTEGER, FOREIGN KEY (BARN_NO) REFERENCES BARN(BARN_NO), FOREIGN KEY (EMPLOYEE_ID) REFERENCES EMPLOYEE(EMPLOYEE_ID), FOREIGN KEY (PRODUCT_CODE) REFERENCES PRODUCT(PRODUCT_CODE))

5. COMMAND : CREATE TABLE COW(COW_ID VARCHAR (4) NOT NULL UNIQUE, BARN_NO VARCHAR(3) NOT NULL, EMPLOYEE_ID VARCHAR(5) NOT NULL, DATE_LASTMILK DATE, COW_STATUS VARCHAR(20), AGE INTEGER, PRIMARY KEY(COW_ID), FOREIGN KEY(BARN_NO) REFERENCES BARN(BARN_NO), FOREIGN KEY(EMPLOYEE_ID) REFERENCES EMPLOYEE(EMPLOYEE_ID))

6. COMMAND : CREATE TABLE MILKER (EMPLOYEE_ID VARCHAR(5) NOT NULL, VOLUME_MILK DECIMAL(4,2), MILK_TIME DATE, BARN_NO VARCHAR(3) NOT

16 | P a g e

NULL, FOREIGN KEY(EMPLOYEE_ID) REFERENCES EMPLOYEE(EMPLOYEE_ID))

7. COMMAND : CREATE TABLE FODDER (EMPLOYEE_ID VARCHAR(5) NOT NULL, FOOD_DATE DATE, BARN_NO VARCHAR (3), END_SCHEDULE DATE, FOREIGN KEY (EMPLOYEE_ID) REFERENCES EMPLOYEE(EMPLOYEE_ID))

8.2 DATA MANIPULATION LANGUAGE DML

COMMAND : SELECT * FROM PRODUCT

17 | P a g e

COMMAND : SELECT * FROM PRODUCTION

COMMAND : SELECT * FROM BARN

18 | P a g e

COMMAND : SELECT * FROM EMPLOYEE

COMMAND : SELECT * FROM COW

19 | P a g e

COMMAND : SELECT * FROM MILKER

SELECT * FROM FODDER

20 | P a g e

8.2.1 MAINTAINING DATA

a) Mr. Abu Bakar has joined the company. His data is as follows: 1.

Employee Number: FF111

2.

Hire date: 1 December 2019

3.

Age: 37

COMMAND : INSERT INTO EMPLOYEE VALUES (‘FF111’, ‘2019-12-01’, ‘ABU BAKAR’, 37)

b) Update product A002 to Blueberry Yogurt Drink.

21 | P a g e

COMMAND : UPDATE PRODUCT SET PRODUCT_DETAIL = ‘Blueberry Yogurt’ WHERE PRODUCT_CODE = ‘A002’

c) Employee named Samad Abdullah has quit his job from the company. Delete his data. COMMAND : DELETE FROM EMPLOYEE WHERE NAME = ‘SAMAD ABDULLAH’

8.2.2 MULTIPLE TABLE

22 | P a g e

a) List the name of the employee that work as fodder and milker at the same time. Sort by age. - SELECT DISTINCT NAME, EMP_AGE FROM EMPLOYEE E, MILKER M, FOODER F WHERE M.EMPLOYEE_ID = F.EMPLOYEE_ID AND F.EMPLOYEE_ID = E.EMPLOYEE_ID ORDER BY EMP_AGE

b) List the product name, quantity and the employee id of the employee that is responsible for product A012. - SELECT DISTINCT P.PRODUCT_DETAIL, P.PRODUCT_QUANTITY, E.EMPLOYEE_ID FROM PRODUCT P, PRODUCTION A, EMPLOYEE E WHERE A.EMPLOYEE_ID = E.EMPLOYEE_ID AND P.PRODUCT_CODE = 'A012'

c) List all the cow code in barn B01 and the location of the barn along with the employee name that was assigned to the cow. Sort by employee name. - SELECT C.COW_ID, B.BARN_DESC, E.NAME FROM COW C, BARN B, EMPLOYEE E WHERE B.BARN_NO = C.BARN_NO AND B.BARN_NO = 'B01' AND C.EMPLOYEE_ID = E.EMPLOYEE_ID ORDER BY E.NAME

23 | P a g e

d) List the product detail and expire date of the product that was produced by cow in barn B02. Sort by expire date. - SELECT P.PRODUCT_DETAIL, P.EXP_DATE FROM PRODUCT P, PRODUCTION R WHERE P.PRODUCT_CODE = R.PRODUCT_CODE AND BARN_NO = 'B02' ORDER BY EXP_DATE

e) List the name and age of employee that milk cow on 8 October 2019. - SELECT E.NAME, E.EMP_AGE FROM EMPLOYEE E, COW C WHERE E.EMPLOYEE_ID = C.EMPLOYEE_ID AND DATE_LASTMILK = '2019-10-08'

24 | P a g e

8.2.3 COMPARISON

a) List the product name, manufacture date of the product that expired before January 2020. -SELECT P.PRODUCT_DETAIL, R.MANUFACTURE_DATE FROM PRODUCT P, PRODUCTION R WHERE P.PRODUCT_CODE = R.PRODUCT_CODE AND P.EXP_DATE < '2020-01-01'

b) List the name of employee that is not assigned to cow in barn B02. -SELECT DISTINCT E.NAME FROM EMPLOYEE E, COW C WHERE E.EMPLOYEE_ID = C.EMPLOYEE_ID AND BARN_NO 'B02'

25 | P a g e

c) List the name of employee that milk more than 50 litres after 12 October 2019. - SELECT DISTINCT E.NAME FROM EMPLOYEE E, COW C, MILKER M WHERE E.EMPLOYEE_ID = M.EMPLOYEE_ID AND VOLUME_MILK > 50 AND DATE_LASTMILK > '2019-10-12'

8.2.4 LOGICAL OPERATOR

a) List the name and manufacture date of product with quantity more than 50 and less than 100. Sort by manufacture date. -SELECT DISTINCT P.PRODUCT_DETAIL, R.MANUFACTURE_DATE FROM PRODUCT P, PRODUCTION R WHERE P.PRODUCT_CODE = R.PRODUCT_CODE AND P.PRODUCT_QUANTITY >= 50 AND P.PRODUCT_QUANTITY 30 OR EMP_HIREDATE > '2016-12-31' ORDER BY EMP_AGE DESC

c) List all the product code and manufacture date that is not milk. -SELECT P.PRODUCT_CODE, R.MANUFACTURE_DATE FROM PRODUCT P, PRODUCTION R WHERE P.PRODUCT_CODE = R.PRODUCT_CODE AND P.PRODUCT_DETAIL NOT LIKE '%MILK'

8.2.5 SPECIAL OPERATOR

a) List the cow id and age of the cow that was taken care by employee that was hired between 2015 and 2017

27 | P a g e

- SELECT C.COW_ID, C.AGE FROM COW C, EMPLOYEE E WHERE C.EMPLOYEE_ID = E.EMPLOYEE_ID AND E.EMP_HIREDATE BETWEEN '201501-01' AND '2017-12-31'

b) List the manufacture date, product name and expire date of product A004, A009, and A013 - SELECT R.MANUFACTURE_DATE, P.PRODUCT_DETAIL, P.EXP_DATE FROM PRODUCTION R, PRODUCT P WHERE P.PRODUCT_CODE = R.PRODUCT_CODE AND PRODUCT_CODE IN (‘A004’,’A009’,’A013’)

c) List employee name that work where their name starts with the letter ‘A’ - SELECT NAME FROM EMPLOYEE WHERE NAME LIKE 'A%'

28 | P a g e

8.2.6 SCALAR FUNCTION AND ARITHMETIC

a) Milk produce by worker that milk after 13th October 2019 is increased by 20%. List the new amount of milk, employee name, and the milk date. Name the derived column as NEW_AMOUNT. Sort by amount of milk descending. - SELECT E.NAME, M.MILK_TIME, DECIMAL(M.VOLUME_MILK * 1.2,4,2) AS NEW_AMOUNT FROM EMPLOYEE E,MILKER M WHERE E.EMPLOYEE_ID = M.EMPLOYEE_ID ORDER BY M.VOLUME_MILK * 1.2 DESC

b) Manager want a list of product that last longer than 5 months. Provide product name, manufacture date and expire date. - SELECT DISTINCT P.PRODUCT_DETAIL, R.MANUFACTURE_DATE, P.EXP_DATE FROM PRODUCT P, PRODUCTION R WHERE P.PRODUCT_CODE

29 | P a g e

= R.PRODUCT_CODE AND MONTH(P.EXP_DATE - R.MANUFACTURE_DATE) > 5

c) The number of reject product that was produce by North Barn has increased by 5 for every item. Display the product code and the new number of the reject product as NEW_REJECT. Sort by new reject. - SELECT P.PRODUCT_CODE, R.REJ_QUANTITY + 5 AS NEW_REJECT FROM PRODUCT P, PRODUCTION R, BARN B WHERE P.PRODUCT_CODE = R.PRODUCT_CODE AND R.BARN_NO = B.BARN_NO AND B.BARN_DESC = 'NORTH BARN' ORDER BY (R.REJ_QUANTITY + 5)

8.2.7 COLUMN FUNCTION AND GROUPING

30 | P a g e

a) Display the total volume of milk that was collected on each day before 13 October 2019. In addition, show the milk date. Order by sum. - SELECT SUM(M.VOLUME_MILK) AS SUM, M.MILK_TIME FROM EMPLOYEE E, MILKER M WHERE MILK_TIME < '2019-10-13' AND E.EMPLOYEE_ID = M.EMPLOYEE_ID GROUP BY MILK_TIME ORDER BY SUM

b) Manager want a list of employee name that was assigned to more than 3 cows. - SELECT NAME FROM EMPLOYEE WHERE EMPLOYEE_ID IN (SELECT EMPLOYEE_ID FROM COW GROUP BY EMPLOYEE_ID HAVING COUNT(*) > 3)

c) Manager want a list of total employee that worked as fodder where their age is more than 30. Group the result by barn number and number of employee. - SELECT F.BARN_NO, COUNT(*) AS NUM_EMPLOYEE FROM EMPLOYEE E, FOODER F WHERE E.EMPLOYEE_ID = F.EMPLOYEE_ID AND E.EMP_AGE > 30 GROUP BY F.BARN_NO

31 | P a g e

8.2.8 USING SUBQUERIES

a) Which employee milk more than average total of milk collected. Show employee number, employee name, and volume of milk. - SELECT E.EMPLOYEE_ID, E.NAME, M.VOLUME_MILK FROM EMPLOYEE E, MILKER M WHERE E.EMPLOYEE_ID = M.EMPLOYEE_ID AND M.VOLUME_MILK > (SELECT AVG(VOLUME_MILK) FROM MILKER)

b) Which employee collect the lowest amount of milk. Show the cow that the employee was assigned to. - SELECT C.COW_ID, E.NAME FROM COW C, EMPLOYEE E, MILKER M WHERE C.EMPLOYEE_ID = E.EMPLOYEE_ID AND E.EMPLOYEE_ID = M.EMPLOYEE_ID AND M.VOLUME_MILK = (SELECT MIN(VOLUME_MILK) FROM MILKER)

32 | P a g e

c) Manager need a list of employee name, id of employee that age is more than average of employee. The list should select only employee that was hired before 2015. - SELECT NAME, EMPLOYEE_ID ...