ASSIGNMENT 2 FRONT SHEET Qualification TEC Level 5 HND Diploma in Computing Unit number and title Unit 04: Database Design & Development Submission date Date Received 1st submission Re-submission Date Date Received 2nd submission Student Name Bui Quang Minh Student ID GCD210325 Class GCD1104 Assessor name Ho Van Phi 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 Minh Grading grid P2 P3 P4 P5 M2 M3 M4 M5 D2 D3 Summative Feedback: Resubmission Feedback: Grade: Assessor Signature: Date: Signature & Date: Table of Contents CHAPTER 1: STATEMENTS OF USER AND SYSTEM REQUIREMENTS (P1). Introduction of proposed system.
Analysing current system. Evaluating current system. Proposal of new system. 10 CHAPTER 2: DESIGN THE RELATIONAL DATABASE SYSTEM (P1-D1).
DATABASE DESING with EXPLAINATIONS. REVIEW IF DATABASE IS NORMALIZED. WIREFRAME OF APPLICATION. Creating tables using tool.
Creating tables using command .1 Sample data of CUSTOMERS table …….2 Sample data of STAFFS table ……….3 Sample data of PRODUCTS table …….4 Sample data of ORDERS table …….5 Sample data of DETAILS table …….6 Sample data of SUPPLIERS table …….1 Query to show products from 10m to 20m …….2 Query to show income of order at a date ………………………….3 Query to show income of all order …………………………………………………….1 View to managing products …….2 View of available products. Assess the effectiveness of the design. 26 CHAPTER 4: DEVELOP DATABASE SYSTEM (P2-P3). QUERYING ACROSS MULTIPLE TABLES.
Print orders’ list of customerID ‘0100’. Show income of order at a date. Show income of all orders. 30 CHAPTER 5: PRODUCE QUERIES (P3-M2-M3).
IMPLEMENT QUERY LANGUAGE (P3). IMPLEMENT FULLY FUNCTIONAL DATABASE (M2). View of PRODUCTS table ……. View of CUSTOMERS table …….
View of STAFFS table ……. View of ORDERS table ……. View of DETAILS table ……. View of SUPPLIERS table …….
View of revenue ragarding year. View of revenue ragarding Products. View of revenue made from staffs. View of orders placed by customers.
View of orders number made by staffs. 39 CHAPTER 6: TEST SYSTEM (P4-M4). 41 CHAPTER 1: STATEMENTS OF USER AND SYSTEM REQUIREMENTS (P1) I. Introduction of proposed system FPT Shop has contacted my firm where I am working as a Database developer because the increasing number of stores.
FPT Shop are having many challenges that it has to handle throughout the nation. It has made decision to create a new database with many purposes with different objects such as users can sign in with phone numbers and other data, supervisors can manage their stores and director board can view all information from all stores. Analysing current system The FPT Shop currently stores all data in excel files when a customer purchases an item, a staff will write that item’s information into a particular paper called receipt and give it to the customer. All available items and purchased items also store in excel files.
Product table’s data in excel After a day, month, or year, staff will create a new table to calculate the total amount of money earned and reckon up the quantity, the following table made in excel: Figure 2. Revenue in January 3. Evaluating current system Advantages of Spreadsheets Spreadsheets require minimal training. Spreadsheets are customizable.
Spreadsheets can be more collaborative than other tools. It’s easy to manipulate and analyze data. You can integrate spreadsheets with specific tools. Spreadsheets are quick and easy to add to a workflow.
Spreadsheets are fantastic tools for financial documents. You have access to countless spreadsheet templates. Disadvantages of Spreadsheets Spreadsheets are not secure. It’s hard to tell who edited the spreadsheet.
There will be multiple versions of the truth. Visualizing data is difficult. Critical customer data is at everyday life's mercy. There’s no native integration with business systems.
Spreadsheets make it harder for managers to manage team members. Proposal of new system By creating a relational database system for the shop and organizing the information that has to be maintained into precise and understandable tables, the above issues may be resolved for the following benefits: Minimum data redundancy Improved data security Increased consistency Lower updating errors Reduced costs of data entry, data storage, and data retrieval Improved data access using host and query languages Higher data integrity from application programs II. Hardware requirement Hard Disk SQL Server requires a minimum of 6 GB of available hard-disk space. Monitor SQL Server requires Super-VGA (800x600) or higher resolution monitor Memory: Minimum Express Editions: 512 MB All other editions: 1 GB Processor Speed: Minimum x64 Processor 1.
Software requirement Operating system Windows 10 TH1 1507 or greater Windows Server 2016 or greater .NET Framework Minimum operating systems includes minimum. CHAPTER 2: DESIGN THE RELATIONAL DATABASE SYSTEM (P1 – M1 - D1) I. ANALYSING THE REQUIREMENTS As a client/customer, - I want to view the detail of the product so that I can select that product. - I want to order products so that I can buy those products.
- I want to check my products/items so that I can make sure that products are mine. - I want to log in and log out the system so that I can use all the functions. - I want to follow my order so that I can keep track of it. - I want to know the origin of products so that I can buy it wihout hesitation.
As a staff, - I want to input the products’ data so that I can manage the products. - I want to alter/modify products’ data so that I can update the products. - I want to approve the customers’ order so that the orders can be delivered. - I want to follow clients’ orders so that I can let them know about their orders.
- I want to view the clients’ feedbacks so that I can support or report to the higher position. - I want to supervise the supply so that I can check the quantity of products and ensure the quality of products. As a manager, - I want to manage the staff so that I can supervise the staff. - I want to view the list of products so that I can manage the products.
- I want to view the daily/monthly/weekly revenue so that I can manage the income/money of my shop. - I want to check staff’s attendance so that I can pay their salary. So, I can define all tables what I need. DATABASE DESIGN with EXPLANATIONS Figure 3.
ERD Diagram The picture above shows the relational entity diagram of FPTSHOP system through the ERD diagram, we can see the following relationships: The Customer entity has a 1-to-many relationship with orders because a customer can place many orders. In contrast, a specific order is placed by just a customer. The Staff entity has a 1-to-many relationship with orders because a staff can organize many orders. In contrast, a specific order is created by just a staff.
The Supplier entity has a 1-to-many relationship with Product because a supplier can provide many kinds of items. In contrast, a product is supplied by just a supplier. The Products entity has a 1-to-many relationship with Details because an item can contain many details. In contrast, a detail is contained by an item.
The Orders entity has a 1-to-many relationship with Details because an order can contain many separately details. In contrast, a detail is contained in an order. REVIEW IF DATABASE IS NORMALIZED From ERD Diagram shown above, we can see that the Products table contains transitive functional Dependence so that it cannot achieve 3NF (The third normal form): ProductID SupplierID SupplierName To achieve 3NF, attribute ‘SupplierName’ needs to split from PRODUCTS in order to combine with SupplierID and then create a new table named SUPPLIERS. As the result, after normalizing, the database system contains the following tables 1.
Products table Products Table: this table is used to store all information about products. It has several columns such as ProductID, ProductName, Price, Quantity… Among these, productID is the primary key. The column productName must be not null and the Price, Quantity must be bigger than zero (>0). The detail of the table Products is shown as follow: Column name Data Tye Allow null Contraint Nvarchar(10) No PK ProductID Nvarchar(100) No Unique ProductName Int Yes Check (Price>0) Price Int Yes Check (quantity>0) Quantity Nvarchar(10) No FK (Suppliers) SupplierID Table 1.
Customers table Customers Table: this table is used to store all information of customers. It has several columns such as CustomerID, CName, Address, PhoneNum… Among these, CustomerID is the primary key. The columns CName and Address must not be null. The detail of the table Customers is shown as follows: Column name Data Tye Allow null Contraint Nvarchar(10) No PK CustomerID Nvarchar(100) No CName Nvarchar(150) No Address Nvarchar(11) Yes PhoneNum Table 2.
Staffs table Staffs Table: this table is used to store all information about staff. It has several columns such as StaffID, SName, Address, Salary… Among these, StaffID is the primary key. The columns SName and Address must not be null and and Salary must be bigger than zero (>0). The detail of the table Staffs is shown as follows: Column name Data Tye Allow null Contraint Nvarchar(10) No PK StaffID Nvarchar(100) No SName Nvarchar(150) No Address Int Yes Check (Salary>0) Salary Table 3.
Orders table Orders Table: this table is used to store all information on orders. It has several columns such as OrderID, OrderDate, CustomerID, StaffID… Among these, StaffID is the primary key. The columns' OrderDate must not be null. The detail of table Orders is shown as follows: Column name Data Tye Allow null Contraint Nvarchar(10) No PK OrderID Date No OrderDate Nvarchar(10) No FK from Customers CustomerID Nvarchar(10) No FK from Staffs StaffID Table 4.
Details table Details Table: this table is used to store all information of all details. It has several columns such as OrderID, ProductID, Price, Quantity… Among these, OrderID and ProductID are the primary keys and the Price, Quantity must be bigger than zero (>0). The detail of table Details is shown as follows: Column name Data Tye Allow null Contraint Nvarchar(10) No PK-FK from Orders OrderID Nvarchar(10) No PK-FK from Products ProductID Int No Check (Price>0) Price Int No Check (quantity>0) Quantity Table 5. Suppliers table Suppliers Table: this table is used to store all information of all suppliers.
It has two columns including SupplierID and SupplierName. Among these, SupplierID is the primary keys. The detail of table Suppliers is shown as follows: Column name Data Tye Allow null Contraint Nvarchar(10) No PK SupplierID Nvarchar(100) No SupplierName Table 6. WIREFRAME OF APPLICATION 1.
Creating Tables using tool Figure 4. Products table Figure 5. Customers table Figure 6. Staffs table Figure 7.
Orders table Figure 8. Details table Figure 9. Creating tables using command CREATE TABLE PRODUCTS ( ProductID NVARCHAR(10) PRIMARY KEY, PName NVARCHAR(50) NOT NULL UNIQUE, Price INT CHECK(Price>0), Quantity INT CHECK(Quantity>0) SupplierID nvarchar(10) REFERENCES SUPPLIERS (SupplierID) ) GO Figure 10. Products table using command CREATE TABLE CUSTOMERS ( CustomerID NVARCHAR(10) PRIMARY KEY, CName NVARCHAR(100) NOT NULL, Address NVARCHAR (150) NOT NULL, PhoneNum NVARCHAR (11) ) GO Figure 11.
Customers table using command CREATE TABLE STAFFS ( StaffID NVARCHAR(10) PRIMARY KEY, SName NVARCHAR(100) NOT NULL, Address NVARCHAR (150) NOT NULL, Salary INT CHECK(Salary>0) ) GO Figure 12. Staffs table using command CREATE TABLE ORDERS ( OrderID NVARCHAR(10) PRIMARY KEY, OrderDate DATE NOT NULL, CustomerID NVARCHAR(10) REFERENCES CUSTOMERS (CustomerID), StaffID NVARCHAR(10) REFERENCES STAFFS (StaffID) ) GO Figure 13.