The following ER diagram describes a database about bands and their tours within

Question

The following ER diagram describes a database about bands and their tours within the United States. A tour consists of a sequence of cities visited by a band. We assume that no city is visited twice on a single tour, and on one date, a band can visit only one city. Some of the entity sets are weak, and some of the relationships are supporting many-one relationships, but all double rectangles and double diamonds are not shown. [You may recognize this diagram from a Gradiance question.] Give the SQL CREATE TABLE statements needed to implement this database as a normalized set of relations. Clearly state any additional assumptions you make.

Explanation / Answer

According to the E-R diagarm provided we will have following tables in our database:

1) Bands_Table(bandNumber, bandName, tourDate)

2) Tours_Table(tourDate, tourNumber, cityName)

3) Place_Table(cityName, stateName)

Here tourDate is common between Bands_Table AND Tours_Table, through which we can get data from both the tables by applying join. In Tours_Table tourDate will always different but tourNumber can we same with some tourDate.

Sql query for creating Bands_Table is:

CREATE TABLE Bands_Table
(
bandNumber int NOT NULL,
bandName varchar(255) NOT NULL,
tourDate DATE,

PRIMARY KEY (bandNumber)
)

Sql query for creting Tours_Table is:

CREATE TABLE Tours_Table
(
tourDate DATE,
tourNumber int NOT NULL,
cityName varchar(255),

PRIMARY KEY (tourDate)
)

Sql query for creating Place_Table is:

CREATE TABLE Place_Table
(
cityName varchar(255),
stateName varchar(255)
)

Related Questions

Navigate

• Browse (All)
• Browse T
• Subjects

---