☰ Contents · Computing

Database design and MS Access elements

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.

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.
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?
2
In the library design, which field links the Loans table with Books?
3
How many tables are in the library design?
4
Why is the reader’s name in the Readers table and not in the Loans table?