Data base system for the SmartMovers Transport Company Assignment Help

Qualification - Higher National Diploma in Computing

Unit Name - Database Design & Development

Unit Number - Unit 04

Assignment Title - Data base system for the SmartMovers Transport Company

Learning Outcome 1: Use an appropriate design tool to design a relational database system for a substantial problem.
Learning Outcome 2: Develop a fully functional relational database system, based on an existing system design.
Learning Outcome 3: Test the system against user and system requirements.
Learning Outcome 4: Produce technical and user documentation.

Get Unit 4 Database Design and Development - Higher National Diploma in Computing Assignment Help From Miracleskills And Score High Grades

Assignment brief

Case study
You are employed as a Database Developer for a large IT development company. The company has been approached by a transport company called SmartMovers which has grown to a level where need to have an automated system to handle day today operational activities to meet customer demands. You are given the job to design and develop a database solution for SmartMovers to meet their business requirements.

Introduction
Smart mover is Transport Company which offers transport for transporting chemicals for one location to another. The company offers lorry and container to transport chemical goods. It required database system to store the transaction details of the transporting company. The GUI has to be designed to interact with the data base. Through GUI, user can add, edit as well as delete records from the database.

System requirements
System requirements grouped into functional and non-functional requirements.

Functional requirements
• User can insert records into database
• User can delete records from database
• User can update records of the database
• User can view the customer details and order details
Non-functional requirements
• Data base system should provide security
• It should provide high performance
• It should have less errors
• It should be reliable for doing transactions
• The system should provide quick service
• It should be usable for the user
• It achieves high availability

Activity 1.2
a. Explain data normalization with examples. Check whether the provided logical design in task 1.1 is normalised. If not, normalize the database by removing the anomalies.

Normalization is performed in database to reduce redundancy. Without normalization, same attributes and data will reside in more than one table. It will occupy more space in the memory. The anomalies related with insert, delete as well as update can be resolved using redundancy.
Various normal forms are followed to avoid redundancy such as
• 1NF - First Normal form
• 2NF- Second Normal form-
• 3NF-Third Normal Form
• BCNF-B-Boyce C-coddN-Normal F-form
• 4NF-Fourth Normal Form and so on
Different normal forms will be represented through an example in the following:

First Normal Form
The attributes should have atomic values in the entity. Each attribute of the entity should not have more groups. The following table illustrates the table with attribute that has more than one value.

To represent the attribute with atomic value, the table is divided into two entities such as employee and department

Employee

Department

Second Normal Form
• Entities of second normal should satisfy first normal form
• Attributes should not have partial dependency
Each record of an entity can identified through unique attributes. For example, each employee can be identified only through emp_ID.

The following employee table will be illustrated with the 2 -normal form:

In the above table, record can be identified either through employee no or department number. In the second normal form, each record of the table uniquely identified with the key attribute. All the non-key attribute should depend on single key-attribute.

To concert the given "entity-employee" into second normal form, it will be divided into two tables such as
employee

Department

Third Normal Form
• Entities should be in second Normal Form
• No transitive dependency in the entities

The above has transitive dependency due to salary_ID and salary. Salary depends on the salary_ID. To remove the transitive dependency, table will be divided into employee and salary.

Employee

Salary

c. Explain the validation mechanisms to validate data in the tableswith examples.

Activity 1.3

a. Check whether the given design (ERD and Logical design)is representing the identified user and system requirements to access the effectiveness of the design.

c. Validation Mechanism
Validation is task preformed to ensure that the data entered in the table is meaningful, consistent, secure and clear. The validation is otherwise called as data validation. There are some methods to perform data validation such as
o Code
o Data type
o Data range
o Constraint
o Structured
For the current data base system, the following methods are proposed for data validation such as

Unique constraint
The constraint is used to enter only unique value for the attribute or set of attributes. This constraint does not duplicate values.
The constraint enforces to enter only unique values for column or set of columns. It does not allow duplicate values.

Not Null Constraints
The constraint enforces the attribute should have value. The column should not be left blank. While inserting as well as updating record, user has to enter value for the column that has not null constraint.

Default constraint
The database system will enter default values for the attributes. User my not has to enter values explicitly.

Check constraint
The attribute is associated with the check condition. While entering values for the attribute, the system verifies the entered value with the specified condition. When the condition is true then only system will allow that specific value.

Primary key constraint
The constraint is the combination of both unique and not null constraint. It enforces the column should have unique as well as not null values.

Foreign key constraint
The constraint establishes relationship between more than one tables. The parent table has the primary key and the child table has the foreign key.

Activity 2
User requirements
• User can enter the data into database
User can enter the data through the Graphical user interface of the system. New user can register their details with the system. New orders can be stored into the database. Employee details can be stored into the database.
• User can delete data from the database
Employee can delete product order details from the system.
• User can update data
Employee can update the records stored in the system. They can update any details like product quantity, start location and so on.
• Viewing payment details
Customer and employee can view the payment details from the system.
• View order details
Employee can view the customer's order details from the GUI.
System requirements
• Security
Security can be achieved through the log in credentials. Only registered user only can access the system. Each time, user has to give user credentials for accessing the system.
• Reliable
The system is more reliable. It does not incur for crashes while accessing the system.
• Availability
The system will be available for users without down time. The features and functions of the system will be available for users.
• Performance
The transaction with the speed like insert, delete, update are performed more efficiently with less time.
• Useable
Each function or feature of the database system can be used by the user

Develop the database system with evidence of user interface, output and data validations, and querying across multiple tables.
a. Develop a relational database system according to the ER diagram you have created (Use SQL DDL statements).
b. Provide evidence of the use of a suitable IDE to create a simple interface to insert, update and delete data in the database
c. Implement the validation methods explained in task 1.2-part c.
d. Display the details payment with the job details and the customer details using Join queries

Activity 2.3

a. Explain how security mechanisms have been used and the importance of these mechanisms for the security of the database. Implement proper security mechanisms (EX: -User groups, access permissions) in the developed database.

Activity 2.3
Database security shall be achieved with the below methods in database
• Roles
• Privileges
• Temporary table space
• Locks
• Row level locks
• Locks for Data manipulations
• Default table space
• Table level locks

Get Essential Online Computer Science Assignment Help From The Professionals!!

Activity 3

Activity 3.1
a. Provide a suitable test plan to test the system against user and system requirements
b. Provide relevant test cases for the database you have implemented
Note:- Learner needs to give expected results in a tabular format and screenshots of the actual results with the conclusion

Activity 3.1
Test plan is document which specifies the methods and techniques for validating the implemented system to ensure that the objectives of the system has attained. There are various test plans such as
• Test plan
• Master
• Phase
Test plan

Test cases:

Task: Execute:AAA

Task: Execute:AAA

User feedback form
Feedback form
1. Do you feel comfortable to access the software?
a. Yesb. No
2. Do you face any difficulties for using the software?
a. Yes
b. No
3. Do you easy with the software?
a. Yes
b. No
4. How is the software performance?
a. Excellent
b. Good
c. Need to improve
5. Are you satisfied with software and interfaces?
a. Yes
b. No

Analysis
The feedback form is given to 10 technical as well as non-technical users to gather feedback about the implemented system. The below table displays the user feedback.

The below diagram displays the average score of user feedback on each category

According to the user feedback, implemented database is useful and efficient for the user. The GUI of the implemented database system is user-friendly. It is easy to use and achieves high performance and objectives of the system.

Activity 4

Activity 4.1
a. Prepare a simple users' guide and a technical documentation for the support and maintenance of the software.

Activity 4.1
Home page

Users of the database system either customer or employee can access the system through the GUI. New customer has to click on the button "Customer" to access the system for placing the orders. Employee can also access the system while clicking the "Employee" button. Only registered users only can access the system.
New customer sign-up page

Customer should provide enough data to become registered customer. New customer has to click on the button "Sign-up" to provide their details and register with the system. After the details stored into database, the customer will have user name and password to access the system.

Customer log in page

Customer has to provide user name and password to access the database system.
Employee log in page

Employee has to provide user name and password to access the system.

Customer order page

Employee can view customer details

Employee can view the details from the database based on customer ID, cost of product, product ID and so on. This can be achieved through Group by and order by clause.

Activity 4.3
a. Suggest the future improvements that may be required to ensure the continued effectiveness of the database system.

Activity 4.3
The chemical transporting company smart movers maintain database to store the details of customer, employees and transactions. There are three different categories of the user such as 1, 2, and 3. The system has the details such as drivers, lorry, container, assistant, payment and so on.

The following recommendation and suggestions will be applied on the database system of Smart Mover to offers efficient service for the users. Now the users of the system are providing their log in credentials for accessing the system. In future, biometric based authentication will be used to improve the security of the system. It enables user provides their face image or finger print to access the database.

Conclusion
The database system is designed and developed for the smart movers with GUI interface. The GUI interface developed using Microsoft studio. MSSQL is used to create backend database. The users of the system can access through the GUI. It offers various forms to achieve different functional requirements from the system. The user details and their transaction details are stored in the database system to store as well as easy retrieval. Validations and testing performed for the database syste. It shows that, the developed system achieves its objectives.