Entity Relationship Diagram Database Assignment Help

This sample solution on entity relationship diagram database assignment will help students to complete their database assignments with the help of ERD

Task 1

Develop design documentation

An Entity Relationship Diagram

Entity Relationship Diagram

image 1

Data Dictionary

Entity Attributes Data type Length Description
CUSTOMER CUST_ID INT 11 PRIMARY KEY
CUST_FNAME VARCHAR 45
CUST_LNAME VARCHAR 45
CUST_PHONE VARCHAR 45
CUST_EMAIL VARCHAR 45
CUST_ADDRESS VARCHAR 45
CUST_CITY VARCHAR 45
CUST_STATE VARCHAR 45
CUST_ZIPCODE INT 11
PRODUCT PRODUCT_ID INT 11 PRIMARY KEY
PRODUCT_NAME VARCHAR 45
PRICE VARCHAR 45
TYPE VARCHAR 45
COLOR VARCHAR 45
CUSTOMER_ORDER ORDER_ID INT 11 PRIMARY KEY
CUST_ID INT 11 FOREIGN KEY
DATETIME DATETIME
TOTAL_AMOUNT CURRENCY
PAYMENT_STATUS VARCHAR 45
STAFF STAFF_ID INT 11 PRIMARY KEY
STAFF_NAME VARCHAR 45
STAFF_ADDRESS VARCHAR 45
STAFF_CITY VARCHAR 45
STAFF_STATE VARCHAR 45
STAFF_PHONE VARCHAR 45
MANAGER MANAGER_ID INT 11 PRIMARY KEY
STAFF_ID INT 11 FOREIGN KEY
REORDER REORDER_ID INT 11 PRIMARY KEY
PRODUCT_ID INT 11 FOREIGN KEY
QUANTITY INT 11
MANAGER_ID INT 11 FOREIGN KEY
ORDER_DETAIL

 

ORDER_DETAIL_ID INT 11 PRIMARY KEY
ORDER_ID INT 11 FOREIGN KEY
PRODUCT_ID INT 11 FOREIGN KEY
QUANTITY INT 11
PRICE CURRENCY

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]

Entity Relationship Diagram

image 2

The database does not find such entry hence popped up the message that the credentials are incorrect.

When the credentials are correct and matched:

Entity Relationship Diagram

image 3

There is an input mask applied on the password format which must be taken the values in asterisk form.

Entity Relationship Diagram

image 4

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

Entity Relationship Diagram

image 5

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]

Entity Relationship Diagram

image 6

It asks for the confirmation before making a deletion

Entity Relationship Diagram

image 7

Task 2

To develop a fully functional database

Tables

  1. Customer table: The customer table contains the basic details of the customers including customer names, email ids, address, etc.
    Entity Relationship Diagram
    image 8
  2. Customer Order: The customer order table contains the order details of the customer along with the payment status and amount of the placed order.
    Entity Relationship Diagram
    image 9
  3. Manager: The manager table specifies the manager of the staff with the use of their respective ids.
    Entity Relationship Diagram
    image 10
  4. 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.
    Entity Relationship Diagram
    image 11
  5. Product: The product table contains all the product details like product name, type, color, price etc.
    Entity Relationship Diagram
    image 12
  6. Reorder: The reorder table determines the product and quantity to be ordered again and the manager id handling the order scenario.
    Entity Relationship Diagram
    image 13
  7. Staff: The staff table contains the details of the staff members like name, address, contact number, etc
    Entity Relationship Diagram
    image 14

Queries

  1. Customer order detail query: This query fetches the detail of the order placed by the customer with the product details.
    Entity Relationship Diagram
    image 15
  2. Customer order query: This query fetches the details of the customer who has placed an order.
    Database Assignment Help
    image 16
  3. NSW State customer query: This query fetches the customer data who resides in the NSW state.
    Database Assignment Help
    image 17
  4. Product Detail query: This query fetches the details of the product.
    Database Assignment Help
    image 18
  5. 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.
    Database Assignment Help
    image 19
  6. Reorder product detail: This query fetches the details of the products which have been reordered by the customers.
    Database Assignment Help
    image 20

Forms

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]

  1. Customer: This form shows the order placed by the customer along with the customer and order details.
    Database Assignment Help
    image 21
  2. Customer order sub form: This form contains the total amount of the products which has been placed on a particular date.
    Database Assignment Help
    image 22
  3. 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.
    Database Assignment Help
    image 23
  4. Login Form: This login form denotes the email id and password of the customer to be entered during the login process.
    Database Assignment Help
    image 24
  5. Update product information: This form contains the details of the product. The products can be added, deleted, updated by the use of this form.
    Database Assignment Help
    image 25

Task 3

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
Test-Case (#1)

Database Assignment Help

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]

Test-Case (#2)

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.

Test-Case (#3)

Database Assignment Help

image 28 Figure 3 product_detail query SQL view

Database Assignment Help

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.

Test-Case (#4)

Database Assignment Help

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]

Test-Case (#5)

Technical 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

Test-Case (#7)

Technical Assignment Help

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.

Technical Assignment Help

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.

Test-Case (#8)

Technical Assignment Help

image 34

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]

Task 4

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
  1. 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.
  2. After installation of Microsoft enterprise a user has to open “Microsoft Access”.
  3. Then after a user has to go through the “File” menu to open a newly designed and develop database
  4. After successfully installation of database a user can be able to view the following screen.

Technical Assignment Help

image 35

  • Maintain:

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.

  1. Use of new database system
  2. The new database system can be open when a user taps two times consequently two times.
  3. 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.
  4. A user can able to view customer’s order by just double click on table customer_order.
  5. A user can able to get customer order detail by just click on to the table order_detail.
  6. 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.
  7. 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]
    Assignment Help Australia
    image 36
  8. 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.
    Assignment Help Australia
    image 37
  9. 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.
    Assignment Help Australia
    image 38
  10. Similarly user can get detail of state customer by the help of table NSW_state_customer_query.
    Assignment Help Australia
    image 39
  11. 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.
  12. 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.
    Assignment Help Australia
    image 40
  13. 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.
    Assignment Help Australia
    image 41
  14. 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.
    Assignment Help Australia
    image 42
  15. 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
    Assignment Help Australia
    image 43
  16. 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.
    Assignment Help Australia
    image 44
  17. 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.

Assignment Help Australia

References

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

top grade assignment

About the Author

Alice