170 likes | 289 Views
Entity-Relationship Model. Entity-Relationship Mode. What is it? Technique for developing an informal organization of tables How does it work? Identify entities (which become tables) Identify attributes of each entity (which become fields) Identify relationships. Notation. Entity
E N D
Entity-Relationship Mode • What is it? • Technique for developing an informal organization of tables • How does it work? • Identify entities (which become tables) • Identify attributes of each entity (which become fields) • Identify relationships
Notation • Entity • A general object is a component of the database. • E.g., book, author, publisher, customer • Attribute • Property of an entity • E.g., title and price of a book, name, phone number of a publishers, name, address of a customer • Entity Class • An entity and its attributes • E.g., Books(ISBN, bkTitle, price)Authors (auID, auName, auPhone)Publishers (pubID, pubName, pubPhone)
Purpose of Attributes • Contain raw data for the database • E.g., bkTtitle, price in Books, auName in Authors • Identify a record uniquely, i.e., as a primary key. • E.g., auID in Authors, pubID in Publishers • Link a table to another table, i.e., as a foreign key. (Need not be included in the Entity-relationship diagram)
Entity-Relationship Diagram • Given the following entities: • Students (stdID, stdName, stdAddress) • Classes (clID, clName, clTime, clRoom, instructor) • Instructors (instID, instName, instGender) • How are they related?
Students, Classes, Instructors N Attends N Students Classes N Teaches 1 Instructors
Students with Attributes instName stdAddress clTime stdName clName clRoom N Attends N Students Classes N Teaches
Refining Tables • Recall • Classes (clID, clName, clTime, clRoom, instName) • Several classes may have the same name. E.g. EN201-01, EN201-02 are both named “Intro to Writing.” • Rooms may require more information. • Instructor name is already in Instructor table
Create New Tables • Courses(crsID, crsName, crsCredits) • Rooms(rmID, rmLocation, rmNumber, rmCapacity) • Classes(clID, classTime, crsID, rmID, instID)Underlined and italicized fields are foreign keys.
More Tables Courses Holds Rooms Taught as N N Attends Classes Students N Teaches 1 Instructors
Your Turn • Bank database • What kind of entities are involve? • What are some of their attributes? • Academic database • What kind of entities are involve? • What are some of their attributes?
Your Turn (2) • Airline database • What kind of entities are involve? • What are some of their attributes? • Student Grades database • What kind of entities are involve? • What are some of their attributes?
Establish Links-Cardinality Customers Library Books Authors Publishers Employees
Establish Links-Cardinality Rooms Buildings Academics Student Classes Courses Faculty Dorms
Establish Links-Cardinality Flights Airlines Aircrafts Reservations Customers Pilots FltAttendants