1 / 11

Creating Tables

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

galvin
Download Presentation

Creating Tables

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Creating Tables Unit 9 - Databases

  2. 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

  3. 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

  4. 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

  5. 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

  6. 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

  7. 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

  8. 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

  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

  10. 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

  11. Data Dictionary Creating Tables Presentation Unit 9

More Related