Popular in Course
verified elite notetaker
Popular in Science
This 30 page Class Notes was uploaded by Dessie Hand on Sunday October 11, 2015. The Class Notes belongs to IS215 at Eastern Michigan University taught by Hung-LianTang in Fall. Since its upload, it has received 153 views. For similar materials see /class/221476/is215-eastern-michigan-university in Science at Eastern Michigan University.
Reviews for End
Report this Material
What is Karma?
Karma is the currency of StudySoup.
You can buy or earn more Karma at anytime and redeem it for class notes, study guides, flashcards, and more!
Date Created: 10/11/15
Decision Making Using Access LEARNING OUTCOMES Describe the steps for creating a form using the Form Wizard using Access Describethe steps for creating and saving AutoForms using Access Describe the steps to modify the properties of a form using Access Describe the steps for creating a report using the Report Wizard using Access Describe the steps to modify the properties of a report using Access 1 Z 3 4 5 Introduction This plugin focuses on the two functions of decision making using Access creating forms and creating reports A form is nothing more than a graphical representation of a table You can add update and delete records in your table by using a form Al though a form can be named different from a table they both still manipulate the same information and the same data Hence if you change a record in a form it will be changed in the table as well A form is useful when you have numerous fields in a table This way you can see all the fields in one screen whereas if you were in the table view datasheet you would have to keep scrolling to get the eld you desire A report is an effective way to present your data in a printed format Because you have control over the size and appearance of everything on a report you can dis play the information the way you want to see it Forms An Access form is a window similar to a dialog box that contains a set of controls such as labels text boxes and check boxes to view enter or edit database infor mation typically one record at a time In a form data are obtained directly from one or more tables or data that have been extracted using a query Although it is possible to directly enter and edit the information in tables in Datasheet view a database usually includes a set of forms which can make entering and editing data considerably easier and can limit the fields that can be viewed or modified T82 I PlugIn T8 16quot u u w 3 gr 3 CREATING A FORM USING THE FORM WIZARD Forms allow you to enter data one record at atime Often it is easier to enter data in awellrdesigned form rat er than in awide datasheet You can create a form that has elds from more than one table or query Like other wizards the Form Vifrzard walks you steprbyistep through the process of creating a orm To create a form using the FormVifrzard Bikeidenliiicalian Nu i I an i n speciaizeaaacrmpei i siznn Easl Pei Ham 1 Open the le TsislopesideBikesiDalambd from the data file that accompanies this ext Rem E WEE we 39 Farm View I n kl 3 Click the TablesQueries droprdown arrow and select Table BIKE FIGURE If I 11 Add all BlKE fields by clicking on the double right arrow Click Next to go to the Ci eate a Form by Using next step Wizald Select the Columnar form layout Click Next Select the Standard graphic style Click Next Type in BIKETitle for aform title Click Finish to open the form and begin entering data see Figure T81 awayr Accessing Several Tables or Queries in a Form When you select the fields foryour form in the first Form Vifrzard dialog boX you ranadd L quot39 Ltableorq select it in the TablesQueries droprdown list and then use the buttons to move the fields you want to the Selected Fields list If nuadd 39 L i d 4 u LL additional dialog boxes that weren t shown in the previous section one dialog box zyup39 q uy tuicw ample if you selected fields from the CUSTOMER and the BIKE tables you would choose to view your data either by Customer or by Bikequot and possibly another dialog box in which you select a layout for asubformv The choices you make deter mine the forms record source l r I ship and if you selected to view your data by the primary table the wizard will let you ui pmy the formAsan altnrnati a the n fo V On L L L 4 if yourdata uy ure related table in the one tormany relationship when the form displays a record in the related table it will simply display the unique matching fields from the primary table along with the fields from the current record in the re ate ta le A form that accesses data from several tables or queries can be complex to der i n f L mndifv ard almost everything is set up for you CREATING AND SAVING AUTOFORMS The AutoForm Vifrzard is the quickest and easiest way to create a form There are five types of AutoForms 1 Columnar 2 Tabular 3 Datasheet 4 PivotTable and 5 PivotChartv The Columnar Form is the default formatv PlugIn T8 Tss FIGURE T82 Create a Form by Using Auto Form T84 PlugIn Ta New Ubiect39 AuraForm toolbar bumm a Microsoft Access Taslnpssidssikesnm Database Access s L l md w Help llvlal m m l la39 39 a c tetablemuestgnv Ell Createtablebyusmqwzard E Create table by Entering data 3 BIKE CUSTDME RENTAL Select Table that you F J Rspnrts I Want m base i AumForm on E P59 a News a Modules T l Ready To use the AutoFormWizard to create a Colurnnar Form 1 Display the Tables window 2 Select the CUSTOMER table 3 Click the New ObjectzAutoForm toolbar button refer to Figure T8i2t If the New Object toolbar button is not in the AutoForm state click the New Object arrow and select AutoForm 4 Access automatically creates a Colurnnar Form based on the CUSTOMER table you selecte 5 Close the form by clicking the Close boxin the uppersright corner 6 Access will automatically prompt you to save the form Save the form as Cus tomert Note Notice that the New Object toolbar button changes The button will change to re ect the most recent object type MODIFYING THE PROPERTIES OF A FORM Once you have created a form using one of the wizards you can still change the way the form looks and functions The AutoFormat button allows you to change the f Openingthe quot y u h h form properties Here is a description on how to modify the properties of a form 1 With the form open in Design view use the AutoFormat toolbar button to change the graphic style of the form refer to FigureT8t3i 2 To change other form properties open the Properties dialog box by clicking the Properties toolbar button refer to Figure T83 Equot Make sure that you are viewing the properties for the form by looking at the box at the top of the dialog Scroll through the list of properties Click the boxnext p p lmtyouwant to chance a dropsdown list with specific choices For other properties you enter a speci c Valuer P m When you have made the changes you want close the Properties dialog box by clicking the Close box in the upperright corner Click on the View button to switch to Form View to see the changes you have made refer to Figure T83 MODIFYING SPECIFIC CONTROLS ON A FORM Not all controls on your form may be of equal importanceYou may want to modify the look of a speci c control to make it stand out or you may want to modify the control behavior When designing forms be careful not to overdesign Too many different colors styles or behaviors can distra ct from accurate data entry To modify the properties of a specific control refer to Figure T83 1 Open BIKE form in Design view 2 Since the BIKE form you created using the wizard did not include a title for the form you should create one 3 Select all the objects on the form by pressing CFRL A Use your down arrow key to move the selected objects down enough for you to enter a form title label 4 Using the Toolbar options select the Label tool Notice that the pointer changes to aplus sign 5 Click and drag a rectangular box at the top of the form Type SLOPESIDE BIKE FORM inside the label box 6 Select Properties from the shortcut menu 7 Click the Format tab if necessary 8 Click the FontWeight property and select Bold in the dropdown list 9 Click the Text Align property and select Center in the dropdown list Note Some properties have a dropdown list with speci c choices For other proper ties you enter a specific value 10 Closethe r 39 quot uux ln ebnxintb pp 39 corner VII3W button Properties button Select control Control properties PlugIn T8 Eh FIGURE T83 Modify Form Properties and Controls FIGURE TBA Modify Form Properties T86 PlugIn T8 11 Switch to Form View to see the changes you have made 12 Close and Save the form Note CuuiLuuu uiuduy ing the size ofthe control based on data the user enters If you turn off scroll bars users can only see the data that fits within the specific control dimensions If you turn on Can Grow or Can Shrink the control area will grow and or shrink to fit the data Many formatting options are available from the shortcut menu selecting a con trol then right mouse click You can change the background color font color and which special effect is appliedto the control c L 1 v ALIGNING CONTROLS IN A FORM To give your form a professional look make sure that controls are aligned evenly You can use the form rulers or display the form grid to give you visual guidelines Rather than uyin lu manually quot t n e the quot toolsYou can select multiple controls and then align themby the left side right side top or bottom To align controls in a form Open the CUSTOMER form Show the form grid by selecting Grid from the View menu Show the rules by selecting Ruler from the Viewmenu Select all the label controls to the left of the text boxes Select Align from the Format menu Select the Right alignment refer to Figure T84 PPN m Switch to Form View to see the changes you have made 7 Close and Save the form Note Use left or right alignment for controls that are in the same column Use top or bottom alignment for controls that are in the same row AutnEormat Warm Qantrni Defaults in Horiznnmi Spawn Select controls 9W mg m Fran If you specify an alignment that would cause controls to overlap Access will place the edges of the controls right next to each other but will not overlap them Reports Reports are used primarily for printing selected database information A report labels groups sorts and summarizes the data it presents Like a form a report can display data directly from one or more tables or it can display the results of a query USING THE REPORT WIZARD Like other wizards the Report Wizard walks you stepbystep through the process of creating a report Unlike forms which are designed for onscreen data entry re ports are designed for print Nd Squot P 99 F 9quot d P To create a report using the Report Wizard In the Reports window doubleclick Create reportby using wizard Click the Tables Queries dropdown arrow Select the CUSTOMER table and select all the elds except the Drivers License Telephone and Credit Card No Then select the RENTAL table and select the Date field Lastly select the BIKE table and select the Description eld Click Next to go to the next step Note If you selected elds from more than one table or query in the previous step the second Report Wizard dialog box asks you to choose one table or query that will be used for grouping the information in the report if possible The next wizard asks how you want to view the report Doubleclick BIKE Click Next Make Date the Grouping Note Use a grouping level to organize the data into subgroups by the value of a specific field Click Next Next specify State as the data sort Click Next Select the Stepped report layout and Landscape orientation Click Next Select the Bold graphic style for the report Click Next Type in Customer Rental by State Report for the title and select View the report Click Finish Figure T85 displays the results MODIFYING THE REPORT DESIGN Once you create a report you can modify the report s design to make it more visu ally attractive As with forms be care il not to overdesign your report A poorly de signed report can distract from the information being presented To modify the design you must have the report open in Design view a N To modify the report design Preview the report first Doubleclick the Customer Rental by State Report No tice that the Description name is truncated and the State column needs to be moved to the right Switch to Design View by clicking the View button Click the Description control under the Bikeild header section and then drag the sizing right handle to increase the width of the control box this will allow more characters to be visible in the report You can also select Size from the Format menu Select To Fit to automatically resize the label or control to t your data PlugIn T8 T87 FIGURE TB5 View Report mason Access Customer Renml by State Report 1 x g a 5 Egg 1D Vlr ln lnmnl wlajwlgl Customer Rental by State Report Descrlpllon Dalebyhlnnlh Stale Lul llama Dale Hmuame Address City 21 Sum Pmla tlphu EECLS Specialiled Rockhopp PE 5mm 62 2225 Sue z 2 3139 Dims 39 SIKYLLCE XE Turk Trek Fuel 7 Trek Fuell l 7 5 x Trek FuelBU a x Page v lt 1 EM L l l 1 Ready 4 Select the State column heading and the State control to select both hold down the Shift key when selecting and move to the right a few spaces using the right arrow key Do not worry if some of the controls overlap 5 Switch back to Preview mode to see the effects of your changes Figure T86 dis plays the modi ed report FIGURE T815 395 Microsoft Access Customer Rental by State Repn 7 He Tpzauuistmnrul Help v 5 x MOdIerfj Report vl as l emnlwvm 1le Properties 1 Customer Rental by State Report Descr Dale by Monlh Slate Lasl Name Dare First Name Addres5 City Zip 5m 5 3 1m 5 phlegm 19019 Specialized Rnckhnpper 5m 5 m 5 91mm 19019 M 3m 393 1m 3m 53 Lure xi Yuk 10131 E Trek Fuel 70 mm 5391M 3r V V Trek Fuel 80 7 x m x mm Trek Fuel 80 l Page u 1 135 3 u it it Ready TBB PlugIn TB ll Note Change the graphic style of the report by clicking the AutoFormat toolbar button The AutoFormat style list is the same list of styles that you choose from when creating a report using the Report Wizard CHANGING MARGINS AND PAGE ORIENTATION FOR REPORTS The default page orientation for a report is portrait This means the height of the page is greater than the width You may want to change this orientation to accom modate multiple columns of data so they fit on one page Margins are the blank spaces at the top bottom left and right of a report By adjusting the page margins you can control the number of records printed on each page Use the dialog box to adjust margins and page orientation for your reports Here is a description on how to change the margins for a report Doubleclick the report name in the Reports window Click the Setup toolbar button The Page Setup dialog box opens with the Margins tab on top Change the values for the top bottom left or right margin Click OK 915de To change the page orientation for a report Doubleclick the report name in the Reports window In the Page Setup dialogbox click the Page tab Click the radio button for Portrait or Landscape orientation Click OK wad Note When you print a report Access prints the data along with all graphic ele ments associated with the report s AutoFormat To print only the data and not the graphic elements of a report select the Print Data Only check box on the Margins tab of the Page Setup dialogbox PlugIn T8 T89 PLUGIN SUMMARY the Form Wizard optionThe Form Wizard lets you choose the specific fields to include which can belong to one or more tables or queries To have Access quickly create a form that has a particular configuration Columnar Tabular Datasheet PivotTable or Piv otChart based on the record source table or query you select in the dropdown list select one ofthe five AutoForm options Access will immediately create the form including all fields from the record source and using default options without asking foryour specifications To have Microsoft Access help you design a report selectthe ReportWizard option To use default settings to quickly create a report based on a singletable or query with a columnar or tabular layout select the AutoReport Columnar or the AutoReport Tabular option Either re port will include all the fields belonging to the record source table or querythatyou select in the dropdown list atthe bottom ofthe New Report dialog box T 0 have Microsoft Access create the form for you according to your specifications select MAKING BUSINESS DECISIONS l WasteNot Recycling WasteNot Recycling introduced in PlugIn T6 and reintroduced in T7 picks up recyclables from homeowners in Boulder ColoradoThe owners ofWasteNot Recycling have asked you to assist with creating a form and several reportsThey have provided you with an updated data base file T8WasteNotRecyclingDatamdb Specifically they want you to do the following Create a form that will allow the owners to enter data into the CUSTOMER and the CUSTOMER RECORD tables They have left the design ie aesthetics up to you How ever they have asked that you locate an appropriate graphic to include on the form You will wantto use the lnternetto find such a graphic Make sure you align all the con trols and adjust the size of all the controls to fit the data Save the form as Customers Create a report that groups the records by customer The report should include data on the customer first and last name service date of pickup weight of paper and weight of other The report should be sorted by customer last name You will need to create a subtotal forthe weights for each customer Create a reporttitle called CustomerWeights 5 3 lt quots A Grind Coffee Shop It39s A Grind Coffee Shop introduced in PlugIn T6 is an Oakland California neighborhood coffee shop Kate Fitzgerald the proprietor has decided that she needs a form and a few reports created to help her purchase new books for her coffee shop Kate has provided you with an updated database file T8ltsAGrindCoffeeDatamdb Specifically Kate wants you to d Create a form thatwill allow Kate to add new books to the BOOKtable Kate has asked you to use a Columnar layout and the Sumi Painting style Kate has given you her logo to add to the form ItsAGrindlmageipg Positionthe logo inthetopleft cornerSavethe form as Books Create a report that groups the records by author lnclude onlythe author from the AU THOR table and book title year and condition from the BOOK table Sortthe report by book title Create a title labeled Current Authors center the title on the report Set the label properties to 12 point bold italic Save the report as Books By Author Create a report using the publisher name booktitleyear and author name Group the report on publisher and sort on the year the book was published Create a reporttitle N 9 T810 PlugIn T8 Denisirm Making Ueiragi to say 30039 BV PUbI39Sher Al39gn the a Microsoft Access Students By Seminar Repon rel n columns evenly and use a profession If r i Typea uesuunrnrhelp al looking font style and layout Save v El am i v i iv i39vmvlm the reportas Publishers Students By Semnar Hennrt Tecth Seminars Tecth Seminars is an organization of inde ISeminanD LIE Nlme First Name Studeanhone Student Numberr pendent seminar facilitators who provide quot3 I onsite technicaltraining to large businesses 3 around the world The facilitators build cur Rm WM 2 riculum that is marketed by Tecth Tecth 13 books the seminars arranges facilities en Weinslem mm ms rolls participants and collects the money 7 mm Sm W While the facilitators are not employees of mm m 331 Tecth they provide the service that is mar WW 39m 2 5 ketedand theirskills and schedules need to i be available to all Tecth offices Deborah TW Wallbridge has been charged with tracking facilitators and their classification She has Harrison m asked you to help develop a form and a few 2mm reports to assist in scheduling seminars mam Lawn 1 Use the TILTechlTSeminarsDatamdb file pm Dale 2 Create a data entry form forthe FACIL 2W lTATUR table Setthe form background Reyes mm color to a blue color lhuel Organize was WWWquot my and align all controls for effective use am ms and full data display For the phone 5 data use the Phone input mask Save the form as Facilitators Form we th 3 Use the ENROLLMENT table to create mssmlz m 1 w sane 277 Page win 1 35 S it Ready a report listing the students currently enrolled in each seminar The report field order is Seminar ID Last Name First Name Phone Number and Student Number Adjust all controls to display all of their contents Adjustthe color and content of the column headings as shown in Figure T87Align all controlsAdd a gray line above and below the column headings Use Sorting and Grouping to sort by Seminar ID Last Name and First Name Place the reportdatetime and Page XX ofXX in the Page Foot er Format the date and time to long format Save the report as Students By Seminar Report Scale Classic Cars Johnny Krol introduced in PlugIn T7 runs a body shop that specializes in restoring classic cars Johnny owns three classics and began collecting scale models when his wife put herfoot down and said no to building more garage space for his cars Johnny has decided that he needs a form and a few reports created to help in identifying the buying habits of his customers Johnny has provided you with an updated databasefile T8ClassicCars7Datamdb Specifically Johnny wants you to 1 Create a form thatwill allow Johnny to enter new classic cars and update existing records The form should use a Columnarformatand the Sandstone style Save the form as Catalog 2 Create a reportlisting classic cars grouped bytheir make The reportshould display all fields from the CATALOG table sort by model name and calculate the average price for each make Use the Formal style Save the report as Cars By Model FIGURE T8 Students by Seminar Report PlugIn T8 Dacisian Making Using Access 391 T811 T52 39 PluglnT5 Designing Database Applications LEARNING OUTCOMES 1 Describe the purpose of the relational database model in a database management system 2 Listthe relational database model39s basic components 3 Describe why entities and attributes are organized into tables 4 Describe how data redundancy is handled in the relational database model 5 Explain the need for an entityrelationship diagram in a database management system 6 Describe the Chen model symbols used in entityrelationship modeling 7 Explain the purpose of normalization 8 Describe the first three normal forms typically used in normalization Introduction Businesses rely on their database systems for accurate uptodate information Without those databases of mission critical information most businesses would be unable to perform their normal daily transactions much less create summary reports that help management make strategic decisions To be useful the infor mation must be accurate complete and organized in such a way that it can be retrieved when needed and in the format required The core units introduced the database which maintains information about various types of objects inventory events transactions people employees and places warehouses A database management system DBMS is software through which users and application programs interact with a database The relational database model is a type of database that stores its information in the form of logi cally related twodimensional tables This plugin will build on the core units by providing specific details about how to design a relational database application Entities and Data Relationships There are numerous elements in a business environment that need to store infor mation and those elements are related to one another in a variety of ways Thus a database must contain not only the information but also information about the relationships among the information The idea behind a database is that the user either a person working interactively or an application program has no need to worry about the way in which infor mation is physically stored on disk A database management system translates between the user s request for information and the physical storage A data model is a formal way to express data relationships to a database man agement system DBMS The underlying relationships in a database environment are independent of the data model and therefore independent of the DBMS that is being used Before designing a database for any data model data relationships need to be defined An entity relationship diagram ERD is a technique for docu menting the relationships between entities in a database environment ENTITIES AND THEIR A39I39I39RIBUTES An entity sometimes called a table is a person place thing transaction or event about which information is stored A customer is an entity as is a merchandise item Entities are not necessarily tangible for instance an appointment to see the doctor is an entity Attributes also called fields or columns are characteristics or properties of an entity instance For example a CUSTOMER entity can be described by a Customer Number FirstName LastName Street City State Zip Code Phone Number Credit Card No and Credit Card Exp refer to Figure T51 When entities in a database are represented only the attributes are stored Each group of attributes models a single entity type in the real world and values assigned to these attributes represent instances of objects entity occurrences cor responding to the entity For example in Figure T52 there are four instances of a CUSTOMER entity stored in a database If there are 1000 customers in the database then there will be 1000 instances of CUSTOMER entities Instances can sometimes be referred to as records Entity Identifiers An entity identifier ensures that each entity instance has a unique attribute value that distinguishes it from every other entity instance an entity identifier is also referred to as a primary key which will be discussed later in the plugin The pri mary purpose for entering the information that describes an entity into a database is to retrieve the information at some later date This means there must be some way of distinguishing one entity instance from another in order to retrieve the cor FIGURE 75 i rect entity instance An entity identi er ensures that each entity instance has a unique attribute value that distinguishes it from every other entity instance Ent39t39es and Atmbums Example CUSTOMER ENTITIES Customer Number Order Number Distributor Number First Name Customer Number ame Last Name Order Date Street Street Order Filled City City State State Zip Code Atmbmes Zip Code Phone Number Phone Number Contact Name Credit Card No Contact Phone Credit Card Exp PlugIn T5 a 39 T53 101 Main Street 5555555555 John Doe 101 Main Street Vail Colorado 88888 6666666666 FIGURE T5 2 Customer Entity Instance FIGURE T5 3 Composite Attributes T54 39 PluglnT5 Denver Colorado 80208 CUSTOMER121 W Assume for example that a local video CU ST O MER 1111 store MegaVideo has two customers S a m Smith named John Smith If an employee searches CUSTOMER OOO Bill Miller 101 North Main Street Englewood Colorado 80211 7777777777 for the items John Smith has ordered which John Smith will the DBMS retrieve In this case both of them Since there is no way to distinguish between the two customers the result of the query will be inaccurate MegaVideo can solve the problem by cre ating an entity identifier Some entities such as ORDER come with natural identifiers such as an Order Number Typically a unique randomly generated number is assigned to entity identifiers A constraint is a rule to which some elements in a database must adhere All entities must have a unique identifier that is a constraint That is to say when an instance of an entity in a database is stored the DBMS needs to ensure that the new instance has a unique identi er The enforcement of a variety of database con straints helps to maintain data consistency and accuracy CUSTOMERWSOU Jane Cook 101 South Main Street Littleton Colorado 80126 4444444444 A39I39I39RIBUTES There are several types of attributes including I Simple versus composite I Singlevalued versus multivalued I Stored versus derived I Nullvalued Simple versus Composite Composite attributes can be divided into smaller subparts which represent more basic attributes that have their own meanings A common example of a composite attribute is Address see Figure T53 Address can be broken down into a number of subparts such as Street City S tdte Zip Code Street may be further broken down by Number Street Ndme and ApdrtmentUnit Number Attributes that are not divis ible into subparts are called simple dttributes SingleValued versus MultiValued When creating a relational database the attributes in the data model must be singlevalued Singlevalued means having only a single value of each attribute at any given time For example a CUSTOMER entity allows only one Phone Number A Composite Attribute for each CUSTOMER If a CUSTOMER has more than one Phone Number and wants them all included in the database then the CUSTOMER entity cannot handle them The existence of more than one Phone Number turns the Phone Number attri bute into a multivalued attribute Multi vulued means having the potential to con tain more than one value for an attribute at any given time An entity in a relational database cannot have multivalued attri butes Those attributes must be handled by creating another entity to hold them In the case of the multiple Phone Num bers a PHONE NUMBER entity needs to be created Each instance of the entity would include the Customer Number of the person to whom the Phone Number belonged along with the Phone Number If a customer had two Phone N umbers then there wouldbe two instances of the PHONE NUMBER entity for the CUSTOMER see Figure T54 Multi valued attributes can cause prob lems with the meaning of data in the dat ha se 39r39 L39 0 place unnecessary restrictions on the amount of data that can be stored Relational databases do not allow multi valued attributes for this reason For example an EMPLOYEE entity with attributes for the Numes and Birthdutes of dependents would be considered multivalued When searching a multivalued attribute a DBMS must search each value in the attribute most likely scanning the contents of the attribute sequentially A sequen tial search is the slowest type of search available Generally a multi valued attribute is a major hint that another entity is needed The only way to handle multiple values of the same attribute is to create an entity for which multiple instances can be stored one for each value of the attribute In the case of the EMPLOYEE entity a DEPENDENT entity that could be related to the EMPLOYEE entity needs to be created There would be one occurrence of the DEPENDENT entity related to an occurrence of the EMPLOYEE entity for each of an employee s dependents In this way there is no limit to the number of an employee s dependents In addition each occurrence of the DEPENDENT entity would contain the Name and Birthdute of only one dependent eliminating any confusion about which Nume was associated with which Birthdute as suggested in Figure T55 Searching would also be fasterbecause the DBMS could use quicker search techniques on the individual DEPENDENT entity occurrences without resorting to the slow sequential search pusmmEmr Sam Smith 101 Main Street Denver Colorado 80208 CUSTOMER 22g John Doe 101 Main Street Vail Colorado 88888 bluwul and Stored versus Derived If an attribute can be calculated using the value of another attribute it is called a derived attribute The attribute that is used to derive the attribute is called a stored uttn bute Derived attributes are not stored in the file but can be derived when needed from the stored attributes One example of a derived and stored attribute is a person s age If the database has a stored attribute such as the person s Dute ofBirth then you can create a derived attribute called Age from taking the Current Date this is pulled from the system the database is running on and subtracting the Date ofBirth to get the age PlugIn T5 5555555555 7777777777 4444444444 PHONE NUMBER 22 FIGURE T54 PHONE NUMBERHl PHONE NUMBERHl Customer Entity and Phone Number Entity 39 T55 quot 5le 1 Sam Smith 101 Main Street Denver Colorado 80208 5555555555 EMPLOYEE20 John Doe 101 Main Street Vail Colorado 88888 6666666666 FIGURE T55 Employee Entity and Dependent Entity FIGURE T5 6 Chen Model with Attributes Customer Number T56 39 PluglnT5 NullValued There are cases where an attribute does not have an applicable value for an attri bute For these situations the null valued attribute is created A person who does not have a mobile phone would have null stored at the value for the Mobile Phone Number attribute Null can also be used in situations where the attribute value is unknown There are two cases where this can occur one where it is known that the attribute is valued but the value is miss ing for example Hair Color Every person has a hair color but the information may be missing Another situation is if Mobile Phone Numberis null it is not known ifthe person does not have a mobile phone or if that information is just missing DEPENDENT100 Sue Smith 101 Main Street Denver Colorado 80208 5141990 0N DEPENDENT100 Bill Smith 101 Main Street Denver Colorado 80208 1141994 007 DEPENDENT20 Jane Doe 101 Main Street Vail Colorado 88888 5162000 Documenting Logical Data Relationships The two most commonly used styles of ERD notation are Chen named after the originator of entityrelationship modeling Dr Peter Chen and Information Engineering which grew out of work by James Martin and Clive Finkelstein It does not matter which is used as long as everyone who is using the diagram understands the notation The Chen model uses rectangles to represent entities Each entity s name appears in the rectangle and is expressed in the singular as in CUSTOMER The original Chen model did not provide a method for showing attributes on the ERD itself However many people have extended the model to include the attributes in ovals as illustrated in Figure T56 BASIC DATA RELATIONSHIPS The relationships that are stored in a database are between instances of entities For example a MegaVideo customer is related to the ITEMs he or she ORDERs Each instance of the CUSTOMER entity is related to instances of the specific ITEM ordered see Figure T57 This is a purely conceptual representation of what is in the database and is completely unre lated to the physical storage of the data When data relationships are docu mented such as drawing an ERD types of relationships among entities are shown displaying the possible relationships that are allowable in the database Unless a relationship is mandatory there is no requirement that every instance of an entitybe involved in the documented rela tionships For example MegaVideo could store information about a CUSTOMER without the customer having any current ORDERs to which it is related Once the basic entities and their attri butes in a database environment have been defined the next task is to identify the relationships among those entities There are three basic types of relation ships 1 onetoone 2 onetomany and 3 manytomany 4 CUSTOMER 111 Sam Smith 101 Main Street Denver Colorado 80208 5555555555 OnetoOne l CUSTOMER 11 11W 1142008 2550 A onetoorie 11 relationship is between two entities in which an instance of entity A can be related to only one instance of entity B and entity B can be related to only one instance of entity A Consider an airport in a small town and the town in which the airport is located both of which are described in a database of small town airports this would not be true for some CUSTOMER 050 1172008 1050 1150 2000 Jane Cook 101 South Main Street Littleton Colorado 80126 4444444444 CUSTOMER 050 12242008 CUSTOMER 111 12182008 CUSTOMER 050 major metropolitan cities such as New York City with two major airports Each of these might be represented as an instance of a different type of entity As shown in Figure T58 the relationships between the two instances can then be expressed as The airport is located in one and only one town and the town contains one and only one airport The Chen method as displayed in Figure T58 uses rectangles to document entities a diamond to represent the relationship and numbers to show the type of relationship in this example 11 This is a true onetoone relationship because at no time can a single AIRPORT be related to more than one TOWN and no TOWN can be related to more than one AIRPORT Although there are municipalities that have more than one AIRPORT the TOWNS in this database are too small for that to happen True onetoone relationships are rare in business For example assume that MegaVideo decides to start dealing with a new distributor of DVDs At rst the company orders only one specialty title from the new distributor The instance of the DISTRIBUTOR entity in the database is related to just the one merchandise ITEM instance This would then appear to be a onetoone relationship Over time MegaVideo may choose to order more titles from the new distributor which would Violate the rule that the distributor must be related to no more than one merchan dise item Therefore this is not a true onetoone relationship this is an example of a onetomany relationship which is discussed next What if MegaVideo created a special CREDIT CARD entity to hold data about the credit cards that CUSTOMERS used to secure their rentals Each CUSTOMER has only one credit card on file with the store There would therefore seem to be a onetoone relationship between the instance of a CUSTOMERS entity and the instance of the CREDIT CARD entity In this case it is a single entity The Credit Card Number the Type of Credit Card and the Credit Card Expiration Date can all become attributes of the CUSTOMERS entity Given that only one credit card is stored for each customer the attributes are not multiValued no separate entity is needed OnetoMany A one tomdny I M relationship is between two enti ties in which an instance of entity A can be related to zero one or more instances of entity B and entity B can be related to only one instance of entity A This is the most common type of relationship In fact PlugIn T5 FIGURE T5 7 Entity Relationships FIGURE 75 8 A OnetoOne Relationship 39 T57 FIGURE T58 A One toIVlany Relationship FIGURE T5 3910 A ManytoMany Relationship T5a PluglnT5 most relational databases are constructed from the rare onetoone relationship and numerous onetomany relationships MegaVideo typically ORDERS many ITEMs in this scenario an item is a DVD title from each DISTRIBUTOR and a given ITEM comes from only one DISTRIBUTOR as Figure T59 demonstrates Similarly a CUSTOMER places many ORDERS but an ORDER comes from only one CUSTOMER When specifying data relationships there needs to be an indication of the pos sible relationships but an indication is not necessary that all instances of all enti ties participate in every documented relationship There is no requirement that a DISTRIBUTOR be related to any merchandise ITEM much less one or more mer chandise I TEMs It might not make much sense to have a DISTRIBUTOR in the database from whom the company did not ORDER but there is nothing to prevent data about that DISTRIBUTOR from being stored You will notice that there is an MzN relationship between ORDER and ITEM which we will discuss next ManytoMany A manytomany MxN relationship is between two entities in which an instance of entity A can be related to zero one or more instances of entity B and entity B can be related to zero one or more instances of entity A There is a manytomany relationship between a MegaVideo ORDER and the merchandise ITEM carried by the store refer to Figure T510 A CUSTOMER can ORDER many I TEMS and each ITEMs canbe ORDERed from many CUSTOMERS anytomany relationships bring two major problems to a database s design These issues and the way in which they are solved are discussed in the section Dealing with ManytoMany Relationships RELATIONSHIP CONNECTIVITY AND CARDINALITY Cardinalin expresses the specific number of entity occurrences associated with one occurrence of the related entity In the Chen model the cardinality is indicated by placing numbers beside the entities in the format of x y The first number in the cardinality represents the minimum value and the second number stands for the maximum value The data relationships discussed thus far have defined those relationships y starting each with zero indicating that the car dinality in a given instance of an entity in a relation ship is optional MegaVideo can store data about a CUSTOMER in its database before the CUSTOMER places an ORDER An instance of the CUSTOMER entity does not have to be related to any instances of the ORDER entity meaning there is an 0 prional cardinality However the reverse is not true for the MegaVideo database An ORDER mustbe related to a CUSTOMER Without a CUSTOMER an ORDER cannot exist As a result an ORDER is an example of a weak entity one that cannot exist in the data base unless a related instance of another entity is present and related to it An instance of the CUSTOMER entity can be related to zero one or more orders An instance of the ORDER entity must be related to one and only one CUSTOMER having a cardinality of 1 1 The zero option is not available to a weak entity The relationship between an instance of the ORDER entity and the CUSTOMER is a mandatory relationship as illustrated in Figure T511 Identifying weak entities and their associated mandatory relationships is impor tant for maintaining the consistency and integrity of the database Consider the effect of storing an ORDER without knowing the CUSTOMER to which it belongs There would be no way to ship the ITEM to the CUSTOMER causing a company to lose business In contrast a merchandise ITEM can exist in a database without indicating the DISTRIBUTOR from which it comes assuming that there is only one source per item Data about a new ITEM can be stored before a DISTRIBUTOR is selected In this case the relationship between a DISTRIBUTOR and an ITEM is actually zerotomany Documenting Relationships The Chen Method As brie y described earlier the Chen method uses diamonds for relationships and lines to show the type of relationship between entities Figure T512 displays the relationship between a MegaVideo CUSTOMER and an ORDER The number 1 next to the CUSTOMER entity indicates that an ORDER belongs to at most one CUSTOMER The letter M next to the ORDER entity indicates that a CUSTOMER can place one or more ORDERs The wordwithin the rela tionship diamond gives some indication of the mean ing of the relationship There is one major limitation to the Chen method of drawing ERDsithere is no obvious way to indicate weak entities and mandatory relationships An ORDER should not exist in the database without a CUSTOMER ORDER is a weak entity and its relationship with a CUSTOMER is mandatory Some database designers have added a new symbol to the Chen method for a weak entity a doublebordered rectangle as shown in Figure T513 Whenever a weak entity is introduced into an ERD it indicates that the relationship between that entity and at least one of its parents is mandatory DEALING WITH MANYTOMANY RELATIONSHIPS There are problems associated with manytomany relationships One problem is straightforwardithe relational data model cannot handle manytomany rela tionships directly it is limited to onetoone and onetomany relationships This means that the manytomany relationships need to be replaced with a collection of onetomany relationships in a relational DBMS A second problem is a bit more subtle To understand it consider the relation ship between an ORDER MegaVideo places with a DISTRIBUTOR and the merchandise ITEM in the ORDER There is a manytomany relationship between the ORDER and the ITEM because each ORDER can PlugIn T5 FIGURE TS I I A Weak Entity and a Mandatory Relationship FIGURE T5 12 Chen Method Weak Entity Symbol FIGURE T5 13 Chen Method with Relationship 39 T59 FIGURE 5 4 Composite Entity Example Order Number 1000 Customer Number 1111 Customer Number1111 Item Number9244 L L T510 39 PlugIn T5 Customer Number 1111 Item Number9244 be for many I TEM s and over time each ITEM can appear on many ORDERs Whenever MegaVideo places an ORDER for an ITEM the number of copies of the ITEM varies depending on the perceived demand for the ITEM at the time the ORDER is placed Now the question Where should we store the Quantity being ordered It cannot be part of the ORDER entity because the Quantity depends on which item is being ordered Similarly the Quantity cannot be part of the ITEM entity because the Quantity depends on the specific ORDER To solve this you would need to create a composite entity which is discussed in the next section Composite Entities Entities that exist to represent the relationship between two other entities are known as composite entities As an example of how composite entities work con sider the relationship between an ORDER placed by a CUSTOMER and the I TEM s in the ORDER There is a manytomany relationship between an ITEM and an ORDER An ORDER can contain many I TEM s and over time the same ITEM can appear on many ORDERs refer back to Figure T59 What is needed is an entity that displays a speci c title that appears on a specific order In Figure T514 there are three ORDER instances and three merchandise ITEMinstances The first ORDER for Customer Number 1111 Order Number 1000 contains only one ITEM Item Number 9244 The second ORDER for Customer Number 1111 Order Number 1001 contains a second copy of Item Number 9244 but ordered on a different date Order Number 1002 which belongs to Customer Number 1211 has two ITEMs Item Number 9250 and Item Number 9255 Therefore a composite entity called ORDER LINE think of it as a line item on a packing slip is created to represent the relationship between an ORDER and an ITEM Figure T515 demonstrates the Chen notation for ERDs the symbol for a composite entity is the combination of a rectangle and a diamond Each ITEM is related to one ORDER LINE instance for each ORDER on which it appears Each ORDER LINE instance is related to one and only one ORDER it is also related to one and only one ITEM As a result the relationship between an ORDER and its ORDER LINE is onetomany one orderhas one or more line items and the relationship between an ITEM and the ORDER LINE on which it appears is onetomany one item appears in zero one or more line items The presence Order Number 1002 Customer Number 1211 12112008 B3490 Order Number 1001 Customer Number1111 11102008 095 Customer Number 1211 Item Number9250 Customer Number 1211 Item Number9255 of the composite entity has removed the original manytomany relationship and turned it into two onetomany relationships SCHEMAS A schema is a completed entity relationship diagram representing the overall logi cal plan of a database This is the way in which the people responsible for maintain ing the database will view the design Users both interactive users and application programs may work with only a portion of the logical schema In addition both the logical schema and the users views of the data are at the same time distinct from the physical storage The underlying physical storage which is managed by the DBMS is known as the physical schema It is for the most part determined by the DBMS only very large DBMSs give any control over physical storage The benefit of this arrangement is that both database designers and users do not need to be concerned about physi cal storage greatly simplifying access to the database and making it much easier to make modifications The Relational Data Model Once the ERD is completed it can be translated from a conceptual logical schema into the formal data model required by the DBMS Most database installations are based on the relational data model The relational data model is the result of the work of one person Edgar E E Codd During the 1960s Dr Codd trained as a mathematician began working with existing data models His experience led him to believe that these were clumsy and unnatural ways of representing data relationships He therefore went back to math ematical set theory and focused on the construct known as a relation Dr Codd extended that concept to produce the relational database model which he intro duced in a historic seminal paper in 1970 UNDERSTANDING RELATIONS In mathematical set theory a relation is the definition of a table with columns eg attributes and rows eg records The word table is used synonymously with entity The definition specifies what will be contained in each column of the table but does not include information When rows of information are included an instance of a relation is created such as the CUSTOMER relation in Figure T516 PlugIn T5 2 FIGURE T5 15 ERD of Composite Entity Iw T511 At first glance a relation looks much cu smmerl l like a portion of a spreadsheet Since it Customerlllum39ber Firstrlllame LastName Phone Number has its underpinnings in mathematical set theory a relation has some very specific 00m Bl M39ller 777 777 7777 characteristics that distinguish it from 0505 Ja ne Cook 4444444444 other ways of looking at information Each of these characteristics forms the basis of ml sam sm39th 555 555 5555 a constraint that will be enforced by the k 1212 John Doe 6666666666 DBMS FIGURE l E l 6 A Sample Customer Relation T512 39 PluglnT5 Columns and Column Characteristics Two or more tables within the same relational schema may have columns with the same names in fact in some circumstances this is highly desirable But a single table must have unique column names When the same column name appears in more than one table and tables that contain that column are used in the same operation eg query the name of the column must be quali ed by preceding it with the name of the table and a period as in CUSTOMER Customer Number First Name Last Name Phone Number Note the proper notation is to capitalize the table name eg CUSTOMER and all columns are in title case Customer Number surrounded by parenthesis Rows and Row Characteristics A row in a relation has the following properties I Only one value at the intersection of a column and rowia relation does not allow multivalued attributes I Uniquenessithere are no duplicate rows in a relation I A primary keyia primary key is a field or group of fields that uniquely identi fies a given entity in a table Primary Key A primary key makes it possible to uniquely identify every row in a table The pri mary key is important to define in order to retrieve every single piece of informa tion put into a database As far as a relational database is concerned there are only three pieces of infor mation to retrieve for any specific bit of information 1 the name of the table 2 the name of the column and 3 the primary key of the row If primary keys are unique for every row then the results will be exactly what was searched for If they are not unique then the data being retrieved will be a row with the primary key value which may not be the row containing the data being searched The proper notation to use when documenting the name of the table the col umn name and primary key is as follows CUSTOMER Customer Number First Name Last Name Phone Number Again notice that the table name is capitalized the primary key is underlined and it is the first attribute listed containing the column names Along with being unique a primary key must not contain the value null Null is a special database value meaning unknown It is not the same as a zero or a blank If one row has a null primary key then the data structure is all right The minute a sec ond one is introduced the property of uniqueness is lost The presence of nulls in any primary key column is forbidden This constraint known as entity integrity will be enforced by a DBMS whenever information is entered or modi ed Entity integ rity is a constraint on a relation that states that no part of a primary key can be null Selecting a primary key can be a challenge Some entities have natural primary keys such as purchase order numbers as previously mentioned Primary keys are often arbitrary unique identifiers such as those a company attaches to the orders it sends to vendors Two qualities of all primary keys are 1 A primary key should contain some value that can never be null Z A primary key should never change REPRESENTING DATA RELATIONSHIPS The use of identi ers in more than one relation was mentioned in the preceding section This is the way in which relational databases represent relationships bet ween entities Each table in Figure T517 is directly analogous to the entity by the same name in the MegaVideo ERD The CUSTOMER table is identified by a Customer Number a randomly generated unique primary key The ORDER table is identified by an Order Number another arbitrary unique primary key assigned by MegaVideo The table ORDER LINE tells the company which I TEM s are part of which ORDER This table requires a concatenated primary keybecause multiple ITEMs can appear on mul tiple ORDERs The selection of this primary key however has more significance than simply identifying each row it also represents a relationship between the ORDER LINES the ORDER on which they appear and the ITEMs being ordered The ITEM table is identified by an Item Number an arbitrary unique primary key The Item Number column in the ORDER LINE table is the same as the primary key of the I IEM table This indicates a onetomany relationship between the two tables Similarly there is also a onetomany relationship between the ORDER and ORDER LINE tables because the OrderNumber column in the ORDER LINE table is the same as the primary key of the ORDER table When a table contains a column that is the same as the primary key of another table the column is called a foreign key A foreign key is a primary key of one table that appears as an attribute in another table and acts to provide a logical relation ship between the two tables The matching of foreign keys to primary keys repre sents data relationships in a relational database 555755575555 Prima ry key 44474444444 Primary 9 3 Foreign key ITEM llemNumhev DlsmhulnvNumhev Prime Iy key PlugIn T5 FIGURE T517 Relationsfrom the MegaVideo Database 39 T513 T514 39 PluglnT5 Foreign keys may be a part of a concatenated primary key or they may not be part of their table s primary key at all Consider a pair of MegaVideo CUSTOMER and ORDER relations CUSTOMER Customer Number First Name Last Name Phone Number ORDEROrder Number Customer Number Order Date The Customer Number column in the ORDER table is a foreign key that matches the primary key of the CUSTOMER table It represents the onetomany relation ship between CUSTOMERs and the ORDERs they place However the Customer Number is not part of the primary key of the ORDER table it is a nonkey attribute that is nonetheless a foreign key which is represented by using the double under line notation Technically foreign keys need not have values unless they are part of a concate nated primary key they can be null However in this particular database Mega Video would be in serious trouble if a Cus tomer Number was null since there would be no way to know which CUSTOMER placed an ORDER A relational DBMS uses the relationships indicated by matching data between pri mary and foreign keys Assume that a MegaVideo employee wanted to see what Titles had been ordered with Order Number 1002 First the DBMS identi es the rows in the ORDER LINE table that contain an Order Number of 1002 Then it takes the Item Numbers from the rows and matches them to the Item Numbers in the ITEM table In the rows where there are matches the DBMS finally retrieves the associated Title Foreign Keys and Primary Keys in the Same Table Foreign keys do not necessarily need to reference a primary key in a different table they need only reference a primary key As an example consider the following employee relation EMPLOYEE Employee Number First Name Last Name Department Manager Number A manager is also an employee Therefore the Manager Number although named differently from the Employee Number is actually a foreign key that refer ences the primary key of its own table The DBMS will therefore always ensure that whenever a user enters a Manager Number that manager already exists in the table as an employee Having a foreign key reference a primary key in the same table is relatively rare Referential Integrity The procedure described in the preceding section works very well unless there is no OrderNumber in the ORDER table to match a row in the ORDER LINE table This is undesirable since there is no way to ship the ordered ITEM because there is no way to find out which CUSTOMER placed the ORDER The relational data model enforces a constraint caled referentidl integrity which states that every nonnull foreign key value must match an existing primary key value Of all the constraints in a relational database this is probably the most impor tant because it ensures the consistency of the crossreferences among tables Referential integrity constraints stored in the database are enforced automati cally by the DBMS As with all other constraints each time a user enters or modifies data the DBMS checks the constraints and veri es that they are met If the con straints are violated the data modification will notbe allowed The Data Dictionary The data dictionary is a le that stores definitions of information types identifies the primary and foreign keys and maintains the relationships among the tables The structure of a relational database is stored in the database s data dictionary or catalog The data dictionary is made up of a set of relations identical in proper ties to the relations used to hold information No user can modify the data diction ary tables directly Data manipulation language commands eg Structured Query Language that create and remove database structural elements work by modifying rows in data dictionary tables The following types of information are typically found in a data dictionary Definitions of the columns that make up each table Integrity constraints placed on relations Security information which user has the right to perform which operation of which table When a user attempts to access information in any way a relational DBMS first goes to the data dictionary to determine whether the database elements the user has requested are actually part of the schema In addition the DBMS verifies that the user has the access rights to whatever he or she is requesting When a user attempts to modify information the DBMS goes to the data dic tionary to look for integrity constraints that may have been placed on the relation see Figure T518 If the information has met the constraints the modi cation is permitted Otherwise the DBMS returns an error message and does not allow the change All access to a relational database is through the data dictionary RELATIONSHIPS AND BUSINESS RULES In many ways database design is as much an art as a science The correct design for a specific business depends on the business rules what is correct for one orga nization may not be correct for another Assume there is more than one store when creating a database for a retail estab lishment One of the elements being modeled in the database is an employee s schedule Before that can be done the question of the relationship between an employee and a store needs to be answered Is it onetomany or manytomany Does an employee always work at only one store in which case the relationship is onetomany or can an employee split his or her time between more than one store producing a manytomany relationship This is not a matter of right or wrong database design but an issue of how the business operates These types of questions mustbe answered before you design a database Normalization Normalization is the process of placing attributes into tables that avoids the prob lems associated with poor database design Given any group of entities and attri butes there is a large number of ways to group them into relations There are at least two ways to approach normalization The first is to work from an ERD If the diagram is drawn correctly then there are some simple rules to use to translate it into relations that will avoid most relational design problems The draw back to this approach is that it can be difficult to determine whether the design is correct The second approach is to use the theoretical concepts behind good design to create relations This is a bit more diffith than working from an ERD but often results in a better design NORMAL FORMS Normal forms are the theoretical rules that the design of a relation must meet Each normal form represents an increasingly stringent set of rules Theoretically the higher the normal form the better the design of the relation PlugIn T5 T515 Table Name Attribute Name Contents Type Len 91h Format Range Req39 Key Relerenceil 1 Table CUSTOMER Customer Number First Name Last Name Street 39W State Zip Code Credit Card No Credit Card Exp Customer Number First Name Last Name Street Address City State ZIP Code CreditCard Number Credit Card Expiration Date VCHAR VCHAR2 VCHAR2 VCHAR2 VCHAR2 VCHAR2 NUMBER NUMBER DATE 10 12 I5 X10 X12 X15I X20 X20 X2 99999 XI5 MMD DYYYY gtgtgtgtgtgtgtgt PK ORDER OrderNumber CustomerNumber OrderDate OrderFI ed OrderNumber CustomerNumber OrderDate OrderFiIIed NUMBER VCHAR DATE DATE 99999 X10 MMD DYYYY MMD DYYYY 199999 PK FK CUSTOMER ORDER LINE OrderNumber Item Number Ouan 39ty Price Shipped OrderNumber Item Number Ouantity Selling Price Shipped NUMBER NUMBER NUMBER NUMBER VCHAR2 99999 99999 999 99999 X 199999 199999 1999 Y N FK FK ORDER ITEM ITEM K FIGURE Item Number Title Distributor Price Data Dictionary Example Item Number Title Distributor Price Number VCHAR2 VCHAR2 Number 99999 X25 X20 99999 199999 gtgtgtgtgtgtgtgtgtgtgtgtgt As illustrated in Figure T519 there are six nested normal forms indicating that if a relation is in one of the higher inner normal forms it is also in all of the normal forms surrounding it In most cases if rela tions are in third normal form 3NF then most ofthe problems common to badrela Bowecodd Normal Form BCNF tional designs are avoided BoyceCodd BCNF and fourth normal form 4NF Fourth Normal Form 4NF handle special situations that arise only occasionally Fifth normal form 5NF is a complex set of criteria that are extremely difficult to work with It is very difficr t to verify that a relation is in 5NF Most prac titioners do notbother with 5NF knowing that if their relations are in 3NF or 4NF if the situation warrants then their designs are generally problemfree BCNF 4NF and 5NF are beyond the scope of this plug in therefore they will not be discussed beyond what is mentioned in this section First Normal Form 1NF Second Normal Form 2NF Third Normal Form 3NF First Normal Form 1NF First normal form 1NF is where each field in a table contains different informa tion For example in the column labeled Customer only customer names or numbers are permitted Atable is in first normal form 1 NF if the data are stored in a twodimensional table with no repeating groups Although first normal form relations have no repeating groups they are full of other problems Expressed in the notation for relations that have been used in this plugin the relation notation would look like the following FIGURE T5 19 Normal Forms ORDERCustomer Number First Name Last Name Street City State ZIP Phone Order Number Order Date Item Number Title Price Shipped The first thing is to determine the primary key for this table The Customer Number alone will not be sufficient because the customer number repeats for every item ordered by the customer The Item Number will also not suffice because it is repeated for every order on which it appears The Order Number cannot be used because itis repeated for everyitem onthe order The only solution is a concatenated key in this example the combination of the Order Number and the Item Number Given that the primarykey is made up of the OrderN umber and the Item Number there are two important things that cannotbe done with this relation I Data about a customer cannot be added until the customer places at least one order because without an order and an item on that order there is no complete primary key I Data about a merchandise item cannot be added without that item being ordered There mustbe an OrderNumber to complete the primary key First normal form relations can also present problems when deleting data Consider for example what happens if a customer cancels the order of a single item I In cases where the deleted itemwas the only item on the order all data about the order is lost I In cases where the order was the only order on which the item appeared data about the item is lost I In cases where the deleted item was the only item ordered by a customer all data about the customer is lost PlugIn T5 DesigningDzliabase swlicazions quot T517 T513 39 PluglnT5 There is a final type of inconsistency in the ORDER relation that is not related to the primary key a modi cation or update anomaly The ORDER relation has a great deal of unnecessary duplicated data in particular information about cus tomers When a customer moves then the customer s data must be changed in every row for every item on every order ever placed by the customer If every row is not changed correctly then data that should be the same are no longer the same Second Normal Form 2NF Second normal form 2NF is when the relation is in first normal form and all non key attributes are functionally dependent on the entire primary key The solution to anomalies in a first normal form relation is to break the relation down so that there is one relation for each entity in the 1NF relation The ORDERs relation for example will break down into four relations CUSTOMER ORDER ORDER LINE and ITEM Such relations are in at least ZNF Although second normal form eliminates problems from many relations rela tions that are in second normal form still exhibit anomalies Assume that each DVD title that MegaVideo carries comes from one DISTRIBUTOR and that each DISTRIBUTOR has only one warehouse which has only one Warehouse Phone Number The following relation is therefore in ZNF IT39EM Item Number Title Distributor Warehouse Phone Number From each Item Number there is only one value for the item s Title Distributor and Wurehouse Phone Number There is one insertion anomalyidata cannot be inserted about a DISTRIBUTOR until an item from the DISTRIBUTOR is entered There is a deletion anomaly as well if the only item from the DISTRIBUTOR is deleted the data about the DISTRIBUTOR is lost Third Normal Form 3NF Third normal form 3NF is when the relation is in second normal form and there are no transitive dependencies In terms of entities the ITEM relation does contain two entities the merchandise ITEM and the DISTRIBUTOR The relation needs to be broken down into two smaller relations both of which are now in 3NF IT39EM Item Number Distributor Number DISTRIBUTORDistributor Number Warehouse Phone Number NORMALIZED RELATIONS AND DATABASE PERFORMANCE Normalizing the relations in a database separates entities into their own relations and makes it possible to enter modify and delete data without disturbing entities other than the one directly being modified When relations are split so that rela tionships are represented by matching primary and foreign keys DBMS is forced to perform matching operations between relations whenever a query requires data from more than one table In a normalized database data is stored about an ORDER in one relation data about a CUSTOMER in a second relation and data about the ORDER LINEs in yet a third relation The operation typically used to bring the data into a single table to prepare an output such as an invoice is known as a join A join is an operation that combines two relations by matching rows based on val ues in columns in the two tables The matching relationship is usually primary key to foreign key In theory a join looks for rows with matching values between two tables and creates a new row in a result table every time it finds a match In practice however performing a join involves manipulating more data than the simple combination of the two tables being joined would suggest Joins of large tables those of more than a few hundred rows can signi cantly slow down the performance of a DBMS PLUGIN SUMMARY database management system or DBMS is considered a basic component of data A processing The main advantage of using a DBMS is to enforce a logical and struc tured organization of the data Additionally using a DBMS provides a central store of data that can be accessed by multiple users from multiple locations Data can be shared among multiple applications instead of new iterations ofthe same data being reproduced and stored in new files for every new application The principal type of database used is a relational DBMS Designing a database requires both a logical and physical design The organization39s data model should reflect its key busi ness processes and decisionmaking requirements Entity relationship diagrams and normal ization are processes used to design a relational database MAKING BUSINESS DECISIONS 39I SportTech Events SportTech Events puts on athletic events for local high school athletes The company needs a database designed to keep track ofthe sponsor forthe event and where the event is located Each event needs a description date and cost Separate costs are negotiated for each event The company would also like to have a list of potential sponsors that in cludes each sponsor39s contact information such asthe name phone number and address Each event will have a single sponsor but a particular sponsor may sponsor more than one event Each location will need an ID contact person and phone number A particular event will use only one location but a location may be used for multiple events SportTech asks you to create an ERD from the information described above and then create a normaliza tion structure in 3NF 2 Course and Student Schedules Dick Scudderthe chairperson ofthe information technology department atthe University of Denver needs to create a database to keep track of all the courses offered bythe depart ment In addition Dick would like the database to include each instructor39s basic contact information such as ID number name office location and phone number Currently Dick has nine instructors seven fulltime faculty members and two adjuncts in the department For each course Dick would like to keeptrack ofthe course lDtitle and number ofcredit hoursWhen courses are offeredthe section ofthe course receives an ID number andwith that numberthe department keeps track ofwhich instructor is teaching the course Finally Dick needsto be able to keep track ofthe lT students and to knowwhich courses each student has taken The information he would like to know about each student includes ID number name and phone number He also needs to know what grade the student re ceives in each course Dick has asked you to create an ERD from the information described above and then create a normalization structure in 3NF A Foothills Athletics Foothills Athletics is an athletic facility offering services in the greater Highlands Ranch Colorado area All property owners living in Highlands Ranch are members ofthe Recreation Function ofthe Highlands Ranch Community Association HRCA Foothills Athletics consists PlugIn T5 Uesigmingi eiab256 gkplic I of a recreation facilitywhere residents have the opportunityto participate in athletic activi ties enroll their children in day camp or preschool or participate in an HRCA program Personnel Foothills Athletics has a number of employees primarily fitness course in structors and administrative personnel eg billing clerks equipment managers etc Re cords are kept on each employee past and present detailing employee name address phone number date of hire position and status as either a current or former employee Employees are assigned a unique fourdigit Employee ID numberwhen they are hired MembersWhen joining the Foothills Athletic center individuals are assigned a unique fourdigit Member ID number This information along with their name address phone number gender birth date and date of membership are recorded At the time of enroll ment each member decides on one ofthree available membership types along with a fixed membership fee Platinum 400 Gold 300 and Silver 200 This is a onetime fee that establishes a lifetime membership Facilities and Equipment Foothills Athletics has a variety of facilities and equipment choices Each facility has a unique room number and a size limitation associated with it Some of the rooms contain pieces of exercise equipment all have a serial number pro vided by its manufacturer that is used for inventory purposes In addition for each piece ofequipment purchase date andthe date of its last maintenance are recorded Each piece of equipment belongs to a specific equipment type such as stair master machine and is assigned a unique threedigit identification number The description the manufacturer39s model number and the recommended maintenance interval for that model of equipment are also kept on file Each equipment type is associated with a single manufacturer that is referenced by a unique twodigit manufacturer ID number Additional information main tained on each manufacturer isthe company name address and phone number The Task You have been hired to assist Foothills Athletics with creating a database structure that will incorporate all the features and business rules mentioned above You should start out developing an ERD and then proceed to create a normalization structure in 3NF 4 OntheVine Vineyard 0ntheVine Vineyard Inc is one of California39s largest winemaking facilities in Sonoma Valley striving to make both a visit to the vineyard and the wine tasting an unforgettable experience 0ntheVine is a small familyowned winery specializing in limited production of premium quality Chardonnay Sauvignon Blanc Merlot Syrah Zinfandel Sangiovese Viognier and Cabernet The Employees 0ntheVine currently employs over 12 fulltime employees with posi tions ranging from administrative assistant to winemaker Among the employees super visors have been appointed to manage the work of other employees Each supervised employee reports to only one supervisor Each employee is assigned a unique identifica tion number In addition to the employee39s name position and identification number the company also records each employee39s Social Security number address phone number and emergency contact The Vineyard The grounds of 0ntheVine Vineyard include the Estate house with an awardwinning rose garden winery and two vineyard plots of 40 acres each in separate locations Each vineyard is managed by a single employee and is referred to by its own unique name Sonoma Cellar and Sonoma Barrel No employee manages more than one vineyard Each vineyard is dedicated to the growing of a single grape variety peryear As mentioned above UntheVine Vineyard currently grows eight different grape varieties 1 Chardonnay 2 Sauvignon Blanc 3 Merlot T520 PluglnT5 DesigningDziiabase sspllcalions Syrah Zinfandel Sangiovese Viognier Cabernet PNP Squot The Winery Each wine produced is given a unique identification number in addition to its name Other information recorded for each wine is its vintage year category eg dry red dessert etc and percent alcohol which is a legal requirement Also recorded is the employee in charge of making that wine Winemakers may be responsible for more than one wine at a time The composition of a wine may be entirely from a single grape variety or may be a blend of more than one variety Several ofthe grape varieties are used in more than one blended wine The Customers OntheVine customers are mainly restaurants and wine shops but the winery also sells to individuals via the Internet All customers are assigned a unique customer identification number and this number is recorded along with their address and phone number Individual customers also have their first name last name and date of birth in order to demonstrate legal age recorded Restaurants and wine shops have their com pany name and tax identification number recorded All customers obtain their products by placing orders directly with OntheVine Each order is assigned a unique order number andthe date the order is receivedthe product or products ordered and the quantity or quantities desired are all recorded atthe same time A shipment status of quotpendingquot is assigned to an order until it is actually shipped where upon the status isthen changed to quotshippedquot The TaskYou have been hired to assist OntheVine Vineyard with creating a database structure that will incorporate all the features and business rules mentioned above You should start out developing an ERD and then proceed to create a normalization structure in 3NF PlugIn T5 Designing DatabaseApplications T521
Are you sure you want to buy this material for
You're already Subscribed!
Looks like you've already subscribed to StudySoup, you won't need to purchase another subscription to get this material. To access this material simply click 'View Full Document'