Example of Database Design and Implementation
This example develops a new system that tracks poets, poems, anthologies and sales.
Phase 1: Analysis
The objective is 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.
- 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.
- 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:
- Poet
- Poem
- Publication
- Sale
- 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:
- Poet: first name, surname, address, email address
- Poem: poem title, poem contents
- Publication: title, price
- Sales: date, amount
- 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:
- poem-publication
- 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));