Course Description
This unit introduces the key principles of databases. Learners will gain an understanding of the core concepts underlying database design, optimisation, and implementation. The different types of databases are introduced, with a focus on relational databases. Learners will be taught to use key commands in SQL to enable them to extract, modify, and create information in popular database management systems. Learners will put theory into practice through designing and building a relational database and integrating it with web applications.
Course Objectives
This unit introduces the key principles of databases. Learners will gain an understanding of the core concepts underlying database design, optimisation, and implementation. The different types of databases are introduced, with a focus on relational databases. Learners will be taught to use key commands in SQL to enable them to extract, modify, and create information in popular database management systems. Learners will put theory into practice through designing and building a relational database and integrating it with web applications.
Key Topics
- LO1: Understand the fundamental concepts of databases.
- LO2: Be able to design a relational database based on user specifications.
- LO3: Be able to use SQL to interact with databases.
Course Outline
| Week | Topics |
|---|
| 1 | Database concepts: tables (entities), attributes and relationships |
| 2 | Database models (hierarchical, network, relational) compared on reliability, maintainability and scalability |
| 3 | Database management systems: MySQL, PostgreSQL and MongoDB compared by strengths, weaknesses and use cases |
| 4 | Queries and views; introduction to SQL: SELECT, INSERT, UPDATE, DELETE and JOIN |
| 5 | Requirements gathering and entity-relationship diagrams for a given application (e.g. in LucidChart) |
| 6 | Normalisation using normal forms: reducing redundancy and improving data integrity |
| 7 | Converting an ER diagram to SQL and deploying the schema in PostgreSQL for testing |
| Midterm | Midterm evaluation |
| 9 | SQL queries for extracting, updating and manipulating records and tables |
| 10 | Server-side programming with PHP: HTTP GET and POST, connecting to SQL databases from PHP |
| 11 | Building a PHP web application on the database, including a basic login system |
| 12 | Applying Databases in practice: case studies |
| 13 | Assignment workshop: research, planning and referencing |
| 14 | Assignment workshop: drafting, feedback and improvement |
| 15 | Review and coursework completion |
| Final | Final assessment and submission |
Learning Outcomes
- LO1 Understand the fundamental concepts of databases.
- 1.1 Compare several major database management systems.
- 1.2 Describe the key concepts of database systems.
- 1.3 Explain the principle of queries and views in databases.
- 1.4 Explain the use of the SQL query language in databases.
- 1.5 Critically evaluate the benefits of the relational database model.
- LO2 Be able to design a relational database based on user specifications.
- 2.1 Identify user requirements and needs for data for particular applications.
- 2.2 Create an entity-relationship diagram based on user requirements.
- 2.3 Explain the process of normalisation and its importance in database design.
- 2.4 Implement a relational database schema that satisfies user requirements.
- LO3 Be able to use SQL to interact with databases.
- 3.1 Use SQL to formulate queries to extract data from databases.
- 3.2 Use SQL to formulate queries to manipulate and update data in databases.
- 3.3 Use PHP to access and update an SQL database for practical applications.
- 3.4 Explain how PHP can be used to interface with databases in server-side applications.
