إعلانيمكن أن تكون علامتك التجارية هناعلامتك التجارية هنافي كل صفحة من Darslab · طلاب وأولياء أمور ومعلمونinfo@darslab.comاسأل عن الأسعارالأسعارinfo@darslab.com
☰ خطة العام · تكنولوجيا المعلومات والاتصالات (بالإنجليزية)

Tables, fields and keys

الأسبوع 5 · الوحدة ٢ «متطلبات ومراحل إنتاج المشروع» (٢) مرحلة إنشاء جداول قاعدة البيانات (١): قاعدة البيانات والجدول والسجل والحقل، أنواع الحقول وأحجامها، الجدول المسطح وقاعدة البيانات العلائقية، المفتاح الأساسي · 3 دروس
2

Types and sizes; flat or relational?

ICT Secondary 2 Term 1, Unit 2, Topic 2: pp. 40–44
GoalChoose a data type and a size for a field, and say what a flat table is and why a database may use several tables.
New words
data type · the kind of value a field holds: text, number or datefield size · for text, the largest number of characters; for a number, which kind of numberflat table · a database with one table that holds all the fieldsrelational database · a database whose data is split into tables joined by relations
Explanation

Each field has a name, a data type and a size. Text fields hold letters and symbols, such as a name; their size is the largest number of characters, for example 30. Numeric fields hold numbers: an integer is a whole number, a decimal has digits after the point. Date fields hold dates. Choose the type by what you do with the value. A telephone number, or a card code such as 0042, is stored as text: it may start with a zero that a number would lose, and nobody adds two telephone numbers. Keep one type for each field: do not put the word “none” in a field of numbers. A table that holds every field is a flat table. It is simple, but facts get repeated: if every player’s row repeats the team’s room, changing the room means editing many rows. So we move fields that share a common subject into their own table and join the tables by relations. This is a relational database.

FieldData typeSizeWhy
member_nonumericintegercounting numbers, whole
nametext30 charactersletters
card_codetext4 characterskeeps the leading zero
joineddatedatea day in the calendar
Worked examples
playerteamroom
SaraHawksRoom 2
AliHawksRoom 2
NourEaglesRoom 5
AdamHawksRoom 2
A flat table of players repeats the team’s room. Hawks play in Room 2 and appear three times; the cell Room 2 is repeated three times. If the Hawks move to Room 4, three rows must be edited, and a forgotten one leaves a mistake.
team_idteamroom|playerteam_id
1HawksRoom 2|Sara1
2EaglesRoom 5|Ali1
|Nour2
|Adam1
The relational version keeps each fact once. The teams table lists the team and its room one time each. The players table has only the player and the team number. Moving the Hawks means editing one row.
Class activity

On paper, choose the type and size for each field of a library table: book_id, title, writer, year, shelf_code (for example 0075). Write a reason for each choice. Then say which fact you would move to its own table if many books share it.

Practice
1
Which type fits a name, which a count of books, and which a birthday?
2
Why is a card code like 0042 stored as text?
3
What does a size of 30 mean for a text field?
4
What problem does a flat table have, and what do we do about it?