Transcript Slide 1 - S
Database Design Concepts With Access Learning Outcomes Identify and define the information that is needed to design a database Create conceptual and logical db designs Build a relational database that provides users with queries, forms, and reports Understand core terms, concepts, and tools Learning Outcomes Design and maintain relational db tables Create Select and Action queries Create normalized relationships between tables, apply validation rules, and referential integrity principles Design and modify reports and forms Challenges House analogy Need a blueprint before you can build GIGO (garbage in, garbage out) Key DB Design Concepts Entity Person, place, thing, event (noun) Attribute Property of an entity (adjective, adverb) Relationship Association between entities Usually a verb Key DB Design Concepts Database Structure that can house information about multiple types of entities, the attributes of these entities, and the relationships among the entities. Relational Database Perceived by users to be a collection of tables; two-dimensional named tables DBMS (software) Design structure of database Create data entry forms Validate data Sort and manipulate data Query the database Produce reports Goals of Database Design Input set of user requirements Output database structure capable of supporting user requirements Database Design Step 1 Information-level design gather user requirements design a database that meets requirements as cleanly as possible independent of DBMS Database Design Step 2 Concerned with characteristics of specific DBMS Must resolve issues such as column names data type number of columns data length General Design Guidelines Identify the tables (entities) Determine the primary keys (unique attribute) Determine additional fields (attributes) Determine relationships among tables General Design Guidelines (cont) Determine data types for fields Identify and remove unwanted redundancy Storing a piece of data in more than one place Determine a storage location Determine additional properties for attributes A Database Example Camashaly Design Group provides custom marketing solutions for the service, non-profit, and retail sectors. The company specializes in designing and maintaining Web sites and using social networking Web sites for online marketing. Camashaly uses business analysts to work collaboratively with clients. Camashaly would like to organize the data on clients and business analysts in to a database managed by Access 2010. Applying the Database Design Guidelines to Camashaly Design Group Client Number BA53 BB32 BC76 CJ29 GA74 GF56 HC10 KD21 KG04 ME14 PJ34 SL77 TB17 WE05 WS01 Client Name Bavant Animal Hospital Babbage CPA Firm Buda Community Clinic Catering by Jenna Grant Antiques Granger Foundation Hendley County Hospital KAL Design Studio Kyle Grocery Cooperative Mike's Electronic Stop Patricia Jean Florist Smarter Law Associates The Bikeshop Walburg Energy Alternatives Woody Sporting Goods Street City 134 Main 464 Linnell 867 Ridge 123 Second 78 Catawba 65 Simpson 216 Rivard 116 Pine 421 First 234 Gilham 345 Magee 764 Main 346 Austin 12 Polk 578 Central Burles Austin Buda Granger Georgetown Granger Austin Georgetown Kyle Georgetown Kyle Burles Buda Walburg Walburg Word table (Clients.doc) State NC SC NC NC NC NC SC NC SC NC SC NC NC NC NC Postal Code 28817 28796 27032 27036 28794 27036 28796 28794 28798 28794 28798 28817 27032 28819 28819 Excel workbook Business Analyst Number 11 14 27 35 Last Name Kerry Martinez Liu Scott First Name Cordelia Manuel Jan Jeff Street 251 Painter 3125 Steel 265 Marble 1925 Pine City Georgetown Kyle Byron Georgetown Word table (Business Analysts) State NC SC SC NC Postal Code 28794 28797 28795 28794 Excel workbook Your Turn! Look at the user documents provided Clients Word document Business analysts Word document Business analyst financial data Excel workbook Client financial data Excel workbook Design a database for Camashaly Design Group Import Excel Files Open Access On the External Data tab click Excel in the Import group Import the two Excel workbooks DO NOT remove any columns Create Tables for Data in Word Documents Create a table for Clients and one for Business Analysts Hint: the Client Number and Business Analyst number should be primary key fields Are there columns that have been duplicated between tables? If so, remove the column from one table. Hint: Client name is contained in both the Clients and Client Financial Data tables. Normalization requires that the name is stored in the Clients table. Remove the column in the Financial Data table. Set Up Relationships Which tables have a column in common? Close the tables (do not close Access) Click the Database Tools tab Click relationships Set up a one to many relationship Hint: Look for the common columns. Client and Business Analyst are the tables that are on the one side. Create a Query Create a Simple Query using the Wizard Include the first name, last name and incentive YTD for the business analysts Specify a Detail query, click Finish Create a Second Query Create a second query using Query Design that will list all clients that owe more than $1,000 From the Show Tables dialog box, add the Clients and Client Financial Data tables The fields to display are client name, postal code, and current due Sort in ascending order on postal code Criteria for the current due is > 1000 Change to Datasheet View to see the results Create a Form Create a form to input new clients Hint: Open the clients table and click on Create/Form Create a Report We want a report that will Use the report wizard Choose the clients table; fields to display are the client name and postal code Choose the client financial data; fields to display are the amount paid and current due View the data by Client Group by Postal Code Report (continued) Sort by current due in descending order No summary options Keep all the defaults, click finish If necessary, edit in Design view to make your report attractive. Congratulations! Thank you to Phil Pratt and Mary Last