Study with the several resources on Docsity
Earn points by helping other students or get them with a premium plan
Prepare for your exams
Study with the several resources on Docsity
Earn points to download
Earn points by helping other students or get them with a premium plan
Assignment 2 1622 Database design and development. Effective for everyone who wants P.
Typology: Assignments
1 / 19
This page cannot be seen from the preview
Don't miss anything!
ASSIGNMENT 2 FRONT SHEET Qualification BTEC Level 5 HND Diploma in Computing Unit number and title Unit 4 : Database Design and Development Submission date Date Received 1st submission Re-submission Date Date Received 2nd submission Student Name (^) Tran Quang Hien Student ID GCS Class (^) GCS1003A Assessor name (^) Nguyen Tuan Dang 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 Grading grid
Student Name/ID Number: Tran Quang Hien/GCS 210109 Unit Number and Title: Unit 04: Database Design & Development Academic Year: 2022 Unit Assessor: Nguyen Tuan Dang Assignment Title: Database design Issue Date: Submission Date: Internal Verifier Name: Date: Submission Format: Format: ● The submission is in the form of an individual written report. This should be written in a concise, formal business style using single spacing and font size 12. You are required to make use of headings, paragraphs and subsections as appropriate, and all work must be supported with research and referenced using the Harvard referencing system. Please also provide a bibliography using the Harvard referencing system. Submission ● Students are compulsory to submit the assignment in due date and in a way requested by the Tutor. ● The form of submission will be a soft copy posted on http://cms.greenwich.edu.vn/. ● Remember to convert the word file into PDF file before the submission on CMS. Note: ● The individual Assignment must be your own work, and not copied by or from another student. ● If you use ideas, quotes or data (such as diagrams) from books, journals or other sources, you must reference your sources, using the Harvard style. ● Make sure that you understand and follow the guidelines to avoid plagiarism. Failure to comply this requirement will result in a failed assignment.
Unit Learning Outcomes: LO2 Develop a fully functional relational database system, based on an existing system design. LO3 Test the system against user and system requirements. LO4 Produce technical and user documentation 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:
Learning Outcomes and Assessment Criteria (Assignment 2 ): Learning Outcome Pass Merit Distinction LO 2 P2^ Develop^ the database system with evidence of user interface, output and data validations, and querying across multiple tables. P3 Implement a query language into the relational database system. M2 Implement a fully functional database system which includes system security and database maintenance. M3 Assess whether meaningful data has been extracted through the use of query tools to produce appropriate management information. D2 Evaluate the effectiveness of the database solution in relation to user and system requirements, and suggest improvements. LO3 P4 Test the system against user and system requirements. M4 Assess the effectiveness of the testing, including an explanation of the choice of test data used. LO4 P5 Produce technical and user documentation. M5 Produce technical and user documentation for a fully functional system, including ER Diagram and normalization statements and describing how the system works. D3 Assess any future improvements that may be required to ensure the continued effectiveness of the database system.
1.3 Create table Store and Manager Figure 3: Statements/Queries to create table Store and Manager I likewise relegated each store a unique Address, as found in the photos above, with the goal that they could be perceived by each other regardless of whether they had a similar name. There is no need to elaborate on the name because the scenario is comparable to the store's previously stated components. A Manager, as I said in the "Prerequisites of the application" segment, could have a few scholars. Thus, there is a many-to-numerous relationship. Since a connection between the two data sets is required, I construct an intersection table with foreign key fields for StoreAddress and ManagerID to lay out a many-to-numerous association. This is significant since they might have alluded to one another later on, and it will help interface current realities to assist with extending the subject's data. To make the connection capability, we'll have to add key limitations like ForeignKey and PrimaryKey. This will be examined further in the part that follows this portrayal of the data set plan.
1. 4 Create table Product Figure 4 : Statements/Queries to create table Product This table is about what the FPT Shop sells, which I referred to as "Product" in this situation. The ProductID must be "Not Null," or else the database would return a null result, which is dangerous. The table also includes ProductName and ProductInformation, which are used to keep track of the information on the product which the store have.
1.5 Create table Customer and Comment Figure 5: Statements/Queries to create table Customer and Comment The store will be utilized to sell products to diverse individuals. As a result, we'll need to construct a table with the customer's information. This table will be constructed in the same manner as the manager's table component, but with a few differences such as CustomerID, CustomerName, CustomerPhone, and CustomerAddress. In this situation, I used two distinct IDs: CommentID and CustomerID, which were obtained from the Comment and Product databases, respectively. The table Comment is used for customers to rate stores, products, and services. I created a unique ID for the Comment table and set it to "Identity" before auto-incrementing a column. The CommentID must be "Not Null," or else the database would return a null result, which is dangerous.
1. 6 Adding constraints (Pk and Fk) A primary key is typically used in databases to identify data between rows of a table. All tables must have a primary key. The relevant id types are often the primary keys. The father-child relationship is represented by the foreign key, which also serves as a connection between the two tables. If table a shares a trait with table b, then table b is the father, and table an is the child. Figure 6: Statements/Queries to adding constraints Pk and FK
2.2 Final database diagram Figure 8: Final database diagram The tables' primary keys are different. The father-child relationship is represented by the foreign key, which also serves as a connection between the two tables. If table a shares a trait with table b, then table b is the father and table an is the child. Since I had covered everything I needed to convey, I won't go into additional detail. The database diagram is shown below. 2.3 Final mock-up of the application The database normally follows the organization information model. Here, the portrayal of information is as hubs associated through joins between them. Dissimilar to the various leveled information base, it permits each record to have numerous youngsters and parent hubs to shape a summed-up diagram structure. Figure 9: Main interface for director
Figure 10: Information of director This is the principal screen for directors, from which they may input and delete the product from the database, manage the store and managers, see customers and their orders. Directors are the only people who can view this page. On a table, there is also a product list for the directors to keep track of. Figure 11: Edit data of product (Create / Update / Delete) This is the admin director page, where they can manage products, change their status, and delete them. Products can also be added and removed from the table by administrators. Figure 12: Edit data of customer (Create / Update / Delete) This is the manager director page, where they can manage customer, change their status, and delete them. Customer can also be added and removed from the table by the manager.
Figure 1 5 : Result at Director A ID is deleted at Customer and result. Figure 16: Queries to delete Figure 1 7 : Result at Customer With update, the record in the Customer table with ID = B123 will be edited name and the result. Figure 18: Queries to update Figure 1 9 : Result at Customer With select, the data from Director will be used. Figure 20: Queries to select Director
Figure 1 9 : Result at Director
2. Advanced query Stored Procedures Figure 20: Queries to create procedure Sp_SelectAllManager and result 3. Evaluate the effectiveness of the database solution Based on the requirements of the FPT Shop, important tables have been built that are users can register with their phone numbers as IDs and order or rate, comment on their bought devices, shop managers can take care of their stores and the director board can view all data from all shops. Essentially, these tables make the FPT shop easy to operate. In addition, users can add, alter, delete, and read data. Then, factual operations utilize select, a strategy that makes it easy for managers to control shop exercises. An easy-to-use model that has been built along with a nitty-gritty instruction on how it works is vital for the users to understand the framework's highlights and use completely. In short, the framework has addressed the greater part of the client's necessities. **Task 3 – Test the system (P4 - P5)
Figure 22: Flowchart of update data
