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,
foreign key (sid) references S(sid)
DescriptionIn this final assignment, the students will demonstrate their ability to apply two ma
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. Thisprogram will have two classes, a LineItem class and a Transaction class. Th
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
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