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 langua
2、ge 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 DATABASECreating Sol
3、omons 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 Solomons
4、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 ViewEnter a table name
5、Use the Design View to enter the specifications for the table Creating Solomons 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
6、 ID as the primary key Creating the Raw Material RelationCreating the Concrete Type RelationCreating 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 Cre
7、ating the Bill 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 RelationDEFINING RE
8、LATIONSHIPS 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 Key Logical TiesDefining Relationships be
9、tween 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 Relationships between RelationsDefining Rel
10、ationships between RelationsENTERING INFORMATION INTO SOLOMONS DATABASEEntering InformationReferential 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
11、 against bad informationIntegrity constraints rules that help ensure the quality of the information Enforcing Referential IntegrityChange the Structure of a RelationCREATING A SIMPLE QUERY USING ONE RELATIONQuery-by-example(QBE)tool-helps you graphically design the answer to a question Suppose we wa
12、nted to see a list of raw materials that showsRaw Material NameSupplier ID Creating a Simple Query Using the Raw Material RelationCreating a Simple Query Using the Raw Material RelationCreating a Simple Query with a ConditionCREATING AN ADVANCED QUERY USING MORE THAN ONE RELATIONSuppose we want a qu
13、ery that showsAll order numbersDate of ordersWhere the goods were deliveredThe 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
14、relation names Close the Show Table dialog box Tables linked appear are 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
15、 grid in the order that you want 4.Click on the exclamation point(Run)in the button bar to see the results of the query Steps to Create an Advanced QuerySteps to Create an Advanced QuerySteps to Create an Advanced QueryGENERATING A SIMPLE REPORT1.Click on Create in the menu area and then the Report
16、Wizard button2.Choose tables and/or queries:Lets you choose which table/query 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 def
17、ault)6.Layout and orientation:Allows you to select layout and page orientation(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 FieldsChoose Table/Query and Fields Sortin
18、g and Layout Style and TitleChanging the Look of the ReportChanging the Look of a 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 Steps for Creating a
19、 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 FieldsGrouping and Sorting Information Top-leve
20、l grouping:Allows you to choose ordering.Since we created a query,Access has defaulted to the first fieldFurther grouping: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 Inf
21、ormationSorting InformationTotaling Information in a ReportTotaling:The sorting screen also has a Summary Options buttonWithin Summary Options you can choose what type of summary you want Totaling Information in a ReportFormatting the ReportOverall structure of report:Allows you to choose the layout
22、 and orientation Report heading:Allows you to enter the title that will appear on the report,then click on Finish Formatting the ReportThe ReportThe Report:Shows the information from the wizard stepsRefining the ReportSteps to adjust the report to make it aesthetically pleasingOpen the report in Des
23、ign View with the triangle buttonAdjust the boxes to the desired sizeDelete unwanted entries Refining the ReportAdjusting the Page HeaderAdjusting the Concrete Type HeaderAdjusting the Detail SectionAdjusting the Concrete Type FooterCREATING 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
