This sample solution on entity relationship diagram database assignment will help students to complete their database assignments with the help of ERD
Develop design documentation
An Entity Relationship Diagram
How verification and validation of the data has been done?
Validation and verification is a part of testing mechanism. We validate that whether the entered data is authorized or not. It is basically applied from security purposes so that no unauthorized user can access the confidential information of the organization. Our local printing and ink filling business has a secured database with appropriate logins. Only the authorized person can access the data. Below screenshots is a proof how our proposed system verifies that whether user is authorized or unauthorized. [Also Read: –System Analysis Design Assignment Help]
The database does not find such entry hence popped up the message that the credentials are incorrect.
When the credentials are correct and matched:
There is an input mask applied on the password format which must be taken the values in asterisk form.
Other than this there are certain other validations like no duplicate values allowed.
In the customer table:
- the values must not be duplicated
- Zero length must not be allowed
Some validations are applied in the form as well while adding, deleting or updating the data into the database. It will ask the user to fill each field of the form before adding any data. Any field left empty will not allow the database to be updated with that new product information. [Also Read: – ERD Database Management System Assignment]
It asks for the confirmation before making a deletion
To develop a fully functional database
- Customer table: The customer table contains the basic details of the customers including customer names, email ids, address, etc.
- Customer Order: The customer order table contains the order details of the customer along with the payment status and amount of the placed order.
- Manager: The manager table specifies the manager of the staff with the use of their respective ids.
- Order detail: The order detail table contains the details of the order placed along with the order id, product id and the quantity of product.
- Product: The product table contains all the product details like product name, type, color, price etc.
- Reorder: The reorder table determines the product and quantity to be ordered again and the manager id handling the order scenario.
- Staff: The staff table contains the details of the staff members like name, address, contact number, etc
- Customer order detail query: This query fetches the detail of the order placed by the customer with the product details.
- Customer order query: This query fetches the details of the customer who has placed an order.
- NSW State customer query: This query fetches the customer data who resides in the NSW state.
- Product Detail query: This query fetches the details of the product.
- Reorder query: This query fetches which product has been reordered. It also fetches the details of the staff that is managed by the manager of the order being placed.
- Reorder product detail: This query fetches the details of the products which have been reordered by the customers.
These forms are replaced with the fact those who don’t want to be the part of customer file. [Also Read: – IT Programming Database Assignment Help]
- Customer: This form shows the order placed by the customer along with the customer and order details.
- Customer order sub form: This form contains the total amount of the products which has been placed on a particular date.
- Customer Detail: This form is used to show the entire customer details by providing their respective ids. This form will display customer name, customer email, password, address, etc.
- Login Form: This login form denotes the email id and password of the customer to be entered during the login process.
- Update product information: This form contains the details of the product. The products can be added, deleted, updated by the use of this form.
We have prepared a test case report to check the functionalities of developed database. The test cases are shown below in the table: [Also Read: – Staff Payroll System Database Assignment Help]
|Test Cases||EXPECTED RESULT||ACTUAL RESULT|
|#1||The database tables are must be filled with suitable data||Yes, all the database tables filled with suitable data|
|#2||All the database tables must have the integrity constraints and primary keys||Yes, the integrity constraints and primary key are applied in the database tables|
|#3||The queries used in the database must be appropriate according to the case study and provide the right output.||Yes, all the queries are appropriate according to the instruction given in the case study and also provide the relevant output|
|#4||The forms used in the database must have the clean, clear and responsive interface||Yes, all the forms are created with the clean, clear and responsive interface|
|#5||The database table must not be containing the null values in the primary key fields.||Yes, there are not any tables available with the null value in their primary key field|
|#6||The table must accept the suitable data type entries in their suitable fields else shows the error message||Yes, the database tables contains only the suitable data type data and if the data type of entries not matches to the specified data type its shows an error message.|
|#7||Validation must be applied in the login forms and some table||Yes, the validation applied in the login form and update_product_information table.|
|#8||Input mask must be applied in the login form for the password||Yes, the input mask applied in the password section of login forms|
|#9||A reorder query must be created which shows the stock level of the product, when it runs.||Yes, a reorder query is created and also it checks the stock level of specific products|
image 26 Figure 1 database table customer
All the table of the database contains the appropriate information/data, as an example the customer table shown above contains all the information of customers such as: customer name, id, phone, email, etc. [Also Read: – Translation Memory Database Assignment Help]
image 27 Figure 2 customer table
The integrity constraints and primary key is defined in the database tables, as an example in the customer table shown above the cust_id is the primary key and it is auto increment type.
image 28 Figure 3 product_detail query SQL view
image 29 product_detail query design view
All the queries created in the database are appropriate according to the requirement given in the case study, like the product_detail is created to see the complete description or information about the specific product such as: product_id, name, price, color, etc. and its output the relevant and right information when the user run it.
image 30 customer form
All the forms are used in the database has the clean, clear and responsive interface, so that when the user input any data from it respond quickly according to the action performed by the user. [Also Read: – Office Solution Development Assignment Help]
image 31 customer table
The tables used in the database not contain any null value in their primary key fields, as shown above in the customer table
image 32 validation applied in login form
Validation is applied in the database login form so that when user input invalid username or password then a pop up message will occur as given above.
image 33 validation applied in the product_information update table
In the product_information update table validation is also applied so when the user add the product without filling the data in all of its field then it shows an pop up message as shown above.
The input mask is applied in the password field of the login form, so when the user input their password for login it shows in the forms of *.
Explain the benefits of using manipulation and query tools in a relational database system.
The benefits of using manipulations and query tools in a relational database system are:
- Query tools are the most efficient way of retrieving data from the database at a faster speed.
- Query tools have a standardized way of representation.
- Lesser amount of coding is required for running the query tools.
- Using manipulation and query tools, it is easier to update or delete data; data management is easier.
- Complex queries can be carried out easily.
- Using manipulation and query tools data and data format can be maintained with much ease.
Critically evaluate how meaningful data has been extracted through the use of query tools
Query tools are used to allow searching for data which can be either computed information or simple information retrieved as it is from the relational database. Whenever a query is executed, it fetches the desired information from the database. The queries can be modified according to the requirements and then the meaningful data can be extracted from the database. [Also Read: – Develop Web Application for Hotel Rooms Reservation System]
User Guide to facilitate a user or owner to install, maintains, and uses the new data base system
The newly designed database is easy to run and maintain and this can effectively store all the customer data according to their required details. This database is so much friendly to the non-technical user also as they can easily make an access within it. To facilitate a user or owner to maintains, and use the new database system is as follows:
- Installation of system
- First of all a user has to install “Microsoft enterprise” and has to ensure about new database must be developing on to the popular or same version.
- After installation of Microsoft enterprise a user has to open “Microsoft Access”.
- Then after a user has to go through the “File” menu to open a newly designed and develop database
- After successfully installation of database a user can be able to view the following screen.
A user can easily maintain this database because this database is protected through login panel. To maintain the database a user just has to make a weekly or monthly review about performance of the database.
- Use of new database system
- The new database system can be open when a user taps two times consequently two times.
- User of this database can view customer’s entry with their personal details along with their addresses. So that the user can get idea about user location by their postal address and deliver the order items on schedule time.
- A user can able to view customer’s order by just double click on table customer_order.
- A user can able to get customer order detail by just click on to the table order_detail.
- The user can be able to get detail about stock of the company including product id no., name of product, price, type and color of the product.
- If a product stock is reached at minimum level of stock then database of the company generate a notification message to the user along with full detail of product. [Also Read: – Big Mike Web Programming Assignment]
- A user don’t has to watch out customer order detail one by one, he/she can easily get full directory of customer order detail by just click on query customer_order_detail_query.
- User of this database or company employees can get details of customer with their full address. User can make idea about order delivery place by the help of customer_order_query. This table help user effectively and by using this table the user can save efforts for company.
- Similarly user can get detail of state customer by the help of table NSW_state_customer_query.
- When a user want to see order details then the user has to click on table Order_detail_query. This table helps to make data sheet of deliver and pending order of customer along with product payment detail and date.
- The user of this database can get full directory product detail by just click on query Product_detail_query with product id number, product_name, price, type and color.
- A customer can view history by just changing date from calendar date view. A customer can make order by filling their following detail within customer order form.
- By help of customer order sub form a customer can able to view order detail with date and total amount of order. Through this form a customer can make idea about total order. A user of this database has to maintain detail of order with total amount within customer order sub form.
- A customer can add detail of specific customer within database using customer detail form. To add detail a user has to fill customer id, customer father name, customer name, phone no., email address, city, state, postal address
- This is a login form of database; user has to fill their login id and password within specific area to get access into database. A manager or a staff has different login id and password. To make an update customer detail, order detail and payment detail a user has to make login within system.
- A user can update product information using update_product_information. An authorized user has to add product information, edit or delete product details and also can clear the filled details of product using “Clear” option.
Opentextbc.ca. (2016). Chapter 3 Characteristics and Benefits of a Database | Database Design – 2nd Edition. [online] Available at: https://opentextbc.ca/dbdesign01/chapter/chapter-3-characteristics-and-benefits-of-a-database.
Opentextbc.ca. (2016). Chapter 13 Database Development Process | Database Design. [online] Available at: https://opentextbc.ca/dbdesign/chapter/chapter-13-database-development-process.
Top Grade Assignment provide technical assignment writing service based on case study requirements in affordable prices and we are providing most flexible online assignment writing help, so book your Assignment with us, order now