Urgenthomework logo
UrgentHomeWork
Live chat

Loading..

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. 

Answer:

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

Buy ICT701 Entity Relationship Diagram Answers Online

Talk to our expert to get the help with ICT701 Entity Relationship Diagram Answers to complete your assessment on time and boost your grades now

The main aim/motive of the management assignment help services is to get connect with a greater number of students, and effectively help, and support them in getting completing their assignments the students also get find this a wonderful opportunity where they could effectively learn more about their topics, as the experts also have the best team members with them in which all the members effectively support each other to get complete their diploma assignments. They complete the assessments of the students in an appropriate manner and deliver them back to the students before the due date of the assignment so that the students could timely submit this, and can score higher marks. The experts of the assignment help services at urgenthomework.com are so much skilled, capable, talented, and experienced in their field of programming homework help writing assignments, so, for this, they can effectively write the best economics assignment help services.

Get Online Support for ICT701 Entity Relationship Diagram Assignment Help Online

Copyright © 2009-2023 UrgentHomework.com, All right reserved.