Banner

 

Reference 1

Answers to exercises

a.
stock
stockcode firmname stockquantity stockdividend stockpe stkexcode
listing
listingprice stockcode stkexcode
stockexchange
stkexcode exchangename nationcode
nation
nationcode nationname exchangerate
CREATE TABLE stock (
   stockcode      CHAR(3), 
   firmname       VARCHAR(20), 
   stockquantity  DECIMAL(8), 
   stockdividend  DECIMAL(5,2), 
   stockpe        DECIMAL(5), 
   stkexcode      CHAR(5), 
     PRIMARY KEY (stockcode) 
     FOREIGN KEY fkhomeexchange (stkexcode) REFERENCES stockexchange);

CREATE TABLE listing (
   listingprice   DECIMAL(6,2),
   stockcode      CHAR(3),
   stkexcode      CHAR(5),
     PRIMARY KEY (stockcode, stkexcode),
     FOREIGN KEY fkstock(stockcode) REFERENCES stock,
     FOREIGN KEY fkstockexchange(stkexcode) REFERENCES stockexchange);
     
CREATE TABLE STOCKEXCHANGE (
   stkexcode     CHAR(5),
   exchangename  VARCHAR(20),
   nationcode    CHAR(3),
     PRIMARY KEY (stkexcode),
     FOREIGN KEY fknation(nationcode) REFERENCES nation);
 																																																		
CREATE TABLE nation (
   nationcode    CHAR (3),
   nationname    VARCHAR(20),
   exchangerate  DECIMAL(9,5),
     PRIMARY KEY (nationcode));
b.
nation
natname natpop natarea
adminunit
unitname unittype unitpop unitarea natname
city
cityname citypop cityarea captype natcaptype natname unitname
adminunitcity
unitcaptype cityname natname unitname
CREATE TABLE nation ( 
natname VARCHAR(30), 
natpop  INTEGER, 
natarea INTEGER, 
PRIMARY KEY(natname)); 

CREATE TABLE adminunit ( 
unitname VARCHAR(30), 
unittype VARCHAR(10), 
unitpop  INTEGER, 
unitarea INTEGER, 
natname  VARCHAR(30), 
PRIMARY KEY(natname,unitname), 
CONSTRAINT fknationadmin FOREIGN KEY(natname) REFERENCES nation);

CREATE TABLE city (
cityname   VARCHAR(30),
citypop    INTEGER,
cityarea   INTEGER,
natcaptype VARCHAR(10),
unitname   VARCHAR(30),
natname    VARCHAR(30),
PRIMARY KEY(natname,unitname,cityname),
CONSTRAINT fknationcity FOREIGN KEY(natname) REFERENCES nation; 
CONSTRAINT fknationadmin FOREIGN KEY(natname,unitname) REFERENCES adminunit);
				
CREATE TABLE adminunitcity ( 
unitcaptype VARCHAR(10), 
cityname    VARCHAR(30), 
natname     VARCHAR(30), 
unitname    VARCHAR(30), 
PRIMARY KEY(natname,unitname, cityname), 
CONSTRAINT fkunit FOREIGN KEY(natname,unitname) REFERENCES adminunit, 
CONSTRAINT fkcity FOREIGN KEY(natname,unitname,cityname) REFERENCES city);
c.
person
id dob fname oname lname gender spouse1 spouse2 marriageno
marriage
marriageno marriagestatus begindate enddate spouse1 spouse2
CREATE TABLE person (
   id         VARCHAR(13),
   dob        DATE,
   fname      VARCHAR(20),
   oname      VARCHAR(20),
   lname      VARCHAR(20),
   gender     CHAR(1),
   spouse1    VARCHAR(13),
   spouse2    VARCHAR(13),
   marriageno INTEGER, 
     PRIMARY KEY (id), 
     FOREIGN KEY fkchild (spouse1, spouse2, marriageno) REFERENCES marriage);
																																											
CREATE TABLE marriage (
   marriageno     INTEGER,
   marriagestatus CHAR(5),
   begindate      DATE,
   enddate        DATE,
   spouse1        VARCHAR(13),
   spouse2        VARCHAR(13),
     PRIMARY KEY (marriageno, spouse1, spouse2),
     FOREIGN KEY fkspouse1(spouse1) REFERENCES person,
     FOREIGN KEY fkspouse2(spouse2) REFERENCES person); 
d.
aircraft
aircraftcode ...
lease
startdate ... aircraftcode agentid airlinename
agent
agentid
airline
airlinename ...
CREATE TABLE aircraft (
   aircraftcode VARCHAR(10), ... 
     PRIMARY KEY (aircraftcode));
     
CREATE TABLE agent 
   agentid      VARCHAR(10), ...,
     PRIMARY KEY(AGENTID));
																			
CREATE TABLE airline (
   airlinename VARCHAR(20), ...,
     PRIMARY KEY(airlinename));
     
CREATE TABLE lease (
   startdate    DATE, ... 
   aircraftcode VARCHAR(10),
   agentid      VARCHAR(10),
   airlinename  VARCHAR(20),
   PRIMARY KEY (startdate, aircraftcode, agentid, airlinename),
   FOREIGN KEY fkaircraft(aircraftcode) REFERENCES aircraft,
   FOREIGN KEY fkagent(agentid) REFERENCES agent,
   FOREIGN KEY fkairline(airlinename) REFERENCES airline);
e.
monarch
monname monnumb montype rgnbegin smonname smonnum
CREATE TABLE monarch (
   monname       VARCHAR(30),
   monnum        VARCHAR(5),
   montype       CHAR(5)     NOT NULL,
   rgnbegin      DATE,
   smonname      VARCHAR(30),
   smonnumb      CHAR(5),
     PRIMARY KEY(monname,monnum));

 

This page is part of the promotional and support material for Data Management (open edition) by Richard T. Watson
For questions and comments please contact the author
Date revised: 10-Dec-2021