Solution Manual For Fundamentals Of Database Systems, 6th Edition

Preview (31 of 155 Pages)

100%

Solution Manual For Fundamentals Of Database Systems, 6th Edition - Page 1 preview image

Loading page ...

Chapter 1: Databases and Database Users1CHAPTER 1: DATABASES AND DATABASE USERSAnswers to Selected Exercises1.8-Identify some informal queries and update operations that 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 the

Solution Manual For Fundamentals Of Database Systems, 6th Edition - Page 2 preview image

Loading page ...

Solution Manual For Fundamentals Of Database Systems, 6th Edition - Page 3 preview image

Loading page ...

Chapter 1: Databases and Database Users2university administration's use.(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 itstype in the relational database terminology that will be covered in Chapter 6, forreference 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 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 studenthas completed a number of sections whose total course CreditHours is greater that 24credits (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.1.Small internal utility to locate files2.2.Small single user application that does not require security (such as a customizedcalculator or a personal address and phone book)3.3.Real-time navigation system (with heavy computation and very little data)4.4.The students may think of others.1.14-Consider Figure 1.2.a.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.b.Can you restructure the columns in COURSE, SECTION, and PREREQUISITE tables sothat only one column will need to be updated?Formatted:Bullets and NumberingFormatted:Bullets and Numbering

Solution Manual For Fundamentals Of Database Systems, 6th Edition - Page 4 preview image

Loading page ...

Chapter 1: Databases and Database Users3Answer:a. 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, 6th 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 studentsin sections 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 intothe 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 utilityto print 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 generategrade slips at the end of each semester for all students who have completed coursesduring that semester. 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 from Section 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.

Solution Manual For Fundamentals Of Database Systems, 6th Edition - Page 6 preview image

Loading page ...

Chapter 2: Database System Concepts and Architecture22.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 onvalues 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, 6th Edition - Page 7 preview image

Loading page ...

Chapter 3: The Relational Data Model and Relational Database Constraints1CHAPTER 3: THE RELATIONAL DATA MODEL AND RELATIONAL DATABASECONSTRAINTSAnswers to Selected Exercises3.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 the new DEPARTMENT tuple to a value that does not violate the key constraint. Violatesreferential integrity because MGRSSN='943775543' and there is no tuple in theEMPLOYEE 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 inEMPLOYEE, or (iii) inserting a new EMPLOYEE tuple with SSN='943775543'.(d) Violates both the entity integrity and referential integrity. Violates entity integrity

Solution Manual For Fundamentals Of Database Systems, 6th Edition - Page 8 preview image

Loading page ...

Chapter 3: The Relational Data Model and Relational Database Constraints2because PNO, which is part of the primary key of WORKS_ON, is null. We may enforcethis constraint by: (i) rejecting the insertion, or (ii) changing the value of PNO in thenew 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 theEMPLOYEE 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 inEMPLOYEE, 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 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 valuefor PNO=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' andthere is no tuple in the EMPLOYEE relation with SSN='943775543'. We may enforce theconstraint by: (i) rejecting the deletion, or (ii) inserting a new EMPLOYEE tuple withSSN='943775543'.(k) No constraint violations.3.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_LEGs with 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 particular AIRPLANE_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 AIRLINE database 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, 6th Edition - Page 9 preview image

Loading page ...

Chapter 3: 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)whenever attribute A (or the set of attributes X) of relation R form a foreign key thatreferences the primary 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_INSTANCE3.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 for Semester):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 to

Solution Manual For Fundamentals Of Database Systems, 6th Edition - Page 10 preview image

Loading page ...

Chapter 3: The Relational Data Model and Relational Database Constraints4examine the rules that the university uses for section numbering. For example, if thesections of a particular course during a particular semester are numbered 1, 2, 3, ...,then a candidate key would be {Course#, Univ_Section#, Semester}. If, on the otherhand, all sections (of any course) have unique numbers during a particular semesteronly, then the candidate key would be {Univ_Section#, Semester}.3.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 ofthis question 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, 6th Edition - Page 11 preview image

Loading page ...

Chapter 3: The Relational Data Model and Relational Database Constraints5The above query lists all orders for which no “timely” shipment was made, includingorders for which no shipment was ever made. It is instructive to compare the abovequery with the one below that lists those orders for which at least one “late” shipmentwas made.3.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:3.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)

Solution Manual For Fundamentals Of Database Systems, 6th Edition - Page 12 preview image

Loading page ...

Chapter 3: The Relational Data Model and Relational Database Constraints6Specify the foreign keys for this schema, stating any assumptions you make.Answer: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:3.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.a.We need the area code (also know as city code in some countries) and perhaps thecountry code (for dialing international phone numbers).b.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.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.Formatted:Bullets and Numbering

Solution Manual For Fundamentals Of Database Systems, 6th Edition - Page 13 preview image

Loading page ...

Chapter 3: The Relational Data Model and Relational Database Constraints7d.d.In general, if the each attribute has an independent logical existence based on theapplication, it would make sense to store it in a separate column otherwise there is no clearadvantage. For example, SSN need not be split into its component unless we are using thesubsequences to make deductions about validity, geography, etc. In the two cases above, itmade logical and business sense to split the attributes.3.19-Consider a STUDENT relation in a UNIVERSITY database with the following attributes(Name, SSN, Local_phone, Address, Cell_phone, Age, GPA). Note that the cell phone maybe from a different city and state (or province) from the local phone. A possible tuple of therelation is shown below:NameSSNLocalPhoneAddressCellPhoneAgeGPAGeorge ShawWilliam Edwards123-45-6789555-1234123 Main St.,Anytown, CA 94539555-4321193.75a. Identify the critical missing information from the LocalPhone and CellPhone attributes asshown in the example above. (Hint: How do call someone who lives in a different state orprovince?)b. Would you store this additional information in the LocalPhone and CellPhone attributes oradd new attributes to the schema for STUDENT?c. Consider the Name attribute. What are the advantages and disadvantages of splitting thisfield from one attribute into three attributes (first name, middle name, and last name)?d. What general guideline would you recommend for deciding when to store information in asingle attribute and when to split the information.Answer:a. A combination of first name, last name, and home phone may address the issue assumingthat there are no two students with identical names sharing a home phone line. It alsoassumes that every student has a home phone number. Another solution may be to use firstname, last name, and home zip code. This again has a potential for duplicates, which wouldbe very rare within one university. An extreme solution is to use a combination of charactersfrom last name, major, house number etc.b. If we use name in a primary key and the name changes then the primary key changes.Changing the primary key is acceptable but can be inefficient as any references to this key inthe database need to be appropriately updated, and that can take a long time in a largedatabase. Also, the new primary key must remain unique. [Footnote: Name change is anexample of where our database must be able to model the natural world. In this case, werecognize that the name change can occur regardless of whether it is due to marriage, or aconsequence of a religious and/or spiritual conversion, or for any other reason.]c. The challenge of choosing an invariant primary key from the natural data items leads tothe concept of generated keys, also known as surrogate keys. Specifically, we can usesurrogate keys instead of keys that occur naturally in the database. Some databaseprofessionals believe that it is best to use keys that are uniquely generated by the database,for example each row may have a primary key that is generated in the sequence of creationof rows (tuples). There are many advantages and disadvantages that are often been arguedin design sessions. The main advantage is that it gives us an invariant key without anyworries about choosing a unique primary key. The main disadvantages of surrogate keys arethat they do not have a business meaning (making some aspects of database managementchallenging) and that they are slightly less efficient (because they require another pass wheninserting a row because the key often needs to be returned to the application after a row is

Solution Manual For Fundamentals Of Database Systems, 6th Edition - Page 14 preview image

Loading page ...

Chapter 3: The Relational Data Model and Relational Database Constraints8inserted).3.20-Recent changes in privacy laws have disallowed organizations from using SSN toidentify individuals unless certain restrictions are satisfied. As a result, most US universitiescannot use SSNs as primary keys (except for financial data). In practice, StudentID, a uniqueID, a unique identifier, assigned to every student, is likely to be used as the primary keyrather than SSN since StudentID is usable across all aspects of the system.a. Some database designers are reluctant to use generated keys (also known assurrogatekeys) for primary keys (such as StudentID) because they are artificial. Can you propose anynatural choices of keys that can be used to store the student record in a UNIVERSITYdatabase?b. Suppose that you were able to guarantee uniqueness of a natural key that included lastname. Are you guaranteed that the last name will not change during the lifetime of thedatabase? If the last name last name can change, what solutions can you propose forcreating a primary key that still includes last name but remains unique?c. What are the advantages and disadvantages of using generated (surrogate) keys?Answer:a. By keeping the name attributes separated, we allow the possibility of looking these piecesof their name. In a practical use, it is not likely that the user will know the correct primary keyfor a given student and so we must consider how a user will locate the correct row withoutthis information. If we were to collapse the name into a single attribute, then we havecomplicated any sort of “lookup by name” query; such a query would then require partialstring matching and any results might not disambiguate between FirstName and LastName.Therefore, a practical system should allow name searches by FirstName and LastName; wemust leave MiddleInitial separated still to avoid ambiguities from combining these piecestogether.b. A single attribute Phone# would no longer suffice if a student were able to have multiplephone numbers. We could possibly have multiple rows for a single student to allow this tohappen, but then we have violated key principles of database design (e.g. having redundantdata). A better solution would be to include the additional attributes HomePhone, CellPhone,and OfficePhone and allow the possibility of these attributes to have no value. Again, this isnot most desirable because most students will not have all three of these attributes, and wewill have many valueless key/attribute pairs. An excellent solution would be add anadditional relation Phone# (SSN, Type, Number) while removing PhoneNumber from theStudent relationship. This new relationship would allow the one-to-many relationship fromstudents to phone numbers without creating redundant data or wasting space on sparse,valueless attributes.

Solution Manual For Fundamentals Of Database Systems, 6th Edition - Page 15 preview image

Loading page ...

Chapter 4: Basic SQL1CHAPTER 4: Basic SQLAnswers to Selected Exercises4.5-Consider the database shown in Figure 1.2, whose schema is shown in Figure 2.1.What are the referential integrity constraints that should hold on the schema?Write appropriate SQL DDL statements to define the database.Answer:The following referential integrity constraints should hold (we use the notation:R.(A1, ..., An)--> S.(B1, ..., Bn)to represent a foreign key from the attributes A1, ..., An of R (the referencing relation)to S (the referenced relation)):PREREQUISITE.(CourseNumber)--> COURSE.(CourseNumber)PREREQUISITE.(PrerequisiteNumber)--> COURSE.(CourseNumber)SECTION.(CourseNumber)--> COURSE.(CourseNumber)GRADE_REPORT.(StudentNumber)--> STUDENT.(StudentNumber)GRADE_REPORT.(SectionIdentifier)--> SECTION.(SectionIdentifier)One possible set of CREATE TABLE statements to define the database is given below.CREATE TABLE STUDENT ( Name VARCHAR(30) NOT NULL,StudentNumber INTEGER NOT NULL,Class CHAR NOT NULL,Major CHAR(4),PRIMARY KEY (StudentNumber) );CREATE TABLE COURSE ( CourseName VARCHAR(30) NOT NULL,CourseNumber CHAR(8) NOT NULL,CreditHours INTEGER,Department CHAR(4),PRIMARY KEY (CourseNumber),UNIQUE (CourseName) );CREATE TABLE PREREQUISITE ( CourseNumber CHAR(8) NOT NULL,PrerequisiteNumber CHAR(8) NOT NULL,PRIMARY KEY (CourseNumber, PrerequisiteNumber),FOREIGN KEY (CourseNumber) REFERENCESCOURSE (CourseNumber),FOREIGN KEY (PrerequisiteNumber) REFERENCESCOURSE (CourseNumber) );CREATE TABLE SECTION ( SectionIdentifier INTEGER NOT NULL,CourseNumber CHAR(8) NOT NULL,Semester VARCHAR(6) NOT NULL,Year CHAR(4) NOT NULL,Instructor VARCHAR(15),PRIMARY KEY (SectionIdentifier),FOREIGN KEY (CourseNumber) REFERENCESCOURSE (CourseNumber) );CREATE TABLE GRADE_REPORT ( StudentNumber INTEGER NOT NULL,SectionIdentifier INTEGER NOT NULL,Grade CHAR,PRIMARY KEY (StudentNumber, SectionIdentifier),FOREIGN KEY (StudentNumber) REFERENCESSTUDENT (StudentNumber),FOREIGN KEY (SectionIdentifier) REFERENCESSECTION (SectionIdentifier) );4.6-Repeat Exercise 4.5, but use the AIRLINE schema of Figure 3.8.Answer:

Solution Manual For Fundamentals Of Database Systems, 6th Edition - Page 16 preview image

Loading page ...

Chapter 4: Basic SQL2The following referential integrity constraints should hold:FLIGHT_LEG.(FLIGHT_NUMBER)--> FLIGHT.(NUMBER)FLIGHT_LEG.(DEPARTURE_AIRPORT_CODE)--> AIRPORT.(AIRPORT_CODE)FLIGHT_LEG.(ARRIVAL_AIRPORT_CODE)--> AIRPORT.(AIRPORT_CODE)LEG_INSTANCE.(FLIGHT_NUMBER, LEG_NUMBER)-->FLIGHT_LEG.(FLIGHT_NUMBER, LEG_NUMBER)LEG_INSTANCE.(AIRPLANE_ID)--> AIRPLANE.(AIRPLANE_ID)LEG_INSTANCE.(DEPARTURE_AIRPORT_CODE)--> AIRPORT.(AIRPORT_CODE)LEG_INSTANCE.(ARRIVAL_AIRPORT_CODE)--> AIRPORT.(AIRPORT_CODE)FARES.(FLIGHT_NUMBER)--> FLIGHT.(NUMBER)CAN_LAND.(AIRPLANE_TYPE_NAME)--> AIRPLANE_TYPE.(TYPE_NAME)CAN_LAND.(AIRPORT_CODE)--> AIRPORT.(AIRPORT_CODE)AIRPLANE.(AIRPLANE_TYPE)--> AIRPLANE_TYPE.(TYPE_NAME)SEAT_RESERVATION.(FLIGHT_NUMBER, LEG_NUMBER, DATE)-->LEG_INSTANCE.(FLIGHT_NUMBER, LEG_NUMBER, DATE)One possible set of CREATE TABLE statements to define the database is given below.CREATE TABLE AIRPORT ( AIRPORT_CODE CHAR(3) NOT NULL,NAME VARCHAR(30) NOT NULL,CITY VARCHAR(30) NOT NULL,STATE VARCHAR(30),PRIMARY KEY (AIRPORT_CODE) );CREATE TABLE FLIGHT ( NUMBER VARCHAR(6) NOT NULL,AIRLINE VARCHAR(20) NOT NULL,WEEKDAYS VARCHAR(10) NOT NULL,PRIMARY KEY (NUMBER) );CREATE TABLE FLIGHT_LEG ( FLIGHT_NUMBER VARCHAR(6) NOT NULL,LEG_NUMBER INTEGER NOT NULL,DEPARTURE_AIRPORT_CODE CHAR(3) NOT NULL,SCHEDULED_DEPARTURE_TIME TIMESTAMP WITH TIME ZONE,ARRIVAL_AIRPORT_CODE CHAR(3) NOT NULL,SCHEDULED_ARRIVAL_TIME TIMESTAMP WITH TIME ZONE,PRIMARY KEY (FLIGHT_NUMBER, LEG_NUMBER),FOREIGN KEY (FLIGHT_NUMBER) REFERENCES FLIGHT (NUMBER),FOREIGN KEY (DEPARTURE_AIRPORT_CODE) REFERENCESAIRPORT (AIRPORT_CODE),FOREIGN KEY (ARRIVAL_AIRPORT_CODE) REFERENCESAIRPORT (AIRPORT_CODE) );CREATE TABLE LEG_INSTANCE ( FLIGHT_NUMBER VARCHAR(6) NOT NULL,LEG_NUMBER INTEGER NOT NULL,LEG_DATE DATE NOT NULL,NO_OF_AVAILABLE_SEATS INTEGER,AIRPLANE_ID INTEGER,DEPARTURE_AIRPORT_CODE CHAR(3),DEPARTURE_TIME TIMESTAMP WITH TIME ZONE,ARRIVAL_AIRPORT_CODE CHAR(3),ARRIVAL_TIME TIMESTAMP WITH TIME ZONE,PRIMARY KEY (FLIGHT_NUMBER, LEG_NUMBER, LEG_DATE),FOREIGN KEY (FLIGHT_NUMBER, LEG_NUMBER) REFERENCESFLIGHT_LEG (FLIGHT_NUMBER, LEG_NUMBER),FOREIGN KEY (AIRPLANE_ID) REFERENCESAIRPLANE (AIRPLANE_ID),FOREIGN KEY (DEPARTURE_AIRPORT_CODE) REFERENCESAIRPORT (AIRPORT_CODE),FOREIGN KEY (ARRIVAL_AIRPORT_CODE) REFERENCESAIRPORT (AIRPORT_CODE) );CREATE TABLE FARES ( FLIGHT_NUMBER VARCHAR(6) NOT NULL,FARE_CODE VARCHAR(10) NOT NULL,

Solution Manual For Fundamentals Of Database Systems, 6th Edition - Page 17 preview image

Loading page ...

Chapter 4: Basic SQL3AMOUNT DECIMAL(8,2) NOT NULL,RESTRICTIONS VARCHAR(200),PRIMARY KEY (FLIGHT_NUMBER, FARE_CODE),FOREIGN KEY (FLIGHT_NUMBER) REFERENCES FLIGHT (NUMBER) );CREATE TABLE AIRPLANE_TYPE ( TYPE_NAME VARCHAR(20) NOT NULL,MAX_SEATS INTEGER NOT NULL,COMPANY VARCHAR(15) NOT NULL,PRIMARY KEY (TYPE_NAME) );CREATE TABLE CAN_LAND ( AIRPLANE_TYPE_NAME VARCHAR(20) NOT NULL,AIRPORT_CODE CHAR(3) NOT NULL,PRIMARY KEY (AIRPLANE_TYPE_NAME, AIRPORT_CODE),FOREIGN KEY (AIRPLANE_TYPE_NAME) REFERENCESAIRPLANE_TYPE (TYPE_NAME),FOREIGN KEY (AIRPORT_CODE) REFERENCESAIRPORT (AIRPORT_CODE) );CREATE TABLE AIRPLANE ( AIRPLANE_ID INTEGER NOT NULL,TOTAL_NUMBER_OF_SEATS INTEGER NOT NULL,AIRPLANE_TYPE VARCHAR(20) NOT NULL,PRIMARY KEY (AIRPLANE_ID),FOREIGN KEY (AIRPLANE_TYPE) REFERENCES AIRPLANE_TYPE (TYPE_NAME) );CREATE TABLE SEAT_RESERVATION ( FLIGHT_NUMBER VARCHAR(6) NOT NULL,LEG_NUMBER INTEGER NOT NULL,LEG_DATE DATE NOT NULL,SEAT_NUMBER VARCHAR(4),CUSTOMER_NAME VARCHAR(30) NOT NULL,CUSTOMER_PHONE CHAR(12),PRIMARY KEY (FLIGHT_NUMBER, LEG_NUMBER, LEG_DATE, SEAT_NUMBER),FOREIGN KEY (FLIGHT_NUMBER, LEG_NUMBER, LEG_DATE) REFERENCESLEG_INSTANCE (FLIGHT_NUMBER, LEG_NUMBER, LEG_DATE) );4.7-Consider the LIBRARY relational database schema of Figure 4.6. Choose theappropriate action (reject, cascade, set to null, set to default) for each referential integrityconstraint, both for thedeletionof a referenced tuple, and for theupdateof a primary keyattribute value in a referenced tuple. Justify your choices.Answer:Below are possible choices. In general, if it is not clear which action to choose, REJECTshould be chosen, since it will not permit automatic changes to happen (by updatepropagation) that may be unintended.BOOK_AUTHORS.(BookId)--> BOOK.(BookId)CASCADE on both DELETE or UPDATE (since this corresponds to a multi-valued attributeof BOOK (see the solution to Exercise 6.27); hence, if a BOOK is deleted, or the value ofits BookId is updated (changed), the deletion or change is automatically propagated to thereferencing BOOK_AUTHORS tuples)BOOK.(PublisherName)--> PUBLISHER.(Name)REJECT on DELETE (we should not delete a PUBLISHER tuple which has existing BOOKtuples that reference the PUBLISHER)CASCADE on UPDATE (if a PUBLISHER's Name is updated, the change should bepropagated automatically to all referencing BOOK tuples)BOOK_LOANS.(BookId)--> BOOK.(BookId)CASCADE on both DELETE or UPDATE (if a BOOK is deleted, or the value of its BookId isupdated (changed), the deletion or change is automatically propagated to the referencingBOOK_LOANS tuples) (Note: One could also choose REJECT on DELETE)BOOK_COPIES.(BookId)--> BOOK.(BookId)CASCADE on both DELETE or UPDATE (if a BOOK is deleted, or the value of its BookId isupdated (changed), the deletion or change is automatically propagated to the referencing

Solution Manual For Fundamentals Of Database Systems, 6th Edition - Page 18 preview image

Loading page ...

Chapter 4: Basic SQL4BOOK_COPIES tuples)BOOK_LOANS.(CardNo)--> BORROWER.(CardNo)CASCADE on both DELETE or UPDATE (if a BORROWER tuple is deleted, or the value ofits CardNo is updated (changed), the deletion or change is automatically propagated to thereferencing BOOK_LOANS tuples) (Note: One could also choose REJECT on DELETE, withthe idea that if a BORROWER is deleted, it is necessary first to make a printout of allBOOK_LOANS outstanding before deleting the BORROWER; in this case, the tuples inBOOK_LOANS that reference the BORROWER being deleted would first be explicitlydeleted after making the printout, and before the BORROWER is deleted)BOOK_COPIES.(BranchId)--> LIBRARY_BRANCH.(BranchId)CASCADE on both DELETE or UPDATE (if a LIBRARY_BRANCH is deleted, or the value ofits BranchId is updated (changed), the deletion or change is automatically propagated tothe referencing BOOK_COPIES tuples) (Note: One could also choose REJECT on DELETE)BOOK_LOANS.(BranchId)--> LIBRARY_BRANCH.(BranchId)CASCADE on both DELETE or UPDATE (if a LIBRARY_BRANCH is deleted, or the value ofits BranchId is updated (changed), the deletion or change is automaticallypropagated to the referencing BOOK_LOANS tuples) (Note: One could also chooseREJECT on DELETE)4.8-Write appropriate SQL DDL statements for declaring the LIBRARY relational databaseschema of Figure 4.6. Specify the keys and referential triggered actions.Answer:One possible set of CREATE TABLE statements is given below:CREATE TABLE BOOK ( BookId CHAR(20) NOT NULL,Title VARCHAR(30) NOT NULL,PublisherName VARCHAR(20),PRIMARY KEY (BookId),FOREIGN KEY (PublisherName) REFERENCES PUBLISHER (Name) ON UPDATECASCADE );CREATE TABLE BOOK_AUTHORS ( BookId CHAR(20) NOT NULL,AuthorName VARCHAR(30) NOT NULL,PRIMARY KEY (BookId, AuthorName),FOREIGN KEY (BookId) REFERENCES BOOK (BookId)ON DELETE CASCADE ON UPDATE CASCADE );CREATE TABLE PUBLISHER ( Name VARCHAR(20) NOT NULL,Address VARCHAR(40) NOT NULL,Phone CHAR(12),PRIMARY KEY (Name) );CREATE TABLE BOOK_COPIES ( BookId CHAR(20) NOT NULL,BranchId INTEGER NOT NULL,No_Of_Copies INTEGER NOT NULL,PRIMARY KEY (BookId, BranchId),FOREIGN KEY (BookId) REFERENCES BOOK (BookId)ON DELETE CASCADE ON UPDATE CASCADE,FOREIGN KEY (BranchId) REFERENCES BRANCH (BranchId)ON DELETE CASCADE ON UPDATE CASCADE );CREATE TABLE BORROWER ( CardNo INTEGER NOT NULL,Name VARCHAR(30) NOT NULL,Address VARCHAR(40) NOT NULL,Phone CHAR(12),PRIMARY KEY (CardNo) );CREATE TABLE BOOK_LOANS ( CardNo INTEGER NOT NULL,

Solution Manual For Fundamentals Of Database Systems, 6th Edition - Page 19 preview image

Loading page ...

Chapter 4: Basic SQL5BookId CHAR(20) NOT NULL,BranchId INTEGER NOT NULL,DateOut DATE NOT NULL,DueDate DATE NOT NULL,PRIMARY KEY (CardNo, BookId, BranchId),FOREIGN KEY (CardNo) REFERENCES BORROWER (CardNo)ON DELETE CASCADE ON UPDATE CASCADE,FOREIGN KEY (BranchId) REFERENCES LIBRARY_BRANCH (BranchId)ON DELETE CASCADE ON UPDATE CASCADE,FOREIGN KEY (BookId) REFERENCES BOOK (BookId)ON DELETE CASCADE ON UPDATE CASCADE );CREATE TABLE LIBRARY_BRANCH ( BranchId INTEGER NOT NULL,BranchName VARCHAR(20) NOT NULL,Address VARCHAR(40) NOT NULL,PRIMARY KEY (BranchId) );4.9-How can the key and foreign key constraints be enforced by the DBMS? Is theenforcement technique you suggest difficult to implement? Can the constraint checks beexecuted in an efficient manner when updates are applied to the database?Answer:One possible technique that is often used to check efficiently for the key constraintis to create an index on the combination of attributes that form each key (primary orsecondary). Before inserting a new record (tuple), each index is searched to check thatno value currently exists in the index that matches the key value in the new record. Ifthis is the case, the record is inserted successfully.For checking the foreign key constraint, an index on the primary key of eachreferenced relation will make this check relatively efficient. Whenever a new record isinserted in a referencing relation , its foreign key value is used to search the index forthe primary key of the referenced relation, and if the referenced record exists, then thenew record can be successfully inserted in the referencing relation.For deletion of a referenced record, it is useful to have an index on the foreign keyof each referencing relation so as to be able to determine efficiently whether any recordsreference the record being deleted.If the indexes described above do not exist, and no alternative access structure (forexample, hashing) is used in their place, then it is necessary to do linear searches tocheck for any of the above constraints, making the checks quite inefficient.4.10–No solution provided.4.11-Specify the updates of Exercise 3.11 using the SQL update commands.Answers:Below, we show how each of the updates may be specified in SQL. Notice that some ofthese updates violate integrity constraints as discussed in the solution to Exercise 5.10,and hence should be rejected if executed on the database of Figure 5.6.(a) Insert < 'Robert', 'F', 'Scott', '943775543', '21-JUN-42', '2365 Newcastle Rd,Bellaire, TX', M, 58000, '888665555', 1 > into EMPLOYEE.INSERT INTO EMPLOYEEVALUES ('Robert', 'F', 'Scott', '943775543', '21-JUN-42', '2365 Newcastle Rd, Bellaire, TX',M, 58000, '888665555', 1)

Solution Manual For Fundamentals Of Database Systems, 6th Edition - Page 20 preview image

Loading page ...

Chapter 4: Basic SQL6(b) Insert < 'ProductA', 4, 'Bellaire', 2 > into PROJECT.INSERT INTO PROJECTVALUES ('ProductA', 4, 'Bellaire', 2)(c) Insert < 'Production', 4, '943775543', '01-OCT-88' > into DEPARTMENT.INSERT INTO DEPARTMENTVALUES ('Production', 4, '943775543', '01-OCT-88')(d) Insert < '677678989', null, '40.0' > into WORKS_ON.INSERT INTO WORKS_ONVALUES ('677678989', NULL, '40.0')(e) Insert < '453453453', 'John', M, '12-DEC-60', 'SPOUSE' > into DEPENDENT.INSERT INTO DEPENDENTVALUES ('453453453', 'John', M, '12-DEC-60', 'SPOUSE')(f) Delete the WORKS_ON tuples with ESSN= '333445555'.DELETE FROM WORKS_ONWHERE ESSN= '333445555'(g) Delete the EMPLOYEE tuple with SSN= '987654321'.DELETE FROM EMPLOYEEWHERE SSN= '987654321'(h) Delete the PROJECT tuple with PNAME= 'ProductX'.DELETE FROM PROJECTWHERE PNAME= 'ProductX'(i) Modify the MGRSSN and MGRSTARTDATE of the DEPARTMENT tuple withDNUMBER=5 to '123456789' and '01-OCT-88', respectively.UPDATE DEPARTMENTSET MGRSSN = '123456789', MGRSTARTDATE = '01-OCT-88'WHERE DNUMBER= 5(j) Modify the SUPERSSN attribute of the EMPLOYEE tuple with SSN= '999887777' to'943775543'.UPDATE EMPLOYEESET SUPERSSN = '943775543'WHERE SSN= '999887777'(k) Modify the HOURS attribute of the WORKS_ON tuple with ESSN= '999887777' andPNO= 10 to '5.0'.UPDATE WORKS_ONSET HOURS = '5.0'WHERE ESSN= '999887777' AND PNO= 104.12-Specify the following queries in SQL on the database schema of Figure 1.2.(a) Retrieve the names of all senior students majoring in 'COSC' (computer science).(b) Retrieve the names of all courses taught by professor King in 85 and 86.(c) For each section taught by professor King, retrieve the course number, semester,year, and number of students who took the section.

Solution Manual For Fundamentals Of Database Systems, 6th Edition - Page 21 preview image

Loading page ...

Chapter 4: Basic SQL7(d) Retrieve the name and transcript of each senior student (Class=5) majoring inCOSC. Transcript includes course name, course number, credit hours, semester, year,and grade for each course completed by the student.(e) Retrieve the names and major departments of all straight A students (students whohave a grade of A in all their courses).(f) Retrieve the names and major departments of all students who do not have any gradeof A in any of their courses.Answers:(a)SELECT NameFROM STUDENTWHERE Major='COSC'(b)SELECT CourseNameFROM COURSE, SECTIONWHERE COURSE.CourseNumber=SECTION.CourseNumber AND Instructor='King'AND (Year='85' OR Year='86')Another possible SQL query uses nesting as follows:SELECT CourseNameFROM COURSEWHERE CourseNumber IN ( SELECT CourseNumberFROM SECTIONWHERE Instructor='King' AND (Year='85' OR Year='86') )(c)SELECT CourseNumber, Semester, Year, COUNT(*)FROM SECTION, GRADE_REPORTWHERE Instructor='King' AND SECTION.SectionIdentifier=GRADE_REPORT.SectionIdentifierGROUP BY CourseNumber, Semester, Year(d)SELECT Name, CourseName, C.CourseNumber, CreditHours, Semester, Year, GradeFROM STUDENT ST, COURSE C, SECTION S, GRADE_REPORT GWHERE Class=5 AND Major='COSC' AND ST.StudentNumber=G.StudentNumber ANDG.SectionIdentifier=S.SectionIdentifier AND S.CourseNumber=C.CourseNumber(e)SELECT Name, MajorFROM STUDENTWHERE NOT EXISTS ( SELECT *FROM GRADE_REPORTWHERE StudentNumber= STUDENT.StudentNumber AND NOT(Grade='A'))(f)SELECT Name, MajorFROM STUDENTWHERE NOT EXISTS ( SELECT *FROM GRADE_REPORTWHERE StudentNumber= STUDENT.StudentNumber AND Grade='A' )4.13-Write SQL update statements to do the following on the database schema shown inFigure 1.2.(a) Insert a new student <'Johnson', 25, 1, 'MATH'> in the database.(b) Change the class of student 'Smith' to 2.

Solution Manual For Fundamentals Of Database Systems, 6th Edition - Page 22 preview image

Loading page ...

Chapter 4: Basic SQL8(c) Insert a new course <'Knowledge Engineering','COSC4390', 3,'COSC'>.(d) Delete the record for the student whose name is 'Smith' and student number is 17.Answers:(a)INSERT INTO STUDENTVALUES ('Johnson', 25, 1, 'MATH')(b)UPDATE STUDENTSET CLASS = 2WHERE Name='Smith'(c)INSERT INTO COURSEVALUES ('Knowledge Engineering','COSC4390', 3,'COSC')(d)DELETE FROM STUDENTWHERE Name='Smith' AND StudentNumber=174.14–No solution provided4.15-Consider the EMPLOYEE table’s constraint EMPSUPERFK as specified in Figure 4.2is changed to read as follows:CONSTRAINT EMPSUPERFKFOREIGN KEY (SUPERSSN) REFERNCES EMPLOYEE(SSN)ON DELETE CASCADE ON UPDATE CASCADE,Answer the following questions:a.a.What happens when the following command is run on the database state shown inFigure 5.6?DELETE EMPLOYEE WHERE LNAME = ‘Borg’b.b.Is it better to CASCADE or SET NULL in case of EMPSUPERFK constraint ONDELETE?Answers:a)The James E. Borg entry is deleted from the table, and each employee with him as asupervisor is also (and their supervisees, and so on). In total, 8 rows are deleted and thetable is empty.b)It is better to SET NULL, since an employee is not fired (DELETED) when theirsupervisor is deleted. Instead, their SUPERSSN should be SET NULL so that they can laterget a new supervisor.4.16-Write SQL statements to create a table EMPLOYEE_BACKUP backup of EMPLOYEEtable shown in Figure 3.6.Answer:INSERT INTO EMPLOYEE_BACKUP VALUES ( SELECT * FROM EMPLOYEE )Formatted:Bullets and NumberingFormatted:Bullets and Numbering

Solution Manual For Fundamentals Of Database Systems, 6th Edition - Page 23 preview image

Loading page ...

Chapter 5:More SQL: Complex Queries, Triggers, Views, and Schema Modification1CHAPTER 5: More SQL: Complex Queries, Triggers, Views, and SchemaModificationAnswers to Selected Exercises5.5-Specify the following additional queries on the database of Figure 3.5 in SQL. Showthe query results if applied to the database of Figure 3.6.(a) For each department whose average employee salary is more than $30,000, retrievethe department name and the number of employees working for that department.(b) Suppose we want the number ofmaleemployees in each department rather than allemployees (as in Exercise 5.4a). Can we specify this query in SQL? Why or why not?Answers:(a)SELECT DNAME, COUNT (*)FROM DEPARTMENT, EMPLOYEEWHERE DNUMBER=DNOGROUP BY DNAMEHAVING AVG (SALARY) > 30000Result:DNAME DNUMBER COUNT(*)Research 5 4Administration 4 3Headquarters 1 1(b) The query may still be specified in SQL by using a nested query as follows (not allimplementations may support this type of query):SELECT DNAME, COUNT (*)FROM DEPARTMENT, EMPLOYEEWHERE DNUMBER=DNO AND SEX='M' AND DNO IN ( SELECT DNOFROM EMPLOYEEGROUP BY DNOHAVING AVG (SALARY) > 30000 )GROUP BY DNAMEResult:DNAME DNUMBER COUNT(*)Research 5 3Administration 4 1Headquarters 1 15.6-Specify the following queries in SQL on the database schema of Figure 1.2.(a) Retrieve the names and major departments of all straight-A students (students whohave a grade of A in all their courses).(b) Retrieve the names and major departments of all students who do not have anygrade of A in any of their courses.

Solution Manual For Fundamentals Of Database Systems, 6th Edition - Page 24 preview image

Loading page ...

Chapter 5:More SQL: Complex Queries, Triggers, Views, and Schema Modification2Answers:(a)SELECT Name, MajorFROM STUDENTWHERE NOT EXISTS ( SELECT *FROM GRADE_REPORTWHERE StudentNumber= STUDENT.StudentNumber AND NOT(Grade='A'))(b)SELECT Name, MajorFROM STUDENTWHERE NOT EXISTS ( SELECT *FROM GRADE_REPORTWHERE StudentNumber= STUDENT.StudentNumber AND Grade='A' )5.7-In SQL, specify the following queries on the database specified in Figure 3.5 using theconcept of nested queries and the concepts described in this chapter.a. Retrieve the names of all employees who work in the department that has theemployee with the highest salary among all employees.b. Retrieve the names of all employees whose supervisor’s supervisor has '888665555'for Ssn.c. Retrieve the names of employees who make at least $10,000 more than the employeewho is paid the least in the company.Answers:a)SELECT LNAME FROM EMPLOYEE WHERE DNO =( SELECT DNO FROM EMPLOYEE WHERE SALARY =( SELECT MAX(SALARY) FROM EMPLOYEE) )b)SELECT LNAME FROM EMPLOYEE WHERE SUPERSSN IN( SELECT SSN FROM EMPLOYEE WHERE SUPERSSN = ‘888665555’ )c)SELECT LNAME FROM EMPLOYEE WHERE SALARY >= 10000 +( SELECT MIN(SALARY) FROM EMPLOYEE)5.8–No solution provided.5.9–No solution provided.

Solution Manual For Fundamentals Of Database Systems, 6th Edition - Page 25 preview image

Loading page ...

Chapter 6: The Relational Algebra and Relational Calculus1CHAPTER 6: THE RELATIONAL ALGEBRA AND RELATIONAL CALCULUSAnswers to Selected Exercises6.15-Show the result of each of the sample queries in Section 6.5 as it would apply to thedatabase state in Figure 3.6.Answer:(QUERY 1) Find the name and address of all employees who work for the 'Research'department.Result:FNAME LNAME ADDRESSJohn Smith 731 Fondren, Houston, TXFranklin Wong 638 Voss, Houston, TXRamesh Narayan 975 Fire Oak, Humble, TXJoyce English 5631 Rice, Houston, TX(QUERY 2) For every project located in 'Stafford', list the project number, thecontrolling department number, and the department manager's last name, address, andbirth date.Result:PNUMBER DNUM LNAME ADDRESS BDATE10 4 Wallace 291 Berry, Bellaire, TX 20-JUN-3130 4 Wallace 291 Berry, Bellaire, TX 20-JUN-31(QUERY 3) Find the names of all employees who work on all the projects controlled bydepartment number 5.Result: (empty because no tuples satisfy the result).LNAME FNAME(QUERY 4) Make a list of project numbers for projects that involve an employee whoselast name is 'Smith' as a worker or as a manager of the department that controls theproject.Result:PNO12(QUERY 5) List the names of all employees with two or more dependents.Result:LNAME FNAMESmith JohnWong Franklin(QUERY 6) List the names of employees who have no dependents.Result:LNAME FNAMEZelaya AliciaNarayan RameshEnglish JoyceJabbar AhmadBorg James(QUERY 7) List the names of managers who have at least one dependent.Result:LNAME FNAME

Solution Manual For Fundamentals Of Database Systems, 6th Edition - Page 26 preview image

Loading page ...

Chapter 6: The Relational Algebra and Relational Calculus2Wallace JenniferWong Franklin6.16-Specify the following queries on the COMPANY relational database schema shown inFigure 3.5, using the relational operators discussed in this chapter. Also show the result ofeach query as it would apply to the database state of Figure 3.6.(a) Retrieve the names of employees in department 5 who work more than 10 hours perweek on the 'ProductX' project.(b) List the names of employees who have a dependent with the same first name asthemselves.(c) Find the names of employees that are directly supervised by 'Franklin Wong'.(d) For each project, list the project name and the total hours per week (by allemployees) spent on that project.(e) Retrieve the names of employees who work on every project.(f) Retrieve the names of employees who do not work on any project.(g) For each department, retrieve the department name, and the average salary ofemployees working in that department.(h) Retrieve the average salary of all female employees.(i) Find the names and addresses of employees who work on at least one project locatedin Houston but whose department has no location in Houston.(j) List the last names of department managers who have no dependents.Answers:In the relational algebra, as in other languages, it is possible to specify the same queryin multiple ways. We give one possible solution for each query. We use the symbolsforSELECT,Pfor PROJECT,Jfor EQUIJOIN, * for NATURAL JOIN, andffor FUNCTION.(a)EMP_W_X <--(sPNAME='ProductX' (PROJECT))J(PNUMBER),(PNO)(WORKS_ON)EMP_WORK_10 <--(EMPLOYEE)J(SSN),(ESSN) (sHOURS>10(EMP_W_X))RESULT <--PLNAME,FNAME (sDNO=5 (EMP_WORK_10))Result:LNAME FNAMESmith JohnEnglish Joyce(b)E <--(EMPLOYEE)J(SSN,FNAME),(ESSN,DEPENDENT_NAME)(DEPENDENT)

Solution Manual For Fundamentals Of Database Systems, 6th Edition - Page 27 preview image

Loading page ...

Chapter 6: The Relational Algebra and Relational Calculus3R <--PLNAME,FNAME (E)Result (empty):LNAME FNAME(c)WONG_SSN <--PSSN (sFNAME='Franklin' ANDLNAME='Wong' (EMPLOYEE))WONG_EMPS <--(EMPLOYEE)J(SUPERSSN),(SSN) (WONG_SSN)RESULT <--PLNAME,FNAME (WONG_EMPS)Result:LNAME FNAMESmith JohnNarayan RameshEnglish Joyce(d)PROJ_HOURS(PNO,TOT_HRS) <--PNOfSUM HOURS (WORKS_ON)RESULT <--PPNAME,TOT_HRS ( (PROJ_HOURS)J(PNO),(PNUMBER)(PROJECT) )Result:PNAME TOT_HRSProductX 52.5ProductY 37.5ProductZ 50.0Computerization 55.0Reorganization 25.0Newbenefits 55.0(e)PROJ_EMPS(PNO,SSN) <--PPNO,ESSN (WORKS_ON)ALL_PROJS(PNO) <--PPNUMBER (PROJECT)EMPS_ALL_PROJS <--PROJ_EMPS-:-ALLPROJS (* DIVISION operation *)RESULT <--PLNAME,FNAME (EMPLOYEE * EMP_ALL_PROJS)Result (empty):LNAME FNAME(f)ALL_EMPS <--PSSN (EMPLOYEE)WORKING_EMPS(SSN) <--PESSN (WORKS_ON)NON_WORKING_EMPS <--ALL_EMPS-WORKING_EMPS (* DIFFERENCE*)RESULT <--PLNAME,FNAME (EMPLOYEE * NON_WORKING_EMPS)Result (empty):LNAME FNAME(g)DEPT_AVG_SALS(DNUMBER,AVG_SAL) <--DNOfAVG SALARY(EMPLOYEE)RESULT <--PDNUMBER,AVG_SAL ( DEPT_AVG_SALS * DEPARTMENT )

Solution Manual For Fundamentals Of Database Systems, 6th Edition - Page 28 preview image

Loading page ...

Chapter 6: The Relational Algebra and Relational Calculus4Result:DNUMBER AVG_SALResearch 33250Administration 31000Headquarters 55000(h)RESULT(AVG_F_SAL) <--fAVG SALARY (sSEX='F' (EMPLOYEE) )Result:AVG_F_SAL31000(i)E_P_HOU(SSN) <--PESSN (WORKS_ON J(PNO),(PNUMBER) (sPLOCATION='Houston'(PROJECT)))D_NO_HOU <--PDNUMBER (DEPARTMENT)-PDNUMBER (sDLOCATION='Houston' (DEPARTMENT))E_D_NO_HOU <--PSSN (EMPLOYEE J(PNO),(DNUMBER) (D_NO_HOU))RESULT_EMPS <--E_P_HOU-E_D_NO_HOU (* this is set DIFFERENCE *)RESULT <--PLNAME,FNAME,ADDRESS (EMPLOYEE * RESULT_EMPS)Result:LNAME FNAME ADDRESSWallace Jennifer 291 Berry, Bellaire, TX(j)DEPT_MANAGERS(SSN)<--PMGRSSN (DEPARTMENT)EMPS_WITH_DEPENDENTS(SSN) <--PESSN (DEPENDENT)RESULT_EMPS <--DEPT_MANAGERS-EMPS_WITH_DEPENDENTSRESULT <--PLNAME,FNAME (EMPLOYEE * RESULT_EMPS)Result:LNAME FNAMEBorg James6.17–No solution provided.6.18-Consider the LIBRARY relational schema shown in Figure 6.14, which is used to keeptrack of books, borrowers, and book loans. Referential integrity constraints are shown asdirected arcs in Figure 6.14, as in the notation of Figure 3.7. Write down relationalexpressions for the following queries on the LIBRARY database:(a)How many copies of the book titled The Lost Tribe are owned by the library branchwhose name is "Sharpstown"?(b) How many copies of the book titled The Lost Tribe are owned by each library branch?

Solution Manual For Fundamentals Of Database Systems, 6th Edition - Page 29 preview image

Loading page ...

Chapter 6: The Relational Algebra and Relational Calculus5(c) Retrieve the names of all borrowers who do not have any books checked out.(d) For each book that is loaned out from the "Sharpstown" branch and whose DueDateis today, retrieve the book title, the borrower's name, and the borrower's address.(e) For each library branch, retrieve the branch name and the total number of booksloaned out from that branch.(f) Retrieve the names, addresses, and number of books checked out for all borrowerswho have more than five books checked out.(g) For each book authored (or co-authored) by "Stephen King", retrieve the title andthe number of copies owned by the library branch whose name is "Central".Answer:(Note: We will useSfor SELECT,Pfor PROJECT,*for NATURAL JOIN,-forSET DIFFERENCE,FforAGGREGATE FUNCTION)(a)A <--BOOKCOPIES * LIBRARY-BRANCH * BOOKRESULT <--P No_Of_Copies ( S BranchName='Sharpstown' and Title='The LostTribe'(A) )Note: A better query would be to do the SELECTs before the JOIN as follows:A <--P No_Of_Copies ( ( S BranchName='Sharpstown' (LIBRARY-BRANCH) ) *(BOOKCOPIES * ( S Title='The Lost Tribe'(BOOK) ) ) )(b) P BranchID,No_Of_Copies ( ( S Title='The Lost Tribe' (BOOK)) * BOOKCOPIES )(c) NO_CHECKOUT_B <--P CardNo (BORROWER)-P CardNo (BOOK_LOANS)RESULT <--P Name (BORROWER * NO_CHECKOUT_B)(d) S <--P BranchId ( S BranchName='Sharpstown' (LIBRARY-BRANCH) )B_FROM_S <--P BookId,CardNo ( ( S DueDate='today' (BOOKLOANS) ) * S )RESULT <--P Title,Name,Address ( BOOK * BORROWER * B_FROM_S )(e) R(BranchId,Total) <--BranchId FCOUNT(BookId,CardNo) (BOOK_LOANS)RESULT <--P BranchName,Total (R * LIBRARY_BRANCH)(f) B(CardNo,TotalCheckout) <--CardNo F COUNT(BookId) (BOOK_LOANS)B5 <--S TotalCheckout > 5 (B)RESULT <--P Name,Address,TotalCheckout ( B5 * BORROWER)(g) SK(BookId,Title) <--( sAuthorName='Stephen King' ( BOOK_AUTHORS)) * BOOKCENTRAL(BranchId) <--sBranchName='Central' ( LIBRARY_BRANCH )RESULT <--P Title,NoOfCopies ( SK * BOOKCOPIES * CENTRAL )6.19–6.21:No solutions provided.

Solution Manual For Fundamentals Of Database Systems, 6th Edition - Page 30 preview image

Loading page ...

Chapter 6: The Relational Algebra and Relational Calculus66.22–Consider the two tablesT1andT2 shown in Figure 6.15. Show the results of thefollowing operations:Answers:(a)P Q R A B C10 a 5 10 b 610 a 5 10 b 525 a 6 25 c 3(b)P Q R A B C15 b 8 10 b 615 b 8 10 b 5(c)P Q R A B C10 a 5 10 b 610 a 5 10 b 515 b 8 null null null25 a 6 25 c 3(d)P Q R A B C15 b 8 10 b 6null null null 25 c 315 b 8 10 b 5(e)P Q R10a 515 b 825 a 610b 625 c 310b 5(f)P Q R A B C10 a 5 10 b 56.23–No solution provided.6.24-Specify queries (a), (b), (c), (e), (f), (i), and (j) of Exercise 6.16 in both tuple anddomain relational calculus.Answer:(a) Retrieve the names of employees in department 5 who work more than 10 hours perweek on the 'ProductX' project.Tuple relational Calculus:{ e.LNAME, e.FNAME | EMPLOYEE(e) AND e.DNO=5 AND (EXISTS p)(EXISTS w)(WORKS_ON(w) AND PROJECT(p) AND e.SSN=w.ESSN ANDw.PNO=p.PNUMBER ANDp.PNAME='ProductX' AND w.HOURS>10 ) }

Solution Manual For Fundamentals Of Database Systems, 6th Edition - Page 31 preview image

Loading page ...

Chapter 6: The Relational Algebra and Relational Calculus7Domain relational Calculus:{ qs | EMPLOYEE(qrstuvwxyz) AND z=5 AND (EXISTS a) (EXISTS b) (EXISTSe)(EXISTS f)(EXISTS g) ( WORKS_ON(efg) AND PROJECT(abcd) AND t=e AND f=b ANDa='ProductX' ANDg>10 ) }(b) List the names of employees who have a dependent with the same first name asthemselves.Tuple relational Calculus:{ e.LNAME, e.FNAME | EMPLOYEE(e) AND (EXISTS d) ( DEPENDENT(d) ANDe.SSN=d.ESSNAND e.FNAME=d.DEPENDENT_NAME ) }Domain relational Calculus:{ qs | (EXISTS t) (EXISTS a) (EXISTS b) ( EMPLOYEE(qrstuvwxyz) ANDDEPENDENT(abcde)AND a=t AND b=q ) }(c) Find the names of employees that are directly supervised by 'Franklin Wong'.Tuple relational Calculus:{ e.LNAME, e.FNAME | EMPLOYEE(e) AND (EXISTS s) ( EMPLOYEE(s) ANDs.FNAME='Franklin' AND s.LNAME='Wong' AND e.SUPERSSN=s.SSN ) }Domain relational Calculus:{ qs | (EXISTS y) (EXISTS a) (EXISTS c) (EXISTS d) ( EMPLOYEE(qrstuvwxyz)ANDEMPLOYEE(abcdefghij) AND a='Franklin' AND c='Wong' AND y=d ) }(e) Retrieve the names of employees who work on every project.Tuple relational Calculus:{ e.LNAME, e.FNAME | EMPLOYEE(e) AND (FORALL p) ( NOT(PROJECT(p))OR(EXISTS w) (WORKS_ON(w) AND p.PNUMBER=w.PNO AND w.ESSN=e.SSN ) ) }Domain relational Calculus:{ qs | (EXISTS t) ( EMPLOYEE(qrstuvwxyz) AND (FORALL b) (NOT(PROJECT(abcd)) OR(EXISTS e) (EXISTS f) (WORKS_ON(efg) AND e=t AND f=b) ) }(f) Retrieve the names of employees who do not work on any project.Tuple relational Calculus:{ e.LNAME, e.FNAME | EMPLOYEE(e) AND NOT(EXISTS w) ( WORKS_ON(w)ANDw.ESSN=e.SSN ) }Domain relational Calculus:{ qs | (EXISTS t) ( EMPLOYEE(qrstuvwxyz) AND NOT(EXISTS a) (WORKS_ON(abc) AND a=t )) }

Preview Mode

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

Report

Study Now!

Document Details

Related Documents

Solution Manual for Fundamentals of Database Systems , 7th 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