Database Physical design
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 is to be considered.
Time since last update: There are attributes in various relations which store a timestamp of when a record was last updated. These records are found in the applicant and application relations. A derived attribute could be used to store the amount of time which would have passed since the last update. After analysing the information and processing required it was deemed that storing this derived attribute would be a waste of processing time due to the information being already stored elsewhere. The relations already store the date and time that the attribute was last updated. Using this information, the users would be able to figure out how long it has roughly been since the last update occurred. Calculating and storing the additional derived attribute is therefore unnecessary although possible to implement.
1.3.3.1.3: General Constraints
Applicant cannot submit more than 50 applications:
CONSTRAINT ApplicantSubmittedTooManyApplications
CHECK (NOT EXISTS (SELECT ApplicantID
FROM Application
GROUP BY ApplicantID
HAVING COUNT(*) > 50))
Before a staff member is assigned to be the supervisor for an application, check that they are not already the supervisor for the applicant:
CONSTRAINT StaffAlreadyASupervisor
CHECK (NOT EXISTS (SELECT Principal_Supervisor.AssociateFAN, Associate_Supervisor.PrincipalFAN
FROM Principal_Supervisor, Associate_Supervisor
WHERE Principal_Supervisor.AssociateFAN = Associate_Supervisor.PrincipalFAN
GROUP BY Associate_Supervisor.PrincipalFAN))
Applicant cannot be added to the database more than once:
CONSTRAINT ApplicantAlreadyExists
CHECK (NOT EXISTS (SELECT firstName, Email_Address
PLACE THIS ORDER OR A SIMILAR ORDER WITH US TODAY AND GET AN AMAZING DISCOUNT 🙂