Assignment Title

The assignment requires creating a SQL query to retrieve the name of the Prime Minister, the date of their appointment as Opposition Leader, and the Deputy Prime Minister's name and party, in ascending order. The query should include integrity constraints to ensure data consistency and uniqueness.

sql assignment
Student
3 October, 2016

Contents
PART 1...................................................................................................................................................2
PART 2...................................................................................................................................................7
1 | P a g e

PART 1
A. Find certain Governors General of Australia.
a. Find all Governors General of Australia who were Barons at the time of their
appointment. List them by Title and name and date that they were appointed to the
position. Order the list by ascending date of appointment. (5 marks)
SQL: SELECT GG_Title as Title, GG_name as name, GG_Begin as
appointment_date from governor_general where GG_Title like '%Baron%' order by
GG_Begin;
OUTPUT:
b. Now, format the date of appointment as day of the week, day of the month, month
and year; eg. Monday, 01 January, 1901. Order the list by ascending date of
appointment. (5 Marks)
SQL: SELECT GG_Title as Title, GG_name as name,
CONCAT(DAYNAME(GG_Begin) , ' , ' , DAYOFMONTH(GG_Begin), ' ',
MONTHNAME(GG_Begin) , ' , ' , YEAR(GG_Begin) ) AS APPOINTMENT_DATE
from governor_general where GG_Title like '%Baron%' order by GG_Begin;
OUTPUT:
2 | P a g e

B. Find certain Leaders of the Opposition.
a. Find all Leaders of the Opposition and their date of appointment, who assumed their
position after 01/01/1980. (5 marks)
SQL: SELECT OP_LDR_NAME AS LEADER_NAME, OP_LDR_BEGIN AS
APPOINT_DATE FROM OPPOSITION WHERE OP_LDR_BEGIN > '1980-01-01';
OUTPUT:
b. For each Leader of the Opposition listed, add their wife’s name and their date of
marriage. (5 Marks)
3 | P a g e

SQL: SELECT DISTINCT(O.OP_LDR_NAME) AS LEADER_NAME,
M.SPOUSE_NAME AS WIFE , M.MAR_YR AS MARRIAGE_DATE FROM
OPPOSITION O, OP_LDR_MARRIAGE M WHERE O.OP_LDR_NAME =
M.OP_LDR_NAME ;
OUTPUT:
C. Find certain Governors General of Australia.
a. List the title, name, date of appointment for Governors General of Australia who were
appointed between 01 January 1930 and 01 January 1960. Order by ascending date
of appointment. (5 marks)
SQL: SELECT GG_Title as Title, GG_name as name, GG_Begin as
appointment_date from governor_general where GG_Begin >= '1930-01-01' AND
GG_Begin <= '1960-01-01'order by GG_Begin;
OUTPUT:
4 | P a g e

b. Now add to the results of q3a, the list of Prime Ministers who appointed them and all
Leaders of the Opposition who served during their appointment as Governor General
of Australia. Order by ascending date of appointment. (5 marks)
SQL: SELECT g.GG_Title as Title, g.GG_name as name, g.GG_Begin as
appointment_date, g.pm_name as Prime_ministers, o.op_ldr_name as
opposition_leader from governor_general g, opposition o where GG_Begin >= '1930-
01-01' AND GG_Begin <= '1960-01-01' and G.pm_name = O.pm_name order by
G.gg_begin ;
OUTPUT:
D. Who are the Opposition Leaders who subsequently became Prime Minister after 1930?
a. List their name, the date they were elected Opposition Leader and the date they were
elected Prime Minister. The dates must be formatted as day of the week, day of the
month, month in digits and year in four digits; eg. Monday, 01/01/1901. Order the list
in ascending date of appointment as Prime Minister. (15 marks)
SQL: SELECT op_ldr_name as name, CONCAT(DAYNAME(OP_LDR_BEGIN) , ' , ' ,
DAYOFMONTH(OP_LDR_BEGIN), '/', MONTH(OP_LDR_BEGIN) , '/' ,
YEAR(OP_LDR_BEGIN) ) AS OPPOSITION_APPOINTMENT_DATE , M.min_begin
as PM_BEGIN_YEAR from opposition, MINISTRY M WHERE M.PM_NAME =
OP_LDR_NAME;
OUTPUT:
5 | P a g e

b. Now add their Deputy Prime Minister’s name and the party that they led. Order the list
by ascending date of appointment as Opposition Leader. (10 marks)
SQL: SELECT op_ldr_name as name, CONCAT(DAYNAME(OP_LDR_BEGIN) , ' , ' ,
DAYOFMONTH(OP_LDR_BEGIN), '/', MONTH(OP_LDR_BEGIN) , '/' ,
YEAR(OP_LDR_BEGIN) ) AS OPPOSITION_APPOINTMENT_DATE , M.min_begin
as PM_BEGIN_YEAR , D.DEPUTY_NAME AS DEPUTY_PM, D.PARTY AS
DEPUTY_PM_PARTY from opposition, MINISTRY M, DEPUTY_PM D WHERE
M.PM_NAME = OP_LDR_NAME AND M.MIN_NR = D.MIN_NR;
OUTPUT:
6 | P a g e

PART 2
The design of the Prime_Ministers database is now very old. You have been asked to review this
design, as shown in the ERD below and advise how it could be updated. You are to complete the
following tasks:
E. Advise how you would improve the ability to query information in this database. For simplicity,
use only the tables prime_minister, governor_general, ministry and opposition in your answer.
a. What new integrity constraints would you use in each of these tables? (5 marks)
ANSWER: The integrity constraints that would be recommended are:
1. PM_ID: PRIMARY KEY IN PRIME_MINISTER
2. PM_ID: FOREIGN KEY IN OPPOSITION, MINISTRY, GOVERNOR_GENERAL
3. GG_ID: PRIMARY KEY IN GOVERNOR_GENERAL
4. OP_ID: PRIMARY KEY IN OPPOSITION
7 | P a g e

b. Why would you use these integrity constraints? Explain how your constraints would
improve queries on the tables. (10 marks)
1. Answer: Adding primary key PM_ID to the prime_minister table. This will help in
storing each prime minister record uniquely and eliminating the redundancy. For
example 2 prime ministers may have same name and birth year. Then it will be
difficult to store the information uniquely in the table.
2. Adding PM_ID foreign key to the table governor general. This will help in
maintaining the primary key foreign key relationship between them because if the
prime minister names are same but they are different people, thus this will create
errors and redundancy in the database.
3. Adding GG_ID in governor general table and OP_ID in the opposition table.
These will also help in locating each record uniquely in the table and maintain
consistency of the database.
4. Adding PM_ID foreign key to the table ministry and opposition. This will help in
maintaining the primary key foreign key relationship between them because again
if the prime minister names are same but they are different people, thus this will
create errors and redundancy in the database.
5. Removing default ZERO (0) from min_nr in ministry because it is a primary key
and must be unique and should not be left zero or null
6. All these will make the entire data more understandable thereby eliminating the
repeated records that occur during querying of the data hence making it complex
because as we see there are multiple time an opposition leader came to position
or became the PM. So to eliminate all this redundancy, these changes are
necessary.
7. Further we can create new tables to store the appointments of the PM, opposition
leaders, governor generals so that there is no duplicity in the parent tables.
c. Write the DDL code that would implement your new integrity constraints for the
following tables:
1. Prime_minister (5 marks)
8 | P a g e

CREATE TABLE prime_minister (
PM_ID VARCHAR(10) PRIMARY KEY,
pm_name varchar(12) NOT NULL,
birth_yr int(11) DEFAULT '0',
yrs_served float DEFAULT '0',
death_age int(11) DEFAULT '0',
state_born varchar(3) DEFAULT NULL,
state_rep varchar(3) DEFAULT NULL,
);
2. Governor General (5 marks)
CREATE TABLE governor_general (
GG_ID INT PRIMARY KEY,
GG_name varchar(50) NOT NULL,
GG_title varchar(50) DEFAULT NULL,
GG_begin date DEFAULT NULL,
GG_end date DEFAULT NULL,
PM_ID VARCHAR(10),
FOREIGN KEY(PM_ID) REFERENCES PRIME_MINISTER(PM_ID)
);
3. Ministry (5 marks)
CREATE TABLE ministry (
min_nr int(11) NOT NULL DEFAULT '0',
PM_ID varchar(10) DEFAULT NULL,
Party varchar(50) DEFAULT NULL,
min_begin date DEFAULT '0000-00-00',
FOREIGN KEY(PM_ID) REFERENCES PRIME_MINISTER(PM_ID)
);
4. Opposition (5 marks)
CREATE TABLE opposition (
9 | P a g e

OP_ID INT PRIMARY KEY,
Op_Ldr_Name varchar(50) NOT NULL,
Op_Ldr_begin date NOT NULL,
Op_Ldr_end date DEFAULT NULL,
Party varchar(45) NOT NULL,
PM_ID varchar(10) DEFAULT NULL,
FOREIGN KEY(PM_ID) REFERENCES PRIME_MINISTER(PM_ID)
)
10 | P a g e