Database System
Homework #1 (Chapter 3)
3.11. Suppose that each of the following Update operations is applied directly to the database state
shown in Figure 3.6. Discuss all integrity constraints violated by each operation, if any, and
the different ways of enforcing these constraints.
1
a. Insert <‘Robert’, ‘F’, ‘Scott’, ‘943775543’, ‘1972-06-21’, ‘2365 Newcastle Rd, Bellaire,
TX’, M, 58000, ‘888665555’, 1 > into EMPLOYEE.
b. Insert < ‘ProductA’, 4, ‘Bellaire’, 2 > into PROJECT.
c. Insert < ‘Production’, 4, ‘943775543’, ‘2007-10-01’ > into DEPARTMENT.
d. Insert < ‘677678989’, NULL, ‘40.0’ > into WORKS_ON.
e. Insert < ‘453453453’, ‘John’, ‘M’, ‘1990-12-12’, ‘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 Mgr_ssn and Mgr_start_date of the DEPARTMENT tuple with Dnumber = 5
to ‘123456789’ and ‘2007-10-01’, respectively.
j. Modify the Super_ssn 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’.
2
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)
A trip can be charged to one or more accounts. Specify the foreign keys for this schema,
stating any assumptions you make.
3
3.17. Consider the following relations for a database that keeps track of automobile sales in a car
dealership (OPTION refers to some optional equipment installed on an automobile):
CAR(Serial_no, Model, Manufacturer, Price)
OPTION(Serial_no, Option_name, Price)
SALE(Salesperson_id, Serial_no, Date, Sale_price)
SALESPERSON(Salesperson_id, Name, Phone)
First, specify the foreign keys for this schema, stating any assumptions you make.
Next, populate the relations with a few sample tuples, and then give an example of an
insertion in the SALE and SALESPERSON relations that violates the referential integrity
constraints and of another insertion that does not.
4
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 Local_phone Address Cell_phone Age Gpa
George Shaw 123-45-6789 555-1234 123 Main St., 555-4321 19 3.75
William Edwards Anytown, CA 94539
a. Identify the critical missing information from the Local_phone and Cell_phone
attributes. (Hint: How do you call someone who lives in a different state or province?)
b. Would you store this additional information in the Local_phone and Cell_phone
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 world you recommend for deciding when to store information in a
single attribute and when to split the information?
e. Suppose the student can have between 0 and 5 phones. Suggest two different designs that
allow this type of information.
