Case Study Unistorage PDF

Preview

Summary

Download Case Study Unistorage PDF

Description

FACULTY OF COMPUTER AND MATHEMATICAL SCIENCES

CS240 BACHELOR OF INFORMATION TECHNOLOGY (HONS.)

ICT 501 – DATABASE MANAGEMENT SYSTEM

CASE STUDY 1 UNISTORAGE

STUDENT NAME

MOHAMMED SYAFIQ BIN AFFANDY

STUDENT ID GROUP

2019311739

LECTURER

MUHAMMAD IQBAL BIN ISKAK 2019563735

MOHAMMAD NOR ASRIE RAFIZIE HAISYAM BIN MOHD FITRI BIN APINI ROSLI 2019219674 2019805086

RCS2403A DR. NORFIZA BINTI IBRAHIM

Question 1

Ignis Travel Sdn Bhd is a renowned holiday reservation company that provides services for booking of holiday package to popular tourists’ destinations in northern region of Malaysia. With its increasing popularity, there is urgent need to store important data pertaining to customers, reservations, holiday packages, payment, and testimonials from customers about their holiday and the package they chose. The company has already launched its website. The customers can browse the company's website to search or browse information of holiday package. Every holiday package has three different departure date that has been set by the company. To make reservation, the customer can either call the company via telephone, go directly to the headquarters, or fill up the online reservation form. In reservation, data that must be stored includes reservation number, reservation date, date of holiday package, and customerID. A customer may reserve as many packages as they wish, and each package can also be reserved by many customers. There are various themes for packages being offered, such as beach and resort holidays, theme park holidays, highland holidays, adventure and recreational, and medical tourism. Each theme can be assigned to many packages, while each package has only one theme. Every package has a predetermined length of stay (for example: 3 days 2 nights, or 4 days 3 nights etc.). The price for each package is determined based on length of stay. The data regarding location/state of the holiday destination must be kept (eg: Kedah, Perlis, Terengganu etc). Details of customers must include customerID, address, telephone number, and email. A customer needs to make payment for reservation worth 60% of the package price. The payment date and payment amount should be stored. A customer will pay twice, firstly during reservation day and secondly three days before departure date. Each payment is made by one customer. A customer also is given loyalty points when they give testimonials about any holiday package that they have experienced. A customer may give testimonial to more than one package, and each package receives testimonials from many customers. (Resolve Many-to-Many Relationships and add relevant attributes where necessary). ICT501 Assessment 2

Question 2

a) What is the Normal Form (NF) of the above relation? Elaborate your answers. (2 marks)

Answer: Unnormalized form (UNF) because there are repeating group in the table.

b) Determine the primary key for the given relation. (2 marks) Answer: Emp_No, Unit_Code

c) Draw the dependency diagram (3 marks) Answer:

d) Normalize the relation to the Third Normal Form (3NF) (8 marks)

Answer: UNF (Emp_No, Name, Post, Unit_Code, Unit_Name, Subject_Code, Subject_Name, Credit_Hr, Room_No})

First Normal Form (1NF) Full functional dependency Emp_No, Unit_Code Room_No

Name, Post, Unit_Name, Subject_Code, Subject_Name, Credit_Hr,

Partial dependency Emp_No Unit_Code

Name, Post Unit_Name, Subject_Code, Subject_Name, Credit_Hr

Transitive dependency Subject_Code

Subject_Name, Credit_Hr

Second Normal Form (2NF)

Partial dependency Emp_No Unit_Code

Name, Post Unit_Name, Subject_Code, Subject_Name, Credit_Hr

Remove partial dependency EMPLOYEE (Emp_No, Name, Post) UNIT (Unit_Code, Unit_Name, Subject_Code, Subject_Name, Credit_Hr)

Write a new table that consists of original composite key ROOM (Emp_No, Unit_Code, Room_No)

Third Normal Form (3NF)

Transitive dependency Subject_Code

Subject_Name, Credit_Hr

Remove transitive dependency SUBJECT (Subject_Code, Subject_Name, Credit_Hr)

Remove attribute from original table UNIT (Unit_Code, Unit_Name, Subject_Code, Subject_Name, Credit_Hr) UNIT (Unit_Code, Unit_Name, Subject_Code*)

Outcome: EMPLOYEE (Emp_No, Name, Post) UNIT (Unit_Code, Unit_Name, Subject_Code*) SUBJECT (Subject_Code, Subject_Name, Credit_Hr) ROOM (Emp_No, Unit_Code, Room_No)...