Reference no: EM132798606
Unit 04 Database Design and Development - HND in Computing
Assignment - 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 Pass, Merit & Distinction
Learning Outcome 3: Test the system against user and system requirements.
Learning Outcome 4: Produce technical and user documentation.
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 they 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.
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 for 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 own transport units which are permanently allocated to them to service Jobs. Normally transport units are held at the base Depot they belong.
The final system should be able to provide the required information for management decision making and to handle daily operations efficiently and effectively.
Activity 1
1) Design and produce a comprehensive database design document for the above scenario and it should cover the below mentioned points.
• User and system requirements
• Complete normalized(up to 3rd normal form) ER Diagram
• Complete Logical database design.
• Validations that used to validate data in the tables
• Simple interfaces to input and output data to the tables
• Critical evaluation and access the effectiveness of the user and system requirements with the database design
Activity 2
2) Develop a comprehensive database system based on the database design document which is provided under Activity 1
• Implement the database according to the provided ER diagram and use DDL statements.
• Implement a GUI based system to view,insert,update,delete data in the tables.
• Implement validation methods explained in the Activity 1.
• Implement join queries display the details payment with the job details and the customer details
• Implement and access DML (select,update,delete) by giving meaningful examples from the developed database
• Explain and implement the security mechanisms in the developed database(EX: - User groups, access permissions).
• Explain and implement the below mentioned SQL statements from the developed database by giving meaningful examples.
• (Group by,Order by,Having,Between,Where)
Activity 3
3) Test the developed database system which has created in the Activity 2.
• Create a test plan to test the system against user and system requirements.
• Create test cases to test the developed database.
• Explain and access how the selected test data can be used improve the effectiveness of testing.
• Get independent feedback and evaluate the effectiveness on the developed database solution from the non-technical users and some developers.
Activity 4
4) Produce a technical documentation for the developed database system that contains a user guide, UML diagrams (Use case diagram, class diagram, flow charts, DFD level 0 and 1) and evaluate the database suggesting future enhancements to be implemented to ensure continuous effectiveness of the system.
Attachment:- Database Design and Development.rar