CI4 : Modelization
Understand E/R diagrams.
Understanding E/R Diagrams (∼45mn – medium)
In this exercise, we will work with the following E/R diagram.
List of the entities with their attributes.
- Teacher: teacherID, name, salary
- Student: studentID, name, birthdate
- Session: SessionID, semester, year, courseID
- Course: courseID, name, credits
- Department: departmentID, name, budget
There is a redundancy in this diagram. Can you spot it?
courseID in Session is useless. It is represented by the relationship "instance".
List all the one-to-one relationships with their attributes (if they have any).
- manages
List all the one-to-many relationships with their attributes (if they have any).
- belongsTo
- advisor
- instance
List all the many-to-many relationships with their attributes (if they have any).
- teaches
- attend: grade
- prerequisite: minimalGrade
Does a teacher have to be an advisor? Why?
No, the minimal cardinality for the number of advised students is 0.
Can there be a department without a manager? Why?
No, the minimal cardinality of "manages" is 1.
Can a session take place if there are no students? Why?
No, the minimal cardinality of "attend" is 1.
Which relationship has the roles mentioned? What are these roles?
"prerequisite" has roles, "previous" and "next", to disambiguate the courses (why want to know which course is a prerequisite for which course).
Give the database schema for this E/R diagram.
- Teacher(teacherID: String, name: String, salary: Float)
- Student(studentID: String, name: String, birthdate: Date)
- Department(departmentID: String, name: String, budget: Integer)
- Course(courseID: String, name: String, credits: Integer)
- Session(sessionID: String, semester: Integer, year: Integer)
- manages(departmentID: String, managerID: String)
- belongsTo(teacherID: String, departmentID: String)
- advisor(studentID: String, teacherID: String)
- teaches(teacherID: String, sessionID: String)
- attend(studentID: String, sessionID: String, grade: Integer)
- instance(sessionID: String, courseID: String)
- prerequisite(previousCourseID: String, nextCourseID: String, minimalGrade: Integer)
Designing a E/R Diagram (∼45mn – medium)
This exercise asks you to create an E/R diagram describing a client's needs. Note that there is no unique solution to this problem.
To draw your diagram, you can use pen and paper or a website like diagrams.net (create a new diagram, and select entity-relationship diagram).
For this exercise, you can work in pairs. Dialogue is an excellent way to find flaws in the design.
A client from an airline company comes to you. They want to rebuild their data architecture, and you are in charge of organizing the data.
The client describes to you their needs. First, they want to manage their pilots. Each pilot has a name and a birthplace. Besides, each pilot can only fly several plane models characterized by their name and brand. Each senior pilot is assigned a junior copilot who always flies with her.
The airline company also has a bunch of planes of various models. A plane has a build date and a predefined number of seats. Each plane has its home airport where it is parked when not used. Likewise, pilots also have a home airport close to where they live. For each airport, we know its name and city.
The airline company organizes flights. Each flight must have a pilot, a copilot, a plane, a departure airport with the gate, and an arrival airport with the gate. In addition, a flight must have a departure time and arrival time.
Then, for each flight, seats will be available to sell at a specific price. A seat has a name, class, price, and we know whether or not it is close to the window. We also know which seats are next to each other.
When a customer (we know their name, address, and payment method) books a trip, he has to define from where he is leaving, where he is going, and when he wants to leave and arrive. Then, the trip is assigned to one or several seats, depending on whether a direct flight is available. Finally, a customer might pay to get the option to cancel his booking.
To draw your diagram, you can use pen and paper or a website like diagrams.net (create a new diagram, and select entity-relationship diagram).
For this exercise, you can work in pairs. Dialogue is an excellent way to find flaws in the design.
A client from an airline company comes to you. They want to rebuild their data architecture, and you are in charge of organizing the data.
The client describes to you their needs. First, they want to manage their pilots. Each pilot has a name and a birthplace. Besides, each pilot can only fly several plane models characterized by their name and brand. Each senior pilot is assigned a junior copilot who always flies with her.
The airline company also has a bunch of planes of various models. A plane has a build date and a predefined number of seats. Each plane has its home airport where it is parked when not used. Likewise, pilots also have a home airport close to where they live. For each airport, we know its name and city.
The airline company organizes flights. Each flight must have a pilot, a copilot, a plane, a departure airport with the gate, and an arrival airport with the gate. In addition, a flight must have a departure time and arrival time.
Then, for each flight, seats will be available to sell at a specific price. A seat has a name, class, price, and we know whether or not it is close to the window. We also know which seats are next to each other.
When a customer (we know their name, address, and payment method) books a trip, he has to define from where he is leaving, where he is going, and when he wants to leave and arrive. Then, the trip is assigned to one or several seats, depending on whether a direct flight is available. Finally, a customer might pay to get the option to cancel his booking.
Draw an E/R diagram to design the database that can answer the client's needs.
This is one of the possible solutions to model this problem.
Give the database schema for this E/R diagram.
- Pilots(pilotID: String, name: String, birthdate: Date)
- PlaneModels(modelID: String, name: String, brand: String)
- Planes(planeID: String, buildDate: Date, numberOfSeats: Integer)
- Airports(airportID: String, name: String, city: String)
- Flights(flightID: String, dateDeparture: Date, dateArrival: Date)
- Seats(seatID: String, price: Float, name: String, class: Integer, nearWindow: Boolean)
- Trips(tripID: String dateDeparture: Date, dateArrival: Date)
- Customers(customerID: String, name: String, address: String, paiementMethod: String)
- copilot(seniorID: String, juniorID)
- canPilot(pilotID: String, modelID: String)
- model(planeID: String, modelID: String)
- planHome(planeID: String, airportID: String)
- pilotHome(pilotID: String, airportID: String)
- hasPilot(flightID: String, pilotID: String)
- usePlane(flightID: String, planeID: String)
- departureAirportFlight(flightID: String, airportID: String, gate: String)
- arrivalAirportFlight(flightID: String, airportID: String, gate: String)
- departureAirportTrip(tripID: String, airportID: String, gate: String)
- arrivalAirportTrip(tripID: String, airportID: String, gate: String)
- hasSeat(seatID: String, flightID: String)
- nextSeat(seat1ID: String, seat2ID: String)
- bookedSeat(seatID: String, tripID)
- booking(tripID: String, customerID: String, cancelable: boolean)
