Skip to content
Textbook Guides

Solution Manual For Fundamentals Of Database Systems, 6th Edition

Solution Manual For Fundamentals Of Database Systems, 6th Edition is packed with detailed solutions to help you grasp concepts effortlessly.

Preview 31 of 155 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

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

    Chapter 1: Databases and Database Users 2 university administration's use. (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 that 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. 1. Small internal utility to locate files 2. 2. Small single user application that does not require security (such as a customized calculator 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’ (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. b. Can you restructure the columns in COURSE, SECTION, and PREREQUISITE tables so that only one column will need to be updated? Formatted: Bullets and Numbering Formatted: Bullets and Numbering

  4. Page 4
    Read page 4 as text

    Chapter 1: Databases and Database Users 3 Answer: 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.

  6. Page 6
    Read page 6 as text

    Chapter 2: Database System Concepts and Architecture 2 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 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: The Relational Data Model and Relational Database Constraints 1 CHAPTER 3: THE RELATIONAL DATA MODEL AND RELATIONAL DATABASE CONSTRAINTS Answers to Selected Exercises 3.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'. (d) Violates both the entity integrity and referential integrity. Violates entity integrity

  8. Page 8
    Read page 8 as text

    Chapter 3: The Relational Data Model and Relational Database Constraints 2 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. 3.12 - Consider the AIRLINE relational database schema shown in Figure 3.8, which describes a database for airline flight information. Each FLIGHT 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 times and airports, and has many LEG_INSTANCEs -- one for each DATE on which the flight travels. FARES are kept for each flight. For each leg instance, SEAT_RESERVATIONs are kept, as is the AIRPLANE used in the leg, and the actual arrival and departure times and airports. 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 land. 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?

  9. Page 9
    Read page 9 as text

    Chapter 3: 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 for 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 3.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

  10. Page 10
    Read page 10 as text

    Chapter 3: The Relational Data Model and Relational Database Constraints 4 examine the rules that the university uses for section numbering. For example, if the sections 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 other hand, all sections (of any course) have unique numbers during a particular semester only, then the candidate key would be {Univ_Section#, Semester}. 3.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 :

  11. Page 11
    Read page 11 as text

    Chapter 3: 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. 3.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: 3.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)

  12. Page 12
    Read page 12 as text

    Chapter 3: The Relational Data Model and Relational Database Constraints 6 Specify 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 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: 3.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. 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. 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. 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. Formatted: Bullets and Numbering

  13. Page 13
    Read page 13 as text

    Chapter 3: The Relational Data Model and Relational Database Constraints 7 d. d. In general, if the each attribute has an independent logical existence based on the application, it would make sense to store it in a separate column otherwise there is no clear advantage. For example, SSN need not be split into its component unless we are using the subsequences to make deductions about validity, geography, etc. In the two cases above, it made 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 may be from a different city and state (or province) from the local phone. A possible tuple of the relation is shown below: Name SSN LocalPhone Address CellPhone Age GPA George Shaw William Edwards 123 - 45 - 6789 555 - 1234 123 Main St., Anytown, CA 94539 555 - 4321 19 3.75 a. Identify the critical missing information from the LocalPhone and CellPhone attributes as shown in the example above. (Hint: How do call someone who lives in a different state or province?) b. Would you store this additional information in the LocalPhone and CellPhone attributes or add new attributes to the schema for STUDENT? c. Consider the Name attribute. What are the advantages and disadvantages of splitting this field 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 a single attribute and when to split the information. Answer: a. A combination of first name, last name, and home phone may address the issue assuming that there are no two students with identical names sharing a home phone line. It also assumes that every student has a home phone number. Another solution may be to use first name, last name, and home zip code. This again has a potential for duplicates, which would be very rare within one university. An extreme solution is to use a combination of characters from 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 in the database need to be appropriately updated, and that can take a long time in a large database. Also, the new primary key must remain unique. [Footnote: Name change is an example of where our database must be able to model the natural world. In this case, we recognize that the name change can occur regardless of whether it is due to marriage, or a consequence 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 to the concept of generated keys, also known as surrogate keys. Specifically, we can use surrogate keys instead of keys that occur naturally in the database. Some database professionals 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 creation of rows (tuples). There are many advantages and disadvantages that are often been argued in design sessions. The main advantage is that it gives us an invariant key without any worries about choosing a unique primary key. The main disadvantages of surrogate keys are that they do not have a business meaning (making some aspects of database management challenging) and that they are slightly less efficient (because they require another pass when inserting a row because the key often needs to be returned to the application after a row is

  14. Page 14
    Read page 14 as text

    Chapter 3: The Relational Data Model and Relational Database Constraints 8 inserted). 3.20 - Recent changes in privacy laws have disallowed organizations from using SSN to identify individuals unless certain restrictions are satisfied. As a result, most US universities cannot use SSNs as primary keys (except for financial data). In practice, StudentID, a unique ID, a unique identifier, assigned to every student, is likely to be used as the primary key rather than SSN since StudentID is usable across all aspects of the system. a. Some database designers are reluctant to use generated keys (also known as surrogate keys) for primary keys (such as StudentID) because they are artificial. Can you propose any natural choices of keys that can be used to store the student record in a UNIVERSITY database? b. Suppose that you were able to guarantee uniqueness of a natural key that included last name. Are you guaranteed that the last name will not change during the lifetime of the database? If the last name last name can change, what solutions can you propose for creating 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 pieces of their name. In a practical use, it is not likely that the user will know the correct primary key for a given student and so we must consider how a user will locate the correct row without this information. If we were to collapse the name into a single attribute, then we have complicated any sort of “lookup by name” query; such a query would then require partial string matching and any results might not disambiguate between FirstName and LastName. Therefore, a practical system should allow name searches by FirstName and LastName; we must leave MiddleInitial separated still to avoid ambiguities from combining these pieces together. b. A single attribute Phone# would no longer suffice if a student were able to have multiple phone numbers. We could possibly have multiple rows for a single student to allow this to happen, but then we have violated key principles of database design (e.g. having redundant data). 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 is not most desirable because most students will not have all three of these attributes, and we will have many valueless key/attribute pairs. An excellent solution would be add an additional relation Phone# (SSN, Type, Number) while removing PhoneNumber from the Student relationship. This new relationship would allow the one - to - many relationship from students to phone numbers without creating redundant data or wasting space on sparse, valueless attributes.

  15. Page 15
    Read page 15 as text

    Chapter 4: Basic SQL 1 CHAPTER 4: Basic SQL Answers to Selected Exercises 4.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) REFERENCES COURSE (CourseNumber), FOREIGN KEY (PrerequisiteNumber) REFERENCES COURSE (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) REFERENCES COURSE (CourseNumber) ); CREATE TABLE GRADE_REPORT ( StudentNumber INTEGER NOT NULL, SectionIdentifier INTEGER NOT NULL, Grade CHAR, PRIMARY KEY (StudentNumber, SectionIdentifier), FOREIGN KEY (StudentNumber) REFERENCES STUDENT (StudentNumber), FOREIGN KEY (SectionIdentifier) REFERENCES SECTION (SectionIdentifier) ); 4.6 - Repeat Exercise 4.5, but use the AIRLINE schema of Figure 3.8. Answer:

  16. Page 16
    Read page 16 as text

    Chapter 4: Basic SQL 2 The 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) REFERENCES AIRPORT (AIRPORT_CODE), FOREIGN KEY (ARRIVAL_AIRPORT_CODE) REFERENCES AIRPORT (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) REFERENCES FLIGHT_LEG (FLIGHT_NUMBER, LEG_NUMBER), FOREIGN KEY (AIRPLANE_ID) REFERENCES AIRPLANE (AIRPLANE_ID), FOREIGN KEY (DEPARTURE_AIRPORT_CODE) REFERENCES AIRPORT (AIRPORT_CODE), FOREIGN KEY (ARRIVAL_AIRPORT_CODE) REFERENCES AIRPORT (AIRPORT_CODE) ); CREATE TABLE FARES ( FLIGHT_NUMBER VARCHAR(6) NOT NULL, FARE_CODE VARCHAR(10) NOT NULL,

  17. Page 17
    Read page 17 as text

    Chapter 4: Basic SQL 3 AMOUNT 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) REFERENCES AIRPLANE_TYPE (TYPE_NAME), FOREIGN KEY (AIRPORT_CODE) REFERENCES AIRPORT (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) REFERENCES LEG_INSTANCE (FLIGHT_NUMBER, LEG_NUMBER, LEG_DATE) ); 4.7 - Consider the LIBRARY relational database schema of Figure 4.6. Choose the appropriate action (reject, cascade, set to null, set to default) for each referential integrity constraint, both for the deletion of a referenced tuple, and for the update of a primary key attribute value in a referenced tuple. Justify your choices. Answer: Below are possible choices. In general, if it is not clear which action to choose, REJECT should be chosen, since it will not permit automatic changes to happen (by update propagation) that may be unintended. BOOK_AUTHORS.(BookId) -- > BOOK.(BookId) CASCADE on both DELETE or UPDATE (since this corresponds to a multi - valued attribute of BOOK (see the solution to Exercise 6.27); hence, if a BOOK is deleted, or the value of its BookId is updated (changed), the deletion or change is automatically propagated to the referencing BOOK_AUTHORS tuples) BOOK.(PublisherName) -- > PUBLISHER.(Name) REJECT on DELETE (we should not delete a PUBLISHER tuple which has existing BOOK tuples that reference the PUBLISHER) CASCADE on UPDATE (if a PUBLISHER's Name is updated, the change should be propagated 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 is updated (changed), the deletion or change is automatically propagated to the referencing BOOK_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 is updated (changed), the deletion or change is automatically propagated to the referencing

  18. Page 18
    Read page 18 as text

    Chapter 4: Basic SQL 4 BOOK_COPIES tuples) BOOK_LOANS.(CardNo) -- > BORROWER.(CardNo) CASCADE on both DELETE or UPDATE (if a BORROWER tuple is deleted, or the value of its CardNo is updated (changed), the deletion or change is automatically propagated to the referencing BOOK_LOANS tuples) (Note: One could also choose REJECT on DELETE, with the idea that if a BORROWER is deleted, it is necessary first to make a printout of all BOOK_LOANS outstanding before deleting the BORROWER; in this case, the tuples in BOOK_LOANS that reference the BORROWER being deleted would first be explicitly deleted 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 of its BranchId is updated (changed), the deletion or change is automatically propagated to the 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 of its BranchId is updated (changed), the deletion or change is automatically propagated to the referencing BOOK_LOANS tuples) (Note: One could also choose REJECT on DELETE) 4.8 - Write appropriate SQL DDL statements for declaring the LIBRARY relational database schema 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 UPDATE CASCADE ); 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,

  19. Page 19
    Read page 19 as text

    Chapter 4: Basic SQL 5 BookId 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 the enforcement technique you suggest difficult to implement? Can the constraint checks be executed 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 constraint is to create an index on the combination of attributes that form each key (primary or secondary). Before inserting a new record (tuple), each index is searched to check that no value currently exists in the index that matches the key value in the new record. If this is the case, the record is inserted successfully. For checking the foreign key constraint, an index on the primary key of each referenced relation will make this check relatively efficient. Whenever a new record is inserted in a referencing relation , its foreign key value is used to search the index for the primary key of the referenced relation, and if the referenced record exists, then the new 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 key of each referencing relation so as to be able to determine efficiently whether any records reference the record being deleted. If the indexes described above do not exist, and no alternative access structure (for example, hashing) is used in their place, then it is necessary to do linear searches to check 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 of these 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 EMPLOYEE VALUES ('Robert', 'F', 'Scott', '943775543', '21 - JUN - 42', '2365 Newcastle Rd, Bellaire, TX', M, 58000, '888665555', 1)

  20. Page 20
    Read page 20 as text

    Chapter 4: Basic SQL 6 (b) Insert < 'ProductA', 4, 'Bellaire', 2 > into PROJECT. INSERT INTO PROJECT VALUES ('ProductA', 4, 'Bellaire', 2) (c) Insert < 'Production', 4, '943775543', '01 - OCT - 88' > into DEPARTMENT. INSERT INTO DEPARTMENT VALUES ('Production', 4, '943775543', '01 - OCT - 88') (d) Insert < '677678989', null, '40.0' > into WORKS_ON. INSERT INTO WORKS_ON VALUES ('677678989', NULL, '40.0') (e) Insert < '453453453', 'John', M, '12 - DEC - 60', 'SPOUSE' > into DEPENDENT. INSERT INTO DEPENDENT VALUES ('453453453', 'John', M, '12 - DEC - 60', 'SPOUSE') (f) Delete the WORKS_ON tuples with ESSN= '333445555'. DELETE FROM WORKS_ON WHERE ESSN= '333445555' (g) Delete the EMPLOYEE tuple with SSN= '987654321'. DELETE FROM EMPLOYEE WHERE SSN= '987654321' (h) Delete the PROJECT tuple with PNAME= 'ProductX'. DELETE FROM PROJECT WHERE PNAME= 'ProductX' (i) Modify the MGRSSN and MGRSTARTDATE of the DEPARTMENT tuple with DNUMBER= 5 to '123456789' and '01 - OCT - 88', respectively. UPDATE DEPARTMENT SET MGRSSN = '123456789', MGRSTARTDATE = '01 - OCT - 88' WHERE DNUMBER= 5 (j) Modify the SUPERSSN attribute of the EMPLOYEE tuple with SSN= '999887777' to '943775543'. UPDATE EMPLOYEE SET SUPERSSN = '943775543' WHERE SSN= '999887777' (k) Modify the HOURS attribute of the WORKS_ON tuple with ESSN= '999887777' and PNO= 10 to '5.0'. UPDATE WORKS_ON SET HOURS = '5.0' WHERE ESSN= '999887777' AND PNO= 10 4.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.

  21. Page 21
    Read page 21 as text

    Chapter 4: Basic SQL 7 (d) Retrieve the name and transcript of each senior student (Class=5) majoring in COSC. 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 who have a grade of A in all their courses). (f) Retrieve the names and major departments of all students who do not have any grade of A in any of their courses. Answers: (a) SELECT Name FROM STUDENT WHERE Major='COSC' (b) SELECT CourseName FROM COURSE, SECTION WHERE COURSE.CourseNumber=SECTION.CourseNumber AND Instructor='King' AND (Year='85' OR Year='86') Another possible SQL query uses nesting as follows: SELECT CourseName FROM COURSE WHERE CourseNumber IN ( SELECT CourseNumber FROM SECTION WHERE Instructor='King' AND (Year='85' OR Year='86') ) (c) SELECT CourseNumber, Semester, Year, COUNT(*) FROM SECTION, GRADE_REPORT WHERE Instructor='King' AND SECTION.SectionIdentifier=GRADE_REPORT.SectionIdentifier GROUP BY CourseNumber, Semester, Year (d) SELECT Name, CourseName, C.CourseNumber, CreditHours, Semester, Year, Grade FROM STUDENT ST, COURSE C, SECTION S, GRADE_REPORT G WHERE Class=5 AND Major='COSC' AND ST.StudentNumber=G.StudentNumber AND G.SectionIdentifier=S.SectionIdentifier AND S.CourseNumber=C.CourseNumber (e) SELECT Name, Major FROM STUDENT WHERE NOT EXISTS ( SELECT * FROM GRADE_REPORT WHERE StudentNumber= STUDENT.StudentNumber AND NOT(Grade='A')) (f) SELECT Name, Major FROM STUDENT WHERE NOT EXISTS ( SELECT * FROM GRADE_REPORT WHERE StudentNumber= STUDENT.StudentNumber AND Grade='A' ) 4.13 - Write SQL update statements to do the following on the database schema shown in Figure 1.2. (a) Insert a new student <'Johnson', 25, 1, 'MATH'> in the database. (b) Change the class of student 'Smith' to 2.

  22. Page 22
    Read page 22 as text

    Chapter 4: Basic SQL 8 (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 STUDENT VALUES ('Johnson', 25, 1, 'MATH') (b) UPDATE STUDENT SET CLASS = 2 WHERE Name='Smith' (c) INSERT INTO COURSE VALUES ('Knowledge Engineering','COSC4390', 3,'COSC') (d) DELETE FROM STUDENT WHERE Name='Smith' AND StudentNumber=17 4.14 – No solution provided 4.15 - Consider the EMPLOYEE table’s constraint EMPSUPERFK as specified in Figure 4.2 is changed to read as follows: CONSTRAINT EMPSUPERFK FOREIGN 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 in Figure 5.6? DELETE EMPLOYEE WHERE LNAME = ‘Borg’ b. b. Is it better to CASCADE or SET NULL in case of EMPSUPERFK constraint ON DELETE? Answers: a) The James E. Borg entry is deleted from the table, and each employee with him as a supervisor is also (and their supervisees, and so on). In total, 8 rows are deleted and the table is empty. b) It is better to SET NULL, since an employee is not fired (DELETED) when their supervisor is deleted. Instead, their SUPERSSN should be SET NULL so that they can later get a new supervisor. 4.16 - Write SQL statements to create a table EMPLOYEE_BACKUP backup of EMPLOYEE table shown in Figure 3.6. Answer: INSERT INTO EMPLOYEE_BACKUP VALUES ( SELECT * FROM EMPLOYEE ) Formatted: Bullets and Numbering Formatted: Bullets and Numbering

  23. Page 23
    Read page 23 as text

    Chapter 5: More SQL: Complex Queries, Triggers, Views, and Schema Modification 1 CHAPTER 5: More SQL: Complex Queries, Triggers, Views, and Schema Modification Answers to Selected Exercises 5.5 - Specify the following additional queries on the database of Figure 3.5 in SQL. Show the query results if applied to the database of Figure 3.6. (a) For each department whose average employee salary is more than $30,000, retrieve the department name and the number of employees working for that department. (b) Suppose we want the number of male employees in each department rather than all employees (as in Exercise 5.4a). Can we specify this query in SQL? Why or why not? Answers: (a) SELECT DNAME, COUNT (*) FROM DEPARTMENT, EMPLOYEE WHERE DNUMBER=DNO GROUP BY DNAME HAVING AVG (SALARY) > 30000 Result: DNAME DNUMBER COUNT(*) Research 5 4 Administration 4 3 Headquarters 1 1 (b) The query may still be specified in SQL by using a nested query as follows (not all implementations may support this type of query): SELECT DNAME, COUNT (*) FROM DEPARTMENT, EMPLOYEE WHERE DNUMBER=DNO AND SEX='M' AND DNO IN ( SELECT DNO FROM EMPLOYEE GROUP BY DNO HAVING AVG (SALARY) > 30000 ) GROUP BY DNAME Result: DNAME DNUMBER COUNT(*) Research 5 3 Administration 4 1 Headquarters 1 1 5.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 who have a grade of A in all their courses). (b) Retrieve the names and major departments of all students who do not have any grade of A in any of their courses.

  24. Page 24
    Read page 24 as text

    Chapter 5: More SQL: Complex Queries, Triggers, Views, and Schema Modification 2 Answers: (a) SELECT Name, Major FROM STUDENT WHERE NOT EXISTS ( SELECT * FROM GRADE_REPORT WHERE StudentNumber= STUDENT.StudentNumber AND NOT(Grade='A')) (b) SELECT Name, Major FROM STUDENT WHERE NOT EXISTS ( SELECT * FROM GRADE_REPORT WHERE StudentNumber= STUDENT.StudentNumber AND Grade='A' ) 5.7 - In SQL, specify the following queries on the database specified in Figure 3.5 using the concept of nested queries and the concepts described in this chapter. a. Retrieve the names of all employees who work in the department that has the employee 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 employee who 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.

  25. Page 25
    Read page 25 as text

    Chapter 6: The Relational Algebra and Relational Calculus 1 CHAPTER 6: THE RELATIONAL ALGEBRA AND RELATIONAL CALCULUS Answers to Selected Exercises 6.15 - Show the result of each of the sample queries in Section 6.5 as it would apply to the database 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 ADDRESS John Smith 731 Fondren, Houston, TX Franklin Wong 638 Voss, Houston, TX Ramesh Narayan 975 Fire Oak, Humble, TX Joyce English 5631 Rice, Houston, TX (QUERY 2) For every project located in 'Stafford', list the project number, the controlling department number, and the department manager's last name, address, and birth date. Result: PNUMBER DNUM LNAME ADDRESS BDATE 10 4 Wallace 291 Berry, Bellaire, TX 20 - JUN - 31 30 4 Wallace 291 Berry, Bellaire, TX 20 - JUN - 31 (QUERY 3) Find the names of all employees who work on all the projects controlled by department 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 whose last name is 'Smith' as a worker or as a manager of the department that controls the project. Result: PNO 1 2 (QUERY 5) List the names of all employees with two or more dependents. Result: LNAME FNAME Smith John Wong Franklin (QUERY 6) List the names of employees who have no dependents. Result: LNAME FNAME Zelaya Alicia Narayan Ramesh English Joyce Jabbar Ahmad Borg James (QUERY 7) List the names of managers who have at least one dependent. Result: LNAME FNAME

  26. Page 26
    Read page 26 as text

    Chapter 6: The Relational Algebra and Relational Calculus 2 Wallace Jennifer Wong Franklin 6.16 - Specify the following queries on the COMPANY relational database schema shown in Figure 3.5, using the relational operators discussed in this chapter. Also show the result of each 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 per week on the 'ProductX' project. (b) List the names of employees who have a dependent with the same first name as themselves. (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 all employees) 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 of employees 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 located in 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 query in multiple ways. We give one possible solution for each query. We use the symbol s for SELECT, P for PROJECT, J for EQUIJOIN, * for NATURAL JOIN, and f for FUNCTION. (a) EMP_W_X < -- ( s PNAME='ProductX' (PROJECT)) J (PNUMBER),(PNO) (WORKS_ON) EMP_WORK_10 < -- (EMPLOYEE) J (SSN),(ESSN) ( s HOURS>10 (EMP_W_X)) RESULT < -- P LNAME,FNAME ( s DNO=5 (EMP_WORK_10)) Result: LNAME FNAME Smith John English Joyce (b) E < -- (EMPLOYEE) J (SSN,FNAME),(ESSN,DEPENDENT_NAME) (DEPENDENT)

  27. Page 27
    Read page 27 as text

    Chapter 6: The Relational Algebra and Relational Calculus 3 R < -- P LNAME,FNAME (E) Result (empty): LNAME FNAME (c) WONG_SSN < -- P SSN ( s FNAME='Franklin' AND LNAME='Wong' (EMPLOYEE)) WONG_EMPS < -- (EMPLOYEE) J (SUPERSSN),(SSN) (WONG_SSN) RESULT < -- P LNAME,FNAME (WONG_EMPS) Result: LNAME FNAME Smith John Narayan Ramesh English Joyce (d) PROJ_HOURS(PNO,TOT_HRS) < -- PNO f SUM HOURS (WORKS_ON) RESULT < -- P PNAME,TOT_HRS ( (PROJ_HOURS) J (PNO),(PNUMBER) (PROJECT) ) Result: PNAME TOT_HRS ProductX 52.5 ProductY 37.5 ProductZ 50.0 Computerization 55.0 Reorganization 25.0 Newbenefits 55.0 (e) PROJ_EMPS(PNO,SSN) < -- P PNO,ESSN (WORKS_ON) ALL_PROJS(PNO) < -- P PNUMBER (PROJECT) EMPS_ALL_PROJS < -- PROJ_EMPS - : - ALLPROJS (* DIVISION operation *) RESULT < -- P LNAME,FNAME (EMPLOYEE * EMP_ALL_PROJS) Result (empty): LNAME FNAME (f) ALL_EMPS < -- P SSN (EMPLOYEE) WORKING_EMPS(SSN) < -- P ESSN (WORKS_ON) NON_WORKING_EMPS < -- ALL_EMPS - WORKING_EMPS (* DIFFERENCE *) RESULT < -- P LNAME,FNAME (EMPLOYEE * NON_WORKING_EMPS) Result (empty): LNAME FNAME (g) DEPT_AVG_SALS(DNUMBER,AVG_SAL) < -- DNO f AVG SALARY (EMPLOYEE) RESULT < -- P DNUMBER,AVG_SAL ( DEPT_AVG_SALS * DEPARTMENT )

  28. Page 28
    Read page 28 as text

    Chapter 6: The Relational Algebra and Relational Calculus 4 Result: DNUMBER AVG_SAL Research 33250 Administration 31000 Headquarters 55000 (h) RESULT(AVG_F_SAL) < -- f AVG SALARY ( s SEX='F' (EMPLOYEE) ) Result: AVG_F_SAL 31000 (i) E_P_HOU(SSN) < -- P ESSN (WORKS_ON J(PNO),(PNUMBER) ( s PLOCATION='Houston' (PROJECT))) D_NO_HOU < -- P DNUMBER (DEPARTMENT) - P DNUMBER ( s DLOCATION='Houston' (DEPARTMENT)) E_D_NO_HOU < -- P SSN (EMPLOYEE J(PNO),(DNUMBER) (D_NO_HOU)) RESULT_EMPS < -- E_P_HOU - E_D_NO_HOU (* this is set DIFFERENCE *) RESULT < -- P LNAME,FNAME,ADDRESS (EMPLOYEE * RESULT_EMPS) Result: LNAME FNAME ADDRESS Wallace Jennifer 291 Berry, Bellaire, TX (j) DEPT_MANAGERS(SSN)< -- P MGRSSN (DEPARTMENT) EMPS_WITH_DEPENDENTS(SSN) < -- P ESSN (DEPENDENT) RESULT_EMPS < -- DEPT_MANAGERS - EMPS_WITH_DEPENDENTS RESULT < -- P LNAME,FNAME (EMPLOYEE * RESULT_EMPS) Result: LNAME FNAME Borg James 6.17 – No solution provided. 6.18 - Consider the LIBRARY relational schema shown in Figure 6.14, which is used to keep track of books, borrowers, and book loans. Referential integrity constraints are shown as directed arcs in Figure 6.14, as in the notation of Figure 3.7. Write down relational expressions for the following queries on the LIBRARY database: (a) How many copies of the book titled The Lost Tribe are owned by the library branch whose name is "Sharpstown"? (b) How many copies of the book titled The Lost Tribe are owned by each library branch?

  29. Page 29
    Read page 29 as text

    Chapter 6: The Relational Algebra and Relational Calculus 5 (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 DueDate is 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 books loaned out from that branch. (f) Retrieve the names, addresses, and number of books checked out for all borrowers who have more than five books checked out. (g) For each book authored (or co - authored) by "Stephen King", retrieve the title and the number of copies owned by the library branch whose name is "Central". Answer: (Note: We will use S for SELECT, P for PROJECT, * for NATURAL JOIN, - for SET DIFFERENCE , F for AGGREGATE FUNCTION) (a) A < -- BOOKCOPIES * LIBRARY - BRANCH * BOOK RESULT < -- P No_Of_Copies ( S BranchName='Sharpstown' and Title='The Lost Tribe' (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)) * BOOK CENTRAL(BranchId) < -- sBranchName='Central' ( LIBRARY_BRANCH ) RESULT < -- P Title,NoOfCopies ( SK * BOOKCOPIES * CENTRAL ) 6.19 – 6.21: No solutions provided.

  30. Page 30
    Read page 30 as text

    Chapter 6: The Relational Algebra and Relational Calculus 6 6.22 – Consider the two tables T 1 and T 2 shown in Figure 6.15. Show the results of the following operations: Answers: (a) P Q R A B C 10 a 5 10 b 6 10 a 5 10 b 5 25 a 6 25 c 3 (b) P Q R A B C 15 b 8 10 b 6 15 b 8 10 b 5 (c) P Q R A B C 10 a 5 10 b 6 10 a 5 10 b 5 15 b 8 null null null 25 a 6 25 c 3 (d) P Q R A B C 15 b 8 10 b 6 null null null 25 c 3 15 b 8 10 b 5 (e) P Q R 10a 5 15 b 8 25 a 6 10b 6 25 c 3 10b 5 (f) P Q R A B C 10 a 5 10 b 5 6.23 – No solution provided. 6.24 - Specify queries (a), (b), (c), (e), (f), (i), and (j) of Exercise 6.16 in both tuple and domain relational calculus. Answer: (a) Retrieve the names of employees in department 5 who work more than 10 hours per week 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 AND w.PNO=p.PNUMBER AND p.PNAME='ProductX' AND w.HOURS>10 ) }

  31. Page 31
    Read page 31 as text

    Chapter 6: The Relational Algebra and Relational Calculus 7 Domain relational Calculus: { qs | EMPLOYEE(qrstuvwxyz) AND z=5 AND (EXISTS a) (EXISTS b) (EXISTS e) (EXISTS f) (EXISTS g) ( WORKS_ON(efg) AND PROJECT(abcd) AND t=e AND f=b AND a='ProductX' AND g>10 ) } (b) List the names of employees who have a dependent with the same first name as themselves. Tuple relational Calculus: { e.LNAME, e.FNAME | EMPLOYEE(e) AND (EXISTS d) ( DEPENDENT(d) AND e.SSN=d.ESSN AND e.FNAME=d.DEPENDENT_NAME ) } Domain relational Calculus: { qs | (EXISTS t) (EXISTS a) (EXISTS b) ( EMPLOYEE(qrstuvwxyz) AND DEPENDENT(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) AND s.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) AND EMPLOYEE(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) AND w.ESSN=e.SSN ) } Domain relational Calculus: { qs | (EXISTS t) ( EMPLOYEE(qrstuvwxyz) AND NOT(EXISTS a) ( WORKS_ON(abc) AND a=t ) ) }

124 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.