Create Database system of Research higher degree.

it will be in two part
1-system security
2-data security

1.3.3.1: Translate logical data model for target DBMS
Through the use of MySQL the logical data model will be successfully translated for the target DBMS. The physical database model has had all the relations validated

and will support all of the required transactions and provide all necessary functionality to the users. The base relations for the database will be created based on

the logical model to show how all attributes will be represented in the database. This information includes data types, length and relationships. All Primary,

Alternate and Foreign keys are supported by the database in order to ensure that all information can be accessed by users on request. The relations represented show

which attributes can store null values. When choosing the attributes which could hold nulls, it was understood that the information provided by applicants may not

comply with any specific format. However there are some things which will definitely need to be present in order for the data to be entered. Obvious things include

information like a first name and email address. Other information required includes a phone number for referees, which is required if the referee is to have any worth

to the application. The database will need to store derived data, one form being how many applications an applicant has submitted. There are referential and general

constraints on the data. These are defined and will be supported by the MySQL database.

1.3.3.1.1:  Target DBMS
MySQL will be used.

1.3.3.1.2:  Base Relations
Domain FAN                 fixed length character string, length 8
Domain FirstName             variable length character string, length 15
Domain LastName             variable length character string, length 15
Domain OtherName             variable length character string, length 15
Domain PhoneNumber         variable length character string, length 15
Domain AddressLine1           variable length character string, length 50
Domain AddressLine2        variable length character string, length 50
Domain City         variable length character string, length 15
Domain State       variable length character string, length 15
Domain Country            variable length character string, length 15
Domain PostCode         variable length character string, length 10
Domain EmailAddress            variable length character string, length 40
Domain Sex          single character, must be one of ‘M’, ‘F’
Domain DateOfBirth      date
Staff (
FAN                FAN       NOT NULL,
firstName                 FirstName       NOT NULL,
lastName                 LastName,
otherName              OtherName,
Phone_Number                PhoneNumber,
addressLine1                 AddressLine1           NOT NULL,
addressLine2                 AddressLine2,
City             City         NOT NULL,
State             State       NOT NULL,
Country                  Country            NOT NULL,
postCode                 PostCode         NOT NULL,
Email_Address                EmailAddress            NOT NULL,
Sex                Sex          NOT NULL,
Date_Of Birth                  DateOfBirth      NOT NULL,

PRIMARY KEY (FAN),
ALTERNATE KEY (Email_Address)
);
Domain ApplicationID             fixed length character string, length 8
Domain ApplicantID             fixed length character string, length 8
Domain Entered_By            fixed length character string, length 8
Domain Application_Status        variable length character string, length 20, must be one of ‘Accepted’, ‘Pending’, ‘Waiting For Supervisor’
Domain Location            fixed length character string, length 8, must be one of ‘Internal, ‘External’
Domain Study_Type            fixed length character string, length 8, must be one of ‘FullTime, ‘PartTime’
Domain Proposed_Supervisor    variable length character string, length 30
Domain Expected_Date_Of_Commencement      date
Domain Last_Updated        date
Application (
ApplicationID                     ApplicationID         NOT NULL,
ApplicantID                    ApplicantID        NOT NULL,
Entered_By                    EnteredBy        NOT NULL,
Application_Status                ApplicationStatus,
Location                    Location,
Study_Type                    StudyType,
Proposed_Supervisor                ProposedSupervisor,
Expected_Date_Of_Commencement        ExpectedDateOfCommencement,
Last_Updated                    LastUpdated,

PRIMARY KEY (ApplicationID)
FOREIGN KEY (ApplicantID) references Applicant(ApplicantID) ) ON DELETE CASCADE, ON UPDATE CASCADE,
FOREIGN KEY (Entered_By) references Staff(FAN) ON DELETE SET NULL, ON UPDATE CASCADE,
FOREIGN KEY (Proposed_Supervisor) references Academic_Staff(FAN) ON DELETE SET NULL, ON UPDATE CASCADE
);
Domain ApplicantID             fixed length character string, length 8
Domain FirstName             variable length character string, length 15
Domain LastName             variable length character string, length 15
Domain OtherName             variable length character string, length 15
Domain PhoneNumber         variable length character string, length 15
Domain AddressLine1           variable length character string, length 50
Domain AddressLine2        variable length character string, length 50
Domain City         variable length character string, length 15
Domain State       variable length character string, length 15
Domain Country            variable length character string, length 15
Domain PostCode         variable length character string, length 10
Domain EmailAddress            variable length character string, length 40
Sex                    single character, must be one of ‘M’, ‘F’
GPA                    double, in the range 0 – 7
FundingDescription            variable length character string, length 200
DateOfBirth                 date
LastUpdated                date
Applicant (
ApplicantID                 ApplicantID            NOT NULL,
firstName                 FirstName       NOT NULL,
lastName                 LastName,
otherName              OtherName,
Phone_Number                PhoneNumber,
Addre¬¬¬ssLine1                  AddressLine1
addressLine2                  AddressLine2,
City               City,
State             State,
Country                  Country,
postcode                 PostCode,
Email_Address                EmailAddress            NOT NULL,
Sex                Sex,
GPA                    GPA,
Funding_Description            FundingDescription,
Date_Of Birth                  DateOfBirth,
Last_Updated                LastUpdated,

PRIMARY KEY (ApplicantID),
ALTERNATE KEY (Email_Address)
);
Domain ApplicantID             fixed length character string, length 8
Domain NameOfDegree        variable length character string, length 60
Domain DateStarted             date
Domain DateCompleted        date
Domain Institution             variable length character string, length 15
Domain LocationOfInstitution        variable length character string, length 40
Domain EducationLevel        variable length character string, length 10, must be one of ‘’Batchelors’, ‘Honors’, ‘Masters’
Domain MajorFieldOfStudy        variable length character string, length 40
Degree (
ApplicantID                 ApplicantID             NOT NULL,
Name_Of_Degree             NameOfDegree,
Date_Started                 DateStarted,
Date_Completed             DateCompleted,
Institution                 Institution            NOT NULL,
Location_Of_Institution        LocationOfInstitution,
Education_Level             EducationLevel        NOT NULL,
Major_Field_Of_Study         MajorFieldOfStudy        NOT NULL,

FOREIGN KEY (ApplicantID) references Applicant(ApplicantID) ON DELETE SET NULL, ON UPDATE CASCADE
PRIMARY KEY (ApplicantID, Name_Of_Degree)
);

Domain DocumentID             fixed length character string, length 8
Domain ApplicantID             fixed length character string, length 8
Domain DocumentTitle        variable length character string, length 40
Domain DocumentType        variable length character string, length 40
Domain DateUploaded        date
Domain DocumentStoragePathway     variable length character string, length 100
Document (
DocumentID                 DocumentID             NOT NULL,
ApplicantID                ApplicantID            NOT NULL,
Document_Title            DocumentTitle            NOT NULL,
Document_Type             DocumentType,
Date_Uploaded            DateUploaded            NOT NULL,
Document_Storage_Pathway        DocumentStoragePathway    NOT NULL,

PRIMARY KEY (DocumentID)
FOREIGN KEY (ApplicantID) references Applicant(ApplicantID) ON DELETE NO ACTION, ON UPDATE CASCADE
);

Domain EmailAddress            variable length character string, length 40
Domain FirstName             variable length character string, length 15
Domain LastName             variable length character string, length 15
Domain OtherName             variable length character string, length 15
Domain PhoneNumber         variable length character string, length 15
Domain Position            variable length character string, length 40
Domain AddressLine1           variable length character string, length 50
Domain AddressLine2        variable length character string, length 50
Domain City         variable length character string, length 15
Domain State       variable length character string, length 15
Domain Country            variable length character string, length 15
Domain PostCode         variable length character string, length 10
Domain ApplicantID             fixed length character string, length 8

Referee (
Email_Address                EmailAddress,
firstName                 FirstName       NOT NULL,
lastName                 LastName,
otherName              OtherName,
Phone_Number                PhoneNumber         NOT NULL,
Position                Position,
addressLine1                 AddressLine1,
addressLine2                 AddressLine2,
City             City,
State             State,
Country                  Country,
postCode                 PostCode,
ApplicantID                ApplicantID            NOT NULL,

FOREIGN KEY (ApplicantID) references Applicant(ApplicantID) ON DELETE NO ACTION, ON UPDATE CASCADE
PRIMARY KEY (Email_Address, ApplicantID)
);
Domain Sender                 fixed length character string, length 8
Domain Recipient                 fixed length character string, length 8
Domain DateTimeSent             date
Domain OutcomeOfCorrespondence        variable length character string, length 500
Correspondence (
Sender                    Sender                NOT NULL,       Recipient                Recipient

NOT NULL,
Date_Time_Sent            DateTimeSent            NOT NULL,
Outcome_Of_Correspondence    OutcomeOfCorrespondence    NOT NULL,

FOREIGN KEY (Sender) references Staff(FAN)
FOREIGN KEY (Recipient) references Applicant(ApplicantID) ON DELETE SET NULL, ON UPDATE CASCADE
PRIMARY KEY (Sender, Recipient, DateTime_Sent)
);

Domain ResearchField         variable length character string, length 40
Research_Fields (
Research_Field             ResearchField       NOT NULL

PRIMARY KEY (Research_Field)
);

Domain ApplicationID             fixed length character string, length 8
Domain ResearchField         variable length character string, length 15
Research_Higher_Degree (
ApplicationID            ApplicationID            NOT NULL,
Research_Field        ResearchField            NOT NULL

FOREIGN KEY (ApplicationID) references Application(ApplicationID) ON DELETE SET NULL, ON UPDATE CASCADE
FOREIGN KEY (Research_Field) references Research_Fields(Research_Field) ON DELETE SET NULL, ON UPDATE CASCADE
PRIMARY KEY (ApplicationID, Research_Field)
);

Domain ResearchField        variable length character string, length 40
Domain FAN                 fixed length character string, length 8
Supervisory_Interests (
Research_Field            ResearchField        NOT NULL,
FAN                    FAN,

FOREIGN KEY (Research_Field) references Research_Fields(Research_Field)  ) ON DELETE SET NULL, ON UPDATE CASCADE
FOREIGN KEY (FAN) references Academic_Staff(FAN) ON DELETE SET DEFAULT, ON UPDATE CASCADE
PRIMARY KEY (Research_Field, FAN)
);

Domain ApplicationID                fixed length character string, length 8
Domain PrincipalFAN                 fixed length character string, length 8
Principal_Supervisor (
ApplicationID            ApplicationID            NOT NULL,
Principal_FAN            PrincipalFAN            NOT NULL,

FOREIGN KEY (ApplicationID) references Application (ApplicationID) ON DELETE NO ACTION, ON UPDATE CASCADE
FOREIGN KEY (Principal_FAN) references Supervisory_Interests(FAN) ON DELETE SET NULL, ON UPDATE CASCADE
PRIMARY KEY (ApplicationID, Principal_FAN)
);

Domain ApplicationID                 fixed length character string, length 8
Domain AssociateFAN             fixed length character string, length 8
Associate _Supervisor (
ApplicationID            ApplicationID            NOT NULL,
Associate _FAN        AssociateFAN            NOT NULL,

FOREIGN KEY (ApplicationID) references Application (ApplicationID) ON DELETE NO ACTION, ON UPDATE CASCADE
FOREIGN KEY (Associate _FAN) references Supervisory_Interests(FAN) ON DELETE SET NULL, ON UPDATE CASCADE
PRIMARY KEY (ApplicationID, Associate _FAN)
);

1.3.3.1.2:  Representation of Derived Data
Number of applications submitted by an applicant: This attribute would be used to store the number of applications which have been submitted by an applicant. This

derived attribute would be appropriate to include because there would be no significant storage overhead. It will only need to be updated when an applicant submits a

new application for a Research Higher Degree. In this case the numberOfApplications attribute will need to be incremented by 1. It is vital to ensure that the correct

count is maintained at all times to ensure database integrity. Storing this value in the database means that each time an applicant is accessed through a query, the

value will not need to be recalculated. Creating the derived attribute requires a join between the Application and Applicant relations. The information was deemed

important as the number of unsuccessful applications already submitted by an applicant could be a deciding factor as to whether their latest application