Download Database Design & Development and more Assignments Database Management Systems (DBMS) in PDF only on Docsity!
ASSIGNMENT 1 FRONT SHEET
Qualification BTEC Level 5 HND Diploma in Computing Unit number and title Unit 04: Database Design & Development Submission date February 20, 2022 Date Received 1st submission Re-submission Date March 1, 2022 Date Received 2nd submission Student Name Huynh Chi Bao Student ID GCC Class GCC0904^ Assessor name Le Huynh Quoc Bao 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 Bao Grading grid
P1 M1 D
Summative Feedback: Resubmission Feedback:
Grade: Assessor Signature: Date: Signature & Date:
LO1 Use an appropriate design tool to design a relational database system for a substantial problem Assignment Brief and Guidance: 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. You are tasked to select one of those systems to develop database for FPT Shop. Your tasks are to: Work with FPT Shop to find out about current requirements for each system Analyse the requirements and produce clear statements of user and system requirements. Design a relational database system using appropriate design tools and techniques Develop a fully functional relational database system, based on an existing system design. Test the system against user and system requirements. Produce technical and user documentation Part 1 (Assignment 1) Before you start the development process, your manager has asked you to produce a report for the CEO of FPT, containing:
- Clear statements of user and system requirements. The system must have at least 3 user roles, including business processes and statistical reports for FPT Shop managers.
- The design of the relational database system using appropriate design tools and techniques. It should contain at least four interrelated tables. You would prefer to produce a more detailed document, so you will produce a comprehensive design for a fully functional system which will include interface and output designs, data validations and cover data normalization. Your manager would like on the report your assessment of the effectiveness of the design in relation to user and system requirements.
Learning Outcomes and Assessment Criteria (Assignment 1): Learning Outcome Pass Merit Distinction LO1 P1 Design a relational database system using appropriate design tools and techniques, containing at least four interrelated tables, with clear statements of user and system requirements. M1 Produce a comprehensive design for a fully functional system which includes interface and output designs, data validations and data normalisation. D1 Assess the effectiveness of the design in relation to user and system requirements.
- Assignment Brief 1 (RQF)
- Higher National Certificate/Diploma in Computing
- Chapter 1 - Statements of user and system requirements (P1)
- Statements of user
- System Requirement Specification (SRS)
- Chapter 2 – Design the relational database system (P1)....................................................................................
- Logical design (ERD)
- Explanation about ER diagram
- Chapter 3 – Data Validation and Normalization (M1)
- Data Validation (Data integrity included in physical design)..............................................................
- Normalization
- Physical design
- User Interfaces of your system
- Chapter 4 – Design Evaluation (D1)
- References
- Figure 1.ER diagram Table of Figures
- Figure 2. FPT shop database management.
- Figure 3. Shop table.........................................................................................................................................
- Figure 4.Category table
- Figure 5. Supplier table
- Figure 6.Product table
- Figure 7.Staff table
- Figure 8.Customer table
- Figure 9.Feedback table...................................................................................................................................
- Figure 10.Order table.......................................................................................................................................
- Figure 11.Detail order table
- Figure 12. FPT shop database management diagram
- Figure 13.FPT Shop Manage Database
Chapter 1 - Statements of user and system requirements (P1)
1. Statements of user The nationwide chain store management system of FPT shop group. User requirements for the system: Customers can register their phone number as an ID and place an order or rate and comment on their purchased device. Store managers can take care of their stores and management can see all data from all stores. 2. System Requirement Specification (SRS) 2.1. Reasons for the project FPT Shop Corporation is currently facing difficulties in managing the database from all its stores nationwide, so it has to develop a new database management system to help FPT shop group manage data from all stores. Everything functions are automated for a purpose Resource management: With the help of the new database management system, management can view all data from all stores. Increase productivity: Store managers can find out what products customers want to buy in many different FPT stores. Data retention: Manual data management leads to risks such as data loss, data entry errors. And so they can track all the data from the new database management system. 2.2.Requirements and Expectation Requirements: - The database systems that we are about to create must ensure that users can easily access, manage, and update data. In addition, this database system has also ensured security, integrity, data recovery, and minimize data redundancy. - The database system will store and classify data in a centralized location where users can access and manage from their machines. This database system should be available in the shortest time possible and the investment cost must be consistent with the requirement of FPT shop. Expectations: FPT shop hopes that the project will be completed on schedule. During operation, the system does not have any errors or malfunctions present in the original design. 2.3. Objectives (Things intended to achieve)
Customer 1 Fill n Order Staff 1 Manage n Order Order 1 Have n Detail Order Customer 1 Send n Feedback Product 1 Have n Feedback 1.3. Identify and associate attributes with entities SHOP entity No. Attribute Description 1 Shop ID Unique ID for shop 2 Area Area of the shop 3 Address Address of the shop 4 Shop Name Name of the shop Primary key is Shop ID PRODUCT entity No. Attribute Description 1 Product Name Name of the product 2 Product ID Unique ID for product’s 3 Price Price of imported products 4 Category ID Unique ID for category products 5 Supplier ID Unique ID for supplier 6 Shop ID Unique ID for shop Primary key is Product ID CATEGORY entity
No. Attribute Description 1 Category ID Unique ID for category products 2 Category Name Name of category products Primary key is Category ID CUSTOMER entity No. Attribute Description 1 Customer Name Name of customer 2 Customer ID Unique ID for customers registered by phone number 3 Address Address of the customer 4 Email Email of the customer Primary key is Customer ID ORDER entity No. Attribute Description 1 Order ID Unique ID for order 2 Customer ID Unique ID for customers registered by phone number 3 Order date Day of order 4 Staff ID Unique ID for staff’s Primary key is Order ID DETAIL ORDER entity No. Attribute Description 1 Product ID Unique ID for the product
2 Feedback ID Unique ID for feedback 3 Content Content of customer feedback 4 Feedback Date The date customer send feedback 5 Customer ID Unique ID for customers registered by phone number Primary key is Feedback ID 1.4. ER diagram Figure 1 .ER diagram
2. Explanation about ER diagram Explain the entities and relationship:
The entity “Shop” has the attributes: “Shop ID”, “Address", "Area”, “Shop Name”. The "Shop" entity has a one-to-many relationship with the "Product" entity. A shop can manage more than 1 product, but some products are only in one shop. The "Category" entity has the following properties: "Category ID", "Category Name". The "Category" entity has a 1 to many relationships with the "Product" entity. A kind product will have many products, but many products belong only to product type. The entity "Supplier" has the attributes: “Supplier ID", "Supplier Name”, “Address”, “Phone Number”, “Email”. The entity "Supplier" has a 1-to-many relationship to the "Product" entity. A supplier can supply many products, but many products must be supplied by one supplier. The "Customer" entity has the attributes:” Customer Name”, “Customer ID”,” Address”, “Email”. The “Customer” entity has a 1 to-many relationships with the "Order" entity. 1 customer can have filled many orders, but many orders can belong to 1 customer. The "Detail Order" entity has the following attributes: "Product ID", "Quantity", Order ID”, “Price”. The "Detail Order" entity has a one-to-many relationship with the "Product" entity. Detail order has many products, but many products are included in a detail order. The "Order" entity has the following properties: "Order ID", "Customer ID", "Order Date", "Staff ID". The "Order" entity has a 1-to-many relationship with the "Order Detail" entity. An order can have multiple order details, but multiple order details can be part of a single order. The "Customer" entity has attributes: “Customer Name", "Customer ID", "Address", "Email". The "Customer" entity has a 1-to-many relationship with the "Feedback" entity. A customer can submit multiple reviews for a product they purchase, but those reviews belong to only one customer. The entity "Staff" has the attributes:" Staff ID”, “Staff Name", "Gender", "Phone Number". The "Staff" entity has a 1 to many relationships with the "Order" entity. 1 employee can manage many orders, but many orders belong to 1 manager.
2 Category
Name Varchar(50) Not null Name of category products CUSTOMER table No. Field name Data type Constraint Description 1 CustomerName Varchar(50) Not null Name of customer 2 Tel nchar(15) Primary key Unique telephone number for customer 3 Address Varchar(50) Not null Address of the customer 4 Email Varchar(50) Not null Email of the customer ORDER table No. Field name Data type Constraint Description 1 Order ID int Primary key Unique ID for order 2 Tel nchar(15) Not null Unique telephone number for customer 3 OrderDate datetime Not null Day of order 4 StaffID Char(10) Not null Unique ID for staff’s DETAIL ORDER table No. Field name Data type Constraint Description 1 ProductID Char(10) Primary key Unique ID for the product 2 Qty_Product int Not null Quantity of the product 3 OrderID int Primary key Unique ID for order
SUPPLIER table No. Field name Data type Constraint Description 1 SupplierID Char(10) Primary key Unique ID for supplier 2 SupplierName Varchar(50) Not null Name of the supplier 3 Address Varchar(50) Not null Address of the supplier 4 Tel nchar(15) Not null Telephone number of the supplier 5 Email Varchar(50) Not null Email of the supplier STAFF table No. Field name Data type Constraint Description 1 StaffID^ Char(10)^ Primary key^ Unique ID for staff’s 2 StaffName Varchar(50) Not null Name of the staff 3 Gender Varchar(50) Not null, only accept ‘male’ or ‘female’ value Gender of the staff 4 Tel nchar(15) Not null Telephone number of the staff FEEDBACK table No. Field name Data type Constraint Description 1 ProductID Char(10) Not null Product ID customer feedback 2 FeedbackID Char(10) Primary key Unique ID for feedback 3 Content Varchar(100)^ Not null^ Content of customer feedback 4 Tel Nchar(10) Not null Telephone number of customer 5 FeedbackDate^ datetime^ Not null^ The date customer send feedback
3.2. Create tables. SHOP table Figure 3. Shop table CATEGORY table Figure 4 .Category table SUPPLIER table Figure 5. Supplier table
PRODUCT table Figure 6 .Product table STAFF table Figure 7 .Staff table CUSTOMER table Figure 8 .Customer table
