Lessons 22–23 · 2 lessons · N. I. Taylaqov (general editor), A. B. Akhmedov, M. D. Pardayeva, A. A. Abdug‘aniyev, U. M. Mirsanov. Informatics and Information Technologies, Grade 10, 1st edition. “Extremum-Press”, Tashkent, 2017
22
Practical lesson
Textbook: pp. 47–48
GoalDesigns the structure of a database for a given field: chooses the tables, fields and field types.
New words
table: one kind of object in the database, such as books or readers · jadval (entity)field: a column of a table with one kind of data · maydonrecord: a row of a table with the data of one object · yozuvprimary key: a field whose value is different for every record · birlamchi kalit
Explanation
Before creating a database you must design it on paper. First decide what the database will store information about: each kind is a separate table. Then write the fields and their types for each table. Every table must have a primary key whose values are not repeated (usually a number, for example BookID). To avoid writing the same data twice, the data are split into tables that are linked by a key: a reader’s name stands only in the readers table, not in the loans table. “Numbers” that are not calculated, such as phone numbers, are written as text, because the “+” and any leading zero matter. In this lesson the design is prepared first in the notebook and then in an Excel table; creating it in Access comes in the next lessons.
💡 Where will I use this?
In real life
A school choir wants to keep information about songs, singers and concerts. Instead of writing a singer’s name next to every concert again and again, they wrote it once in a separate table and linked it by a number. If a name changes, it is corrected in one place.
Who uses it
Software developers, hospital administrators and analysts plan data on paper in advance and divide it into tables.
Try it today
Choose something you know well (football teams, plants, songs) and on paper draw two tables with three fields each, marking one field that links them.
Worked examples
A school library design: Books(BookID, Title, Author, Year), Readers(ReaderID, FullName, Class), Loans(LoanID, BookID, ReaderID, DateOut). Three tables; BookID and ReaderID in Loans point to the keys of the other tables.
Field
Kind of data
BookID
number (grows by itself)
Title
text
Year
number
DateOut
date
phone
text
The kind of data for each field: BookID – a number that grows by itself with each new record; Title – text; Year – a number; DateOut – a date; phone – text (not calculated, has a “+”). In Excel enter 6 invented books and 4 readers and check that the data are in one format. The Access names of these types are learned in the next lesson.
Class activity
“Database engineers”: groups design 3 tables for a topic of their choice (a sports club, a hospital, a cinema) and another group tries to find repeated data in it.
Practice
1
What kind of data does a phone number field hold: a number or text? Why?
Text; it is not used in calculations and the “+” and a leading zero must be kept.
2
In the library design, which field links the Loans table with Books?
BookID
3
How many tables are in the library design?
3
4
Why is the reader’s name in the Readers table and not in the Loans table?
So the name is not repeated for every loan and, if it changes, is corrected in one place only.