Solution Manual for Fundamentals of Database Systems , 7th Edition

Preview (31 of 150 Pages)

100%

Solution Manual for Fundamentals of Database Systems , 7th Edition - Page 1 preview image

Loading page ...

Chapter 1: Databases and Database Users1CHAPTER 1: DATABASES AND DATABASE USERSAnswers to Selected Exercises1.8-Identify some informalqueries and update operationsthat you would expect to apply tothe database shown in Figure 1.2.Answer:(a) (Query) List the names of all students majoring in Computer Science.(b) (Query) What are the prerequisites of the Database course?(c) (Query) Retrieve the transcript of Smith. This is a list of <CourseName,SectionIdentifier, Semester, Year, Grade> for each course section that Smith hascompleted.(d) (Update) Insert a new student in the database whose Name=Jackson,StudentNumber=23, Class=1 (freshman), and Major=MATH.(e) (Update) Change the grade that Smith received in Intro to Computer Science section119 to B.1.9-What is the difference between controlled and uncontrolled redundancy?Answer:Redundancy is when the same fact is stored multiple times in several places in a database.For example, in Figure 1.5(a) the fact that the name of the student with StudentNumber=8 isBrown is stored multiple times. Redundancy is controlled when the DBMS ensures thatmultiple copies of the same data are consistent; for example, if a new record withStudentNumber=8 is stored in the database of Figure 1.5(a), the DBMS will ensure thatStudentName=Smith in that record. If the DBMS has no control over this, we haveuncontrolled redundancy.1.10-Specify all the relationships among the records of the database shown in Figure 1.2.Answer:(a) Each SECTION record is related to a COURSE record.(b) Each GRADE_REPORT record is related to one STUDENT record and one SECTIONrecord.(c) Each PREREQUISITE record relates two COURSE records: one in the role of a courseand the other in the role of a prerequisite to that course.1.11-Give some additional views that may be needed by other user groups for the databaseshown in Figure 1.2.Answer:(a) A view that groups all the students who took each section and gives each student'sgrade. This may be useful for printing the grade report for each section for theuniversityadministration's use.

Solution Manual for Fundamentals of Database Systems , 7th Edition - Page 2 preview image

Loading page ...

Solution Manual for Fundamentals of Database Systems , 7th Edition - Page 3 preview image

Loading page ...

Chapter 1: Databases and Database Users2(b) A view that gives the number of courses taken and the GPA (grade point average) foreach student. This may be used to determine honors students.1.12–Cite some examples of integrity constraints that you think can apply to the databaseshown in Figure 1.2.Answer:We give a few constraints expressed in English. Following each constraint, we give itstypein the relational database terminology that will be covered in Chapter 6, forreferencepurposes.(a) The StudentNumber should be unique for each STUDENT record (key constraint).(b) The CourseNumber should be unique for each COURSE record (key constraint).(c) A value of CourseNumber in a SECTION record must also exist in some COURSErecord(referential integrity constraint).(d) A value of StudentNumber in a GRADE_REPORT record must also exist in someSTUDENT record (referential integrity constraint).(e) The value of Grade in a GRADE_REPORT record must be one of the values in the set{A, B, C, D, F, I, U, S} (domain constraint).(f) Every record in COURSE must have a value for CourseNumber (entity integrityconstraint).(g) A STUDENT record cannot have a value of Class=2 (sophomore) unless the studenthascompleted a number of sections whose total course CreditHours is greater than24credits(general semantic integrity constraint).1.13-Give examples of systems in which it may make sense to use traditional fileprocessing instead of a database approach.Answer:1.Small internal utility to locate files2.Small single user application that does not require security (such as a customizedcalculator or a personal address and phone book)3.Real-time navigation system (with heavy computation and very little data)4.The students may think of others.1.14-Consider Figure 1.2.a.If the name of the ‘CS’ (Computer Science) Department changes to ‘CSSE’ (ComputerScience and Software Engineering) Department and the corresponding prefix for thecourse number also changes, identify the columns in the database that would needto be updated.b.Can you restructure the columns in COURSE, SECTION, and PREREQUISITE tables sothat only one column will need to be updated?Answer:

Solution Manual for Fundamentals of Database Systems , 7th Edition - Page 4 preview image

Loading page ...

Chapter 1: Databases and Database Users3a. The following columns will need to be updated.TableColumn(s)STUDENTMajorCOURSECourseNumber and DepartmentSECTIONCourseNumberPREREQUISITECourseNumber and PrerequisiteNumberb. You should split the following columns into two columns:TableColumnSplit ColumnsCOURSECourseNumberCourseDept and CourseNumSECTIONCourseNumberCourseDept and CourseNumPREREQUISITECourseNumberCourseDept and CourseNumPREREQUISITEPrerequisiteNumberPreReqDept and PreReqNumNote that in the COURSE table, the column CourseDept will not be needed after the abovechange, since it is redundant with the Department column.

Solution Manual for Fundamentals of Database Systems , 7th Edition - Page 5 preview image

Loading page ...

Chapter 2: Database System Concepts and Architecture1CHAPTER 2: DATABASE SYSTEM CONCEPTS AND ARCHITECTUREAnswers to Selected Exercises2.12-Think of different users for the database of Figure 1.2. What type of applications wouldeach user need? To which user category would each belong and what type of interfacewould they need?Answer:(a) Registration Office User: They can enter data that reflect the registration of studentsinsections of courses, and later enter the grades of the students. Applications caninclude:-Register a student in a section of a course-Check whether a student who is registered in a course has the appropriate prerequisitecourses-Drop a student from a section of a course-Add a student to a section of a course-Enter the student grades for a sectionApplication programmers can write a number of canned transactions for the registrationoffice end-users, providing them with either forms and menus, or with a parametricinterface.(b) Admissions Office User: The main application is to enter newly accepted students intothedatabase. Can use the same type of interfaces as (a).(c) Transcripts Office User: The main application is to print student transcripts.Application programmers can write a canned transaction using a report generator utilitytoprint the transcript of a student in a prescribed format. The particular student can beidentified by name or social security number. Another application would be to generategradeslips at the end of each semester for all students who have completed coursesduring thatsemester. Again, this application could be programmed using a reportgenerator utility.2.13-No solution provided.2.14-if you were designing a Web-based system to make airline reservations and to sellairline tickets, which DBMS Architecture would you choose fromSection 2.5? Why? Whywould the other architectures not be a good choice?Answer:2.5.4 Three-Tier Client/Server Architecture for Web Application is the best choice. The Clientconsists of Web User Interface. The Web Server contains the application logic whichincludes all the rules and regulations related to the reservation process and the issue oftickets; the Database Server contains the DBMS.2.5.1 Centralized DBMS Architecture would not work since the user interface and databaseserver are on different machines for a web-based system.2.5.2 Basic Client/Server Architecture and 2.5.3 Two-Tier Client/Server Architecture wouldwork if the Business Logic can reside on server other than the DBMS Server. In general, ifthe business logic was on the DBMS Server, it will put an excessive burden on the server. Ifthe business logic were to reside on the web client, it will burden the communication networkas well a possibly thin client.2.15-Consider Figure 2.1. In addition to constraints relating the values of columns in onetable to columns in another table, there are also constraints that impose restrictions on

Solution Manual for Fundamentals of Database Systems , 7th Edition - Page 6 preview image

Loading page ...

Chapter 2: Database System Concepts and Architecture2values in a column or a combination of columns within a table. One such constraint forcesthat a column or a group of columns must be unique across all rows in the table. Forexample, in the STUDENT table, the StudentNumber column must be unique (to prevent twodifferent students from having the same StudentNumber). Identify the column or the groupof columns in the other tables that must be unique across all rows in the table?Answer:TableColumn(s)COURSECourseNumberSince this contains the combination of the department and the numberthat must be unique within the department. Note we will overlook thefact this does not accommodate a department from offering several“Special Topics” course with the same CourseNumber but differenttitles. We could make this a combination of CourseNumber andCourseName, but this is more succeptible to someone mistyping whileentering data.PREREQUISITEThe combination of CourseNumber and PrerequisiteNumberSECTIONSectionIdentifierWe assume that no two sections can have the same SectionIdentifier.If we were to consider that SectionIdentifier is unique only within agiven course offered in a given term (such as section 2 of CS101)then the answer changes to the combination of SectionIdentifier,CourseNumber, Semester, and Year.GRADE_REPORTStudentNumber and SectionIdentifierAs per assumption stated in SECTION, the SectionIdentifier will bedifferent if a student takes the same course or a different course inanother term.

Solution Manual for Fundamentals of Database Systems , 7th Edition - Page 7 preview image

Loading page ...

Chapter3: Data Modeling Using the Entity-Relationship (ER) Model1CHAPTER3: DATA MODELING USING THE ENTITY-RELATIONSHIP (ER) MODELAnswers to Selected Exercises3.16-Consider the following set of requirements for a UNIVERSITY database that is used tokeep track of students' transcripts. This is similar but not identical to the database shown inFigure 1.2:(a) The university keeps track of each student's name, student number, social securitynumber, current address and phone, permanent address and phone, birthdate, sex, class(freshman, sophomore, ..., graduate), major department, minor department (if any), anddegree program (B.A., B.S., ..., Ph.D.). Some user applications need to refer to the city,state, and zip of the student's permanent address, and to the student's last name. Bothsocial security number and student number have unique values for each student.(b) Each department is described by a name, department code, office number, office phone,and college. Both name and code have unique values for each department.(c) Each course has a course name, description, course number, number of semester hours,level, and offering department. The value of course number is unique for each course.(d) Each section has an instructor, semester, year, course, and section number. The sectionnumber distinguishes different sections of the same course that are taught during the samesemester/year; its values are 1, 2, 3, ..., up to the number of sections taught during eachsemester.(e) A grade report has a student, section, letter grade, and numeric grade (0, 1, 2, 3,4 for F, D, C, B, A, respectively).Design an ER schema for this application, and draw an ER diagram for that schema.Specify key attributes of each entity type and structural constraints on each relationship type.Note any unspecified requirements, and make appropriate assumptions to make thespecification complete.Answer:

Solution Manual for Fundamentals of Database Systems , 7th Edition - Page 8 preview image

Loading page ...

Chapter3: Data Modeling Using the Entity-Relationship (ER) Model23.17-Composite and multi-valued attributes can be nested to any number of levels.Suppose we want to design an attribute for a STUDENT entity type to keep track of previouscollege education. Such an attribute will have one entry for each college previously attended,and this entry is composed of: college name, start and end dates, degree entries (degreesawarded at that college, if any), and transcript entries (courses completed at that college, ifany). Each degree entry is formed of degree name and the month and year it was awarded,and each transcript entry is formed of a course name, semester, year, and grade. Design anattribute to hold this information. Use the conventions of Figure 7.5.Answer:{ PreviousEducation ( CollegeName, StartDate, EndDate,{ Degree (DegreeName, Month, Year) },{ Transcript (CourseName, Semester, Year, Grade) } ) }

Solution Manual for Fundamentals of Database Systems , 7th Edition - Page 9 preview image

Loading page ...

Chapter3: Data Modeling Using the Entity-Relationship (ER) Model33.18-Show an alternative design for the attribute described in Exercise 7.17 that uses onlyentity types (including weak entity types if needed) and relationship types.Answer:This example illustrates a perceived weakness of the ER model, which is: how does thedatabase designer decide what to model as an entity type and what to model as arelationship type. In our solution, we created a weak entity type ATTENDANCE; each (weak)entity in ATTENDANCE represents a period in which a STUDENT attended a particularCOLLEGE, and is identified by the STUDENT and the StartDate of the period. Hence, theStartDate attribute is the partial key of ATTENDANCE. Each ATTENDANCE entity is relatedto one COLLEGE and zero or more DEGREEs (the degrees awarded during that attendanceperiod). The TRANSCRIPT of the STUDENT during each attendance period is modeled as aweak entity type, which gives the records of the student during the attendance period. Each(weak) entity in TRANSCRIPT gives the record of the sudent in one course during theattendance period, as shown in the ER diagram below. Other ER schema designs are alsopossible for this problem.

Solution Manual for Fundamentals of Database Systems , 7th Edition - Page 10 preview image

Loading page ...

Chapter3: Data Modeling Using the Entity-Relationship (ER) Model43.19-Consider the ER diagram of Figure 7.20, which shows a simplified schema for anairline reservations system. Extract from the ER diagram the requirements and constraintsthat resulted in this schema. Try to be as precise as possible in your requirements andconstraints specification.Answer:(1) The database represents each AIRPORT, keeping its unique AirportCode, the AIRPORTName, and the City and State in which the AIRPORT is located.(2) Each airline FLIGHT has a unique number, the Airline for the FLIGHT, and the Weekdayson which the FLIGHT is scheduled (for example, every day of the week except Sunday canbe coded as X7).(3) A FLIGHT is composed of one or more FLIGHT LEGs (for example, flight numberCO1223 from New York to Los Angeles may have two FLIGHT LEGs: leg 1 from New Yorkto Houston and leg 2 from Houston to Los Angeles). Each FLIGHT LEG has a DEPARTUREAIRPORT and Scheduled Departure Time, and an ARRIVAL AIRPORT and ScheduledArrival Time.(4) A LEG INSTANCE is an instance of a FLIGHT LEG on a specific Date (for example,CO1223 leg 1 on July 30, 1989). The actual Departure and Arrival AIRPORTs andTimes are recorded for each flight leg after the flight leg has been concluded. TheNumber of available seats and the AIRPLANE used in the LEG INSTANCE are also kept.(5) The customer RESERVATIONs on each LEG INSTANCE include the Customer Name,Phone, and Seat Number(s) for each reservation.

Solution Manual for Fundamentals of Database Systems , 7th Edition - Page 11 preview image

Loading page ...

Chapter3: Data Modeling Using the Entity-Relationship (ER) Model5(6) Information on AIRPLANEs and AIRPLANE TYPEs are also kept. For each AIRPLANETYPE (for example, DC-10), the TypeName, manufacturing Company, andMaximum Number of Seats are kept. The AIRPORTs in which planes of this typeCAN LAND are kept in the database. For each AIRPLANE, the AirplaneId, Total number ofseats, and TYPE are kept.3.20-No solution provided.3.21-Additional information:-There are 435 congresspersons in the U.S. House of Representatives.-States have between one (AK, DE, MT, ND, SD, VT, and WY) and 52 (CA)representatives.-M represents number of bills during the 2-year session.The resulting ER Diagram is shown in Figure A.3.22-A database is being constructed to keep track of the teams and games of a sportsleague. A team has a number of players, not all of whom participate in each game.It is desired to keep track of the players participating in each game for each team, thepositions they played in that game, and the result of the game. Try to design an ER schemadiagram for this application, stating any assumptions you make. Choose your favorite sport(soccer, football, baseball ...).Answer:The following design may be used for a baseball league. Here, we assumed that each gamein the schedule is identified by a unique Game#, and a game is also identified uniquely bythe combination of Date, starting Time, and Field where it is played. The Performanceattribute of PARTICIPATE is used to store information on the individual performance of each

Solution Manual for Fundamentals of Database Systems , 7th Edition - Page 12 preview image

Loading page ...

Chapter3: Data Modeling Using the Entity-Relationship (ER) Model6player in a game. This attribute can be designed to keep the information needed forstatistics, and may be quite complex. One possible design for the Performance attribute maybe the following (using the notation of Figure 7.8):Performance( {Hitting(AtBat#, Inning#, HitType, Runs, RunsBattedIn, StolenBases)},{Pitching(Inning#, Hits, Runs, EarnedRuns, StrikeOuts, Walks, Outs,Balks, WildPitches)},{Defense(Inning#, {FieldingRecord(Position, PutOuts, Assists, Errors)})} )Here, performance is a composite attribute made up of three multivalued components:Hitting, Pitching, and Defense. Hitting has a value for each AtBat of a player, and recordstheHitType (suitable coded; for example, 1 for single, 2 for double, 3 for triple, 4 forhome run, 0for walk,-1 for strikeout,-2 for fly out, ...) and other informationconcerning the AtBat.Pitching has a value for each inning during which the playerpitched. Defense has a value foreach inning a player played a fielding position. We canhave a less detailed or a moredetailed design for the performance of a player in eachgame, depending on how muchinformation we need to keep in the database. Suitablevariations of the ER diagram shownbelow can be used for other sports.3.23-Consider the ER diagram shown in Figure 7.21 for part of a BANK database. Eachbank can have multiple branches, and each branch can have multiple accounts and loans.(a) List the strong (nonweak) entity types in the ER diagram.(b) Is there a weak entity type? If so, give its name, its partial key, and its identifyingrelationship.(c) What constraints do the partial key and the identifying relationship of the weak entity typespecify in this diagram?

Solution Manual for Fundamentals of Database Systems , 7th Edition - Page 13 preview image

Loading page ...

Chapter3: Data Modeling Using the Entity-Relationship (ER) Model7(d) List the names of all relationship types, and specify the (min,max) constraint on eachparticipation of an entity type in a relationship type. Justify your choices.(e) List concisely the user requirements that led to this ER schema design.(f) Suppose that every customer must have at least one account but is restrictedto at most two loans at a time, and that a bank branch cannot have more than1000 loans. How does this show up on the (min,max) constraints?Answer:(a) Entity types: BANK, ACCOUNT, CUSTOMER, LOAN(b) Weak entity type: BANK-BRANCH. Partial key: BranchNo.Identifying relationship: BRANCHES.(c) The partial key BranchNo in BANK-BRANCH specifies that the same BranchNo value ayoccur under different BANKs. The identifying relationship BRANCHES specifies that

Solution Manual for Fundamentals of Database Systems , 7th Edition - Page 14 preview image

Loading page ...

Chapter3: Data Modeling Using the Entity-Relationship (ER) Model8BranchNo values are uniquely assigned for those BANK-BRANCH entities that are relatedtothe same BANK entity. Hence, the combination of BANK Code and BranchNo togetherconstitute a full identifier for a BANK-BRANCH.(d) Relationship Types: BRANCHES, ACCTS, LOANS, A-C, L-C. The (min, max) constraintsare shown below.(e) The requirements may be stated as follows: Each BANK has a unique Code, as well as aName and Address. Each BANK is related to one or more BANK-BRANCHes, and theBranhNo is unique among each set of BANK-BRANCHes that are related to the same BANK.Each BANK-BRANCH has an Address. Each BANK-BRANCH has zero or more LOANS andzero or more ACCTS. Each ACCOUNT has an AcctNo (unique), Balance, and Type and isrelated to exactly one BANK-BRANCH and to at least one CUSTOMER. Each LOAN has aLoanNo (unique), Amount, and Type and is related to exactly one BANK-BRANCH and to atleast one CUSTOMER. Each CUSTOMER has an SSN (unique), Name, Phone, andAddress, and is related to zero or more ACCOUNTs and to zero or more LOANs.(f) The (min, max) constraints would be changed as follows:3.24-Consider the ER diagram in Figure 7.22. Assume that an employee may work in up totwo departments or may not be assigned to any department. Assume that each departmentmust have one and may have up to three phone numbers. Supply (min, max) constraints onthis diagram. State clearly any additional assumptions you make. Under what conditionswould the relationship HAS_PHONE be redundant in this example?Answer:Assuming the following additional assumptions:-Each department can have anywhere between 1 and 10 employees.-Each phone is used by one, and only one, department.-Each phone is assigned to at least one, and may be assigned to up to 10 employees.-Each employee is assigned at least one, but no more than 6 phones.The resulting ER Diagram will have the (min, max) constraints shown in Figure A.Relationship HAS-PHONE would be redundant under the following conditions:-Each employee is assigned all of the phones of each department that he/she works in.

Solution Manual for Fundamentals of Database Systems , 7th Edition - Page 15 preview image

Loading page ...

Chapter3: Data Modeling Using the Entity-Relationship (ER) Model9-An employee cannot have any other phones outside the departments he/she works is.EMPLOYEEPHONEDEPARTMENTHAS-PHONE CONTAINSWORKS-IN(0, 2)(1, 3)(1, 10)(1, 10) (1, 1)(1, 6)3.25-Consider the ER diagram in Figure 7.23. Assume that a course may or may not use atextbook, but that a text by definition is a book that is used in some course. A course may notuse more than five books. Instructors teach from two to four courses. Supply (min, max)constraints on this diagram. State clearly any additional assumptions you make. If we addthe relationship ADOPTS between INSTRUCTOR and TEXT, what (min, max) constraintswould you put on it? Why?Answer:Assuming the following additional assumptions:-Each course is taught by exactly one instructor.-Each textbook is used by one and only one course.-An instructor does not have to adopt a textbook for all courses.-If a text exists:-___it is used in some course,-___hence it is adopted by some instructor who teaches that course.-An instructor is considered to adopt a text if it is used in some course taught-by that instructor.The resulting ER Diagram will have the (min, max) constraints shown in Figure B.3.26-Consider an entity typeSECTIONin aUNIVERSITYdatabase, which describes thesection offerings of courses. The attributes ofSECTIONare SectionNumber, Semester,Year, CourseNumber, Instructor, RoomNo (where section is taught), Building (where sectionis taught), Weekdays (domain is the possible combinations of weekdays in which a sectioncan be offered {MWF, MW, TT, etc.}). Assume tat SectionNumber is unique for each course

Solution Manual for Fundamentals of Database Systems , 7th Edition - Page 16 preview image

Loading page ...

Chapter3: Data Modeling Using the Entity-Relationship (ER) Model10within a particular semester/year combination (that is, if a course if offered multiple timesduring a particular semester, its section offerings are numbered 1, 2, 3, etc.). There areseveral composite keys forSECTION, and some attribute sare components of more than onekey. Identify three composite keys, and show how they can be represented in an ER schemadiagram.Answer:3.27-Cardinality ratios often dictate the detailed design of a database. The cardinality ratiodepends on the real-world meaning of the entity types involved and is defined by the specificapplication. For the binary relationships below, suggest cardinality ratios based on common-sense meaning of the entity types. Clearly state any assumptions you make.Entity 1Cardinality RatioEntity 21.StudentSocialSecurityCard2.StudentTeacher3.ClassRoomWall4.CountryCurrentPresident5.CourseTextBook6.Item (that can befound in an order)Order

Solution Manual for Fundamentals of Database Systems , 7th Edition - Page 17 preview image

Loading page ...

Chapter3: Data Modeling Using the Entity-Relationship (ER) Model117.StudentClass8.ClassInstructor9.InstructorOffice10.E-bay AuctionitemE-bay bidAnswer:Entity 1Cardinality RatioEntity 21.Student1-manyA student may have morethan one social securitycard (legally with thesame unique socialsecurity number), andevery social securitynumber belongs to aunique student.SocialSecurityCard2.StudentMany-manyGenerally students aretaught by many teachersand a teacher teachesmany students.Teacher3.ClassRoomMany-manyDon’t forget that the wallis usually shared byadjacent rooms.Wall4.Country1-1Assuming a normalcountry under normalcircumstances having onepresident at a time.CurrentPresident5.CourseMany-manyA course may have manytextbooks and a text bookmay be prescribed fordifferent courses.TextBook

Solution Manual for Fundamentals of Database Systems , 7th Edition - Page 18 preview image

Loading page ...

Chapter3: Data Modeling Using the Entity-Relationship (ER) Model126.Item (that can befound in an order)Many-manyAssuming the same itemcan appear in differentorders.Order7.StudentMany-manyOne student may takeseveral classes. Everyclass usually has severalstudents.Class8.ClassMany-to-1Assuming that every classhas a unique instructor. Incase instructors wereallowed to team teach,this will be many-many.Instructor9.Instructor1-1Assuming every instructorhas only one office and itis not shared. In case ofoffices shared by 2instructors, therelationship will be 2-1.Conversely, if aninstructor has a jointappointment (in twodepartments) and officesin both departments, thenthe relationship will be 1-2. In a very general case,it may be many-many.Office10.E-bay Auctionitem1-many1 item has many bids anda bid is unique to an item(assuming a regularauction format).E-bay bid3.28-3.29–No solutions provided.3.30-Illustrate the UML Diagram for exercise 7.16. Your UML design should observe thefollowing requirements:a.The student should have the ability to compute his/her GPA and add or drop majorsand minors.

Solution Manual for Fundamentals of Database Systems , 7th Edition - Page 19 preview image

Loading page ...

Chapter3: Data Modeling Using the Entity-Relationship (ER) Model13b.Each department should be to able add or delete courses and hire or terminatefaculty.c.Each instructor should be able to assign or change a grade to a student for a course.Note:Some of these functions may be spread over multiple classes.Answer:COURSECourseNameDescriptionCourseNumber: {Course}SemesterHoursLevelDepartmentSECTIONInstructorSemesterYearCourseNumberSectionNumber: {1,2,…}GRADE REPORTStudentNumberSectionNumberLetterGrade: {A,B,C,D,F}NumberGrade: {0,1,2,3,4}PREREQUISITECourseNumberPrerequisiteNumberSTUDENTName:<Name>StudentNumberSocialSecNumberCurrentAddress:<Address>CurrentPhone:<Phone>PermanentAddress:<Address>PermanentPhone:<Phone>Birthdate: DateSex: {M,F}Class: {F,So,J,Se,G}MajorDepartmentMinorDepartmentDegree: {BA,BS,…}compute_gpaadd_majordrop_majoradd_minordrop_minorDEPARTMENTName: {CS,…}DepartmentCode: {Codes}OfficeNumberOfficePhone:<Phone>College: {Colleges}add_coursedelete_coursehire_facultyfire_facultyINSTRUCTORName:<Name>Phone:<Phone>DepartmentCodeassign_gradechange_grade

Solution Manual for Fundamentals of Database Systems , 7th Edition - Page 20 preview image

Loading page ...

Chapter3: Data Modeling Using the Entity-Relationship (ER) Model14This one isn’t finished yet. The relationships need to be added and some of the functionsshould be moved to span classes.

Solution Manual for Fundamentals of Database Systems , 7th Edition - Page 21 preview image

Loading page ...

Chapter4: The Enhanced Entity-Relationship (EER) Model1CHAPTER4: THE ENHANCED ENTITY-RELATIONSHIP (EER) MODELAnswers to Selected Exercises4.16–No solution provided.4.17-Consider the BANK ER schema of Figure 7.21, and suppose that it is necessary tokeep track of different types of ACCOUNTS (SAVINGS_ACCTS, CHECKING_ACCTS,...) and LOANS (CAR_LOANS, HOME_LOANS, ...). Suppose that it is also desirable to keeptrack of each account's TRANSACTIONs (deposits, withdrawals, checks, ...) and each loan'sPAYMENTs; both of these include the amount, date, time, ... Modify the BANK schema,using ER and EER concepts of specialization and generalization. State any assumptions youmake about the additional requirements.Answer:4.18-No solution provided.4.19-Identify all the important concepts represented in the library database case studydescribed here. In particular, identify the abstraction of classification (entity types andrelationship types), aggregation, identification, and specialization/generalization. Specify(min, max) cardinality constraints whenever possible. List details that will affect the eventualdesign but which have no bearing on the conceptual design. List the semantic separately.Draw an EER diagram of the library database.Answer:

Solution Manual for Fundamentals of Database Systems , 7th Edition - Page 22 preview image

Loading page ...

Chapter4: The Enhanced Entity-Relationship (EER) Model24.20-No solution provided.4.21-Figure 8.12 shows an example of an EER diagram for a small private airport databasethat is used to keep track of airplanes, their owners, airport employees, and pilots. From therequirements for this database, the following information was collected: Each airplane has aregistration number [Reg#], is of a particular plane type [of_type], and is stored in a particularhangar [stored_in]. Each plane_type has a model number [Model], a capacity [Capacity], anda weight [Weight]. Each hangar has a number [Number], a capacity [Capacity], and alocation [Location]. The database also keeps track of the owners of each plane [owns] andthe employees who have maintained the plane [maintain]. Each relationship instance in ownsrelates an airplane to an owner and includes the purchase date [Pdate]. Each relationshipinstance in maintain relates to an employee to a service record [service]. Each planeundergoes service many times; hence, it is related by [plane_service] o a number of servicerecords. A service record includes as attributesthe date of maintenance [Date], the numberof hours spent on the work [Hours], and the type of work done [Workcode]. We use a weakentity type [service] to represent airplane service, because the airplane registration numberis used to identify a service record. An owner is either a person or a corporation. Hence, weuse a union type (category) [owner] that is a subset of the union of corporation [Corporation]and person [Person] entity types. Both pilots {Pilot] and employees [Employee] aresubclasses of person. Each pilot has specific attributes license number [Lic_Num] andrestrictions [Restr]; each employee has specific attributes salary [Salary] and shift {Shift]. Allperson entities in the database have data kept on their social security number [Ssn], name

Solution Manual for Fundamentals of Database Systems , 7th Edition - Page 23 preview image

Loading page ...

Chapter4: The Enhanced Entity-Relationship (EER) Model3[Name], address [Address], and telephone number [Phone]. For corporation entities, the datakept includes name [Name], address [Address], and telephone number [Phone]. Thedatabase also keeps track of the types of planes each pilot is authorized to fly [Flies] and thetypes of planes each employee can do maintenance work on [Works_on]. Show how thesmall airport EER schema of Figure 8.12 may be represented in UML notation. (Note: Wehave not discussed how to represent categories (union types) in UML, so you do not have tomap the categories in this and the following question.)Answer:4.22–4.23–No solutions provided.4.24-Draw a UML diagram for storing a played game of chess in a database. You may lookat http://www.chessgames.com for an application similar to what you are designing. Stateclearly any assumptions you make in your UML diagram. A sample of assumptions you can

Solution Manual for Fundamentals of Database Systems , 7th Edition - Page 24 preview image

Loading page ...

Chapter4: The Enhanced Entity-Relationship (EER) Model4make about the scope is a follows:1.The game of chess is played between two players.2.The game is played on an 8x8 board like the one shown below:3.The players are assigned a color of black or white at the start of the game.4.Each player starts with the following pieces (traditionally called chessmen):a.1 kingb.1 queenc.2 rooksd.2 bishopse.2 knightsf.8 pawns5.Every piece has its own initial position.6.Every piece has its own set of legal moves based on the state of the game. You do notneed to worry about which moves are legal and which are not other than the followingissues:a.A piece may move to an empty square or capture an opposing piece.b.If a piece is captured, it is removed from the board.c.If a pawn moves to the last row, it is “promoted” but converting it into anotherpiece (a queen, a rook, a bishop, or a knight).Note:Some of these functions may be spread over multiple classes.4.25–No solution provided.4.26-Which of the following EER diagram(s) is/are incorrect and why? State clearly anyassumptions you make.a)1NRAEE3BoE1E2

Solution Manual for Fundamentals of Database Systems , 7th Edition - Page 25 preview image

Loading page ...

Chapter4: The Enhanced Entity-Relationship (EER) Model5Answer:Only (c) is incorrect.4.27-Consider the following EER diagram that describes computer systems at a company.Provide your own attributes and key for each entity type. Supply max cardinality constraintsjustifying your choice. Write a complete narrative description of what this EER diagramrepresents.NM11b)AEc)RAE1E2CE3dE1oRComputerLaptopDesktopAccessoryKeyboardMonitorMouseSoftwareOperatingSystemComponentMemorySound cardVideo carddddUUUUUUUUUSoldWithOptionsOptionsSupportsInstalledInstalledOS

Solution Manual for Fundamentals of Database Systems , 7th Edition - Page 26 preview image

Loading page ...

Chapter5: The Relational Data Model and Relational Database Constraints1CHAPTER5: THE RELATIONAL DATA MODEL AND RELATIONAL DATABASECONSTRAINTSAnswers to Selected Exercises5.11-Suppose each of the following Update operations is applied directly to the database ofFigure 3.6. Discussallintegrity constraints violated by each operation, if any, and thedifferent ways of enforcing these constraints:(a) Insert < 'Robert', 'F', 'Scott', '943775543', '21-JUN-42', '2365 Newcastle Rd,Bellaire, TX', M, 58000, '888665555', 1 > into EMPLOYEE.(b) Insert < 'ProductA', 4, 'Bellaire', 2 > into PROJECT.(c) Insert < 'Production', 4, '943775543', '01-OCT-88' > into DEPARTMENT.(d) Insert < '677678989', null, '40.0' > into WORKS_ON.(e) Insert < '453453453', 'John', M, '12-DEC-60', 'SPOUSE' > into DEPENDENT.(f) Delete the WORKS_ON tuples with ESSN= '333445555'.(g) Delete the EMPLOYEE tuple with SSN= '987654321'.(h) Delete the PROJECT tuple with PNAME= 'ProductX'.(i) Modify the MGRSSN and MGRSTARTDATE of the DEPARTMENT tuple withDNUMBER=5 to '123456789' and '01-OCT-88', respectively.(j) Modify the SUPERSSN attribute of the EMPLOYEE tuple with SSN= '999887777' to'943775543'.(k) Modify the HOURS attribute of the WORKS_ON tuple with ESSN= '999887777' andPNO= 10 to '5.0'.Answers:(a) No constraint violations.(b) Violates referential integrity because DNUM=2 and there is no tuple in theDEPARTMENT relation with DNUMBER=2. We may enforce the constraint by: (i) rejectingthe insertion of the new PROJECT tuple, (ii) changing the value of DNUM in the newPROJECT tuple to an existing DNUMBER value in the DEPARTMENT relation, or (iii)inserting a new DEPARTMENT tuple with DNUMBER=2.(c) Violates both the key constraint and referential integrity. Violates the key constraintbecause there already exists a DEPARTMENT tuple with DNUMBER=4. We may enforcethis constraint by: (i) rejecting the insertion, or (ii) changing the value of DNUMBERin thenew DEPARTMENT tuple to a value that does not violate the key constraint. Violatesreferential integrity because MGRSSN='943775543' and there is no tuple in theEMPLOYEErelation with SSN='943775543'. We may enforce the constraint by: (i)rejecting the insertion,(ii) changing the value of MGRSSN to an existing SSN value inEMPLOYEE, or (iii) insertinga new EMPLOYEE tuple with SSN='943775543'.

Solution Manual for Fundamentals of Database Systems , 7th Edition - Page 27 preview image

Loading page ...

Chapter5: The Relational Data Model and Relational Database Constraints2(d) Violates both the entity integrity and referential integrity. Violates entity integritybecausePNO, which is part of the primary key of WORKS_ON, is null. We may enforcethis constraintby: (i) rejecting the insertion, or (ii) changing the value of PNO in thenew WORKS_ON tupleto a value of PNUMBER that exists in the PROJECT relation.Violates referential integritybecause ESSN='677678989' and there is no tuple in theEMPLOYEE relation withSSN='677678989'. We may enforce the constraint by: (i)rejecting the insertion, (ii) changingthe value of ESSN to an existing SSN value inEMPLOYEE, or (iii) inserting a newEMPLOYEE tuple with SSN='677678989'.(e) No constraint violations.(f) No constraint violations.(g) Violates referential integrity because several tuples exist in the WORKS_ON,DEPENDENT, DEPARTMENT, and EMPLOYEE relations that reference the tuple beingdeleted from EMPLOYEE. We may enforce the constraint by: (i) rejecting the deletion, or(ii)deleting all tuples in the WORKS_ON, DEPENDENT, DEPARTMENT, and EMPLOYEErelations whose values for ESSN, ESSN, MGRSSN, and SUPERSSN, respectively, is equalto'987654321'.(h) Violates referential integrity because two tuples exist in the WORKS_ON relations thatreference the tuple being deleted from PROJECT. We may enforce the constraint by: (i)rejecting the deletion, or (ii) deleting the tuples in the WORKS_ON relation whose valueforPNO=1 (the value for the primary key PNUMBER for the tuple being deleted fromPROJECT).(i) No constraint violations.(j) Violates referential integrity because the new value of SUPERSSN='943775543' andthereis no tuple in the EMPLOYEE relation with SSN='943775543'. We may enforce theconstraintby: (i) rejecting the deletion, or (ii) inserting a new EMPLOYEE tuple withSSN='943775543'.(k) No constraint violations.5.12-Consider the AIRLINE relational database schema shown in Figure 3.8, whichdescribes a database for airline flight information. Each FLIGHT is identified by a flightNUMBER, and consists of one or more FLIGHT_LEGswith LEG_NUMBERs 1, 2, 3, etc.Each leg has scheduled arrival and departure times and airports, and has manyLEG_INSTANCEs--one for each DATE on which the flight travels. FARES are kept for eachflight. For each leg instance, SEAT_RESERVATIONs are kept, as is the AIRPLANE used inthe leg, and the actual arrival and departure times and airports. An AIRPLANE is identifiedby an AIRPLANE_ID, and is of a particularAIRPLANE_TYPE. CAN_LAND relatesAIRPLANE_TYPEs to the AIRPORTs in which they can land. An AIRPORT is identified byan AIRPORT_CODE. Consider an update for the AIRLINEdatabase to enter a reservationon a particular flight or flight leg on a given date.(a) Give the operations for this update.(b) What types of constraints would you expect to check?(c) Which of these constraints are key, entity integrity, and referential integrity constraintsand which are not?

Solution Manual for Fundamentals of Database Systems , 7th Edition - Page 28 preview image

Loading page ...

Chapter5: The Relational Data Model and Relational Database Constraints3(d) Specify all the referential integrity constraints on Figure 3.8.Answers:(a) One possible set of operations for the following update is the following:INSERT <FNO,LNO,DT,SEAT_NO,CUST_NAME,CUST_PHONE> intoSEAT_RESERVATION; MODIFY the LEG_INSTANCE tuple with the condition:( FLIGHT_NUMBER=FNO AND LEG_NUMBER=LNO AND DATE=DT) by settingNUMBER_OF_AVAILABLE_SEATS = NUMBER_OF_AVAILABLE_SEATS-1; Theseoperations should be repeated for each LEG of the flight on which a reservation is made.This assumes that the reservation has only one seat. More complex operations will beneeded for a more realistic reservation that may reserve several seats at once.(b) We would check that NUMBER_OF_AVAILABLE_SEATS on each LEG_INSTANCE ofthe flight is greater than 1 before doing any reservation (unless overbooking is permitted),and that the SEAT_NUMBER being reserved in SEAT_RESERVATION is available.(c) The INSERT operation into SEAT_RESERVATION will check all the key, entity integrity,and referential integrity constraints for the relation. The check thatNUMBER_OF_AVAILABLE_SEATS on each LEG_INSTANCE of the flight is greater than 1does not fall into any of the above types of constraints (it is a general semantic integrityconstraint).(d) We will write a referential integrity constraint as R.A--> S (or R.(X)--> T)wheneverattribute A (or the set of attributes X) of relation R form a foreign key thatreferences theprimary key of relation S (or T). FLIGHT_LEG.FLIGHT_NUMBER--> FLIGHTFLIGHT_LEG.DEPARTURE_AIRPORT_CODE--> AIRPORTFLIGHT_LEG.ARRIVAL_AIRPORT_CODE--> AIRPORTLEG_INSTANCE.(FLIGHT_NUMBER,LEG_NUMBER)--> FLIGHT_LEGLEG_INSTANCE.DEPARTURE_AIRPORT_CODE--> AIRPORTLEG_INSTANCE.ARRIVAL_AIRPORT_CODE--> AIRPORTLEG_INSTANCE.AIRPLANE_ID--> AIRPLANEFARES.FLIGHT_NUMBER--> FLIGHTCAN_LAND.AIRPLANE_TYPE_NAME--> AIRPLANE_TYPECAN_LAND.AIRPORT_CODE--> AIRPORTAIRPLANE.AIRPLANE_TYPE--> AIRPLANE_TYPESEAT_RESERVATION.(FLIGHT_NUMBER,LEG_NUMBER,DATE)--> LEG_INSTANCE5.13-Consider the relation CLASS(Course#, Univ_Section#, InstructorName, Semester,BuildingCode, Room#, TimePeriod, Weekdays, CreditHours). This represents classes taughtin a university with unique Univ_Section#. Give what you think should be various candidatekeys and write in your own words under what constraints each candidate key would be valid.Answer:Possible candidate keys include the following (Note: We assume that the values of theSemester attribute include the year; for example "Spring/94" or "Fall/93" could bevalues forSemester):1. {Semester, BuildingCode, Room#, TimePeriod, Weekdays} if the same room cannotbeused at the same time by more than one course during a particular semester.2. {Univ_Section#} if it is unique across all semesters.3. {InstructorName, Semester} if an instructor can teach at most one course during eachsemester.4. If Univ_Section# is not unique, which is the case in many universities, we have toexamine the rules that the university uses for section numbering. For example, if the

Solution Manual for Fundamentals of Database Systems , 7th Edition - Page 29 preview image

Loading page ...

Chapter5: The Relational Data Model and Relational Database Constraints4sections of a particular course during a particular semester are numbered 1, 2, 3, …thena candidate key would be {Course#, Univ_Section#, Semester}. If, on the otherhand, allsections (of any course) have unique numbers during a particular semesteronly, then thecandidate key would be {Univ_Section#, Semester}.5.14-Consider the following six relations for an order-processing database application in acompany:CUSTOMER (Cust#, Cname, City)ORDER (Order#, Odate, Cust#, Ord_Amt)ORDER_ITEM (Order#,Item#, Qty)ITEM (Item#, Unit_price)SHIPMENT (Order#,Warehouse#, Ship_date)WAREHOUSE (Warehouse#, City)Here, Ord_Amt refers to total dollar amount of an order; Odate is the date the order wasplaced; Ship_date is the date an order (or part of an order) is shipped from the warehouse.Assume that an order can be shipped from several warehouses. Specify the foreign keys forthis schema, stating any assumptions you make. What other constraints can you think of forthis database?Answer:Strictly speaking, a foreign key is asetof attributes, but when that set contains only oneattribute, then that attribute itself is often informally called a foreign key. The schema ofthisquestion has the following five foreign keys:1. the attribute Cust# of relation ORDER that references relationCUSTOMER,2. the attribute Order# of relationORDER_ITEMthat references relationORDER,3. the attribute Item# of relationORDER_ITEMthat references relationITEM,4. the attribute Order# of relationSHIPMENTthat references relationORDER,and5. the attribute Warehouse# of relationSHIPMENTthat references relationWAREHOUSE.We now give the queries in relational algebra:

Solution Manual for Fundamentals of Database Systems , 7th Edition - Page 30 preview image

Loading page ...

Chapter5: The Relational Data Model and Relational Database Constraints5The above query lists all orders for which no “timely” shipment was made, includingordersfor which no shipment was ever made. It is instructive to compare the abovequery with theone below that lists those orders for which at least one “late” shipmentwas made.5.15-Consider the following relations for a database that keeps track of business trips ofsalespersons in a sales office:SALESPERSON (SSN, Name, Start_Year, Dept_No)TRIP (SSN, From_City, To_City, Departure_Date, Return_Date,Trip_ID)EXPENSE (Trip_ID,Account#, Amount)Specify the foreign keys for this schema, stating any assumptions you make.Answer:The schema of this question has the following two foreign keys:1. the attribute SSN of relation TRIP that references relationSALESPERSON,and2. the attribute Trip_ID of relationEXPENSE that references relationTRIP.In addition, the attributes Dept_No of relation SALESPERSON and Account# of relationEXPENSE are probably also foreign keys referencing other relations of the database notmentioned in the question. We now give the queries in relational algebra:5.16-Consider the following relations for a database that keeps track of student enrollmentin courses and the books adopted for each course:STUDENT (SSN, Name, Major, Bdate)COURSE (Course#,Quarter, Grade)ENROLL (SSN,Course#,Quarter, Grade)BOOK_ADOPTION (Course#,Quarter, Book_ISBN)TEXT (Book_ISBN, Book_Title, Publisher, Author)Specify the foreign keys for this schema, stating any assumptions you make.

Solution Manual for Fundamentals of Database Systems , 7th Edition - Page 31 preview image

Loading page ...

Chapter5: The Relational Data Model and Relational Database Constraints6Answer:The schema of this question has the following four foreign keys:3. the attribute SSN of relationENROLLthat references relationSTUDENT,4. the attribute Course# in relationENROLLthat references relation COURSE,5. the attribute Course# in relationBOOK_ADOPTIONthat references relation COURSE, and6. the attribute Book_ISBN of relationBOOK_ADOPTIONthat references relation TEXT.We now give the queries in relational algebra:5.18-Database design often involves decisions about the storage of attributes. For examplea Social Security Number can be stored as a one attribute or split into three attributes (onefor each of the three hyphen-deliniated groups of numbers in a Social Security Number—XXX-XX-XXXX). However, Social Security Number is usually stored in one attribute. Thedecision is usually based on how the database will be used. This exercise asks you to thinkabout specific situations where dividing the SSN is useful.Answer:a.We need the area code (also know as city code in some countries) and perhaps thecountry code (for dialing international phone numbers).b.I would recommend storing the numbers in a separate attribute as they have their ownindependent existence. For example, if an area code region were split into two regions, itwould change the area code associated with certain numbers, and having area code in aseparate attribute will make it is easier to update the area code attribute by itself.c.I would recommend splitting first name, middle name, and last name into differentattributes as it is likely that the names may be sorted and/or retrieved by the last name, etc.d.In general, if the each attribute has an independent logical existence based on the

Preview Mode

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

Report

Study Now!

Document Details

Related Documents

Solution Manual For Fundamentals Of Database Systems, 6th Edition

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

Company

Explore

Study Tools