title: Problem Specifying the tables in SQL
body: <p>I am relatively new to database design. I have a database which has the following design</p>
  
  <p>I have specified the the tables in SQL as follows.</p>
  
  <pre><code>CREATE TABLE Piece
  (identifier INT NOT NULL Unique,
   value      INT NOT NULL,
   the date it was acquited DATE,
   the date it was made DATE,
   PRIMARY KEY (identifier));
  
  CREATE TABLE Person
  (name VARCHAR(50),
   person_id INT NOT NULL Unique,
   biography VARCHAR (50),
   date of birth DATE,
   date of death DATE,
   PRIMARY KEY (person_id));
  
  CREATE TABLE Jewel
  (code INT NOT NULL Unique,
   gem type VARCHAR (50),
   weight INT,
   quality VARCHAR (50),
   color VARCHAR (50),
   description VARCHAR (50),
   PRIMARY KEY (code));
  
  CREATE TABLE Gem
  (type VARCHAR (50) NOT NULL,
   hardness INT,
   density  INT,
   FOREIGN KEY (type) references JEWEL(gem type));
  
  CREATE TABLE Ownership
  (person VARCHAR (50),
   piece  INT,
   start of ownership DATE,
   end of ownership   DATE,
   FOREIGN KEY (person) references PERSON(person_id),
   FOREIGN KEY (piece) references PIECE(identifier)); 
  </code></pre>
  
  <p>My question is </p>
  
  <p>1.) How can I specify a primary key to the GEM table as all the 3 attributes are not unique, should I have to create a new attribute like Gem_id, as I would prefer to use the existing attributes and not add a new attribute. </p>
  
  <p>2.) I have used person_id attribute in the person table to make it unique and use it as a primary key, is there another way to create a primary key for the person table without adding the extra attribute and obviously I cant include constraints to the existing attribute and make it UNIQUE</p>
  
  <p>3.) Is all integrity constraints and data types right ? Is my design flawed in any way. </p>