الأسبوع 12 · الدرس ٨ «تصميم قواعد البيانات» (٢): جدول البيانات واختيار البرنامج وأنواع الحقول والمفتاح الأساسي · 3 دروس
3
Designing a table: field names, data types, primary key
ICT Prep 1 Term 1, Lesson 8: pp. 77–85
GoalDesign a table with field names and data types, and choose a primary key.
New words
design view · the place where you write the field names and choose the data type of each fielddata view · the place where you type the data or look at itdata type · the kind of value a field holds, such as text, number or automatic numberprimary key · a field whose value is different in every record and never repeats
Explanation
Designing a table is like planning a form before filling it. In the design view you write the name of each field and choose its data type. For our “Student results” table, we write id and choose automatic number, so the system gives each pupil a number by itself. We write name and choose text. We write the three marks and choose number, because we want to calculate with numbers. If a mark were saved as text, we could not add it. Then we save the table with a name, such as “Student results”. After that we go to the data view to type the data, and we can go back to the design view to change a field. Finally we choose a primary key: a field that is different for every record and never repeats. Here it is the id, an automatic number, which no two pupils share. A name is a poor key, because two pupils can have the same name. Steps differ in each program; we learn the ideas.
design view: names and types→save with a name→data view: type the data
Worked examples
A
B
1
Field name
Data type
2
id (primary key)
automatic number
3
Name
text
4
Arabic
number
5
English
number
6
Computer
number
This is the design of the “Student results” table: each field has a name and a data type. The id is the primary key.
Saved as
Can the program add 18 and 15?
number
yes, 33
text
no
Why must marks be numbers? Two marks saved as numbers can be added: 18 + 15 = 33. If they were saved as text they would be only letters and signs on the screen, and the program could not add them.
A
B
1
id
Name
2
4
Mina
3
9
Mina
Two pupils are both called Mina. Their names are the same, so the name cannot tell the records apart. Their ids are 4 and 9, and no two records ever share an id. So the id is the better primary key.
Class activity
Design a table on paper for a class library. Write five field names and choose a data type for each (text, number or automatic number). Choose the primary key and write one sentence that explains why it never repeats. Then add two invented records in the data view.
Practice
1
Which data type should the marks have, and why?
Number, because we want to calculate with them.
2
Put in order: type the data, save the table with a name, write field names and types.
Write field names and types; save the table with a name; type the data.
3
What is a primary key?
A field that is different in every record and never repeats.
4
Why is a pupil’s name a poor primary key?
Two pupils can have the same name, so it could repeat.
