120 likes | 223 Views
Creating Tables. Unit 9 - Databases. Planning Your Table. Before you create the table you must plan: What fields you will include What the data-types of those fields will be What names to give them Which field to use as the primary key The range of acceptable values for each field
E N D
Creating Tables Unit 9 - Databases
Planning Your Table • Before you create the table you must plan: • What fields you will include • What the data-types of those fields will be • What names to give them • Which field to use as the primary key • The range of acceptable values for each field • This is important as changes may be difficult later • You can plan a table design using a data dictionary Creating Tables Presentation Unit 9
Data-type • Each field must have a data-type • Choosing the correct data-type is important • Helps ensure only valid data is entered • Allows you to use features related to the data-type • Commonly used data-types are: • Text • Number • Date • Yes/No Creating Tables Presentation Unit 9
Other Field Settings • As well as setting the name and data-type of a field there are other settings you can make. • Most of the settings are dependant on the data-type • Typical settings you might change include: • Field length • Format (e.g. date format, 20/7/05 or 20th July 2005) • Default value • Validation rule Creating Tables Presentation Unit 9
Text Data-type • Use where text or text combined with numbers will be stored in the field • Also used to store numbers that don’t require calculations to be done (e.g. phone numbers) • Default length is 50 characters, maximum length is 255 characters. • For longer text use the Memo data-type • No preset formats Creating Tables Presentation Unit 9
Numeric Data-type • Field size • Not set as a value (e.g. 10 digits) but as a method of storage • This sets the range of values that can be stored • Integer – range of minus 32,768 to plus 32,768 • Long Integer - range of minus/plus 2 million • Double stores very large real numbers. • Format • Allows formats such as • Fixed number of decimals • Scientific • Percent • Currency Creating Tables Presentation Unit 9
Auto-number • Special numeric data-type • Automatically set to long integer – don’t change it. • Automatically incremented by 1 when a new record is created • Ensures every record in a table has a unique value for this field • Ideal for primary key fields • BUT you cannot modify values in this field Creating Tables Presentation Unit 9
Date/time data-type • Used to store dates and or times • Will not allow input of invalid values (e.g. 32/7/05) • Various preset formats • Order of the day, month and year is set by Regional Settings in Control Panel. • Default value can be a fixed date or the =Now() function will insert the current date when inserting new records. Creating Tables Presentation Unit 9
Example • You are creating a database of secondary school student details and you want a field to hold the student’s age on the 1st September in the current year • What data-type should you choose? • What field length? • Would a default value be a good idea? • Are there any validation rules you could add? Creating Tables Presentation Unit 9
Example • The data-type should be Numeric • Field length should be Integer • Default value is probably not a good idea here • Validation rule could be added to restrict values to greater than 12 and less than 21 Creating Tables Presentation Unit 9
Data Dictionary Creating Tables Presentation Unit 9