Unit 4: Database Design & Development
Organisations depend on their databases to provide information essential for their day-to-day operations and to help them take advantage of today’s rapidly growing and maturing e-commerce opportunities. An understanding of database tools and technologies is an essential skill for designing and developing systems to support them.
Database systems continue to demand more complex data structures and interfaces, as applications get increasingly sophisticated. Most organisations collect and store large volumes of data, either on their own systems or in the cloud, and this data is used not just for the operational running of their business but also mined for other more intelligent and complex applications. Databases stand as the back-end of most systems used by organisations for their operations.
Database design and development is a fundamental and highly beneficial skill for computing students to master, regardless of their specialism.
The aim of this unit is to give students opportunities to develop an understanding of the concepts and issues relating to database design and development, as well as to provide the practical skills to translate that understanding into the design and creation of complex databases.
Topics included in this unit are: examination of different design tools and techniques; examination of different development software options; considering the development features of a fully functional robust solution covering data integrity, data validation, data consistency, data security and advanced database querying facilities across multiple tables; appropriate user interfaces for databases and for other externally linked systems; creating complex reports/dashboards, testing the system against the user and system requirements; and elements of complete system documentation.
On successful completion of this unit students will be able to use appropriate tools to design and develop a relational database system for a substantial problem. They will be able to test the system to ensure it meets user and system requirements and fully document the system by providing technical and user documentation. For practical purposes, this unit covers relational databases and related tools and techniques. A brief overview of object-oriented databases will also be covered.
Students will develop skills such as communication literacy, critical thinking, analysis, reasoning and interpretation, which are crucial for gaining employment and developing academic competence.
LO1: Use an appropriate design tool to design a relational database system for a substantial problem of programming an application
- The role of database systems e.g. as back-end systems, in e-commerce, for data mining applications etc.
- Determining user and system requirements.
- Design tools and techniques for a relational database system.
- Logical design for relational databases e.g. tables, data elements, data types, indexes, primary/foreign keys, entity relationship modelling, referential integrity, data normalisation to third normal form.
- Designs for data integrity, data validations, data security and data controls.
- User interface design.
- Output designs for user requirements.
- Overview of object-oriented databases and their design tools.
LO2: Develop a fully functional relational database system, based on an existing system design
- Consideration of database and platform options for system development.
- Examination of different software development options for developing the relational database system.
- Implementation of the physical data model based on the logical model.
- Data stores, internal storage and external storage (e.g. the cloud).
- Implementation of security elements in databases.
- Relational databases with controls like data validation using; input masks, drop down lists, option buttons.
- User interface for requirements, functionality, reliability, consistency and performance.
- Consideration of interface links with other systems e.g. internet-based applications.
- Data manipulation using appropriate query tools, including complex queries to query across multiple tables, and using functions and formulae.
- Database maintenance and data manipulation: inserts, updates, amendments, deletions, data backup and recovery.
- System reports using report writing tools and report generators, dashboards.
LO3: Test the system against user and system requirements
- Identify elements of the system that need to be tested.
- Consider data that should be used to fully test the system.
- Match tests against user and system requirements.
- Test procedures to be used: test plans, test models e.g. white box, black box; testing documentation.
- Functional and system testing and testing the robustness of the system, including help menus, pop-ups, hot-spots, data validation checks.
LO4: Produce technical and user documentation
- Technical and user documentation and their contents.
- The documentation can include diagrams showing movement of data through the system, and flowcharts describing how the system works. Documentation could also extend to user guides and any initial design and implementation plans.