Database Design
Database Design And Implementation
10/8/2023
Min Myat Wai Yan
Database Design Assignment 2 Min Myat Wai Yan
Contents
i. Create and populate a
database……………………………………………
ii. Create features in data entry forms to ensure
validity and integrity of data……………………….
iii. Perform queries using multiple tables and multiple
criteria……………………………………………...
iv. Include an advanced feature in a database
design………………………………………………
v. Test a relational
database……………………………………………
vi. Import data from an external
source………………………………………………
vii. Export data to an external
source………………………………………………
viii. Implement an automated
function…………………………………………….
ix. Evaluate a database against the specified user
need………………………………………………...
Database Design Assignment 2 Min Myat Wai Yan
Create And Populate A Database
To develop a database, start with a blank database named Clothing_sale_system1. Create five tables:
Customer, Supplier, Purchase, Sale, and Stock. Each table has its own primary key, such as
CustomerID, CustomerName, Phone, and Address. The Supplier table has its own primary key,
SupplierID, SupplierName, SupplierAddress, SupplierPhone, and SupplierEmail. The Purchase table
has its own primary key, including ItemsName, Category, PurchasePrice, and PurchaseQty. The Sale
table has its own primary key, SaleID, SaleDate, ItemName, Category, SalePrice, SaleQty, and Total.
The Stock table has its own primary key, StockID, StockName, Category, PurchaseQty, SaleQty, and
Balance.
To create relationships between tables, first add foreign keys in the Sale ID field. Add the CustomerID
as a Foreign Key to create relationships between the Customer and Sale tables. Add the SaleID and
PurchaseID as a Foreign Key to create relationships between the Sale and Stock tables using the
SaleID and PurchaseID. Finally, add the SupplierID as a Foreign Key to create relationships between
the Purchase and Supplier tables.
After creating relationships, all tables will be linked to each other, as shown in the images below.
This is an image of the Customer ID relationship
This is an image of all the relationships being linked in the table.
Database Design Assignment 2 Min Myat Wai Yan
Create Features In Data Entry Forms To Ensure Validity
And Integrity Of Data
Creating relationships between tables is crucial for entering data into the clothing selling
database. The Customer Table will include customer data such as CustomerID,
CustomerName, Phone, and Address, which will be used to track purchases.
Create A Customer Table
A customer table is being created here as shown below in the image.
Database Design Assignment 2 Min Myat Wai Yan
Create A Purchase Table
A purchase table is being created here as shown below in the image.
Create A Sale Table
A sale table is being created here as shown below in the image.
Create A Supplier Table
A supplier table is being created here as shown below in the image.
Database Design Assignment 2 Min Myat Wai Yan
Data being inserted into the Customer Table
The Customer Table will include information about the customers personal information such
as CustomerID, CustomerName, CustomerService, CustomerPhoneNumber, CustomerEmail,
CustomerAddress.
Data being inserted into the Supplier Table
The Supplier Table will include information about the supplier where clothings were
purchased, including SupplierID, SupplierName, SupplierDate, SupplierType,
SupplyPhonenumber and SupplierAddress.
Database Design Assignment 2 Min Myat Wai Yan
Data being inserted into the PurchaseTable
The Purchase Table will include data on purchase clothing, including attributes such as
PurchaseID, PurchaseItem, PurchaseType, PurchasePrice, SupplierID and StockID.
Data being inserted into the Sale Table
The Sale Table will include data on clothing sales in the shop, including attributes such as
SaleID, SalePrice, SalePerson, SaleDiscount, SaleTotalPrice, StockID, Customer ID.
Database Design Assignment 2 Min Myat Wai Yan
Data being inserted into the Stock Table
The Stock Table will include data on the stock, including attributes such as StockID,
StockType, StockPrice, StockQuantity, StockQuality
Creating A Customer Form
Start by creating a form for the customer and entering test data afterwards.
Database Design Assignment 2 Min Myat Wai Yan
Database Design Assignment 2 Min Myat Wai Yan
Creating a Supplier Form
The image below shows how the supplier form is being created.
Database Design Assignment 2 Min Myat Wai Yan
Creating a Sale Form
The image below shows how a sale form is being created.
Database Design Assignment 2 Min Myat Wai Yan
Creating a Stock Form
The image below shows how a stock form is being formed.
Database Design Assignment 2 Min Myat Wai Yan
Creating a Purchase Form
The image below shows how a purchase form is being created.
Database Design Assignment 2 Min Myat Wai Yan
Validating The Fields For Each Of The Forms
Validating each form to ensure correct data can only be inputted into the form.
Database Design Assignment 2 Min Myat Wai Yan
Database Design Assignment 2 Min Myat Wai Yan
Database Design Assignment 2 Min Myat Wai Yan
Perform Queries Using Multiple Tables And Multiple
Criteria
Customer Query
Database Design Assignment 2 Min Myat Wai Yan
Using OR In Customer Query
Using And In Customer Query
Using Not In Customer Query
Database Design Assignment 2 Min Myat Wai Yan
Purchase Query
Sale Query
Database Design Assignment 2 Min Myat Wai Yan
Stock Query
Supplier Query
Database Design Assignment 2 Min Myat Wai Yan
Include An Advanced Feature In A Database
Test A Relational Database
Import Data From An External Source
Export Data To An External Source
Implement An Automated Function
Evaluate A Database Against The Specified User Need