draw an ER diagram that correctly represents the business rules. Assume that the only business rules that hold are those stated, and that no additional business rules hold.

INSTRUCTIONS TO CANDIDATES

ANSWER ALL QUESTIONS

Problem 1 (40 points) Business rules to ER diagrams

For each question below, draw an ER diagram that correctly represents the business rules. Assume that the only business rules that hold are those stated, and that no additional business rules hold. Clearly mark all key and participation constraints using the notation presented in class.

 (10 points) Countries host Olympics. A country is identified by its name. An Olympics event is identified by a year in which it is held and the season (Winter or Summer). Each Olympics event is hosted by a specific country. We only record countries that hosted Olympics in our database. A country may host multiple Olympics (e.g., Japan hosted the Winter Olympics in 1998 and in 1972), and all such occurrences must be recorded

(10 points) A dish combines two or more ingredients by cooking them using some cooking method, at some temperature. Dishes, ingredients and cooking methods all have unique names. Ingredients are additionally described by a calorie count per unit weight. Each dish is prepared using at most one cooking method. Only ingredients that are used in some dish are recorded in our database

(10 points) Bands, identified by a name and a year in which they were formed, go on tours. A tour is described by a name and is associated with a start and end dates. You may assume that tour dates for a particular band do not overlap (i.e., a band is on at most one tour on any particular day). Further, assume that if a band is removed from our database, then so are all of that band's tours.

(10 points) An artist is uniquely identified by her A painting is uniquely identified by the combination of name and year when it was painted. Artists paint paintings. Each painting is painted by one artist, but for some of the paintings the name of the artist is unknown. A museum is uniquely identified by its name. Every painting is owned by a particular museum. We record the year in which the painting was acquired by the museum that currently owns it in our database.

Problem 2 (30 points): ER to relational

 Consider ER diagrams below. Write one or several create table statements that implement the entity sets, relationship sets and constraints encoded by the diagrams. Give a brief explanation of your implementation, explaining how you encoded the

constraints specified by the diagram. If a constraint cannot be encoded – you must state so explicitly.

 

• (15 points) Assume that id attributes are underlined, and are of type integer. Assume that name attributes are of type char(32). Arrow from “A” to “rel” is not bold, arrow from “C” to “rel” is bold

 

• (15 points) Assume that id attributes are underlined, and are of type integer. Assume that name attributes are of type char(32). Arrow from “A” to “rel1” is not bold, arrow from “C” to “rel2” is bold

 

Problem 3 (30 points): Relational to ER

Consider create table statements below. In each question, draw an ER diagram from which these create table statements could have been derived. Be sure to mark any key and participation constraints.

 

(a)  (15 points)

 

create table Foo (

X integer primary key, Y integer unique,

Z            integer not null, foreign key (Y) references Bar(Y)

);

 

create table Bar (

Y integer primary key

);

 

create table Baz (

X integer primary key,

W integer not null,

foreign key (X) references Foo(X)

);

(b)  (15 points)

create table R (

rid      integer primary key,

name       varchar(64) not null

)

 

create table S (

sid        integer primary key, name varchar(64) not null, rid  integer not null unique, foreign key (rid) references R(rid)

)

 

create table T (

tid      integer primary key,

val        float,

sid        integer,

foreign key (sid) references S(sid)

)

Attachments:

Related Questions

. Introgramming & Unix Fall 2018, CRN 44882, Oakland University Homework Assignment 6 - Using Arrays and Functions in C

DescriptionIn this final assignment, the students will demonstrate their ability to apply two ma

. The standard path finding involves finding the (shortest) path from an origin to a destination, typically on a map. This is an

Path finding involves finding a path from A to B. Typically we want the path to have certain properties,such as being the shortest or to avoid going t

. Develop a program to emulate a purchase transaction at a retail store. This program will have two classes, a LineItem class and a Transaction class. The LineItem class will represent an individual

Develop a program to emulate a purchase transaction at a retail store. Thisprogram will have two classes, a LineItem class and a Transaction class. Th

. SeaPort Project series For this set of projects for the course, we wish to simulate some of the aspects of a number of Sea Ports. Here are the classes and their instance variables we wish to define:

1 Project 1 Introduction - the SeaPort Project series For this set of projects for the course, we wish to simulate some of the aspects of a number of

. Project 2 Introduction - the SeaPort Project series For this set of projects for the course, we wish to simulate some of the aspects of a number of Sea Ports. Here are the classes and their instance variables we wish to define:

1 Project 2 Introduction - the SeaPort Project series For this set of projects for the course, we wish to simulate some of the aspects of a number of