ICT701 Entity Relationship Diagram

There are two parts to the assignment. Part A and Part B. Part A consists of the ERD diagram and any assumptions made. Part B is all the SQL code for your implementation of the database. Specifically, each part consists of the following: For Part A you are to include a word document or PDF that contains: - ER Diagram in Crows Foot notation (including primary & foreign keys) - Ensure normalisation of database to 3rd Normal Form - Assumptions that explain important design choices you made.

 

For Part B you are to submit - A single plain text file, name _ABC.sql. In this file you are to include all the SQL for your implementation. This includes: o CREATE TABLEstatements including all integrity constraints, and actions on update and delete o INSERT INTO statements for populating the database based on trial data from appropriate forms (if this must happen in a particular order then make sure you order itappropriately!). Note: There is a sample completed Abbreviated CV (Appendix C).

 

However, you must come up with and insert 5 additional staff members and corresponding sample data yourself to test the database you created. o The following queries and trigger: • SELECT: List the Staff ID, title, name (first and last together) and highest qualification for all staff ordered by the lastname. • SELECT: All staff that have not been approved to teach. Display the Staff ID, title, name (first and last together), approval notes. • SELECT: All staff that have had no teaching experience

 

• SELECT: Display the total number of staff grouped by the AQF qualification level • TRIGGER: When a staff member has been approved (i.e. when the staff approval date is updated – after update on staff table), the review date should automatically be inserted to be 1 year from the approval date. o CREATE INDEX statements for the Staff and Approval tables. ICT701 Relational Database Systems Task 2 6 | P a g e Specific Instructions As the organisation is new, there is no actual data yet. They plan to collect data via the Abbreviated CV (Appendix B).

 

There is a sample completed Abbreviated CV (Appendix C). However, you must also create 5 additional sample data yourself to test the database you create. Basically, you will be inserting 5 fictional people and corresponding information into the database. You must use MySQL to develop the database. MS Access is not appropriate for any section of this assignment. You must use the ER notation that was taught in ICT701. Penalties will apply to incorrect notations. 

I create this entity relationship diagram (ERD) according to the requirements. I create eight entities. This entity relationship diagram contains entities, attributes, relationship between entities and primary and foreign key in each table. These eight entities are following: -

1. Staff_List: - this table store the data related to all staff.
2. Qualification_Level: - this table store information related to qualification name and required level set by the ABC teach teaching.
3. AQF_Levels: - this table store the information related to the Staff
   
   qualification and staff qualification level active by each staff.
4. Employee_Experience: - this table store information related to the each staff employment experience.
5. Teaching_Experience: - this table store information related to the staff teaching experience.
6. Awards: - this table store information related to the staff obtains by the staff.
7. Approval_Detail: -this table store the information related to the approval detail to each staff.
8. Publications: - this table store information related to each staff publications.
9.         Information related to each entity: -

Entity name	Attribute  name	Description	Data type	size	Null/ not null	Key	Other
Staff_List	Staff_ID	Staff unique ID	int	-	-	Primary key	Auto increment
	Name_Title	MR, mrs wtc	Varchar	4	Not null	-	-
	First_Name	First name id staff	varchar	22	Not null	-	-
	Last_Name	Last name of staff	varchar	22	Not null	-	-
	Address	Address of staff	varchar	200	Not null	-	-
	Email	Email address of staff	varchar	55	Null	-	-
	Phone	Phone number of staff	varchar	12	Not null	-	-
Qualification_Level	Qualification_ID	Unique qualification ID	INT	-	-	Primary key	-
	QL_Name	Qualification name	varchar	77	Not null	-	-
	Req_Level	Minimum level od qualification	ENUM	-	-	-	LIST OF LEVEL
Aqf_Level	Staff_ID	Unique ID of staff	int	-	-	Primary key, foreign key	-
	Qualification_ID	Unique ID of qualification	int	-	-	Primary key, foreign key	-
	AQF_Level	Staff qualification level	Enum	-	Not null	-	List of level
Awards	Staff_Id	Unique staff id	int	-	-	Primary key, foreign key	-
	AwardName	Award name	varchar	67	-	Primary key	-
	Major_Subject	Major Subject related to that Award	varchar	33	Not null	-	-
	Instituation_Name	Name of Institution where award taken by staff	varchar	33	null	-	-
	Year	Year	varchar	4	Not null	-	-
Teaching_Experience	TeachExp_No	Unique key of Teaching experience	Int	-	-	Primary key	Auto increment
	Staff_IID	Unique ID of staff	Int	-	-	Foreign key	-
	Start_Date	Teaching experience Start date	date	-	Not null	-	-
	End_Date	Teaching experience end date	date	-	Not null	-	-
	Course_Name	Name of course	varchar	33	Not null	-	-
	Organization_Name	Name of organization	varchar	67	Not null	-	-
	Org_Address	Address of Organization	varchar	200	null	-	-
	Role_Name	Name of teacher role	Enum	-	-	-	List of Role
Employee_Experience	EmpExp_No	Unique key of employee experience	int	-	-	Primary key	Auto increment
	Staff_ID	Unique id of staff	int	-	-	Foreign key	-
	Start_Date	Employee Experience Start date	date	-	Not null	-	-
	End_Date	Employee Experience End date	date	-	Not null	-	-
	Work_Status	Status of work	Enum	-	-	-	List (Full time, Part time)
	Position	Position of staff	varchar	55	Not null	-	-
	Relevant_Duties	Duties of staff member	varchar	60	Not null	-	-
Publications	Publication_No	Unique number of staff publication	int	-	-	Primary key	Auto increment
	Staff_ID	Unique id of staff member	int	-	-	Foreign key	-
	Pub_Year	Publication Year	int	-	Not null	-	-
	Pub_Title	Publication title	long	-	Not null	-	-
	Journal_Volumn	Journal Volumn of publication	varchar	100	Not null	-	-
	Pub_Type	Publication type	Enum	-	-	-	list
	Reviewed	Reviewed of publication	Enum	-	-	-	List
	Research_Classfication	Research classification of Publication	Enum	-	-	-	list
	Main_Field	Main field of publication	Enum	-	-	-	list
Approval_Detail	Staff_ID	Unique staff ID	int	-	-	Primary key, foreign key	-
	Approval_Level	Approval level of staff	Enum	-	-	-	list
	Approval_Discipline	Approval Discipline of staff	varchart	40	Not null	-	-
	Who_Approved	witch person approved	varchar	50	Not null	-	-
	Location	Where staff is approved	Enum	-	-	-	list
	Approval_Date	Date of Approval	Date	-	Not null	-	-
	Review_Date	Date of Review	date	-	null	-	-
	Note	Description of approval	long	-	Not null	-	-

Normalization: -

• In this database no data groups are repeated. Each table contains primary key those gave information related to other attributes of the tables. Hence these relations are in first normal form.
• In this database each table contains the primary key and this primary key of each table gave the information of related to all other attributes of the table. So that these relations not contains the partial dependency. Hence these relations are in second normal form.
• In this database each table not contains the transitive dependency. Hence all relation in this database in third normal form.
•      Assumption: -

• 1: M relationship between Staff_List and Publications.
• 1: M relationship between Staff_List and teaching_Experience.
• 1: M relationship between Staff_List and Awards.
• 1: M relationship between Staff_List and Employee_Experience.
• 1: M relationship between Staff_List and Approval_Detail.
• M: M relationship between Staff_List and Qualification_Level. To resolve many to many relationship I create a new table that is AQF_Level. Because here given that a staff has one or many qualification and a qualification has one or many Staff. After creating AQF_LEevel table a Staff_List has one or many AQF_Level and each AQF_Level is related to one nad only one Staff_List. A Qualification_Level has one or many AQF_Level and each AQF_Level is related to one and only one Staff_List.

References

DuBois, P. (2013). MySQL. Upper Saddle River, NJ: Addison-Wesley.

DuBois, P., & Go?mez Pastor, J. (2005). MySQL. Madrid: Anaya Multimedia.

 Schwartz, B., Zaitsev, P., Tkachenko, V., & Zawodny, J. (2012). High performance MySQL. Sebastopol, CA: O'Reilly Media.

Tahaghoghi, S., & Williams, H. (2007). Learning MySQL. Sebastopol, Calif.: O'Reilly