Solution Manual For Database Systems: Design, Implementation, and Management, 13th Edition

Preview (16 of 544 Pages)

100%

Purchase to unlock

Loading page image...

Chapter 1 Database Systems1Chapter 1Database SystemsDiscussion FocusHow often have your students heard that “you have only one chance to make a good first impression?”That’s why it’s so important to sell the importance of databases and the desirability of good databasedesign during the first class session.Start by showingyourstudents that they interact with databases on a daily basis. For example, howmany of them have bought anything using a credit card during the past day, week, month, or year? Noneof those transactions would be possible without a database. How many have shipped a document or apackage via an overnight service or via certified or registered mail? How many have checked coursecatalogs and class schedules online? And surely all of your students registered for your class? Didanybody use a web search engine to look for–and find–information about almost anything? This pointis easy to make: Databases are important because we depend on their existence to perform countlesstransactions and toprovide information.If you are teaching in a classroom equipped with computers, give some “live” performances. Forexample, you can use the web to look up a few insurance quotes or compare car prices and models.Incidentally, this is a good place to make the very important distinction between data and information. Inshort, spend some time discussing the points made in Section 1.1,"Why Databases?" and Section 1.2“Data vs. Information.”After demonstrating that modern daily life is almost inconceivable without the ever-present databases,discuss how important it is that the (database) transactions are made successfully, accurately, andquickly. That part of the discussion points to the importance of database design, which is at the heart ofthis book. If you want to have the keys to the information kingdom, you’ll want to know about databasedesign and implementation. And, of course, databases don’t manage themselves … and that point leadsto the importance of the database administration (DBA) function. There is a world of exciting databaseemployment opportunities out there.After discussing why databases, database design, and database administration are important, you canmove through the remainder of the chapter to develop the necessary vocabularyand concepts. Thereview questions help you do that … and the problems provide the chance to test the newfoundknowledge.

Loading page image...

Chapter 1 Database Systems2Answers toReview Questions1.Defineeach of the following terms:a.dataRaw facts from which the required information is derived. Data have little meaning unless theyare grouped in a logical manner.b.fieldA character or a group of characters (numeric or alphanumeric) that describes a specificcharacteristic. A field may define a telephone number, a date, or other specific characteristicsthat the end user wants to keep track of.c.recordA logically connected set of one or more fields that describes a person, place, event, or thing. Forexample,aCUSTOMERrecordmaybecomposedofthefieldsCUST_NUMBER,CUST_LNAME,CUST_FNAME,CUST_INITIAL,CUST_ADDRESS,CUST_CITY,CUST_STATE, CUST_ZIPCODE, CUST_AREACODE, and CUST_PHONE.d.fileHistorically, a collection of file folders, properly tagged and kept in a filing cabinet. Althoughsuch manual files still exist, we more commonly think of a (computer) file as acollection ofrelated recordsthat contain information of interest to the end user. For example, a salesorganization is likely to keep a file containing customer data. Keep in mind that the phraserelated recordsreflects a relationship based on function. For example, customer data are kept ina file named CUSTOMER. The records in this customer file are related by the fact that they allpertain to customers. Similarly, a file named PRODUCT would contain records that describeproducts–the records in this file are all related by the fact that they all pertain to products. Youwould not expect to find customer data in a product file, or vice versa.NOTENote: Field, record, and file are computer terms, created to help describe how data arestored insecondary memory. Emphasize that computer file data storage does not matchthe human perception of such data storage.

Loading page image...

Chapter 1 Database Systems32.What is data redundancy, and which characteristics of the file system can lead to it?Data redundancy exists when unnecessarily duplicated data are found in the database. For example, acustomer's telephone number may be found in the customer file, in the sales agent file, and in theinvoice file. Data redundancy is symptomatic of a (computer) file system, given its inability torepresent and manage data relationships. Data redundancy may also be the result of poorly-designeddatabases that allow the same data to be kept in different locations. (Here's another opportunity toemphasize the need for good database design!)3.What is data independence, and why is it lacking in file systems?Data independence is a condition in which the programs that access data are not dependent on thedata storage characteristics of the data. Systems that lack data independence are said to exhibit datadependence.File systems exhibit data dependence because file access is dependent on a file's datacharacteristics. Therefore, any time the file data characteristics are changed, the programs that accessthe data within those files must be modified.Dataindependenceexists when changes in the data characteristics don't require changes in theprograms that access those data.File systems lack data independence because all data accessprograms are subject to change when any of the file system’s data storage characteristics–such aschanging a data type--change.4.What is a DBMS, and what are its functions?A DBMS is best described as a collection of programs that manage the database structure and thatcontrol shared access to the data in the database. Current DBMSes also store the relationshipsbetween the database components; they also take care of defining the required access paths to thosecomponents. The functions of a current-generation DBMS may be summarized as follows:•The DBMS stores the definitions of data and their relationships (metadata) in a datadictionary; any changes made are automatically recorded in the data dictionary.•The DBMS creates the complex structures required for data storage.•The DBMS transforms entered data to conform to the data structures in item 2.•The DBMS creates a security system and enforces security within that system.•The DBMS creates complex structures that allow multiple-user access to the data.•The DBMS performs backup and data recovery procedures to ensure data safety.•The DBMS promotes and enforces integrity rules to eliminate data integrity problems.•The DBMS provides access to the data via utility programs and from programminglanguages interfaces.•The DBMS provides end-user access to data within a computer network environment.5.What isstructualindependence, and why is it important?Structuralindependence exists when data access programs are not subject to change when the file'sstructuralcharacteristics, such as the number or order of the columns in a table,change.Structuralindependence is important because it substantially decreases programming effort and programmaintenance costs.

Loading page image...

Chapter 1 Database Systems46.Explain the differencesbetweendata,information, and a databaseData are raw facts. Information is processed data to reveal the meaning behind the facts.Let’ssummarize some key points:•Data constitute the building bocks of information.•Information is produced by processing data.•Information is used to reveal the meaning of data.•Good, relevant, and timely information is the key to good decision making.•Good decision making is the key to organizational survival in a global environment.A database is a computer structure for storing data in a shared, integrated fashion so that the data canbe transformed into information as needed.7.What is the role of a DBMS, and what are its advantages?What are its disadvantages?Adatabase management system(DBMS) is a collection of programs that manages the databasestructure and controls access to the data stored in the database. Figure 1.2(shown in the text)illustrates that the DBMS serves as the intermediary between the user and the database. The DBMSreceives all application requests and translates them into the complex operations required to fulfillthose requests. The DBMS hides much of the database’s internal complexity from the applicationprogramsandusers.Theapplicationprogrammightbewrittenbyaprogrammerusingaprogramming language such as COBOL, Visual Basic, or C++, or it might be created through aDBMS utility program.Having a DBMS between the end user’s applications and the database offers some importantadvantages. First, the DBMS enables the data in the databaseto be sharedamong multipleapplications or users. Second, the DBMSintegratesthe many different users’ views of the data intoa single all-encompassing data repository.Because data are the crucial raw material from which information is derived, you must have a goodway of managing such data. As you will discover in this book, the DBMS helps make datamanagement more efficient and effective. In particular, a DBMS provides advantages such as:•Improved data sharing. The DBMS helps create an environment in which end users havebetter access to more and better-managed data. Such access makes it possible for end users torespond quickly to changes in their environment.•Better data integration. Wider access to well-managed data promotes an integrated view ofthe organization’s operations and a clearer view of the big picture. It becomes much easier tosee how actions in one segment of the company affect other segments.•Minimized data inconsistency.Data inconsistencyexists when different versions of the samedata appear in different places. For example, data inconsistency exists when a company’ssales department stores a sales representative’s name as “Bill Brown” and the company’spersonnel department stores that same person’s name as “William G. Brown” or when thecompany’s regional sales office shows the price of product “X” as $45.95 and its nationalsales office shows the same product’s price as $43.95. The probability of data inconsistencyis greatly reduced in a properly designed database.•Improved data access. The DBMS makes it possible to produce quick answers to ad hocqueries. From a database perspective, aqueryis a specific request for data manipulation (for

Loading page image...

Chapter 1 Database Systems5example, to read or update the data) issued to the DBMS. Simply put, a query is a questionand anad hoc queryis a spur-of-the-moment question. The DBMS sends back an answer(called thequery result set) to the application. For example, end users, when dealing withlarge amounts of sales data, might want quick answers to questions (ad hoc queries) such as:➢What was the dollar volume of sales by product during the past six months?➢What is the sales bonus figure for each of our salespeople during the past threemonths?➢How many of our customers have credit balances of $3,000 or more?•Improved decision making. Better-managed data and improved data access make it possibleto generate better quality information, on which better decisions are based.•Increased end-user productivity. The availability of data, combined with the tools thattransform data into usable information, empowers end users to make quick, informeddecisions that can make the difference between success and failure in the global economy.The advantages of using a DBMS are not limited to the few just listed. In fact, you will discovermany more advantages as you learn more about the technical details of databases and their properdesign.Although the database system yields considerable advantages over previous data managementapproaches, database systems do carry significant disadvantages. For example:•Increased costs. Database systems require sophisticated hardware and software and highlyskilled personnel. The cost of maintaining the hardware, software, and personnel required tooperate and manage a database system can be substantial. Training, licensing, and regulationcompliance costs are often overlooked when database systems are implemented.•Management complexity. Database systems interface with many different technologies and havea significant impact on a company’s resources and culture. The changes introduced by theadoption of a database system must be properly managed to ensure that they help advance thecompany’s objectives. Given the fact that databases systems hold crucial company data that areaccessed from multiple sources, security issues must be assessed constantly.•Maintaining currency. To maximize the efficiency of the database system, you must keep yoursystem current. Therefore, you must perform frequent updates and apply the latest patches andsecurity measures to all components. Because database technology advances rapidly, personneltraining costs tend to be significant.•Vendordependence.Giventheheavyinvestmentintechnologyandpersonneltraining,companies might be reluctant to change database vendors. As a consequence, vendors are lesslikely to offer pricing point advantages to existing customers, and those customers might belimited in their choice of database system components.•Frequent upgrade/replacement cycles.DBMS vendors frequently upgrade their products byadding new functionality. Such new features often come bundled in new upgrade versions of thesoftware.Some oftheseversionsrequire hardwareupgrades.Not onlydothe upgradesthemselves cost money, but it also costs money to train database users and administrators toproperly use and manage the new features.

Loading page image...

Chapter 1 Database Systems68.List and describethe different types of databases.The focus is on Section 1-3b, TYPES OF DATABASES. Organize the discussion around thenumber of users, database site location, and data use:•Number of usersoSingle-useroMultiuseroWorkgroupoEnterprise•Database site locationoCentralizedoDistributedoCloud-based•Type of dataoGeneral-purposeoDiscipline-specific•Database useoTransactional (production) database(OLTP)oData warehouse database(OLAP)•Degree of data structureoUnstructured dataoStructured dataFor a description of each type of database, please see section 1-3b.9.What are the main components of a database system?The basis of this discussion is Section 1-7a, THE DATABASE SYSTEM ENVIRONMENT. Figure1.10provides a good bird’s eye view of the components. Note that the system’s components arehardware, software, people, procedures, and data.10.Whatismetadata?Metadata is data about data. That is,metadata define the data characteristics such as the data type(such as character or numeric)and the relationships that link the data. Relationships are animportant component of database design.What makes relationships especiallyinteresting is that theyare often defined by their environment. For instance, the relationship between EMPLOYEE and JOBis likely to depend on the organization’s definition of the work environment. For example, in someorganizations an employee can have multiple job assignments, while in other organizations–or evenin other divisions within the same organization–an employee can have only one job assignment.The details of relationshiptypesand the rolesplayed by those relationships in data modelsaredefined and described in Chapter 2, Data Models.”. Relationships will play a key role in subsequentchapters. You cannot effectively deal with database design issues unless you address relationships.11.Explain why database design is important.

Loading page image...

Chapter 1 Database Systems7The focus is on Section 1-4, WHY DATABASE DESIGN IS IMPORTANT.Explain that moderndatabase andapplications development software is so easy to use that many people can quickly learnto implement a simple database and develop simple applications within a week or so, without givingdesignmuchthought. As data and reporting requirements become more complex, those same peoplewill simply (and quickly!) produce the required add-ons. That's how data redundancies and all theirattendant anomalies develop, thus reducing the "database" and its applications to a status worse thanuseless. Stress these points:•Good applications can't overcome bad database designs.•The existence of a DBMS does not guarantee good data management, nor does it ensure thatthe database will be able to generate correct and timely information.•Ultimately, the end user and the designer decide what data will be stored in the database.A database created without the benefit of a detailed blueprint is unlikely to be satisfactory. Pose thisquestion: would you think it smart to build a house without the benefit of a blueprint?So whywould you want to create a database without a blueprint? (Perhaps it would be OK to build a chickencoop without a blueprint, but would you want your house to be built the same way?)12.What are the potential costs of implementing a database system?Although the database system yields considerable advantages over previous data managementapproaches, database systems do impose significant costs. For example:•Increasedacquisition and operatingcosts. Database systems require sophisticated hardwareand software and highly skilled personnel. The cost of maintaining the hardware, software,and personnel required to operate and manage a database system can be substantial.•Management complexity. Database systems interface with many different technologies andhave a significant impact on a company's resources and culture. The changes introduced bythe adoption of a database system must be properly managed to ensure that they help advancethe company's objectives. Given the fact that databases systems hold crucial company datathat are accessed from multiple sources, security issues must be assessed constantly.•Maintaining currency. To maximize the efficiency of the database system, you must keepyour system current. Therefore, you must perform frequent updates and apply the latestpatches and security measures to all components. Because database technology advancesrapidly, personnel training costs tend to be significant.•Vendor dependence. Given the heavy investment in technology and personnel training,companies may be reluctant to change database vendors. As a consequence, vendors are lesslikely to offer pricing point advantages to existing customers and those customers may belimited in their choice of database system components.13.Use examples to compare and contrast unstructured and structured data. Which type is moreprevalent in a typical business environment?Unstructured dataare data that exist in their original (raw) state, that is, in the format in which theywere collected. Therefore, unstructured data exist in a format that does not lend itself to theprocessing that yields information.Structured dataare the result of taking unstructured data andformatting (structuring) such data to facilitate storage, use,andthe generation ofinformation. Youapply structure (format) based on the type of processing that you intend to perform on the data.

Loading page image...

Chapter 1 Database Systems8Some datamightbe not ready (unstructured) for some types of processing,but theymightbe ready(structured) for other types of processing. For example, the data value 37890mightrefer to a zipcode, a sales value, or a product code. If this value represents a zip code or a product code and isstored as text, you cannot perform mathematical computations with it. On the other hand, if thisvalue represents a sales transaction, it is necessary to format it as numeric.If invoicesare storedas images for future retrieval and display, you can scan them and save them ina graphic format. On the other hand, if you want to derive information such as monthly totalsandaverage sales, such graphic storage would not be useful. Instead, you could store the invoice data ina (structured) spreadsheet format so that you can perform the requisite computations.Based on sheer volume, most data is unstructured or semistructured. Data for conducting actualbusiness transactions is usually structured.14.What are some basic database functions that a spreadsheet cannot perform.Spreadsheets do not support self-documentation through metadata,enforcement of data types ordomainsto ensure consistency of data within a column, defined relationships among tables, orconstraints to ensureconsistency of data across related tables.15. What common problems do a collection of spreadsheets created by end users share with thetypical file system?A collection of spreadsheets shares several problems with the typical file system. First problem is thatend users create their own, private, copies of the data, which creates issues of data ownership. Thissituation also creates islands of information where changes to one set of data are not reflected in all ofthe copies of the data. This leads to the second problem–lack of data consistency. Because the data invariousspreadsheets may be intended to represent a view of the business environment, a lack ofconsistency in the data may lead to faulty decision making based on inaccurate data.16. Explain the significance of the loss of direct, hands-on access to business data that usersexperienced with the advent of computerized data repositories.Users lost direct, hands-on access to the business data when computerized data repositories weredeveloped because the IT skills necessary to directly access and manipulate the data were beyond theaverage user's abilities, and because security precautions restricted access to the shared data. This wassignificant because it removed users from the direct manipulation of data and introduced significant timedelays for data access. When users need answers to business questions from the data, necessity oftendoes not give them the luxury of time to wait days, weeks, or even months for the required reports. Thedesire to return hands-on access to the data to the users, among other drivers, helped to propel thedevelopment of database systems. While database systems have greatly improved the ability of users todirectly access data, the need to quickly manipulate data for themselves has lead to theproblems ofspreadsheets being used when databases are needed.17.Explain why the cost of ownership may be lower with a cloud database than with a traditional,company database.Cloud databases reside on the Internet instead of within the organization’s own network infrastructure.This can reduce costs because the organization is not required to purchase and maintain the hardwareand software necessary to house the database and support the necessary levels of system performance.

Loading page image...

Chapter 1 Database Systems9ProblemSolutionsONLINE CONTENTThe file structures you see in this problem set are simulated in a Microsoft Access databasenamedCh01_Problems, availablewww.cengagebrain.com.Given the file structure shown in Figure P1.1, answer Problems 1-4.FIGURE P1.1The File Structure for Problems 1-41.How many records does the file contain? How many fields are there per record?The file contains seven records (21-5Z through 31-7P) and each of the records is composed of fivefields (PROJECT_CODE through PROJECT_BID_PRICE.)2.What problem would you encounter if you wanted to produce a listing by city? How would yousolve this problem by altering the file structure?The city names are contained within the MANAGER_ADDRESS attribute and decomposing thischaracter (string) field at the application level is cumbersome at best. (Queries become much moredifficult to write and take longer to execute when internal string searches must be conducted.) If theability to produce city listings is important, it is best to store the city name as a separate attribute.3.If you wanted to produce a listing of the file contents by last name, area code, city, state, or zipcode, how would you alter the file structure?The more we divide the address into its component parts, the greater its information capabilities. Forexample,bydividingMANAGER_ADDRESSintoitscomponentparts(MGR_STREET,MGR_CITY, MGR_STATE, and MGR_ZIP), we gain the ability to easily select records on thebasis of zip codes, city names, and states. Similarly, by subdividing the MANAGER name into itscomponents MGR_LASTNAME, MGR_FIRSTNAME, and MGR_INITIAL, we gain the ability toproduce more efficient searches and listings. For example, creating a phone directory is easy whenyou can sort by last name, first name, and initial. Finally, separating the area code and the phonenumber will yield the ability to efficiently group data by area codes. Thus MGR_PHONE might be

Loading page image...

Chapter 1 Database Systems10decomposed intoMGR_AREA_CODE and MGR_PHONE. The more you decompose the data intotheir component parts, the greater the search flexibility. Data that are decomposed into their mostbasic components are said to beatomic.4.What data redundancies do you detect? How could those redundancies lead to anomalies?Note that the manager named Holly B. Parker occurs three times, indicating that she manages threeprojects coded 21-5Z, 25-9T, and 29-2D, respectively. (The occurrences indicate that there is a 1:Mrelationship between PROJECT and MANAGER: each project is managed by only one manager but,apparently, a manager may manage more than one project.) Ms. Parker's phone number and addressalso occur three times. If Ms. Parker moves and/or changes her phone number, these changes mustbe made more than onceand they must all be made correctly... without missing a single occurrence.If any occurrence is missed during the change, the data are "different" for the same person. Aftersome time, it may become difficult to determine what the correct data are. In addition, multipleoccurrences invite misspellings and digit transpositions, thus producing the same anomalies. Thesame problems exist for the multiple occurrences of George F. Dorts.5.Identify and discuss the serious data redundancy problems exhibited by the file structure shown inFigure P1.5.FIGURE P1.5The File Structure for Problems 5-8

Loading page image...

Chapter 1 Database Systems11NOTEIt is not too early to begin discussing proper structure. For example, you may focusstudent attention on the fact that, ideally, each row should represent a single entity.Therefore, each row's fields should define the characteristics of one entity, rather thanincludecharacteristicsofseveralentities.Thefilestructureshownhereincludescharacteristics of multiple entities. For example, the JOB_CODE is likely to be acharacteristic of a JOB entity. PROJ_NUM and PROJ_NAME are clearly characteristicsof a PROJECT entity. Also, since (apparently) each project has more than one employeeassigned to it, the file structure shown here shows multiple occurrences for each of theprojects. (Hurricane occurs three times, Coast occurs twice, and Satellite occurs fourtimes.)Given the file's poor structure, the stage is set for multiple anomalies. For example, if the charge forJOB_CODE = EE changes from $85.00 to $90.00, that change must be made twice. Also, ifemployeeJune H. Sattlemeier is deleted from the file, you also lose information about the existenceof her JOB_CODE = EE, its hourly charge of $85.00, and the PROJ_HOURS = 17.5. The loss of thePROJ_HOURS value will ultimately mean that the Coast project costs are not being chargedproperly, thus causing a loss of PROJ_HOURS*JOB_CHG_HOUR = 17.5 x $85.00 = $1,487.50 tothe company.Incidentally, note that the file contains different JOB_CHG_HOUR values for the same CT job code,thus illustrating the effect of changes in the hourly charge rate over time. The file structure appearsto represent transactions that charge project hours to each project. However, the structure of this filemakes it difficult to avoid update anomalies and it is not possible to determine whether a chargechange isaccuratelyreflected in each record. Ideally, a change in the hourly charge rate would bemade in only one place and this change would then be passed on to the transaction based on thehourly charge. Such a structural change would ensure the historical accuracy of the transactions.You might want to emphasize that the recommendedchangesrequire a lot of work in a file system.6.Looking at the EMP_NAME and EMP_PHONE contents in Figure P1.5, what change(s)would you recommend?A good recommendation would be to make the data moreatomic. That is, break up the datacomponntswheneverpossible.Forexample,separatetheEMP_NAMEintoitscomponenstEMP_FNAME, EMP_INITIAL, and EMP_LNAME. This change will make it much easier toorganize employee datathrough the employee name component. Similarly, the EMP_PHONE datashould be decomposed into EMP_AREACODE and EMP_PHONE. For example, breaking up thephone number 653-234-3245 into the area code 653 and the phone number 234-3245 will make itmuch easier to organize the phone numbers by area code. (If you want to print an employee phonedirectory, the more atomic employee name data will make the job much easier.)

Loading page image...

Chapter 1 Database Systems127.Identify thevariousdata sources in the file you examined in Problem 5.Given their answers to problem 5 and some additional scrutiny of Figure 1.5, your students shouldbe able to identify these data sources:•Employee data such as names and phone numbers.•Project data such as project names. If you start with an EMPLOYEE file, the project namesclearly do not belong in that file. (Project names are clearlynotemployee characteristics.)•Job data such as the job charge per hour. If you start with an EMPLOYEE file, the job chargeper hour clearly does not belong in that file. (Hourly charges are clearlynotemployeecharacteristics.)•The project hours, which are most likely the hours worked by the employee for that project.(Such hours are associated with a work product, not the employee per se.)8.Given your answer to Problem 7, what new files should you create to help eliminate the dataredundancies found in the file shown in Figure P1.5?The data sources are probably the PROJECT, EMPLOYEE, JOB, and CHARGE. The PROJECT fileshould contain project characteristics such as the project name, the project manager/coordinator, theproject budget, and so on. The EMPLOYEE file might contain the employee names, phone number,address, and so on. The JOB file would contain the billing charge per hour for each of the job types–a database designer, an applications developer, and an accountant would generate different billingcharges per hour. The CHARGE file would be used to keep track of the number of hours by job typethat will be billed for each employee who worked on the project.9.Identify and discuss the serious data redundancy problems exhibited by the file structure shown inFigure P1.9. (The file is meant to be used as a teacher class assignment schedule. One of the manyproblems with data redundancy is the likely occurrence of data inconsistencies–that two differentinitials have been entered for the teacher named Maria Cordoza.)FIGURE P1.9The File Structure for Problems 9-10Note that the teacher characteristics occur multiple times in this file. For example, the teacher namedMaria Cordoza’s first name, last name, and initial occur three times. If changes must be made forany given teacher, those changes must be made multiple times. All it takes is one incorrect entry orone forgotten change to create data inconsistencies. Redundant data are not a luxuryyou can affordin a data environment.

Loading page image...

Chapter 1 Database Systems1310.Given the file structure shown in Figure P1.9, what problem(s) might you encounter if buildingKOM were deleted?You would lose all the time assignment data about teachers Williston, Cordoza, and Hawkins, aswell as the KOM rooms 204E, 123, and 34. Here is yet another good reason for keeping data aboutspecific entities in their own tables! This kind of an anomaly is known as adeletion anomaly.11.Using your school’s student information system, print your class schedule. The scheduleprobably would contain the student identification number, student name, class code, classname, class credit hours, class instructor name, the class meeting days and times, and the classroom number. Use Figure P1.11 as a template to complete the following actions.a.Create a spreadsheet using the template shown in Figure P1.11 and enter your currentclass schedule.b.Enter the class schedule of two of your classmates into the same spreadsheet.c.Discuss the redundancies and anomalies caused by this design.This could be a good “mini-group” problem–groups of 3 students max. Ask them to create theirindividual class schedules in separate spreadsheets and then, a single spreadsheet containing all theirclass schedules.This exercise should incentivate “group discussion” and discover data anomalies and brain stormbetter wayd to store the class schedule data.Students are likely to use MS Excel or Google Sheets to create a simple tabular spreasheetcontaining the data outlined inFigure P1.11. The rows of the spreadsheet(s) will represent each oneof the classes they are taking.Students are likely to identify the redundancies around the class information since all three schedules(the student’s own schedule plus the schedules of the two classmates) will have at least the databaseclass in common. This easily leads to discussions of separating the data into at least two tables in adatabase.However, that still leaves the redundancies of redundant student data with each class thatthey are taking.Astute students might realize that this is analogous to theEmployee SkillCertifications shown in Figures 1.4 and Figure 1.5, such that a table for student data, a table for classdata, and a table to relate the students and classes is appropriate.

Loading page image...

Chapter 2 Data Models14Chapter 2Data ModelsDiscussion FocusAlthough all of the topics covered in this chapter are important, our students have given us consistentfeedback:If you can write precise business rules from a description of operations, database design isnot that difficult. Therefore, once data modeling(Sections 2-1,"Data Modelingand Data Models",Section 2-2 "The Importance of Data Models,”and 2-3, “Data Model Basic Building Blocks,”)has beenexamined in detail, Section 2-4, “Business Rules,” should receive a lot of class time and attention.Perhaps it is useful to argue that the answers toquestions2 and 3in theReview Questionssection arethe key to successful design.That’s why we have found it particularly important to focus on businessrules and theirimpact on the database design process.What are business rules, what is their source, and why are they crucial?Business rules are precisely written and unambiguousstatementsthat arederived from a detaileddescription ofanorganization's operations.When written properly, business rules define one or more ofthe following modeling components:•entities•relationships•attributes•connectivities•cardinalities–these will be examined in detail in Chapter 3, “The Relational Database Model.”Basically, the cardinalities yield the minimum and maximum number of entity occurrences in anentity. For example, the relationship decribed by “a professor teaches one or more classes”means that the PROFESSOR entity is referenced at least once and no more than four times in theCLASS entity.•constraintsBecause the business rules form the basis of the data modeling process, their precise statement is crucialto the success of the database design. And, because the business rules are derived from a precisedescription of operations, much of the design's success depends on the accuracy of the description ofoperations.Examples of business rules are:•An invoice contains one or more invoice lines.•Each invoice line is associated with a single invoice.•A store employs many employees.•Each employee is employed by only one store.•A college has many departments.•Each department belongs to a single college. (This business rule reflects a university that hasmultiple colleges such as Business, Liberal Arts, Education, Engineering, etc.)

Loading page image...

Chapter 2 Data Models15•A driver may be assigned to drive many different vehicles.•Each vehicle can be driven by many drivers. (Note: Keep in mind that this business rule reflectsthe assignment of drivers during some period of time.)•A client may sign many contracts.•Each contract is signed by only one client.•A sales representative may write many contracts.•Each contract is written by one sales representative.Note that each relationship definition requires the definition of two business rules. For example, therelationship between the INVOICE and (invoice) LINE entities is defined by the first two business rulesin the bulleted list. This two-way requirement exists because there is always a two-way relationshipbetween any two related entities. (This two-way relationship description also reflects the implementationby many of the availabledatabase designtools.)Keep in mind that the ER diagrams cannot always reflect all of the business rules. For example, examinethe following business rule:A customer cannot be given a credit line over $10,000 unless that customer has maintained asatisfactory credit history (as determined by the credit manager) during the past two years.This business rule describes a constraint that cannot be shown in the ER diagram. The business rulereflected in this constraint would be handled at the applications software level through the use of atrigger or a stored procedure. (Your students will learn about triggers and stored procedures in Chapter8, “Advanced SQL.”)Given their importance to successful design, we cannot overstate the importance of business rules andtheir derivation from properly written description of operations. It is not too early to start asking studentsto write business rules for simple descriptions of operations. Begin by using familiar operationalscenarios, such as buying a book at the book store, registering for a class, paying a parking ticket, orrenting a DVD.Also, try reversing the process: Give the students a chance to write the business rules from a basic datamodel such as the onerepresentedbythe text’s Figure 2.1 and 2.2. Ask your students to write thebusiness rules that are the foundation of the relational diagram in Figure 2.2and then point theirattention to the relational tables in Figure 2.1to indicate thatan AGENT occurrence can occur multipletimes in the CUSTOMER entity, thus illustrating the implementation impact of the business rulesAn agent can serve many customers.Each customer is served by one agent.

Preview Mode

This document has 544 pages. Sign in to access the full document!

Report

Study Now!

Document Details

Related Documents

DevOps and Its Importance Study Notes

Veeam Certified Engineer VMCE-V12

Modules 1 - 3 Basic Network Connectivity and Commu

Notes CCNA

CCNA 200-301 Portable Command Guide 5th Edition 20

CCNA 200-301 Portable Command Guide 5th Edition 22

CCNA 200-301 Portable Command Guide 5th Edition 36

Foundations of Novell Networking Section 26

Modulo 7 DHCPv4

Build and troubleshooting Integration Challenge

Company

Explore

Study Tools