《信息时代的管理信息系统ModuleJBook课件》由会员分享，可在线阅读，更多相关《信息时代的管理信息系统ModuleJBook课件（65页珍藏版）》请在金锄头文库上搜索。
1、Extended Learning Module J (Office 2010 Version)Implementing a Database with Microsoft AccessSOLOMON DATABASEThe steps to creating a database are1.Define entity classes and primary keys2.Define relationships among the entity classes3.Define fields for each relation (file)4.Use a data definition lang
2、uage to create the database, which is the focus of this Module SOLOMON DATABASEIn Figure J.1 (p. 474) on the following 3 slides, weve recreated the correct database structure from Extended Learning Module CRevisit Module C if you need a refresher SOLOMON DATABASESOLOMON DATABASESOLOMON DATABASECreat
3、ing Solomons DatabaseData dictionary - contains the logical structure for the informationTo create the Solomon Enterprise database:1.Start Microsoft Access2.Click on Blank Database in the upper left corner of the screen3.Enter Solomon Enterprises.accdb as the database name4.Click on Create Creating
4、Solomons Database1. Create a new database by clicking on Blank Database2. Enter SolomonEnterprises.accdbCreating Solomons DatabaseOur recommendation: Create a relation (table) in Design view:Must switch from Datasheet View to Design ViewClick on View in the upper left corner and then Design ViewEnte
5、r a table nameUse the Design View to enter the specifications for the table Creating Solomons DatabaseTo switch to Design View, To switch to Design View, click on click on ViewView and then and then Design ViewDesign ViewThis is This is Design View Design View our our recommendation for creating rec
6、ommendation for creating the tables in a databasethe tables in a databaseCreating the Raw Material RelationEnter the four fields of the Raw Material relationRaw Material IDRaw Material NameQOHSupplier IDClick on the Raw Material ID row and then the key button to designate Raw Material ID as the prim
7、ary key Creating the Raw Material RelationPrimary key identifier and Primary key identifier and the the Primary key Primary key buttonbuttonIntegrity constraints for Integrity constraints for Raw Material IDRaw Material IDCreating the Concrete Type RelationThe fields for The fields for Concrete Type
8、Concrete TypePrimary KeyPrimary KeyIntegrity ConstraintsIntegrity ConstraintsCreating the Bill of Material RelationWe 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 Creating the Bil
9、l of Material RelationThe Bill of Material relation has a primary key composed of two fields (composite primary key):Concrete Type Raw Material IDComposite primary key - consists of the primary key fields from the two intersecting relations Creating the Bill of Material RelationComposite Composite p
10、rimary keyprimary keyDEFINING RELATIONSHIPS WITHIN SOLOMONS DATABASEThe final structural task is to define how all the relations relate to each otherThat is, link primary and foreign keysForeign key - a primary key of one file (relation) that appears in another file (relation) Primary and Foreign Ke
11、y Logical TiesDefining Relationships between RelationsTo create relationshipsClick 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 AddThen click on the Close button Defining Rela
12、tionships between RelationsRelationship Relationship palettepaletteSelect tables and Select tables and click on click on AddAddDefining Relationships between RelationsENTERING INFORMATION INTO SOLOMONS DATABASEDouble-click on any table to Double-click on any table to begin entering informationbegin
13、entering informationEntering InformationWe double-clicked on the We double-clicked on the SupplierSupplier table and table and can now begin entering informationcan now begin entering informationWe have completed entering We have completed entering all supplier informationall supplier informationRef
14、erential IntegrityReferential integrity ensures consistency. For example, that you dont put a non-existent Supplier ID into the Raw Material relationThe relationships we set up for referential integrity guard against bad informationIntegrity constraints rules that help ensure the quality of the info
15、rmation Enforcing Referential IntegrityBecause we entered a non-Because we entered a non-existent existent Supplier ID Supplier ID (445) that (445) that doesnt exist, Access will not doesnt exist, Access will not allow us to continueallow us to continueIf you try to close the If you try to close the
16、 window, Access will window, Access will allow you to change the allow you to change the information or save the information or save the good information good information without the badwithout the badChange the Structure of a RelationYou can change the structure of a relation by You can change the
17、structure of a relation by clicking on clicking on View View and then and then DesignDesign ViewViewIn Design View, you can In Design View, you can change the structure of a change the structure of a tabletableCREATING A SIMPLE QUERY USING ONE RELATIONQuery-by-example (QBE) tool - helps you graphica
18、lly design the answer to a question Suppose we wanted to see a list of raw materials that showsRaw Material NameSupplier ID Creating a Simple Query Using the Raw Material RelationFirst, click on First, click on CreateCreate and and then then Query DesignQuery DesignSecond, select the table, Second,
19、select the table, click on click on AddAdd, and then , and then close the box.close the box.Creating a Simple Query Using the Raw Material RelationThe result of the queryThe result of the queryDrag and drop the appropriate Drag and drop the appropriate field names and then click on the field names a
20、nd then click on the exclamation point (exclamation point (RunRun). ).Creating a Simple Query with a ConditionSet the condition hereSet the condition hereCREATING AN ADVANCED QUERY USING MORE THAN ONE RELATIONSuppose we want a query that showsAll order numbersDate of ordersWhere the goods were deliv
21、eredThe contact personThe truck involved in each deliveryThe truck driver in each delivery Steps to Create an Advanced Query1.Click on Create in the menu area and then Query Design2.In the Show Table dialog boxSelect and Add the relation names Close the Show Table dialog box Tables linked appear are
22、 joined by lines with 1 beside the table with the primary keyThe infinity sign by the table with the foreign key Steps to Create an Advanced Query3.Drag and drop the fields that you want from the appropriate relation into the QBE grid in the order that you want 4.Click on the exclamation point (Run)
23、 in the button bar to see the results of the query Steps to Create an Advanced QueryFrom the From the Show Table Show Table box, box, select the appropriate tables select the appropriate tables Customer, Order, Employee Customer, Order, Employee andand Truck TruckSteps to Create an Advanced QueryDra
24、g and drop the appropriate fieldsDrag and drop the appropriate fieldsSteps to Create an Advanced QueryThe completed queryThe completed queryGENERATING A SIMPLE REPORT1.Click on Create in the menu area and then the Report Wizard button2.Choose tables and/or queries: Lets you choose which table/query
25、you want3.Choose fields: Lets you choose the fields you want Grouping, Sorting, Layout of Report4.Grouping: Lets you specify grouping of information (we chose the default)5.Sorting: Allows you to specify sorting (we chose the default)6.Layout and orientation: Allows you to select layout and page ori
26、entation (we chose the default) Style and Header of Report7.Report header: Allows us to enter a title for the report. 8.The Report: Shows all customers and phone numbers. Choose Table/Query and FieldsClick on Click on CreateCreate and then and then Report WizardReport WizardChoose tables and/or quer
27、iesChoose tables and/or queriesChoose Table/Query and Fields Select fields by clicking on Select fields by clicking on each and clicking on the each and clicking on the Then click on Then click on NextNext4. Grouping4. GroupingWe dont need grouping so We dont need grouping so click on click on NextN
28、ext. .Sorting and Layout We dont need to sort so click We dont need to sort so click on on NextNext. .Select a Select a LayoutLayout and and OrientationOrientationStyle and TitleChoose a Choose a Style Style then then click on click on NextNext. .Enter a report headerEnter a report header and click
29、on and click on FinishFinishThe wizard generated The wizard generated completed reportcompleted reportChanging the Look of the ReportClick on Click on Close Print Close Print PreviewPreviewClick on Click on ViewView an an choose choose LayoutLayout View ViewChanging the Look of a ReportMove the repo
30、rt elements to Move the report elements to change the look of the reportchange the look of the reportREPORT WITH GROUPING, SORTING, AND TOTALSSay we want to create the Supply Chain Management report from Extended Learning Module CFirst, create a queryThen, put the query into the report generator Ste
31、ps for Creating a Report with Grouping, Sorting, and TotalsClick on Create in the menu area and click on the Report Wizard buttonChoose tables/queries: Query: Supply Chain QueryChoose fields: Select all fields by clicking on the double greater-than sign () Specify Table/Query FieldsSelect the Select
32、 the Supply Chain Supply Chain queryquerySelect all the fields of the Select all the fields of the Supply Chain Supply Chain queryqueryGrouping and Sorting Information Top-level grouping: Allows you to choose ordering. Since we created a query, Access has defaulted to the first fieldFurther grouping
33、: Lets you specify groups within the top grouping of Concrete Type, but we dont want any further groupingSorting: Next we have a chance to sort our information Steps for Grouping InformationGroup by Group by Concrete Type Concrete Type and and Type DescriptiType Description and on and then click the
34、n click NextNext. .Sorting InformationSort on Sort on Raw Material ID Raw Material ID to sort and change to sort and change the order from the order from AscendingAscending to to DescendingDescending. . Totaling Information in a ReportTotaling: The sorting screen also has a Summary Options buttonWit
35、hin Summary Options you can choose what type of summary you want Totaling Information in a ReportClick on Click on Summary Options Summary Options and and check the check the SumSum box for box for UnitUnit. .Formatting the ReportOverall structure of report: Allows you to choose the layout and orien
36、tation Report heading: Allows you to enter the title that will appear on the report, then click on Finish Formatting the ReportChoose the desired report layout and orientation.Choose the desired report layout and orientation.The ReportThe Report: Shows the information from the wizard stepsRefining t
37、he ReportSteps to adjust the report to make it aesthetically pleasingOpen the report in Design View with the triangle buttonAdjust the boxes to the desired sizeDelete unwanted entries Refining the ReportClick on Click on Design ViewDesign View to to see the report in design see the report in design
38、formatformatClick on Click on Close Close Print PreviewPrint Preview. .Adjusting the Page HeaderMove the divider to make Move the divider to make more heading room and more heading room and change the shape and change the shape and size of the header boxessize of the header boxesClick on Click on Vi
39、ewView and then and then Report View Report View to see the to see the changeschangesAdjusting the Concrete Type HeaderMoveMove Concrete Concrete Type tType to the left o the left and shrink it.and shrink it.Concrete Type should now Concrete Type should now be lined up under its headerbe lined up un
40、der its headerAdjusting the Detail SectionAdjust the elements of Adjust the elements of the detail line so that they the detail line so that they line up with their headersline up with their headersThe realigned reportThe realigned reportAdjusting the Concrete Type FooterDelete the Delete the Summar
41、ySummary line. line. Change Change SumSum to to Total UnitsTotal Units, , and change its colorand change its colorThe completed reportThe completed reportCREATING A DATA INPUT FORMSelect the Order tableClick on Create and then FormCREATING A DATA INPUT FORM1.Select the Order table2.Click on Create and then FormFinal Input Form
