|
Implementing a Database with Microsoft Access
|
tarix | 18.07.2018 | ölçüsü | 14,04 Mb. | | #56381 |
|
Implementing a Database with Microsoft Access
The steps to creating a database are The steps to creating a database are - Define entity classes and primary keys
- Define relationships among the entity classes
- Define fields for each relation (file)
- Use a data definition language to create the database, which is the focus of this Module
In Figure J.1 (p. 474) on the following 3 slides, we’ve recreated the correct database structure from Extended Learning Module C In Figure J.1 (p. 474) on the following 3 slides, we’ve recreated the correct database structure from Extended Learning Module C Revisit Module C if you need a refresher
Data dictionary - contains the logical structure for the information To create the Solomon Enterprise database: - Start Microsoft Access
- Click on Blank Database in the upper left corner of the screen
- Enter Solomon Enterprises.accdb as the database name
- Click on Create
Our recommendation: Create a relation (table) in Design view: Our recommendation: Create a relation (table) in Design view: - Must switch from Datasheet View to Design View
- Click on View in the upper left corner and then Design View
- Enter a table name
- Use the Design View to enter the specifications for the table
Enter the four fields of the Raw Material relation - Raw Material ID
- Raw Material Name
- QOH
- Supplier ID
Click on the Raw Material ID row and then the key button to designate Raw Material ID as the primary key
We created (in Extended Learning Module C) the Bill of Material relation to eliminate the many-to-many relationship between the Concrete Type and Raw Material relations We created (in Extended Learning Module C) the Bill of Material relation to eliminate the many-to-many relationship between the Concrete Type and Raw Material relations
The Bill of Material relation has a primary key composed of two fields (composite primary key): The Bill of Material relation has a primary key composed of two fields (composite primary key): - Concrete Type
- Raw Material ID
Composite primary key - consists of the primary key fields from the two intersecting relations
The final structural task is to define how all the relations relate to each other The final structural task is to define how all the relations relate to each other That is, link primary and foreign keys Foreign key - a primary key of one file (relation) that appears in another file (relation)
To create relationships - Click on Database Tools in the menu area and then click on the Relationships button
- Make each relation appear on the palette by highlighting each relation name and clicking on Add
- Then click on the Close button
Referential integrity ensures consistency. For example, that you don’t put a non-existent Supplier ID into the Raw Material relation Referential integrity ensures consistency. For example, that you don’t put a non-existent Supplier ID into the Raw Material relation Integrity constraints – rules that help ensure the quality of the information
Query-by-example (QBE) tool - helps you graphically design the answer to a question Query-by-example (QBE) tool - helps you graphically design the answer to a question Suppose we wanted to see a list of raw materials that shows - Raw Material Name
- Supplier ID
Suppose we want a query that shows Suppose we want a query that shows - All order numbers
- Date of orders
- Where the goods were delivered
- The contact person
- The truck involved in each delivery
- The truck driver in each delivery
Click on Create in the menu area and then Query Design Click on Create in the menu area and then Query Design In the Show Table dialog box - Select and Add the relation names
- Close the Show Table dialog box
- Tables linked appear are joined by lines with
- 1 beside the table with the primary key
- The infinity sign by the table with the foreign key
Drag and drop the fields that you want from the appropriate relation into the QBE grid in the order that you want Drag and drop the fields that you want from the appropriate relation into the QBE grid in the order that you want Click on the exclamation point (Run) in the button bar to see the results of the query
Click on Create in the menu area and then the Report Wizard button Click on Create in the menu area and then the Report Wizard button Choose tables and/or queries: Lets you choose which table/query you want
Grouping: Lets you specify grouping of information (we chose the default) Grouping: Lets you specify grouping of information (we chose the default) Sorting: Allows you to specify sorting (we chose the default) Layout and orientation: Allows you to select layout and page orientation (we chose the default)
Report header: Allows us to enter a title for the report. Report header: Allows us to enter a title for the report. The Report: Shows all customers and phone numbers.
Say we want to create the Supply Chain Management report from Extended Learning Module C Say we want to create the Supply Chain Management report from Extended Learning Module C First, create a query Then, put the query into the report generator
Click on Create in the menu area and click on the Report Wizard button Click on Create in the menu area and click on the Report Wizard button Choose tables/queries: Query: Supply Chain Query Choose fields: Select all fields by clicking on the double greater-than sign (>>)
Top-level grouping: Allows you to choose ordering. Since we created a query, Access has defaulted to the first field Top-level grouping: Allows you to choose ordering. Since we created a query, Access has defaulted to the first field Further grouping: Lets you specify groups within the top grouping of Concrete Type, but we don’t want any further grouping Sorting: Next we have a chance to sort our information
Totaling: The sorting screen also has a Summary Options button Totaling: The sorting screen also has a Summary Options button - Within Summary Options you can choose what type of summary you want
Overall structure of report: Allows you to choose the layout and orientation Overall structure of report: Allows you to choose the layout and orientation Report heading: Allows you to enter the title that will appear on the report, then click on Finish
The Report: Shows the information from the wizard steps The Report: Shows the information from the wizard steps
Steps to adjust the report to make it aesthetically pleasing Steps to adjust the report to make it aesthetically pleasing - Open the report in Design View with the triangle button
- Adjust the boxes to the desired size
- Delete unwanted entries
Select the Order table Select the Order table Click on Create and then Form
Select the Order table Select the Order table Click on Create and then Form
Dostları ilə paylaş: |
|
|