Before your tutorial, work through and attempt all of the Questions in the first section. . Draw the ER diagram you created in Question 5 using the dia tool and . transform an ER diagram into a relational database schema). d. QUESTION 1: THE DATA DESIGN PROCESS (25 points). work plan to the next meeting. Erd practice exercises. 1. Exercise 1 Problem • Employees work in departments; each Draw an ER diagram that captures this information.
Add the names of all entities, weak entities, supertypes and subtypes that appear on the previous ERD as well, if one is available. If you are confident already, at this stage, that some noun shouldn't become an entity or an attribute, then it's certainly OK if you don't include it on the list that you prepare here.
Learning MySQL by Hugh E. Williams, Saied M.M. Tahaghoghi
The noun or noun phrase describes something in the problem domain. Reject anything that appears to be computer-related jargon that refers to an implementation detail for the system to be produced, rather than part of its requirements. That is, it must describe data that might be created by one application of a function provided by the system, and then could possibly be used read, modified, or even deleted by one or more later applications of system functions. Discard anything that does not meet this condition.
It should be possible for the system to need to know about multiple instances of whatever it is that the noun or noun phrase describes.
If this isn't the case, then the noun or noun phrase is more likely to describe an attribute of something else, than it is for it to be an entity. Note, by the way, that you don't need to try to find all of the candidate entity's attributes, at this point, in order to make this assessment. You also make sure that the same entity hasn't been included more than once, with different names: If two or more nouns or noun phrases appear to be synonyms and really do describe the same thing in the problem statementthen all but one of them should be eliminated from the list.
There are other characteristics of entities that are important - all their instances should have common attributes, and it should be possible to choose a primary key for the entity. We won't worry about these at this point, but they will be considered during later steps of the process. Find Candidate Relationships Make a list of all the transitive verbs and transitive verb phrases that appear in the problem statement. Include the names of all the relationships and associative objects that appear on the previous ERD, if one has been supplied.
Prune List of Candidates Discard verbs or verb phrases unless their subjects and objects are both the names of entities that were selected above or, synonyms for them. You should also discard verbs or verb phrases if they don't represent connections between instances of these entities that the system must remember in order to function.
Finally, make sure that you don't include two or more names for the same information. As usual, you should also eliminate synonyms so that information isn't included on your ERD more than once. Once you've formed and pruned your list of attributes, you should choose the entity that each attribute corresponds to.
A course has a name, a course identifier, a credit point value, and the year it commenced. Students have one or more given names, a surname, a student identifier, a date of birth, and the year they first enrolled. When he finishes the course, a grade such as A or B and a mark such as 60 percent are recorded. Each course in a program is sequenced into a year for example, year 1 and a semester for example, semester 1.
Although it is compact, the diagram uses some advanced features, including relationships that have attributes and two many-to-many relationships. The ER diagram of the university database In our design: Each student must be enrolled in a program, so the Student entity participates totally in the many-to-one EnrollsIn relationship with Program.
Entity Relationship Modeling Examples - Learning MySQL [Book]
A program can exist without having any enrolled students, so it participates partially in this relationship. As a weak entity, Course participates totally in the many-to-one identifying relationship with its owning Program. This relationship has Year and Semester attributes that identify its sequence position. Student and Course are related through the many-to-many Attempts relationships; a course can exist without a student, and a student can be enrolled without attempting any courses, so the participation is not total.
When a student attempts a course, there are attributes to capture the Year and Semester, and the Mark and Grade. For a real university, many more aspects would need to be captured by the database.
The airline has one or more airplanes. An airplane has a model number, a unique registration number, and the capacity to take one or more passengers. An airplane flight has a unique flight number, a departure airport, a destination airport, a departure date and time, and an arrival date and time. Each flight is carried out by a single airplane. A passenger has given names, a surname, and a unique email address.
A passenger can book a seat on a flight. The ER diagram of the flight database An Airplane is uniquely identified by its RegistrationNumber, so we use this as the primary key. A Flight is uniquely identified by its FlightNumber, so we use the flight number as the primary key.
The departure and destination airports are captured in the From and To attributes, and we have separate attributes for the departure and arrival date and time.
Because no two passengers will share an email address, we can use the EmailAddress as the primary key for the Passenger entity. An airplane can be involved in any number of flights, while each flight uses exactly one airplane, so the Flies relationship between the Airplane and Flight relationships has cardinality 1: N; because a flight cannot exist without an airplane, the Flight entity participates totally in this relationship.
A passenger can book any number of flights, while a flight can be booked by any number of passengers. N Books relationship between the Passenger and Flight relationship, but considering the issue more carefully shows that there is a hidden entity here: We capture this by creating the intermediate entity Booking and 1: N relationships between it and the Passenger and Flight entities.
Identifying such entities allows us to get a better picture of the requirements. There are no requirements to capture passenger details such as age, gender, or frequent-flier number.
If, instead, we assumed that the capacity is determined by the model number, we would have created a new AirplaneModel entity with the attributes ModelNumber and Capacity. The Airplane entity would then not have a Capacity attribute. Airlines typically use a flight number to identify a given flight path and schedule, and they specify the date of the flight independently of the flight number. For example, there is one IR flight on April 1, another on April 2, and so on.How to draw ER diagram
Different airplanes can operate on the same flight number over time; our model would need to be extended to support this. The system also assumes that each leg of a multihop flight has a different FlightNumber. Our database also has limited ability to describe airports.
If we were to model the airport as a separate entity, we could use the IATA-assigned airport code as the primary key.