Database Design and Development

DATABASE DESIGN AND DEVELOPMENT
Module Title Database Design and Development

Assignment Title Seven-star IT Product Sales System
Examination Cycle Spring 2021

Candidate Name MYO LWIN MOE AUNG
Candidate No 00187449
Centre Name KMD INSTITUTE(YANGON)
Submission Date: 29.1.2021
Page |1
Statement and Confirmation of Own Work
Programmed/Qualification name: Level 5 DC
All NCC Education assessed assignments submitted by students must have this statement as the cover
page or it will not be accepted for marking. Please ensure that this statement is either firmly attached to the
cover of the assignment or electronically inserted into the front of the assignment.
Student declaration
I have read and understood NCC Education’s Policy on Academic Dishonesty and Plagiarism.
I can confirm the following details:
Student ID/Registration number : 00187449
Name : MYO LWIN MOE AUNG

Centre Name : KMD Computer Centre (Yangon)
Module Name : Database Design and Development
Module Leader : TAH MLAH MOO
Number of words : (4939) words
I confirm that this is my own work and that I have not plagiarized any part of it. I have also noted the
assessment criteria and pass mark for assignments.
Due Date : 30-1-2021
Student Signature : lwin
Submitted Date : 28-1-2021
Page |2
Table of Contents
Contents
Introduction.................................................................................................................................................................... 4
Task 1....................................................................................................................................................................... 5 to 7
Task 2..................................................................................................................................................................... 8 to 15
Task 3................................................................................................................................................................... 16 to 20
Task 4................................................................................................................................................................... 21 to 23
Task 5................................................................................................................................................................... 24 to 34
Task 6................................................................................................................................................................... 35 to 46
Task 7................................................................................................................................................................... 47 to 52
Task 8................................................................................................................................................................... 53 to 55
Task 9 ………………………………………………………………………………………………………………………………………………………………56 to 57
References...........................................................................................................................................................58 to 59
Candidate Checklist.......................................................................................................................................................60
Page |3
Seven-star IT Product Sales System

Introduction
Since the 2012, Seven-star IT Product Sales center was opened in Yangon. In 2016, Seven-star
celebrated the opening of its 30th store. And it is open 9 hours and offers customers exceptional
value by providing IT device such as Mobile Phone, Laptop, IT accessary. Now. Seven-star IT store
at various township in Yangon, Nay Pyi Taw and Mandalay. Seven-star aims to be a top IT sell and
service outlet in Myanmar by accelerating and focusing on customer service more than ever.
Page |4
Task 1
Description of Business
Scenario
Page |5
Since the 2012, Seven-star IT Sell and Service center was opened in Yangon. In 2016, Seven-star
celebrated the opening of its 30th store. And it is open 9 hours and offers customers exceptional
value by providing IT device such as Mobile Phone, Laptop, IT accessary. Staff at the company
include the CEO, Manager, technical Staff and sale staff. All staff have access to the information
system
Seven-star organization process is one staff can make so many order form and one supplier can
make so many purchase form. It is not all as an entity relationship diagram one brand and one
category have so many products. That is why brand table, category table and product table are one
to many relationship tables. And also customer tables and customer types table too because in a
one type of customer there are so many customers are including.
And product table and order table are many to many relationships. That why we have to add new
table like order detail table. And also to product table and purchase table are many to many
relationships too. That why purchase detail table are include. Those tables are dummy tables.
Because this is the only way to solve data corruption.
When we create the table we had to create one to many relationships tables as first priority. In one
to many relationship tables first we had to create a table which is non foreign key table after that
foreign key tables and the last many to many relationship tables.
Document-1: Orders form

Order_ID Order_Date Product_ID Product_Name Brand_ID Brand_name Category_ID
O_0001 13-10-2020 P_0004 Samsung Z filp B_0001 Samsung Ca_0008
O_0002 15-10-2020 P-0004 Samsung Z filp B_0001 Samsung Ca_0008
O_0003 15-10-2020 P-0001 I phone 12 pro Max B_0003 Apple Ca_0008
O_0004 15-10-2020 P-0009 Dell Latitude 7400 B_0002 Dell Ca_0001
O_0005 16-10-2020 P-0007 GeForce RTX 3060 Ti B_0006 Nvidia Ca_0005
O_0006 16-10-2020 P-0004 Samsung Z filp B_0001 Samsung Ca_0008
O_0007 16-10-2020 P-0005 Epson M 100 B_0009 Epson Ca_0007
O_0008 17-10-2020 P-0010 MacBook Air 2020 B_0003 Apple Ca_0001
O_0009 17-10-2020 P-0008 GeForce RTX 2080 Ti B_0006 Nvidia Ca_0005
O_0010 18-10-2020 P-0001 I phone 12 pro Max B_0003 Apple Ca_0008
Category_Name Customer_ID Customer_Name CustomerType_ID Customer_Type Staff_ID Staff_Name

Phone Cu_0001 John Smith CT_0001 Regular St_0001 Micheal
Phone Cu_0002 Mikey Pull CT-0002 Member St_0001 Micheal
Phone Cu_0003 Elena Gilbert CT-0002 Member St-0005 Nick
Laptop Cu_0005 Christ Walker CT-0001 Regular St-0008 Edward
Graphic Card Cu_0001 John Smith CT-0001 Regular St-0005 Nick
Phone Cu_0003 Elena Gilbert CT-0002 Member St-0003 Peter
Printer Cu_0001 John Smith CT-0001 Regular St-0001 Micheal
Laptop Cu_0001 John Smith CT-0001 Regular St-0008 Edward
Graphic Card Cu_0007 Den Decker CT-0002 Member St-0005 Nick
Phone Cu_0004 Chloe Miller CT-0002 Member St-0007 Tyson
Page |6
Document -2: Purchase form

Purchase_ID Purchase_Date Product_ID Product_Name Brand_ID
Pu-0001 10-9-2020 P_0004 Samsung Z Flip B_0001
Pu-0002 15-9-2020 P_0010 Mac Book Air 2020 B_0003
Pu-0003 15-9-2020 P_0001 I Phone 12 Pro Max B_0003
Pu-0004 15-9-2020 P_0009 Dell Latitude 7400 B_0002
Pu-0005 17-9-2020 P_0005 Epson M 100 B_0009
Pu-0006 17-9-2020 P_0008 GeForce RTX 2080Ti B_0006
Pu-0007 17-9-2020 P_0007 GeForce RTX 3060Ti B_0006
Pu-0008 17-9-2020 P_0003 Asus ZenBook Pro B_0010
Pu-0009 18-9-2020 P_0002 Radeon RX 6900 XT B_0005
Pu-0010 18-9-2020 P_0006 Radeon RX 6800 B_0005
Brand_Name Category_ID Category_Name Supplier_ID Supplier_Name

Samsung Ca_0008 Phone Sp-0001 Mr. Ambrose
Apple Ca_0001 Laptop Sp-0002 Mr. Kaye
Apple Ca_0008 Phone Sp-0002 Mr. Kaye
Dell Ca_0001 Laptop Sp-0003 Miss. Alison
Epson Ca_0007 Printer Sp-0004 Mrs. Grace
Nvidia Ca_0005 Graphic Card Sp-0005 Mr. Lucas
Nvidia Ca_0005 Graphic Card Sp-0005 Mr. Lucas
Asus Ca_0001 Laptop Sp-0006 Miss. Bail
AMD Ca_0005 Graphic Card Sp-0007 Mr. Jasper
AMD Ca_0005 Graphic Card Sp-0007 Mr. Jasper
Page |7
Task 2
ERD and Data Dictionary
Page |8
Entity Relationship Diagram for Seven-star IT Product Sales System
Page |9
Data Dictionary
Entity: CustomerType

Primary key: CustomerType_ID

Foreign Key : -
Name Type Size Description
CustomerType_ID Varchar 20 To store uniquely identify

customer type number
Customer_Type varchar 40 To record the customer

type
Entity: Staff

Primary key: Staff_ID

Foreign Key : -
Staff_ID Varchar 20 To record uniquely identify Staff number
Staff_Name varchar 50 To store the name of the staff
Staff_Role varchar 30 To store the position of the staff
Email varchar 30 To store the email of staff
Staff_Address varchar 255 To store the address of staff
Phone varchar 20 To record the phone number of staff
DOB date To record the birth day of staff
P a g e | 10
Entity: Supplier

Primary key: Supplier_ID

Foreign Key : -
Supplier_ID Varchar 20 To record uniquely identify supplier

number
Supplier_Name varchar 50 To record the name of the supplier
Company_Address Varchar 255 To record the address of the company
Phone varchar 20 To record the phone number of the

Supplier
Email varchar 30 To record the supplier’s email
Entity: Brand

Primary key: Brand_ID

Foreign Key : -
Brand_ID Varchar 20 To store the uniquely

identify the brand number
Brand_Name varchar 40 To record the name of the

brand
P a g e | 11
Entity: Category

Primary key: Category_ID

Foreign Key : -
Category_ID Varchar 20 To record the uniquely

identify the category
number
Category_Name varchar 40 To store the name of the
category
Entity: Customer

Primary key: Customer_ID

Foreign Key : - CustomerType_ID
Customer_ID Varchar 20 To record the uniquely

identify the customer
number
Customer_Name varchar 30 To store the name of the
customer
Customer_Address Varchar 255 To record the address of

the customer
Phone varchar 20 To store the phone number

of the customer
Email varchar 30 To store the email of

customer
Varchar 20 To record the customer

CustomerType_ID type
P a g e | 12
Entity: Product

Primary key: Product_ID
Foreign Key : Category_ID, Brand_ID

Product_ID Varchar 20 To record the uniquely

identify the product
number
Product_Name varchar 255 To record the name of
product
Brand_ID Varchar 20 To record the product of

brand
Category_ID Varchar 20 To record the product of

category
Colour Varchar 30 To store the colour of the

product
Price int To record the price of the

product
Size Varchar 40 To record the size of the

product
Entity: Orders

Primary key: Orders_ID
Foreign Key :Staff_ID, Customer_ID, Product_ID

Order_ID Varchar 20 To record the uniquely

identify the order number
Ordre_Date date To store the order date
Customer_ID Varchar 20 To record customer who

order our product
Product_ID Varchar 20 To record Product which is

order by customer
P a g e | 13
Staff_ID Varchar 20 To record the staff who
receive the order.
Entity: Purchase

Primary key: Purchase_ID
Foreign Key : Product_ID, Supplier_ID

Purchase_ID Varchar 20 To record the uniquely

identify the purchase
number
Purchase_Date date The date of the purchase
record
Supplier_ID Varchar 20 To record supplier who

supplied the product
Product_ID Varchar 20 To store product which has

been purchase
Entity: OrderDetail

Primary Key : Order_ID, Product_ID

Foreign Key : Order_ID, Product_ID
Order_ID Varchar 20 To record the uniquely

identify the order number

number
Quantity Int To record the quantity of
ordering product
Entity: PurchaseDetail

Primary Key : Purchase_ID, Product_ID
Foreign Key : Purchase_ID, Product_ID
P a g e | 14
Purchase_ID Varchar 20 To record the uniquely

identify the purchase
number
number
Quantity Int To record the quantity of
product which has been
purchase
P a g e | 15
Task 3
Normalization
P a g e | 16
Purpose of Normalization
Normalization is the process of organizing data in a database. And this includes creating tables and
establishing relationships between them. And according to rules designed both to protect the data
and to make the database more flexible by eliminating redundancy and inconsistent dependency.
There are a few rules for database normalization. Each rule is called a normal form. If the first rule is
observed, the database is said to be in first normal form. If the first three rules are observed, the
database is considered to be in third normal form. Although other levels of normalization are
possible, third normal form is considered the highest level necessary for most applications
Three Steps of Normalization
1. First normal form (It is putting all repeated fields in separate files and assigning appropriate
keys to them
2. Second normal form (it is all non-key elements that are fully specified by something other
than the complete key are placed in a separate table.)
3. Third normal form (It offers the grouping of data that is simple, easily, maintainable and with
minimum redundancy.)
An entity is in the third normal form (3NF) if it is in the second normal form and all of its attributes
are not transitively dependent on the complete primary key. Transitive dependency exists when a
non-prime attribute depends on other words the third normal form means that no attribute within an
entity is dependent on a non-prime attribute that, in turn, depends on the primary key.[ CITATION
mic20 \l 1033 ]
Normalization for Order Form
UNF Level 1st Normal Form 2nd Normal Form 3rd Normal Form Entity
Order_ID 1 Order_ID(PK) Order_ID(PK) Order_ID(PK) Orders

1
Order_Date Order_Date Order_Date Order_Date
1
Customer_ID Customer_ID Customer_ID Staff_IDFPK)
1
Customer_Name Customer_Name Customer_Name Customer_ID(FK)
1
Customer_Address Customer_Address Customer_Address
1
Phone Phone Phone Staff_ID(PK) Staff
1
Email Email Email Staff_Name
1
CustomerType_ID CustomerType_ID CustomerType_ID Staff_Role
1
CustomerType CustomerType CustomerType Email
1
Staff_ID Staff_ID Staff_IDc Staff_Address
1
Staff_Name Staff_Name Staff_Name
1
Staff_Role Staff_Role Staff_Role Customer_ID(PK) Customer
1
Email Email Email Customer_Name
1
Staff_Address Staff_Address Staff_Address Customer_Address
Product_ID 2
Phone
Product_Name 2
Order_ID(FK) Order_ID(FK) Email
2
P a g e | 17
Colour 2 Product_ID Product_ID CustomerType_ID(FK)
Price 2 Product_Name Product_Name
Size 2 Colour Colour CustomerType_ID(PK) CustomerType
Brand_ID 2 Price Price CustomerType
Brand_Name 2 Size Size
OrderDetail
Category_ID 2 Brand_ID Brand_ID(FK) Order_ID(PK,FK)
Category_Name Brand_Name Category_ID(FK)
Product_ID(PK,FK)
Category_ID
Quantity
Category_Name Brand_ID(PK)
Brand_Name Product
Product_ID(PK)
Category_ID(PK)
Product_Name
Category_Name
Colour
Price
Size
Brand_ID(FK)
Category_ID(FK)
Brand
Brand_ID(PK)
Brand_Name
Category
Category_ID(PK)
Category_Name
P a g e | 18
Normalization for Purchase Form

UNF Level 1st Normal Form 2nd Normal Form 3rd Normal Form Entity
Product_ID 1 Product_ID(PK) Product_ID(PK) Product_ID(PK) Product

Product_Name 1 Product_Name Product_Name Product_Name
Colour 1 Colour Colour Colour
1
Price Price Price Price
1
Size Size Size Size
1
Brand_ID Brand_ID Brand_ID Brand_ID(FK)
1
Brand_Name Brand_Name Brand_Name Category_ID(FK)
1
Category_ID 1 Category_ID Category_ID
Category_Name Category_Name Category_Name Brand_ID(PK) Brand
1
Purchase_ID 2 Brand_Name
Puchase_Date 2 Product_ID(FK) Product_ID(FK)
Product_ID 2 Purchase_ID Purchase_ID(PK) Category_ID(PK) Category
2
Product_Name Puchase_Date Quantity Category_Name
2
Colour Product_ID
2
Price Product_Name Purchase_ID(PK) Product_ID(PK,FK) PurchaseDetail
2
Size Colour Puchase_Date Purchase_ID(PK,PK)
22
Supplier_ID 2 Price Product_ID Quantity
Supplier_Name 2 Size Product_Name
Company_Address 2 Supplier_ID Colour Purchase_ID(PK) Purchase
Phone 2 Supplier_Name Price Puchase_Date
Email 2 Company_Address Size Product_ID
Phone Supplier_ID Product_Name
Email Supplier_Name Colour
Company_Address Price
Phone Size
Email Supplier_ID(FK)
Supplier_ID(PK)
Supplier
Supplier_Name
Company_Address
Phone
Email
P a g e | 19
Before the normalization changing the product information is difficult because we have update every
single column in our database. For example, one of their product price are change at that time we
have to change price in every price column. To solve this, we would split the table as a making
normalization.
P a g e | 20
Task 4
Assessment of Design
P a g e | 21
Mapped logical database design to physical database design

Seven-star company want to have database because they want to reduce the work load and fault of
their staff. To create the database, Entity Relation Diagram is need to draw first. The working
process of seven-star organization are simpler other system. Staff are record the information of
purchase process and ordering process. In the purchase process product, brand, category, staff
and supplier information are involved. Product entity, Brand entity, Category entity, Staff entity,
Purchase entity, PurchaseDetail entity and supplier are store suitable information. And there is
some entity are increase for ordering process Customer entity, Order entity, OrderDetail entity. And
when we create a physical database we create entity like in ERD for one to many relationships. For
many to many relationships entities we create dummy tables. These tables are PurchaseDetail and
OrderDetail. In a logical database we announce the customer type that is why we create a customer
type table in physical database.
Designed table for the target DBMS

In Database Management System (DBMS) we used SQL language to implementing a database for
Severn-star organization. During the implementation we have to create the tables from the Entity
Relation Diagram, we have to inserted the suitable data for each table. Such as, in the supplier
entity Supplier ID, Supplier Name, Company Address, Phone and Email are store. And the we have
to defined the domain and key constraints such as this column is unique or not, null or not null,
primary key and foreign key.
Derived data
They went to know the total amount of the Ordering product. In order to do that we need to add total
amount column in orders tables. And the following SQL statement will be running. First of all, need
to add a new column called Total Amount is adding to Orders table. When we adding the column we
used alter statement and add statement. After adding the column, we had insert the data. When we
insert the data we use update statement, set statement and condition statement and. Then two
columns price of Product and Quantity of Purchase Detail are multiplied and result is as a Total
Amount.
“Alter table Order

Add TotalAmount int;”
“Update Order
Set TotalAmount=Product.Price*OrderDetail.Quantity
And Order. Product_ID= Product. Product_ID ;”
Before
Order_ID Order_Date Customer_ID Product_ID Staff_ID

P a g e | 22
O-0001 13/October/2020 Cu-0001 P-0004 St-0001

O-0002 15/October/2020 Cu-0002 P-0004 St-0001
O-0003 15/October/2020 Cu-0003 P-0001 St-0005
O-0004 15/October/2020 Cu-0005 P-0009 St-0008
O-0005 16/October/2020 Cu-0001 P-0007 St-0005
O-0006 16/October/2020 Cu-0003 P-0004 St-0003
O-0007 16/October/2020 Cu-0001 P-0005 St-0001
O-0008 17/October/2020 Cu-0001 P-0010 St-0008
O-0009 17/October/2020 Cu-0007 P-0008 St-0005
O-0010 18/October/2020 Cu-0004 P-0001 St-0007
After
Order_ID Order_Date Customer_ID Product_ID Staff_ID TotalAmount

O-0001 13/October/2020 Cu-0001 P-0004 St-0001 8963500
O-0002 15/October/2020 Cu-0002 P-0004 St-0001 7170800
O-0003 15/October/2020 Cu-0003 P-0001 St-0005 475410000
O-0004 15/October/2020 Cu-0005 P-0009 St-0008 4610900
O-0005 16/October/2020 Cu-0001 P-0007 St-0005 1982300
O-0006 16/October/2020 Cu-0003 P-0004 St-0003 8963500
O-0007 16/October/2020 Cu-0001 P-0005 St-0001 316800
O-0008 17/October/2020 Cu-0001 P-0010 St-0008 4858800
O-0009 17/October/2020 Cu-0007 P-0008 St-0005 3564600
O-0010 18/October/2020 Cu-0004 P-0001 St-0007 1584700
P a g e | 23
Task 5
Scripts to create table structures
1. Create Statement for Customer Type Table

This table is record for customer type id and customer type.
Before
P a g e | 24
Fig 1
After
Fig 2
2. Create Statement for Staff Table
This table is record for staff information such as staff id, staff name, staff role, email, staff
address, phone number and date of birth.
Before
Fig 1
After
P a g e | 25
3. Create Statement for Supplier table

This table is record for supplier information such as supplier id, supplier name, company
address, phone number and email.
Before
Fig 1
After
Fig 2
4. Create Statement for Brand table
P a g e | 26
This table is record for brand information such as brand id and brand name.
Before
Fig 1
After
Fig 2
5. Create Statement for Category table

This table is record for category information such category id and category name.
Before
P a g e | 27
Fig 1
After
Fig 2
6. Create Statement for Customer table
P a g e | 28
This table is record for customer information such customer id, customer name, customer
address, customer phone, email and customer type id.
Before
Fig 1
After
Fig 2
P a g e | 29
7. Create Statement for Product table
This table is record for Product information such product id, product name, brand id, category id,
colour, price and size.
Before
Fig 1
After
Fig 2
P a g e | 30
8. Create Statement for Order table
This table is record for Customer order information such order id., order date, customer id,
product id and staff id
Before
Fig 1
After
Fig 2
P a g e | 31
9. Create Statement for Purchase table
This table is record for purchase information such purchase id., purchase date, Supplier id and
product id.
Before
Fig 1
After
Fig 2
10. Create Statement for Order detail table

This table is record for order detail information such order id, product id and quantity
Before
P a g e | 32
Fig 1
After
Fig 2
P a g e | 33
11. Create Statement for Purchase detail table

This table is record for purchase detail information such purchase id, product id and quantity
Before
Fig 1
After
Fig 2
Explanation
These all state meant are based on data dictionary in task 2. And those all quarry are run in
Microsoft SQL database. And a table which have only primary key are as a first priority and then one to
many relationship tables and the last is many to many relationship tables because these table have dummy
table.
P a g e | 34
Task 6
Data Population
1. Insert Statement for Customer Type table
Before
P a g e | 35
Fig 1
After
Fig 2
P a g e | 36
2. Insert Statement for Staff table
Before
Fig 1
After
Fig 2
3. Insert Statement for Supplier table
P a g e | 37
Before
Fig 1
After
Fig 2
4. Insert Statement for Brand table
P a g e | 38
Before
Fig 1
After
Fig 2
5. Insert Statement for Category table
P a g e | 39
Before
Fig 1
After
Fig 2
6. Insert Statement for Customer table
P a g e | 40
Before
Fig 1
After
Fig 2
7. Insert Statement for Product table
P a g e | 41
Before
Fig 1
After
Fig 2
8. Insert Statement for Orders table
Before
P a g e | 42
Fig 1
After
Fig 2
9. Insert Statement for Purchase table
Before
P a g e | 43
Fig 1
After
Fig 2
10. Insert Statement for Order Detail table
Before
P a g e | 44
Fig 1
After
Fig 2
11. Insert Statement for Purchase Detail table
P a g e | 45
Before
Fig 1
After
Fig 2
P a g e | 46
Task 7
SQL Reports
1. Query 1
P a g e | 47
In this query the product which is laptop and the price is between 1500000 and 2000000 and
which is selected in the query 1. The purpose of this query is customer went to know the product
between that price so staff use these statement.
2. Query 2
In this query the product which ordering in ‘17/October/2020’ and which is selected in the query
2. The purpose of this query is manager went to check ordering list so manager use this
statement.
3. Query 3
In this query the product which purchase between ‘17/September/2020’ and
‘18/September/2020’ and which is selected in the query 3. The purpose of this query is manager
went to check purchase list so manager use this statement.
P a g e | 48
4. Query 4
In this query the product which purchase form supplier Mr. Jasper and which is selected in query
4. The purpose of this query is manager went to check purchase list from supplier Mr. Jasper so
he used this statement.
5. Query 5
P a g e | 49
In this query the customer who is member from the organization and which is selected in query
5.
The purpose of this query is staff went to give promotion to the customer who is member in this
organization so he uses this statement.
6. Query 6
In this query the product which is most sold form the company and which is selected in query 6.
The purpose of this query is manager went to refill the product which is the most sold. So he
used this statement.
P a g e | 50
7. Query 7
In this query customer went to know the graphic card detail form NVidia Brand. So staff used
this statement.
8. Query 8
In this query organization went to give a present to customer who is most bought from their
organization. So staff use this statement to know the most buyer.
P a g e | 51
9. Query 9
In this query organization went to update customer type member who is most bought from their
organization. And also who must be from regular customer type.So staff use this statement to
know the most buyer from regular.
10. Query 10
In this query manager went to know how many sale staff in their organization. Because he went
to increase the sale staff so he used this statement to check the number of sale staff.
P a g e | 52
Task 8
Future development of a distributed database
Seven star is the company which is selling IT device such as Laptop, Desktop, mobile phone,
printer and so many things. And seven-star company have a planned to separate a branch in the
future. And Distributed Database Manage System is considered to use.
Meaning of the distributed database system
Distributed database system is a centralized software system that manages a distributed database
in a manner as if it were all stored in a single location. And that is use to connect more than one site
P a g e | 53
either on the same network or on entirely different networks. And there are three type of distributed
databases system. And distributed database system need Network, computer hardware and
security function and also DBMS software.
[ CITATION Tut21 \l 1033 ]
Advantages
 Distributed database system can manage the data with different level of transparency.
 Distributed database system is reliability and availability.
 It has easier expansion
 It has improved performance and secure
[ CITATION Que19 \l 1033 ]
Disadvantages
 Difficult to use
 High cost
 Lack of standard
 Integrity of control
[ CITATION Que19 \l 1033 ]
Components of disturbed database system
There are two things are involving in distributed database manage system. They are fragmentation
and replication. And also there are two things separate from fragmentation they are vertical
fragmentation and horizontal fragmentation. Vertical fragmentation is the only selected for column.
The last things horizontal fragmentation is only use for row which are needed to selected. And also
need to check condition by using where common. [CITATION Placeholder1 \l 1033 ].
Factor of distributed database management system
Homogeneous system is only use for all sites have the same structure in the sense that they all
have database and workstations. And they are also running the same DBMS and the same
operating system. There are two types of homogeneous they are autonomous and non-
autonomous. Heterogeneous can be allowing separate site to develop without overall central based
on a single Database Manage System. The last one is federated it is not strictly a distributed
database and also it is a series of different databases that can be joined in various databases as the
need arises. [ CITATION Que21 \l 1033 ]
Replication and Fragmentation distributed database system
The last involving thing is replication is the method which is storing of data in more than one site.
And it is improving the availability of data. And replication process is simply copying data from one
server to another server. That is why all the user can share the same data without any
inconsistence. And there are three type of replication. They are transactional replication, snapshot
replication and merge replication. In a transactional replication user can receive full initial copies of
data and also user can receive change or updates data too. Snapshot replication is opposite
because user can’t get changing or updating data. Merger replication is combining two or more
databases to become one. Merge is the most complex type of replication because it allows both
publisher and subscriber to independently make changes to the databases.[ CITATION Har21 \l 1033 ]
Potential future expansion
For seven-star company homogeneous is the most suitable for any other type because seven-star
company process is not big and also each site uses similar DBMS and operating system. And it is
more reliability and availability than other type. And also all the data and information is accessed
and modified simultaneously and it is very easy to access and handle. That is why so easy to use.
P a g e | 54
And it has a little disadvantage but when we compare the advantage it nothings and small and easy
to fix it. That is why I recommend the homogeneous system.
P a g e | 55
Task 9
Evaluation
P a g e | 56
Description
Seven-star is a IT product sales company and it went database system to record their purchase
information and ordering information. Before the database system they run their process with
manually but there has some problem such as employee mistake. So they went to change the
system Because this system is not only reducing the employee mistake but also more efficiencies
Feeling
When I start implementing the database of the Seven-star company. I needed to know their
business process so I had to interview of their staff and investigate their business requirement.
When I start Implement their system I thought it would be easy for me but it’s not because when I
reach the middle of the implementation I were knew the working pressure such as lack of ideas and
system error.
Evaluation
In the time of implementation, I face a lot of difficulties one of them lack of ideas is worse things I
had face because at that saturation I don’t know what to do next at that I had asking for advice from
my senior. Another thing when I collecting the information from their staff it was difficult because
they afraid to talk about the process they might be think it would be losing their job. After the
implementation I was much deeper know about the database system and some of their key word
and also their working process.
Analysis
There are so many state to I had to passed to reach the coding state first of all collecting the
information that I need and then make plan and entity relationship diagram (ERD) was draw. And
data dictionary, normalization and the last coding state. After the coding state there is one more
state that is repot. In the report all the process of the implementation is include.
Conclusion
If I more time I would like explain every detail process of this system and I also went to explain
database management system (DBMS) function and working process. When I doing the project I
got so many precious things such as time management skill, thinking skill and sales system
process. And I also have more confidence than before this project. Because I had been through
difficult thing.
Action Plan
In this project I had learn so many lesson and I also got the experience and confidence. So I believe
I can do simpler system. When I doing that I will doing much better than now. And now I also learn
some programing language such as PHP and C#. And I will keep learn about SQL because there
are so many things left to learn. There is a saying “Never too old to Learn”.
P a g e | 57
Reference
References
Anon., 2019. Ques110. [Online]
Available at: https://www.ques10.com/p/17591/what-are-the-advantages-and-disadvantages-of-dis-1/
[Accessed 12 January 2021].
Anon., 2020. Distributed Databases. In: Database Design and Development. s.l.:NCC education UK, pp. Topic-9 pg 1 ,2
and 3.
Anon., 2020. microsoft. [Online]

Available at: https://docs.microsoft.com/en-us/office/troubleshoot/access/database-normalization-description
[Accessed 24 12 2020].
Anon., 2020. Star and SnowFlake Schema in Data Warehousing. [Online]

Available at: https://www.guru99.com/star-snowflake-data-warehousing.html
Anon., n.d. A correlated sub-query. Manchester, United Kingdom: s.n.
Anon., n.d. Advantages of Distributed Data. Manchester, United Kingdom: s.n.
Anon., n.d. Anomalies. Manchester, United Kingdom: s.n.
Anon., n.d. Conceptual Database Design. Manchester, United Kingdom: s.n.
Anon., n.d. Data Definition Language (DDL). Manchester, United Kingdom: s.n.
Anon., n.d. Data Manipulation Language (DML). Manchester, United Kingdom: s.n.
Anon., n.d. Data Manipulation Language (DML). Manchester, United Kingdom: s.n.
Anon., n.d. Definition of Normalization up to 3rd normal form. Manchester, United Kingdom: s.n.
Anon., n.d. Derived. Manchester, United Kingdom: s.n.
Anon., n.d. Disadvantages of Distributed Databases. Manchester, United Kingdom: s.n.
Anon., n.d. Distributed Database Definition. Manchester, United Kingdom: s.n.
Anon., n.d. Features Distributed Database Systems. Manchester, United Kingdom: s.n.
Anon., n.d. Federated (multi-database). Manchester, United Kingdom: s.n.
Anon., n.d. Fragmentation. Manchester, United Kingdom: s.n.
Anon., n.d. Heterogeneous. Manchester, United Kingdom: s.n.
Anon., n.d. Homogenous. Manchester, United Kingdom: s.n.
P a g e | 58
Anon., n.d. Horizontal and vertical fragmentation and suggest when they might be used.. Manchester, United
Kingdom: s.n.
Anon., n.d. Horizontal fragmentation. Manchester, United Kingdom: s.n.
Anon., n.d. Logical Database Design. Manchester, United Kingdom: s.n.
Anon., n.d. Physical Database Design. Manchester, United Kingdom: s.n.
Anon., n.d. Purpose of aggregate functions. Manchester, United Kingdom: s.n.
Anon., n.d. Purpose of Normalization. Manchester, United Kingdom: s.n.
Anon., n.d. Ques10. [Online]

Available at: https://www.ques10.com/p/17615/what-are-the-homogeneous-and-heterogeneous-distr-1/
Anon., n.d. Recap of Phases of Database Design. Manchester, United Kingdom: s.n.
Anon., n.d. SQL REPORTS. Manchester, United Kingdom: s.n.
Anon., n.d. Sub-query. Manchester, United Kingdom: s.n.
Anon., n.d. Table names used in SQL in the process of retrieving data. Manchester, United Kingdom: s.n.
Anon., n.d. The Purpose of Physical Design. Manchester, United Kingdom: s.n.
Anon., n.d. Transparency. Manchester, United Kingdom: s.n.
Anon., n.d. Tutorialspoint. [Online]

Available at: https://www.tutorialspoint.com/distributed_dbms/distributed_dbms_database_environments.htm
Anon., n.d. Types of Distributed Database. Manchester, United Kingdom: s.n.
Anon., n.d. Vertical Fragmentation. Manchester, United Kingdom: s.n.
Anon., n.d. Why are Distributed Database Needed?. Manchester, United Kingdom: s.n.
NCC, n.d. Database Design and Development. s.l.:KMD.
Pandey, H., n.d. GeeksforGeeks. [Online]

Available at: https://www.geeksforgeeks.org/data-replication-in-dbms/#:~:text=Data%20Replication%20is%20the
%20process,same%20data%20without%20any%20inconsistency.
Rouse, M., n.d. searchoracle.techtarget.com. [Online]

Available at: (https://searchoracle.techtarget.com/definition/distributed-database)
[Accessed 20 Sep 2018].
P a g e | 59
Candidate Checklist
Please use the following checklist to ensure that your work is ready for submission.
Have you read the NCC Education documents 'What is Academic Misconduct? Guidance for 
Candidates' and 'Avoiding Plagiarism and Collusion: Guidance for Candidates' and ensured that
you have acknowledge all the sources that you have used in your work?
Have you completed the 'Statement and Confirmation of Own Work' form and attached it to your

assignment? You must do this.
Have you ensured that your work has not gone over or under the recommended word count by
more than 10%?

Have you ensured that your work does not contain viruses and can be run directly?

P a g e | 60

Database Design and Development

Uploaded by

Copyright:

Available Formats

Database Design and Development

Uploaded by

Document Information

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Database Design and Development

Uploaded by

Copyright:

Available Formats

DATABASE DESIGN AND DEVELOPMENT

Module Title Database Design and Development

Examination Cycle Spring 2021

Statement and Confirmation of Own Work

Programmed/Qualification name: Level 5 DC

Student ID/Registration number : 00187449

Name : MYO LWIN MOE AUNG

Due Date : 30-1-2021

Student Signature : lwin

Submitted Date : 28-1-2021

Seven-star IT Product Sales System

Document-1: Orders form

Category_Name Customer_ID Customer_Name CustomerType_ID Customer_Type Staff_ID Staff_Name

Document -2: Purchase form

Brand_Name Category_ID Category_Name Supplier_ID Supplier_Name

Entity Relationship Diagram for Seven-star IT Product Sales System

Name Type Size Description

CustomerType_ID Varchar 20 To store uniquely identify

Customer_Type varchar 40 To record the customer

Name Type Size Description

Staff_ID Varchar 20 To record uniquely identify Staff number

Staff_Name varchar 50 To store the name of the staff

Staff_Role varchar 30 To store the position of the staff

Email varchar 30 To store the email of staff

Staff_Address varchar 255 To store the address of staff

Phone varchar 20 To record the phone number of staff

DOB date To record the birth day of staff

Name Type Size Description

Supplier_ID Varchar 20 To record uniquely identify supplier

Supplier_Name varchar 50 To record the name of the supplier

Company_Address Varchar 255 To record the address of the company

Phone varchar 20 To record the phone number of the

Email varchar 30 To record the supplier’s email

Name Type Size Description

Brand_ID Varchar 20 To store the uniquely

Brand_Name varchar 40 To record the name of the

Name Type Size Description

Category_ID Varchar 20 To record the uniquely

Name Type Size Description

Customer_ID Varchar 20 To record the uniquely

Customer_Address Varchar 255 To record the address of

Phone varchar 20 To store the phone number

Email varchar 30 To store the email of

Varchar 20 To record the customer

Foreign Key : Category_ID, Brand_ID

Product_ID Varchar 20 To record the uniquely

Brand_ID Varchar 20 To record the product of

Category_ID Varchar 20 To record the product of

Colour Varchar 30 To store the colour of the

Price int To record the price of the

Size Varchar 40 To record the size of the

Foreign Key :Staff_ID, Customer_ID, Product_ID

Name Type Size Description

Order_ID Varchar 20 To record the uniquely

Ordre_Date date To store the order date

Customer_ID Varchar 20 To record customer who

Product_ID Varchar 20 To record Product which is

Foreign Key : Product_ID, Supplier_ID

Purchase_ID Varchar 20 To record the uniquely