Document on Entity Relationship Diagram (ERD)

[TYPE THE DOCUMENT TITLE]
[Type the abstract of the document here. The abstract is typically a short summary of the
contents of the document. Type the abstract of the document here. The abstract is typically
a short summary of the contents of the document.]
[Year]
Northumbria
University
jxcf6

Table of Contents
1 Introduction.........................................................................................................................2
2 Part 1...................................................................................................................................2
2.1 ERD.............................................................................................................................2
2.1.1 ERD Explanation.................................................................................................2
2.1.2 SQL and output screenshots.................................................................................2
3 Part 2...................................................................................................................................2
3.1 Query 1........................................................................................................................2
3.2 Query 2........................................................................................................................2
4 Part 3...................................................................................................................................3
4.1 Report 1.......................................................................................................................3
4.1.1 Professional Issues...............................................................................................3
4.1.2 Legal Issues..........................................................................................................3
4.1.3 Ethical Issues........................................................................................................4
4.1.4 Security Issues......................................................................................................4
4.2 Report 2.......................................................................................................................5
4.2.1 Entity Relationship Diagram (ERD)....................................................................5
4.2.2 Semantic Data Model...........................................................................................5
4.2.3 Normalization.......................................................................................................5
4.2.4 Approach used for UMVO...................................................................................6
5 Conclusion..........................................................................................................................6
6 References...........................................................................................................................6

1 Introduction
UMVO has decided to implement an automated information system to make sure that its
information sets are adequately managed. It is required for the information system to have a
strong back-end implemented to achieve the business objectives. The document covers the
aspects associated with the database design for the system.
2 Part 1
In this part, an ER diagram along with its mapped database have been created. The database
has been made on Oracle database notations.
2.1 ERD
2.1.1 ERD Explanation
ER diagram has identified key entities – UMPIRE, MATCH, TEAM, PLAYER,
PRACTISE_SESSION, SUBSCRIPTION. There are few many-to-many relationships among
few entities such as SENIOR_PLAYER and SUBSCRIPTION; PLAYER and
PRACTICE_SESSION, etc. However all of such entities have been broken into normalized
form using an associative entity for e.g. SENIOR_SUBSC between SENIOR_PLAYER and
SUBSCRIPTION. Also, there are few inheritance relationship among classes like PLAYER
and SENIOR_PLAYER, JUNIOR_PLAYER.
2.1.2 SQL and output screenshots
CREATE TABLE UMPIRE (
umpireID NUMBER(9) NOT NULL,
umpireName CHAR(30) NOT NULL,
umpireQual VARCHAR(50) NOT NULL,
CONSTRAINT pk_ump PRIMARY KEY (umpireID)
);

INSERT INTO UMPIRE VALUES(1, 'Umpire 1', 'Grade4');
INSERT INTO UMPIRE VALUES(2, 'Umpire 2', 'Grade4');
CREATE TABLE TEAM (
teamID NUMBER(9) NOT NULL,
teamName CHAR(30) NOT NULL,
CONSTRAINT pk_team PRIMARY KEY (teamID)
);
INSERT INTO TEAM VALUES(1, 'Team 1');
INSERT INTO TEAM VALUES(2, 'Team 2');
INSERT INTO TEAM VALUES(3, 'Team 3');
INSERT INTO TEAM VALUES(4, 'Team 4');
CREATE TABLE PLAYER (
playerID NUMBER(9) NOT NULL,
playerFirstName CHAR(30) NOT NULL,
playerLastName CHAR(30) NOT NULL,
CONSTRAINT pk_player PRIMARY KEY (playerID)
);
INSERT INTO PLAYER VALUES(1, 'Player 1', 'Player 1');
INSERT INTO PLAYER VALUES(2, 'Player 2', 'Player 2');
INSERT INTO PLAYER VALUES(3, 'Player 3', 'Player 3');
INSERT INTO PLAYER VALUES(4, 'Player 4', 'Player 4');
INSERT INTO PLAYER VALUES(5, 'Player 5', 'Player 5');
INSERT INTO PLAYER VALUES(6, 'Player 6', 'Player 6');
CREATE TABLE PLAYER_JUNIOR (
playerID NUMBER(9) NOT NULL,
institutionName VARCHAR(30) NOT NULL,
CONSTRAINT pk_player_junior PRIMARY KEY (playerID),
CONSTRAINT fk_player_junior FOREIGN KEY (playerID) REFERENCES PLAYER
(playerID) ON DELETE CASCADE
);
INSERT INTO PLAYER_JUNIOR VALUES(1, 'InstitutionName');
CREATE TABLE PLAYER_SENIOR (
playerID NUMBER(9) NOT NULL,
sCharge NUMBER(5) NOT NULL,
CONSTRAINT pk_player_senior PRIMARY KEY (playerID),
CONSTRAINT fk_player_senior FOREIGN KEY (playerID) REFERENCES PLAYER
(playerID) ON DELETE CASCADE
);
INSERT INTO PLAYER_SENIOR VALUES(2, 10);
INSERT INTO PLAYER_SENIOR VALUES(3, 20);
INSERT INTO PLAYER_SENIOR VALUES(4, 30);
INSERT INTO PLAYER_SENIOR VALUES(5, 40);