Download Assignment 1 1622 (D) Grade and more Assignments Information Technology 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^27 -^4 -^2023 Date Received 1st submission^27 -^4 -^2023 Re-submission Date Date Received 2nd submission Student Name NGuyễn Văn Quang Student ID Gch Class Gch1107 Assessor name Trần Thị Thoa 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 Quang Grading grid P1 M1 D
❒ Summative Feedback: ❒ Resubmission Feedback: Grade: Assessor Signature: Date: Signature & Date:
- I. Statements of user and system requirements (P1).
- 1.1.Overview about the Problem
- 1.2.Requirements of the application
- II.Design the relational database system (P1 – M1)
- 2.1 Analyse the requirements
- 2.2 Database design with explanations
- 2.3 Review whether the database is normalized
- 2.4 Data validation
- 2.5 Wireframe of the application
- III. Assess the effectiveness of the design in relation to user and system requirements (D1) - 1.1.Assess the effectiveness of the design
- 1.2.Advantages
- 1.3.Disadvantages
- 1.4.Future development plant
- Reference
- Figure 1:Entity-relationship diagram at the conceptual level
- Figure 2:Complete Entity Relationship Diagram
- Figure 3: wireframe login interface
- Figure 4: Wire frame main page of customer
- Figure 5: wireframe of view page
- Figure 6: wireframe of update page
- Figure 7:the wireframe of the Devices_order_list
- Figure 8:the wireframe of the Devices_order_list after fill in
- Figure 9:The Wireframe of product list
- Figure 10:The Wireframe of product list after fill in
- Figure 11:The Wireframe of comment list
- Figure 12:The Wireframe of comment list after fill in
- Figure 13: wireframe add page
- Figure 14: wireframe statistic page
- Figure 15:wireframe update for manager
- Figure 16:wirefame update for manager after fill blank
- Figure 17:wireframe result customer for manager....................................................................................................................................
- Table 1:Identify relationships
- Table 2:Customers entity
- Table 3: Product entity
- Table 4:Comment entity
- Table 5:Bill entity
- Table 6:Manger entity
- Table 7: Director entiry.............................................................................................................................................................................
- Table 8: category entity
- Table 9: Devices_order entity
- Table 10: product table data validation
- Table 11:Director table data validation.....................................................................................................................................................
- Table 12: manager table data validation
- Table 13:comment table data validation
- Table 14:devices order table data validation
- Table 15: bill table data validation............................................................................................................................................................
- Table 16: category table data validation
Comment Management: The application should allow customers to comment on products and rate them. Managers should be able to moderate the comments and delete inappropriate ones. Employee Management: The application should allow the admin to manage the employees, including managers and directors, and assign roles to them. The application should ensure the security of customer data and protect it from unauthorized access or theft. Scalability: The application should be scalable to handle a large number of users and products. The application should provide fast response times and handle high traffic loads without any downtime. Objectives: The database must have sufficient functionality for adding, deleting, and changing data as well as for rapidly and simply accessing it by users. It must also guarantee the database's security. II.Design the relational database system (P1 – M1) 2.1 Analyse the requirements Users should be able to sign up for the database using their phone numbers as IDs. This will provide each customer a special identification number and make it possible to track customer information effectively. Moreover, the system ought to have options for password resets and user authentication. Customers should be able to order the devices they want from the database. The system should also make it possible for store managers to handle and track orders and guarantee prompt consumer delivery. This should provide functions like inventory control, delivery tracking, and order status monitoring. Customers should be able to review and comment on the devices they have purchased using the database. As a result, FPT Shop will be able to enhance the quality of their goods and services while also assisting other customers in making well-informed judgments. To aid in the analysis of client input, the system should also incorporate capabilities like data analytics and reporting tools. Shop managers should be able to monitor their operations using the database's tools. To assist them in making wise judgments and enhancing the effectiveness of their operations, this should include inventory management, sales tracking, and other pertinent data. Additionally, the system should have functions like permission management and user access control. The database ought to make it possible for the director board to obtain information from every FPT Store location. As a result, it will be easier to spot trends, gain insightful information about each location's performance, and support corporate decision-making. To consolidate data from diverse sources, the system should also have capabilities like data warehousing and data integration tools. As FPT
Shop grows, the database should be scalable and able to handle growing volumes of data and traffic. For the system to stay reliable and effective as the business expands, it should also have capabilities like data partitioning and replication. 2.2 Database design with explanations Identify relationships: Entity Multiplicity Relationship Multiplicity Entity Product 1 Has M Category ,comment, Order_devices Director 1 Manage M Manager Manager 1 Manage M Order_devices Customer 1 has M Comment, bill Customer 1 Order M Order_devices Order_device 1 Has 1 Bill Category 1 Has M Products Table 1 :Identify relationships Identify and associate attributes with entities Customer entity No Attribute Decription 1 CustomerId Contains customer’s Id 2 Fullname Contains customer’s FullName 3 Email Contains customer’s email 4 User_age Contains customer’s age 5 address Contains customer’s address 6 Phonenumber Contains customer’s phonenumber Primary key: Customer_Id Table 2 :Customers entity
Primary key: Bill_Id, Foreign Key Order_id and Customer_id Table 5 :Bill entity Manager entity No Attribute Decription 1 Manager_Id Contains Manager’s Id 2 Email Contains Manager’s email 3 Fullname Contains Manager’s fullname 4 Manager_age Contains Manager’s age 5 Address Contains Manager’s Address 6 Phone Contains Manager’s Phone 7 PhoneNumber Contains Manager’s number phone 8 Director_id Contains director’s id Primary key: Manager_id, Foreign key director_id Table 6 :Manger entity Director entity No Attribute Decription 1 Director_Id Contains Director’s Id 2 Email Contains Director’s email 3 Fullname Contains Director’s fullname 4 Director_age Contains Director’s age 5 Address Contains Director’s Address 6 Phone Contains Director’s Phone 7 PhoneNumber Contains Director’s number phone Primary key: Director_id Table 7 : Director entiry
Category entity No Attribute Decription 1 Category_id Contains category’s Id 2 Category_name Contains Director’s email Primary key: Category Table 8 : category entity Devices_order entity No Attribute Decription 1 Order_id Contains Devices_order’s Id 2 date Contains Devices_order’s date 3 status Contains Devices_order’s status 4 Manager_id Contains Manager’s id 5 Customer_id Contains Customer’s id 6 Product_id Contains Product’s id Primary key: Director_id, Foreign key Manager_id, Customer_id, Product_id Table 9 : Devices_order entity ER diagram:
Figure 2 :Complete Entity Relationship Diagram
Explanation of figure 2: Product table: This table contains information about products, such as their ID, name, description, price, and quantity. It also has a foreign key reference to the Category table to indicate the category to which each product belongs. Director table: This table contains information about directors, such as their ID, full name, email, age, address, and phone number. Manager table: This table contains information about managers, such as their ID, email, full name, address, age, and phone number. It also has a foreign key reference to the Director table to indicate the director who manages each manager. Customer table: This table contains information about customers, such as their ID, full name, age, email, address, and phone number. Comment table: This table contains information about comments made by customers on products, such as the comment ID, content, product ID, and customer ID. It has foreign key references to both the Product and Customer tables. Device_Order table: This table contains information about orders made by customers, such as the order ID, date, status, customer ID, manager ID, and product ID. It has foreign key references to the Customer, Manager, and Product tables. Bill table: This table contains information about bills generated for orders, such as the bill ID, date, total amount, customer ID, and order ID. It has foreign key references to the Customer and Device_Order tables. Category table: This table contains information about categories of products, such as the category ID and name. Explanation ER diagram: The diagram above has 8 entities that are related to one another. Product and Category tables: This relationship is a one-to-many relationship, as one category can have many products, but a product can belong to only one category. The Product table has a foreign key reference to the Category table, which allows products to be grouped by their category. Comment table and Product table: This relationship is a one-to-many relationship, as one product can have many comments, but a comment can only be made on one product. The Comment table has a foreign key reference to the Product table, which allows comments to be associated with the product they are commenting on.
First Normal Form was used to introduce the theory of data normalization, and he went on to develop it further with Second and Third Normal Form. Subsequently, he collaborated with Raymond F. Boyce to create the Boyce-Codd Normal Form theory. The MySQL server's Theory of Data Normalization is still being improved. For instance, the sixth normal form is the subject of disputes. But, in the majority of real-world scenarios, the third normal form is where normalization excels. The following illustrates how normalization in SQL theory has changed throughout time (Peterson, 2023). Category table: 1NF: Each attribute in the Category table contains atomic values. There is a primary key (ID) for each record, which uniquely identifies each category. 2NF: There is only one candidate key (ID) in the Category table, and all non-key attributes depend on it. Therefore, the Category table satisfies the second normal form. 3NF: There are no transitive dependencies in the Category table. All non-key attributes depend only on the primary key, ID. Therefore, the Category table is in 3NF. Assuring that data dependencies make sense, i.e. that data is stored properly. The above database is designed by me according to normalized format: Product table: 1NF: Each attribute in the Product table contains atomic values. There is a primary key (Product_ID) for each record, which uniquely identifies each product. 2NF: There is only one candidate key (Product_ID) in the Product table, and all non-key attributes depend on it. Additionally, there is a foreign key (Category_id) that references the primary key of the Category table. Therefore, the Product table satisfies the second normal form. 3NF: There are no transitive dependencies in the Product table. All non-key attributes depend only on the primary key, Product_ID, or on the foreign key, Category_id. Therefore, the Product table is in 3NF. Director table:
1NF: Each attribute in the Director table contains atomic values. There is a primary key (Director_id) for each record, which uniquely identifies each director. 2NF: There is only one candidate key (Director_id) in the Director table, and all non-key attributes depend on it. Therefore, the Director table satisfies the second normal form. 3NF: There are no transitive dependencies in the Director table. All non-key attributes depend only on the primary key, Director_id. Therefore, the Director table is in 3NF Manager table: 1NF: Each attribute in the Manager table contains atomic values. There is a primary key (Manager_ID) for each record, which uniquely identifies each manager. 2NF: There is only one candidate key (Manager_ID) in the Manager table, and all non-key attributes depend on it. Additionally, there is a foreign key (Director_id) that references the primary key of the Director table. Therefore, the Manager table satisfies the second normal form. 3NF: There are no transitive dependencies in the Manager table. All non-key attributes depend only on the primary key, Manager_ID, or on the foreign key, Director_id. Therefore, the Manager table is in 3NF. Customer table: 1NF: Each attribute has an atomic value, meaning that it cannot be further divided into smaller components. 2NF: There is only one candidate key, Customer_ID, and all non-key attributes depend on this key. There are no partial dependencies. 3NF: There are no transitive dependencies between non-key attributes. All attributes in this table are directly dependent on the primary key, Customer_ID. Therefore, the Customer table is normalized up to 3NF. Comment table: 1NF: Each attribute has an atomic value.
Product table: No Field Name Data type Constraint Description 1 Product_id int Primary key Product_Id will be of integer type and constrain theprimary key so that id is unique and NOT NULL 2 Product_name Varchar(50) NOT NULL The product name will be typeface and the NOT NULL constraint will tell the typist to enter a value and cannot be left blank. 3 Description^ text^ NOT NULL^ The description name will be typeface and the NOT NULL constraint will tell the typist to enter a value and cannot be left blank. 4 Price^ INT^ NOT NULL^ The price will be typeface and the NOT NULL constraint will tell the typist to enter a value and cannot be left blank. 5 Category_id INT Foreign key The Category table's ID column is set as a foreign key referencing the Category table's ID column, proving that each product is associated with a valid category. Table 10 : product table data validation Director table: No Field Name Data type Constraint Description 1 Director_id^ int^ Primary key^ Director_Id will be of integer type and constrain the primary key so that id is unique and NOT NULL 2 Fullname^ varchar(50)^ NOT NULL^ The^ fullname will be typeface and the NOT NULL constraint will tell the typist to enter a value and cannot be left blank. 3 Email varchar(50) NOT NULL, UNIQUE Not null constraint: Email columns are set as not null, ensuring that each director has a email. Unique constraint: the Email column is set as unique, ensuring that each director has a unique email.
4 Director_age INT Allow null Age for integer value and can enter value or leave null. 5 Address Varchar(50) Allow null Address for string value and can enter value or leave null. 6 Phone^ INT^ NOT NULL, UNIQUE^ Not null constraint: Phone columns are set as not null, ensuring that each director has a Phone. Unique constraint: the phone column is set as unique, ensuring that each director has a unique phone. Table 11 :Director table data validation Manager table: No Field Name Data type Constraint Description 1 Manager_id int Primary key Manager_Id will be of integer type and constrain the primary key so that id is unique and NOT NULL 2 Fullname varchar(50) NOT NULL The fullname will be typeface and the NOT NULL constraint will tell the typist to enter a value and cannot be left blank. 3 Email^ Varchar(^50 )^ NOT NULL, UNIQUE^ Not null constraint: Email columns are set as not null, ensuring that each manager has a email. Unique constraint: the Email column is set as unique, ensuring that each manager has a unique email. 4 Manager_age INT Allow null Age for integer value and can enter value or leave null. 5 Address Varchar(50) Allow null Address for string value and can enter value or leave null. 6 Phone INT NOT NULL, UNIQUE Not null constraint: Phone columns are set as not null, ensuring that each director has a Phone. Unique constraint: the phone column is set as unique, ensuring that each director has a unique phone. Table 12 : manager table data validation Comment table:
