|
|
Access and databases. Creating a Database.Стр 1 из 3Следующая ⇒ Launching Microsoft Access Microsoft Access is launched from the Windows desktop in a manner similar to that used to launch Microsoft Word, Excel, or PowerPoint. You can launch Microsoft Access either from the desktop shortcut or from the Start program. - To open Microsoft Access using the desktop shortcut: Double-click the Microsoft Access shortcut icon on the Windows desktop. - To open Microsoft Access using the Start program: 1) Click the Start button located in the lower left corner of the Windows screen. 2) Click the Programs option on the Start menu. 3) Click the Microsoft Access selection. Creating a new database. When you open Microsoft Access, Access 2003 Getting Started Task Pane will appear on the right side of your Access 2003 screen. Сlick the left mouse button on Create a new file. The New File menu screen at the on the right will appear when you click the left mouse button on Create a new file. Click–on Blank database:
Picture 3 creating a new database and saving its. In the upper left corner of the File New Database menu screen that appears, you will see a Save in: Z:\ group №\... Next click-in the area to the right of File Name:. Delete any text (db1) that is entered in the area and then type-in: Dean's office. And the area to the right of Save as type don’t change:.mdb Then click-on the Create button. Creating a table «Lecturers»
Picture 4 Microsoft Access ContactManagement.
To begin designing the database, please click-on Tables in the left border and then click-on the button «Design» at the top or double click-on «Create table in Design view» in the "white" area.
Picture 5 Design view
There are three things: Field name, Data Type, and Description, and, in the lower half of the window Field Properties. Addsome field name for creating the table «Lecturer»:
Table 3 Structure of the table «Lecturers». Part 1.
Now notice in the lower part of the screen, under Field Properties, that a box appeared when you selected the Text Data Type. This box is "tailored" to the Text Data Type that you selected above:
Picture 6 Field Properties for Text Data Type.
Field Size: Is currently set to 50 characters. That's pretty large for a name. So, click-in this area and change the number to 15 (you can make this larger or smaller later if you have to). Also you should change this area for First name and Middle name: Table 4 Field name and its properties.
Before saving the table we need to assign a primary key. Click on the name of the field that will be the primary key (ID lecturer). Click on the Primary key button Save this table. Then add some field name more: Table 5 Structure of the table «Lecturers». Part 2
Some annotations:
- Date of birth: type- Date/Time. We'll use an Input Mask for Date of birth for the convenience of date input:
Picture 7 Field Properties. Input Mask.
Сlick-in the area to the right of Input Mask and write: 99.99.99 or click-on the button «the three dots» and select any form by the Input Mask Wizard.
- Position: Field Size: 25 Create a combo box for this field.To start the wizard you will need to position yourself in the design window of the table over the type of data field («Position») that will contain the data extracted from the list and select Lookup wizard... Select «I will type in the values that I want». A second screen appears where we need to indicate the values filling the column:
· Professor · Associate professor · Senior lecturer · Assistant
Once we have introduced all of the values press the Next> button to continue to the next screen where we can type a name for the column.
- Experience. Type: Number.
Field Size: integer Also this value should be >0. For this:
Picture 8 Field Properties. Validation rule and Validation text
Validation rule: This property permits us to control the entry of data according to a specified criteria.The criteria must to be typed to ensure that any value introduced into the field is good. Write in this area: >0, so when data is entered and not matches the condition, an error message will be displayed.
Validation text: In this property we will write the text which we wish to appear in the case of us trying to introduce a value into a field that does not comply with the validation rule specified before.
Write this area: Erorr- Length of service shuold be >0.
- Phone: type – Number. Field Size: 9 Use Input Mask for this field: 999-99-99
In the end you have the table «Lecturers»: Table 6 All structure of the table «Lecturers»
Save this table. Creating a table «Students» Table 7 Structure of the table «Students
Some annotations: - ID student This field will be the Primary key for this table. Field Size: Integer or Autonumerical - Last name and Middle name Field Size: 15 - First name Field Size: 12 - № group Field Size: Integer - Address Field Size: 30 - Phone Use for this field a Input Mask: 999-99-99 - Medallist Field Size: 4 Create a combo box · Yes · No Also make «No» - default value
Picture 9 Field Properties. Default Value.
Save this table. Field Size: 30 - Term Field Size: Integer - ID lecturer Field Size: Integer You should link the table «Disciplines» and the table «Lecturers». It is always easier to pick from a list than to remember what the choices are. In the Table Design View, in the bottom section, each field has a Lookup wizard (We used a Lookup wizard for a creating combo box). Now select «I want the lookup column to look up the valuesin a table or query».
Picture 10 Lookup Wizard.
In the View box, if we select Tables, only tables defined in the database will appear. If we select Queries, only queries will appear, and if we select Both, both tables and queries will appear. So you should select the table «Lecturers» and click-on the «Next >» button.
In this window we have in the list on the left the columns of the table or selected query, and in the list on the right we will place the columns that we wish to appear in the lookup list. To select a column click on it so that it stands out and then press the The The The
Field Size: 10 Create a combo box · Exam · Test
Field Size: Byte
Creating relationships To create relationships in Access2003 firstly we need to click on the The Show table dialogue box will appear. Click on one of the tables required in the relationship and click on the Add button; or double-click the name of the table. Repeat the previous step to add the second table, and so on. Finally click on the Close button to finish adding tables.
Double click on relation line: the Edit relationships dialogue box appears next:
Picture 12 Edit relationships
Observe at the bottom the Relationship type assigned depends on the characteristics of the related fields (in our case One-to-Many) Activate the Enforce Referential integrity by clicking on it. Also the boxes Cascade Update Related Fields and Cascade Delete Related Records can be activated. To terminate, click on the Create button.
Referential integrity is a system of rules that relational database systems use in order to ensure that data stored in related tables are valid. If Referential Integrity is checked, Access will not allow us to insert a record in the related table if there is no matching record in the primary table.
Referential integrity has two associated actions: Cascade update the related fields: If checked, when a value is changed in the related field in the primary table, the values in its related records in the related table will be automatically changed. Cascade delete related records: If checked, when a record is deleted from the primary table, all of the related records in the related table will also be deleted
Save relationships.
References: http://www.teacherclick.com/access2003/index.htm http://www.jegsworks.com/lessons/databases/intro/index.html http://www.hostitwise.com/microsoft_access_2003.html
Access and databases. Creating a Database.
A database is an organized collection of data for one or more purposes, usually in digital form. A Database Management System (DBMS) is a program (set of programs) that stores and organizes data. One such organization method is relational where data is organized by means of related tables called relations and hence the name Relational Database Management System (RDBMS). RDBMS stores information in related relations (tables, subjects) which can be related using common fields. The term relational database was originally defined by and is attributed to Edgar Codd at IBM Almaden Research Center in 1970. Relational database theory uses a set of mathematical terms, which are roughly equivalent to SQL database terminology. The table below summarizes some of the most important relational database terms and their SQL database equivalents.
Picture 1. Set of terms.
A relationship is an association that is established between columns (fields) in two tables. Three types of relationships can be distinguished: - One-to-one relationship: when one record in a table can have only one matching record the another table and vice versa. This type of relationship is used normally when we want to store information that only have some records in a table. - One-to-many relationship: When a record in a table can have only one matching record in the other table, and a record from the second table can have many matching records in the first table. - Many-to-many relationships: When a record in a table can have many matching records in a second table and when a record in the second table can have many matching records in the first table. A many-to-many relationship is only possible by defining an intermediate table between the two other tables.
Picture 2 Three types of relationships
MS Access is a Relational Database Management System (RDBMS). Like all database systems MS Access stores database information in a database file (MS Access uses.mdb extension), MS Access.mdb files store information about your database objects such as tables, queries, forms, macros, reports, access web pages, and modules. An RDBMS is used to store, retrieve and update data via facilities and mechanisms that are supported from within the database system, these mechanisms include tools to: - view the data. - update the data. - facility to store data (Tables). - present data (Forms, Reports, Access Web Pages). - generate reports from the database dynamically (Wizards). - Security utilities – only authorized users can access the database. - retrieve subset of the data based on a defined selection criteria (Queries). MS Access uses Structured Query Language (see query section) to support query services. - backing up and restoring data.
MS Access 2003 supports all these features and provide objects that are used to implement each tool. The relational database objects that are supported by MS Access 2003 are organized in a database file. A relational database is a database that conforms to relational model theory. The software used in a relational database is called a relational database management system (RDBMS). A Microsoft Access database is made up of several components including:
Table 1. Microsoft Access components.
Tables are collection of records about a particular subject (i.e., Student). Each record in the table is uniquely identified by the primary key of the table. Primary Keys are fields in the table that uniquely identifies a row (record). Access supports the following data types:
Also table fields have some properties. Field properties are a set of characteristics that provide additional control over how the data in a field is stored, entered, or displayed. Which properties are available depends on a field's data type. Table 2 Field properties
Practice. Creating a database «Dean's office» Launching Microsoft Access Microsoft Access is launched from the Windows desktop in a manner similar to that used to launch Microsoft Word, Excel, or PowerPoint. You can launch Microsoft Access either from the desktop shortcut or from the Start program. - To open Microsoft Access using the desktop shortcut: Double-click the Microsoft Access shortcut icon on the Windows desktop. - To open Microsoft Access using the Start program: 1) Click the Start button located in the lower left corner of the Windows screen. 2) Click the Programs option on the Start menu. 3) Click the Microsoft Access selection. Creating a new database. When you open Microsoft Access, Access 2003 Getting Started Task Pane will appear on the right side of your Access 2003 screen. Сlick the left mouse button on Create a new file. The New File menu screen at the on the right will appear when you click the left mouse button on Create a new file. Click–on Blank database:
Picture 3 creating a new database and saving its. In the upper left corner of the File New Database menu screen that appears, you will see a Save in: Z:\ group №\... Next click-in the area to the right of File Name:. Delete any text (db1) that is entered in the area and then type-in: Dean's office. And the area to the right of Save as type don’t change:.mdb Then click-on the Create button.   Конфликты в семейной жизни. Как это изменить? Редкий брак и взаимоотношения существуют без конфликтов и напряженности. Через это проходят все...  ЧТО ПРОИСХОДИТ, КОГДА МЫ ССОРИМСЯ Не понимая различий, существующих между мужчинами и женщинами, очень легко довести дело до ссоры...  ЧТО ПРОИСХОДИТ ВО ВЗРОСЛОЙ ЖИЗНИ? Если вы все еще «неправильно» связаны с матерью, вы избегаете отделения и независимого взрослого существования...  Что вызывает тренды на фондовых и товарных рынках Объяснение теории грузового поезда Первые 17 лет моих рыночных исследований сводились к попыткам вычислить, когда этот... Не нашли то, что искали? Воспользуйтесь поиском гугл на сайте:
|
on the toolbar. On the left of the field name will appear a key indicating to us that this field is the primary key of the table.
button to send the column selected to the right hand side.
button allows us to send all the columns from the left to the right side in one go.
button allows us to remove a column from the list on the right.
button allows us to remove all the columns from the list on the right in one go.
button on the toolbar.
