1622 - Assignment 1 - Bui Ngoc Vinh Khanh PDF

Preview

Summary

Download 1622 - Assignment 1 - Bui Ngoc Vinh Khanh PDF

Description

ASSIGNMENT 1 FRONT SHEET Qualification

BTEC Level 5 HND Diploma in Computing

Unit number and title

Unit 04: Database Design & Development

Submission date

18/02/2022

Re-submission Date

Date Received 1st submission Date Received 2nd submission

Student Name

Bui Ngoc Vinh Khanh

Student ID

GCC200001

Class

GCC0902

Assessor name

Nguyen Hung Dung

Student declaration I certify that the assignment submission is entirely my own work and I fully understand the consequences of plagiarism. I understand that making a false declaration is a form of malpractice. Student’s signature Grading grid

P1

M1

D1

Bui Ngoc Vinh Khanh

 Summative Feedback:

Grade: Signature & Date:

 Resubmission Feedback:

Assessor Signature:

Date:

Assignment Brief 1 (RQF) Higher National Certificate/Diploma in Computing Student Name/ID Number: Unit Number and Title:

Unit 04: Database Design & Development

Academic Year:

2022

Unit Assessor:

Tran Trong Minh

Assignment Title:

Database design

Issue Date:

18 Jan 2022

Submission Date: Internal Verifier Name: Date:

Submission Format:

Format: ●

The submission is in the form of an individual written report. This should be written in a concise, formal business style using single spacing and font size 12. You are required to make use of headings, paragraphs and subsections as appropriate, and all work must be supported with research and referenced using the Harvard referencing system. Please also provide a bibliography using the Harvard referencing system.

Submission ●

Students are compulsory to submit the assignment in due date and in a way requested by the Tutor.

●

The form of submission will be a soft copy posted on http://cms.greenwich.edu.vn/.

●

Remember to convert the word file into PDF file before the submission on CMS.

Note: ● ●

The individual Assignment must be your own work, and not copied by or from another student. If you use ideas, quotes or data (such as diagrams) from books, journals or other sources, you must reference your sources, using the Harvard style.

●

Make sure that you understand and follow the guidelines to avoid plagiarism. Failure to comply this requirement will result in a failed assignment.

Unit Learning Outcomes:

LO1 Use an appropriate design tool to design a relational database system for a substantial problem

Assignment Brief and Guidance:

You are employed as a Database Developer for a large IT consultancy company. The company has been approached by FPT Shop which is expanding due to the growth of the number of stores. FPT Shop is currently facing difficulties in dealing with managing the database from all shops on over country. It decided to develop a new database so that: users can register with their phone numbers as IDs and order or rate, comment for their bought devices, shop managers can take care for their stores and director board can view all data from all shops. You are tasked to select one of those systems to develop database for FPT Shop. Your tasks are to:

•

Work with FPT Shop to find out about current requirements for each system

•

Analyse the requirements and produce clear statements of user and system requirements.

•

Design a relational database system using appropriate design tools and techniques

•

Develop a fully functional relational database system, based on an existing system design.

•

Test the system against user and system requirements.

•

Produce technical and user documentation

Part 1 (Assignment 1) Before you start the development process, your manager has asked you to produce a report for the CEO of FPT, containing:

1. Clear statements of user and system requirements. The system must have at least 3 user roles, including business processes and statistical reports for FPT Shop managers. 2. The design of the relational database system using appropriate design tools and techniques. It should contain at least four interrelated tables. You would prefer to produce a more detailed document, so you will produce a comprehensive design for a fully functional system which will include interface and output designs, data validations and cover data normalization. Your manager would like on the report your assessment of the effectiveness of the design in relation to user and system requirements.

Learning Outcomes and Assessment Criteria (Assignment 1): Learning Outcome

Pass

Merit

Distinction

LO1

P1 Design a relational database system using appropriate design tools and techniques, containing at least four interrelated tables, with clear statements of user and system requirements.

M1 Produce a comprehensive design for a fully functional system which includes interface and output designs, data validations and data normalisation.

D1 Assess the effectiveness of the design in relation to user and system requirements.

TABLE OF CONTENTS I.

STATEMENTS OF USER AND SYSTEM REQUIREMENTS ........................................................................... 5 1.

Statements of user .............................................................................................................................. 5

2.

System Requirement Specification ..................................................................................................... 5

II.

DESIGN THE RELATIONAL DATABASE SYSTEM ........................................................................................ 5 1.

Logical design (ERD) ............................................................................................................................. 5

2.

ER diagram ........................................................................................................................................... 8

III. DATA VALIDATION AND NORMALIZATION ............................................................................................. 9 1.

Data validation .................................................................................................................................... 9

2.

Nomalization .....................................................................................................................................10

3.

Physical design................................................................................................................................... 10

4.

Interface of system: ........................................................................................................................... 13

REFERENCES.................................................................................................................................................. 15

ASSIGNMENT I.

STATEMENTS OF USER AND SYSTEM REQUIREMENTS 1. Statements of user A company is having some problems managing the purchases in their game store. Knowing that you are a talented database designer and manager, they decided to hire you to create a database to help manage the purchase and sale of game discs for that store. Customers can order game discs by filling out an order, each customer has a unique ID, orders also have their own ID to distinguish them and they are managed by the store's staff.

2. System Requirement Specification

II.

-

Reasons for the project: A company is having some problems managing the purchases in their game store and they need to create a database to help manage the purchase and sale of game discs for that store.

-

Requirements and Expectation: The created database needs to meet basic criteria such as easy access, storage, management and updating. The database also needs to be easily accessed and ensure the security of the database. At the same time, they must meet all the requirements that the scenario gives.

-

Objectives (Things intended to achieve): The database needs to ensure enough features such as adding, deleting and editing data, as well as allowing users to quickly and easily access it, and also ensure the safety of the database.

DESIGN THE RELATIONAL DATABASE SYSTEM 1. Logical design (ERD) -

Identify entities: No.

Entity

Description

1

Customer

Contains customer information

2

Orders

Contains order information

3

Staff

Contains staff information

4

Games

Contains game information

5

OrderDetail

Contains detail of order information

-

-

Identify relationships: Entity

Multiplicity

Relationship

Multiplicity

Entity

Customer

1

Order

n

Orders

Orders

1

Has

n

OrderDetail

Staff

1

Manage

n

Orders

Games

1

Has

n

OrderDetail

Identify and associate attributes with entities: Customer Entity No.

Attribute

Description

1

CustomerID

Contains customer ID

2

CustomerName

Contains customer name

3

CustomerPhone

Contains customer phone

4

CustomerAddress

Contains customer address

Primary Key: CustomerID

Orders Entity No.

Attribute

Description

1

CustomerID

Contains customer ID

2

OrderID

Contains order ID

3

StaffID

Contains staff ID

4

OrderDate

Contains order date

5

DeliveryDate

Contais delivery date

Primary Key: OrderID

Staff Entity No.

Attribute

Description

1

StaffID

Contains staff ID

2

StaffName

Contains staff name

3

StaffPhone

Contains staff phone Primary Key: StaffID

OrderDetail Entity No.

Attribute

Description

1

GameID

Contains game ID

2

OrderID

Contains order ID

3

Quantity

Contains quantity of order

Primary Key: OrderID and GameID

Games Entity No. 1 2 3 4 5

Attribute GameID GameName Price Description Quantity

Description Contains game ID Contains order ID Contains game price Contains description about game Contains game quantity Primary Key: GameID

2. ER diagram

Explanation about ER diagram: The diagram above has 5 entities that have a relationship with each other and in which, the OrderDetail entity is an entity separated from the relationship between the 2 entities Orders and Games. The above entities all have the attributes listed above. There are 4 relationships between 5 entities: -

Customer and Orders (1 - n): 1 customer can place multiple orders but those orders can only be ordered by 1 customer.

-

Staff and Orders (1 - n): 1 staff can manage multiple orders but those orders can only be managed by 1 staff.

-

Games and OrderDetail (1 – n): A game can belong to more than 1 line of order detail if that game is ordered more than 1 order, but each line in the order detail is only related to that game

-

Orders and OrderDetail (1 - n): An orders can belong to more than 1 line of order detail if the orders contains more than 1 game, but each line in the order detail is only related to one order.

III.

DATA VALIDATION AND NORMALIZATION 1. Data validation Customer Table No.

Field name

Data type

Constraint

Description

1

CustomerID

char(10)

Primary Key

Customer unique ID

2

CustomerName

varchar(30)

Not Null

Customer name

3

CustomerPhone

int

Not Null

Customer phone number

4

CustomerAddress

varchar(100)

Customer address

Games Table No.

Field name

Data type

Constraint

Description

1

GameID

char(10)

Primary Key

Game unique ID

2

GamerName

varchar(50)

Not Null

Game name

3

Price

int

Not Null

Price of game

4

Quantity

int

Not Null

Game quantity

5

Description

varchar(500)

Description about game

Staff Table No.

Field name

Data type

Constraint

Description

1

StaffID

char(10)

Primary Key

Staff unique ID

2

StaffName

Varchar(30)

Not Null

Staff name

3

StaffPhone

int

Not Null

Staff phone number

Orders Table No.

Field name

Data type

Constraint

Description

1

OrderID

char(10)

Order unique ID

2

CustomerID

char(10)

3

StaffID

char(10)

4

OrderDate

datetime

Primary Key Foreign Key of Customer Table Foreign Key of Staff Table Not Null

5

DeliveryDate

datetime

Not Null

Customer unique ID Staff unique ID Order Date Delivery Date

OrderDetail Table No.

Field name

Data type

Constraint

Description

1

OrderID

char(10)

Primary Key

Order unique ID

2

GameID

char(10)

Primary Key

Game unique ID

3

Quantity

int

Not Null

Order quantity

2. Nomalization The above database is 3NF normalized, I don't need to do normalization anymore.

3. Physical design -

Create database:

-

Create CUSTOMER table:

-

Create GAMES table:

-

Create STAFF table:

-

Create ORDERS table:

-

Create ORDERDETAIL table:

-

Create Database diagram:

4. Interface of system: In terms of interface, I don't focus much on aesthetics. The interface is divided into 2 parts, 1 part for Customers and the rest for Staff. Anyone can access the Customer section to order products. Unlike the Customer section, only the Staff who work at the store can access the Staff section.

In the Customer interface, customers can enter their personal and game information and the amount they want. The data will be displayed on the Staff interface so that the staff can manage them.

REFERENCES greenwich, 2022. flm.greenwich.edu.vn. [Online] Available at: https://flm.greenwich.edu.vn/gui/role/student/SyllabusDetails?sylID=2597 [Accessed 18 2 2022]....