Great Deal! Get Instant $10 FREE in Account on First Order + 10% Cashback on Every Order Order Now

Task 1 – (LO1) Use an appropriate design tool to design a relational database system for a substantial problem Task 1.1 (P1) Design a relational database system using appropriate design tools and...

1 answer below »

Task 1 – (LO1) Use an appropriate design tool to design a relational database system for a substantial problem

Task 1.1

(P1) Design a relational database system using appropriate design tools and techniques, containing at least four interrelated tables, with clear statements of user and system requirements.

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 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 relational database model including primary keys foreign keys and referential Integrities. It should contain at least five interrelated tables.

Note:-It is allowed to have your own assumptions and related attributes within the scope of the case study given.

Task 1.2

(M1) Produce a comprehensive design for a fully functional system, which includes interface and output designs, data validations and data normalisation.

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.

b. Design 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.

Task 1.3

(D1) Assess the effectiveness of the design in relation to user and system requirements

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.

Task 2 - LO2 Develop a fully functional relational database system, based on an existing system design

Task 2.1

(P2) 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

Task 2.2

(P3) Implement a query language into the relational database system

a. Explain the usage of DML with below mentioned queries by giving at least one single example per each case from the developed database.

Select

Update

Delete

Note: - The queries you have selected should be meaningful and relevant to the given scenario.

Task 2.3

(M2) Implement a fully functional database system, which includes system security and database maintenance.

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.

Task 2.4

(M3) Assess whether meaningful data has been extracted using query tools to produce appropriate management information.

a. Explain the usage of the below SQL statements with the examples from the developed database

Group by

Order by

Having

Between

Where

Task 3 - LO3 Test the systems against user and system requirements

Task 3.1

(P4) Test the system against user and system requirements.

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

Task 3.2

(M4) Assess the effectiveness of the testing, including an explanation of the choice of test data used.

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

Task 3.3

(D2) Evaluate the effectiveness of the database solution in relation to user and system requirements, and suggest improvements.

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.

Task 4- LO4 Produce technical and user documentation

Task 4.1

(P5) Produce technical and user documentation.

a. Prepare a simple users’ guide and a technical documentation for the support and maintenance of the software.

Task 4.2

(M5) Produce technical and user documentation for a fully functional system, including diagrams showing movement of data through the system, and flowcharts describing how the system works.

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

Task 4.3

(D3) Assess any future improvements that may be required to ensure the continued effectiveness of the database system.

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

Answered Same Day Mar 24, 2020

Solution

Navashree answered on Mar 31 2020
132 Votes
customercreate database SmartMovers2;
use SmartMovers2;
CREATE TABLE Customer(
    custID INT NOT NULL,
    custName Varchar(150) NOT NULL,
    phNumber char(10) NOT NULL,
    email varchar(100),
    address varchar(250) NOT NULL,
    category INT NOT NULL,
    PRIMARY KEY(custID)
);
CREATE TABLE Product(
    prodID INT NOT NULL,
    prodDesc varchar(250),
    prodType varchar(50) NOT NULL,
    PRIMARY KEY(prodID)
);
CREATE TABLE Depot(
    depotID INT NOT NULL,
    PRIMARY KEY(depotID)
);
CREATE TABLE Job(
    jobID INT NOT NULL,
    jobDesc varchar(250),
    dateBooking DATE NOT NULL,
    statusJob Varchar(50) NOT NULL,
    payment FLOAT NOT NULL,
    custID INT NOT NULL,
    depotID INT NOT NULL,
    PRIMARY KEY (jobID),
    FOREIGN KEY (custID) REFERENCES Customer(custID),
    FOREIGN KEY (depotID) REFERENCES Depot(depotID)
);
CREATE TABLE Transport_Unit(
    unitID INT NOT NULL,
    lo
yNumber char(4) NOT NULL,
    driverName varchar(150) NOT...
SOLUTION.PDF

Answer To This Question Is Available To Download

Related Questions & Answers

More Questions »

Submit New Assignment

Copy and Paste Your Assignment Here