Your company has been awarded a contract to implement an information system for

Question

Your company has been awarded a contract to implement an information system for a
foreign company. The contract includes a speci cation of the relevant information in
the form of the following ER diagram.

Convert the ER diagram to a relational schema on the next page. Your schema can
be described by a set of CREATE TABLE" commands or by using the UML-like box
notation used in class. Using a single sentence for each case, explain any constraints
reflected in the ER diagram that are not captured by your relational schema.

Explanation / Answer

1. The ER diagram is shown in Figure 2.3.

2. If the drug is to be sold at a fixed price we can add the price attribute to the Drug entity set and eliminate the Sell relationship set.

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.

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. Draw the ER diagram for the database.

Related Questions

Navigate

• Browse (All)
• Browse Y
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.