الأسبوع 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.
Field
Data type
Size
Why
member_no
numeric
integer
counting numbers, whole
name
text
30 characters
letters
card_code
text
4 characters
keeps the leading zero
joined
date
date
a day in the calendar
Worked examples
player
team
room
Sara
Hawks
Room 2
Ali
Hawks
Room 2
Nour
Eagles
Room 5
Adam
Hawks
Room 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_id
team
room
|
player
team_id
1
Hawks
Room 2
|
Sara
1
2
Eagles
Room 5
|
Ali
1
|
Nour
2
|
Adam
1
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?
Text, numeric (integer), date.
2
Why is a card code like 0042 stored as text?
A number would drop the leading zeros, and we never add codes.
3
What does a size of 30 mean for a text field?
The field may hold up to 30 characters.
4
What problem does a flat table have, and what do we do about it?
Facts are repeated. We move them into their own table and join the tables by a relation.