Database Answers Header Kyomizu Dera, Japan
ACS Database Schema
Home Ask a Question Careers Data Models FAQs SQL Scripts Search Site Map  
The tutorial example has 4 distinct objects: 
  a Publication, a Magazine, an Article, and a Paragraph. 

A Publication can be a magazine, a book, a journal, or something of a similar nature. 
This example will use a Magazine, which is simply a collection of Articles. 
The final element type is Paragraph. 
Each paragraph belongs to an article and does not have any meaning outside the given 
article (it is a composite relationship). 

Finally, this example will assume that each author only has content in a single publication. 

It will use the following schema: 

create table publications (    
			publication_id     integer 
                       constraint publications_pub_id_nn
                       not null 
                       constraint publications_pub_id_pk
                       primary key,    name               varchar(400)
                       constraint publications_pub_id_nn
                       not null    );

create table magazines (
		      magazine_id       integer
                      constraint magazines_magazine_id_fk
                      references publications
                      constraint magazines_magazine_id_pk
                      primary key,    issue_number      varchar(30));

create table articles (    article_id        integer 
                      constraint articles_article_id_pk
                      primary key
                      constraint articles_article_id_nn
                      not null    title             varchar(700)
                      constraint articles_title);

create table magazine_article_map (    
			magazine_id       integer
                      constraint mag_article_map_mag_id_nn
                      not null
                      constraint mag_article_map_mag_id_fk     references magazines,    article_id        integer
                      constraint mag_article_map_article_id_fk references articles
                      constraint mag_article_map_article_id_nn not null);

create table paragraphs (
  		      paragraph_id      integer 
                      constraint paragraphs_paragraph_id_pk primary key
                      constraint paragraphs_paragraph_id_nn not null,    
                      text              clob,
                      article_id        integer
                      constraint paragraphs_article_id_fk references articles
                      constraint paragraphs_article_id_nn not null);

create table authors (author_id         integer
                      constraint authors_author_id_nn not null
                      constraint authors_author_id_pk primary key,    
			name              varchar(700)
                      constraint authors_name_nn not null,
		      publication_id    integer
                      constraint authors_publication_id_fk
                      references publications);

Barry Williams
Principal Consultant
Database Answers

[ Home Page | Ask Us a Question | Day in the Life | Email Us | FAQs | Search & Site Map ]

© IceBreaker WebDesigns 2001