Module ICP-3027:
Databases - Design & Implement

Module Facts

Run by School of Computer Science and Electronic Engineering

10 Credits or 5 ECTS Credits

Semester 2

Organiser: Mr Andrew Harbach

Overall aims and purpose

To provide an in depth understanding of the database design process, and a range of implementation issues including transactions and recovery, data storage techniques, query processing, distributed DBMSs and security.

Course content

• Database design methodologies;conceptual, logical and physical design; conceptual database design methodology;use of ER modelling to build a conceptual data model.

• Deriving a set of relations from a conceptual model; validation using normalization techniques; validation with respect to transactions;merging local data models to obtain a global model.

• Mapping a logical design to a physical design; designing base relations for a target DBMS; designing general constraints; selecting file organizations for transaction processing; using indexes; designing user views; security mechanisms.

• The need for database security; Types of threats; Mitigation techniques; MySQL grant tables; Understand how a firewall can protect a database in a web based environment.

• Properties of transactions; concurrency control; database backup and recovery; the database administrator role. Understand professional issues and responsibilities (e,g, backup)

• Query processing; query decomposition; query optimization; the cost of queries.

• Introduction to distributed DBMSs and their architecture; data allocation and fragmentation; transparencies; transactions, concurrency control, database recovery and query optimization in distributed systems; data replication.

Learning outcomes mapped to assessment criteria

  threshold

40%

good

60%

excellent

70%

Understand the basic concepts of Distributed DBMSs

Understands in depth distributed concurrency control and distributed database recovery Understands in broad terms the problems associated with a distributed database. Given a set of user requirements can determine the optimum distributed database design and design any necessary optimized distributed queries.

Understand and apply a conceptual database design methodology

Can design a logical model for a limited scenario involving a small number of entities. Can design a logical model for a realistic scenario involving a substantial number of entities. Can distinguish conceptual, logical and physical designs. Can read and understand a simple ER mode

Understand and apply a logical database design methodology for the relational model.

Can design a logical model for a realistic scenario involving a substantial number of entities. Can describe in outline how a set of normalised relations can be derived from a conceptual mode Can design a logical model for a limited scenario involving a small number of entities.

Understand and apply a physical database design methodology for a relational database.

Can understand the basic differences between the different types of storage/organization techniques. Can distinguish the appropriate type of storage/organization for a set of requirements. Can fully explain the details of all the storage techniques and discuss their applicability.

Understand database security issues.

Can provide basic mitigation against security threats. Can provide comprehensive mitigation against security threats and understand a DMZ. Can describe a range of common threats to a database system.

Understand transaction and recovery issues.

Can test transactions for serialiability; can apply concurrency control techniques to non trivial transactions. .Can explain the concurrency control techniques; can understand how a transaction log is used in database recovery. Can explain the fundamentals of transactions and why concurrency control mechanisms are needed.

Assessment Methods

Type Name Description Weight
Examination 40
Assignment 1 20
Assignment 2 20
Assignment 3 20

Teaching and Learning Strategy

Hours
Lecture

24 hours over 12 weeks

24
Laboratory

24 hours over 12 weeks including guided work and assignments.

24
Private study

Including self study and working on lab exercises

52

Transferable skills

  • Literacy - Proficiency in reading and writing through a variety of media
  • Numeracy - Proficiency in using numbers at appropriate levels of accuracy
  • Computer Literacy - Proficiency in using a varied range of computer software
  • Self-Management - Able to work unsupervised in an efficient, punctual and structured manner. To examine the outcomes of tasks and events, and judge levels of quality and importance
  • Exploring - Able to investigate, research and consider alternatives
  • Information retrieval - Able to access different and multiple sources of information
  • Critical analysis & Problem Solving - Able to deconstruct and analyse problems or complex situations. To find solutions to problems through analyses and exploration of all possibilities using appropriate methods, rescources and creativity.

Subject specific skills

  • Knowledge and understanding of facts, concepts, principles & theories
  • Use of such knowledge in modelling and design
  • Problem solving strategies
  • Deploy theory in design, implementation and evaluation of systems
  • Knowledge of information security issues
  • Specify, design or construct computer-based systems
  • Evaluate systems in terms of quality and trade-offs
  • Methods, techniques and tools for information modelling, management and security
  • Knowledge of systems architecture
  • Specify, deploy, verify and maintain information systems
  • Defining problems, managing design process and evaluating outcomes
  • System Design
  • Knowledge and/or understanding of appropriate scientific and engineering principles
  • Knowledge and understanding of mathematical principles
  • Specify, deploy, verify and maintain computer-based systems

Resources

Pre- and Co-requisite Modules

Courses including this module