คอมพิวเตอร์ III: สร้าง, queries และอธิบายแอปพลิเคชันเว็บฐานข้อมูล
GAC คอมพิวเตอร์ หัวข้อ 3 19:47 การบรรยายภาษาอังกฤษ · คำบรรยายภาษาอังกฤษ + 中文 ลอยตัวบนภาพ
บท
Transcript
Computing III asks you to connect a website to data and use SQL to support decisions. Our practical question is which classes have places left. The records are invented for learning, so the result is not an actual school booking recommendation. We will trace a complete request, create three related tables, calculate totals and display them in a browser. The existing notes stay in place, with separate practical notes providing the detailed example. Follow the current centre guide for assessed depth, required tools and submission evidence.
The front end runs in the reader's browser. The back end receives requests on a server. In this example the SQLite database runs inside that server process. The browser does not receive a database password or run the server's SQL. It asks for course data through an API. The server returns data, and the browser turns that data into visible table cells. Keeping these jobs distinct helps you locate a fault: wrong totals suggest the data or query, while correct JSON with a broken table suggests the browser code.
A route connects a request path to the code that handles it. The example's course route accepts a minimum enrolment count. A browser number field helps a reader, but another caller can send a request directly. The server must therefore enforce its own input rule. Keep SQL structure fixed and bind the accepted number as a value. Return a useful status when the input is invalid, the route is missing or a request method is unsupported. Validation, binding and response handling have different jobs; one does not replace the others.
One student may take several courses, and one course may contain several students. Two tables alone cannot represent both directions without awkward repeated values. An enrolment table holds one student and course pair per row. It links to the student table and the course table using their IDs. The same student ID may appear in several different pairs, as may the same course ID. The pair itself is unique. This design also avoids repeating the student's name in every enrolment row, so a name correction happens in one place.
SQL lets you select, relate and summarise stored records. A join connects matching keys. A where condition selects individual rows before grouping, while a having condition checks a group result such as its count. The original notes include a class-total query. Our practical example extends it by grouping by a unique class ID and retaining classes with no enrolments. These details matter: a query can run successfully while merging different classes or hiding an empty one. Always compare its result with a small dataset whose answers you can check by hand.
The server prepares a SQL statement and passes the accepted minimum separately. Binding protects this query from treating that value as SQL syntax. It does not prove the whole application is secure, and values do not substitute for column names or keywords. The rows are returned as JSON data. If no courses meet the minimum, the server still completed a valid query. It returns an empty array with a success status. The browser must handle that result as a normal state, rather than assuming every successful response contains a row.
A data project starts with a question and checks whether the available records can answer it. Clean missing or inconsistent values, check duplicate relationships and confirm that keys refer to real records. Then calculate a summary suited to the question. Our class totals describe current enrolments and spare capacity. They cannot explain why students chose a course or whether teaching improved learning. A clear report states these limits and separates its observation from a proposed next action. A chart can show a pattern clearly without establishing a cause.
Follow one request rather than memorising three disconnected files. The reader enters two and submits the form. The browser requests the course API with min equal to two. The server checks that this is one permitted whole number, binds it into the prepared query and receives the grouped rows. The response is a JSON object containing the accepted minimum and a course array. The browser checks the status, reads the object and makes table cells. Only course ten meets this minimum in the original dataset.
The supplied source includes a complete server, a complete browser page and a SQL seed file. Save the page's downloaded text copy as index dot HTML. Run the server with a compatible Node runtime and open the address it prints. You do not need an account or a package installation. The server uses a fresh in-memory database every time it starts, so browser results do not save edits. If a port is occupied, choose an automatic port rather than stopping someone else's process. This is a local teaching example, not a public service.
Draw the relationships before writing a query. Student one has two enrolment pairs, linking to classes ten and twenty. Class ten has three pairs, linking to students one, two and three. These links explain the many-to-many relationship directly. A pair holds IDs, not repeated names and course titles. Changing Mei's name therefore does not require changing every enrolment she has. Course IDs also separate classes that happen to share a title. Names are display information; IDs provide the identity used by the relationships.
The student and course tables are the parents of enrolment links. Their integer primary keys identify individual records. Not null prevents a required field from being absent, while the capacity check rejects a negative value. These are specific rules, not a general guarantee that the data is correct. For example a non-empty name could still be misspelled, and a capacity could be entered incorrectly while remaining non-negative. Explain what each constraint enforces and what it leaves for other checks. The complete seed script creates these tables in a fresh database.
The enrolment table references both parent tables. With foreign-key checking enabled, a pair cannot refer to a missing student or course. The script turns this checking on explicitly. Its composite primary key makes the student and course pair unique. Student one can join another class, but cannot be entered twice in the same class. A rejected pair does not cause SQLite to create the missing parent. These protections are visible in the practical tasks, which deliberately try a duplicate pair and a reference to student ninety-nine.
It is tempting to think that storing capacity three prevents four students joining. It does not. The database check only requires the stored capacity to be non-negative. The paired enrolment keys prevent duplicate pairs and missing references, but they do not compare the total with capacity. This example deliberately exposes no booking operation. A real operation would need a capacity rule and safe handling of simultaneous requests, so two bookings cannot both use the same final place. State this boundary when explaining what your example demonstrates; do not present it as a finished enrolment system.
The two Coding classes make an important query mistake visible. Class ten has three enrolments and class twenty has one. If the query groups only by title, both classes become one Coding group with a count of four. That might be correct for a question about titles, but it is wrong for a question about individual classes and their available places. Group by the course ID as well as the displayed title and capacity. Decide the unit being summarised before choosing a grouping key. A result that looks plausible still needs this identity check.
An inner join returns only courses with a matching enrolment. That would hide Music, precisely the class with the most spare places. A left join starts with the course table and preserves each course even when there is no matching enrolment row. The unmatched enrolment fields are empty. Count the matched student ID, which is not null in a real enrolment, to get zero for Music. This is why the left table and the counted expression both matter. Check the output against all four expected courses before trusting a larger dataset.
For Music, the left join has one preserved course row with no matching student ID. Count of the matched student ID ignores that empty value and gives zero. Count star counts the preserved row itself and gives one. Neither count function is broken; they count different things. The mistake is labelling a joined-row count as an enrolment count. In the exercise, deliberately replacing the expression reveals this error. It is useful to test an empty relationship because data containing only populated classes would hide the difference.
The threshold checks a class total after grouping. Minimum one keeps the three classes with enrolments and excludes Music. Minimum three keeps class ten, because its count equals three. The greater-than-or-equal operator includes equality; changing it to greater-than would answer a different question. A where condition on course ID would select a class before grouping. It does not express a condition on the completed count. Predict these small results before running the query, then compare them with the API output to confirm both the grouping and threshold logic.
Adding the course counts gives five enrolments. That is not five different students. Mei joins two classes and therefore contributes two pairs. A report that labels this total as unique students would overcount people even though every SQL operation ran correctly. Keep the distinction in your table labels, chart labels and interpretation. The original question concerns class places, so counting enrolment pairs per class is appropriate. A separate question about the number of people participating would need a different calculation based on distinct student identities.
The server reads every min parameter, not just the first one without checking. If there is more than one, it rejects the request. An absent parameter defaults to zero. The accepted text must represent one whole number from zero to ninety-nine, without a sign or a decimal point. The browser control expresses the same intended rule for readers, but callers can bypass that control. This is why a request such as min equals minus one is checked by the server itself. Input validation is part of the API contract, not just a page convenience.
The prepared query contains a question-mark placeholder where the threshold value belongs. The server supplies the accepted number to the statement's all method. User input is not concatenated into the query text. This protects this value position from being interpreted as SQL syntax. It is a specific protection: binding does not create access rules or make an unrelated route safe. It also does not let a value stand in for an SQL keyword or column name. For structural choices, use fixed server-owned alternatives rather than treating arbitrary input as query structure.
An HTTP status describes the request outcome. A valid threshold of four returns no matching courses and a success status of two hundred. A negative minimum violates the input rule and receives four hundred. An unknown route receives four hundred and four. A method other than GET receives four hundred and five because this teaching API is read-only. Do not use not found to mean an empty successful query. A browser also needs to distinguish these states, so that no courses found is not confused with a request that could not be completed.
The query response provides course IDs, titles, capacities and enrolment totals. It does not expose student names to the browser. This is enough to answer the practical question and display places left. Reducing returned fields also makes the API easier to explain. The fictional dataset contains no real student information, and it is only served on loopback. If this were adapted to real records or a public server, permission and access rules would become necessary. An invented local example is not evidence that those requirements have been implemented.
Fetch completing does not mean that the server accepted the request. A server can return an error status and a JSON explanation through an otherwise completed network exchange. The browser therefore reads the object and checks response dot ok before using its course array. If the response is unsuccessful, it displays an error instead of attempting to loop over missing result data. Network failures and server errors both need a reader-visible outcome. The supplied complete page handles these cases and enables another attempt after the request finishes.
The page creates row and cell elements, then assigns each value to text content. This means a course title is displayed as text rather than treated as HTML markup. The values shown are the course ID, title, enrolment count and remaining places. Remaining places is capacity minus the returned enrolment total. The complete dataset gives zero, one, one and three. This display calculation does not enforce capacity in the database. Keep the difference between showing a number and enforcing a booking rule clear when you adapt the example.
A failed request should not leave an old table looking like the new result. The example clears its rows when a load starts, displays a loading message and disables its button temporarily. A populated response fills the table. An empty response says that no courses meet the minimum. A failure explains that the local server should be checked and permits a retry. We verified this by stopping only the example server while leaving the page open, requesting again and then restarting it. The retry restored the four original course results.
Asynchronous requests do not always finish in the order they start. The sample keeps a latest-request number. Each new request receives the next number, and a response may update the page only if its number is still current. The same condition controls final button re-enabling. This prevents an older response from replacing a newer result. It is a browser display rule, not a database transaction rule. It does not make simultaneous bookings safe, because no booking operation exists in this example. Explain the boundary rather than giving one mechanism credit for another task.
For the decision about spare places, one course ID is the unit of analysis. Before calculating, check that records contain the needed values, IDs refer to existing parents, enrolment pairs are not repeated and capacities are non-negative. A real report also needs a date because these totals change. The database constraints catch some of these issues, but do not establish that every capacity is accurate or that every real enrolment was recorded. State the meaning of each row and total explicitly, so a reader does not confuse class counts, student counts and enrolment counts.
The original totals show spare places in classes twenty, thirty and forty. Music has three spare places, while Coding twenty and Art thirty each have one. A supported next action is to check whether those places are still available before offering them. Do not infer that Music is worse taught because fewer students enrolled. Do not predict next term's demand from these four invented records. A recommendation should be tied to the measured question and should name what still needs checking. This is how a descriptive SQL result can support a careful decision.
A bar chart can compare capacity and enrolments for each individual course. Label the Coding classes with their IDs so readers do not merge them. Show Music's zero instead of hiding the empty category. The report should explain its question, data checks, calculation or query, result and limits, followed by a sensible next action. Another reader should be able to reproduce the summary. A clear picture makes the data easier to inspect, but it does not establish why the pattern occurred. Keep causal claims separate from what these records actually measure.
Work through the practice tasks in your own copy. Predict the results before running the code so that the run checks your reasoning. Minimum one returns ten, twenty and thirty; minimum three returns ten. Adding Drama with no enrolments should retain it at minimum zero with two spare places. Adding student two to class twenty raises that class total to two, so minimum two now returns both Coding classes. A duplicate pair and a missing student reference should each be rejected for a different reason. Describe the relevant key rule rather than simply saying the database dislikes the input.
The explained answers connect the visible result to its cause in the code or data. The third table represents a many-to-many relationship. Title-only grouping merges different classes. Count star includes the unmatched course row. Foreign-key checking rejects a missing student; the paired primary key rejects the same enrolment twice. The server validates because callers can bypass a browser control. An empty successful query receives two hundred, while invalid input receives four hundred. End your project explanation with a recommendation about checking spare places and a limitation: invented current totals cannot predict actual future demand.