Bài giảng Management information systems - Module J: Implementing a Database with Microsoft Access

Extended Learning Module J (Office 2010 Version)Implementing a Database with Microsoft AccessSOLOMON DATABASEThe steps to creating a database areDefine entity classes and primary keysDefine relationships among the entity classesDefine fields for each relation (file)Use a data definition language to create the database, which is the focus of this Module SOLOMON DATABASEIn Figure J.1 (p. 474) on the following 3 slides, we’ve recreated the correct database structure from Extended Learning Module CRevisit Module C if you need a refresher SOLOMON DATABASESOLOMON DATABASESOLOMON DATABASECreating Solomon’s DatabaseData dictionary - contains the logical structure for the informationTo create the Solomon Enterprise database:Start Microsoft AccessClick on Blank Database in the upper left corner of the screenEnter Solomon Enterprises.accdb as the database nameClick on Create Creating Solomon’s Database1. Create a new database by clicking on Blank Database2. Enter SolomonEnterprises.accdbCreating Solomon’s 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 nameUse the Design View to enter the specifications for the table Creating Solomon’s DatabaseTo switch to Design View, click on View and then Design ViewThis is Design View – our recommendation for creating the 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 primary key Creating the Raw Material RelationPrimary key identifier and the Primary key buttonIntegrity constraints for Raw Material IDCreating the Concrete Type RelationThe fields for Concrete TypePrimary KeyIntegrity 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 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 RelationComposite primary keyDEFINING RELATIONSHIPS WITHIN SOLOMON’S 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 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 Relationships between RelationsRelationship paletteSelect tables and click on AddDefining Relationships between RelationsENTERING INFORMATION INTO SOLOMON’S DATABASEDouble-click on any table to begin entering informationEntering InformationWe double-clicked on the Supplier table and can now begin entering informationWe have completed entering all supplier informationReferential IntegrityReferential integrity ensures consistency. For example, that you don’t 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 information Enforcing Referential IntegrityBecause we entered a non-existent Supplier ID (445) that doesn’t exist, Access will not allow us to continueIf you try to close the window, Access will allow you to change the information or save the good information without the badChange the Structure of a RelationYou can change the structure of a relation by clicking on View and then Design ViewIn Design View, you can change the structure of a tableCREATING A SIMPLE QUERY USING ONE RELATIONQuery-by-example (QBE) tool - helps you graphically 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 Create and then Query DesignSecond, select the table, click on Add, and then close the box.Creating a Simple Query Using the Raw Material RelationThe result of the queryDrag and drop the appropriate field names and then click on the exclamation point (Run).Creating a Simple Query with a ConditionSet the condition hereCREATING AN ADVANCED QUERY USING MORE THAN ONE RELATIONSuppose we want a query 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 QueryClick on Create in the menu area and then Query DesignIn the Show Table dialog boxSelect 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 keyThe infinity sign by the table with the foreign key Steps to Create an Advanced QueryDrag 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 Steps to Create an Advanced QueryFrom the Show Table box, select the appropriate tables – Customer, Order, Employee and TruckSteps to Create an Advanced QueryDrag and drop the appropriate fieldsSteps to Create an Advanced QueryThe completed queryGENERATING A SIMPLE REPORTClick on Create in the menu area and then the Report Wizard buttonChoose tables and/or queries: Lets you choose which table/query you wantChoose fields: Lets you choose the fields you want Grouping, Sorting, Layout of ReportGrouping: 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) Style and Header of ReportReport header: Allows us to enter a title for the report. The Report: Shows all customers and phone numbers. Choose Table/Query and FieldsClick on Create and then Report WizardChoose tables and/or queriesChoose Table/Query and Fields Select fields by clicking on each and clicking on the >Then click on Next4. GroupingWe don’t need grouping so click on Next.Sorting and Layout We don’t need to sort so click on Next.Select a Layout and OrientationStyle and TitleChoose a Style then click on Next.Enter a report header and click on FinishThe wizard generated completed reportChanging the Look of the ReportClick on Close Print PreviewClick on View an choose Layout ViewChanging the Look of a ReportMove the report elements to change 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 Steps 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 Supply Chain querySelect all the fields of the Supply Chain queryGrouping and Sorting Information Top-level 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 don’t want any further groupingSorting: Next we have a chance to sort our information Steps for Grouping InformationGroup by Concrete Type and Type Description and then click Next.Sorting InformationSort on Raw Material ID to sort and change the order from Ascending to Descending. Totaling 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 ReportClick on Summary Options and check the Sum box for Unit.Formatting the ReportOverall 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 Formatting the ReportChoose the desired report layout and orientation.The ReportThe Report: Shows the information from the wizard stepsRefining the 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 Design View to see the report in design formatClick on Close Print Preview.Adjusting the Page HeaderMove the divider to make more heading room and change the shape and size of the header boxesClick on View and then Report View to see the changesAdjusting the Concrete Type HeaderMove Concrete Type to the left and shrink it.Concrete Type should now be lined up under its headerAdjusting the Detail SectionAdjust the elements of the detail line so that they line up with their headersThe realigned reportAdjusting the Concrete Type FooterDelete the Summary line. Change Sum to Total Units, and change its colorThe completed reportCREATING A DATA INPUT FORMSelect the Order tableClick on Create and then FormCREATING A DATA INPUT FORMSelect the Order tableClick on Create and then FormFinal Input Form