AIS275 Group Project - Database for Microsoft Access Assignment PDF

Preview

Summary

Download AIS275 Group Project - Database for Microsoft Access Assignment PDF

Description

FACULTY OF ACCOUNTANCY DATA MANAGEMENT (AIS275)

A4AC120 4C

PREPARED BY: NAME

STUDENT ID

NUR SUFIAH MUHAMMAD TAUFIK

2019427696

DINIE SORFINA FATHANAH BINTI KAMARUL ARIFFIN

2019279284

NUR IZZATUL NAJWA BINTI AHMAD BUKHARI

2019204034

SITI NUR ANIS HASLINDA BINTI SAMSURI

2019273864

MUHAMMAD ZUHAIR BIN MOHD ZAID

2018258576

PREPARED FOR : SIR MUHAMMAD IQBAL MOHAMED AZHARI Submission Date: 24/7/2021

ACKNOWLEDGEMENT

Alhamdulillah, We would like to express our greatest gratitude to the Almighty for giving us the strength and patience to complete the task given by our AIS275 lecturer, Sir Muhammad Iqal Mohamed Azhari. It’s a relieved that we are able to complete the assignment during the time period given. Eventhough a few problems had occured during the completion of this assignment, we are happy that we are able to solve the problems wisely and effectively as a group. Besides that, we would also like to show appreciation to our beloved lecturer Sir Muhammad Iqal Mohamed Azhari, because without him, it is almost impossible for us to complete the assignment properly. We really appreciate all the guidances and motivations that he had gave to us throughout the completion of the assignment. On the other hand, we would also like to thank our dearly loved parents. They had been our pillar of strength since the start of the assignment till’ we are able to accomplish the task given. Although they did not help us physically, they had been helping us emotionally with their wise words of motivation. We are sincerely thankful for that. Last but certainly not least, we would like to express a sincere gratitude to our much loved friends who would always be there to help us complete the assignment with all effort and responsibility. We really hope that all the efforts and dedication that we had put in completing the assignment will give benefits and good result in our group assignment. We are sincerely thankful for all the assistance and support that had been given to us whether it is physically or emotionally.

ii

TABLE OF CONTENT CONTENT

PAGE

Acknowledgement

ii

Table of content

iii

1.0 Introduction 2.0 Case Study 2.1 Background 2.2 Current System Description 2.3 Current System Problem 2.4 Suggestion Solution 2.5 System Objective

1-3

3.0 ERD Narrative

4-6

4.0 Database Conceptual Design 4.1 Entity Relationship Diagram 4.2 Relational Diagram/Database Model (Relational Schema) 4.3 3NF Table Structure 5.0 Database System Implementation 5.1 Data Definition Language (DDL) 5.1.1 Create Table Statement 5.1.2 Create View Single Table 5.1.3 Create View Multiple Table 5.1.4 Create View Single Table (updateable) 5.1.5 Create View Multiple Table (updateable) 5.1.6 Drop Table Statement 5.1.7 Alter Table Statement - ADD column - DROP column - ALTER column 5.2 Data Manipulation Language (DML) 5.2.1 Insert Into Table Statement 5.2.2 Insert Into View (updateable) 5.2.3 Update Table Statement 5.2.4 Update View (updateable) 5.2.5 Delete Table Statement 5.2.6 SELECT Statement with Minimum 20 Records for All Tables (question, query statement, output) - SELECT Queries (approximately 1 query for each operator) a. Comparison Operator b. Logical Operator c. Special Operator (LIKE, BETWEEN, DISTINCT) d. Arithmetic Function (*, /, +, -) e. Column Function and Grouping f. Type I Nested Queries (IN, NOT IN) g. Type II Nested Queries (EXIST, NOT EXIST) h. Retrieve Data from Multiple Table (5 queries) 6.0 System Implementation (visual basic application)

7

8-16

17-18

7.0 Conclusion

19

8.0 References

20

iii

1.0 INTRODUCTION Dyjewell Company does an online business which is selling online jewellery. Customers will make a purchase of the product by phone call or through a messaging application. This can cause hassle to both parties. Before this, if the customer wanted to purchase a product from the company, they would need to make a phone call or make an order through text messages. This will also be time consuming because of the process they took plus the work done by the workers is increasing. However, with our new database system, the customer can make their purchase easily using the system and it will be easier for the staff to take orders and observe their sales.

2.0 CASE STUDY

2.1 Background Dyjewll company is an online business that sells earrings, necklaces and bracelets. This company has been operated since 1 March 2020. Starting their business as a small company, Dyjewll has evolved to be one of the most successful online jewellry shop in the market.

2.2 Current System Description The current system used by the company is time consuming and ineffective. The customer who wishes to make an order of the product must make a phone call or through text messages and the staff will take their order. Then, the staff will pack their order and have it sent to the customer.

2.3 Current System Problem The problem that arises from the current system is that it will take time for the customer to get hold of the staff in charge to make an order since it is done via phone call. Other than, affecting their monthly sales. The work done by the staff also increases.

1

2.4 Suggestion Solution

Planning By using the new database system created, the customer can easily make an order by filling up the online form provided by the company. The customer will fill in the form as below:•

Customer’s name

•

Customer’s address

•

Customer’s phone number The customer can click on the product they wanted to and the data will be recorded in

the company’s system as soon as the customer has made the purchase. The staff can also keep track of the order efficiently since it will be stored in one database system. This process will be less time consuming for both parties and effective for the company to observe their monthly sales.

Analysis The system used before is being upgraded to a new system that can make the customer and staff work easier and faster. It is also a customer friendly system where the customer can order the jewellery without any problem.

Implementation Implementation is when the database is being installed which is to be used in Dyjewell Company to improve their system to be more systematic and easier to record customer’s data. The application that is being installed will be published on Dyjewell Company’s website and from there, customers can make their order to purchase the jewellery.

2

Maintenance Adaptive maintenance which has been affected by a change that occurred in some other part of the system is the implementation of changes in a part of the system. Changes in the environment such as the hardware or the operating system is the adapting software that have in adaptive software. The conditions and the influences which act from outside on the system refer to the terms of environment. In addition to the activities to increase the system’s performance, perfective maintenance is involved in making functional enhancements to the system even when the changes have not been suggested by faults and it also mainly deals with changed user requirements or implementing new requirements. In day-today system functions, corrective maintenance deals with the repair of faults or defects. Errors in software design, coding and logic are the result of the defect. Usually, the need for corrective maintenance is initiated by bug reports drawn by the users.

3

3.0 ERD NARRATIVE

Entity Name: AGENT Explanation: Record data of Agent Attribute’s Name

Explanation/content

Data Type

Format Required PK (Not or Null) FK

AGT_ID

Agent ID

char(10)

Xxxxx

AGT_Name

Agent Name

Varchar(50) Xxxxx

AGT_Email

Agent Email

Varchar(30) Xxxxx

AGT_Phone

Agent’s Phone Number

Varchar(10) Xxxxx

Yes

FK reference table

PK

Entity Name: CUSTOMER Explanation: Record data of Customer Attribute’s Name

Explanation/content Data Type

Format Required PK (Not or Null) FK

CUST_ID

Customer ID

Varchar(10)

Xxxxx

CUST_Name

Customer Name

Varchar(50)

Xxxxx

CUST_Add

Customer Address

Varchar(50)

Xxxxx

CUST_Phone

Customer’s Phone Number

Varchar(10)

Xxxxx

Yes

FK reference table

PK

4

Entity Name: ORDER Explanation: Record data of Sales Order Attribute’s Name

Explanation/content

Data Type

Format Required PK (Not or Null) FK

ORDER_Num

Order Number

Varchar(20)

Xxxxx

ORDER_Date

Order Delivery Date

Date

Ddmmyyyy

CUST_ID

Customer ID

Varchar(30)

Xxxxx

FK

CUSTOMER

AGT_ID

Agent ID

Char(10)

Xxxxx

FK

AGENT

Yes

FK reference table

PK

Entity Name: PRODUCT Explanation: Record data of Product Attribute’s Name

Explanation/cont ent

Data Type

Format Required PK (Not or Null) FK

PROD_Code

Product Code

Varchar(15)

Xxxxx

PROD_Quantity

Product Quantity

Varchar(10)

Xxxxx

PROD_Type

Product Type

Char(10)

Xxxxx

VEN_Code

Vendor Code

Varchar(15)

Xxxxx

FK

VENDOR

AGT_ID

Agent ID

Char(10)

Xxxxx

FK

AGENT

Yes

FK reference table

PK

5

Entity Name: VENDOR Explanation: Record data of Vendor Attribute’s Name

Explanation/content Data Type

Format Required PK (Not or Null) FK

VEN_Code

Vendor Code

Char(10)

Xxxxx

VEN_Name

Vendor Name

Varchar(20) Xxxxx

VEN_Address

Vendor Address

Varchar(50) Xxxxx

VEN_Email

Vendor Email

Varchar(30) Xxxxx

VEN_Phone

Vendor Phone Number

Varchar(10) Xxxxx

Yes

FK reference table

PK

6

4.0 DATABASE CONCEPTUAL DESIGN

4.1 Entity Relationship Diagram

4.2 Relational Diagram/ Database Model (Relational Schema) CUSTOMER (CUST_ID, CUST_Name, CUST_Add, CUST_Phone) ORDER (ORDER_Num, ORDER_Date, CUST_ID*, AGT_ID*) AGENT (AGT_ID, AGT_Name, AGT_Email, AGT_ID*) PRODUCT (PROD_Code, PROD_Quantity, VEN_Code*) VENDOR (VEN_Code, VEN_Name, VEN_Add, VEN_Email, VEN_Phone) 4.3 3NF Table Structure CUSTOMER (CUST_ID, CUST_Name, CUST_Add, CUST_Phone) ORDER (ORDER_Num, ORDER_Date, CUST_ID*, AGT_ID*) AGENT(AGT_ID, AGT_Name, AGT_Email, AGT_Phone) PRODUCT(PROD_Code, PROD_Quantity, VEN_Code*) VENDOR(VEN_Code, VEN_Name, VEN_Address, VEN_Email, VEN_Phone) NECKLACES (PROD_Code, Quantity) BRACELETS (PROD_Code, Quantity) EARRINGS (PROD_Code, Quantity)

7

5.0 DATABASE SYSTEM IMPLEMENTATION

5.1 Data Definition Languages (DDL) 5.1.1

Create Table Statement

CREATE TABLE AGENT( AGT_ID CHAR(10) NOT NULL UNIQUE, AGT_NAME VARCHAR(20) NOT NULL, AGT_EMAIL VARCHAR(30) NOT NULL, AGT_PHONE VARCHAR(10) NOT NULL, PRIMARY KEY (AGT_ID)); CREATE TABLE CUSTOMER( CUST_ID VARCHAR(10) NOT NULL UNIQUE, CUST_NAME VARCHAR(20) NOT NULL, CUST_ADD VARCHAR(50) NOT NULL, CUST_PHONE VARCHAR(10) NOT NULL, PRIMARY KEY (CUST_ID)); CREATE TABLE ORDER( ORDER_NUM VARCHAR(20) NOT NULL UNIQUE, ORDER_DATE DATE NOT NULL, CUST_ID VARCHAR(10) NOT NULL, AGT_ID CHAR(10) NOT NULL, PRIMARY KEY (ORDER_NUM), FOREIGN KEY (AGT_ID) REFERENCES AGENT (AGT_ID), FOREIGN KEY (CUST_ID) REFERENCES CUSTOMER (CUST_ID)); CREATE TABLE PRODUCT( PROD_CODE VARCHAR(15) NOT NULL UNIQUE, PROD_QUANTITY VARCHAR(10) NOT NULL, PROD_TYPE CHAR(10) NOT NULL, VEN_CODE VARCHAR(15) NOT NULL, AGT_ID CHAR(10) NOT NULL, PRIMARY KEY (PROD_CODE), FOREIGN KEY (VEN_CODE) REFERENCES VENDOR (VEN_CODE), FOREIGN KEY (AGT_ID) REFERENCES AGENT (AGT_ID)); CREATE TABLE VENDOR( VEN_CODE CHAR(10) NOT NULL UNIQUE, VEN_NAME VARCHAR(20) NOT NULL, VEN_ADD VARCHAR(50) NOT NULL, VEN_EMAIL VARCHAR(30) NOT NULL, VEN_PHONE VARCHAR(10) NOT NULL, PRIMARY KEY (VEN_CODE);

8

CREATE TABLE EARRINGS( PROD_CODE VARCHAR(15) NOT NULL UNIQUE, QUANTITY CHAR(5) NOT NULL, PRIMARY KEY (PROD_CODE), FOREIGN KEY (PROD_CODE) REFERENCES PRODUCT (PROD_CODE)); CREATE TABLE NECKLACE( PROD_CODE VARCHAR(15) NOT NULL UNIQUE, QUANTITY CHAR(5) NOT NULL, PRIMARY KEY (PROD_CODE), FOREIGN KEY (PROD_CODE) REFERENCES PRODUCT (PROD_CODE)); CREATE TABLE BRACELETS( PROD_CODE VARCHAR(15) NOT NULL UNIQUE, QUANTITY CHAR(5) NOT NULL, PRIMARY KEY (PROD_CODE), FOREIGN KEY (PROD_CODE) REFERENCES PRODUCT (PROD_CODE)); 5.1.2

Create View Single Table

Create a view customer ID where the customer ID is more than ‘1011’ CREATE VIEW CUST_ID AS SELECT CUST_ID, CUST_NAME, CUST_PHONE FROM CUSTOMER WHERE ((CUST_ID)>'1011'); 5.1.3

Create View Multiple Table

Create a view order number where agent ID is less than ‘A04’ CREATE VIEW ORDER_NUM AS SELECT ORDER.AGT_ID, AGENT.AGT_NAME, ORDER.ORDER_NUM FROM AGENT INNER JOIN [ORDER] ON AGENT.AGT_ID = ORDER.AGT_ID WHERE (((ORDER.AGT_ID)'1011'); 5.1.5

Create View Multiple Table (updateable)

SELECT ORDER.AGT_ID, AGENT.AGT_NAME, ORDER.ORDER_NUM FROM [ORDER], AGENT WHERE (((ORDER.AGT_ID)...