Lecture 8 - ERD Use Cases PDF

FUNDAMENTALS OF DATABASES ERD and use cases NGUYEN Hoang Ha Email: [email protected]  Learning Objectives  Reinforce the ERD with Chen’s notation  Learn how to represent entities and weak entities with the relational model via use cases  Learn how to...

FUNDAMENTALS OF DATABASES ERD and use cases NGUYEN Hoang Ha Email: [email protected]  Learning Objectives  Reinforce the ERD with Chen’s notation  Learn how to represent entities and weak entities with the relational model via use cases  Learn how to transform E-R data modelling into relational designs  Know how to represent:  1:1 binary relationships  1:N binary relationships  M:N binary relationships 2 2 ERD CHEN’S NOTATION RECALL 3  Entity sets Weak Entity set Strong Entity Set 4 4  Attributes 5 5  Relationships Relationship Identifying Relationship Normal relationship connection Mandatory relationship connection Attention: do not group connection lines together 6 6  Cardinality  Attention: do not group connection lines 7 7  Example: Music ERD  The collection consists of albums.  An album is made by exactly one artist.  An artist makes one or more albums.  An album contains one or more tracks  Artists, albums, and tracks each have a name.  Each track is on exactly one album.  Each track has a time length, measured in seconds.  When a track is played, the date and time the playback began (to the nearest second) should be recorded; this is used for reporting when a track was last played, as well as the number of times music by an artist, from an album, or a track has been played. 8 8  Example: University ERD The university offers one or more programs. A program is made up of one or more courses. A student must enroll in a program. A student takes the courses that are part of her program. A program has a name, a program identifier, the total credit points required to graduate, and the year it commenced. 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. We can treat all given names as a single object—for example, “John Paul.” When a student takes a course, the year and semester he attempted it are recorded. 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). 9 9  Example: Flight ERD 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. 10 10  Example: Simplified Northwind ERD Email Address Phone Order Date Name OrderCode Quantity Unit Price List Price Name 1 N N Customers Make Orders Includes Category ID Name Address Email Creation Payment N Date method Phone Ordinal Products Name Unit Cost Number Quantity N 1 N Suppliers Supply Purchase Orders Includes N 11 11 ERD TO TABLES 12  Mapping Entity Sets to Database Tables  For each strong entity, create a table comprising its attributes and designate the primary key. ARTIST(artist_id, artist_name) 13 13  Mapping Entity Set to Database Tables  For each week entity, create a table comprising its attributes and including the primary key of its owning entity. ARTIST(artist_id, artist_name) ALBUM(album_id, artist_id, album_name) (*) If the relationship with the owning entity has any attributes, add them to this table. 14 14  Mapping Entities to Database Tables ARTIST(artist_id, artist_name) ALBUM(album_id, artist_id, album_name) TRACK(track_id, album_id, artist_id, trak_name, time) PLAYED(played, track_id, album_id, artist_id) 15 15  Practice - Mapping Entities to Database Tables Convert the entities in the following ER diagram to relational tables. 16 16  Mapping Relationships to Database Tables ❑ For each one-to-one relationship between two enties, include the primary key of one entity as a foreign key in the table belonging to the other. ❑ For each non-identifying one-to-many relationship between two entities, include the primary key of the entity on the “1” side as a foreign key in the table for the entity on the “N” side. 17 17  Mapping Relationships to Database Tables Program (program_id, name, CreditPoints, YearCommenced) Student (Student_Id, GivenNames, Surname,Date_of_Birth, program_id,YearEnrolled) 18 18  Mapping Relationships to Database Tables ❑For each many-to-many relationship between two entities, create a new table containing the primary key of each entity as the primary key, and add any attributes of the relationship. ATTEMPTS(Student_ID, course_id, year, semester, Mark, Grade) 19 19  ERD to Database Schema  Student (Student_Id, GivenNames, Surname,Date_of_Birth, program_id,YearEnrolled)  Program (program_id, name, CreditPoints, YearCommenced)  Course (course_id, program_id, Name, CreaditPoints, YearCommenced)  Attempts(Student_ID, course_id, program_id, year, semester, Mark, Grade) 20 20  Exercise  Airplane (RegistrationNumber, ModelNumber, Capacity)  Flight (FlightNumber, From, To, DepartureDate, DepartureTime, ArrivalDate, ArrivalTime, AirplaneRegistrationNumber)  Passenger (EmailAddress, GivenName, Surname)  Booking (FlightNumber, PassengerEmail) 21 21