Skip to content
Textbook Guides

Solution Manual for Fundamentals of Database Systems , 7th Edition

Solution Manual for Fundamentals of Database Systems , 7th Edition gives you the answers you need, explained in a simple and clear way.

Preview 31 of 150 pages

See the first 31 pages exactly as they appear in the document.

  1. Page 1
    Read page 1 as text

    Chapter 1: Databases and Database Users 1 CHAPTER 1: DATABASES AND DATABASE USERS Answers to Selected Exercises 1.8 - Identify some informal queries and update operations that you would expect to apply to the 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 has completed. (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 section 119 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 is Brown is stored multiple times. Redundancy is controlled when the DBMS ensures that multiple copies of the same data are consistent; for example, if a new record with StudentNumber=8 is stored in the database of Figure 1.5(a), the DBMS will ensure that StudentName=Smith in that record. If the DBMS has no control over this, we have uncontrolled 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 SECTION record. (c) Each PREREQUISITE record relates two COURSE records: one in the role of a course and 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 database shown in Figure 1.2. Answer: (a) A view that groups all the students who took each section and gives each student's grade. This may be useful for printing the grade report for each section for the university administration's use.

  2. Page 2
  3. Page 3
    Read page 3 as text

    Chapter 1: Databases and Database Users 2 (b) A view that gives the number of courses taken and the GPA (grade point average) for each student. This may be used to determine honors students. 1.12 – Cite some examples of integrity constraints that you think can apply to the database shown in Figure 1.2. Answer: We give a few constraints expressed in English. Following each constraint, we give its type in the relational database terminology that will be covered in Chapter 6, for reference purposes. (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 COURSE record (referential integrity constraint). (d) A value of StudentNumber in a GRADE_REPORT record must also exist in some STUDENT 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 integrity constraint). (g) A STUDENT record cannot have a value of Class=2 (sophomore) unless the student has completed a number of sections whose total course CreditHours is greater tha n 24 credits (general semantic integrity constraint). 1.13 - Give examples of systems in which it may make sense to use traditional file processing instead of a database approach. Answer: 1. Small internal utility to locate files 2. Small single user application that does not require security (such as a customized calculator 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’ (Computer Science and Software Engineering) Department and the corresponding prefix for the course number also changes, identify the columns in the database that would need to be updated. b. Can you restructure the columns in COURSE, SECTION, and PREREQUISITE tables so that only one column will need to be updated? Answer:

  4. Page 4
    Read page 4 as text

    Chapter 1: Databases and Database Users 3 a. The following columns will need to be updated. Table Column(s) STUDENT Major COURSE CourseNumber and Department SECTION CourseNumber PREREQUISITE CourseNumber and PrerequisiteNumber b. You should split the following columns into two columns: Table Column Split Columns COURSE CourseNumber CourseDept and CourseNum SECTION CourseNumber CourseDept and CourseNum PREREQUISITE CourseNumber CourseDept and CourseNum PREREQUISITE PrerequisiteNumber PreReqDept and PreReqNum Note that in the COURSE table, the column CourseDept will not be needed after the above change, since it is redundant with the Department column.

  5. Page 5
    Read page 5 as text

    Chapter 2: Database System Concepts and Architecture 1 CHAPTER 2: DATABASE SYSTEM CONCEPTS AND ARCHITECTURE Answers to Selected Exercises 2.12 - Think of different users for the database of Figure 1.2. What type of applications would each user need? To which user category would each belong and what type of interface would they need? Answer: (a) Registration Office User: They can enter data that reflect the registration of students in sections of courses, and later enter the grades of the students. Applications can include: - Register a student in a section of a course - Check whether a student who is registered in a course has the appropriate prerequisite courses - Drop a student from a section of a course - Add a student to a section of a course - Enter the student grades for a section Application programmers can write a number of canned transactions for the registration office end - users, providing them with either forms and menus, or with a parametric interface. (b) Admissions Office User: The main application is to enter newly accepted students into the database. 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 utility to print the transcript of a student in a prescribed format. The particular student can be identified by name or social security number. Another application would be to generate grade slips at the end of each semester for all students who have completed courses during that semester. Again, this application could be programmed using a report generator utility. 2.13 - No solution provided. 2.14 - if you were designing a Web - based system to make airline reservations and to sell airline tickets, which DBMS Architecture would you choose from Section 2.5? Why? Why would 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 Client consists of Web User Interface. The Web Server contains the application logic which includes all the rules and regulations related to the reservation process and the issue of tickets; the Database Server contains the DBMS. 2.5.1 Centralized DBMS Architecture would not work since the user interface and database server 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 would work if the Business Logic can reside on server other than the DBMS Server. In general, if the business logic was on the DBMS Server, it will put an excessive burden on the server. If the business logic were to reside on the web client, it will burden the communication network as well a possibly thin client. 2.15 - Consider Figure 2.1. In addition to constraints relating the values of columns in one table to columns in another table, there are also constraints that impose restrictions on

  6. Page 6
    Read page 6 as text

    Chapter 2: Database System Concepts and Architecture 2 values in a column or a combination of columns within a table. One such constraint forces that a column or a group of columns must be unique across all rows in the table. For example, in the STUDENT table, the StudentNumber column must be unique (to prevent two different students from having the same StudentNumber). Identify the column or the group of columns in the other tables that must be unique across all rows in the table? Answer: Table Column(s) COURSE CourseNumber Since this contains the combination of the department and the number that must be unique within the department. Note we will overlook the fact this does not accommodate a department from offering several “Special Topics” course with the same CourseNumber but different titles. We could make this a combination of CourseNumber and CourseName, but this is more succeptible to someone mistyping while entering data. PREREQUISITE The combination of CourseNumber and PrerequisiteNumber SECTION SectionIdentifier We assume that no two sections can have the same SectionIdentifier. If we were to consider that SectionIdentifier is unique only within a given 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_REPORT StudentNumber and SectionIdentifier As per assumption stated in SECTION, the SectionIdentifier will be different if a student takes the same course or a different course in another term.

  7. Page 7
    Read page 7 as text

    Chapter 3 : Data Modeling Using the Entity - Relationship (ER) Model 1 CHAPTER 3 : DATA MODELING USING THE ENTITY - RELATIONSHIP (ER) MODEL Answers to Selected Exercises 3 .16 - Consider the following set of requirements for a UNIVERSITY database that is used to keep track of students' transcripts. This is similar but not identical to the database shown in Figure 1.2: (a) The university keeps track of each student's name, student number, social security number, current address and phone, permanent address and phone, birthdate, sex, class (freshman, sophomore, ..., graduate), major department, minor department (if any), and degree 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. Both social 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 section number distinguishes different sections of the same course that are taught during the same semester/year; its values are 1, 2, 3, ..., up to the number of sections taught during each semester. (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 the specification complete. Answer:

  8. Page 8
    Read page 8 as text

    Chapter 3 : Data Modeling Using the Entity - Relationship (ER) Model 2 3 .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 previous college 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 (degrees awarded at that college, if any), and transcript entries (courses completed at that college, if any). 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 an attribute 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) } ) }

  9. Page 9
    Read page 9 as text

    Chapter 3 : Data Modeling Using the Entity - Relationship (ER) Model 3 3.18 - Show an alternative design for the attribute described in Exercise 7.17 that uses only entity 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 the database designer decide what to model as an entity type and what to model as a relationship 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 particular COLLEGE, and is identified by the STUDENT and the StartDate of the period. Hence, the StartDate attribute is the partial key of ATTENDANCE. Each ATTENDANCE entity is related to one COLLEGE and zero or more DEGREEs (the degrees awarded during that attendance period). The TRANSCRIPT of the STUDENT during each attendance period is modeled as a weak 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 the attendance period, as shown in the ER diagram below. Other ER schema designs are also possible for this problem.

  10. Page 10
    Read page 10 as text

    Chapter 3 : Data Modeling Using the Entity - Relationship (ER) Model 4 3 .19 - Consider the ER diagram of Figure 7.20, which shows a simplified schema for an airline reservations system. Extract from the ER diagram the requirements and constraints that resulted in this schema. Try to be as precise as possible in your requirements and constraints specification. Answer: (1) The database represents each AIRPORT, keeping its unique AirportCode, the AIRPORT Name, 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 Weekdays on which the FLIGHT is scheduled (for example, every day of the week except Sunday can be coded as X7). (3) A FLIGHT is composed of one or more FLIGHT LEGs (for example, flight number CO1223 from New York to Los Angeles may have two FLIGHT LEGs: leg 1 from New York to Houston and leg 2 from Houston to Los Angeles). Each FLIGHT LEG has a DEPARTURE AIRPORT and Scheduled Departure Time, and an ARRIVAL AIRPORT and Scheduled Arrival 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 and Times are recorded for each flight leg after the flight leg has been concluded. The Number 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.

  11. Page 11
    Read page 11 as text

    Chapter 3 : Data Modeling Using the Entity - Relationship (ER) Model 5 (6) Information on AIRPLANEs and AIRPLANE TYPEs are also kept. For each AIRPLANE TYPE (for example, DC - 10), the TypeName, manufacturing Company, and Maximum Number of Seats are kept. The AIRPORTs in which planes of this type CAN LAND are kept in the database. For each AIRPLANE, the AirplaneId, Total number of seats, 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 sports league. 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, the positions they played in that game, and the result of the game. Try to design an ER schema diagram 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 game in the schedule is identified by a unique Game#, and a game is also identified uniquely by the combination of Date, starting Time, and Field where it is played. The Performance attribute of PARTICIPATE is used to store information on the individual performance of each

  12. Page 12
    Read page 12 as text

    Chapter 3 : Data Modeling Using the Entity - Relationship (ER) Model 6 player in a game. This attribute can be designed to keep the information needed for statistics, and may be quite complex. One possible design for the Performance attribute may be 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 records the HitType (suitable coded; for example, 1 for single, 2 for double, 3 for triple, 4 for home run, 0 for walk, - 1 for strikeout, - 2 for fly out, ...) and other information concerning the AtBat. Pitching has a value for each inning during which the player pitched. Defense has a value for each inning a player played a fielding position. We can have a less detailed or a more detailed design for the performance of a player in each game, depending on how much information we need to keep in the database. Suitable variations of the ER diagram shown below can be used for other sports. 3 .23 - Consider the ER diagram shown in Figure 7.21 for part of a BANK database. Each bank 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 identifying relationship. (c) What constraints do the partial key and the identifying relationship of the weak entity type specify in this diagram?

  13. Page 13
    Read page 13 as text

    Chapter 3 : Data Modeling Using the Entity - Relationship (ER) Model 7 (d) List the names of all relationship types, and specify the (min,max) constraint on each participation 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 restricted to at most two loans at a time, and that a bank branch cannot have more than 1000 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 ay occur under different BANKs. The identifying relationship BRANCHES specifies that

  14. Page 14
    Read page 14 as text

    Chapter 3 : Data Modeling Using the Entity - Relationship (ER) Model 8 BranchNo values are uniquely assigned for those BANK - BRANCH entities that are related to the same BANK entity. Hence, the combination of BANK Code and BranchNo together constitute a full identifier for a BANK - BRANCH. (d) Relationship Types: BRANCHES, ACCTS, LOANS, A - C, L - C. The (min, max) constraints are shown below. (e) The requirements may be stated as follows: Each BANK has a unique Code, as well as a Name and Address. Each BANK is related to one or more BANK - BRANCHes, and the BranhNo 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 and zero or more ACCTS. Each ACCOUNT has an AcctNo (unique), Balance, and Type and is related to exactly one BANK - BRANCH and to at least one CUSTOMER. Each LOAN has a LoanNo (unique), Amount, and Type and is related to exactly one BANK - BRANCH and to at least one CUSTOMER. Each CUSTOMER has an SSN (unique), Name, Phone, and Address, 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 to two departments or may not be assigned to any department. Assume that each department must have one and may have up to three phone numbers. Supply (min, max) constraints on this diagram. State clearly any additional assumptions you make. Under what conditions would 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.

  15. Page 15
    Read page 15 as text

    Chapter 3 : Data Modeling Using the Entity - Relationship (ER) Model 9 - An employee cannot have any other phones outside the departments he/she works is. EMPLOYEE PHONE DEPARTMENT HAS - PHONE CONTAINS WORKS - 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 a textbook, but that a text by definition is a book that is used in some course. A course may not use 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 add the relationship ADOPTS between INSTRUCTOR and TEXT, what (min, max) constraints would 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 type SECTION in a UNIVERSITY database, which describes the section offerings of courses. The attributes of SECTION are SectionNumber, Semester, Year, CourseNumber, Instructor, RoomNo (where section is taught), Building (where section is taught), Weekdays (domain is the possible combinations of weekdays in which a section can be offered {MWF, MW, TT, etc.}). Assume tat SectionNumber is unique for each course

  16. Page 16
    Read page 16 as text

    Chapter 3 : Data Modeling Using the Entity - Relationship (ER) Model 10 within a particular semester/year combination (that is, if a course if offered multiple times during a particular semester, its section offerings are numbered 1, 2, 3, etc.). There are several composite keys for SECTION , and some attribute sare components of more than one key. Identify three composite keys, and show how they can be represented in an ER schema diagram. Answer: 3 .27 - Cardinality ratios often dictate the detailed design of a database. The cardinality ratio depends on the real - world meaning of the entity types involved and is defined by the specific application. For the binary relationships below, suggest cardinality ratios based on common - sense meaning of the entity types. Clearly state any assumptions you make. Entity 1 Cardinality Ratio Entity 2 1. Student SocialSecurityCard 2. Student Teacher 3. ClassRoom Wall 4. Country CurrentPresident 5. Course TextBook 6. Item (that can be found in an order) Order

  17. Page 17
    Read page 17 as text

    Chapter 3 : Data Modeling Using the Entity - Relationship (ER) Model 11 7. Student Class 8. Class Instructor 9. Instructor Office 10. E - bay Auction item E - bay bid Answer: Entity 1 Cardinality Ratio Entity 2 1. Student 1 - many A student may have more than one social security card (legally with the same unique social security number), and every social security number belongs to a unique student. SocialSecurityCard 2. Student Many - many Generally students are taught by many teachers and a teacher teaches many students. Teacher 3. ClassRoom Many - many Don’t forget that the wall is usually shared by adjacent rooms. Wall 4. Country 1 - 1 Assuming a normal country under normal circumstances having one president at a time. CurrentPresident 5. Course Many - many A course may have many textbooks and a text book may be prescribed for different courses. TextBook

  18. Page 18
    Read page 18 as text

    Chapter 3 : Data Modeling Using the Entity - Relationship (ER) Model 12 6. Item (that can be found in an order) Many - many Assuming the same item can appear in different orders. Order 7. Student Many - many One student may take several classes. Every class usually has several students. Class 8. Class Many - to - 1 Assuming that every class has a unique instructor. In case instructors were allowed to team teach, this will be many - many. Instructor 9. Instructor 1 - 1 Assuming every instructor has only one office and it is not shared. In case of offices shared by 2 instructors, the relationship will be 2 - 1. Conversely, if an instructor has a joint appointment (in two departments) and offices in both departments, then the relationship will be 1 - 2. In a very general case, it may be many - many. Office 10. E - bay Auction item 1 - many 1 item has many bids and a bid is unique to an item (assuming a regular auction format). E - bay bid 3 .28 - 3 .29 – No solutions provided. 3.30 - Illustrate the UML Diagram for exercise 7.16. Your UML design should observe the following requirements: a. The student should have the ability to compute his/her GPA and add or drop majors and minors.

  19. Page 19
    Read page 19 as text

    Chapter 3 : Data Modeling Using the Entity - Relationship (ER) Model 13 b. Each department should be to able add or delete courses and hire or terminate faculty. 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: COURSE CourseName Description CourseNumber: {Course} SemesterHours Level Department SECTION Instructor Semester Year CourseNumber SectionNumber: {1,2,…} GRADE REPORT StudentNumber SectionNumber LetterGrade: {A,B,C,D,F} NumberGrade: {0,1,2,3,4} PREREQUISITE CourseNumber PrerequisiteNumber STUDENT Name: <Name> StudentNumber SocialSecNumber CurrentAddress: <Address> CurrentPhone: <Phone> PermanentAddress: <Address> PermanentPhone: <Phone> Birthdate: Date Sex: {M,F} Class: {F,So,J,Se,G} MajorDepartment MinorDepartment Degree: {BA,BS,…} compute_gpa add_major drop_major add_minor drop_minor DEPARTMENT Name: {CS,…} DepartmentCode: {Codes} OfficeNumber OfficePhone: <Phone> College: {Colleges} add_course delete_course hire_faculty fire_faculty INSTRUCTOR Name: <Name> Phone: <Phone> DepartmentCode assign_grade change_grade

  20. Page 20
    Read page 20 as text

    Chapter 3 : Data Modeling Using the Entity - Relationship (ER) Model 14 This one isn’t finished yet. The relationships need to be added and some of the functions should be moved to span classes.

  21. Page 21
    Read page 21 as text

    Chapter 4 : The Enhanced Entity - Relationship (EER) Model 1 CHAPTER 4 : THE ENHANCED ENTITY - RELATIONSHIP (EER) MODEL Answers to Selected Exercises 4 .16 – No solution provided. 4 .17 - Consider the BANK ER schema of Figure 7.21, and suppose that it is necessary to keep track of different types of ACCOUNTS (SAVINGS_ACCTS, CHECKING_ACCTS, ...) and LOANS (CAR_LOANS, HOME_LOANS, ...). Suppose that it is also desirable to keep track of each account's TRANSACTIONs (deposits, withdrawals, checks, ...) and each loan's PAYMENTs; both of these include the amount, date, time, ... Modify the BANK schema, using ER and EER concepts of specialization and generalization. State any assumptions you make about the additional requirements. Answer: 4 .18 - No solution provided. 4 .19 - Identify all the important concepts represented in the library database case study described here. In particular, identify the abstraction of classification (entity types and relationship types), aggregation, identification, and specialization/generalization. Specify (min, max) cardinality constraints whenever possible. List details that will affect the eventual design but which have no bearing on the conceptual design. List the semantic separately. Draw an EER diagram of the library database. Answer:

  22. Page 22
    Read page 22 as text

    Chapter 4 : The Enhanced Entity - Relationship (EER) Model 2 4 .20 - No solution provided. 4 .21 - Figure 8.12 shows an example of an EER diagram for a small private airport database that is used to keep track of airplanes, their owners, airport employees, and pilots. From the requirements for this database, the following information was collected: Each airplane has a registration number [Reg#], is of a particular plane type [of_type], and is stored in a particular hangar [stored_in]. Each plane_type has a model number [Model], a capacity [Capacity], and a weight [Weight]. Each hangar has a number [Number], a capacity [Capacity], and a location [Location]. The database also keeps track of the owners of each plane [owns] and the employees who have maintained the plane [maintain]. Each relationship instance in owns relates an airplane to an owner and includes the purchase date [Pdate]. Each relationship instance in maintain relates to an employee to a service record [service]. Each plane undergoes service many times; hence, it is related by [plane_service] o a number of service records. A service record includes as a ttributes the date of maintenance [Date], the number of hours spent on the work [Hours], and the type of work done [Workcode]. We use a weak entity type [service] to represent airplane service, because the airplane registration number is used to identify a service record. An owner is either a person or a corporation. Hence, we use 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] are subclasses of person. Each pilot has specific attributes license number [Lic_Num] and restrictions [Restr]; each employee has specific attributes salary [Salary] and shift {Shift]. All person entities in the database have data kept on their social security number [Ssn], name

  23. Page 23
    Read page 23 as text

    Chapter 4 : The Enhanced Entity - Relationship (EER) Model 3 [Name], address [Address], and telephone number [Phone]. For corporation entities, the data kept includes name [Name], address [Address], and telephone number [Phone]. The database also keeps track of the types of planes each pilot is authorized to fly [Flies] and the types of planes each employee can do maintenance work on [Works_on]. Show how the small airport EER schema of Figure 8.12 may be represented in UML notation. (Note: We have not discussed how to represent categories (union types) in UML, so you do not have to map 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 look at http://www.chessgames.com for an application similar to what you are designing. State clearly any assumptions you make in your UML diagram. A sample of assumptions you can

  24. Page 24
    Read page 24 as text

    Chapter 4 : The Enhanced Entity - Relationship (EER) Model 4 make 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 king b. 1 queen c. 2 rooks d. 2 bishops e. 2 knights f. 8 pawns 5. 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 not need to worry about which moves are legal and which are not other than the following issues: 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 another piece (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 any assumptions you make. a) 1 N R A E E3 B o E1 E2

  25. Page 25
    Read page 25 as text

    Chapter 4 : The Enhanced Entity - Relationship (EER) Model 5 Answer: 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 constraints justifying your choice. Write a complete narrative description of what this EER diagram represents. N M 1 1 b) A E c) R A E1 E2 C E3 d E1 o R Computer Laptop Desktop Accessory Keyboard Monitor Mouse Software Operating System Component Memory Sound card Video card d d d U U U U U U U U U SoldWith Options Options Supports Installed InstalledOS

  26. Page 26
    Read page 26 as text

    Chapter 5 : The Relational Data Model and Relational Database Constraints 1 CHAPTER 5 : THE RELATIONAL DATA MODEL AND RELATIONAL DATABASE CONSTRAINTS Answers to Selected Exercises 5 .11 - Suppose each of the following Update operations is applied directly to the database of Figure 3.6. Discuss all integrity constraints violated by each operation, if any, and the different 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 with DNUMBER=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' and PNO= 10 to '5.0'. Answers: (a) No constraint violations. (b) Violates referential integrity because DNUM=2 and there is no tuple in the DEPARTMENT relation with DNUMBER=2. We may enforce the constraint by: (i) rejecting the insertion of the new PROJECT tuple, (ii) changing the value of DNUM in the new PROJECT 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 constraint because there already exists a DEPARTMENT tuple with DNUMBER=4. We may enforce this constraint by: (i) rejecting the insertion, or (ii) changing the value of DNUMBER in the new DEPARTMENT tuple to a value that does not violate the key constraint. Violates referential integrity because MGRSSN='943775543' and there is no tuple in the EMPLOYEE relation with SSN='943775543'. We may enforce the constraint by: (i) rejecting the insertion, (ii) changing the value of MGRSSN to an existing SSN value in EMPLOYEE, or (iii) inserting a new EMPLOYEE tuple with SSN='943775543'.

  27. Page 27
    Read page 27 as text

    Chapter 5 : The Relational Data Model and Relational Database Constraints 2 (d) Violates both the entity integrity and referential integrity. Violates entity integrity because PNO, which is part of the primary key of WORKS_ON, is null. We may enforce this constraint by: (i) rejecting the insertion, or (ii) changing the value of PNO in the new WORKS_ON tuple to a value of PNUMBER that exists in the PROJECT relation. Violates referential integrity because ESSN='677678989' and there is no tuple in the EMPLOYEE relation with SSN='677678989'. We may enforce the constraint by: (i) rejecting the insertion, (ii) changing the value of ESSN to an existing SSN value in EMPLOYEE, or (iii) inserting a new EMPLOYEE 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 being deleted from EMPLOYEE. We may enforce the constraint by: (i) rejecting the deletion, or (ii) deleting all tuples in the WORKS_ON, DEPENDENT, DEPARTMENT, and EMPLOYEE relations whose values for ESSN, ESSN, MGRSSN, and SUPERSSN, respectively, is equal to'987654321'. (h) Violates referential integrity because two tuples exist in the WORKS_ON relations that reference 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 value for PNO=1 (the value for the primary key PNUMBER for the tuple being deleted from PROJECT). (i) No constraint violations. (j) Violates referential integrity because the new value of SUPERSSN='943775543' and there is no tuple in the EMPLOYEE relation with SSN='943775543'. We may enforce the constraint by: (i) rejecting the deletion, or (ii) inserting a new EMPLOYEE tuple with SSN='943775543'. (k) No constraint violations. 5 .12 - Consider the AIRLINE relational database sc hema shown in Figure 3.8, which describes a database for airline flight information. Each F LIGHT is identified by a flight NUMBER, and consists of one or more FLIGHT_LEGs with LEG_NUMBERs 1, 2, 3, etc. Each leg has scheduled arrival and departure t imes and airports, and has many LEG_INSTANCEs -- one for each DATE on which the flight t ravels. FARES are kept for each flight. For each leg instance, SEAT_RESERVATIONs are k ept, as is the AIRPLANE used in the leg, and the actual arrival and departure times and airp orts. An AIRPLANE is identified by an AIRPLANE_ID, and is of a particular AIRPLANE_TYPE. CAN_LAND relates AIRPLANE_TYPEs to the AIRPORTs in which they can la nd. An AIRPORT is identified by an AIRPORT_CODE. Consider an update for the AIRLINE database to enter a reservation on 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 constraints and which are not?

  28. Page 28
    Read page 28 as text

    Chapter 5 : The Relational Data Model and Relational Database Constraints 3 (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> into SEAT_RESERVATION; MODIFY the LEG_INSTANCE tuple with the condition: ( FLIGHT_NUMBER=FNO AND LEG_NUMBER=LNO AND DATE=DT) by setting NUMBER_OF_AVAILABLE_SEATS = NUMBER_OF_AVAILABLE_SEATS - 1; These operations 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 be needed 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 of the 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 f or the relation. The check that NUMBER_OF_AVAILABLE_SEATS on each LEG_INSTANCE of the flight is greater than 1 does not fall into any of the above types of constraints (it is a general semantic integrity constraint). (d) We will write a referential integrity constraint as R.A -- > S (or R.(X) -- > T) whenever attribute A (or the set of attributes X) of relation R form a foreign key that references the primary key of relation S (or T). FLIGHT_LEG.FLIGHT_NUMBER -- > FLIGHT FLIGHT_LEG.DEPARTURE_AIRPORT_CODE -- > AIRPORT FLIGHT_LEG.ARRIVAL_AIRPORT_CODE -- > AIRPORT LEG_INSTANCE.(FLIGHT_NUMBER,LEG_NUMBER) -- > FLIGHT_LEG LEG_INSTANCE.DEPARTURE_AIRPORT_CODE -- > AIRPORT LEG_INSTANCE.ARRIVAL_AIRPORT_CODE -- > AIRPORT LEG_INSTANCE.AIRPLANE_ID -- > AIRPLANE FARES.FLIGHT_NUMBER -- > FLIGHT CAN_LAND.AIRPLANE_TYPE_NAME -- > AIRPLANE_TYPE CAN_LAND.AIRPORT_CODE -- > AIRPORT AIRPLANE.AIRPLANE_TYPE -- > AIRPLANE_TYPE SEAT_RESERVATION.(FLIGHT_NUMBER,LEG_NUMBER,DATE) -- > LEG_INSTANCE 5 .13 - Consider the relation CLASS(Course#, Univ_Section#, InstructorName, Semester, BuildingCode, Room#, TimePeriod, Weekdays, CreditHours). This represents classes taught in a university with unique Univ_Section#. Give what you think should be various candidate keys 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 the Semester attribute include the year; for example "Spring/94" or "Fall/93" could be values for Semester): 1. {Semester, BuildingCode, Room#, TimePeriod, Weekdays} if the same room cannot be used 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 each semester. 4. If Univ_Section# is not unique, which is the case in many universities, we have to examine the rules that the university uses for section numbering. For example, if the

  29. Page 29
    Read page 29 as text

    Chapter 5 : The Relational Data Model and Relational Database Constraints 4 sections of a particular course during a particular sem ester are numbered 1, 2, 3, … then a candidate key would be {Course#, Univ_Section#, Semester}. If, on the other hand, all sections (of any course) have unique numbers during a particular semester only, then the candidate key would be {Univ_Section#, Semester}. 5 .14 - Consider the following six relations for an order - processing database application in a company: 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 was placed; 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 for this schema, stating any assumptions you make. What other constraints can you think of for this database? Answer: Strictly speaking, a foreign key is a set of attributes, but when that set contains only one attribute, then that attribute itself is often informally called a foreign key. The schema of this question has the following five foreign keys: 1. the attribute Cust# of relation ORDER that references relation CUSTOMER , 2. the attribute Order# of relation ORDER_ITEM that references relation ORDER , 3. the attribute Item# of relation ORDER_ITEM that references relation ITEM , 4. the attribute Order# of relation SHIPMENT that references relation ORDER , and 5. the attribute Warehouse# of relation SHIPMENT that references relation WAREHOUSE . We now give the queries in relational algebra :

  30. Page 30
    Read page 30 as text

    Chapter 5 : The Relational Data Model and Relational Database Constraints 5 The above query lists all orders for which no “timely” shipment was made, including orders for which no shipment was ever made. It is instructive to compare the above query with the one below that lists those orders for which at least one “late” shipment was made. 5 .15 - Consider the following relations for a database that keeps track of business trips of salespersons 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 relation SALESPERSON , and 2. the attribute Trip_ID of relation EXPENSE that references relation TRIP . In addition, the attributes Dept_No of relation SALESPERSON and Account# of relation EXPENSE are probably also foreign keys referencing other relations of the database not mentioned 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 enrollment in 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.

  31. Page 31
    Read page 31 as text

    Chapter 5 : The Relational Data Model and Relational Database Constraints 6 Answer: The schema of this question has the following four foreign keys: 3. the attribute SSN of relation ENROLL that references relation STUDENT , 4. the attribute Course# in relation ENROLL that references relation COURSE, 5. the attribute Course# in relation BOOK_ADOPTION that references relation COURSE, and 6. the attribute Book_ISBN of relation BOOK_ADOPTION that references relation TEXT. We now give the queries in relational algebra: 5 .18 - Database design often involves decisions about the storage of attributes. For example a Social Security Number can be stored as a one attribute or split into three attributes (one for 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. The decision is usually based on how the database will be used. This exercise asks you to think about 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 the country code (for dialing international phone numbers). b. I would recommend storing the numbers in a separate attribute as they have their own independent existence. For example, if an area code region were split into two regions, it would change the area code associated with certain numbers, and having area code in a separate 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 different attributes 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

119 more pages in the full document

Unlock it once and it stays in your library, ready to chat with or turn into flashcards.

Unlock the full document

More Information Technology study materials

See all

Study this document with CramX

Once it is in your library, every tool can work from it.