ACTIVITY – CREATING TABLES

GOAL

The goal of this practice is to discover the table creation process using MS-ACCESS software.

SCENARIO

You work as a database designer for a groceries reseller NorthWind that is selling its products to different warehouses all over the world. Your goal is to define the set of tables that may be used for storing sales information.The possible ERD structure is shown on the figure below.

Figure. The ERD for NorthWind System.

YOUR ASSIGNMENT

  1. Analyze the ERD structure presented on the figure and think about proper data types for all fields in the tables Customers, Orders, Order Details, Products and Employees. Read carefully the document entitled Access Data Types and think all definitions over in the context of these tables.
  2. Create a blank database called SaleSystem. In order to do so undertake the following steps:
  3. Click Blank Database under the New category of the New File menu. If you are on the Getting Started menu, select Create a New File to go to the New File menu.
  4. The File New Database dialog box opens. You can see any existing *.mdb files in the file list part of the window. The New File Database dialog box may initially open to the My Documents folder. Navigate to the folder you want to place your new database file in—in the case of the author, it is a folder named “Access db Files.”
  5. A default name of db1.mdb will appear in the File Name text box at the bottom of the window. Simply type over this default name with the name SaleSystem. (Typing the extension .mdb is optional because Access automatically supplies it if you do not.)
  6. Click the Create button. When the new database is created, Access automatically opens it for you.
  1. Create the tables Customers, Orders, Order Details, Products and Employeesusing the Table Design Window and define the names and types for all fields. Remember about naming rules which are the following:
  2. Field names can be from 1 to 64 characters.
  3. Field names can include letters, numbers, and many special characters.
  4. Field names cannot include a period (.), exclamation point (!), brackets ([ ]), or accent grave (`).
  5. You can’t use low-order ASCII characters, for example Ctrl-J or Ctrl-L (ASCII values 0 to 31).
  6. You can’t start with a blank space.
  7. You can’t use a double quotation mark (“) in the name of a Microsoft Access project file.

  1. Define the input mask (using input mask wizard) for Postal Code and Phone fields in table Customers.
  2. Change the format of OrderDate field to Medium Date.
  3. Change the format of ShippedDate field to Long Date.
  4. Change the format of UnitPrice (Order Details table) to Euroand set the validation rule that will enforce entering only positive numbers. Set the text “the price must be positive number”.
  5. Set the validation rule for OrderDate field. The OrderDate value must be greater than or equal to current date. Hint: in order to define the current date use predefined function Date(). Set validation text to “The date must be equal to or greater than current date”.
  6. Create a new field in table Employee, name it BirthDate and set the validation rule for the age of the employees. The age of the each employee should be lower then 100 years.
  7. Create the primary keys for all defined tables.
  8. Check your skills in changing a table design.
  9. Try to insert a new field (in table Customers) with name eMail and type hyperlink and put it between ContactTitle and Address fields.
  10. Delete field ContactName.
  11. In place of ContactName insert two fields ContactFName and ContactLName (the field type is Text for both cases, think about the length property for these fields).
  12. Define the input mask that will automatically force to uppercase the first character of the text entered in ContactFName and ContactLName fields in the Customers table.
  1. Define the new field in the table Customers. Name it DocNumber and set a data type to Text. Then define the input mask that will be playing the role of the placeholder for adocument number that consists of two capital letters and seven-digit number. All letters and digits are required. The letters and the digits are separated with special characters “-“ and “/”. The example of the document number that may be entered into this filed is BB-45/05/907.
  1. Join created tables according to the schema presented on the figure above and set up the referential integrity rules (before joining the tables read the document entitled Understanding relationships between tables). Use Relationship option from Tools menu.