NSI (Numérique et Sciences Informatiques, "Digital Technology and Computer Science") is a computer science specialty in the French high school curriculum, taught in Première (year 11) and Terminale (year 12, students aged 17-18). This appendix is aimed at teachers following this curriculum, but the proposed progression can easily be adapted to other introductory database courses.
This chapter starts by recalling the NSI curriculum (in French). It then discusses a teaching progression based on the chapters of this book and shows how it aligns with the curriculum. Finally, it discusses practical implementation and proposes several projects that can be given to students.
The NSI curriculum is defined for the Première year and for the Terminale year (both documents in French).
As far as web application programming is concerned, the Première curriculum focuses on HTML, the HTTP protocol and the use of JavaScript in the browser. The Terminale curriculum covers databases.
Table 12.1 shows how the chapters of this book map to the two years of the NSI curriculum.
You can see that, for the Première year, the first chapters of this book (1, 2, 4 and 5) as well as chapter 9 match the curriculum. Note that CSS (chapter 3) is not part of the curriculum. For the Terminale year, chapters 6, 7 and 8 cover databases. The advanced concepts of sessions and cookies that we develop in this book are not part of the NSI curriculum.
| Chapter | NSI Première | NSI Terminale |
|---|---|---|
| Chapter 1: Web Page Server | X | |
| Chapter 2: HTML and Web Pages | X | |
| Chapter 3: CSS Style | ||
| Chapter 4: Static and Dynamic Modes | X | |
| Chapter 5: The HTTP Protocol | X | |
| Chapter 6: The Relational Model | X | |
| Chapter 7: Queries | X | |
| Chapter 8: Connecting the Server to the Database | X | |
| Chapter 9: Event Handling in JavaScript | X | |
| Chapter 10: Sessions and User Accounts |
Table 12.1: Mapping between the chapters of this book and the NSI years.
The NSI curriculum for the Terminale year is made up of six sections. Among them, the databases section is related to the development of web applications. This section is described as follows (our translation):
Developing computer processing requires handling ever-growing amounts of data. Organizing and storing this data is a key performance issue. Relational databases are a very widespread solution today. They make it possible to organize, store, update and query large volumes of structured data used simultaneously by different programs or different users. This is impossible with the tabular representations studied in Première. Very large database management systems (DBMS), on the order of a petabyte, are at the heart of many systems for collecting, storing and producing information. Data in a relational database is accessed through query and update statements, which can for example be written in the SQL language (_Structured Query Language_). Processing can combine SQL with a programming language. Students should be made aware of the need for a critical and responsible use of data.The NSI curriculum specifies four content items for the databases section. Each item is presented with a list of expected skills and comments. Table 12.2 summarizes these items (our translation).
| Content | Expected skills | Comments |
|---|---|---|
| Relational model: relation, attribute, domain, primary key, foreign key, relational schema. | Identify the concepts that define the relational model. | These concepts are used to express integrity constraints (domain, relation and reference). |
| Relational database. | Distinguish the structure of a database from its content. Spot anomalies in the schema of a database. | The structure is a set of relational schemas that satisfy the constraints of the relational model. Anomalies can be data redundancy or insertion, deletion and update anomalies. Working with large, realistic data sets is preferred. |
| Relational database management system. | Identify the services provided by a relational database management system: data persistence, management of concurrent access, efficient query processing, secure access. | The aim is to understand the role and purpose of the various services without going into how they work. |
| SQL language: queries and updates of a database. | Identify the components of a query. Build queries using the SQL clauses SELECT, FROM, WHERE, JOIN. Build insert and update statements using UPDATE, INSERT, DELETE. | DISTINCT, ORDER BY or aggregate functions may be used, without the GROUP BY and HAVING clauses. |
Table 12.2: The content of the NSI curriculum for the Terminale year.
Table 12.3 gives a detailed mapping between the content of the NSI curriculum for the Terminale year and the chapters of this book.
| NSI content for the Terminale year | Chapters of the book |
|---|---|
| Relational model | Chapter 6 |
| Relational database | Chapter 6 |
| Relational database management system | Chapter 8 |
| SQL language | Chapters 6 and 7 |
Table 12.3: Mapping between the chapters of this book and the content of the NSI curriculum for the Terminale year.
For the teaching progression, we think it is important to start with chapter 6 and explain the principles of the relational model. The formalization can perfectly well be left out. Practicing SQL, on the other hand, seems more than useful to us. Moreover, students really enjoy it.
Continuing with chapter 7 and presenting queries seems to be the natural progression. The extra abstraction of relational algebra is not necessary. On the other hand, we find it worthwhile to present single-table queries first, then multi-table queries. Here again, practicing SQL seems necessary to us.
Chapters 6 and 7 cover three content items out of four. We chose not to describe in detail what database management systems provide. Students see it during the hands-on sessions (we chose to use Postgres).
Our chapter 8 is not part of the NSI curriculum. We nevertheless find it worthwhile to illustrate the connection between a web server and a database. It makes databases more concrete and links them to web applications. It also explains the role of database management systems.
In summary, the teaching progression we propose has 5 parts:
To fully match the curriculum, the role of the database management system must be explained. This can be done during the hands-on sessions. With Postgres, you can even show the physical model (the files used to store the data).
As in our book, we suggest using NodeJS and Postgres to put the knowledge into practice. We recommend installing Postgres on every workstation.
The review questions and exercises we propose in the chapters of this book can be reused for teaching NSI. The image wall project can also be reused. Here we propose another project that is easy to adapt to our five-part teaching progression.
The goal of this project is to work with the IMDb database.
It is described here.
The files containing the data can be downloaded here.
We mainly use three files:
We propose a design with three tables and have built a database containing only a small part of all the downloadable data. The SQL files that install this database are available with 100 actors / actresses or 500 actors / actresses.
With this data, you can ask questions about table design and query writing. For example, students can be asked to build a website presenting the actors and actresses (table names) and giving access to the films in which they appear. Designing the database is an interesting exercise because there are foreign keys between the tables names, titles and principals. Writing the queries is also an interesting exercise, because joins are required and there is a lot of data, which raises display issues.
We propose an implementation with two solutions. The first solution lists all the actors and actresses in a drop-down list. The second solution lets the user type a few letters of the name and calls the query from JavaScript in the browser. You can see the result of this project here.