Database Developer Case Study - Entities and Attributes of the Scenario - ER Model into Logical Database - IT Assignment Help

Assignment Task

Database Design & Development

Unit Learning Outcomes:

LO1 Use an appropriate design tool to design a relational database system for a substantial problem.  
LO2 Develop a fully functional relational database system, based on existing system design. 
LO3 Test the system against user and system requirements.
LO4 Produce technical and user documentation. 

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 needs 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.

The company transports chemical products from one location to another on behalf of registered customers. Customers are of three types namely Catogory1, 2 and 3. Each customer is given a unique customer number and his or her details have to be recorded in the system. 
Each transport operation is called a job, which involves picking up one or more loads of the products from a customer requested start location and delivers it to a customer requested destination. A unique number is given for each job and each load when they are created.  
A Load is transported using a particular transport unit, which consists of a lorry, a driver, assistant, and a container (for carrying the product) A container is fixed to a trailer so it needs to be coupled to a lorry at the start of a Job. (Single container can transport only one chemical product due to safety requirements) 
Payments for a job depends on customer type, product type (No Risk, High risk) and the load type (small, medium and large). Transport operations are run from 9 regional depots.  Jobs are allocated to individual depots to service.  Depots hold, manage and maintain their transport units which are permanently allocated to them to service Jobs.  Normally transport units are held at the base Depot they belong to. The final system should be able to provide the required information for management decision making and to handle daily operations efficiently and effectively.

Activity 01
Activity 1.1 
a. Identify the user and system requirements to design a database for the above scenario.
b.Identify entities and attributes of the given scenario and design a relational database system using the conceptual design (ER Model) by including identifiers (primary Key) of entities and cardinalities, participations of relationships.
c.Convert the ER Model into logical database design using a relational database model including primary keys foreign keys and referential Integrities. It should contain at least five interrelated tables.

Activity 1.2
a. Explain data normalization with examples. Check whether the provided logical design in task 1.1 is normalized. If not, normalize the database by removing the anomalies.
b.Design a set of simple interfaces to input and output for the above scenario using Wireframe or any interface-designing tool.
c.Explain the validation mechanisms to validate data in the tables with 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. 

Activity 2 
Activity 2.1 
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.2 
a. Explain the usage of DML with the below-mentioned queries by giving at least one single example per each case from the developed database.
Select 
             Update
             Delete        

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.4 
a.Explain the usage of the below SQL statements with the examples from the developed database
              Group by
              Order by
              Having
              Between
              Where

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 

Activity 3.2
a. Explain how the selected test data in task 3.1 b) can be used to improve the effectiveness of testing.

Activity 3.3
a. Get independent feedback on your database solution from the non-technical users and some developers (use surveys, questioners, interviews or any other feedback collecting method) and make recommendations and suggestions for improvements in a separate conclusion/recommendations section.

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

Activity 4.2
a. Your technical documentation should include some of the UML diagrams (Use case diagram Class diagram, etc.), flow charts for the important functionalities, context level DFD and the Level 1 DFD

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

This IT Assignment has been solved by our IT experts at My Uni Paper. Our Assignment Writing Experts are efficient to provide a fresh solution to this question. We are serving more than 10000+ Students in Australia, UK & US by helping them to score HD in their academics. Our experts are well trained to follow all marking rubrics & referencing style.

Be it a used or new solution, the quality of the work submitted by our assignment experts remains unhampered. You may continue to expect the same or even better quality with the used and new assignment solution files respectively. There’s one thing to be noticed that you could choose one between the two and acquire an HD either way. You could choose a new assignment solution file to get yourself an exclusive, plagiarism (with free Turnitin file), expert quality assignment or order an old solution file that was considered worthy of the highest distinction.