Create table pri phy patient ssn char

DATABASE MANAGEMENT SYSTEMS SOLUTIONS MANUAL

Raghu Ramakrishnan et al.

Answer 2.1 No answer provided yet.

Exercise 2.2 A university database contains information about professors (identified by social security number, or SSN) and courses (identified by courseid). Professors teach courses; each of the following situations concerns the Teaches relationship set. For each situation, draw an ER diagram that describes it (assuming that no further constraints hold).

5. Every professor teaches exactly one course (no more, no less), and every course must be taught by some professor.

6. Now suppose that certain courses can be taught by a team of professors jointly, but it is possible that no one professor in a team can teach the course. Model this situation, introducing additional entity sets and relationship sets if necessary.

Professors have an SSN, a name, an age, a rank, and a research specialty.

Projects have a project number, a sponsor name (e.g., NSF), a starting date, an ending date, and a budget.

Each project is worked on by one or more graduate students (known as the project’s research assistants).

When graduate students work on a project, a professor must supervise their work on the project. Graduate students can work on multiple projects, in which case they will have a (potentially different) supervisor for each one.

Each graduate student has another, more senior graduate student (known as a student advisor) who advises him or her on what courses to take.

Design and draw an ER diagram that captures the information about the university. Use only the basic ER model here, that is, entities, relationships, and attributes. Be sure to indicate any key and participation constraints.

grad

employee; a child must be identified uniquely by name when the parent (who is an employee; assume that only one parent works for the company) is known. We are not interested in information about a child once the parent leaves the company.

Draw an ER diagram that captures this information.

Each album that is recorded on the Notown label has a title, a copyright date, a format (e.g., CD or MC), and an album identifier.

Each song recorded at Notown has a title and an author.

Design a conceptual schema for Notown and draw an ER diagram for your schema. The following information describes the situation that the Notown database must model. Be sure to indicate all key and cardinality constraints and any assumptions that you make. Identify any constraints that you are unable to capture in the ER diagram and briefly explain why you could not express them.

Answer 2.5 The ER diagram is shown in Figure 2.2.

Every airplane has a registration number, and each airplane is of a specific model.

The airport accommodates a number of airplane models, and each model is iden-tified by a model number (e.g., DC-10) and has a capacity and a weight.

The airport has a number of tests that are used periodically to ensure that air-planes are still airworthy. Each test has a Federal Aviation Administration (FAA) test number, a name, and a maximum possible score.

The FAA requires the airport to keep track of each time that a given airplane is tested by a given technician using a given test. For each testing event, the information needed is the date, the number of hours the technician spent doing the test, and the score that the airplane received on the test.

Exercise 2.7 The Prescriptions-R-X chain of pharmacies has offered to give you a free lifetime supply of medicines if you design its database. Given the rising cost of health care, you agree. Here’s the information that you gather:

Patients are identified by an SSN, and their names, addresses, and ages must be recorded.

Every patient has a primary physician. Every doctor has at least one patient.

Each pharmacy sells several drugs and has a price for each. A drug could be sold at several pharmacies, and the price could vary from one pharmacy to another.

2. How would your design change if each drug must be sold at a fixed price by all pharmacies?

3. How would your design change if the design requirements change as follows: If a doctor prescribes the same drug for the same patient more than once, several such prescriptions may have to be stored.

date

supervisor
name phone_num

3. The date information can no longer be modeled as an attribute of Prescription. We have to create a new entity set called Prescription date and make Prescription a 4-way relationship set that involves this additional entity set.

Exercise 2.8 Although you always wanted to be an artist, you ended up being an expert on databases because you love to cook data and you somehow confused ‘data base’ with ‘data baste.’ Your old love is still there, however, so you set up a database company, ArtBase, that builds a product for art galleries. The core of this product is a database with a schema that captures all the information that galleries need to maintain. Galleries keep information about artists, their names (which are unique), birthplaces, age, and style of art. For each piece of artwork, the artist, the year it was made, its unique title, its type of art (e.g., painting, lithograph, sculpture, photograph), and its price must be stored. Pieces of artwork are also classified into groups of various kinds, for example, portraits, still lifes, works by Picasso, or works of the 19th century; a given piece may belong to more than one group. Each group is identified by a name (like those above) that describes the group. Finally, galleries keep information about customers. For each customer, galleries keep their unique name, address, total amount of dollars they have spent in the gallery (very important!), and the artists and groups of art that each customer tends to like.

Answer 3.1 A relation schema can be thought of as the basic information describing a table or relation. This includes a set of column names, the data types associated with each column, and the name associated with the entire table. For example, a relation schema for the relation called Students could be expressed using the following representation:

Students(sid: string, name: string, login: string,
age: integer, gpa: real)

The relation cardinality is the number of tuples in the relation.

The relation degree is the number of fields (or columns) in the relation.

The Relational Model 15

Exercise 3.3 Does the relational model, as seen by an SQL query writer, provide physical and logical data independence? Explain.

Exercise 3.5 Consider the instance of the Students relation shown in Figure 3.1.

16 Chapter 3

key, not something that only might be a key. The instance shown is just one possible“snapshot” of the relation. At other times, the same relation may have an instance (or snapshot) that contains a totally different set of tuples, and we cannot make predictions about those instances based only upon the instance that we are given.

2. Give an example of a (plausible) constraint involving one or more of these relations that is not a primary key or foreign key constraint.

Answer 3.7 There is no reason for a foreign key constraint (FKC) on the Students, Faculty, Courses, or Rooms relations. These are the most basic relations and must be free-standing. Special care must be given to entering data into these base relations.

1. Give an example of a foreign key constraint that involves the Dept relation. What are the options for enforcing this constraint when a user attempts to delete a Dept tuple?

The Relational Model 17

4. Write an SQL statement to add ‘John Doe’ as an employee with eid = 101,

age = 32 and salary = 15, 000.

Answer 3.8 Answer omitted.

sid name login age gpa

1. Modify this query so that only the login column is included in the answer.

2. If the clause WHERE S.gpa >= 2 is added to the original query, what is the set of

FROM Students S

WHERE S.age < 18

that the ssn field of Manages be non-null?

18 Chapter 3

We cannot capture the total participation constraint of A in R. This is because we cannot ensure that every key a1 appears in R without the use of checks.

Exercise 3.12 Consider the scenario from Exercise 2.2 where you designed an ER diagram for a university database. Write SQL statements to create the corresponding relations and capture as many of the constraints as possible. If you cannot capture some constraints, explain why.

Exercise 3.14 Consider the scenario from Exercise 2.4 where you designed an ER diagram for a company database. Write SQL statements to create the corresponding relations and capture as many of the constraints as possible. If you cannot capture some constraints, explain why.

Answer 3.14 Answer omitted.

Exercise 3.16 Translate your ER diagram from Exercise 2.6 into a relational schema, and show the SQL statements needed to create the relations, using only key and null constraints. If your translation cannot capture any constraints in the ER diagram, explain why.

In Exercise 2.6, you also modified the ER diagram to include the constraint that tests on a plane must be conducted by a technician who is an expert on that model. Can you modify the SQL statements defining the relations obtained by mapping the ER diagram to check this constraint?

1. CREATE TABLE Pri Phy Patient ( ssn CHAR(11),
name CHAR(20),
age INTEGER,
address CHAR(20),
phy ssn CHAR(11),
PRIMARY KEY (ssn),
FOREIGN KEY (phy ssn) REFERENCES Doctor )

2. CREATE TABLE Prescription ( ssn CHAR(11),
phy ssn CHAR(11),
date CHAR(11),
quantity INTEGER,
trade name CHAR(20),
pharm id CHAR(11),
PRIMARY KEY (ssn, phy ssn),
FOREIGN KEY (ssn) REFERENCES Patient, FOREIGN KEY (phy ssn) REFERENCES Doctor) FOREIGN KEY (trade name, pharm id) References Make Drug)

PRIMARY KEY (name, pharm id),
FOREIGN KEY (name) REFERENCES Pharmacy,
FOREIGN KEY (pharm id) REFERENCES Pharm co)

Exercise 3.18 Write SQL statements to create the corresponding relations to the ER diagram you designed for Exercise 2.8. If your translation cannot capture any constraints in the ER diagram, explain why.

Exercise 4.2 Given two relations R1 and R2, where R1 contains N1 tuples, R2 con-tains N2 tuples, and N2 > N1 > 0, give the minimum and maximum possible sizes (in tuples) for the result relation produced by each of the following relational algebra expressions. In each case, state any assumptions about the schemas for R1 and R2 that are needed to make the expression meaningful:

(1) R1∪R2, (2) R1∩R2, (3) R1−R2, (4) R1×R2, (5) σa=5(R1), (6) πa(R1), and (7) R1/R2

23