Docsity
Docsity
Prepare for your exams
Prepare for your exams

Study with the several resources on Docsity

Earn points to download
Earn points to download

Earn points by helping other students or get them with a premium plan

Guidelines and tips
Guidelines and tips
Sell on Docsity
Sell on Docsity
Docsity AI
Docsity AI
Log inSign up

Prepare for your exams

Study with the several resources on Docsity

Find documents

Prepare for your exams with the study notes shared by other students like you on Docsity

Search for your university

Find the specific documents for your university's exams

Docsity AINEW

Summarize your documents, ask them questions, convert them into quizzes and concept maps

Explore questions

Clear up your doubts by reading the answers to questions asked by your fellow students

Earn points to download

Earn points by helping other students or get them with a premium plan

Share documents
download point icon20 Points

For each uploaded document

Answer questions
download point icon5 Points

For each given answer (max 1 per day)

All the ways to get free points
Get points immediately

Choose a premium plan with all the points you need

Study Opportunities

Choose your next study program

Get in touch with the best universities in the world. Search through thousands of universities and official partners

Community

Ask the community

Ask the community for help and clear up your study doubts

Free resources

Our save-the-student-ebooks!

Download our free guides on studying techniques, anxiety management strategies, and thesis advice from Docsity tutors

BTEC Database Design and Development, Essays (university) of Database Management Systems (DBMS)

FPT University (FPTU)Database Management Systems (DBMS)

Assignment number 1 - Database Design and Development in BTEC Higher national diplomat in Computing

Typology: Essays (university)

2021/2022

Uploaded on 07/12/2022

Vu-Nguyen-73
Vu-Nguyen-73 🇻🇳

4.2

(5)

7 documents

1 / 41

Toggle sidebar

This page cannot be seen from the preview

Don't miss anything!

bg1
PROGRAM TITLE: HIGHER NATIONAL DIPLOMA IN COMPUTING
UNIT TITLE: DATABASE DESIGN AND DEVELOPMENT
ASSIGNMENT NUMBER: 01
ASSIGNMENT NAME: ASSIGNMENT BKC
SUBMISSION DATE: 30/5/2022
DATE RECEIVED: 31/5/2022
TUTORIAL LECTURER:
WORD COUNT: 3536
STUDENT NAME:
STUDENT ID:
MOBILE NUMBER:
0
pf3
pf4
pf5
pf8
pf9
pfa
pfd
pfe
pff
pf12
pf13
pf14
pf15
pf16
pf17
pf18
pf19
pf1a
pf1b
pf1c
pf1d
pf1e
pf1f
pf20
pf21
pf22
pf23
pf24
pf25
pf26
pf27
pf28
pf29

Partial preview of the text

Download BTEC Database Design and Development and more Essays (university) Database Management Systems (DBMS) in PDF only on Docsity!

PROGRAM TITLE: HIGHER NATIONAL DIPLOMA IN COMPUTING

UNIT TITLE: DATABASE DESIGN AND DEVELOPMENT

ASSIGNMENT NUMBER: 0 1

ASSIGNMENT NAME: ASSIGNMENT BKC

SUBMISSION DATE: 30/5/

DATE RECEIVED: 31/5/

TUTORIAL LECTURER:

WORD COUNT: 3536

STUDENT NAME:

STUDENT ID:

MOBILE NUMBER:

Summative Feedback: Internal verification:

  • TABLE OF CONTENTS...........................................................................................................
  • TABLE OF FIGURES...............................................................................................................
  • INTRODUCTION......................................................................................................................
  • PART A: BASIC DATABASE THEORY................................................................................
      1. What are Relational database management systems..........................................................
      • Definition...........................................................................................................................
      • Applications of database management systems:................................................................
      • MySql.................................................................................................................................
      • Oracle database...................................................................................................................
      • SQL Server.........................................................................................................................
      1. Entity Relationship Diagram (ERD)..................................................................................
      1. Some tools for drawing ERD.............................................................................................
      • Lucidchart...........................................................................................................................
      • Draw.io.............................................................................................................................
      • DBDiagram.io..................................................................................................................
  • PART B: ANALYSIS OF THE BKC PROBLEM..................................................................
      1. Problem: BKC company data management system.........................................................
      1. The BKC database needed data representation................................................................
      • Employee..........................................................................................................................
      • Customer..........................................................................................................................
      • Product.............................................................................................................................
      • Manufacturer....................................................................................................................
      • Import...............................................................................................................................
      • Sale...................................................................................................................................
      • Sale_detail........................................................................................................................
      • Import detail.....................................................................................................................
      1. Entities relationships in the BKC database......................................................................
      • Customer and Sale............................................................................................................
      • Employee and Sale...........................................................................................................
      • Sale and Sale detail...........................................................................................................
      • Manufacturer and Import..................................................................................................
      • Product and Import detail.................................................................................................
      • Import and Import detail...................................................................................................
      • Product and Sale detail.....................................................................................................
      • Manufacturer and Product................................................................................................
  • PART C: THE DATABASE DESIGN....................................................................................
      1. Choosing ERD drawing tool: Lucidchart.........................................................................
      1. The ERD for the BKC database.......................................................................................
      1. Tables structure of the database.......................................................................................
      • 3.1 employee....................................................................................................................
      • 3.2 customer.....................................................................................................................
      • 3.3 sale..............................................................................................................................
      • 3.4 product........................................................................................................................
      • 3.5 manufacturer...............................................................................................................
      • 3.6 import.........................................................................................................................
      • 3.7 sale_detail...................................................................................................................
      • 3.8 import_detail..............................................................................................................
      • 3.9 Database normalization..............................................................................................
      • 3.10 Database normalization purpose:.............................................................................
      • 3.11 List of database normal forms..................................................................................
      1. A Relational model...........................................................................................................
  • PART D: THE DATABASE IMPLEMENTATION...............................................................
      1. Choosing RDBMS............................................................................................................
      1. Implementing the database...............................................................................................
      • Creating the database........................................................................................................
      • Creating the tables............................................................................................................
      1. The database visualization................................................................................................
      1. Implement a query language into our relational database................................................
      • INSERT............................................................................................................................
      • SELECT...........................................................................................................................
      • UPDATE..........................................................................................................................
      • DELETE...........................................................................................................................
      • INNER JOIN....................................................................................................................
  • PART E: TESTING THE DATABASE..................................................................................
      1. Test sets............................................................................................................................
      • Set 1:.................................................................................................................................
      • Set 2:.................................................................................................................................
      • Set 3:.................................................................................................................................
      • Set 4:.................................................................................................................................
      • Set 5:.................................................................................................................................
      • Set 6:.................................................................................................................................
      • Set 7:.................................................................................................................................
      1. Test cases..........................................................................................................................
  • Part F: TECHNICAL AND USER MANUAL........................................................................
      1. System requirement..........................................................................................................
      1. Installation manual...........................................................................................................
  • REFERENCES.........................................................................................................................
  • Figure 1: MySQL (source: Internet)........................................................................................... TABLE OF FIGURES
  • Figure 2: Oracle database...........................................................................................................
  • Figure 3: Microsoft SQL Server.................................................................................................
  • Figure 4: Lucidchart UI............................................................................................................
  • Figure 5: Draw.io UI................................................................................................................
  • Figure 6: DBDiagram.io UI......................................................................................................
  • Figure 7: ERD of the BKC database........................................................................................
  • Figure 8: Example of the 1NF..................................................................................................
  • Figure 9: Example of the 2NF..................................................................................................
  • Figure 10: Relational model of the BKC database...................................................................
  • Figure 11: PHPMyAdmin........................................................................................................
  • Figure 12: The BKC database visualization.............................................................................
  • Figure 13: Insert and Select queries of table customer..........................................................
  • Figure 14: Insert and Select queries of table product............................................................
  • Figure 15: Insert and Select queries of table employee.........................................................
  • Figure 16: Customer id 4 before the update.............................................................................
  • Figure 17: Customer id 4 after the update................................................................................
  • Figure 18: Delete query result..................................................................................................
  • Figure 19: Inner Join result......................................................................................................
  • Figure 20: XAMPP installation................................................................................................
  • Figure 21: Installation - Step 6.................................................................................................
  • Figure 22: Installation - Step 7.................................................................................................
  • Figure 23: Installation - Step 8.................................................................................................
  • Figure 24: Installation - Step 9.................................................................................................
  • Figure 25: XAMPP running interface......................................................................................

INTRODUCTION

BKC is a local double-glazing company, that has approached my company as they are rapidly expanding. New housing developments, as a result of the government’s program of regenerating the old disused factory complexes outside the city of Sheffield, have caused a huge increase in the demand for their products and services from building contractors. This document is my solution to their problems.

PART A: BASIC DATABASE THEORY

1. What are Relational database management systems Definition Before tackling the problem, we will need to quickly understand what a Database management system is. A database management system (DBMS) is a package that has tools for users to retrieve and manage data in a database. And a Relational database management system (RDBMS) does the same things as above, but it stores data in the form of tables. Most RDBMSs use Structured Query Language (SQL). Applications of database management systems:  Organizing data  Serving as an interface between an end-user and a database  Improving data access  Creating stronger relationships between entities in the database  Increasing data security within the databases  Creating data backups and error snapshots MySql MySQL is a relational database management system. It is one of the most well-known open- source database management. Most of the time, MySQL is used for web-based application

Figure 3 : Microsoft SQL Server

2. Entity Relationship Diagram (ERD) An entity-relationship diagram (ERD), also known as an entity-relationship model, is a graphical representation that depicts relationships among people, objects, places, concepts, or events within an information technology (IT) system. An ERD uses data modeling techniques that can help define business processes and serve as the foundation for a relational database. (Biscobing, n.d.) 3. Some tools for drawing ERD (Opinaldo, 2022) Lucidchart Lucidchart is a cloud-based tool that helps users sketch and share flowchart diagrams, providing designs for anything. It is pretty well-known and one of the best tools for creating ERD.  Easy to use  Cloud-based  Can import existing files into your workspace  Good price

Figure 4 : Lucidchart UI Draw.io This is a web-based tool that allows you to create entity relationship diagrams without registration required. Users can choose the location where to save the diagrams (e.g: Google Drive, Dropbox, or computer hard drive). Over and above, users may continue editing diagrams with integrated productivity programs like Jira and Confluence.  Easy to use  Available in many languages  Can be integrated into Jira and Confluence

Figure 6 : DBDiagram.io UI

PART B: ANALYSIS OF THE BKC PROBLEM

1. Problem: BKC company data management system The BKC company needs a make from scratch system to manage its business. Their two main problems are:  Its employees’ information and their tasks are not recorded  Information about its lead customers did not well manage including their purchases from the company  Information about manufacturers who BKC buy products from is not recorded 2. The BKC database needed data representation To handle the problems of BKC company, we must transform these real-life data into database entities

Employee Needed information about a BKC employee Attribute Description ID (^) Employee id Name (^) Employee name Phone (^) Employee phone number Email (^) Employee email Dob (^) Employee date of birth Position (^) Employee position Address (^) Employee address Customer Information about customers of the company Attribute Description ID Customer ID Name Customer name Phone Customer phone number Email Customer email Address Customer address Product Products data Attribute Description ID Product id Name Name of the product Import_date The date a product imported Import_price The import price of a product Sale_price The sale price of the product

Attribute Description ID Sale detail ID Product_ID The ID of the product Sale_ID The ID of the sale Quantity Number of products Import detail Detail of an imported report Attribute Description ID The ID of this imported detail Product_ID The ID of the imported product Imported_ID The ID of the imported Quantity Number of products

3. Entities relationships in the BKC database Customer and Sale A customer can buy many products from the BKC at different times. A sale can only belong to one customer. => The Customer and Sale relationship is: 1 – N Employee and Sale An employee can have many sale deals. A sale can only be closed by one sales employee. => The Employee and Sale relationship is: 1 - N Sale and Sale detail A sale can have many sale_detail records.

A sale detail can only belong to one sale. => The Sale and Sale detail relationship is: 1 - N Manufacturer and Import A manufacturer can sell supplies for the company many times. An import can only originate from one manufacturer => The Manufacturer and Import relationship is: 1 - N Product and Import detail A product can be imported many times, each with a different import detail record. An import detail record can only hold one product's information. => The Product and Import detail relationship is: 1 – N Import and Import detail An import record can have multiple import detail records. An import detail can only belong to one import record. => The Import and Import detail relationship is: 1 - N Product and Sale detail A product can be sold many times in many sales detail records. A sale detail can only have information about one product type. => The Product and Sale detail relationship is: 1 – N Manufacturer and Product A manufacturer can produce many products. A product can only originate from one manufacturer.

3. Tables structure of the database 3.1 employee No. Field Type ISNULL Default Extra 1 id int(11) No None

PK,

AUTO_INCREMENT

2 name varchar(30) No None 3 phone varchar(10) No None 4 email varchar(50) No None 5 dob date Yes Null 6 position varchar(50) No None 7 address varchar(100) No None Table 1 : Table structure - employee 3.2 customer No. Field Type ISNULL Default Extra 1 id int(11) No None

PK,

AUTO_INCREMENT

2 name varchar(30) No None 3 phone varchar(10) Yes NULL 4 email varchar(50) Yes NULL 5 address varchar(100) No None Table 2 : Table structure - customer 3.3 sale No. Field Type ISNULL Default Extra 1 id int(11) No None

PK,

AUTO_INCREMENT

2 emp_id int(11) No None FK employee.id

3 cust_id int(11) No None FK customer.id 4 date date No None Table 3 : Table structure - sale 3.4 product No. Field Type

ISNUL

L

Default Extra 1 id int(11) No None

PK,

AUTO_INCREMENT

2 name varchar(100) No None 3 import_date date No None 4 import_pric e decimal(10,2) No None 5 sale_price decimal(10,2) No None 6 mfr_id int(11) Yes NULL FK manufacturer.id Table 4 : Table structure - product 3.5 manufacturer No. Field Type ISNULL Default Extra 1 id int(11) No None

PK,

AUTO_INCREMENT

2 name varchar(50) No None 3 phone varchar(10) No None 4 email varchar(50) No None 5 address varchar(100) No None Table 5 : Table structure - manufacturer 3.6 import No. Field Type ISNULL Default Extra 1 id int(11) No None PK,