We are to develop a new system that tracks poets, poems, anthologies and sales.

Phase 1: Analysis

The following information is gleaned from speaking to the various stakeholders. They want to develop a database system to track the poets they have recorded, the poems they write, the publications they appear in, as well as the sales to customers that these publications make.

  • A poet can be anybody who wants to be a poet, not necessarily someone who has a poem captured in the system or someone who has even written a poem.
  • Poems can be submitted through a web interface, by email or on paper.
  • All captured poems are written by an associated poet, whose details are already in the system. There can be no poems submitted and stored without a full set of details of the poet.
  • A publication can be a single poem, a poetry anthology, or a work of literary criticism.
  • Customers can sign up through a web interface and may order publications at that point in time, or express interest in receiving updates for possible later purchases.
  • Sales of publications are made to customers whose details are stored in the system. There are no anonymous sales.
  • A single sale can be for one publication, but many publications can also be purchased at the same time. If more than one customer is involved in this sale, it is treated as more than one sale. Each customer has their own sale.
  • Not all publications make sales - some may be special editions, and others simply never sell any copies.

Phase 2: Design

Based on the information and requirements, following initial entities are identified:

  1. Poet
  2. Poem
  3. Publication
  4. Sale
  5. Customer

Next, you need to determine the relationship between these entities.

  • A poet can write many poems. The analysis identified the fact that a poet can be stored in the system even if there are no associated poems. Poems may be captured at a later point in time, or the poet may still be a potential poet. Conversely, many poets could conceivably write a poem, though the poem must have been written by at least one poet.
  • A publication may contain many poems (an anthology) or just one. It can also contain no poems (poetry criticism for example). A poem may or may not appear in a publication.
  • A sale must be for at least one publication, but it may be for many. A publication may or may not have made any sales.
  • A customer may be made for many sales, or none at all. A sale is only made for one and only one customer.

You can identify the following attributes:

  1. Poet: first name, surname, address, email address
  2. Poem: poem title, poem contents
  3. Publication: title, price
  4. Sales: date, amount
  5. Customer: first name, surname, address, email address

There are two many-to-many relationships in the design. These need to be converted into one-to-many relationships before implementation. The intersection entities are

  1. poem-publication
  2. sale-publication

Now, to begin the logical and physical design, you need to add attributes that can create the relationship between the entities and specify primary keys.

1. Poet table

Field Definition
poet code primary key, integer
first name character (30)
surname character (40)
address character (100)
postcode character (20)
email address character (254)

2. Poem table

Field Definition
poem code primary key, integer
poem title character(50)
poem contents text
poet code foreign key, integer

3. Poem-publication table

Field Definition
poem code joint primary key, foreign key, integer
publication code joint primary key, foreign key, integer

4. Publication table

Field Definition
publication code primary key, integer
title character(100)
price numeric(5.2)

5. Sale-publication table

Field Definition
sale code joint primary key, foreign key, integer
publication code joint primary key, foreign key, integer

6. Sale table

Field Definition
sale code primary key, integer
date date
amount numeric(10.2)
customer code foreign key, integer

7. Customer table

Field Definition
customer code primary key, integer
first name character (30)
surname character (40)
address character (100)
postcode character (20)
email address character (254)

Phase 3: Implementation

With the design is complete, it is time to run the CREATE statements.

CREATE DATABASE poets_circle;

CREATE TABLE poet (
poet_code INT NOT NULL,
first_name VARCHAR(30),
surname VARCHAR(40),
address VARCHAR(100),
postcode VARCHAR(20),
email VARCHAR(254),
PRIMARY KEY(poet_code)
);

CREATE TABLE poem(
poem_code INT NOT NULL,
title VARCHAR(50),
contents TEXT,
poet_code INT NOT NULL,
PRIMARY KEY(poem_code),
INDEX(poet_code),
FOREIGN KEY(poet_code) REFERENCES poet(poet_code)
);

CREATE TABLE publication(
publication_code INT NOT NULL,
title VARCHAR(100),
price MEDIUMINT UNSIGNED,
PRIMARY KEY(publication_code)
);

CREATE TABLE poem_publication(
poem_code INT NOT NULL,
publication_code INT NOT NULL,
PRIMARY KEY(poem_code, publication_code),
INDEX(publication_code),
FOREIGN KEY(poem_code) REFERENCES poem(poem_code),
FOREIGN KEY(publication_code) REFERENCES publication(publication_code)
);

CREATE TABLE sales_publication(
sales_code INT NOT NULL,
publication_code INT NOT NULL,
PRIMARY KEY(sales_code, publication_code)
);

CREATE TABLE customer(
customer_code INT NOT NULL,
first_name VARCHAR(30),
surname VARCHAR(40),
address VARCHAR(100),
postcode VARCHAR(20),
email VARCHAR(254),
PRIMARY KEY(customer_code)
);

CREATE TABLE sale(
sale_code INT NOT NULL,
sale_date DATE,
amount INT UNSIGNED,
customer_code INT NOT NULL,
PRIMARY KEY(sale_code),
INDEX(customer_code),
FOREIGN KEY(customer_code) REFERENCES customer(customer_code)
);