FPT Shop Database Design and Development, Study notes of Insurance law

Download FPT Shop Database Design and Development and more Study notes Insurance law in PDF only on Docsity!

ASSIGNMENT 1 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 Bùi Huy Hoàng Student ID GCH

Class GCH1106 Assessor name Đinh Đức Mạnh

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 Hoang

Grading grid

P1 M1 D

❒ Summative Feedback: ❒ Resubmission Feedback:

Grade: Assessor Signature: Date:

Signature & Date:

2.1. Overview about the Problem

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.

2.2. Requirements of the application  User requirements:  Users can register purchases with their personal ID, purchase ID and product ID  Allow them to comment, rate purchased products  They can order home delivery with complete information

 Store requirements:

 Store managers can take care of their stores.  Management can view information of customers who have purchased products.  Management can see all data from all stores.  Manager can add customer and order information.   Employee requirements:

 They can add customer and order information.  Allow them to place orders.  Let them be able to create delivery orders for customers.  Allow them to manage customer information and orders.

 Requirements of the application

 The website must operate 24/24 all days of the year except for the maintenance period, which must be notified in advance to users.  The system must be designed to be simple for the user to operate.  The system can allow users to create their own order and delivery information.  The system must allow users to view product information.  The system must have a function that allows users to add, delete, and edit order information to place an order.  The system can compile a list of orders, information about shoppers, products and deliveries. User Roles:  As an employee, I want to know information about orders and add, edit, and delete customer information.  As an employee, I want to know information about customers to make it easier to manage customers.  As an employee, I want to know product information so that I can advise customers.  As a manager, I want to know the information of orders to manage the store.  As the Board of Directors I want to manage all the information of the stores.  As a customer who wants to know the information of the product and create my own order.

III. Design the relational database system 3.1. Analyse the requirements

I use a Microsoft SQL Server database to create a database that meets the article's specifications. The request of FPT Shop is the foundation for this design. There are 5 tables in this database design article, each with its own documentation to meet the article's requirements. 5 panels include:

 Product table: provides all relevant data related to the product.  Customer table: Contains all data related to buyers  Orders table: Includes data about buyer, seller and order fulfillment date.  Order details: Includes order number, order number and product.  Delivery: including delivery code, product information, customer information, delivery date and receipt date.

3.2. Database design with explanations

 Logical design of database:

 Physical Design :

 Explanation:

The 5 tables in the database include the following information:

 Customer: CustomerID, CustomerName, Phone, Address.  Product: ProductID, ProductName, ProductType, Quantity.  Order: OrderID, CustomerID, DeliveryID, ProductID, PurchaseDate, Quantity, PaymentStatus.  Order Details: OrderDetailID, OrderID, ProductID, ProductName, CustomerName, TotalPrice.  Delivery: DeliveryID, Address, CustomerName, Phone, Product, Quantity, Describe, DeliveryDate, ReceivedDate, TotalPrice.

I create 3 parent tables Customer, Delivery and Product. Set primary key for 3 parent tables Customer(CustomerID), Delivery(DeliveryID) and Product(ProductID). The two child tables, Order and OrderDetail, have a subkey of Order(CustomerID,DeliveryID) to join the CustomerID and Delivery tables. The OrderDetail subtable has a subkey OrderID and ProductID to join the Product and Order tables. Tables are linked with a 1-to-many relationship because the same information can be used more than once

3.3. Review whether the database is normalized Table name Fields Data type Constraint Customer CustomerID int Primary key, not null CustomerName nvarchar(50) allow null Address nvarchar(50) allow null Phone int allow null OrderDetails OrderDetailID int Primary key, not null OrderID int foreign key ProductID int foreign key DeliveryID int allow null Order OrderID int Primary key, not null CustomerID int foreign key DeliveryID int foreign key PurchaseDate date allow null Quantity int allow null PaymentStatus nvarchar(50) allow null Product ProductID int Primary key, not null ProductName nvarchar(50) allow null ProductType int allow null