2.2: Using Design View to Create Tables
- Page ID
- 15497
\( \newcommand{\vecs}[1]{\overset { \scriptstyle \rightharpoonup} {\mathbf{#1}} } \)
\( \newcommand{\vecd}[1]{\overset{-\!-\!\rightharpoonup}{\vphantom{a}\smash {#1}}} \)
\( \newcommand{\id}{\mathrm{id}}\) \( \newcommand{\Span}{\mathrm{span}}\)
( \newcommand{\kernel}{\mathrm{null}\,}\) \( \newcommand{\range}{\mathrm{range}\,}\)
\( \newcommand{\RealPart}{\mathrm{Re}}\) \( \newcommand{\ImaginaryPart}{\mathrm{Im}}\)
\( \newcommand{\Argument}{\mathrm{Arg}}\) \( \newcommand{\norm}[1]{\| #1 \|}\)
\( \newcommand{\inner}[2]{\langle #1, #2 \rangle}\)
\( \newcommand{\Span}{\mathrm{span}}\)
\( \newcommand{\id}{\mathrm{id}}\)
\( \newcommand{\Span}{\mathrm{span}}\)
\( \newcommand{\kernel}{\mathrm{null}\,}\)
\( \newcommand{\range}{\mathrm{range}\,}\)
\( \newcommand{\RealPart}{\mathrm{Re}}\)
\( \newcommand{\ImaginaryPart}{\mathrm{Im}}\)
\( \newcommand{\Argument}{\mathrm{Arg}}\)
\( \newcommand{\norm}[1]{\| #1 \|}\)
\( \newcommand{\inner}[2]{\langle #1, #2 \rangle}\)
\( \newcommand{\Span}{\mathrm{span}}\) \( \newcommand{\AA}{\unicode[.8,0]{x212B}}\)
\( \newcommand{\vectorA}[1]{\vec{#1}} % arrow\)
\( \newcommand{\vectorAt}[1]{\vec{\text{#1}}} % arrow\)
\( \newcommand{\vectorB}[1]{\overset { \scriptstyle \rightharpoonup} {\mathbf{#1}} } \)
\( \newcommand{\vectorC}[1]{\textbf{#1}} \)
\( \newcommand{\vectorD}[1]{\overrightarrow{#1}} \)
\( \newcommand{\vectorDt}[1]{\overrightarrow{\text{#1}}} \)
\( \newcommand{\vectE}[1]{\overset{-\!-\!\rightharpoonup}{\vphantom{a}\smash{\mathbf {#1}}}} \)
\( \newcommand{\vecs}[1]{\overset { \scriptstyle \rightharpoonup} {\mathbf{#1}} } \)
\( \newcommand{\vecd}[1]{\overset{-\!-\!\rightharpoonup}{\vphantom{a}\smash {#1}}} \)
\(\newcommand{\avec}{\mathbf a}\) \(\newcommand{\bvec}{\mathbf b}\) \(\newcommand{\cvec}{\mathbf c}\) \(\newcommand{\dvec}{\mathbf d}\) \(\newcommand{\dtil}{\widetilde{\mathbf d}}\) \(\newcommand{\evec}{\mathbf e}\) \(\newcommand{\fvec}{\mathbf f}\) \(\newcommand{\nvec}{\mathbf n}\) \(\newcommand{\pvec}{\mathbf p}\) \(\newcommand{\qvec}{\mathbf q}\) \(\newcommand{\svec}{\mathbf s}\) \(\newcommand{\tvec}{\mathbf t}\) \(\newcommand{\uvec}{\mathbf u}\) \(\newcommand{\vvec}{\mathbf v}\) \(\newcommand{\wvec}{\mathbf w}\) \(\newcommand{\xvec}{\mathbf x}\) \(\newcommand{\yvec}{\mathbf y}\) \(\newcommand{\zvec}{\mathbf z}\) \(\newcommand{\rvec}{\mathbf r}\) \(\newcommand{\mvec}{\mathbf m}\) \(\newcommand{\zerovec}{\mathbf 0}\) \(\newcommand{\onevec}{\mathbf 1}\) \(\newcommand{\real}{\mathbb R}\) \(\newcommand{\twovec}[2]{\left[\begin{array}{r}#1 \\ #2 \end{array}\right]}\) \(\newcommand{\ctwovec}[2]{\left[\begin{array}{c}#1 \\ #2 \end{array}\right]}\) \(\newcommand{\threevec}[3]{\left[\begin{array}{r}#1 \\ #2 \\ #3 \end{array}\right]}\) \(\newcommand{\cthreevec}[3]{\left[\begin{array}{c}#1 \\ #2 \\ #3 \end{array}\right]}\) \(\newcommand{\fourvec}[4]{\left[\begin{array}{r}#1 \\ #2 \\ #3 \\ #4 \end{array}\right]}\) \(\newcommand{\cfourvec}[4]{\left[\begin{array}{c}#1 \\ #2 \\ #3 \\ #4 \end{array}\right]}\) \(\newcommand{\fivevec}[5]{\left[\begin{array}{r}#1 \\ #2 \\ #3 \\ #4 \\ #5 \\ \end{array}\right]}\) \(\newcommand{\cfivevec}[5]{\left[\begin{array}{c}#1 \\ #2 \\ #3 \\ #4 \\ #5 \\ \end{array}\right]}\) \(\newcommand{\mattwo}[4]{\left[\begin{array}{rr}#1 \amp #2 \\ #3 \amp #4 \\ \end{array}\right]}\) \(\newcommand{\laspan}[1]{\text{Span}\{#1\}}\) \(\newcommand{\bcal}{\cal B}\) \(\newcommand{\ccal}{\cal C}\) \(\newcommand{\scal}{\cal S}\) \(\newcommand{\wcal}{\cal W}\) \(\newcommand{\ecal}{\cal E}\) \(\newcommand{\coords}[2]{\left\{#1\right\}_{#2}}\) \(\newcommand{\gray}[1]{\color{gray}{#1}}\) \(\newcommand{\lgray}[1]{\color{lightgray}{#1}}\) \(\newcommand{\rank}{\operatorname{rank}}\) \(\newcommand{\row}{\text{Row}}\) \(\newcommand{\col}{\text{Col}}\) \(\renewcommand{\row}{\text{Row}}\) \(\newcommand{\nul}{\text{Nul}}\) \(\newcommand{\var}{\text{Var}}\) \(\newcommand{\corr}{\text{corr}}\) \(\newcommand{\len}[1]{\left|#1\right|}\) \(\newcommand{\bbar}{\overline{\bvec}}\) \(\newcommand{\bhat}{\widehat{\bvec}}\) \(\newcommand{\bperp}{\bvec^\perp}\) \(\newcommand{\xhat}{\widehat{\xvec}}\) \(\newcommand{\vhat}{\widehat{\vvec}}\) \(\newcommand{\uhat}{\widehat{\uvec}}\) \(\newcommand{\what}{\widehat{\wvec}}\) \(\newcommand{\Sighat}{\widehat{\Sigma}}\) \(\newcommand{\lt}{<}\) \(\newcommand{\gt}{>}\) \(\newcommand{\amp}{&}\) \(\definecolor{fillinmathshade}{gray}{0.9}\)In this section we will step through the process of creating a table. From the web page for these notes download and open the MyUniversity Database.
- Click on the Create tab – use the mouse and click it to see the options available (Table, Table Templates, SharePoint Lists, Table Design, etc.):
- Click the Table Design option to begin a process to create a new table, the Department table. You are presented with a form where you can enter field definitions for a table. A field definition comprises field name, data type, and description. You can also set the table’s primary key. The Table Design form is shown below:
- Begin by entering each field name and choosing Text as the data type. In the description column you may enter a longer description of the field’s contents – these are treated as comments that may be useful for someone who is learning about your tables. Once you have done this you should have a form that looks like:
- Next we set the primary key for the table to be the deptCode field. Click the mouse in the spot just to the left of deptCode and then click the Primary Key icon which is in the upper left area of the form beside the View button. Access uses an icon to show the deptCode as the PK:
- At this point you should save your work by clicking the Save icon in the upper left hand corner of the form – you will be prompted to give the table a name – name it Department.
You should still be in Design View for the Department table. Note that you can press the F1 function key to get help pertinent to the location of the mouse cursor. If your cursor is positioned on a Field Name and you press F1 you will see a window pop open that displays suggestions from MS Access regarding how you should name fields. Try this. Before going any further, try pressing F1 in other locations too, such as Data Type and Description. We recommend that you read some of the information available to become more familiar with MS Access.
Data Types
MS Access provides several data types – we will discuss Text, Memo, Number, Date/Time, Currency, AutoNumber, Yes/No, and Lookup Wizard.
Text
If you specify that a field has the Text data type then Access will permit any characters to be placed in that field in a row of the table. This is a common choice when the data will not be used in calculations. The Text data type provides for values that have fewer than 256 characters. If you know that a maximum length less than 255 would be appropriate then you could use the Field Size property (discussed in the next section) to limit the maximum length of a text string.
Memo
A designer selects Memo if the field will have character data but the length might be longer than 255. Memo allows for a maximum length of 63,999 characters. Consider the description field of the Course table: could these be longer than 255 characters?
Number
If a field is used for storing values that are used in numerical calculations (e.g. quantity ordered) then Number is appropriate. The Field Size property (properties are discussed later) can be used to limit the number of storage locations used per value.
Date/Time
If a field contains date and or time values, then the Date/Time data type should be chosen. The Format property (discussed later) allows you to control how these values will appear to the user.
Currency
If a field will contain monetary values then the Currency data type should be chosen. This data type provides for numeric calculations that are accurate to 15 digits to the left of the decimal and 4 digits to the right of the decimal.
AutoNumber
If you choose AutoNumber MS Access will generate a value for you when a row is inserted into the table. You can, via the New Values property, arrange the numbers to be generated sequentially or randomly. Often control numbers for things like orders, invoices, registrations, etc. are numeric and we can leave it to the system to generate a next value for us.
Yes/No
This data type restricts possible values to yes or no.
Lookup Wizard
Sometimes you need to restrict values to a list of known values (e.g. a list of genders: Male, Female), or to values appearing as primary key values elsewhere in the database. Consider the creditHours field – a suitable list could be (1, 2, 3, 6). The Lookup Wizard is a suitable data type for these situations; when selected the system steps you through a series of windows where you can make the appropriate choices.
Exercises (Set 1)
These exercises refer to the Library database.
Exercise \(\PageIndex{1}\)
Consider the Member table. The id field was defined with the AutoNumber datatype. Experiment by adding new members and you will note that id values increase by 1. Now try deleting the last two members that you added. If you add those members back in what id values do they get? Are id values reused?
Exercise \(\PageIndex{2}\)
Consider the Book table. Add a field, paperback, that can be used to indicate whether or not a book is a paperback. Choose the YES/NO datatype. Save the design and switch to datasheet view. Now you will see how to enter such values – MS Access provides a box that is to be checked, or not. You can select (a ‘Yes’) using the mouse or by using the space bar. You should experiment with this.
Exercise \(\PageIndex{3}\)
Consider the Member table. Previously you added a gender field. Open the Member table in design view and change the datatype for gender to be Lookup Wizard. The wizard will automatically present 3 successive popup windows where you will:
Save the table and enter datasheet view so you can test out the datatype you have just created. You will notice the user sees a drop down list containing Male and Female, and so the user cannot enter/select an inappropriate value.
- specify that you are providing the lookup values;
- enter the values (Male and Female);
- specify that values are to be limited to your list.
Properties
Each field must have a data type as discussed above. According to the data type, MS Access will present to you a set of field properties that you can tailor for your table. We will discuss the following: Field Size, Format, Input Mask, Caption, Default Value, Validation Rule & Validation Text, Required, Indexed, Show Date Picker, and New Values.
Field Size
Consider a field like deptCode. Suppose the University uses 3 and 4 character values for department codes. Because of this it is reasonable to set the Field Size to 4 to limit the possibility that an end-user accidentally types a longer string of characters and thereby enters incorrect data. In this way we can limit the kinds of errors users make when they enter data and thus improve the overall quality of our database.
Data integrity is a serious issue for databases. Setting Field Size for Text data and Number data is a common thing to do. Often organizations will limit the data they collect for fields such as last name and first name (for example, 30 characters). If the data type is Number then values selected for Field Size are values such as Byte, Integer, Long Integer, etc. These kinds of values are associated with increasing number of memory locations used per value. A selection of Byte restricts storage to 1 byte of memory (8 bits), and since the largest positive integer that can be stored in a byte is 255, the values stored in the field are forced to be in the range from 0 to 255. Further information is readily available if you use the F1 function key on Field Size for a Number data type.
Format
The Format property is used to customize the way text, number, dates, and times are displayed to the end user. For instance, selecting Medium Date causes values like January 14, 2013 to be displayed as 14-Jan-13; selecting Long Date results in the display January-14-13. See Table \(\PageIndex{1}\) for examples. If you have Text data such as department code then you could force the display to be in capital letters by specifying > as the format code. An interesting Format specification is @;None. If this is used and if there is no value at all to display the word None will be displayed to the user. Another example: suppose the field is for the Canadian SIN. You may have seen these displayed to users with hyphens between the 3rd and 4th digits and the 6th and 7th digits. If the SIN is a Text field of length 9 it can be displayed this way by using a Format specification of @@@-@@@-@@@.
Value in field | Format Property | Displayed as |
---|---|---|
barack obama | > | BARACK OBAMA |
January 14, 2013 |
Medium Date |
14-Jan-13 |
January 14, 2013 |
Long Date |
January-14-13 |
@;None |
None |
|
786456789 |
@@@-@@@-@@@ |
786-456-789 |
@@@-@@@-@@@;None |
None |
Input Mask
The Input Mask property is used to force the user to add data according to some pattern. This is another nice feature to help improve the overall quality of data added to a database. When the cursor is in the Input Mask area a ‘builder button’ appears. When you click this button you will see a list of popular controls. If you were to choose the mask for phone number you will see the control !(999) 000-0000 appear. As a result of this choice, the user must enter a 7-digit phone number with an optional 3-digit area code).
Caption
If there is no caption, then the heading used in displays of data is the field name. Sometimes the field name is not what you want your users to see. For example, instead of the heading deptCode above a list of department codes, you may prefer to use the words Department Code. To accomplish this just enter such a heading in the caption property for the field.
Default Value
If some value for a field is very common then you should consider setting a default value. For example, if most courses are 3-credit hour courses then the value 3 can be set as the default for all new courses.
Validation Rule & Validation Text
If a field has a validation rule then the rule is tested whenever the user enters data. If the test fails the user is prompted with a message containing the validation text. A simple use of this could enforce the credit hours to be less than 10 by entering the rule <10 and the validation text Please enter a value between 0 and 10. Again, this is a nice feature to improve overall data quality.
Required
Consider the deptName field of the Department table. If a user enters data for a new department then it is unreasonable for the deptName field to not have a value. To ensure there will be a value we make the field required – i.e. we choose Yes for the Required property.
Indexed
MS Access automatically creates an index (unique – no duplicates) on a field that is the primary key. A unique index is a special internal data structure that Access builds to facilitate two things: (1) to ensure fast access to rows of data when the user specifies a value for such a field in a query, and (2) to ensure in the case of no duplicates that no two rows of the indexed table could have the same value for that field. The index data structure is very similar to the index you see at the back of books. An index comprises several entries where each entry has a value (a term used in the book) and a reference (a page number in a book) – in the case of duplicates allowed there can be several references (several pages where the term appears).
You may choose to have an index on any field. If a field could have duplicate values then you must choose an index that allows duplicates.
Show Date Picker
If the data type is Date/Time, then this selection enables the user to select a date using a picker – a convenient tool for data entry.
New Values
If the data type is AutoNumber you can use New Values to specify whether the next value for the field will be the next highest integer, or if it will be a random integer.
Exercises (Set 2)
In the next two exercises you are working with your University database.
Exercise \(\PageIndex{4}\)
Consider the Department table. In design view, set the deptCode field to have a length of 4 and use > as the display format. Set the length of the deptPhone field to be 10, and choose the Phone Number input mask. Save the table and switch to datasheet view. Use Table 2.0.1 as a guide and enter data into the Department table.
Exercise \(\PageIndex{5}\)
Create a Course table with attributes for department code, course number, title, short description, and credit hours. The credit hours field should be numeric with no decimal places, and the other fields are Text fields. Set the deptCode field to be Text with a length of 4 so that it matches the properties of deptCode in Department. Later it will be important that the deptCode field in both Department and Course are defined the same. Use Table 2.0.2 as a guide and enter data into the Course table.
The following exercises relate to the Library database.
Exercise \(\PageIndex{6}\)
Consider the firstName and lastName fields in the Member table of the Library database. Modify the caption for these fields to be First Name and Last Name respectively. Save the table and reopen in datasheet view. You will see these captions at the top of their respective columns.
Exercise \(\PageIndex{7}\)
The Loan table has fields that are defined with the DateTime datatype. Experiment with different formats for these dates.
Exercise \(\PageIndex{8}\)
Consider the id field in the Member table of the Library database. In design view change the increment property of the id field to be random instead of increment. This is a non-reversible action (but you can download the database later to get a fresh copy). Now add some new members. What can you say about the id values that are assigned?
Exercise \(\PageIndex{9}\)
Validation rules and validation text are important features to assist database users.
- Consider the Loan table and its date fields. MS Access has many built in functions one of which is Date() which always returns today’s date. So, to ensure that someone always enters a due date later than today, in the properties section for the date due field enter the following:
Validation rule: >= Date()
Validation text: Enter a future date.
In this situation we are entering a field-level validation rule. These rules are useful when we can state a requirement independent of other fields. Test the effect of this validation rule by switching to datasheet view and entering valid and invalid values for the due date.
- To ensure the date borrowed value is less than or equal to the date returned value we construct a validation rule that involves two fields. MS Access will not let you enter this rule at the field level; instead, such a rule must be specified at the table level. To enter a table level rule you must click in a small square just below the table name and choose properties:
and enter the properties:
Validation rule: [dateBorrowed]<=[dateReturned]
Validation text: Date returned cannot be prior to date borrowed.
The square braces, [ ], that appear in the expression are required. These inform MS Access of references to fields in the table.
Enter this rule and verify that it prevents the user from entering improper dates.
Primary Keys
This section assumes you have created the Department and Course tables. Every table should have a primary key, but this is just a rule-of-thumb that most database designers follow. In our database:
- The Department table has deptCode as its primary key.
- The Course table has a composite primary key – a key formed using two attributes: deptCode and courseNo.
To set a primary key the table must be open in Design View. You must first select the field (or combination of fields) and then click the Primary Key icon. This is straightforward for the Department table, but not for the Course table because its’ primary key comprises two fields. Because the PK involves more than one field we say this primary key is composite.
Exercises (Set 3)
Exercise \(\PageIndex{10}\)
Set the primary key for the Department table. With the Department table in Design View, select the deptCode field and then click on the Primary Key button. When done successfully you will see the deptCode field with a key icon beside it:
If MS Access rejects your primary key then you must examine the values you previously entered for deptCode – there must be some duplicated value. If this happens you must view the table in Datasheet View and find the duplicated value and make necessary changes.
Once MS Access has accepted your primary key you should open the table in datasheet view and experiment: How does MS Access respond if you try to create a new row with an existing primary key value?
Exercise \(\PageIndex{11}\)
Set the composite primary key for the Course table. To do this you first select one field, and then while holding the Control key down select the other field. With both fields selected, click the Primary Key button:
- Select the deptCode (click to the left of the deptCode field).
- To select the next field to be part of the PK: while holding the Control key down, click the courseNo field (and now release the Control key).
- Next, click the Primary Key icon. You will now see the key image beside both fields as in:
If MS Access rejects your primary key then you must examine the values you previously entered for deptCode and courseNo – there must be some duplicated value. If this happens open the table in Datasheet View and examine the rows to find duplicate values of the combination {deptCode, courseNo}.
Once MS Access has accepted your primary key you should open the table in datasheet view and experiment: How does MS Access respond if you try to create a new row with an existing primary key value?
Exercise \(\PageIndex{12}\)
(Advanced) Later on we discuss relationships between tables. Perhaps you are willing to try this now. The Department and Course tables are related to one another through the deptCode field. It is reasonable for us to expect that a deptCode value in a row of the Course table also appears in a row of the Department table. That is, if we are recording a course for the mathematics department then we expect the database to have a corresponding row in the Department table. To ensure this is the case we create a formal relationship between these two tables using the Relationships Tool:
- First, click Database Tools. Then click Relationships:
- The Relationships Tool opens and you see a blank relationships diagram:
- Position the mouse in the window, then right-click and choose Show Table. From the list of tables, select both Department and Course, and then click Add.
- With both tables showing on the diagram you must select the PK of Department, drag it to the Course table, and release the mouse button above the deptCode field of Course. If you follow the directions on the screen you will be able to select enforce referential integrity (RI) and then you end up with the following:
- If RI is enforced then it becomes impossible to have a row in Course without a matching row in Department.
You can compare your MyUniversity database to the University database provided on the web page for these notes.