Skip to content

Assignment 2: ER Diagramming

Step 1 – Sign up for Lucid Chart.

Sign up for the Lucid Chart software. You can practice creating diagrams from the two YouTube video tutorials.

Video 1 – Entity Relationship Diagram (ERD) Tutorial – Part 1

Video 2 – Entity Relationship Diagram (ERD) Tutorial – Part 2

Step 2 – Plane Diagram

For the next part of the assignment you should create a new diagram from the Plane database below.

Plane Diagram 1

A1 Plane Service Company charters airplanes. They are planning to implement a relational database to keep all their records. Information is kept on all the A1 Plane Service employees as well as extra information on every pilot that works for the company. Information is also kept on all of A1’s customers. The aircraft registration(tail) number uniquely identifies each aircraft that A1 owns. Charter flight data is maintained for every flight. This data includes the pilot, co-pilot, customers on the flight, and aircraft flown information. It is a business rule that every charter flight is required to have both a pilot and a co-pilot. Write any assumptions that you make.

Database Overview

Customer [CustomerID, CustFirstName, CustFastName, CustAddress, CustCity, CustState, CustZip, CustPhone]

Flight [FlightID, Pilot, CoPilot, CustomerID, TicketNumber, Destination, FlightTime, FlightDate, PlaneID]

  • FK: Pilot references Pilot(PilotID)
  • FK: CoPilot references Pilot(PilotID)
  • FK: PlaneID references Plane
  • FK: CustomerID references Customer

Pilot [PilotID, EmployeeID, LicenseNumber]

  • FK: EmployeeID references Employee

Employee [EmployeeID, EmpFirstName, EmpLastName, EmpAddress, EmpCity, EmpState, EmpZip, EmpPhone, JobTitle]

Plane [PlaneID, RegistrationNumber, Model, Capacity]

Step 3 – IT Department

For the last part of this assignment create a new diagram for the scenario below.

You are the Enterprise Architect Specialist in the IT department at Georgia College. You have been tasked with building a database to collect all necessary data on students, faculty and staff.

  • For students, you will need to track all of the courses they take as well as what grades they received in the courses. You will also need to track when they enrolled, their SAT scores and their majors and minors. Students can have multiple majors but only one minor.
  • Georgia College has many different student clubs. You need to collect any relevant data on these clubs including who the members are. There are no restrictions on how many clubs the students can be a belong to.
  • For faculty, you will need to track which courses they teach and what college they teach in. You will also need to track degrees, and teaching level (i.e., professor, associate professor, etc.).
  • For staff, you need to track which department they work in and what job they do at the college.
  • For all faculty and staff, you will need to track their hire date, base pay.

Step 4 – Turn it in

Take screenshots of your two (2) completed Lucid Chart designs and upload them to Assignment 2 on GeorgiaView.